表单公式引用集算表

表单公式可以交叉引用和汇总集算表中的数据,以创建高级报表视图。

表单可以通过QUERY函数直接访问DataManager中的数据。

用户可以在表单中,像引用表格一样使用结构引用来引用集算表。 集算表名称是表的名称,列标题是表的列。下面是示例。 它也支持A1/R1C1参考样式,但它不容易阅读。A1/R1C1参考样式可以在单元格公式、图表和条件格式中使用。 用户可以使用QUERY函数从DataManager表中获取数据。 QUERY语法 返回类型 示例 整个表 QUERY("table1") 这个列 QUERY("table1", "column1") 单一数据 QUERY("table1#1", "column1")QUERY("customer/abc@gmail.com", "name) 整个行 QUERY("table1#1")QUERY("customer/abc@gmail.com") 筛选行 用户可以使用简单的行筛选器。 类型 示例 按照行索引 QUERY("table1#1") 按照行主键 QUERY("customer/abc@gmail.com") 按照键值对筛选 QUERY("order?status=sucess") 多列 用户可以通过使用数组来选择许多列。 类型 示例 按列引用 QUERY("order?status=shipping", {"order date", "address", "Cargo weight"}) 通过维度引用 QUERY("order?status=shipping", A1:D1)
/*REPLACE_MARKER*/ /*DO NOT DELETE THESE COMMENTS*/ window.onload = function() { var spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 1 }); initSpread(spread); }; function initSpread(spread) { spread.suspendPaint(); spread.options.autoFitType = GC.Spread.Sheets.AutoFitType.cellWithHeader; spread.options.allowDynamicArray = true; //init a data manager var baseApiUrl = getBaseApiUrl(); var dataManager = spread.dataManager(); //add product table var productTable = dataManager.addTable("productTable", { remote: { read: { url: baseApiUrl + "/Product" } } }); //add category table var categoryTable = dataManager.addTable("categoryTable", { remote: { read: { url: baseApiUrl + "/Category" } } }); //add relation ship dataManager.addRelationship(productTable, "CategoryId", "category", categoryTable, "Id", "products"); //init a table sheet var sheet = spread.addSheetTab(0, "TableSheet1", GC.Spread.Sheets.SheetType.tableSheet); sheet.options.allowAddNew = false; //hide new row sheet.setDefaultRowHeight(40, GC.Spread.Sheets.SheetArea.colHeader); //bind a view to the table sheet var myView = productTable.addView("myView", [ { value: "Id", caption: "ID" }, { value: "category.CategoryName", caption: "Category", width: 120 }, { value: "ProductName", caption: "Product Name", width: 200 }, { value: "UnitPrice", caption: "Unit Price", width: 120 }, { value: "UnitsInStock", caption: "Units In Stock", width: 120 }, { value: "UnitsOnOrder", caption: "Units On Order", width: 130 }, { value: "=SUM([@UnitsInStock], [@UnitsOnOrder])", caption: "Total Amount", width: 130 }, { value: "=[@UnitPrice] * SUM([@UnitsInStock], [@UnitsOnOrder])", caption: "Total Price", width: 120 } ]); myView.fetch().then(function() { spread.suspendPaint(); sheet.setDataView(myView); var sheet1 = spread.getSheet(0); sheet1.setColumnWidth(0, 170); sheet1.setColumnWidth(1, 95); sheet1.setColumnWidth(2, 95); sheet1.setColumnWidth(3, 120); sheet1.setColumnWidth(4, 170); sheet1.setColumnWidth(6, 110); sheet1.setColumnWidth(7, 170); sheet1.setValue(0, 0, "Product Count:") sheet1.setFormula(0, 1, "=COUNTA(TableSheet1[ID])"); sheet1.setValue(0, 3, 'Total Price:') sheet1.setFormula(0, 4, '=SUM(TableSheet1[Total Price])'); //Category Summary applyTableStyleForRange(sheet1, 3, 0, 9, 5); sheet1.setValue(2, 0, "Category Summary:") sheet1.setArray(3, 0, [ ["Category", "Product No", "On Order Cost", "Highest Unit Price", "Highest Price Product"] ]); sheet1.setFormula(4, 0, '=UNIQUE(TableSheet1[Category])'); sheet1.setFormula(4, 1, '=COUNTIF(TableSheet1[Category], A5#)'); sheet1.setFormula(4, 3, '=MAXIFS(TableSheet1[Unit Price], TableSheet1[Category], A5#)'); for (var i = 5; i <= 12; i++) { sheet1.setFormula(i - 1, 2, '=SUMPRODUCT((TableSheet1[Category]=A' + i + ')*TableSheet1[Units On Order],TableSheet1[Unit Price])'); sheet1.setFormula(i - 1, 4, '=XLOOKUP(D' + i + ', IF(TableSheet1[Category] = A' + i + ', TableSheet1[Unit Price], -1), TableSheet1[Product Name])'); } //Out of Stock sheet1.setValue(2, 6, "Out of Stock:") applyTableStyleForRange(sheet1, 3, 6, 6, 2); sheet1.setArray(3, 6, [ ["Category", "Product"] ]); sheet1.setFormula(4, 6, '=FILTER(TableSheet1[Category]:TableSheet1[Product Name], TableSheet1[Units In Stock] = 0)'); //Filters sheet1.setValue(13, 0, "Category:"); sheet1.setFormula(13, 1, "=A5"); var dv2 = GC.Spread.Sheets.DataValidation.createFormulaListValidator('=$A$5#'); sheet1.setDataValidator(13, 1, dv2); sheet1.setValue(13, 3, "Category ID:"); sheet1.setFormula(13, 4, '=QUERY("categoryTable?CategoryName="&B14, "Id")'); sheet1.comments.add(13, 4, 'Query the category id.'); applyTableStyleForRange(sheet1, 15, 0, 14, 3); sheet1.setArray(15, 0, [ ["ProductName", "UnitPrice", "UnitsInStock"] ]); sheet1.setFormula(16, 0, '=QUERY("productTable?CategoryId="&E14, A16:C16)'); sheet1.comments.add(16, 0, 'Use the QUERY function to get data from DataManager.'); spread.setActiveSheetIndex(0); spread.resumePaint(); }); spread.resumePaint(); } function getBaseApiUrl() { return window.location.href.match(/http.+spreadjs\/SpreadJSTutorial\//)[0] + 'server/api'; } function applyTableStyleForRange(sheet, row, col, rowCount, colCount, options) { var tableName = "tmpTable"; var TableThemes = GC.Spread.Sheets.Tables.TableThemes; // use table to help set style then remove like convert table to range in Excel sheet.tables.add(tableName, row, col, rowCount, colCount, TableThemes.light2, options); sheet.tables.remove(tableName, 2 /* keep style */ ); }
<!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-software/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-software/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-tablesheet/dist/gc.spread.sheets.tablesheet.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/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 id="optionContainer" class="optionContainer"> </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; }