Use the Excel Export API to Merge Cells or Split Merged Cells
- 3 minutes to read
This example illustrates how to use the Excel Export Library to merge and split cells in a worksheet.
#Merge Cells
To merge adjacent cells in a worksheet into a single cell, pass the range of cells to be merged to the IXlMergedCells.Add method of the IXlMergedCells object, which represents the collection of all merged cells in a worksheet and can be accessed from the IXlSheet.MergedCells property.
Note
When you merge a cell range, the content and format settings of only the top-left cell will appear in the resulting merged cell. The data contained in other cells of the original range will be lost.
// Merge cells contained in the range A1:E1.
sheet.MergedCells.Add(XlCellRange.FromLTRB(0, 0, 4, 0));
// Merge cells contained in the range A2:A5.
sheet.MergedCells.Add(XlCellRange.FromLTRB(0, 1, 0, 4));
// Merge cells contained in the range B2:E5.
sheet.MergedCells.Add(XlCellRange.FromLTRB(1, 1, 4, 4));
The image below illustrates the result of code execution (the workbook is opened in Microsoft® Excel®).
Important
By default, you cannot merge cells in a range that overlap existing merged cells in a worksheet. When you call the IXl
#Split Merged Cells
You can split a merged cell into separate cells again by deleting the merged cells from the IXlMergedCells collection. To do this, use the IXlMergedCells.Remove or IXlMergedCells.RemoveAt method as shown in the example below.
// Split the merged cell in the range A1:E1.
sheet.MergedCells.RemoveAt(0);
// Split the merged cell in the range A2:A5.
sheet.MergedCells.Remove(XlCellRange.FromLTRB(0, 1, 0, 4));
To split all merged cells in the worksheet at once, call the IXlMergedCells.Clear method.
The image below shows how cells are split in a worksheet (the workbook is opened in Microsoft® Excel®). Note that the content of the merged cell appears in the top-left cell of the range of split cells.