Formulas in Excel

I don’t officially support formulas (yet). But it is something I have ideas for. Not sure if free or pro yet to be honest. We’ll see. ??

But I have a sneaky hook build in (ssshhtt). It’s called: gfexcel_renderer_cell_properties.
But I just realized I have not implemented this properly. So to work with it you need to edit line 314 in AbstractPhpExcelRenderer.php to say:

gf_do_action(
            ['gfexcel_renderer_cell_properties'],
            $cell,
            $value
        );

So the hook needs to be wrapped in an array.

It has 2 params, so you have to add it with 10, 2 on the add_filter to use both. You can directly “speak” to the cell in from phpspreadsheet (the underlying library).

I’m not sure if this will work but you can have a try (using add_action because the hook is also a do_action):

add_action('gfexcel_renderer_cell_properties', static function(\PhpOffice\PhpSpreadsheet\Cell\Cell $cell, $value) {
    if ((string) $value === 'Your formula goes here exactly') {
        $cell->setDataType(\PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_FORMULA);
    }
}, 10, 2);

But this is highly experimental, and I cannot give you any guarantees this will work how you want. (I’ll fix the hook in the next release).

• This reply was modified 4 years, 2 months ago by Doeke Norg. Reason: line position
• This reply was modified 4 years, 2 months ago by Doeke Norg.

Swanky! That worked exactly like I needed it to. So, I’m guessing on this action it checks every cell against the string copy, so that I would compare it to every formula I’ve got in the excel sheet…I just want to be sure I understand how the action is working, basically whether or not to duplicate the add_action for every string comparison vs. the way I think it works (which is to compare every cell to the chosen formulas I’ve got, so I just add those comparisons to the add action).

@froydman Cool.

This action indeed runs on every cell in the excelsheet. Which isn’t very productive, but with a current lack of better support it is what it is.

You could rewrite it like this to make ik more useful for multiple formulas:

add_action('gfexcel_renderer_cell_properties', static function(\PhpOffice\PhpSpreadsheet\Cell\Cell $cell, $value) {
    if (in_array((string) $value, [
        'Your formula goes here exactly',
        'Next formula',
        // etc...
    ]) {
        $cell->setDataType(\PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_FORMULA);
    }
}, 10, 2);

I’ll mark this topic as resolved. Enjoy!