Links
Comment on page

Formula evaluation

Formula parser and calculation is plug n play
Spreadsheet is completely headless, you can use your own formula parser and evaluator. You can choose to use client-side or server side evaluation.

Formula parser

The default formula parser is based on open source fast-formula-parser . All formula calculations are done on the client-side.

Custom functions

If you are using useSpreadsheetState hook to render the data of the Spreadsheet, its easy to add custom functions.

1. Create your named function

import type FormulaParser from "@rowsncolumns/fast-formula-parser";
import type { FunctionArgument } from "@rowsncolumns/calculator";
const SAY_WORLD = (parser: FormulaParser, arg: FunctionArgument) => {
if (arg.value === 'hello') {
return 'world'
}
}

2. Create a function description

Function description appears in the dropdown when users enters the formula
const functionDescriptions = [{
datatype: "Text",
title: "SAY_WORLD",
syntax: "SAY_WORLD(value)",
description: "Returns world if user says hello.",
example: "SAY_WORLD('hello')",
usage: ["SAY_WORLD('oops')"],
parameters: [
{
title: "value",
description: "The text that user enters.",
},
],
}]

3. Pass the function and description to useSpreadsheetState

const MySpreadsheet = () => {
const {} = useSpreadsheetState({
functions: {
SAY_WORLD
}
})
return (
<CanvasGrid
functionDescriptions={functionDescriptions}
/>
)
}
Your new custom formula should be ready to use

Using your own formula evaluation

There are 2 ways to use a custom formula evaluation engine.

1. enqueueCalculation in useSpreadsheetState

import type { CellCoordinate } from "@rowsncolumns/dag";
const MySpreadsheet = () => {
const {} = useSpreadsheetState({
enqueueCalculation: (type: 'add' | 'remove' | 'dirty', position: CellCoordinate) => {
// Process calculations and update sheet
const value = getCellValue (position)
if (value.str(0) === '=') {
const result = myCustomEvaluationQueue(value)
// Call setState to update result
onChangeSheetData()
}
}
})
return (
<CanvasGrid
/>
)
}
If you are using a custom calculator, assuming that you are maintaining the cell dependency graph, you will also have to provide getDependents and getPrecendents API to useSpreadsheetState

2. Sending results to back-end

You can either use above method to send the calculation to the back-end to evaluate formulas or you can use the headless-ui, without useSpreadsheetState hook
import type { CellCoordinate } from "@rowsncolumns/dag";
const MySpreadsheet = () => {
return (
<CanvasGrid
onChange={(sheetId, rowIndex, columnIndex, value: string) => {
if (value.str(0) === '=') {
const result = await fetch("post", { value })
onChangeSheetData({ result })
}
})
/>
)
}

Web Worker support

@rowsncolumns/calculator-worker package supports calculation via a web worker. The cell dependency graphs is still on the main UI thread, but formula parsing and calculation happens on a web worker