2

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 writed 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 wich path to follow.

Alex Dana
  • 811
  • 2
  • 9
  • 27
  • 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

1 Answers1

3

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
  • 623
  • 7
  • 16
  • 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://docs.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