Merging and Unmerging Cells
Introduction
You don’t always want the same number of cells in every row or column. For example, you might want to put a title in a cell that spans several columns. Or, if creating an invoice, you might want fewer columns for the total. To make one cell from two or more cells, merge them. Microsoft Excel lets users select files and merge them to structure the spreadsheet the way they want.
Merging Cells in a Worksheet
Merging Cells in Microsoft Excel
The following steps describe how to merge cells in the worksheet using MS Excel.
- Copy the data you want into the upper-leftmost cell within the range.
- Select the cells you want to merge.
- To merge cells in a row or column and center the cell contents, click Merge and Center icon on the Formatting toolbar.
Merging Cells with Aspose.Cells for Python via .NET
The Aspose.Cells.Cells Class has some useful methods for the task. For example, the method Merge() merges the cells into a single cell within a specified range.
The following example shows how to merge cells (C6:E7) in a worksheet.
from aspose.cells import BackgroundType, Workbook | |
from aspose.pydrawing import Color | |
from os import os, path | |
# For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET | |
# The path to the documents directory. | |
dataDir = RunExamples.GetDataDir(".") | |
# Create directory if it is not already present. | |
IsExists = path.isdir(dataDir) | |
if notIsExists: | |
os.makedirs(dataDir) | |
# Create a Workbook. | |
wbk = Workbook() | |
# Create a Worksheet and get the first sheet. | |
worksheet = wbk.worksheets[0] | |
# Create a Cells object ot fetch all the cells. | |
cells = worksheet.cells | |
# Merge some Cells (C6:E7) into a single C6 Cell. | |
cells.merge(5, 2, 2, 3) | |
# Input data into C6 Cell. | |
worksheet.cells.get(5, 2).put_value("This is my value") | |
# Create a Style object to fetch the Style of C6 Cell. | |
style = worksheet.cells.get(5, 2).get_style() | |
# Create a Font object | |
font = style.font | |
# Set the name. | |
font.name = "Times New Roman" | |
# Set the font size. | |
font.size = 18 | |
# Set the font color | |
font.color = Color.blue | |
# Bold the text | |
font.is_bold = True | |
# Make it italic | |
font.is_italic = True | |
# Set the backgrond color of C6 Cell to Red | |
style.foreground_color = Color.red | |
style.pattern = BackgroundType.SOLID | |
# Apply the Style to C6 Cell. | |
cells.get(5, 2).set_style(style) | |
# Save the Workbook. | |
wbk.save(dataDir + "mergingcells.out.xls") |
Unmerging (Splitting) Merged Cells
Using Microsoft Excel
The following steps describe how to split merged cells using Microsoft Excel.
- Select the merged cell. When cells have been combined, Merge and Center is selected on the Formatting toolbar.
- Click Merge and Center on the Formatting toolbar.
Using Aspose.Cells for Python via .NET
The class Aspose.Cells.Cells has a method named UnMerge() that splits the cells into their original state. The method unmerges the cells using the cell’s reference in the merged cell range.
The following example shows how to split the merged cells (C6). The example uses the file created in the previous example and splits the merged cells.
from aspose.cells import Workbook | |
# For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET | |
# The path to the documents directory. | |
dataDir = RunExamples.GetDataDir(".") | |
# Create a Workbook. | |
# Open the excel file. | |
wbk = Workbook(dataDir + "mergingcells.xls") | |
# Create a Worksheet and get the first sheet. | |
worksheet = wbk.worksheets[0] | |
# Create a Cells object ot fetch all the cells. | |
cells = worksheet.cells | |
# Unmerge the cells. | |
cells.un_merge(5, 2, 2, 3) | |
# Save the file. | |
wbk.save(dataDir + "unmergingcells.out.xls") |