5

I'm using OpenPyxl to create and modify an Excel sheet. I have the following formula in Excel:

=(SUM(IF(LEFT(Balances!$B$2:$B$100,LEN($B4))=$B4,Balances!$D$2:$D$100)))

This formula which is an "array formula" is working but in order to write it by hand, I have to finish with CTRL+SHIFT+ENTER (because it's an array formula). This transform then the formula as follow:

{=(SUM(IF(LEFT(Balances!$B$2:$B$100,LEN($B4))=$B4,Balances!$D$2:$D$100)))}

I want to be able to write this formula via OpenPyxl with the following code:

    sheet.cell(row=j, column=i).value = '{=(SUM(IF(LEFT(Balances!$B$2:$B$100,LEN($B4))=$B4,Balances!$D$2:$D$100)))}'

However, it doesn't work. OpenPyxl can't manage it. It give me the formula written but not working.

I could do it with XLSX Writer https://xlsxwriter.readthedocs.io/example_array_formula.html

However XLSX writer doesn't work with already created files.

I don't see which path to follow.

Jason Aller
  • 3,541
  • 28
  • 38
  • 38
Alex Dana
  • 1,076
  • 3
  • 14
  • 30
  • I already saw this post but don't understand how to make it work https://stackoverflow.com/questions/52488989/keep-the-nature-of-array-formulas-when-using-openpyxl – Alex Dana Jul 31 '19 at 20:44

3 Answers3

6

Use the worksheet.formula_attributes to set the array formula. Place the formula in the desired cell, A1 for this example. Then set the formula_attributes to the cell range you want to apply the formula to.

ws["A1"] = "=B4:B8"
ws.formula_attributes['A1'] = {'t': 'array', 'ref': "A1:A5"}
ConSod
  • 743
  • 8
  • 18
  • please, can you explain, how the attribute `ref` work? I've put for example a formula `ws['D1'] = "=SUM(A1:A5*B1:B5)"` and then `ws.formula_attributes['D1'] = {'t': 'array', 'ref': "D1:D1"}`. This worked, but can I always set `ref` to the cell's coordinate? – Vertex Feb 21 '20 at 13:54
  • I've found a description of the `ref` attribute [here](https://learn.microsoft.com/en-gb/dotnet/api/documentformat.openxml.spreadsheet.cellformula?view=openxml-2.8.1) "Range of cells which the formula applies to.[...]" but this doesn't helps me so much :) – Vertex Feb 21 '20 at 14:44
1

In case solution provided above does not work, check whether you are using english name of functions in your formulae.

In my case I have been using czech function name and although formulae works if inserted manually, it did not work when inserted via openpyxl.

Switching to english name of the function solved the issue!

Sukondeez Nats
  • 26
  • 3
0

Update: Version 3.1 added support for legacy style arrays (no dynamic array support yet). See Openpyxl docs

This removes the need to use

ws.formula_attributes

You still need to know how large the result set is. If you are using say FILTER you need to add a prefix like this

formula=formula.replace('FILTER','_xlfn._xlws.FILTER')

Original comment:

In my case the formula was using arrays for intermediate results before summarizing with a MAX. The formula worked OK when typed in but not when inserted via openpyxl. Office 365 version of Excel was inserting the new implicit intersection operator, @, incorrectly.

formula: ="Y" & MAX(tbl_mcare_opt[Year]*(tbl_mcare_opt[Who]=[@Who])*(tbl_mcare_opt[Year]<=intyear(this_col_name())))

It turns out that the properties needed to be set, as above. This allowed Excel to correctly interpret the formula. In my case the ref turned out to be just the single cell address.

I was able to determine that the formula was using dynamic arrays with a regex. If it was then I added the formula properties.

        # provision for dynamic arrays to be included in formulas - notify excel
        if is_formula(values[cn]):
          regex_column=r'[A-Za-z_]+(\[\[?[ A-Za-z0-9]+\]?\])'
          pattern=re.compile(regex_column)
          matches=pattern.findall(values[cn]) 
          if len(matches): # looks like a dynamic formula
            address=get_column_letter(cix)+str(rix)
            ws.formula_attributes[address]={'t':'array','ref': address}
Cronical
  • 1
  • 2