templater doesnt run the formulaes at the end

[Raman117]

I'm working with an Excel (.xlsx) template file that contains charts and formulas. I'm using the Template package to populate the template with data. However, I'm encountering an issue where the data is being updated correctly, but the cells where formulas are left blank. There should be calculated data in them.

Specifically, in the row, the formula cells are being represented like this: [ 'E' => [ 'v' => (formula is here, not data, should be data), 'f' => (formula is here) ] ]

Additionally, the charts in the file are not loading correctly because the formulas haven't been executed and haven't retrieved data from other sheets.

To resolve this, I need to find a way to trigger the formulas to run before I download the updated file. How can I ensure the formulas are properly evaluated in the output file?

[aVadim483]

Are you transferring formulas from the template to the output file? I haven't been able to reproduce this bug yet. Can you give me an example file so I can try to reproduce it?

[Raman117]

$excel = TemplateExcel::template($templateLocation, $fileName); $sheet = $excel->getSheet('تقييم ECC.2'); $rowTemplate = $sheet->getRowTemplate(25); $data['F'] = 'غير مطبق - Not Implemented'; //$data['E'] = '=IF(F25="مطبق كليًا - Implemented",3,IF(F25="مطبق جزئيًا - Partially Implemented",2,IF(F25="غير مطبق - Not Implemented",1,0)))'; $sheet->replaceRow(25, $rowTemplate, $data); $excel->save();

        this is all the code i have
        TemplateExcel is just referenve name

        so that data['E'] is commented and thats the formulae

        one formula changes data or another in a way

        when i goto excel i can click on calculate worksheet formula and everything works
        is there a way to calculate these formulas before writing/saving?

        also the excel file download xlsx isnt compatible in mac/windows excel how can i solve that too?

        thanks for responding

[Raman117]

in 'E', 'v' is string and it should be number

[aVadim483]

The library writes the output file row by row and does not store the values of the written cells in memory, so it cannot calculate the values of formulas. Excel recalculates the values when open the file

[Raman117]

thanks for responding. The aVadim483/fast-excel-templator package is great, but when it writes/saves the file, it doesnt pre-calculates all the formulas.

I found that one in phpSpreadsheet.

Is there something similar here? Or can this functionality be added to the fast-excel-writer manually?

I tried to add it , but the pre-calculate didn't work for me. maybe I did something wrong it's in phpspreadsheet's save() function somewhere inside as ->preCalculate();

The reason I need this is that it's the only package that allows me to use a template excel file without changing the images, charts, and design.

Can you please help me figure out a way to get the pre-calculation working? I'd really appreciate your assistance on this.

[aVadim483]

Yes, as far as I know, the phpSpreadsheet library can do this. This library works like this: it loads all cells into memory, manipulates them, then saves them to a file. Therefore, it can perform formula calculations. But this consumes a lot of memory and is slow.

My libraries work differently. They read and write Excel files row by row. When the library reads or writes a row, it no longer has the previous row. This reduces the ability to manipulate cells, but saves memory and gives very high processing speed. And since the formula may reference cells that have already been unloaded or which have not yet been loaded, it is not possible to add a pre-calculation of the formula to my library. But when the saved file is opened in Excel, Excel recalculates all cells with formulas and saves the calculated values in the file

[Raman117]

hey is there a way to add images in chart through templator? like i want to add image in sheet 4 of in A20 like this

[aVadim483]

Not yet, but there are plans