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.
The default formula parser is based on open source
fast-formula-parser
. All formula calculations are done on the client-side.If you are using
useSpreadsheetState
hook to render the data of the Spreadsheet, its easy to add custom functions.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'
}
}
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.",
},
],
}]
const MySpreadsheet = () => {
const {} = useSpreadsheetState({
functions: {
SAY_WORLD
}
})
return (
<CanvasGrid
functionDescriptions={functionDescriptions}
/>
)
}
Your new custom formula should be ready to use
There are 2 ways to use a custom formula evaluation engine.
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
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
hookimport 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 })
}
})
/>
)
}
@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 workerLast modified 7mo ago