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.js
index.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:
window.onload = async () => { const loadingTip = addLoadingTip(); const { spread, designer } = createSpreadAndDesigner(); 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(); if (designer) { designer.refresh(); } } function createSpreadAndDesigner() { const demoHost = document.getElementById('demo-host'); if (window !== top) { return { spread: new GC.Spread.Sheets.Workbook(demoHost, { sheetCount: 1 }), } } else { const designer = new GC.Spread.Sheets.Designer.Designer(demoHost, undefined, undefined, { sheetCount: 1 }); return { designer, spread: designer.getWorkbook(), } } } 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; }
<!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"> <link rel="stylesheet" type="text/css" href="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets-designer/styles/gc.spread.sheets.designer.min.css"> <link rel="stylesheet" type="text/css" href="styles.css"> <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-print/dist/gc.spread.sheets.print.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets-shapes/dist/gc.spread.sheets.shapes.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets-charts/dist/gc.spread.sheets.charts.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets-reportsheet-addon/dist/gc.spread.report.reportsheet.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/spread/source/js/license.js" type="text/javascript"></script> <script> const designerDependencyScripts = [ '$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets-barcode/dist/gc.spread.sheets.barcode.min.js', '$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets-pdf/dist/gc.spread.sheets.pdf.min.js', '$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets-pivot-addon/dist/gc.spread.pivot.pivottables.min.js', '$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets-slicers/dist/gc.spread.sheets.slicers.min.js', '$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets-tablesheet/dist/gc.spread.sheets.tablesheet.min.js', '$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets-ganttsheet/dist/gc.spread.sheets.ganttsheet.min.js', '$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets-formula-panel/dist/gc.spread.sheets.formulapanel.min.js', '$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets-io/dist/gc.spread.sheets.io.min.js', '$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets-designer-resources-en/dist/gc.spread.sheets.designer.resource.en.min.js', '$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets-designer/dist/gc.spread.sheets.designer.all.min.js', '$DEMOROOT$/spread/source/js/designer/license.js', ] function appendScriptNode (src) { const script = document.createElement('script'); script.src = src; script.async = false; script.type = 'text/javascript'; document.head.appendChild(script); } if (top === window) { // not in iframe designerDependencyScripts.forEach(appendScriptNode); } </script> <script src="app.js" type="text/javascript"></script> </head> <body> <div class="sample-tutorial"> <div id="demo-host"></div> </div> </body> </html>
body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; } .sample-tutorial { position: relative; height: 100%; overflow: hidden; } #demo-host { width: 100%; height: 100%; overflow: hidden; float: left; }