Kalkulationsbogen Spreadsheet builder for nodejs written in TypeScript
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.
$ npm install --save kalkulationsbogen
import { buildSpreadsheet } from "kalkulationsbogen";
Specific imports:
import { buildSpreadsheet } from "kalkulationsbogen/spreadsheet";
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.
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);
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"" }],
];
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",
},
],
];