Calculated Columns

TableSheet uses the 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.

The custom view supports column formulas, which include support for CalcEngine Functions. Other columns can be referenced using a structured reference like "=[@column1]"; This is the sample code. The reference is 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 [] 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" } } }); //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: "=[@UnitsInStock] + [@UnitsOnOrder]", caption: "Total Units", width: 120 }, { value: "=[@UnitPrice] * ([@UnitsInStock] + [@UnitsOnOrder])", 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: "=([@UnitPrice] * ([@UnitsInStock] + [@UnitsOnOrder]))/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/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <!-- Promise Polyfill for IE, --> <script src=""></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; }