0

Short description of the problem:

I am currently accessing an Excel workbook from Python with openpyxl. I have some dynamic spill formulas in sheet1, like filter(), byrow() and unique(). With the python script, I am doing some operations in sheet2, but I am not touching sheet1 (where the dynamic spill formulas are located). When using workbook.save() method in Python, I experience that the dynamic formulas in sheet1 are ruined and static, not having the dynamic functionality they had before interacting with python.

What can I do? Use a parameter in .save()? Use another method?

Detailed description of problem (with pictures):

I have a workbook called Original, with the following three sheets:

  • nums
  • dynamic
  • dump

In "nums" I have a cell for ID (AA), and a column with some numerical values (picture1).
In "dynamic" I have some dynamic formulas like byrow() and filter() that updates automatically with the values in ID and Values-column of "nums" (picture2). The sheet "dump" is for now empty.

I have a second workbook called Some_data, which have one sheet with a 3-column dataframe (picture3).

I am dumping the 3-column dataframe of Some_data into the empty "dump"-sheet of Original with a Python script, and then using the workbook.save() method to save the new workbook.

The code is here:

import pandas as pd
from openpyxl import load_workbook


Some_data = filepath of the workbook
Original = filepath of the workbook

df = pd.read_excel(Some_data, engine = "openpyxl")

wb = load_workbook(filename = Original)
ws = wb["dump"]

rownr = 2
for index, row in df.iterrows():
    ws["B"+str(rownr)] = row["col1"]
    ws["C"+str(rownr)] = row["col2"]
    ws["D"+str(rownr)] = row["col3"]
    rownr+=1
    
wb.save(filepath of new workbook)

Now, the newly saved workbook's sheet "dump" has now been populated. The problem is that the dynamic formulas in the sheet "dynamic" has been ruined, although the python script does not interact with any of the sheets "nums" or "dynamic". First of all - the dynamic array formulas (like filter) now have brackets around them (picture4), and the dynamic array formulas are not dynamic anymore (there are no blue line around the array when selected, and they do not update automatically; picture5).

I need help with what to do. I want to save the excel-file, but with the dynamic array formulas not being ruined.

Thank you for your help, in advance.

Frode

frickinFrodo
  • 89
  • 6
  • I'm not sure how robust openpyxl's support is for Dynamic Array formulas. Other alternatives may be xlsxwriter, xlwings, or win32com. – BigBen Sep 26 '22 at 13:16
  • @BigBen Thx for your comment. Would I be able to both read and save excel-files with one of those packages you just mentioned? – frickinFrodo Sep 26 '22 at 13:20
  • Yes, that is doable. – BigBen Sep 26 '22 at 13:21
  • @BigBen Do you recommend one of them, that you know would tackle the dynamic formula issue? – frickinFrodo Sep 26 '22 at 13:23
  • `xlwings` or `win32com` would generally require Excel to be installed on the machine. If that's an issue, than I'd try `xlsxwriter`. – BigBen Sep 26 '22 at 13:25
  • As xlwings .Book() does not have a method called iterrows() - do you have any suggestions on how I solve the populating in the for-loop provided in the code? – frickinFrodo Sep 26 '22 at 13:37
  • `iterrows` is a pandas method. – BigBen Sep 26 '22 at 13:37
  • I see @BigBen. Does xlwings have method that can replace the pandas iterrows()? – frickinFrodo Sep 26 '22 at 13:41
  • https://stackoverflow.com/questions/65349008/from-pandas-dataframe-to-excel-with-xlwings – BigBen Sep 26 '22 at 13:43
  • @BigBen, thank you. One question however: When using xlwings and workbook.save(filepath) - I get an error when there is a file in that directory that already has the same name. I want to overwrite though. Do you have an idea? – frickinFrodo Sep 26 '22 at 15:52
  • https://stackoverflow.com/questions/53426763/xlwings-save-and-close ? – BigBen Sep 26 '22 at 15:53
  • Thank you, but the solution there was entering the full filepath inside .save(). I am already doing that, but still receiving the error. – frickinFrodo Sep 26 '22 at 15:55
  • `save` should overwrite w/o prompt. If you're getting an error, likely that file is in use by another process. Btw, [the docs](https://docs.xlwings.org/en/stable/api.html#xlwings.Book.save). – BigBen Sep 26 '22 at 15:57
  • @BigBen, hi again. Thank you so much for your help. I have one last question/error if you should have the time. It is in this link: https://stackoverflow.com/questions/73864007/error-with-xlwings-book-save-saveas-method-of-workbook-class-failed – frickinFrodo Sep 27 '22 at 08:00

0 Answers0