Group Layout

TableSheet supports using a group layout, enabling you to define the header aggregations and footer subtotals.

Layout Mode TableSheet supports three different Group Layouts, which can be set by groupLayout: Group Layout Mode: Tabular Outline Condensed Group Fields Users can set up multiple group fields at the same time, or for each field. Spacing The customer could show the spacing to separate the root group level items with the spacing. Spacing is only useful in Outline and Condensed modes. Multiple Group Fields Multiple group headings can be added, with different levels of indentation for the content of the grouped items. Summary Fields Options Users can set the content of each field.
/*REPLACE_MARKER*/ /*DO NOT DELETE THESE COMMENTS*/ window.onload = function () { var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 0 }); initSpread(spread); initSplitView(spread); }; const OutlineAndCondensedGroup = [ { field: "Office", caption: "OFFICE", style: { foreColor: '#000000', backColor: '#ededed', fontSize: "20px", cellPadding: "16 0 0 0" }, headerStyle: { font: "10px Arial", foreColor: "#666", cellPadding: "2 0 0 0" }, summaryFields: [ { caption: "COUNT", formula: "=COUNTA([OrderID])", style: { foreColor: "rgb(185, 122, 87)", fontSize: "20px", cellPadding: "16 0 0 0" }, headerStyle: { font: "10px Arial", foreColor: "#666", cellPadding: "2 0 0 0" } }, { caption: "TOTAL AMOUNT", formula: '=SUM([Amount])', relateTo: "Amount", style: { formatter: "$ #,##0.00", cellPadding: "30 0 0 0" }, headerStyle: { cellPadding: "8 0 0 0" } }, { caption: "Σ(Quantity)", formula: "=SUM([Quantity])", style: { cellPadding: "30 0 0 0" }, headerStyle: { cellPadding: "8 0 0 0" } }, ] }, { field: "OrderDate", spacing: { row: 20 }, caption: "ORDER DATE", style: { foreColor: '#000000', backColor: '#f7f7f7', fontSize: "20px", cellPadding: "16 0 0 0" }, headerStyle: { font: "10px Arial", foreColor: "#666", cellPadding: "2 0 0 0" }, summaryFields: [ { caption: "SUBTOTAL AMOUNT", formula: '=SUM([Amount])', position: "footer", relateTo: "Amount", style: { formatter: "$ #,##0.00", cellPadding: "30 0 0 0" }, headerStyle: { cellPadding: "8 0 0 0" } }, { caption: "SUBTOTAL COUNT", formula: "=SUM([Quantity])", position: "footer", style: { cellPadding: "30 0 0 0" }, headerStyle: { cellPadding: "8 0 0 0" } } ] } ] const TabularGroup = [ { field: "Office", caption: "OFFICE", style: { foreColor: '#000000', backColor: '#ededed', fontSize: "20px" }, headerStyle: { font: "10px Arial", foreColor: "#666" }, summaryFields: [ { caption: "COUNT", formula: "=COUNTA([OrderID])", style: { foreColor: "rgb(185, 122, 87)", fontSize: "20px" }, headerStyle: { font: "10px Arial", foreColor: "#666" } }, { caption: "TOTAL AMOUNT", formula: '=SUM([Amount])', relateTo: "Amount", style: { formatter: "$ #,##0.00" }, }, { caption: "Σ(Quantity)", formula: "=SUM([Quantity])", }, ] }, { field: "OrderDate", spacing: { row: 20 }, caption: "ORDER DATE", style: { foreColor: '#000000', backColor: '#f7f7f7', fontSize: "20px" }, headerStyle: { font: "10px Arial", foreColor: "#666" }, summaryFields: [ { caption: "SUBTOTAL AMOUNT", formula: '=SUM([Amount])', position: "footer", relateTo: "Amount", style: { formatter: "$ #,##0.00" }, }, { caption: "SUBTOTAL COUNT", formula: "=SUM([Quantity])", position: "footer", } ] } ] function initSpread(spread) { spread.suspendPaint(); spread.options.autoFitType = GC.Spread.Sheets.AutoFitType.cellWithHeader; spread.options.scrollByPixel = true; spread.options.scrollPixel = 5; //init a data manager var dataManager = spread.dataManager(); var ordersTable = dataManager.addTable("ordersTable", { data: orderDataSource, schema: { type: "csv", columns: { OrderDate: { dataType: "date" }, ShipDate: { dataType: "date" }, Quantity: { dataType: "number" }, Amount: { dataType: "number" } } } }); //init a table sheet var sheet = spread.addSheetTab(0, "TableSheet1", GC.Spread.Sheets.SheetType.tableSheet); sheet.setDefaultRowHeight(40, GC.Spread.Sheets.SheetArea.colHeader); sheet.options.allowAddNew = false; //hide new row sheet.options.groupLayout.mode = GC.Spread.Sheets.TableSheet.GroupLayoutMode.outline; // apply the outline group layout mode sheet.options.showRowNumber = true; sheet.applyTableTheme(GC.Spread.Sheets.Tables.TableThemes.light1); sheet.options.alternatingRowOptions = { step: [1, 1], style: new GC.Spread.Sheets.Style("#ffffff") }; //bind a view to the table sheet ordersTable.fetch().then(function () { var myView = ordersTable.addView("myView", [ { value: "OrderID", width: 180, style: { formatter: "O00000" } }, { value: "Product", width: 200 }, { value: "Quantity", width: 150 }, { value: "OrderDate", width: 100, visible: false }, { value: "ShipDate", width: 100, visible: false }, { value: "Office", width: 100 }, { value: "Category", width: 150 }, { value: "Amount", width: 150 }, ]); sheet.setDataView(myView); //init a table sheet panel var host = document.getElementById("panel"); var panel = new GC.Spread.Sheets.TableSheet.TableSheetPanel("myPanel", sheet, host); sheet.groupBy(OutlineAndCondensedGroup); }); spread.resumePaint(); var groupLayoutMode = document.getElementById("tableSheetGroupLayoutMode"); var groupLayoutPosition = document.getElementById("tableSheetGroupLayoutPosition"); const resetGroupLayout = function () { var mode = +groupLayoutMode.value; sheet.options.groupLayout = { mode: mode, position: groupLayoutPosition.value }; if (mode === 0) { sheet.groupBy(TabularGroup); } else { sheet.groupBy(OutlineAndCondensedGroup); } } groupLayoutMode.addEventListener("change", function () { resetGroupLayout(); }); groupLayoutPosition.addEventListener("change", function () { resetGroupLayout(); }); } function initSplitView(spread) { var host = document.getElementById("split-view"); var content = host.getElementsByClassName("split-content")[0]; var panel = host.getElementsByClassName("split-panel")[0]; new SplitView({ host: host, content: content, panel: panel, refreshContent: function () { spread.refresh(); }, defaultPanelWidth: 320, allowResize: false }); } function openTab(event, tabId) { var i, tabcontent, tablinks; tabcontent = document.getElementsByClassName("split-tab-content"); for (i = 0; i < tabcontent.length; i++) { tabcontent[i].style.display = "none"; } tablinks = document.getElementsByClassName("split-tab-link"); for (i = 0; i < tablinks.length; i++) { tablinks[i].className = tablinks[i].className.replace(" active", ""); } document.getElementById(tabId).style.display = "block"; event.currentTarget.className += " active"; } document.addEventListener("DOMContentLoaded", function () { document.getElementsByClassName("split-tab-link")[0].click(); });
<!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/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <link rel="stylesheet" type="text/css" href="$DEMOROOT$/spread/source/splitView/splitView.css"> <!-- Promise Polyfill for IE, https://www.npmjs.com/package/promise-polyfill --> <script src="https://cdn.jsdelivr.net/npm/promise-polyfill@8/dist/polyfill.min.js"></script> <script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets-tablesheet/dist/gc.spread.sheets.tablesheet.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/spread/source/data/orderDataSource.js" type="text/javascript"></script> <script src="$DEMOROOT$/spread/source/splitView/splitView.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 id="split-view" class="sample-tutorial"> <div id="ss" class="sample-spreadsheets split-content"></div> <div class="split-panel"> <div class="split-tabs"> <button class="split-tab-link active" onclick="openTab(event, 'panel')">TableSheet Panel</button> <button class="split-tab-link" onclick="openTab(event, 'settings')">Setting</button> </div> <div class="split-panels"> <div id="panel" class="split-tab-content container"></div> <div id="settings" class="split-tab-content options-container"> <fieldset> <legend>Group Layout Mode</legend> <select id="tableSheetGroupLayoutMode"> <option value="0">Tabular</option> <option value="1" selected>Outline</option> <option value="2">Condensed</option> </select> </fieldset> <fieldset> <legend>Default Summary Position</legend> <select id="tableSheetGroupLayoutPosition"> <option value="header" selected>Header</option> <option value="footer">Footer</option> </select> </fieldset> </div> </div> </div> </div> </html>
.sample-tutorial { position: relative; height: 100%; overflow: hidden; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; margin: 0; } .sample-spreadsheets { width: calc(100% - 303px); height: 100%; overflow: hidden; float: left; } .container { width: 300px; height: 100%; float: left; border: 1px solid lightgrey; } fieldset select { margin-top: 6px; padding: 4px 6px; width: 125px; } fieldset input { margin-top: 6px; padding: 4px 6px; width: 110px; } .split-panels { height: 535px; } .tab-container { width: 50%; margin: 0 auto; } .split-tabs { position: absolute; top: 12px; right: 5px; display: flex; justify-content: flex-start; flex-direction: row; align-items: center; } .split-tab-link { height: 30px; background-color: #f1f1f1; border: none; cursor: pointer; transition: background-color 0.3s; margin-bottom: 10px; } .split-tab-link:hover { background-color: #ddd; } .split-tab-link.active { background-color: #ccc; } .split-tab-content { display: none; } .split-tab-content.active { display: block; }