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

Data validation

Real-time data

Last modified 7mo ago