excel_plus
Formulas in Dart
Write formulas into cells, and compute their results without Excel ever being involved.
Install#
dart pub add excel_plusimport 'package:excel_plus/excel_plus.dart';Writing a formula#
sheet.updateCell(CellIndex.indexByString('A1'), IntCellValue(10));
sheet.updateCell(CellIndex.indexByString('A2'), IntCellValue(20));
sheet.updateCell(CellIndex.indexByString('A3'), FormulaCellValue('SUM(A1:A2)'));
// Or set one on a cell that already exists.
sheet.cell(CellIndex.indexByString('A4')).setFormula('AVERAGE(A1:A2)');Evaluating without Excel#
A formula written into a file has no result until something calculates it. If the file is going to be read by a program rather than opened in Excel, calculate it yourself.
print(sheet.evaluate(CellIndex.indexByString('A3')));
// Store every formula's computed result in the file, so a reader sees values.
excel.recalculate();Recalculating only what changed#
On a large workbook, recomputing everything after each edit is wasteful. Name the cells you touched and only the formulas depending on them are redone.
sheet.updateCell(CellIndex.indexByString('A1'), IntCellValue(99));
excel.recalculate(changed: ['A1']);Dynamic arrays#
A formula that returns several values spills into the cells below or beside it, the same way modern Excel behaves.
sheet.cell(CellIndex.indexByString('D1')).setFormula('SEQUENCE(3)');
excel.recalculate();
// D1 keeps the formula and reports its spill range as 'D1:D3'.
// D2 and D3 receive 2 and 3.
// If a target cell is already occupied the anchor reports #SPILL!
// and nothing is overwritten.Custom functions#
Register a Dart function and call it from a formula like any built in.
excel.formula.registerFunction('TRIPLE', (args) {
final v = args.isEmpty ? null : args.first;
return IntCellValue((v is IntCellValue ? v.value : 0) * 3);
});
sheet.cell(CellIndex.indexByString('B1')).setFormula('TRIPLE(A1)');What is available#
Over 300 functions are implemented, covering maths and trigonometry, the full statistical library (distributions, their inverses, and the hypothesis tests), text, logical, lookup and reference, date and time, financial, database and engineering families. The full list lives in the function reference.