Secure write of formulas

[Matthew Hegarty]

We've had an issue raised re disabling the writing of formulas when exporting Excel files

I just wanted to check if there is a best practice for doing this?  Is there a preferred way to safely write Excel formulas?

I've read that prepending a single quote to an excel field will force the formula to be written as a string.

I've read through existing issues and searched the forum.  This issue has come up before but AFAICT it was not implemented yet?

[Charlie Clark]

On 21 Feb 2023, at 13:00, Matthew Hegarty wrote:

I've read that prepending

<https://github.com/jazzband/tablib/issues/257#issue-191732176> a single

quote to an excel field will force the formula to be written as a string.

I've read through existing issues and searched the forum. This issue has

come up before

Openpyxl has always followed Excel's practice of treating strings that start with = as formulae. This is very convenient and probably suitable in 99.9% of the cases. It doesn't do any additional processing or validation of formulae, though it does at least now check that formulae are syntactically correct. And, as it doesn't evaluate formulae itself, it's immune from any such poisoning.

<https://groups.google.com/g/openpyxl-users/c/Z4QBuFYtMo0/m/VIkcmGszBQAJ>

but AFAICT it was not implemented yet?

It's not openpyxl's job to do this and I'm not going to maintain any code that would. It's fairly straightforward to write a monkey patch that would work this; my suggestion would be to strip the = from the start of a string and rebind cell.value = cell.value.replace("=", "") if cell.data_type is "f" and if cell.value.startswith("f").

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

[Matthew Hegarty]

Hi Charlie

Thanks for the quick response and your suggested patch.

Regards

Matt

[Charlie Clark]

On 21 Feb 2023, at 14:48, Matthew Hegarty wrote:

> Hi Charlie
>
> Thanks for the quick response and your suggested patch.

You should try it first…

If speed is of the essence you could probably rewrite the XML while streaming. But that would be way more code…