[]
DEMOS DOC DESIGNER API
DOWNLOAD
(Showing Draft Content)

Preserve Formula in Export

The Preserve Formula in Export feature allows users to retain formulas when exporting data to Excel.

Usage Scenarios

When exporting reports, developers often face the issue that calculated fields or formulas are converted into static values. This breaks dynamic behavior in the resulting Excel files and forces users to re‑enter or rebuild formulas manually.

With Preserve Formula in Export, these challenges are avoided:

  • Retained calculations: Formulas defined in your dataset, templates, or computed fields remain intact after export, ensuring Excel users can continue working with live calculations rather than fixed numbers.

  • Editable Excel output: Exported workbooks allow end‑users to adjust cell values or ranges, with dependent formulas updating automatically, just as in the original environment.

Related API

This feature is configured through the GC.Spread.Report.TemplateSheet.setTemplateCell method.

When applying the setting on a StaticCell, use the preserveFormulaInExport property.

export type StaticCell = {
    type: 'Static',
    preserveFormulaInExport?: boolean;
    // see API reference for other properties
};

Notes:

  • Formula preservation is only available in preview mode.

  • This feature is effective only when the related option has been properly configured.

Formula Export Behavior

Formula Type

Condition

Export Behavior

Example

Standard Excel Functions

Expanded cell range is continuous

The reference is exported as a single range.

image


Expanded cell range is non‑continuous

Supported functions (SUM, AVERAGE, MIN, MAX, COUNT, COUNTA, COUNTBLANK) export multiple ranges

image

R.V Formulas

Resolvable in preview mode

Converted into actual cell references

image


Not resolvable

Exported as empty cells

image


Partially resolvable within expressions

Replaced by

'SJS.EMPTY_CELL'

(value = 0)

image

SpreadJS Report Sheet Specific Formulas

Functions like R.Index, R.Rank, R.MoM, R.YoY, R.Proportion, R.Cumulative, …

  • Export retains function names and references.

  • Excel shows ‘#NAME?’' (unsupported)

image

Usage Example

const templateSheet = reportSheet.getTemplate();
templateSheet.setFormula(3, 3, '=SUM(D2)');

// Set the preserveFormulaInExport as true.
templateSheet.setTemplateCell(3, 3, {
  type: 'Static',
  preserveFormulaInExport: true,
});

Using Designer

You can also set the preserveFormulaInExport property of the template cell using the SpreadJS designer's Preserve Formula In Export option available in the Report Cell properties panel.

image