Spire.XLS provides a class named Worksheet, it contains a MergedCells property which makes it easy for us to obtain the merged cells in a worksheet. This property will return an array of the merged cell ranges, after that we can do any desired operations such as unmerge and apply formatting on the cells in the ranges. This article explains how to detect all merged cells in a worksheet and unmerge them once using Spire.XLS.
Here we used a template Excel file which has some merged cells:
Detail steps:
Step 1: Instantiate a Workbook object and load the Excel file.
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");
Step 2: Access the first worksheet by passing its sheet index.
Worksheet sheet = workbook.Worksheets[0];
Step 3: Get the merged cell ranges in the first worksheet and put them into a CellRange array.
CellRange[] range = sheet.MergedCells;
Step 4: Traverse through the array and unmerge the merged cells.
foreach (CellRange cell in range)
{
cell.UnMerge();
}
Step 5: Save the file.
workbook.SaveToFile("Output.xlsx",ExcelVersion.Version2010);
After executing the code, we'll get the following output file:
Full codes:
using Spire.Xls;
namespace Detect_Merged_Cells
{
class Program
{
static void Main(string[] args)
{
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");
Worksheet sheet = workbook.Worksheets[0];
CellRange[] range = sheet.MergedCells;
foreach (CellRange cell in range)
{
cell.UnMerge();
}
workbook.SaveToFile("Output.xlsx",ExcelVersion.Version2010);
}
}
}
Imports Spire.Xls
Namespace Detect_Merged_Cells
Class Program
Private Shared Sub Main(args As String())
Dim workbook As New Workbook()
workbook.LoadFromFile("Sample.xlsx")
Dim sheet As Worksheet = workbook.Worksheets(0)
Dim range As CellRange() = sheet.MergedCells
For Each cell As CellRange In range
cell.UnMerge()
Next
workbook.SaveToFile("Output.xlsx",ExcelVersion.Version2010)
End Sub
End Class
End Namespace
