Auto Merge In Column Direction

SpreadJS supports Column AutoMerge, allowing neighboring column cells to automatically be merged if the cells contain the same text and they are not in any span range.

You can set auto merge using the autoMerge method. There are two options for setting AutoMerge: 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. The following code will apply auto merge with restricted mode. The following code will apply auto merge with free mode. An option includeAutoMergedCells added to the workbook json serialization, which helps you to save the automatically merged cells to common merged cells in excel, default is false.
window.onload = function () { var spread = new GC.Spread.Sheets.Workbook(_getElementById('ss'), { sheetCount: 1 }); initSpread(spread); }; function initSpread(spread) { var switchAutoMergeMode = _getElementById("switchAutoMergeMode"); switchAutoMergeMode.addEventListener("change", function (event) { var range = new GC.Spread.Sheets.Range(-1, -1, -1, -1); var sheet = spread.getActiveSheet(); sheet.suspendPaint(); //remove old auto merge range sheet.autoMerge(range, GC.Spread.Sheets.AutoMerge.AutoMergeDirection.none); //add new auto merge range sheet.autoMerge(range, GC.Spread.Sheets.AutoMerge.AutoMergeDirection.column, parseInt(event.target.value)); sheet.resumePaint(); }); var dataSource = [{ "Country": "United Kingdom", "State": "England", "City": "Basildon", "Product": "KIND Bars Almond & Coconut Gluten Free", "Detail": "1.4 Ounce Bars 12 Count" }, { "Country": "United Kingdom", "State": "England", "City": "London", "Product": "Kraft Grated Parmesan Cheese", "Detail": "24 oz" }, { "Country": "United Kingdom", "State": "England", "City": "Manchester", "Product": "Kraft Grated Parmesan Cheese", "Detail": "24 oz" }, { "Country": "United Kingdom", "State": "England", "City": "London", "Product": "KIND Bars Almond & Coconut Gluten Free", "Detail": "1.4 Ounce Bars 12 Count" }, { "Country": "United Kingdom", "State": "England", "City": "Newbury", "Product": "Kraft Grated Parmesan Cheese", "Detail": "24 oz" }, { "Country": "United Kingdom", "State": "England", "City": "Kemble", "Product": "Smartfood Popcorn", "Detail": "White Cheddar 9 Ounce" }, { "Country": "United Kingdom", "State": "England", "City": "Headley", "Product": "Smartfood Popcorn", "Detail": "White Cheddar 9 Ounce" }, { "Country": "United Kingdom", "State": "England", "City": "Southampton", "Product": "Planters Deluxe Whole Cashew", "Detail": "18.25 Ounce" }, { "Country": "United Kingdom", "State": "England", "City": "Cheltenham", "Product": "Smartfood Popcorn", "Detail": "White Cheddar 9 Ounce" }, { "Country": "United Kingdom", "State": "England", "City": "Bournemouth", "Product": "Smartfood Popcorn", "Detail": "White Cheddar 9 Ounce" }, { "Country": "United Kingdom", "State": "Northern Ireland", "City": "Helens Bay", "Product": "KIND Bars Almond & Coconut Gluten Free", "Detail": "1.4 Ounce Bars 12 Count" }, { "Country": "United States", "State": "MO", "City": "Parkville", "Product": "Jaybee's Gourmet Nuts Gift Pack (3 Lb)", "Detail": "Premium Quality Nuts Elegant Design Vegetarian Friendly & Kosher" }, { "Country": "United States", "State": "OR", "City": "Astoria", "Product": "Kraft Grated Parmesan Cheese", "Detail": "24 oz" }, { "Country": "United States", "State": "AL", "City": "Cahaba Heights", "Product": "Kraft Real Mayo", "Detail": "30 Ounces" }, { "Country": "United States", "State": "NJ", "City": "Mickleton", "Product": "Kraft Real Mayo", "Detail": "30 Ounces" }, { "Country": "United States", "State": "NJ", "City": "Riverside", "Product": "Smartfood Popcorn", "Detail": "White Cheddar 9 Ounce" }, { "Country": "United States", "State": "NJ", "City": "Clinton", "Product": "Teddy Grahams Crackers", "Detail": "Honey 10-Ounce Boxes 6-Pack" }, { "Country": "United States", "State": "IL", "City": "Peoria", "Product": "KIND Bars Almond & Coconut Gluten Free", "Detail": "1.4 Ounce Bars 12 Count" }, { "Country": "United States", "State": "IL", "City": "Morton", "Product": "Teddy Grahams Crackers", "Detail": "Honey 10-Ounce Boxes 6-Pack" }, { "Country": "United States", "State": "IL", "City": "Flossmoor", "Product": "Teddy Grahams Crackers", "Detail": "Honey 10-Ounce Boxes 6-Pack" }, { "Country": "United States", "State": "IL", "City": "Genoa", "Product": "Kraft Real Mayo", "Detail": "30 Ounces" }, { "Country": "United States", "State": "TN", "City": "Martin", "Product": "KIND Bars Almond & Coconut Gluten Free", "Detail": "1.4 Ounce Bars 12 Count" }, { "Country": "United States", "State": "TN", "City": "Memphis", "Product": "Teddy Grahams Crackers", "Detail": "Honey 10-Ounce Boxes 6-Pack" }, { "Country": "United States", "State": "NY", "City": "New York", "Product": "Jaybee's Gourmet Nuts Gift Pack (3 Lb)", "Detail": "Premium Quality Nuts Elegant Design Vegetarian Friendly & Kosher" }, { "Country": "United States", "State": "NY", "City": "New York", "Product": "Kraft Grated Parmesan Cheese", "Detail": "24 oz" }, { "Country": "United States", "State": "NY", "City": "New York", "Product": "Teddy Grahams Crackers", "Detail": "Honey 10-Ounce Boxes 6-Pack" }, { "Country": "United States", "State": "NY", "City": "Brooklyn", "Product": "Teddy Grahams Crackers", "Detail": "Honey 10-Ounce Boxes 6-Pack" }, { "Country": "United States", "State": "NY", "City": "New Rochelle", "Product": "KIND Bars Almond & Coconut Gluten Free", "Detail": "1.4 Ounce Bars 12 Count" }, { "Country": "United States", "State": "NY", "City": "Staten Island", "Product": "KIND Bars Almond & Coconut Gluten Free", "Detail": "1.4 Ounce Bars 12 Count" }, { "Country": "United States", "State": "TX", "City": "Shavano Park", "Product": "Kraft Real Mayo", "Detail": "30 Ounces" }, { "Country": "United States", "State": "TX", "City": "Sugar Land", "Product": "Kraft Real Mayo", "Detail": "30 Ounces" }, { "Country": "United States", "State": "TX", "City": "Houston", "Product": "Planters Deluxe Whole Cashew", "Detail": "18.25 Ounce" }, { "Country": "United States", "State": "TX", "City": "Houston", "Product": "KIND Bars Almond & Coconut Gluten Free", "Detail": "1.4 Ounce Bars 12 Count" }, { "Country": "United States", "State": "TX", "City": "Lakewood Village", "Product": "Planters Deluxe Whole Cashew", "Detail": "18.25 Ounce" }, { "Country": "United States", "State": "ID", "City": "Eagle", "Product": "Planters Deluxe Whole Cashew", "Detail": "18.25 Ounce" }, { "Country": "United States", "State": "UT", "City": "Salt Lake City", "Product": "Kraft Grated Parmesan Cheese", "Detail": "24 oz" }, { "Country": "United States", "State": "CA", "City": "Chula Vista", "Product": "Jaybee's Gourmet Nuts Gift Pack (3 Lb)", "Detail": "Premium Quality Nuts Elegant Design Vegetarian Friendly & Kosher" }, { "Country": "United States", "State": "CA", "City": "Los Gatos", "Product": "KIND Bars Almond & Coconut Gluten Free", "Detail": "1.4 Ounce Bars 12 Count" }, { "Country": "United States", "State": "CA", "City": "Santa Monica", "Product": "Planters Deluxe Whole Cashew", "Detail": "18.25 Ounce" }, { "Country": "United States", "State": "CA", "City": "Irvine", "Product": "Kraft Grated Parmesan Cheese", "Detail": "24 oz" }, { "Country": "United States", "State": "CA", "City": "San Francisco", "Product": "Teddy Grahams Crackers", "Detail": "Honey 10-Ounce Boxes 6-Pack" }, { "Country": "United States", "State": "FL", "City": "Miami", "Product": "Jaybee's Gourmet Nuts Gift Pack (3 Lb)", "Detail": "Premium Quality Nuts Elegant Design Vegetarian Friendly & Kosher" }, { "Country": "United States", "State": "FL", "City": "Delray Beach", "Product": "Smartfood Popcorn", "Detail": "White Cheddar 9 Ounce" }, { "Country": "United States", "State": "FL", "City": "Fort Lauderdale", "Product": "KIND Bars Almond & Coconut Gluten Free", "Detail": "1.4 Ounce Bars 12 Count" }, { "Country": "United States", "State": "FL", "City": "Amelia Island", "Product": "Kraft Grated Parmesan Cheese", "Detail": "24 oz" }, { "Country": "United States", "State": "FL", "City": "Coral Gables", "Product": "Jaybee's Gourmet Nuts Gift Pack (3 Lb)", "Detail": "Premium Quality Nuts Elegant Design Vegetarian Friendly & Kosher" }, { "Country": "United States", "State": "FL", "City": "Miami", "Product": "Smartfood Popcorn", "Detail": "White Cheddar 9 Ounce" }, { "Country": "United States", "State": "FL", "City": "Parkland", "Product": "Planters Deluxe Whole Cashew", "Detail": "18.25 Ounce" }, { "Country": "United States", "State": "VA", "City": "Reston", "Product": "Kraft Real Mayo", "Detail": "30 Ounces" }, { "Country": "United States", "State": "VA", "City": "Keller", "Product": "Smartfood Popcorn", "Detail": "White Cheddar 9 Ounce" }, { "Country": "United States", "State": "VA", "City": "Keller", "Product": "Kraft Grated Parmesan Cheese", "Detail": "24 oz" }, { "Country": "United States", "State": "AZ", "City": "Phoenix", "Product": "Smartfood Popcorn", "Detail": "White Cheddar 9 Ounce" }, { "Country": "United States", "State": "VT", "City": "Pittsfield", "Product": "Planters Deluxe Whole Cashew", "Detail": "18.25 Ounce" }, { "Country": "United States", "State": "VT", "City": "Pittsfield", "Product": "Jaybee's Gourmet Nuts Gift Pack (3 Lb)", "Detail": "Premium Quality Nuts Elegant Design Vegetarian Friendly & Kosher" }, { "Country": "United States", "State": "GA", "City": "Ball Ground", "Product": "Smartfood Popcorn", "Detail": "White Cheddar 9 Ounce" }, { "Country": "United States", "State": "GA", "City": "Sandy Springs", "Product": "Planters Deluxe Whole Cashew", "Detail": "18.25 Ounce" }, { "Country": "United States", "State": "IA", "City": "Ankeny", "Product": "Kraft Real Mayo", "Detail": "30 Ounces" }, { "Country": "United States", "State": "NC", "City": "Pittsboro", "Product": "Kraft Grated Parmesan Cheese", "Detail": "24 oz" }, { "Country": "United States", "State": "OH", "City": "Beachwood", "Product": "Kraft Grated Parmesan Cheese", "Detail": "24 oz" }, { "Country": "United States", "State": "OH", "City": "Cincinnati", "Product": "Kraft Real Mayo", "Detail": "30 Ounces" }, { "Country": "United States", "State": "NH", "City": "W Lebanon", "Product": "Jaybee's Gourmet Nuts Gift Pack (3 Lb)", "Detail": "Premium Quality Nuts Elegant Design Vegetarian Friendly & Kosher" }, { "Country": "United States", "State": "MD", "City": "Woodsboro", "Product": "Kraft Grated Parmesan Cheese", "Detail": "24 oz" }, { "Country": "United States", "State": "Michigan", "City": "Farmington", "Product": "Jaybee's Gourmet Nuts Gift Pack (3 Lb)", "Detail": "Premium Quality Nuts Elegant Design Vegetarian Friendly & Kosher" }, { "Country": "United States", "State": "HI", "City": "Honolulu", "Product": "Kraft Real Mayo", "Detail": "30 Ounces" }, { "Country": "United States", "State": "CO", "City": "Englewood", "Product": "Planters Deluxe Whole Cashew", "Detail": "18.25 Ounce" }, { "Country": "United States", "State": "KY", "City": "Owensboro", "Product": "KIND Bars Almond & Coconut Gluten Free", "Detail": "1.4 Ounce Bars 12 Count" }, { "Country": "Australia", "State": "Victoria", "City": "Echuca", "Product": "KIND Bars Almond & Coconut Gluten Free", "Detail": "1.4 Ounce Bars 12 Count" }, { "Country": "Australia", "State": "Victoria", "City": "Melbourne", "Product": "Teddy Grahams Crackers", "Detail": "Honey 10-Ounce Boxes 6-Pack" }, { "Country": "Australia", "State": "Queensland", "City": "Burpengary", "Product": "Smartfood Popcorn", "Detail": "White Cheddar 9 Ounce" }, { "Country": "Australia", "State": "Queensland", "City": "Gold Coast", "Product": "Planters Deluxe Whole Cashew", "Detail": "18.25 Ounce" }, { "Country": "Israel", "State": "Tel Aviv", "City": "Tel Aviv", "Product": "Jaybee's Gourmet Nuts Gift Pack (3 Lb)", "Detail": "Premium Quality Nuts Elegant Design Vegetarian Friendly & Kosher" }, { "Country": "France", "State": "Ile-de-France", "City": "Chatou", "Product": "Smartfood Popcorn", "Detail": "White Cheddar 9 Ounce" }, { "Country": "France", "State": "Upper Normandy", "City": "Rouen", "Product": "Jaybee's Gourmet Nuts Gift Pack (3 Lb)", "Detail": "Premium Quality Nuts Elegant Design Vegetarian Friendly & Kosher" }, { "Country": "Netherlands", "State": "Noord-Brabant", "City": "Eindhoven", "Product": "Jaybee's Gourmet Nuts Gift Pack (3 Lb)", "Detail": "Premium Quality Nuts Elegant Design Vegetarian Friendly & Kosher" }, { "Country": "Netherlands", "State": "Noord-Holland", "City": "Badhoevedorp", "Product": "Kraft Real Mayo", "Detail": "30 Ounces" }, { "Country": "Ireland", "State": "Meath", "City": "Julianstown", "Product": "Jaybee's Gourmet Nuts Gift Pack (3 Lb)", "Detail": "Premium Quality Nuts Elegant Design Vegetarian Friendly & Kosher" }, { "Country": "Ireland", "State": "Cork", "City": "Ballynora", "Product": "Smartfood Popcorn", "Detail": "White Cheddar 9 Ounce" }, { "Country": "Ireland", "State": "Dublin", "City": "Kinsaley", "Product": "Jaybee's Gourmet Nuts Gift Pack (3 Lb)", "Detail": "Premium Quality Nuts Elegant Design Vegetarian Friendly & Kosher" }, { "Country": "Ireland", "State": "Dublin", "City": "Rathgar", "Product": "Kraft Real Mayo", "Detail": "30 Ounces" }, { "Country": "Canada", "State": "Ontario", "City": "Ottawa", "Product": "Kraft Grated Parmesan Cheese", "Detail": "24 oz" }, { "Country": "Canada", "State": "Ontario", "City": "Belleville", "Product": "KIND Bars Almond & Coconut Gluten Free", "Detail": "1.4 Ounce Bars 12 Count" }, { "Country": "Canada", "State": "Ontario", "City": "Alliston", "Product": "Kraft Grated Parmesan Cheese", "Detail": "24 oz" }, { "Country": "Canada", "State": "British Columbia", "City": "Maple Ridge District Municipality", "Product": "Smartfood Popcorn", "Detail": "White Cheddar 9 Ounce" }, { "Country": "Canada", "State": "British Columbia", "City": "Comox", "Product": "Jaybee's Gourmet Nuts Gift Pack (3 Lb)", "Detail": "Premium Quality Nuts Elegant Design Vegetarian Friendly & Kosher" }, { "Country": "Canada", "State": "British Columbia", "City": "Vancouver", "Product": "KIND Bars Almond & Coconut Gluten Free", "Detail": "1.4 Ounce Bars 12 Count" }, { "Country": "Canada", "State": "British Columbia", "City": "Vancouver", "Product": "Planters Deluxe Whole Cashew", "Detail": "18.25 Ounce" }, { "Country": "Canada", "State": "British Columbia", "City": "Tsawwassen", "Product": "Teddy Grahams Crackers", "Detail": "Honey 10-Ounce Boxes 6-Pack" }, { "Country": "Canada", "State": "Saskatchewan", "City": "Prince Albert", "Product": "Smartfood Popcorn", "Detail": "White Cheddar 9 Ounce" }, { "Country": "Canada", "State": "Alberta", "City": "Red Deer", "Product": "Smartfood Popcorn", "Detail": "White Cheddar 9 Ounce" }, { "Country": "Canada", "State": "Alberta", "City": "Calgary", "Product": "Planters Deluxe Whole Cashew", "Detail": "18.25 Ounce" }, { "Country": "Canada", "State": "Alberta", "City": "Calgary", "Product": "Kraft Grated Parmesan Cheese", "Detail": "24 oz" }, { "Country": "Canada", "State": "Alberta", "City": "Okotoks", "Product": "Smartfood Popcorn", "Detail": "White Cheddar 9 Ounce" }, { "Country": "India", "State": "Andhra Pradesh", "City": "Hyderabad", "Product": "Teddy Grahams Crackers", "Detail": "Honey 10-Ounce Boxes 6-Pack" }, { "Country": "South Africa", "State": "Gauteng", "City": "Roodepoort", "Product": "Jaybee's Gourmet Nuts Gift Pack (3 Lb)", "Detail": "Premium Quality Nuts Elegant Design Vegetarian Friendly & Kosher" }, { "Country": "Finland", "State": "Ita-Suomen Laani", "City": "Kuopio", "Product": "Planters Deluxe Whole Cashew", "Detail": "18.25 Ounce" }, { "Country": "Switzerland", "State": "Geneve", "City": "Vesenaz", "Product": "KIND Bars Almond & Coconut Gluten Free", "Detail": "1.4 Ounce Bars 12 Count" }, { "Country": "Switzerland", "State": "Vaud", "City": "Lausanne", "Product": "Smartfood Popcorn", "Detail": "White Cheddar 9 Ounce" }, { "Country": "Switzerland", "State": "Vaud", "City": "Morges", "Product": "Kraft Real Mayo", "Detail": "30 Ounces" }, { "Country": "Denmark", "State": "Frederiksborg", "City": "Helsingor", "Product": "Planters Deluxe Whole Cashew", "Detail": "18.25 Ounce" }, { "Country": "Denmark", "State": "Kobenhavn", "City": "Kobenhavn", "Product": "Kraft Grated Parmesan Cheese", "Detail": "24 oz" }, { "Country": "Denmark", "State": "Storstrom", "City": "Nakskov", "Product": "Kraft Grated Parmesan Cheese", "Detail": "24 oz" }, { "Country": "Belgium", "State": "Brussels (Bruxelles)", "City": "Brussels", "Product": "Jaybee's Gourmet Nuts Gift Pack (3 Lb)", "Detail": "Premium Quality Nuts Elegant Design Vegetarian Friendly & Kosher" }, { "Country": "Sweden", "State": "Stockholm", "City": "Saltsjobaden", "Product": "KIND Bars Almond & Coconut Gluten Free", "Detail": "1.4 Ounce Bars 12 Count" }, { "Country": "Norway", "State": "Rogaland", "City": "Stavanger", "Product": "Smartfood Popcorn", "Detail": "White Cheddar 9 Ounce" }, { "Country": "Norway", "State": "Oslo", "City": "Oslo", "Product": "Planters Deluxe Whole Cashew", "Detail": "18.25 Ounce" }, { "Country": "Luxembourg", "State": "Luxembourg", "City": "Gasperich", "Product": "Planters Deluxe Whole Cashew", "Detail": "18.25 Ounce" }, { "Country": "Italy", "State": "Lombardy", "City": "Milan", "Product": "Smartfood Popcorn", "Detail": "White Cheddar 9 Ounce" }, { "Country": "Germany", "State": "Nordrhein-Westfalen", "City": "Telgte", "Product": "Jaybee's Gourmet Nuts Gift Pack (3 Lb)", "Detail": "Premium Quality Nuts Elegant Design Vegetarian Friendly & Kosher" }, { "Country": "Moldova", "State": "Chisinau", "City": "Bubuieci", "Product": "Smartfood Popcorn", "Detail": "White Cheddar 9 Ounce" }, { "Country": "Spain", "State": "Murcia", "City": "La Alberca", "Product": "Kraft Grated Parmesan Cheese", "Detail": "24 oz" }, { "Country": "United Arab Emirates", "State": "Dubayy", "City": "Jumeira", "Product": "KIND Bars Almond & Coconut Gluten Free", "Detail": "1.4 Ounce Bars 12 Count" }, { "Country": "Bahrain", "State": "Al Manamah", "City": "Al 'Adliyah", "Product": "Kraft Grated Parmesan Cheese", "Detail": "24 oz" }]; spread.suspendPaint(); var sheet = spread.getActiveSheet(); //bind data source sheet.setRowHeight(0, 30, 1); sheet.autoGenerateColumns = false; sheet.setDataSource(dataSource); var colInfos = [ { name: 'Country', size: 180 }, { name: 'State', size: 120 }, { name: 'City', size: 120 }, { name: 'Product', size: 280 }, { name: 'Detail', size: '*' } ]; sheet.bindColumns(colInfos); //apply auto merge var range = new GC.Spread.Sheets.Range(-1, -1, -1, -1); sheet.autoMerge(range, GC.Spread.Sheets.AutoMerge.AutoMergeDirection.column, GC.Spread.Sheets.AutoMerge.AutoMergeMode.restricted); spread.resumePaint(); } function _getElementById(id) { return document.getElementById(id); }
<!doctype html> <html style="height:100%;font-size:14px;"> <head> <meta charset="utf-8" /> <meta name="viewport" content="width=device-width, initial-scale=1.0" /> <link rel="stylesheet" type="text/css" href="$DEMOROOT$/en/purejs/node_modules/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <script src="$DEMOROOT$/en/purejs/node_modules/@grapecity/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/spread/source/data/data.js" type="text/javascript"></script> <script src="$DEMOROOT$/spread/source/js/license.js" type="text/javascript"></script> <script src="app.js" type="text/javascript"></script> <link rel="stylesheet" type="text/css" href="styles.css"> </head> <body> <div class="sample-tutorial"> <div id="ss" class="sample-spreadsheets"></div> <div class="options-container"> <div class="option-row"> Changes the auto merge mode. <select id="switchAutoMergeMode"> <option value="0">Free</option> <option value="1" selected="selected">Restricted</option> </select> </div> </div> </div> </body> </html>
.sample-tutorial { position: relative; height: 100%; overflow: hidden; } .sample-spreadsheets { width: calc(100% - 280px); height: 100%; overflow: hidden; float: left; } .options-container { float: right; width: 280px; padding: 12px; height: 100%; box-sizing: border-box; background: #fbfbfb; overflow: auto; } .option-row { font-size: 14px; padding: 5px; margin-top: 10px; } #switchAutoMergeMode { margin: 10px 0px; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; }