search

alhimik1986 / php-excel-templator

PHP Spreadsheet extension to the export excel file from excel template
MIT License
346 stars 69 forks source link

Option for CellSetterArrayValueSpecial to fill down formulas alongside it #40

Open chris114782 opened 1 year ago

chris114782 commented 1 year ago

If I have a table like so: image

And it is filled using the CellSetterArrayValueSpecial setter, I would like it to carry the formula down with the rows too.

Currently you end up with:

image

Which is not great as the formula is then not used.

I was wondering if it was possible to do this using callbacks on say Threshold but it would need to know how many rows to fill it down and I can't see how to get that in the params.

Any suggestions

chris114782 commented 1 year ago

I am aware I could just set the formula in code, but these templates are to be edited by non developers and I'd like to leave as much excel functionality intact as possible, without having to update the code every time a new template is built.

alhimik1986 commented 1 year ago

Well, you are passing an array to the CellSetter. To find out how many rows to fill, you simply pass the size of this array to the callback function. This will be the number of rows to fill (or maybe number of rows minus one).

If the formula will be edited you can try some solution: insert a template variable with name like {cell_E2_formula} in D2 cell so the cell could be cloneable. And then add some formula to E2 cell. After that copy the formula from cell E2 using event PhpExcelTemplator::BEFORE_INSERT_PARAMS. And then you need to somehow copy the formula so that there is a relative shift of the cells along the rows (using CellSetter callback). I hope the PHPSpreadsheet could make this.

I'm not sure this will work. It has not been tested. But that's all I can help without modifying the code to solve the problem quickly.