Merge or unmerge Excel cells in C# and VB.NET
Using GemBox.Spreadsheet, you can easily merge several Excel cells into a single cell with C# or VB.NET code.
For this, you need to retrieve a CellRange, a rectangular group of ExcelCell objects adjacent horizontally or vertically and set the CellRange.Merged property to true.
When you merge a cell range, the value and style of the top-left cell will be taken as the value and style of the merged range. The rest of the cell data in that range will be lost. As a result, multiple cells in the range are combined into one larger cell, and if you modify the value or style of any cell within the range, the whole merged range will be modified.
The following example shows how you can merge Excel cells and set the merged range's value and style.
using GemBox.Spreadsheet;
class Program
{
static void Main()
{
// If using the Professional version, put your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
var workbook = new ExcelFile();
var worksheet = workbook.Worksheets.Add("Sheet1");
// Get the cell range.
var range = worksheet.Cells.GetSubrange("B2:E5");
// Merge cells in the current range.
range.Merged = true;
// Set the value of the merged range.
range.Value = "Merged";
// Set the style of the merged range.
range.Style.VerticalAlignment = VerticalAlignmentStyle.Center;
// Set the style of the merged range using a cell within.
worksheet.Cells["C3"].Style.Borders
.SetBorders(MultipleBorders.All, SpreadsheetColor.FromName(ColorName.Red), LineStyle.Double);
workbook.Save("Merged Cells.%OutputFileType%");
}
}Imports GemBox.Spreadsheet
Module Program
Sub Main()
' If using the Professional version, put your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")
Dim workbook As New ExcelFile()
Dim worksheet = workbook.Worksheets.Add("Sheet1")
' Get the cell range.
Dim range = worksheet.Cells.GetSubrange("B2:E5")
' Merge cells in the current range.
range.Merged = True
' Set the value of the merged range.
range.Value = "Merged"
' Set the style of the merged range.
range.Style.VerticalAlignment = VerticalAlignmentStyle.Center
' Set the style of the merged range using a cell within.
worksheet.Cells("C3").Style.Borders _
.SetBorders(MultipleBorders.All, SpreadsheetColor.FromName(ColorName.Red), LineStyle.Double)
workbook.Save("Merged Cells.%OutputFileType%")
End Sub
End ModuleTo split the merged cell range, you need to retrieve a CellRange object again but now set its CellRange.Merged property to false.
When you unmerge a cell range, only the value of the top-left cell will remain, the rest of the cells in that range will be empty. Also, all the split cells will have the same style that's taken from the previous merged range. If you want to remove the style from an ExcelCell object, you can use its Clear method with the ClearOptions.Format argument.
The following example shows how you can find the first merged range in the sheet and unmerge those Excel cells.
using System.Linq;
using GemBox.Spreadsheet;
class Program
{
static void Main()
{
// If using the Professional version, put your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
var workbook = ExcelFile.Load("%InputFileName%");
var worksheet = workbook.Worksheets[0];
// Get the first merged range.
var mergedRange = worksheet.Rows
.SelectMany(row => row.AllocatedCells)
.Select(cell => cell.MergedRange)
.FirstOrDefault(range => range != null);
if (mergedRange != null)
{
// Important, you cannot unmerge the ExcelCell.MergedRange property.
// In other words, the following is not allowed: mergedRange.Merged = false;
// Instead, you need to retrieve the same CellRange from the ExcelWorksheet and then unmerge it.
// This kind of implementation was chosen for performance reasons.
worksheet.Cells.GetSubrange(mergedRange.Name).Merged = false;
worksheet.Cells[mergedRange.StartPosition].Value = "Unmerged";
}
workbook.Save("Unmerged Cells.xlsx");
}
}Imports System.Linq
Imports GemBox.Spreadsheet
Module Program
Sub Main()
' If using the Professional version, put your serial key below.
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")
Dim workbook = ExcelFile.Load("%InputFileName%")
Dim worksheet = workbook.Worksheets(0)
' Get the first merged range.
Dim mergedRange = worksheet.Rows _
.SelectMany(Function(row) row.AllocatedCells) _
.Select(Function(cell) cell.MergedRange) _
.FirstOrDefault(Function(range) range IsNot Nothing)
If mergedRange <> Nothing Then
' Important, you cannot unmerge the ExcelCell.MergedRange property.
' In other words, the following is not allowed: mergedRange.Merged = False
' Instead, you need to retrieve the same CellRange from the ExcelWorksheet and then unmerge it.
' This kind of implementation was chosen for performance reasons.
worksheet.Cells.GetSubrange(mergedRange.Name).Merged = False
worksheet.Cells(mergedRange.StartPosition).Value = "Unmerged"
End If
workbook.Save("Unmerged Cells.xlsx")
End Sub
End Module