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#
Around 160 functions are implemented, covering maths and statistics, text, logical, lookup and reference, date and time, financial, database and engineering families. The full list lives in the function reference.