Overview

TableSheet grouping can group a data source by the specified fields.

Besides the basic column, each group could have summary columns, which could use any formula to aggregate the grouped records, such as SUM, AVERAGE, COUNT, COUNTA, MIN or MAX.

Each summary column could have slice columns, which could slice the aggregation result to several columns.

In addition, the basic column and the summary columns can have captions and the width and style can be changed.

The slice columns have width and styles that can be changed.

TableSheet class provides the groupBy method and removeGroupBy method. TableSheet could be grouped by a single field. TableSheet could be grouped by single field, with summary fields and slice fields. TableSheet could be removed the group status.
/*REPLACE_MARKER*/ /*DO NOT DELETE THESE COMMENTS*/ window.onload = function() { var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 0 }); initSpread(spread); }; function initSpread(spread) { spread.suspendPaint(); spread.options.autoFitType = GC.Spread.Sheets.AutoFitType.cellWithHeader; //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 //bind a view to the table sheet ordersTable.fetch().then(function () { var myView = ordersTable.addView("myView", [ { value: "OrderID", caption: "ID", width: 100 }, { value: "Product", width: 150 }, { value: "OrderDate", caption: "Order Date", width: 105 }, { value: "ShipDate", caption: "Ship Date", width: 105 }, { value: "Quantity", width: 100 }, { value: "Amount", width: 100 }, { value: "Office", width: 100 }, { value: "Category", width: 150 } ]); spread.suspendPaint(); sheet.setDataView(myView); groupCallback(); spread.resumePaint(); }); spread.resumePaint(); var groupCallback = function () { sheet.groupBy([ { caption: "Category", field: "Category", width: 120, style: { backColor: "#D0CECE" }, summaryFields: [ { caption: "Σ(Quantity)", formula: "=SUM([Quantity])", slice: {field: "Office", width: 70, style: {backColor: "#D9E1F2"}}, width: 105, style: {backColor: "#D9E1F2"} }, { caption: "μ(Amount)", formula: "=AVERAGE([Amount])", slice: {field: "=YEAR([@OrderDate])", width: 80, style: {backColor: "#E2EFDA", formatter: "$ #,##0.00"}}, width: 100, style: {backColor: "#E2EFDA", formatter: "$ #,##0.00"} } ] } ]); }; var groupButton = document.getElementById("groupButton"); groupButton.addEventListener("click", groupCallback); var removeGroupButton = document.getElementById("removeGroupButton"); removeGroupButton.addEventListener("click", function () { sheet.removeGroupBy(); }); }
<!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"> <!-- 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/@grapecity/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/en/purejs/node_modules/@grapecity/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/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"> <label>Use the below buttons to group or ungroup.</label> </div> <div class="option-row"> <input type="button" value="Group" id="groupButton" /> <input type="button" value="Remove Group" id="removeGroupButton" /> </div> </div> </div> </html>
.sample-tutorial { position: relative; height: 100%; overflow: hidden; } .sample-spreadsheets { width: calc(100% - 210px); height: 100%; overflow: hidden; float: left; } .options-container { float: right; width: 210px; padding: 12px; height: 100%; box-sizing: border-box; background: #fbfbfb; overflow: auto; } .option-row { font-size: 14px; padding: 5px; margin-top: 10px; } label { display: block; margin-bottom: 3px; margin-top: 3px; } input { padding: 4px 6px; } input[type=button] { margin-top: 6px; display: block; width: 100%; text-align: center; } input[type=text] { width: 230px; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; }