Array formulae handling by excel on Mac?

62 views
Skip to first unread message

Lee Don

unread,
Aug 29, 2023, 6:50:38 AM8/29/23
to openpyxl-users

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

unread,
Aug 29, 2023, 8:25:49 AM8/29/23
to openpyxl-users
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

unread,
Aug 30, 2023, 2:18:18 AM8/30/23
to openpyxl-users
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

unread,
Aug 30, 2023, 4:59:30 AM8/30/23
to openpyxl-users
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

unread,
Aug 30, 2023, 6:35:37 AM8/30/23
to openpyxl-users
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

unread,
Aug 30, 2023, 11:10:13 AM8/30/23
to openpyxl-users
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
Reply all
Reply to author
Forward
0 new messages