SpreadJS 13
SpreadJS Documentation / Developer's Guide / Features / Manage User Interface / 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:

    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. 

    For example - While generating sales reports, it's a good idea to automatically merge matching states or city names together to make it look neater and cleaner while ensuring that no information loss occurs and the data remains intact in the merged cells.

    The following screenshot depicts how the auto merge operation combines the names of countries, states and cities in a worksheet.

    Auto merg operation in worksheet

    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:

    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 ()
    {
     GC.Spread.Sheets.LicenseKey = "xxx";
    // 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);
    activeSheet.autoMerge(range, GC.Spread.Sheets.AutoMerge.AutoMergeDirection.column, GC.Spread.Sheets.AutoMerge.AutoMergeMode.restricted);
    // Set the column width
    for (var c = 0; c < activeSheet.getColumnCount(); c++)
    activeSheet.setColumnWidth(c, 130.0, GC.Spread.Sheets.SheetArea.viewport);
    });
    </script>