TableSheet Reference in Worksheet Formulas

Worksheet formulas can cross-reference and summarize data in the TableSheet to create advanced report views.

Worksheets can directly access the data in the DataManager through the QUERY function.

Users can use structured references to reference the TableSheet in a Worksheet just like a table. The TableSheet name is the table name and the column caption is the table column. Here is the sample: It also supports the A1/R1C1 reference style, but it's not easy to read. The reference style can be used in cell formulas, charts, and conditional formats. Users can use the QUERY function to fetch data from DataManager tables. QUERY Syntax Return Types Examples The whole table QUERY("table1") The whole column QUERY("table1", "column1") The data QUERY("table1#1", "column1")QUERY("customer/abc@gmail.com", "name) The certain row QUERY("table1#1")QUERY("customer/abc@gmail.com") The filter row User can use simple filter of rows: Type Examples By row index QUERY("table1#1") By row primary key QUERY("customer/abc@gmail.com") By key-value filter QUERY("order?status=sucess") The multi-columns User can select many columns by using the array: Type Examples By column array QUERY("order?status=shipping", {"order date", "address", "Cargo weight"}) By one dimension reference 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\/demos\//)[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 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/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; }