Kalkulationsbogen is a library for turing data into a spreadsheet. It is not meant to be a general purpose tool for controlling all aspects of a spreadsheet. The focus is on a simple and small API.

The motivation to build kalkulationsbogen was that writing out CSV files is simple but the capabilities are very limited. For example numbers or currency values can't be nicely formatted in CSV files.

For the time being, kalkulationsbogen only supports the Open Document Spreadsheet (ods) format.

Install / Import

$ npm install --save kalkulationsbogen

import { buildSpreadsheet } from "kalkulationsbogen";

Specific imports:

import { buildSpreadsheet } from "kalkulationsbogen/spreadsheet";

Usage

The API provides the function buildSpreadsheet, helper functions and a few types. It takes an argument of type spreadsheetInput which is an array of rows, each row is an array of cells. Cells may take different forms where the most simple one is a plain string. More complex cells are useful if data should be formatted according to its type. The provided types map to formatting options which are hardcoded in the spreadsheet template for now.

Examples

Simple spreadsheet with different data types

import { buildSpreadsheet } from "kalkulationsbogen";

const spreadsheet = [
  ["String", "Float", "Date", "Time", "Currency", "Percentage"],
  [
    {
      value: "ABBA",
      valueType: "string",
    },
    {
      value: "42.3324",
      valueType: "float",
    },
    {
      value: "2022-02-02",
      valueType: "date",
    },
    {
      value: "19:03:00",
      valueType: "time",
    },
    {
      value: "2.22",
      valueType: "currency",
    },
    {
      value: "0.4223",
      valueType: "percentage",
    },
  ],
];

const mySpreadsheet = await buildSpreadsheet(spreadsheet);
await writeFile("mySpreadsheet.fods", mySpreadsheet);

Formulas

Formula are represented in cells which take a functionName and a argument field. The arguments field may be a string if the function takes a single argument, or an array if it takes multiple arguments.

Cell references need to be provided in the "A1" format as in this example. Using the A1(column, row) function might be convenient.

Note that the indexes are 1-based, passing 0 will throw an error.

[
  [
    { value: "1.0", valueType: "float" },
    { value: "2.0", valueType: "float" },
    { value: "3.0", valueType: "float" },
  ],
  [
    { functionName: "SUM", arguments: `[.${A1(1, 1)}:.${A1(3, 1)}]` },
    { functionName: "AVERAGE", arguments: "[.A1:.C1]" },
    { functionName: "MIN", arguments: "[.A1:.C1]" },
  ],
  [
    { value: "1.1111111", valueType: "float" },
    { functionName: "ROUND", arguments: ["[.A3]", "1"] },
  ],
  [
    { value: "9.9876", valueType: "float" },
    { functionName: "ROUND", arguments: ["[.A4]", "1"] },
  ],
  [{ functionName: "ARABIC", arguments: ""MCMIII"" }],
];

Named Ranges

You might not want to use cell addresses in your formulas because they are not self documenting. You may use named ranges to help with that.

You may create a named range by applying the range property in one or multiple cells. If you apply the same name to multiple cells they need to be contiguous.

Usage example:

[
  [
    { value: "1", range: "one", valueType: "float" },
    { value: "1", range: "one", valueType: "float" },
    { value: "1", range: "one", valueType: "float" },
  ],
  [
    { value: "2", range: "two", valueType: "float" },
    { value: "3", range: "three", valueType: "float" },
  ],
  [
    {
      functionName: "SUM",
      arguments: "one",
    },
    {
      functionName: "",
      arguments: "two + three",
    },
  ],
];