formulas in workbook are being altered when saving

[Chad Dale]

I am attempting to populate a template with data and openpyxl seems to be altering formulas in the sheet when saving.

I am writing values into column A of my sheet, column B then contains the following formula: =IF(A9<>"",TEXTSPLIT(A9,"|"),"") to split the data on | delimiter. Whenever I use openpyxl to save the resulting workbook, the formulas in col B are changed to array formulas ({=IF(A9<>"",TEXTSPLIT(A9,"|"),"")}) and the formula doesn't evaluate properly.

Any suggestions?

[Charlie Clark]

On 10 Jun 2024, at 17:07, Chad Dale wrote:

I am attempting to populate a template with data and openpyxl seems to be
altering formulas in the sheet when saving.

That might seem to be the case, but it isn't.

I am writing values into column A of my sheet, column B then contains the
following formula: =IF(A9<>"",TEXTSPLIT(A9,"|"),"") to split the data on |
delimiter. Whenever I use openpyxl to save the resulting workbook, the
formulas in col B are changed to array formulas
({=IF(A9<>"",TEXTSPLIT(A9,"|"),"")}) and the formula doesn't evaluate
properly.

Without a sample file I can't say very much about this, but Excel does some very odd things with some formulae in the GUI and we do our best to handle this at the file format level. TEXTSPLIT() is not in the original specification so I'm not sure how it works, but if you look at the source on in the cell, you'll see it has to be written with a prefix. If it has been saved by Excel as an array formula, then it will be visible in Openpyxl as an ArrayFormula, but it's also possible that Excel thinks that this is how this kind of formula should be understood.

Charlie

--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Sengelsweg 34
Düsseldorf
D- 40489
Tel: +49-203-3925-0390
Mobile: +49-178-782-6226

[suresh S]

Hi 

I  now about working the in python 

--
You received this message because you are subscribed to the Google Groups "openpyxl-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to openpyxl-user...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/openpyxl-users/8C20E7FF-DFD9-413F-B8BE-C31A5D41865C%40clark-consulting.eu.