Array Formulas : how can I see the actual dynamic array formula?

[Kurt Schmitz]

I'm trying to see the array formulas within an excel spreadsheet. 

I can extract the array_formulae dictionary:

try:
    ws_af = ws.array_formulae
except:
    print("no array formulae found")
else:
    print(ws_af)

And the dictionary looks like this: 

{'B12': 'B12:B19', 'D12': 'D12:D19', 'E12': 'E12:E19'}

This shows me the Base/Anchor cell for a Dynamic array formula, and the spill range.

However it does not reveal the actual formula.

In the XML for the worksheet, the values are there

<row r="12" spans="1:8">

<c r="A12" s="44">

<v>1003</v>

</c>

<c r="B12" s="45" t="str" cm="1">

<f t="array" ref="B12:B19">VLOOKUP(A12:A19,'Product List'!A2:B13,2,FALSE)</f>

<v>Bed Frame (queen)</v>

</c>

<c r="C12" s="40">

<v>1</v>

</c>

<c r="D12" s="46" cm="1">

<f t="array" ref="D12:D19">VLOOKUP(A12:A19,'Product List'!A1:C13,3,FALSE)</f>

<v>115.99</v>

</c>

<c r="E12" s="41" cm="1">

<f t="array" ref="E12:E19">D12:D19*C12:C19</f>

<v>115.99</v>

</c>

</row>

However, I cannot find a way to access and see these values using openpyxl (version 3.1.3, or version 3.2.0b1)

All I get is a frustrating data structure reference that I don't know how to use.

<openpyxl.worksheet.formula.ArrayFormula object at 0x000001FF72218940>

Any suggestions?

[Charlie Clark]

On 11 Jun 2024, at 17:35, Kurt Schmitz wrote:

And the dictionary looks like this: 

{'B12': 'B12:B19', 'D12': 'D12:D19', 'E12': 'E12:E19'}

This shows me the Base/Anchor cell for a Dynamic array formula, and the spill range.

However it does not reveal the actual formula.

Have you looked at the documentation?

for coord in ws.array_formula:
	print(ws[coord].text)

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

[Kurt Schmitz]

Thanks for the suggestion Charlie. 

When I run the proposed code I get an error:

    for coord in ws.array_formulae:
        print(ws[coord].text) 
     AttributeError: 'Cell' object has no attribute 'text'

Is there an import I'm missing?

Regards, Kurt 

-- 

Kurt Schmitz, Ph.D., PgMP

Clinical Associate Professor, Computer Information Systems

J. Mack Robinson College of Business | Georgia State University

55 Park Place, Room 1739 | Atlanta, GA 30303 |USA 

[Kurt Schmitz]

OK, I've been playing with your (Charlie's) proposed code.

With a slight modification I have my answer

     ws[coord].value.text   gives  me the actual array formula