phpexcelphpspreadsheet

Can I create formulas in php-generated spreadsheets using Openspout?

I have used phpspreadsheet to generate spreadsheets - it's a very solid library with a ton of capability and flexibility.

However, if you're generating large spreadsheets, with 1,000s of cells, it consumes an enormous amount of memory (something like 1.6kb per cell...)

To generate larger spreadsheets, I've seen recommendations to use Openspout, since it's far more memory-efficient.

However, after reading the documentation, I can't figure out how to create any cells that contain a formula (which seems like a pretty common use-case in spreadsheets!).

The predecessor of OpenSpout - Box/Spout - specifically mentioned that it did not support charts and formulas - but OpenSpout only specifically mentions not supporting charts (implying that it does support formulas).

So, can I create a spreadsheet containing formulas with OpenSpout?

Thanks in advance!

Solution

  • I recommend using FastExcelWriter, it's a lightweight and very fast XLSX-writer. I've used it for generation spreadsheets with rows 100k.

    This library can generate XLSX with formulas

    use \avadim\FastExcelWriter\Excel;

$excel = Excel::create(['Formulas']);
$sheet = $excel->getSheet();

// formulas with relative addresses
$sheet->writeRow([1, 123, '=RC[-1]*0.1']);
$sheet->writeRow([2, 456, '=RC[-1]*0.1']);
$sheet->writeRow([3, 789, '=RC[-1]*0.1']);

$totalRow = [
    'Total',
    '=SUM(B1:B3)', // absolute addresses
    '=SUM(C1:C3)',
];

$sheet->writeRow($totalRow);

$excel->save('formulas.xlsx');