Array formulae handling by excel on Mac?

[Lee Don]

Hi,

I have a question regarding the save operation of WorkBook. It seems that if openpyxl saves worksheet having array fomular, it saves in a way that excel handling it in another way.

Here is the sample code for openpyxl 3.1.2:

-------------

from openpyxl import load_workbook 

wb = load_workbook('openpyxl_save.xlsx') 

wb.save('openpyxl_save_result.xlsx')

---------------

And the file for testing and file for result are as below:

openpyxl_save.xlsx openpyxl_save_result.xlsx

The formula of cell C1 in openpyxl_save_result.xlsx is shown as "{=A1:A10^3}" while it was shown in openpyxl_save.xlsx as "=A1:A10^3" by Excel on Mac.

I checked the underlying xml, openpyxl does not add the {} to array formulae, but why Excel show them differently? The problem I meet is a formula in normal format "=FILTER(xxxxx)" does not work anymore after save because it is changed to "{=FILTER(xxxx)}".

Any hints are highly appreciated! 

Regards

[Charlie Clark]

On 29 Aug 2023, at 12:50, Lee Don wrote:

> I checked the underlying xml, openpyxl does not add the {} to array
> formulae, but why Excel show them differently? The problem I meet is a
> formula in normal format "=FILTER(xxxxx)" does not work anymore after save
> because it is changed to "{=FILTER(xxxx)}".

I know little or nothing about using array or table formulae but Excel seems to detect them automatically, presumably based on the formula name. The XML uses additional attributes to distinguish them because they're applied to a range of cells; in the GUI this is signified using curly brackets.

You can create them manually using the ArrayFormula class:

https://openpyxl.readthedocs.io/en/stable/simple_formulae.html#id1

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

[Lee Don]

Thanks, the way to create them manually does not work for my scenario: the Excel template is provided by others, I just use openpyxl to open it, add some data and save it. I even do not know existence of such Array Formulae, while during save something is changed and the Excel does not work as before data add anymore because of the '{}' added to formulae.

What I can imagine as a workaround is iterating over sheets and use WorkSheet.array_formula to check whether there is any, and let user checking whether their formula working or not afterwards...

[Charlie Clark]

On 30 Aug 2023, at 8:18, Lee Don wrote:

> Thanks, the way to create them manually does not work for my scenario: the
> Excel template is provided by others, I just use openpyxl to open it, add
> some data and save it. I even do not know existence of such Array Formulae,
> while during save something is changed and the Excel does not work as
> before data add anymore because of the '{}' added to formulae.
>
> What I can imagine as a workaround is iterating over sheets and use
> WorkSheet.array_formula to check whether there is any, and let user
> checking whether their formula working or not afterwards...

It's important to understand that openpyxl doesn't do any transformation of the formulae. I introduced ArrayFormula and TableFormula to expose something that Excel buries in the attributes and relies on "magic" in the GUI to handle.

If you think that openpyxl is breaking things then you will need to compare the XML of the worksheets. This is best done using the OOXML Productivity Tool from Microsoft.

Charlie Clark

[Lee Don]

Thanks. I will try to find a Windows environment (I am working on Mac) to try out Open XML Productivity Tool. If I find out something interesting I will update here. 

[Charlie Clark]

On 30 Aug 2023, at 12:35, Lee Don wrote:

> Thanks. I will try to find a Windows environment (I am working on Mac) to
> try out Open XML Productivity Tool. If I find out something interesting I
> will update here.

It's best just running a Windows VM: that's what I do.

Charlie