ENH: Support for Excel features when writing

[rhshadrach]

Context

Currently DataFrame.to_excel supports freeze_panes, and there are requests to support other Excel features (#42560). I've gone through the features of openpyxl and Xlsxwriter, here are other Excel features that pandas might support:

• autofilter
• hiding rows/column/worksheets
• password protection
• conditional formatting
• data validation
• dropdown lists
• defined names
• cell comments

Not listed here are styles as I believe the styler supports most of these (cc @ahawryluk).

It seems difficult to justify pandas supporting some of these, but not others. On the other hand, implementing all or even just some of them for each engine adds a lot of complexity and maintenance burden to pandas. I haven't checked, but it may also be the case that some features are supported by certain engines but not by others.

While pandas documents the writer of ExcelWriter as having no public attributes, third party engines document using writer.book e.g. xlsxwriter.

Proposal 1: Add more arguments to write_excel.

Add other arguments to to_excel, such as autofilter, hiding row/columns/worksheets, conditional formatting. At least these three I perceive to be commonly used.

This is the most user friendly option in that it allows users access to these features with a single function call and the ability to switch engines without having to refactor their code. However as mentioned above, this adds complexity to pandas and is a maintenance burden.

Proposal 2: Make writer.book public, prefix all or most other attributes with an _.

This would allow users to use the features that the engine supports directly with ExcelWriter, e.g.

with ExcelWriter("test.xlsx") as writer: 
    df.to_excel(writer)
    worksheet = writer.book.get_worksheet_by_name("Sheet1")
    worksheet...

This would require refactoring the writer.sheets as in current state it can get out of sync with writer.book if e.g. a user does writer.book.add_sheet(...).

This proposal is less complex/maintenance than Proposal 1, more user friendly than Proposal 3.

Proposal 3: Don't support any Excel features.

Users wanting these features would either be required to (a) write their DataFrames directly with the engine of choice or (b) write their DataFrames with pandas, then open a book with the engine of their choice if the engine supports modifying existing Excel workbooks.

Less complex/maintenance than Proposal 2, but also less user friendly.

I am currently in favor of Proposal 2. I also see a mix of Proposal 1 and Proposal 2 as viable, where some features are supported directly as arguments in to_excel with other features require the user to use writer.book for others.

cc @pandas-dev/pandas-core

[twoertwein]

might also help #44868

[twoertwein]

Proposal 2 would go well together with making .book a property: There are quite a few issues where users replace .book with an externally loaded workbook.

[WillAyd]

What would prevent us from extending the styler class to support most of these?

[bashtage]

To me 2 makes more sense as it avoids a lot of complexity while allowing end-users to customize to their heart's content. In the past I have used to_excel followed by my own custom code to add features that cannot be directly accomplished using pandas., essentially 3(b)

[jreback]

+1 on option 2

[ahawryluk]

I'm also +1 on option 2. For what it's worth, we took a similar approach for reading hidden sheets (#19842). We showed how to check for hidden-ness using the engine for that file format without actually adding the feature to pandas for all 4 engines.

…

On Sun., Jan. 23, 2022, 4:07 p.m. Jeff Reback, ***@***.***> wrote: +1 on option 2 — Reply to this email directly, view it on GitHub <#45572 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AMAZB7T3JQWMCXMYUDKRS73UXSCZXANCNFSM5MTU3YRA> . Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>. You are receiving this because you were mentioned.Message ID: ***@***.***>

[attack68]

What would prevent us from extending the styler class to support most of these?

I think some of these would just require a lot of complicated, and non-universal, code to get it working. And it would have have to be coded for Styler, and then parsed by the DataFrame.to_excel, method, duplicating the work.

At the moment styles and number formats to excel is a hacked on component. Styler does not have its own renderer for excel like it does for HTML, LaTeX and String.

For a user keen to do these things, I think its better to just open up some generic API and let them customise. Providing a user guide with good examples is probably worth a lot more. +1 for Proposal 2.

[Dr-Irv]

+1 for Proposal 2. I also think we should expose writer.sheets in addition to writer.book based on some code I wrote a while back.

[m12t]

I don't think this merits a new issue on its own just yet, so I'll ask it here as a comment:

tldr: how to set the ExcelWriter.book and .sheets attributes in 1.5.0 and beyond

I'm one of the users using the .book and .sheets attributes before they became public in 1.5.0. The following code broke with the change from 1.4.4 to 1.5.0

book = load_workbook(template_path)  # use the openpyxl function, load_workbook() to grab the template book
writer = pd.ExcelWriter(out_path, engine="openpyxl")
writer.book = book
writer.sheets = {ws.title: ws for ws in book.worksheets}

After updating to 1.5.0, this causes two AttributeErrors, one on book and the other on sheets

After updating to 1.5.1, setting the book attribute no longer errors, but now gives:

1. FutureWarning: Setting the `book` attribute is not part of the public API, usage can give unexpected or corrupted results and will be removed in a future version

2.   ....
       self.writer.sheets = {ws.title: ws for ws in self.file.worksheets}
   AttributeError: can't set attribute
   

In the What’s new in 1.5.0 there's a quote

The following attributes are now public and considered safe to access.
book
...
sheets
...

Does this mean book and sheets are read-only?

In the source code for 1.5.1, I noticed that there's a setter for book (which explains why no AttributeError was raised there), but no similar setter exists for sheets. Is the public book setter being deprecated already as is alluded to in the FutureWarning?

In essence, what's the recommended process to set the book and sheet attributes on ExcelWriter?

I've found a solution to my problem, and described it on stack overflow for anybody in a similar situation. It doesn't access any private attributes or use deprecated setters, so should be pretty safe going forward.