Merging cells means combining multiple adjacent cells into a larger one. The merged cell will inherit all the properties and contents of the original cells. This feature is particularly useful when you need to create a larger cell to accommodate more content or create a header row. Unmerging cells, on the other hand, involves reverting the merged cells back to the original multiple cells. The unmerged cells will revert back to their original independent state, and you can input different content into each individual cell. Merging and unmerging cells are common operations in spreadsheet software, allowing you to adjust the layout and structure of a table as needed, making the data clearer and easier to understand. In this article, you will learn how to merge or unmerge cells in Excel in Python by using Spire.XLS for Python.
- Merge the Cells of the Specified Row or Column
- Merge Ranges of Cells
- Unmerge the Cells of the Specified Row or Column
- Unmerge Ranges of Cells
Install Spire.XLS for Python
This scenario requires Spire.XLS for Python and plum-dispatch v1.7.4. They can be easily installed in your Windows through the following pip command.
pip install Spire.XLS
If you are unsure how to install, please refer to this tutorial: How to Install Spire.XLS for Python on Windows
Merge the Cells of the Specified Row or Column
With Spire.XLS for Python, users are able to effortlessly merge the cells of the specific column or row in Excel, thereby enhancing their data manipulation capabilities. The following are the detailed steps.
- Create an object of Workbook class.
- Load a sample Excel file using Workbook.LoadFromFile() method.
- Get the desired worksheet by using Workbook.Worksheets[] property.
- Access the cells of the specific column or row and merge them by calling Worksheet.Columns[].Merge() or Worksheet.Rows[].Merge() methods.
- Save the result file using Workbook.SaveToFile() method.
- Python
from spire.xls import * from spire.xls.common import * inputFile = "Sample.xlsx" outputFile = "MergeRowColumn.xlsx" #Create an object of Workbook class workbook = Workbook() #Load a sample Excel file from disk workbook.LoadFromFile(inputFile) #Get the first worksheet of this file sheet = workbook.Worksheets[0] #Merge the first column in Excel #sheet.Columns[0].Merge() #Merge the first row in Excel sheet.Rows[0].Merge() #Save the result file workbook.SaveToFile(outputFile, ExcelVersion.Version2013) workbook.Dispose()
Merge Ranges of Cells
In addition to merging the specific column or row, Spire.XLS for Python also supports users to merge the specified cell ranges. The following are the detailed steps.
- Create an object of Workbook class.
- Load a sample Excel file using Workbook.LoadFromFile() method.
- Get the desired worksheet by using Workbook.Worksheets[] property.
- Access the specific range of cells and merge them together by calling Worksheet.Range[].Merge() method.
- Save the result file using Workbook.SaveToFile() method.
- Python
from spire.xls import * from spire.xls.common import * inputFile = "Sample.xlsx" outputFile = "MergeCellRange.xlsx" #Create an object of Workbook class workbook = Workbook() #Load a sample Excel file from disk workbook.LoadFromFile(inputFile) #Get the first worksheet of this file sheet = workbook.Worksheets[0] #Merge the particular cell range in Excel sheet.Range["B6:G6"].Merge() #Save the result file workbook.SaveToFile(outputFile, ExcelVersion.Version2013) workbook.Dispose()
Unmerge the Cells of the Specified Row or Column
Additionally, users are also allowed to unmerge the merged cells of the specific column or row at any time with Spire.XLS for Python. The following are the detailed steps.
- Create an object of Workbook class.
- Load a sample Excel file using Workbook.LoadFromFile() method.
- Get the desired worksheet by using Workbook.Worksheets[] property.
- Access the merged cells of the specific column or row and unmerge them by calling Worksheet.Columns[].UnMerge() and Worksheet.Rows[].UnMerge() methods.
- Save the result file using Workbook.SaveToFile() method.
- Python
from spire.xls import * from spire.xls.common import * inputFile = "MergeRowColumn.xlsx" outputFile = "UnmergeRowColumn.xlsx" #Create an object of Workbook class workbook = Workbook() #Load a sample file from disk workbook.LoadFromFile(inputFile) #Get the first worksheet of this file sheet = workbook.Worksheets[0] #Unmerge the first column in Excel #sheet.Columns[0].UnMerge() #Unmerge the first column in Excel sheet.Rows[0].UnMerge() #Save to file. workbook.SaveToFile(outputFile, ExcelVersion.Version2013) workbook.Dispose()
Unmerge Ranges of Cells
What's more, users are also able to unmerge the specified cell ranges using Spire.XLS for Python. The following are the detailed steps.
- Create an object of Workbook class.
- Load a sample Excel file using Workbook.LoadFromFile() method.
- Get the desired worksheet by using Workbook.Worksheets[] property.
- Access the specific cell ranges and unmerge them by calling Worksheet.Range[].UnMerge() method.
- Save the result file using Workbook.SaveToFile() method.
- Python
from spire.xls import * from spire.xls.common import * inputFile = "MergeCellRange.xlsx" outputFile = "UnmergeCellRange.xlsx" #Create an object of Workbook class workbook = Workbook() #Load a sample file from disk workbook.LoadFromFile(inputFile) #Get the first worksheet of this file sheet = workbook.Worksheets[0] #Unmerge the particular cell range in Excel sheet.Range["B6:G6"].UnMerge() #Save to file. workbook.SaveToFile(outputFile, ExcelVersion.Version2013) workbook.Dispose()
Apply for a Temporary License
If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.
