Python creating excel files

Viewed 51

im trying to generate a excel while not deleting the user configuration. For example you can create here enter image description here

a new view. And save it.

But when im reading the excel file with pandas or anything else and generate the excel 'the view' would be deleted.

Is there a way where I can create the view in python again? Or dont delete the view?

I looked into some other libraries like openpyxl, xlswriter, but i didnt found any option that can do this.

1 Answers

Openpyxl has the functionality to use Sheet Views. I've never used it, so I can't give you specifics. In theory it would allow you to read and rebuild a Sheet View.

Pandas doesn't include that functionality as far as I know. What it does have is the recent ability (and it's also in openpyxl) to append to an existing Excel workbook instead of overwriting.

If you have a Sheet View pointing at a particular sheet, and are adding/editing a different sheet, you could use this and it shouldn't impact the sheet view.

If you are editing the sheet the sheet view is pointing at, then you would need to rebuild the view using Openpyxl (but you could still write to it initially with Pandas if that is easier for you).

The code for appending in Pandas is:

# use ExcelWriter rather than using to_Excel directly in order to give access to the append & replace functions
with pd.ExcelWriter("data.xlsx", engine="openpyxl", mode="a", if_sheet_exists="replace") as writer:
    df.to_excel(writer, 'My Data', index=False)

If you are using openpyxl directly, then workbook.create_sheet(sheet_name) will append a new sheet to an existing workbook.

You may find that you have to use win32com, a module which gives you access to some of the functionality that vba has in Excel. The documentation for Views seems scarce though; all I could easily find where these two:

https://docs.microsoft.com/en-us/office/vba/api/excel.window.sheetviews
https://docs.microsoft.com/en-us/office/vba/api/excel.sheetviews

Related