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 IXlMergedCells.Add method, it checks whether the cell range you wish to merge intersects other merged cells and throws a ArgumentException if it does. However, if there are many merged cells in your document, this checking operation may slow down the process of merging cells. To disable the internal checking of intersecting merged cells, and thereby speed up the document generation, call the IXlMergedCells.Add method overload with the checkOverlap parameter set to false. Keep in mind that in this case, you may get an invalid document if there are any merged cells that intersect other merged cells in a worksheet.
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.
