0

I wrote FILTER formula in excel file by python. But there is formula error when I open that excel file. Code as following,

wb = load_workbook('test.xlsx')
ws = wb['Sheet1']
ws['A2'].value = '=FILTER(A1:H13,H1:H13="N")'
wb.save('test_m.xlsx')

There is no error when running python but there is error(formula) when I open saved 'test_m.xlsx' file. But when I get value by print(ws['A2'].value) and input excel file, it works.

Does anybody know the possible reason?

Thanks in advance..

jaqen
  • 1
  • 1
  • https://stackoverflow.com/a/66010728 – BigBen Oct 17 '22 at 15:55
  • Also see [this comment](https://stackoverflow.com/questions/66008301/symbol-appearing-after-inserting-if-formula-into-excel-using-openpyxl#comment116712216_66010728) by the developer of openpyxl. – BigBen Oct 17 '22 at 15:56
  • Thank you, @BigBen. It really gave me the hope. But I tried `ws.formula_attributes['A2'] = {'t': 'array', 'ref': "A2:L20"}` and it doesn't work as well :( .There is still error when I open saved files and ask to remove the formula.. – jaqen Oct 19 '22 at 01:13
  • You may have more luck with [xlsxwriter](https://xlsxwriter.readthedocs.io/example_dynamic_arrays.html). – BigBen Oct 19 '22 at 01:21
  • Yes. it seems xlsxwriter can do this.. Anyway as you know, xlsxwriter can't open an existing file.. I already finished most of code work with openpyxl, several hundreds of code lines, so I really don't want to re-write the whole code with xlsxwriter from the beginning.. Is there any other possible way? – jaqen Oct 19 '22 at 01:47
  • You might try the results of [this search](https://www.google.com/search?q=dynamic+array+formula+openpyxl). I don't know how much support openpyxl (at least the current version) has for dynamic array formulas. `xlwings` can open an existing file, and should in theory work with dynamic array formulas, though it requires an installation of Excel. – BigBen Oct 19 '22 at 01:59
  • 1
    It took 7 days for me to re-write code with xlsxwriter. Now everything is OK and I'd like to say I love xlsxwriter~!! It runs faster than openpyxl and documentation is more friendly than others. The only cons is it doesn't support read/modify, then I used excel formula to solve. – jaqen Nov 03 '22 at 02:57

0 Answers0