Python excel cell merge

Openpyxl || Merge/Unmerge excel cells and assign value to unmerged cells.

In this tutorial, we will learn how to merge/unmerge cells in an excel file and assign the value to unmerged cells in an excel sheet using the openpyxl module in python. In openpyxl, we will use the merge_cells() built-in function to merge cells and unmerge_cells() to unmerge the cells.Before jumping into the coding section download the sample excel file by clicking the download sample file button or you can use your document as well.

Merge:

Below is the snapshot of the input sheet.We are going to merge red outlined cells.

Source Code:

import openpyxl from openpyxl.styles import Alignment wbook=openpyxl.load_workbook("openpyxl_merge_unmerge.xlsx") sheet=wbook["merge_sample"] data=sheet['B4'].value sheet.merge_cells('B4:E4') sheet['B4']=data sheet['B4'].alignment = Alignment(horizontal='center') wbook.save("openpyxl_merge_unmerge.xlsx") exit() 

Now save the above program and give a name merge_openpyxl.py then execute the file as shown below.

Output:

Unmerge:

Below is the snapshot of the excel sheet on which we are going to perform an unmerged operation. Also, we will assign the value to unmerged cells as well.

Source code:

import openpyxl from openpyxl.utils import range_boundaries wbook=openpyxl.load_workbook("openpyxl_merge_unmerge.xlsx") sheet=wbook["unmerge_sample"] for cell_group in sheet.merged_cells.ranges: min_col, min_row, max_col, max_row = range_boundaries(str(cell_group)) top_left_cell_value = sheet.cell(row=min_row, column=min_col).value sheet.unmerge_cells(str(cell_group)) for row in sheet.iter_rows(min_col=min_col, min_row=min_row, max_col=max_col, max_row=max_row): for cell in row: cell.value = top_left_cell_value wbook.save("openpyxl_merge_unmerge.xlsx") exit() 

Now save the above program and give a name unmerge_openpyxl.py and execute the program as shown below.

Источник

Example: Merging Cells

This program is an example of merging cells in a worksheet. See the merge_range() method for more details.

############################################################################## # # A simple example of merging cells with the XlsxWriter Python module. # # SPDX-License-Identifier: BSD-2-Clause # Copyright 2013-2023, John McNamara, jmcnamara@cpan.org # import xlsxwriter # Create an new Excel file and add a worksheet. workbook = xlsxwriter.Workbook("merge1.xlsx") worksheet = workbook.add_worksheet() # Increase the cell size of the merged cells to highlight the formatting. worksheet.set_column("B:D", 12) worksheet.set_row(3, 30) worksheet.set_row(6, 30) worksheet.set_row(7, 30) # Create a format to use in the merged range. merge_format = workbook.add_format(  "bold": 1, "border": 1, "align": "center", "valign": "vcenter", "fg_color": "yellow", > ) # Merge 3 cells. worksheet.merge_range("B4:D4", "Merged Range", merge_format) # Merge 3 cells over two rows. worksheet.merge_range("B7:D8", "Merged Range", merge_format) workbook.close() 

© Copyright 2013-2023, John McNamara.
Created using Sphinx 1.8.6.

Источник

Python Excel Merge Unmerge Cells

In this example I am going to show you how to merge and unmerge cells in excel sheet using Python script. You can merge two or more columns in a single row or multiple rows in excel file.

For merging cells you need to specify the range of rows and columns in the excel sheet. So, you need to specify from which row to which row and from which column to which column you want to merge. Rows and columns are 1 based index.

Remember when you merge multiple cells, the contents of only one cell (the upper-left cell for left-to-right languages, or the upper-right cell for right-to-left languages) appear in the merged cell. The contents of the other cells that you merge are deleted.

You can also unmerge cells which were previously merged in an excel sheet (file). For unmerging cells you also need to specify a range of rows and columns.

Prerequisites

Python 3.9.1, openpyxl 3.0.10 (pip install openpyxl)

Merge Cells

Here I am going to discuss about merging cells in excel sheet. Let’s say I have the following data in the excel file:

I am going to merge cells or columns B and C to distribute the text “This is a merge test” over two columns.

The following code snippet will do the merging job:

Or even you can specify row and column indices to merge your column data:

ws.merge_cells(start_row=2, start_column=2, end_row=2, end_column=3)

The both lines of above code will produce the same output as shown in the below image:

You can also merge multiple rows and columns of excel sheet. For example, the following line of code will merge second and third rows/columns:

Or the following line will produce the same output:

ws.merge_cells(start_row=2, start_column=2, end_row=3, end_column=3)

The output will be as shown in the following image:

Unmerge Cells

Now I am going to show you how to unmerge cells in excel sheet. Now I will unmerge the merged cells from the above example.

To unmerge columns or cells, you can use the following line code:

ws.unmerge_cells(start_row=2, start_column=2, end_row=3, end_column=3)

provided that you have merged second and third rows/columns or change accordingly. The unmerge functionality will produce the text in the second row and third column.

Источник

How to merge cells in Python

EasyXLS Excel library can be used to export Excel files with Python on Windows, Linux, Mac or other operating systems. The integration vary depending on the operating system or if the bridge for .NET Framework of Java is chosen:

1. EasyXLS on Windows using .NET Framework with Python

2. EasyXLS on Linux, Mac, Windows using Java with Python

EasyXLS on Windows using .NET Framework with Python

If you opt for the .NET version of EasyXLS, the below code requires Pythonnet, a bridge between Python and .NET Framework.

Step 1: Download EasyXLS Excel Library for .NET

To download the trial version of EasyXLS Excel Library, press the below button:

If you already own a license key, you may login and download EasyXLS from your account.

Install the downloaded EasyXLS installer for v8.6 or earlier.

Step 2: License file setup

Step required for EasyXLS v9.0 or later.

If you are using a trial, generate a trial license file from EasyXLS trials page. The trial license is valid for 30-days.

If you own a license key, you may login to the account that purchased the license and generate the license file from:
https://www.easyxls.com/my-orders

Setup the license file into your project using these guidelines.

Step 3: Install Pythonnet

For the installation you need to run «pip» command as it follows. Pip is a package-management system used to install and manage software packages written in Python.
\Scripts>pip install «pythonnet.whl»

Step 4: Include EasyXLS library into project

EasyXLS.dll must be added to your project. EasyXLS.dll can be found:
— Inside the downloaded archive at Step 1 for EasyXLS v9.0 or later
— Under installation path for EasyXLS v8.6 or earlier, in «Dot NET version» folder.

Step 5: Run Python code that merges cells in Excel sheet

Execute the following Python code that exports an Excel file with merge cells.

 ) workbook = ExcelDocument(1) xlsTable = workbook.easy_getSheet( xlsTable.easy_mergeCells( print() workbook.easy_WriteXLSXFile() sError = workbook.easy_getError() ) + sError + gc.collect() 

EasyXLS on Linux, Mac, Windows using Java with Python

If you opt for the Java version of EasyXLS, a similar code as above requires Py4J, Pyjnius or any other bridge between Python and Java.

Step 1: Download EasyXLS Excel Library for Java

To download the trial version of EasyXLS Excel Library, press the below button:

If you already own a license key, you may login and download EasyXLS from your account.

Install the downloaded EasyXLS installer for v8.6 or earlier.

Step 2: License file setup

Step required for EasyXLS v9.0 or later.

If you are using a trial, generate a trial license file from EasyXLS trials page. The trial license is valid for 30-days.

If you own a license key, you may login to the account that purchased the license and generate the license file from:
https://www.easyxls.com/my-orders

Setup the license file into your project using these guidelines.

Step 3: Install Py4j

For the Py4j installation you need to run «pip» command as it follows. Pip is a package-management system used to install and manage software packages written in Python.
\Scripts>pip install «py4j.whl»

Step 4: Create additional Java program

The following Java code needs to be running in the background prior to executing the Python code.

Step 5: Add py4j library to CLASSPATH

py4j.jar must be added to your classpath of the additional Java program. py4j.jar can be found after installing Py4j, in «\share\py4j» folder.

Step 6: Add EasyXLS library to CLASSPATH

EasyXLS.jar must be added to your classpath of the additional Java program. EasyXLS.jar can be found:
— Inside the downloaded archive at Step 1 for EasyXLS v9.0 or later
— Under installation path for EasyXLS v8.6 or earlier, in «Lib» folder.

Step 7: Run additional Java program

Start the gateway server application and it will implicitly start Java Virtual Machine as well.

Step 8: Run Python code that merges cells in Excel sheet

Execute a code as below Python code that exports an Excel file with merge cells.

 ) workbook = gateway.jvm.ExcelDocument(1) xlsTable = workbook.easy_getSheet( xlsTable.easy_mergeCells( print() workbook.easy_WriteXLSXFile() sError = workbook.easy_getError() ) + sError + gc.collect() 

Источник