How to detect merged cells in Excel?

8 Dec 20233 minutes to read

The merged cells in an Excel worksheet can be detected through MergedCells of IWorksheet. The following complete code snippet explains this.

C# [Cross-platform]

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  FileStream fileStream = new FileStream("Sample.xlsx", FileMode.Open, FileAccess.Read);
  IWorkbook workbook = application.Workbooks.Open(fileStream);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Create a new worksheet
  IWorksheet mergedCells = workbook.Worksheets.Create("MergedCells");

  //Get the list of merged cells
  for (int pos = 0; pos < worksheet.MergedCells.Length; pos++)
  {
    mergedCells.Range["A" + (pos + 1).ToString()].Text = (pos + 1).ToString() + "th Merged region = " + worksheet.MergedCells[pos].Address;
  }

  //Autofit the used range
  mergedCells.UsedRange.AutofitColumns();

  //Saving the workbook as stream
  FileStream stream = new FileStream("Output.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(stream);
  stream.Dispose();
}

C# [Windows-specific]

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
  IWorksheet worksheet = workbook.Worksheets[0];

  //Create a new worksheet
  IWorksheet mergedCells = workbook.Worksheets.Create("MergedCells");

  //Get the list of merged cells
  for (int pos = 0; pos < worksheet.MergedCells.Length; pos++)
  {
    mergedCells.Range["A" + (pos+1).ToString()].Text = (pos+1).ToString()+"th Merged region = "+ worksheet.MergedCells[pos].Address;
  }

  //Autofit the used range
  mergedCells.UsedRange.AutofitColumns();

  workbook.SaveAs("Output.xlsx");
}

VB.NET [Windows-specific]

Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = ExcelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Open("Sample.xlsx")
  Dim worksheet As IWorksheet = workbook.Worksheets(0)

  'Create a new worksheet
  Dim mergedCells As IWorksheet = workbook.Worksheets.Create("MergedCells")

  'Get the list of merged cells
  For pos As Integer = 0 To worksheet.MergedCells.Length - 1 Step 1
    mergedCells.Range("A" + (pos + 1).ToString()).Text = (pos + 1).ToString() + "th Merged region = " + worksheet.MergedCells(pos).Address
  Next

  'Autofit the used range
  mergedCells.UsedRange.AutofitColumns()

  workbook.SaveAs("Output.xlsx")
End Using