Auto Merge Cells

SpreadJS supports the auto-merge feature allowing users to automatically merge the neighboring cells containing duplicate text. During the auto-merge operation, no data is lost in the process.

Benefits of Using Auto Merge

The auto-merge feature helps users to:

• Create clear visualization of groups within large data sets.

• Merge identical cells without losing any data.

• Eliminate redundancy (repetition of text) to remove unnecessary clutter in the worksheet.

• Save a considerable amount of time and effort without manually scanning the duplicate cells to merge them one by one.

How Auto Merge Works

The auto-merge feature automatically merges the contiguous cells with identical text and adjusts the text position of the merged cells.

This feature works upon the condition that the cells you want to merge shouldn't belong to any span range. While working with SpreadJS, users can use the autoMerge method to apply the auto merge feature to specified ranges, including range, row, column and the entire worksheet. The directions supported by the AutoMergeDirection enumeration include: row direction, column direction and both row and column direction.

SpreadJS provides support for the following two modes available in the AutoMergeMode enumeration:

• Free Mode- In this mode, cells with identical values are automatically merged with the neighboring cells.

• Restricted Mode - In this mode, cells with identical values are merged with the neighboring cells only if the corresponding cells in the previous row or column are merged in a similar way.

To support auto-merge with the selection feature of addSpan in SpreadJS, use the parameter SelectionMode for the autoMerge method in Worksheet class.

You can set the SelectionMode parameter to either source or merged . These two options are described below:

• source: In this mode option, you can select individual cells in the automatically merged area.

• merged: In this mode option, you can select the entire merged cell in the automatically merged area.

Usage Scenario

While executing data analysis on a worksheet, users may want to combine several cells containing identical text for enhanced data presentation and better structure.

Consider a scenario where we are analyzing the sales of products in various cities and states across different countries in the world using the worksheet. Users can merge the cells based on the values by using the auto-merge feature and by using the new SelectionMode they can get the same selection effect as spans.

The following example shows a variety of products sold in different cities and states across countries. The cells are merged in case of the same values using the SelectionMode property, where the SelectionMode property for the "Country" column is set to Merged, and the "State" column is set to Source.

Auto Merge Cells vs Spanning and Merging Cells

The basic differences between executing the "Auto Merge Cells" operation and "Spanning and Merging Cells" in a worksheet are:

• While using the Auto Merge feature in SpreadJS, the cells remain navigable and editable even after the merge operation has been executed. On the contrary, while carrying out the spanning and merging of cells, the spanned and merged cells are automatically adjusted when a cell value is changed.

• When a user scrolls through the worksheet, the text remains visible(sticky) in the merged span. Unlike regular merge spans, the auto-merged cells have the same value.

Note: The following limitations must be kept in mind while using the auto-merge feature:

• While adding a row or column

• While removing a row or column

• While setting row count or column count

• The auto-merge range is affected when a user executes the following operations in the spreadsheet

• While printing or saving to a PDF file (using the savePDF method), all the merged cells will automatically be treated as normal span ranges.

• The copy or move operation will not affect the auto-merge range, and will not treat the automatically merged cells as normal span ranges.

Using Code

The following code snippet merges the cells containing identical country names, states and cities using the autoMerge method.

var data = [
    {
        "Country": "Canada",
        "State": "Ontario",
        "City": "Ottawa",
        "Product": "Kraft Grated Parmesan Cheese"
    },
    {
        "Country": "Canada",
        "State": "Ontario",
        "City": "Belleville",
        "Product": "KIND Bars Almond & Coconut Gluten Free"
    },
    {
        "Country": "Canada",
        "State": "Ontario",
        "City": "Alliston",
        "Product": "Kraft Grated Parmesan Cheese"
    },
    {
        "Country": "Canada",
        "State": "Saskatchewan",
        "City": "Prince Albert",
        "Product": "Smartfood Popcorn"
    },
    {
        "Country": "Canada",
        "State": "Alberta",
        "City": "Red Deer",
        "Product": "Smartfood Popcorn"
    },
    {
        "Country": "Canada",
        "State": "Alberta",
        "City": "Calgary",
        "Product": "Planters Deluxe Whole Cashew"
    },
    {
        "Country": "Canada",
        "State": "Alberta",
        "City": "Calgary",
        "Product": "Kraft Grated Parmesan Cheese"
    },
    {
        "Country": "Canada",
        "State": "Alberta",
        "City": "Okotoks",
        "Product": "Smartfood Popcorn"
    },
    {
        "Country": "India",
        "State": "Andhra Pradesh",
        "City": "Hyderabad",
        "Product": "Teddy Grahams Crackers"
    },
    {
        "Country": "South Africa",
        "State": "Gauteng",
        "City": "Roodepoort",
        "Product": "Jaybee's Gourmet Nuts Gift Pack (3 Lb)"
    },
    {
        "Country": "Finland",
        "State": "Ita-Suomen Laani",
        "City": "Kuopio",
        "Product": "Planters Deluxe Whole Cashew"
    },
    {
        "Country": "Switzerland",
        "State": "Geneve",
        "City": "Vesenaz",
        "Product": "KIND Bars Almond & Coconut Gluten Free"
    },
    {
        "Country": "Switzerland",
        "State": "Vaud",
        "City": "Lausanne",
        "Product": "Smartfood Popcorn"
    },
    {
        "Country": "Switzerland",
        "State": "Vaud",
        "City": "Morges",
        "Product": "Kraft Real Mayo"
    },
    {
        "Country": "Denmark",
        "State": "Frederiksborg",
        "City": "Helsingor",
        "Product": "Planters Deluxe Whole Cashew"
    },
    {
        "Country": "Denmark",
        "State": "Kobenhavn",
        "City": "Kobenhavn",
        "Product": "Kraft Grated Parmesan Cheese"
    },
    {
        "Country": "Denmark",
        "State": "Storstrom",
        "City": "Nakskov",
        "Product": "Kraft Grated Parmesan Cheese"
    }
]
$(document).ready(function () {
    // Initializing Spread
    var spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss'), { sheetCount: 1 });

    // Get the activesheet
    var activeSheet = spread.getActiveSheet();

    // Bind data source
    activeSheet.setRowHeight(0, 30, 1);
    activeSheet.autoGenerateColumns = true;
    activeSheet.setDataSource(data);

    // Merging complete sheet cells when AutoMergeDirection is set to Column & AutoMergeMode is set to restricted mode
    var range = new GC.Spread.Sheets.Range(-1, -1, -1, -1);

    // Auto merge SelectionMode is merged
    activeSheet.autoMerge(range, GC.Spread.Sheets.AutoMerge.AutoMergeDirection.column, GC.Spread.Sheets.AutoMerge.AutoMergeMode.restricted, GC.Spread.Sheets.SheetArea.viewport, GC.Spread.Sheets.AutoMerge.SelectionMode.merged);

    // Set the column width
    for (var c = 0; c < activeSheet.getColumnCount(); c++)
        activeSheet.setColumnWidth(c, 130.0, GC.Spread.Sheets.SheetArea.viewport);
});