SpreadJS 14
Features / Cells / Auto Merge Cells
In This Topic
    Auto Merge Cells
    In This Topic

    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:

    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:

    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:

    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 auto-merge feature and by using the new SelectionMode they can get the same selection effect as spans.

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

    Auto merge operation in worksheet

    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:

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

    • The auto merge range is affected when a user executes the following operations in the spreadsheet
      • While adding a row or column
      • While removing a row or column
      • While setting row count or column count
    • 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.

    JavaScript
    Copy Code
    <script>
        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"
            }
        ]
    </script>
    <script>
        $(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);
        });
    </script>