Calculated Columns

TableSheet uses SpreadJS powerful calculation engine to add calculated columns to your views. You can create columns with simple column references or advanced summary and logical functions.

In the following sample, we have multiplied Unit Price and Unit In Stock to have a calculated column for Stock Value.

You can add the calculated columns to the table, and the calculated columns can be referenced by the View, Window function, and Query method. The calculated columns can reference the data columns, the related table columns, and other calculated columns. The calculated columns in the table are defined as follows: The custom view supports calculated columns too, and it can reference the data columns and calculated columns in the table. This is the sample code. The calculated columns can reference other columns by using structured references like "=[@column1]"; The reference must consist of at least one row ref or column ref. Row Ref Reference Description Sample Current Row Reference the current row [@column1] the data in the current row of field column1 Relative Row Reference the relative row [@+1column1] returns column1 value in the next row [@-1column1] returns column1 value in the previous row. Absolute Row Reference the row of absolute position by row number or "last"/"odd"/"even" [#1[column1]] returns column1 values in first row. [#last[column1]] returns column1 value in the last rows Absolute Row by ID Reference the row by the primary key [$1234[column1]] returns column1 values of data with primary key "1234" Row Range Reference the range of rows by from and to [@:#last[column1]] returns column1 values of rows from current row to the last row Several Rows Reference several rows by comma [#1,@-1:@+1,#last[column1]] returns column1 values in first row, previous row to the next row, and the last row. Column Ref Reference Sample Regular Field [@column1] Attribute of object field [@column1.abc] get the attribute value of "abc" in the current row of field column1 Item of array field [@column1.1] get the first value in the current row of field column1 Special Field [@[column 1].[a aa]] use the square brackets means a certain field Whole Column [column1] get all the data in the column1
/*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 baseApiUrl = getBaseApiUrl(); var dataManager = spread.dataManager(); //add product table var productTable = dataManager.addTable("productTable", { remote: { read: { url: baseApiUrl + "/Product" } }, schema: { columns: { TotalUnits: { dataType: "formula", value: "[@UnitsInStock] + [@UnitsOnOrder]" }, StockValue: { dataType: "formula", value: "[@UnitPrice] * ([@TotalUnits])" } } } }); //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", width: 50 }, { value: "ProductName", caption: "Name", width: 170 }, { value: "UnitPrice", caption: "Unit Price", style: {formatter: "$#,##0.00"}, width: 120 }, { value: "UnitsInStock", caption: "Units In Stock", width: 120 }, { value: "TotalUnits", caption: "Total Units", width: 120 }, { value: "StockValue", caption: "Stock Value", style: {formatter: "$#,##0.00"}, width: 120 }, { value: "=SUM([#1:@[UnitPrice]]*([#1:@[UnitsInStock]]+[#1:@[UnitsOnOrder]])", caption: "Running SUM", style: {formatter: "$#,##0.00"}, width: 150 }, { value: "=[@StockValue]/SUM([UnitPrice] * ([UnitsInStock] + [UnitsOnOrder]))", caption: "Stock Value Ratio", style: {formatter: "0.00%"}, width: 160 } ]); myView.fetch().then(function() { sheet.setDataView(myView); }); spread.resumePaint(); } function getBaseApiUrl() { return window.location.href.match(/http.+spreadjs\/demos\//)[0] + 'server/api'; }
<!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"> <!-- 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/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> </body> </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; }