按公式或相关字段分组

集算表不仅支持视图字段,还支持公式相关字段来作为分组或分片列。

你可以为分组选项设置一个公式或一个相关字段。 切片列可以以同样的方式支持公式和相关字段。 使用DATEPART公式可以帮助对日期类型的字段进行分组,其语法如下。 参数 描述 date_value (必填)日期值。 format_string (必填)日期的格式字符串。 [week_num_type] (可选) 与WEEKNUM的第二个参数相同。 DATEPART公式格式字符串的示例 格式 结果 描述 yyyyQ 20214 数字:1位数(季度编号/名称。) yyyyQQ 202104 数字:2位数+零点占位 yyyyQQQ 2021Q4 缩略 yyyy QQQQ 2021 4th quarter 全描述 YYYY w 2021 8 数字:最小数字(年度周(数字)。当与年份一起使用时,在年份字段中使用'Y')。 YYYY ww 2021 08 数字:2位数,如果需要的话,可以用零占位。 MM-yyyy 09-2021 在单元格格式化中提供部分日期格式化 使用CALCULATE和REMOVEFILTERS公式可以帮助扩大组的范围,其语法如下。 参数 描述 formula_string (必填) 该公式将通过expand_context的上下文进行计算。 expand_context (必填) expand_context来自REMOVEFILTERS。 参数 描述 [group_field_string] (可选)组字段表示被扩展到的范围。 CALCULATE只能在summaryFields选项的公式选项中使用,而REMOVEFILTERS只能用于REMOVEFILTERS和CALCULATE的组合。
/*REPLACE_MARKER*/ /*DO NOT DELETE THESE COMMENTS*/ var baseApiUrl = getBaseApiUrl(); function getApiUrl(tableName) { return baseApiUrl + "/" + tableName; } window.onload = function () { var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 0 }); initSpread(spread); }; function initSpread(spread) { spread.suspendPaint(); spread.clearSheets(); spread.options.autoFitType = GC.Spread.Sheets.AutoFitType.cellWithHeader; //init a data manager var dataManager = spread.dataManager(); var ordersTable = dataManager.addTable("ordersTable", { remote: { read: { url: getApiUrl("Order") } }, schema: { columns: { orderDate: { dataType: "date" }, requiredDate: { dataType: "date" }, shippedDate: { dataType: "date" } } } }); var customersTable = dataManager.addTable("customersTable", { remote: { read: { url: getApiUrl("Customer") } } }); dataManager.addRelationship(ordersTable, "CustomerId", "Customers", customersTable, "Id", "Orders"); var view = ordersTable.addView("orderView", [ { value: "Id", width: 65 }, { value: "OrderDate", width: 100 }, { value: "RequiredDate", width: 120 }, { value: "ShippedDate", width: 110 }, { value: "ShipVia", width: 110 }, { value: "Freight", width: 80 }, { value: "ShipName", width: 200 }, ]); //init a table sheet var sheet = spread.addSheetTab(0, "MyTableSheet", GC.Spread.Sheets.SheetType.tableSheet); view.fetch().then(function () { sheet.setDataView(view); sheet.groupOutlinePosition(GC.Spread.Sheets.TableSheet.GroupOutlinePosition.none); sheet.groupBy([ { caption: "Company Name", field: "Customers.CompanyName", width: 160, }, { caption: "Year Quarter", field: '=DATEPART([@OrderDate],"yyyy-QQQ")', width: 160, }, { caption: "Ship Via", field: 'ShipVia', style: { formatter: '=SWITCH(@,1,"Speedy Express", 2,"United Package", 3, "Federal Shipping","")' }, width: 135, summaryFields: [ { caption: "Freight", width: 100, formula: "=SUM([Freight])", }, { caption: "Company Ratio", width: 130, style: { formatter: "0.00%" }, formula: '=SUM([Freight]) / CALCULATE( SUM([Freight]), REMOVEFILTERS("ShipVia","=DATEPART([@OrderDate],""yyyy-QQQ"")"))', // ratio of sum of freight under freight level to sum of freight under year quarter }, { caption: "% Grand Total", width: 120, style: { formatter: "0.00%" }, formula: '=SUM([Freight]) / CALCULATE( SUM([Freight]), REMOVEFILTERS())', // ratio of sum of freight under freight level to sum of freight under all records } ] }, { caption: "Freight Level", width: 135, field: '=IFS([@Freight]<30,0,AND([@Freight]>=30,[@Freight]<60),1,[@Freight]>60,2)', style: { formatter: '=SWITCH(@,0,"Low",1,"Medium",2,"High","no match")' }, } ]); }); spread.resumePaint(); } function getBaseApiUrl() { return window.location.href.match(/http.+spreadjs\/SpreadJSTutorial\//)[0] + 'server/api'; }
<!doctype html> <html style="height:100%;font-size:14px;"> <head> <meta name="spreadjs culture" content="zh-cn" /> <meta charset="utf-8" /> <meta name="viewport" content="width=device-width, initial-scale=1.0" /> <link rel="stylesheet" type="text/css" href="$DEMOROOT$/zh/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$/zh/purejs/node_modules/@grapecity/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity/spread-sheets-tablesheet/dist/gc.spread.sheets.tablesheet.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity/spread-sheets-resources-zh/dist/gc.spread.sheets.resources.zh.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> </html>
.sample-tutorial { position: relative; height: 100%; overflow: hidden; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; } .sample-spreadsheets { width: 100%; height: 100%; overflow: hidden; float: left; }