Merge and Unmerge Cells
You have the ability to merge two or more adjacent cells into a single cell that spans over multiple rows and columns. The content of the top-left cell is displayed in the newly created merged cell. The content of the rest of the cells in the merged region is cleared. Once merged, a cell can be easily unmerged back to its compound cells.
Merge Cells
To merge cells you have to create a CellSelection object which determines the region of cells that will be merged. The CellSelection class offers two methods that perform different types of merge: Merge() and MergeAcross(). The former method joins all cells to create one big cell, while the latter combines all cells that appear in the same row, thus, creating a merged cell for every row in the selected region.
Let's take a closer look at how the two methods for merging change the following worksheet.
Example 1 constructs a worksheet that will be used as a starting point in the next few examples.
Example 1: Construct worksheet
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
worksheet.Cells[0, 0].SetValue("A1");
worksheet.Cells[1, 0].SetValue("A2");
worksheet.Cells[0, 1].SetValue("B1");
worksheet.Cells[1, 1].SetValue("B2");
Figure 1 shows the result of the snippet in Example 1.
Figure 1: Worksheet
Example 2 illustrates how to perform a merge operation on the cell region A1:B2.
Example 2: Perform merge operation
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
CellIndex A1Cell = new CellIndex(0, 0);
CellIndex B2Cell = new CellIndex(1, 1);
worksheet.Cells[A1Cell, B2Cell].Merge();
As a result of the merge, the four cells appear as one. The content of the newly created cell is equal to the top left cell of the merged region, i.e. A1. At this point, the values of the rest of the cells in the merged region are cleared, so now cells A2, B1 and B2 have no values.
Figure 2 demonstrates the result of Example 2 executed over the worksheet created in Example 1.
Figure 2: Merge operation result
Let’s see how the MergeAcross() method will change the same region in the original worksheet.
Example 3 illustrates how to perform a merge operation on the cell region A1:B2.
Example 3: Perform merge across
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
CellIndex A1Cell = new CellIndex(0, 0);
CellIndex B2Cell = new CellIndex(1, 1);
worksheet.Cells[A1Cell, B2Cell].MergeAcross();
Note that unlike Merge(), the MergeAcross() method creates a new cell for every row. Each newly created cell contains the value of the leftmost cell that is in the same row and in the merged region. The value of the rest of the merged cells is cleared, so cells B1 and B2 have an empty value.
Figure 3 demonstrates the result of Example 3 executed over the worksheet created in Example 1.
Figure 3: Merge across operation result
If you now try to merge a cell range that intersects with another merged cell range, a third merged cell range will be produced out of the top-left and bottom-right cells of the two ranges.
Example 4 merges across the region A1:B2 and then performs another merge on the cells in the region B2:C3:
Example 4: Intersect cell range with merged cell range
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
CellIndex B2Cell = new CellIndex(1, 1);
CellIndex C3Cell = new CellIndex(2, 2);
worksheet.Cells[B2Cell, C3Cell].Merge();
The result is a merged cell that ranges from A1 to C3.
Figure 4 demonstrates the result of Example 4 executed over the worksheet created in Example 1.
Figure 4: Merge cell range with merged cell range result
Get Merged Cell Ranges
In some scenarios you may want to know if a particular cell is part of a merged region. In others, you may need to retrieve all merged ranges. This section outlines the possible approaches for getting the merged regions.
How to Check if a Cell is Merged?
The Cells class exposes a GetIsMerged() method that allows you to determine if a cell belongs to a merged cell. The method takes a single parameter of type CellIndex which designates a cell you would like to inspect and returns a Boolean value that indicates whether the cell is contained in a merged cell.
Example 5 checks if cell A1 is in a merged region.
Example 5: Check if cell is in merged cell range
CellIndex A1CellIndex = new CellIndex(0, 0);
bool isA1merged = worksheet.Cells.GetIsMerged(A1CellIndex);
How to Get the Containing Merged Cell Range, if the Cell is Merged?
Another way to check if a cell belongs to a merged range is to use the TryGetContainingMergedRange() method of the Cells class. Similarly to the GetIsMerged(), this method returns a Boolean value which indicates if the cell actually is contained in a merged cell. It requires a CellIndex parameter that points the cell to be checked. The method also has one additional out parameter of type CellRange that holds the merged range of the cell, if the cell belongs to such.
Example 6 shows how to use TryGetContainingMergedRange() method.
Example 6: Try get merged cell range
CellIndex A1CellIndex = new CellIndex(0, 0);
CellRange mergedCellRange;
bool canGetContainingMergedCellRange = worksheet.Cells.TryGetContainingMergedRange(A1CellIndex, out mergedCellRange);
How to Get All Merged Cell Ranges Contained in a Given Cell Range?
Use the GetContainingMergedRanges() method of the Cells class to retrieve all merged cells in a specified range. The method takes a single argument of type CellRange that determines the range of the search and returns an enumerable that contains all merged cell ranges.
Example 7 shows how to use GetContainingMergedRanges() method.
Example 7: Get all containing merged ranges in a range
CellIndex A1CellIndex = new CellIndex(0, 0);
CellIndex N33CellIndex = new CellIndex(32, 13);
CellRange A1N33CellRange = new CellRange(A1CellIndex, N33CellIndex);
IEnumerable<CellRange> containingMergedCellRanges = worksheet.Cells.GetContainingMergedRanges(A1N33CellRange);
How to Get All Merged Ranges?
The GetMergedCellRanges() method of the Cells class returns an enumeration holding all merged cell ranges in the worksheet.
Example 8 shows how to get all merged ranges in a worksheet.
Example 8: Get all merged ranges
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
IEnumerable<CellRange> mergedCellRanges = worksheet.Cells.GetMergedCellRanges();
Unmerge Cells
Once a cell is merged, the API offers an easy way to split it back to its composing cells. This is achieved through the Unmerge() method of the CellSelection class. When this method is invoked it unmerges all merged cell ranges that intersect with the selected cell range. For example, consider the worksheet in Figure 5 that has the regions A1:B2 and D4:E5 merged.
Figure 5: Sample worksheet
Example 9 invokes the Unmerge() method for the region B2:D4 of the worksheet from Figure 5, which intersects with the two merged ranges.
Example 9: Unmerge cells
CellIndex B2CellIndex = new CellIndex(1, 1);
CellIndex D4CellIndex = new CellIndex(3, 3);
CellRange B2D4CellSelection = new CellRange(B2CellIndex, D4CellIndex);
worksheet.Cells[B2D4CellSelection].Unmerge();
Figure 6 shows that as a result, the two ranges are unmerged.
Figure 6: Result of unmerge action
