Demos
Enhanced Add-Ons
ReportSheet
Data Settings
Calculated Columns

Calculated Column

In some conditions, the table origin columns only have basic data. If you want to add a new column that is calculated based on the other columns in the table, you can use a Calculated Column.

JavaScript
Angular
Vue3
React
Description
app.component.ts
index.html
app.component.html
styles.css
Copy to CodeMine
Open the demo in new window can open the designer

How to open desgnr

You can add a calculated column into a table column with the following code:

    const spread = new GC.Spread.Sheets.Workbook('ss', { sheetCount: 1 });
    const orderDetailsTable = spread.dataManager().addTable('OrderDetails', {
        remote: {
            read: {
                url: baseApiUrl + '/OrderDetail'
            }
        }
    });
    orderDetailsTable.fetch().then(() => {
        orderDetailsTable.columns["Sales"] = { // add a custom column
            value: "=[@UnitPrice]*[@Quantity]*(1-[@Discount])", // the column value is formula.
        };
        const reportSheet = spread.addSheetTab(0, 'report1', GC.Spread.Sheets.SheetType.reportSheet);
        const templateSheet = reportSheet.getTemplate();
        // set value and binding for the template
        templateSheet.setTemplateCell(1, 0, {
            type: 'List',
            binding: `OrderDetails[ProductId]`,
        });
        templateSheet.setTemplateCell(1, 1, {
            type: 'Summary',
            aggregate: "Sum",
            binding: `OrderDetails[Sales]`,
        });
        // refresh the report
        reportSheet.refresh();
    });
Open the demo in new window can open the designer You can add a calculated column into a table column with the following code:
import { Component, NgModule, enableProdMode } from '@angular/core'; import { BrowserModule } from '@angular/platform-browser'; import { platformBrowserDynamic } from '@angular/platform-browser-dynamic'; import { SpreadSheetsModule } from '@mescius/spread-sheets-angular'; import GC from '@mescius/spread-sheets'; import '@mescius/spread-sheets-print'; import '@mescius/spread-sheets-shapes'; import '@mescius/spread-sheets-charts'; import '@mescius/spread-sheets-reportsheet-addon'; import '@mescius/spread-sheets-tablesheet'; import '@mescius/spread-sheets-ganttsheet'; import '@mescius/spread-sheets-formula-panel'; import '@mescius/spread-sheets-designer-resources-en'; import '@mescius/spread-sheets-designer'; import { DesignerModule } from "@mescius/spread-sheets-designer-angular"; import './styles.css'; import '$DEMOROOT$/spread/source/js/angular/license.ts'; import { registerlic } from '$DEMOROOT$/spread/source/js/designer/react_vue/license.js'; registerlic(GC); @Component({ selector: 'app-component', templateUrl: 'src/app.component.html' }) export class AppComponent { showDesigner = top === window; props = {}; hostStyle: any; constructor() { if (!this.showDesigner) { this.hostStyle = { width: '100%', height: '100%', overflow: 'hidden', float: 'left' }; } } initDesigner($event) { initDesigner($event.designer); } initSpread($event) { initSpread($event.spread); } } async function initDesigner(designer) { await initSpread(designer.getWorkbook()); designer.refresh(); } async function initSpread(spread) { spread.suspendPaint(); spread.setSheetCount(1); const loadingTip = addLoadingTip(); const res = await fetch('$DEMOROOT$/en/sample/features/report-sheet/data-settings/calculated-columns/spread.json'); await spread.fromJSON(correctTableUrl(await res.json())); loadingTip.remove(); spread.resumePaint(); } function addLoadingTip() { const div = document.createElement('div'); div.style.position = 'absolute'; div.style.inset = '0'; div.style.display = 'flex'; div.style.alignItems = 'center'; div.style.justifyContent = 'center'; div.style.background = 'white'; div.style.zIndex = '100'; div.textContent = 'Loading data from server ...'; document.body.appendChild(div); return div; } function correctTableUrl(json) { const baseUrl = '$DEMOROOT$'; const localUrl = 'http://localhost:8070/spreadjs/demos'; const replaceUrl = (obj) => { if (obj && obj.url && obj.url.startsWith(localUrl)) { obj.url = obj.url.replace(localUrl, baseUrl); } } if (json.dataManager && json.dataManager.tables) { json.dataManager.tables.forEach((table) => { if (table.dataSourceOption && table.dataSourceOption.remote) { Object.values(table.dataSourceOption.remote).forEach(replaceUrl); } }); } return json; } @NgModule({ imports: [BrowserModule, SpreadSheetsModule, DesignerModule], declarations: [AppComponent], exports: [AppComponent], bootstrap: [AppComponent] }) export class AppModule { } enableProdMode(); // Bootstrap application with hash style navigation and global services. platformBrowserDynamic().bootstrapModule(AppModule);
<!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/angular/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <link rel="stylesheet" type="text/css" href="$DEMOROOT$/en/angular/node_modules/@mescius/spread-sheets-designer/styles/gc.spread.sheets.designer.min.css"> <!-- Polyfills --> <script src="$DEMOROOT$/en/angular/node_modules/core-js/client/shim.min.js"></script> <script src="$DEMOROOT$/en/angular/node_modules/zone.js/fesm2015/zone.min.js"></script> <!-- SystemJS --> <script src="$DEMOROOT$/en/angular/node_modules/systemjs/dist/system.js"></script> <script src="systemjs.config.js"></script> <script> // workaround to load 'rxjs/operators' from the rxjs bundle System.import('rxjs').then(function (m) { System.import('@angular/compiler'); System.set(SystemJS.resolveSync('rxjs/operators'), System.newModule(m.operators)); System.import('./src/app.component'); }); </script> </head> <body> <app-component></app-component> </body> </html>
<div class="sample-tutorial"> <designer *ngIf="showDesigner" [props]="props" (designerInitialized)="initDesigner($event)"></designer> <gc-spread-sheets *ngIf="!showDesigner" [hostStyle]="hostStyle" (workbookInitialized)="initSpread($event)"></gc-spread-sheets> </div>
body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; } .sample-tutorial { position: relative; height: 100%; overflow: hidden; } .gc-designer-container { height: 100%; width: 100%; }
(function (global) { System.config({ transpiler: 'ts', typescriptOptions: { tsconfig: true }, meta: { 'typescript': { "exports": "ts" }, '*.css': { loader: 'css' } }, paths: { // paths serve as alias 'npm:': 'node_modules/' }, // map tells the System loader where to look for things map: { 'core-js': 'npm:core-js/client/shim.min.js', 'zone': 'npm:zone.js/fesm2015/zone.min.js', 'rxjs': 'npm:rxjs/dist/bundles/rxjs.umd.min.js', '@angular/core': 'npm:@angular/core/fesm2022', '@angular/common': 'npm:@angular/common/fesm2022/common.mjs', '@angular/compiler': 'npm:@angular/compiler/fesm2022/compiler.mjs', '@angular/platform-browser': 'npm:@angular/platform-browser/fesm2022/platform-browser.mjs', '@angular/platform-browser-dynamic': 'npm:@angular/platform-browser-dynamic/fesm2022/platform-browser-dynamic.mjs', '@angular/common/http': 'npm:@angular/common/fesm2022/http.mjs', '@angular/router': 'npm:@angular/router/fesm2022/router.mjs', '@angular/forms': 'npm:@angular/forms/fesm2022/forms.mjs', 'jszip': 'npm:jszip/dist/jszip.min.js', 'typescript': 'npm:typescript/lib/typescript.js', 'ts': './plugin.js', 'tslib':'npm:tslib/tslib.js', 'css': 'npm:systemjs-plugin-css/css.js', 'plugin-babel': 'npm:systemjs-plugin-babel/plugin-babel.js', 'systemjs-babel-build':'npm:systemjs-plugin-babel/systemjs-babel-browser.js', '@mescius/spread-sheets-designer-resources-en': 'npm:@mescius/spread-sheets-designer-resources-en/index.js', '@mescius/spread-sheets-designer': 'npm:@mescius/spread-sheets-designer/index.js', '@mescius/spread-sheets-designer-angular': 'npm:@mescius/spread-sheets-designer-angular/fesm2020/mescius-spread-sheets-designer-angular.mjs', '@mescius/spread-excelio': 'npm:@mescius/spread-excelio/index.js', '@mescius/spread-sheets-barcode': 'npm:@mescius/spread-sheets-barcode/index.js', '@mescius/spread-sheets-charts': 'npm:@mescius/spread-sheets-charts/index.js', '@mescius/spread-sheets-languagepackages': 'npm:@mescius/spread-sheets-languagepackages/index.js', '@mescius/spread-sheets-print': 'npm:@mescius/spread-sheets-print/index.js', '@mescius/spread-sheets-pdf': 'npm:@mescius/spread-sheets-pdf/index.js', '@mescius/spread-sheets-shapes': 'npm:@mescius/spread-sheets-shapes/index.js', '@mescius/spread-sheets-io': 'npm:@mescius/spread-sheets-io/index.js', '@mescius/spread-sheets-reportsheet-addon': 'npm:@mescius/spread-sheets-reportsheet-addon/index.js', '@mescius/spread-sheets-tablesheet': 'npm:@mescius/spread-sheets-tablesheet/index.js', '@mescius/spread-sheets-ganttsheet': 'npm:@mescius/spread-sheets-ganttsheet/index.js', '@mescius/spread-sheets-formula-panel': 'npm:@mescius/spread-sheets-formula-panel/index.js', '@mescius/spread-sheets-angular': 'npm:@mescius/spread-sheets-angular/fesm2020/mescius-spread-sheets-angular.mjs', '@mescius/spread-sheets': 'npm:@mescius/spread-sheets/index.js', }, // packages tells the System loader how to load when no filename and/or no extension packages: { src: { defaultExtension: 'ts' }, rxjs: { defaultExtension: 'js' }, "node_modules": { defaultExtension: 'js' }, "node_modules/@angular": { defaultExtension: 'mjs' }, "@mescius/spread-sheets-angular": { defaultExtension: 'mjs' }, "@mescius/spread-sheets-designer-angular": { defaultExtension: 'mjs' }, '@angular/core': { defaultExtension: 'mjs', main: 'core.mjs' } } }); })(this);