private void MergeRange(MSOpenXML.Worksheet worksheet, string cell1Name, string cell2Name)
{
// Open the document for editing.
if (worksheet == null || string.IsNullOrEmpty(cell1Name) || string.IsNullOrEmpty(cell2Name))
{
return;
}
if (cell1Name == cell2Name)
{
return;
}
// Verify if the specified cells exist, and if they do not exist, create them.
CreateCellIfNotExist(worksheet, cell1Name);
CreateCellIfNotExist(worksheet, cell2Name);
MSOpenXML.MergeCells mergeCells;
if (worksheet.Elements <MSOpenXML.MergeCells>().Count() > 0)
{
mergeCells = worksheet.Elements <MSOpenXML.MergeCells>().First();
}
else
{
mergeCells = new MSOpenXML.MergeCells();
// Insert a MergeCells object into the specified position.
if (worksheet.Elements <MSOpenXML.CustomSheetView>().Count() > 0)
{
worksheet.InsertAfter(mergeCells, worksheet.Elements <MSOpenXML.CustomSheetView>().First());
}
else if (worksheet.Elements <MSOpenXML.DataConsolidate>().Count() > 0)
{
worksheet.InsertAfter(mergeCells, worksheet.Elements <MSOpenXML.DataConsolidate>().First());
}
else if (worksheet.Elements <MSOpenXML.SortState>().Count() > 0)
{
worksheet.InsertAfter(mergeCells, worksheet.Elements <MSOpenXML.SortState>().First());
}
else if (worksheet.Elements <MSOpenXML.AutoFilter>().Count() > 0)
{
worksheet.InsertAfter(mergeCells, worksheet.Elements <MSOpenXML.AutoFilter>().First());
}
else if (worksheet.Elements <MSOpenXML.Scenarios>().Count() > 0)
{
worksheet.InsertAfter(mergeCells, worksheet.Elements <MSOpenXML.Scenarios>().First());
}
else if (worksheet.Elements <MSOpenXML.ProtectedRanges>().Count() > 0)
{
worksheet.InsertAfter(mergeCells, worksheet.Elements <MSOpenXML.ProtectedRanges>().First());
}
else if (worksheet.Elements <MSOpenXML.SheetProtection>().Count() > 0)
{
worksheet.InsertAfter(mergeCells, worksheet.Elements <MSOpenXML.SheetProtection>().First());
}
else if (worksheet.Elements <MSOpenXML.SheetCalculationProperties>().Count() > 0)
{
worksheet.InsertAfter(mergeCells, worksheet.Elements <MSOpenXML.SheetCalculationProperties>().First());
}
else
{
worksheet.InsertAfter(mergeCells, worksheet.Elements <MSOpenXML.SheetData>().First());
}
}
// Create the merged cell and append it to the MergeCells collection.
MSOpenXML.MergeCell mergeCell = new MSOpenXML.MergeCell()
{
Reference = cell1Name + ":" + cell2Name
};
mergeCells.Append(mergeCell);
}