Formulas not auto calculating values

[atrabulsi]

Hello,

Thanks for this package, it's really cool and super fast!

I ran into an issue when working with spreadsheets with formulas. I have the following code snippet:

        $xlsxFastEditor = new XlsxFastEditor(storage_path('test.xlsm'));
        $worksheetId1 = 3;
        $row = $xlsxFastEditor->getRow($worksheetId1, 4);
        $cell = $row->getCellOrNull('D');
        $cell->writeInt(50);
        $xlsxFastEditor->setFullCalcOnLoad($worksheetId1, true);
        $xlsxFastEditor->save();

So I'm basically writing the value 50 in the cell D4.

Another cell has a formula that depends on the value of D4. When opening the spreadsheet later in LibreOffice, the cell with the formula did not change to reflect the change of D4. If I manually change the value of D4, the cell with the formula changes. I used LibreOffice, not Excel. When I opened the updated file with Google Spreadsheet, it worked fine.

So my question is: does the line:

        $xlsxFastEditor->setFullCalcOnLoad($worksheetId1, true);

Work only with Excel (and Google Spreadsheet)?

Thanks, Anas

[Alkarex]

Hello, And thanks for the feedback. I have not tested with LibreOffice Calc (yet) but maybe it requires an internal parameter to be an integer and not a boolean. Could you please test https://github.com/alexandrainst/php-xlsx-fast-editor/pull/8 ?

[atrabulsi]

No luck :( same behavior

[atrabulsi]

One workaround I found is to use php-xlsx-fast-editor to re-write all the cells with formulas. All I have to do really is read every cell as a formula, and if it's not null, rewrite the same value. If I do that, the value appears correctly. It's not ideal, but it works especially for smaller spreadsheet.

[Alkarex]

Related: Under Tools > Options > LibreOffice Calc > Formula https://ask.libreoffice.org/t/how-do-i-make-formulas-automatically-recalculate/27902/3

[Alkarex]

https://bugs.documentfoundation.org/show_bug.cgi?id=144819 https://ask.libreoffice.org/t/libreoffice-calc-not-computing-functions-showing-function-not-output/17418/42

[atrabulsi]

Related: Under Tools > Options > LibreOffice Calc > Formula

Worked fine, but since I'm not the main user of the spreadsheet, I wouldn't expect from the main users to enable recalculation on file load. I guess the problem as you pointed out is:

XLS and XLSX files have mechanisms for marking that a file is dirty and should be recalculated.  LibreOffice is not honoring these flags.

Thanks

[boryn]

Hi @Alkarex!

I have similar question, though I would like to read the recalculated value inside the XlsxFastEditor library itself. So I do:

$cell->writeInt(50);
// here I save the new spreadsheet
$xlsxFastEditor->save();

// reopen it
$xlsxFastEditor = new XlsxFastEditor('test.xlsx');
// let's assume A10 contains a formula using modified $cell
$modifiedCell = $xlsxFastEditor->readFloat($worksheetId1, 'A10');

and unfortunately, $modifiedCell still contains the old value. The setFullCalcOnLoad() does not help. On the other hand, when I open the file in the Excel itself, I can see the new D4 value and A10 is modified as expected, but I want to read this modified value "programatically", within the server. Is this at all possible? Or the formulas are only applied in the real spreadsheet program?

[Alkarex]

@boryn This library does not implement the calculation logic so no. If you have a formula you want to recompute, either you re-open the document in Excel / Calc, or translate the formula to PHP, or use another library such as https://github.com/PHPOffice/PhpSpreadsheet (but which is much slower).