Office 365 Excel dynamic arrays support?

[Jason Blair]

Hello,

I am wondering if there is anything in the planning to support the new Excel features currently included in Office 365 version of Excel, specifically dynamic array support.

I have an .xlsx file which includes dynamic array functions, for example:

=FILTER(FILTER(INDIRECT(B2);(INDIRECT(B3)=1)*(INDIRECT(B4)<>""));(Data!A1:T1="Date")+(Data!A1:T1=C1))

which returns two columns of values, but when opened and then saved with openpyxl, the function is wrapped in curly braces like so:

{=FILTER(FILTER(INDIRECT(B2);(INDIRECT(B3)=1)*(INDIRECT(B4)<>""));(Data!A1:T1="Date")+(Data!A1:T1=C1))}

which is the old excel way of indicating a control+shift+enter (CSE) array.

Adding these curly braces breaks my workbook.

Is there a plan for support of the new dynamic arrays or, is there currently a way to prevent the addition of the curly braces when I open the workbook in openpyxl?

Thank you for your attention,

Jason

[张衡]

Hello, about curly braces, you need to check which version of Microsoft office is installed on your.

Because I don't often go to the forum in China, if you want to discuss this issue in depth, please leave a message by email. My email is: zh13...@outlook.com

[Charlie Clark]

On 30 Sep 2022, at 10:21, 'Jason Blair' via openpyxl-users wrote:

Hello,

I am wondering if there is anything in the planning to support the new

Excel features currently included in Office 365 version of Excel,

specifically dynamic array support.

Any particular version of MS Excel is not a target for the library but anything that can be done in OOXML can probably done with Openpyxl, though some things may require additional development.

I have an .xlsx file which includes dynamic array functions, for example:

=FILTER(FILTER(INDIRECT(B2);(INDIRECT(B3)=1)*(INDIRECT(B4)<>""));(Data!A1:T1="Date")+(Data!A1:T1=C1))

which returns two columns of values, but when opened and then saved with

openpyxl, the function is wrapped in curly braces like so:

{=FILTER(FILTER(INDIRECT(B2);(INDIRECT(B3)=1)*(INDIRECT(B4)<>""));(Data!A1:T1="Date")+(Data!A1:T1=C1))}

which is the old excel way of indicating a control+shift+enter (CSE) array.

Adding these curly braces breaks my workbook.

See https://openpyxl.readthedocs.io/en/3.1/usage.html#id1

Is there a plan for support of the new dynamic arrays or, is there

currently a way to prevent the addition of the curly braces when I open the

workbook in openpyxl?

The curly brackets are one of the annoying red herrings in the GUI. You need to be prepared to look at the source XML to see what's actually going on, including formula prefixes if necessary.

Openpyxl has traditionally had minimal support for shared formulae and, by extension, array formulae through a rather clumsy ws.formula_attributes dictionary. This is changing in 3.1 which makes array and data table formulae first class objects, though to be honest, these are too cumbersome to create manually so the aim is preserve them in existing files and they are editable.

Charlie

--
Charlie Clark
Managing Director
Clark Consulting & Research
German Office
Waldlehne 23
Düsseldorf
D- 40489
Mobile: +49-178-782-6226

[Jason Blair]

Thank you for this quick response.  Not what I was hoping to hear, but I appreciate you taking time to answer.

Cheers,

Jason