Auto Merge Cells | Document Solutions for Excel, .NET Edition

Auto Merge Cells

In This Topic

DsExcel allows you to add auto-merge information for a range that needs the merging of neighboring cells containing identical text using AutoMerge method of IWorksheet interface. AutoMerge method initiates an automatic merging operation that only takes effect at the moment of exporting the workbook to PDF, HTML, Image, .xlsx, .sjs, or Json with IncludeAutoMergedCells property of XlsxSaveOptions, SjsSaveOptions, SerializationOptions, PdfSaveOptions, ImageSaveOptions, and HtmlSaveOptions classes set to true.

AutoMerge method has the following parameters that help in adding and customizing the auto-merge operation:

Parameter Name	Description
range	The range for the auto-merge.
direction	The direction for the auto-merge. The default value is Column. AutoMergeDirection provides the following auto-merge directions: Row: Applies the auto-merge in row direction. Column: Applies the auto-merge in column direction. ColumnRow: Applies the auto-merge in column direction, preferentially, then in row direction. RowColumn: Applies the auto-merge in row direction, preferentially, then in column direction. None: Cancels the auto-merge.
mode	The mode for the auto-merge. The default value is Free. AutoMergeMode provides the following auto-merge modes: Free: Applies the auto-merge when neighboring cells have the same value. Restricted: Applies the auto-merge when neighboring cells have the same value and the corresponding cells in the previous row or column are merged automatically.
selectionMode	The selection mode for the auto-merge. The default value is Source. AutoMergeSelectionMode provides the following auto-merge selection modes: Source: Selects individual cell when the auto-merge is applied. This will take effect only when IncludeAutoMergedCells is false. Merged: Selects all cells that have the same value when the auto-merge is applied. Note: selectionMode parameter is only applicable to SpreadJS.

Refer to the following example code to add auto-merge information for a range and export the workbook as .xlsx, .sjs, .pdf, and .png files:

C#	Copy Code
// Create a new workbook. var workbook = new Workbook(); // Open existing workbook. workbook.Open("Regional_Product_List.xlsx"); // Get active worksheet. var worksheet = workbook.ActiveSheet; // Add auto-merge range with restricted mode and merged selection mode. worksheet.AutoMerge(worksheet.UsedRange, AutoMergeDirection.Column, AutoMergeMode.Restricted, AutoMergeSelectionMode.Merged); // Set IncludeAutoMergedCells to true to include the automatically merged cells. // Set XlsxSaveOptions to export workbook as XLSX file. XlsxSaveOptions xlsxOptions = new XlsxSaveOptions { IncludeAutoMergedCells = true }; // Set SjsSaveOptions to export workbook as .sjs file. SjsSaveOptions sjsOptions = new SjsSaveOptions { IncludeAutoMergedCells = true }; // Set PdfSaveOptions to export workbook as PDF file. PdfSaveOptions pdfOptions = new PdfSaveOptions { IncludeAutoMergedCells = true }; // Set ImageSaveOptions to export workbook as PNG file. ImageSaveOptions pngOptions = new ImageSaveOptions { IncludeAutoMergedCells = true }; // Save workbook as XLSX file. workbook.Save("AutoMerge.xlsx", xlsxOptions); // Save workbook as .sjs file. workbook.Save("AutoMerge.sjs", sjsOptions); // Save workbook as PDF file. workbook.Save("AutoMerge.pdf", pdfOptions); // Save workbook as PNG file. worksheet.ToImage("AutoMerge.png", pngOptions);

Copy Code

// Create a new workbook.
var workbook = new Workbook();

// Open existing workbook.
workbook.Open("Regional_Product_List.xlsx");

// Get active worksheet.
var worksheet = workbook.ActiveSheet;

// Add auto-merge range with restricted mode and merged selection mode.
worksheet.AutoMerge(worksheet.UsedRange, AutoMergeDirection.Column, AutoMergeMode.Restricted, AutoMergeSelectionMode.Merged);

// Set IncludeAutoMergedCells to true to include the automatically merged cells.
// Set XlsxSaveOptions to export workbook as XLSX file.
XlsxSaveOptions xlsxOptions = new XlsxSaveOptions
{
    IncludeAutoMergedCells = true
};

// Set SjsSaveOptions to export workbook as .sjs file.
SjsSaveOptions sjsOptions = new SjsSaveOptions
{
    IncludeAutoMergedCells = true
};

// Set PdfSaveOptions to export workbook as PDF file.
PdfSaveOptions pdfOptions = new PdfSaveOptions
{
    IncludeAutoMergedCells = true
};

// Set ImageSaveOptions to export workbook as PNG file.
ImageSaveOptions pngOptions = new ImageSaveOptions
{
    IncludeAutoMergedCells = true
};

// Save workbook as XLSX file.
workbook.Save("AutoMerge.xlsx", xlsxOptions);

// Save workbook as .sjs file.
workbook.Save("AutoMerge.sjs", sjsOptions);

// Save workbook as PDF file.
workbook.Save("AutoMerge.pdf", pdfOptions);

// Save workbook as PNG file.
worksheet.ToImage("AutoMerge.png", pngOptions);

Without AutoMerge	With AutoMerge

Note:

The auto-merge operation will throw an exception if the ranges intersect with each other. For example, “A1:C3, B2:D4”.
The auto-merge operation will throw an exception if the range intersects an existing auto-merge range.
The auto-merge information is added for each area if the range has multiple areas. For example, “A1:B2, D3:E4.“
Inserting or deleting rows or columns will affect the auto-merge range. Any other API operations will not affect the auto-merge range.
The auto-merge operation will not change the workbook, so MergeCells and MergeArea properties of IRange interface cannot obtain the results of the auto-merge operation.
The auto-merged cells will be stored as normal span ranges in .xlsx, .sjs, and .json files if IncludeAutoMergedCells is true.