Forum


Replies: 5   Views: 69
Function replacement

Posted by KaiVoigt  · 19-02-2024 - 12:30

I am using the sample_1 example in the setCellValue folder.
My xlsx that i use has a simple VLOOKUP function in cell B5.

After executing the sample_1.php, the resulting excel cannot be opened.

It shows an alert "We found a problem with some content in 'example_setCellValue_1.xlsx'".

What is the proper way to remove/replace a function in a cell? Is there a way to handle this yet?

 

I am using phpxlsx v3 at the moment but i dont think 3.5 fixed this. At least i cannot see any hints in the release notes.

Regards,

Kai

Posted by KaiVoigt  · 19-02-2024 - 12:44

When unzipping the xlsx, a calcChain.xml exists in the xl folder. Although no more functions exist. Removing this and zipping the file again, leads to a working xlsx.
 

It seems that the calcChain.xml is not being replaced or edited.

Posted by admin  · 20-02-2024 - 09:04

Hello,

The setCellValue method replaces a cell value keeping all styles (this method works with text contents). Please note that this method calls addCell internally:

$this->addCell($contents, $position, array(), array('useCellStyles' => true));

Maybe you need to use addCell with specific options such as isFunction? Or use addFunction directly? Or the new content can't be added into that specific cell position due to external dependencies in the XLSX?
The current stable version of phpxlsx doesn't update the calcChain.xml file. If ithis file is removed, MS Excel regenerates it when opening the XLSX.

If you send to contact[at]phpdocx.com an XLSX sample and the code you are running that illlustrates your issue. We'll check it.

Regards.

Posted by KaiVoigt  · 21-02-2024 - 10:43

I have sent an example file via mail.
I know that setCellValue uses addCell internally.

Using addCell with isFunction true and without the option to reuse the existing cellStyles leads to the same issue.

The function addFunction is no solution as i want to remove a function. Not add one.

Posted by KaiVoigt  · 21-02-2024 - 11:50

Just as information for everyone else stumbling over this issue. 

Currently you can add functions to a cell, replace functions in a cell, but not remove functions from a cell.

This might be supported in upcoming versions.

Posted by admin  · 21-02-2024 - 18:09

Hello,

Thanks for your feedback. A new method will be developed for the stable release of phpxlsx to remove cell contents.

Regards.