Works out each worksheet cell from an object's fields with a small formula language, rounds it to the column's decimals, and adds sums, counts, averages and extremes.
How the precision is kept
Formulas are evaluated in 64-bit numbers by the file's own parser; nothing is evaluated as code. When a column has a number of decimals set, each cell's value is rounded to it, and the sorting, subtotals and totals then work on those rounded cells. Without a decimals setting the full 64-bit value is kept.
Open item. Total the unrounded values and round the total, instead of adding cells already rounded to the column's decimals.
Calculations under this policy
5 calculations in 1 source file. The code is read from the released app when this page loads. It is the calculation people run, not a copy kept for this page.
Each operator is the plain 64-bit operation, with powers through Math.pow. Equality between numbers is exact, with no tolerance, so two values that differ in the last digit compare as different. A zero divisor and a negative square root return error codes instead of Infinity or NaN.
landscape/worksheet.js
lines 115 to 161
CivilCraft 0.8.1
function ev(n, row) {
switch (n.t) {
case 'num': case 'str': case 'bool': return n.v;
case 'field': { const v = fieldOf(row, n.name); return v === undefined ? null : v; }
case 'neg': return -num(ev(n.e, row));
case 'bin': {
const a = ev(n.l, row), b = ev(n.r, row);
switch (n.op) {
case '+': return num(a) + num(b);
case '-': return num(a) - num(b);
case '*': return num(a) * num(b);
case '/': { const d = num(b); if (d === 0) throw new CellError('#DIV/0'); return num(a) / d; }
case '%': { const d = num(b); if (d === 0) throw new CellError('#DIV/0'); return num(a) % d; }
case '^': { const r = Math.pow(num(a), num(b)); if (!fin(r)) throw new CellError('#ERR'); return r; }
case '&': return (str(a) + str(b)).slice(0, 2000);
default: {
const both = (fin(a) || typeof a === 'boolean') && (fin(b) || typeof b === 'boolean');
const x = both ? num(a) : str(a).toLowerCase(), y = both ? num(b) : str(b).toLowerCase();
return { '=': x === y, '<>': x !== y, '<': x < y, '<=': x <= y, '>': x > y, '>=': x >= y }[n.op];
}
}
}
case 'fn': {
const A = n.args, a = (i) => ev(A[i], row);
switch (n.f) {
case 'if': return truthy(a(0)) ? (A.length > 1 ? a(1) : true) : A.length > 2 ? a(2) : false;
case 'and': return A.every((_, i) => truthy(a(i)));
case 'or': return A.some((_, i) => truthy(a(i)));
case 'not': return !truthy(a(0));
case 'round': { const d = A.length > 1 ? Math.max(0, Math.min(10, Math.round(num(a(1))))) : 0, k = 10 ** d; return Math.round(num(a(0)) * k) / k; }
case 'ceil': return Math.ceil(num(a(0)));
case 'floor': return Math.floor(num(a(0)));
case 'abs': return Math.abs(num(a(0)));
case 'sqrt': { const v = num(a(0)); if (v < 0) throw new CellError('#ERR'); return Math.sqrt(v); }
case 'min': if (!A.length) throw new CellError('#ERR'); return Math.min(...A.map((_, i) => num(a(i))));
case 'max': if (!A.length) throw new CellError('#ERR'); return Math.max(...A.map((_, i) => num(a(i))));
case 'concat': return A.map((_, i) => str(a(i))).join('').slice(0, 2000);
case 'upper': return str(a(0)).toUpperCase();
case 'lower': return str(a(0)).toLowerCase();
case 'len': return str(a(0)).length;
case 'text': { const d = A.length > 1 ? Math.max(0, Math.min(10, Math.round(num(a(1))))) : 2; return num(a(0)).toFixed(d); }
default: throw new CellError('#ERR');
}
}
default: throw new CellError('#ERR');
}
}
Numbers to and from text inside a formula
64-bit
How a value is read as a number, and how a number is written when it is joined into text.
text of a number = round(v × 10⁹) ÷ 10⁹
An empty or missing value counts as 0 and text is converted with Number. A number joined into text, or compared with text, is first rounded to 9 decimals.
landscape/worksheet.js
lines 111 to 113
CivilCraft 0.8.1
const num = (v) => { if (v instanceof CellError) throw v; if (fin(v)) return v; if (typeof v === 'boolean') return v ? 1 : 0; if (v == null || v === '') return 0; const n = Number(v); if (Number.isFinite(n)) return n; throw new CellError('#ERR'); };
const str = (v) => { if (v instanceof CellError) throw v; return v == null ? '' : fin(v) ? String(Math.round(v * 1e9) / 1e9) : String(v); };
const truthy = (v) => (typeof v === 'boolean' ? v : fin(v) ? v !== 0 : !!v);
Rounding a cell to its column's decimals
64-bit
Applies the column's decimals setting to a cell.
cell = Number(v.toFixed(dec))
The value itself is replaced by its rounded form, not only its display, so everything downstream (sorting, subtotals, totals, CSV and spreadsheet export) sees the rounded number. With no decimals set the value passes through unchanged.
The total of a column over a group or the whole worksheet.
sum = Σ cells; average = Σ cells ÷ n
A plain left-to-right sum over the numeric cells; text and error cells are left out of sums and averages. The cells summed are the ones already rounded to the column's decimals.
landscape/worksheet.js
lines 193 to 202
CivilCraft 0.8.1
function totalOf(kind, vals) {
const nums = vals.filter(fin);
if (kind === 'count') return vals.filter((v) => v !== '' && v != null).length;
if (!nums.length) return '';
if (kind === 'sum') return nums.reduce((a, b) => a + b, 0);
if (kind === 'avg') return nums.reduce((a, b) => a + b, 0) / nums.length;
if (kind === 'min') return Math.min(...nums);
if (kind === 'max') return Math.max(...nums);
return '';
}
Rows, groups, subtotals and totals
64-bit
Builds the worksheet's rows from the objects, grouped and sorted, with subtotals and a total line.
Cells are rounded to the column's decimals as they are made; subtotals and the grand total are computed from those cells and rounded once more. Sorting compares the rounded cells numerically.
landscape/worksheet.js
lines 204 to 225
CivilCraft 0.8.1
export function runWorksheet(ws, rows) {
const errors = [], parsed = ws.columns.map((c, i) => { try { return parseExpr(c.expr); } catch (e) { errors.push({ col: i, msg: e.message }); return null; } });
let fAst = null, gAst = null;
if (ws.filter) { try { fAst = parseExpr(ws.filter); } catch (e) { errors.push({ col: -1, msg: e.message }); } }
if (ws.group) { try { gAst = parseExpr(ws.group); } catch (e) { errors.push({ col: -2, msg: e.message }); } }
const src = (rows || []).slice(0, LIMITS.rows).filter((r) => !fAst || truthy(evalExpr(fAst, r)));
let lines = src.map((r) => ({ g: gAst ? str2(evalExpr(gAst, r)) : '', cells: parsed.map((a, i) => (a ? fmt(evalExpr(a, r), ws.columns[i].dec) : '#ERR')) }));
if (ws.sort) { const c = ws.sort.col, d = ws.sort.dir; lines.sort((a, b) => { const x = a.cells[c], y = b.cells[c]; return (fin(x) && fin(y) ? x - y : String(x ?? '').localeCompare(String(y ?? ''))) * d; }); }
const sub = (ls) => ws.columns.map((c, i) => (c.total ? fmt(totalOf(c.total, ls.map((l) => l.cells[i])), c.dec) : ''));
const body = [];
if (gAst) {
const groups = new Map();
for (const l of lines) { if (!groups.has(l.g)) groups.set(l.g, []); groups.get(l.g).push(l); }
for (const [g, ls] of [...groups.entries()].sort((a, b) => a[0].localeCompare(b[0]))) {
body.push({ kind: 'group', cells: [g, ...ws.columns.slice(1).map(() => '')] });
for (const l of ls) body.push({ kind: 'row', cells: l.cells });
if (ws.columns.some((c) => c.total)) body.push({ kind: 'subtotal', cells: sub(ls) });
}
} else for (const l of lines) body.push({ kind: 'row', cells: l.cells });
const totals = ws.columns.some((c) => c.total) ? sub(lines) : null;
return { head: ws.columns.map((c) => c.label), body, totals, errors, count: lines.length };
}
In detail
Method
Recursive-descent parser for + − × ÷ % ^, comparisons, text join and a fixed list of functions; totals by a plain sum in row order.
Number types
64-bit numbers; text for joined values.
Tolerances
None in comparisons: = and <> on numbers are exact. round(x, d) and text(x, d) take 0 to 10 decimals; a column takes 0 to 6. A number joined into text is rounded to 9 decimals.
Limits
A total over a column with decimals set is the sum of rounded cells, which can differ from the rounded sum of the full values. A division by zero shows #DIV/0 and a result that is not finite shows #ERR.
Approach: 64-bit, direct
Every value is a 64-bit number from input to result. Formulas are closed form or a direct solve, and every comparison uses a stated tolerance.