Merging Cells

Merging combines two or more cells to create a new, larger cell. This is a great way to create a label that spans several columns.

<p>Use the <strong>addSpan</strong> method to merge cells in a specified range to a new, larger cell, as shown in the following code.</p> <pre><code class="hljs js language-js"> <span class="hljs-comment">// Merge cells and set label</span> sheet.addSpan(<span class="hljs-number">1</span>, <span class="hljs-number">4</span>, <span class="hljs-number">1</span>, <span class="hljs-number">7</span>); sheet.setValue(<span class="hljs-number">1</span>, <span class="hljs-number">4</span>, <span class="hljs-string">"Goods"</span>); <span class="hljs-comment">// Merge cells across multi rows (3) and columns (4)</span> sheet.addSpan(<span class="hljs-number">20</span>, <span class="hljs-number">1</span>, <span class="hljs-number">3</span>, <span class="hljs-number">4</span>); sheet.getCell(<span class="hljs-number">20</span>, <span class="hljs-number">1</span>).value(<span class="hljs-string">"Demo"</span>).hAlign(<span class="hljs-number">1</span>).vAlign(<span class="hljs-number">1</span>); </code></pre> <p>Use the <strong>removeSpan</strong> method to remove the span that contains the specified cell.</p> <pre><code class="hljs js language-js"> sheet.removeSpan(<span class="hljs-number">20</span>, <span class="hljs-number">1</span>); </code></pre> <p>Change the <strong>allowUserDragMerge</strong> property to allow or not allow merging cells via dragging the mouse. If <strong>allowUserDragMerge</strong> is set to true, there will be an indicator in the selection border of the cell.</p> <pre><code class="hljs js language-js"> <span class="hljs-comment">// default value is false</span> spread.options.allowUserDragMerge = <span class="hljs-literal">true</span>; </code></pre> <p><strong>Note:</strong> Make sure the data you want to display in the merged cell is in the upper-left cell. All data in the other merged cells will be hidden until the span is removed.</p>
window.onload = function() { var spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss')); initSpread(spread); }; function initSpread(spread) { var sheet = spread.getSheet(0); sheet.suspendPaint(); sheet.options.allowCellOverflow = true; sheet.name('Demo'); initSpreadData(sheet); sheet.resumePaint(); document.getElementById('btnMerge').addEventListener('click', function() { var sheet = spread.getActiveSheet(); var sel = sheet.getSelections(); if (sel.length > 0) { sel = getActualCellRange(sel[sel.length - 1], sheet.getRowCount(), sheet.getColumnCount()); sheet.addSpan(sel.row, sel.col, sel.rowCount, sel.colCount); } }); document.getElementById('btnUnMerge').addEventListener('click', function() { var sheet = spread.getActiveSheet(); var sel = sheet.getSelections(); if (sel.length > 0) { sel = getActualCellRange(sel[sel.length - 1], sheet.getRowCount(), sheet.getColumnCount()); sheet.suspendPaint(); for (var i = 0; i < sel.rowCount; i++) { for (var j = 0; j < sel.colCount; j++) { sheet.removeSpan(i + sel.row, j + sel.col); } } sheet.resumePaint(); } }); document.getElementById('allowDragMerge').addEventListener('change', function() { spread.options.allowUserDragMerge = document.getElementById('allowDragMerge').checked; }); } function getActualCellRange(cellRange, rowCount, columnCount) { var spreadNS = GC.Spread.Sheets; if (cellRange.row == -1 && cellRange.col == -1) { return new spreadNS.Range(0, 0, rowCount, columnCount); } else if (cellRange.row == -1) { return new spreadNS.Range(0, cellRange.col, rowCount, cellRange.colCount); } else if (cellRange.col == -1) { return new spreadNS.Range(cellRange.row, 0, cellRange.rowCount, columnCount); } return cellRange; } function initSpreadData(sheet) { var spreadNS = GC.Spread.Sheets; sheet.addSpan(1, 1, 1, 3); sheet.setValue(1, 1, 'Store'); sheet.addSpan(1, 4, 1, 7); sheet.setValue(1, 4, 'Goods'); sheet.addSpan(2, 1, 1, 2); sheet.setValue(2, 1, 'Area'); sheet.addSpan(2, 3, 2, 1); sheet.setValue(2, 3, 'ID'); sheet.addSpan(2, 4, 1, 2); sheet.setValue(2, 4, 'Fruits'); sheet.addSpan(2, 6, 1, 2); sheet.setValue(2, 6, 'Vegetables'); sheet.addSpan(2, 8, 1, 2); sheet.setValue(2, 8, 'Foods'); sheet.addSpan(2, 10, 2, 1); sheet.setValue(2, 10, 'Total'); sheet.setValue(3, 1, 'State'); sheet.setValue(3, 2, 'City'); sheet.setValue(3, 4, 'Grape'); sheet.setValue(3, 5, 'Apple'); sheet.setValue(3, 6, 'Potato'); sheet.setValue(3, 7, 'Tomato'); sheet.setValue(3, 8, 'SandWich'); sheet.setValue(3, 9, 'Hamburger'); sheet.addSpan(4, 1, 7, 1); sheet.addSpan(4, 2, 3, 1); sheet.addSpan(7, 2, 3, 1); sheet.addSpan(10, 2, 1, 2); sheet.setValue(10, 2, 'Sub Total:'); sheet.addSpan(11, 1, 7, 1); sheet.addSpan(11, 2, 3, 1); sheet.addSpan(14, 2, 3, 1); sheet.addSpan(17, 2, 1, 2); sheet.setValue(17, 2, 'Sub Total:'); sheet.addSpan(18, 1, 1, 3); sheet.setValue(18, 1, 'Total:'); sheet.setValue(4, 1, 'NC'); sheet.setValue(4, 2, 'Raleigh'); sheet.setValue(7, 2, 'Charlotte'); sheet.setValue(4, 3, '001'); sheet.setValue(5, 3, '002'); sheet.setValue(6, 3, '003'); sheet.setValue(7, 3, '004'); sheet.setValue(8, 3, '005'); sheet.setValue(9, 3, '006'); sheet.setValue(11, 1, 'PA'); sheet.setValue(11, 2, 'Philadelphia'); sheet.setValue(14, 2, 'Pittsburgh'); sheet.setValue(11, 3, '007'); sheet.setValue(12, 3, '008'); sheet.setValue(13, 3, '009'); sheet.setValue(14, 3, '010'); sheet.setValue(15, 3, '011'); sheet.setValue(16, 3, '012'); sheet.setFormula(10, 4, '=SUM(E5:E10)'); sheet.setFormula(10, 5, '=SUM(F5:F10)'); sheet.setFormula(10, 6, '=SUM(G5:G10)'); sheet.setFormula(10, 7, '=SUM(H5:H10)'); sheet.setFormula(10, 8, '=SUM(I5:I10)'); sheet.setFormula(10, 9, '=SUM(J5:J10)'); sheet.setFormula(17, 4, '=SUM(E12:E17)'); sheet.setFormula(17, 5, '=SUM(F12:F17)'); sheet.setFormula(17, 6, '=SUM(G12:G17)'); sheet.setFormula(17, 7, '=SUM(H12:H17)'); sheet.setFormula(17, 8, '=SUM(I12:I17)'); sheet.setFormula(17, 9, '=SUM(J12:J17)'); for (var i = 0; i < 14; i++) { sheet.setFormula(4 + i, 10, '=SUM(E' + (5 + i).toString() + ':J' + (5 + i).toString() + ')'); } sheet.setFormula(18, 4, '=E11+E18'); sheet.setFormula(18, 5, '=F11+F18'); sheet.setFormula(18, 6, '=G11+G18'); sheet.setFormula(18, 7, '=H11+H18'); sheet.setFormula(18, 8, '=I11+I18'); sheet.setFormula(18, 9, '=J11+J18'); sheet.setFormula(18, 10, '=K11+K18'); sheet.getRange(1, 1, 3, 10).backColor('#D9D9FF'); sheet.getRange(4, 1, 15, 3).backColor('#D9FFD9'); sheet.getRange(1, 1, 3, 10).hAlign(spreadNS.HorizontalAlign.center); sheet.getRange(1, 1, 18, 10).setBorder(new spreadNS.LineBorder('Black', spreadNS.LineStyle.thin), { all: true }); sheet.getRange(4, 4, 3, 6).setBorder(new spreadNS.LineBorder('Black', spreadNS.LineStyle.dotted), { inside: true }); sheet.getRange(7, 4, 3, 6).setBorder(new spreadNS.LineBorder('Black', spreadNS.LineStyle.dotted), { inside: true }); sheet .getRange(11, 4, 3, 6) .setBorder(new spreadNS.LineBorder('Black', spreadNS.LineStyle.dotted), { inside: true }); sheet .getRange(14, 4, 3, 6) .setBorder(new spreadNS.LineBorder('Black', spreadNS.LineStyle.dotted), { inside: true }); fillSampleData(sheet, new spreadNS.Range(4, 4, 6, 6)); fillSampleData(sheet, new spreadNS.Range(11, 4, 6, 6)); } function fillSampleData(sheet, range) { for (var i = 0; i < range.rowCount; i++) { for (var j = 0; j < range.colCount; j++) { sheet.setValue(range.row + i, range.col + j, Math.ceil(Math.random() * 300)); } } }
<!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/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="demo-options"> <label>Select a block of cells and then click one of the buttons below.</label> <div class="option-row"> <input type="button" value="Merge Cells" title="Merge cells in selected range to one cell" id="btnMerge"/> <input type="button" value="Unmerge Cells" title="Unmerge cells in selected range back to all in a single cell" id="btnUnMerge"/> </div> </div> <div class="demo-options" style="padding-top: 16px"> <label>Check the option below to turn drag merge on and off.</label> <div class="option-row"> <label> <input type="checkbox" id="allowDragMerge"> Allow Drag Merge </label> </div> </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; } label { margin-bottom: 6px; } input { padding: 4px 6px; } input[type=button] { margin-top: 6px; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; }