ENH: Allow to assign/create custom body_style and header_style property to an instance of ExcelFormatter used in method df.to_excel()
State: closed

- [X] closes #52369 
- [ ] [Tests added and passed](https://pandas.pydata.org/pandas-docs/dev/development/contributing_codebase.html#writing-tests) if fixing a bug or adding a new feature
- [ ] All [code checks passed](https://pandas.pydata.org/pandas-docs/dev/development/contributing_codebase.html#pre-commit).
- [ ] Added [type annotations](https://pandas.pydata.org/pandas-docs/dev/development/contributing_codebase.html#type-hints) to new arguments/methods/functions.
- [X] Added an entry in the latest `doc/source/whatsnew/vX.X.X.rst` file if fixing a bug or adding a new feature.


Labels:
name: IO Excel
description: read_excel, to_excel
name: Styler
description: conditional formatting using DataFrame.style

Comments:
@rhshadrach I'm reading through the linked GitHub issue, as well as @attack68's comments. Do we want to use setter methods to declare values for `body_style` and `header_style`? 

Or are we  letting `Styler` be used to add and define all the custom style?
My preferred option as stated in the issues was to remove the formatting added by default when doing `df.to_excel()`, and make the user use `Styler` to control all of the output formatting.

However, given that might annoy a number of users I would suggest a compromise.

1) Adding `properties` and `setters` as per one of the issues.
2) Default these default properties to no styling later in a major release and document how a user can set these properties to retrieve the original behaviour (bolding  /bordering the header / index cells).    
@attack68 (from https://github.com/pandas-dev/pandas/pull/53973#issuecomment-1617487108)

> However, given that might annoy a number of users I would suggest a compromise.

Is it only the removing of the default style that you feel might annoy users? Or being forced into the Styler in order to add style? Or something else?

> My preferred option as stated in the issues was to remove the formatting added by default when doing df.to_excel(), and make the user use Styler to control all of the output formatting.

Pending a response to the above, I'm +1 on this approach. I think modifying global state should be avoided where ever possible, and this feature seems perfect for the Styler to me.
> Is it only the removing of the default style that you feel might annoy users? Or being forced into the Styler in order to add style? Or something else?

`to_excel` has been around for a while bolding the headers, perhaps users expect that by now? If we could deprecate that and use a keyword instead (like we do in to_latex and to_html) then I would prefer that, e.g. `bold_rows=False/True`. The method could re-route via the Styler implementation to do this (as it does for `to_latex`)

Since the Styler uses the underlying implementation of DataFrame.to_excel, it is a shame that an unstyled Styler ends up with bold headers by default because of the status quo.

> Pending a response to the above, I'm +1 on this approach. I think modifying global state should be avoided where ever possible, and this feature seems perfect for the Styler to me.

It does seem unnecessary to add this to the state variables of the excel writer when Styler can already do this by default and with greater flexibility. Perhaps documenting users to use the Styler is the better approach that making the code more complicated.

@attack68 just to recap, add a parameter to `to_excel` that takes a boolean value representing whether the headers should be bolded. As you said, the value would propagate through the code so that the Styler implementation is responsible for formatting the output. 

For the formatting of the spreadsheet body, that would be controlled via `Styler` as well. 

@rhshadrach Are you good with this?
@rmhowe425 I just reviewed the current arguments for `DataFrame.to_excel`. It is actually *very* complicated to add a `bold_rows` argument that uses the Styler implementation  Not because this is complicated itself, but because you have to ensure all the other argument combinations that might used also result in the same excel file.  So let me save you the bother now - don't attempt it.

@rhshadrach - If we remove styling from `to_excel` and do not bold or create borders for any cells do we need a deprecation cycle for that, even though it isn't actually an option it is just created by default? As part of the deprecation we could write notes in the docs how to replicate similar features with the `Styler.to_excel` example?

> If we remove styling from `to_excel` and do not bold or create borders for any cells do we need a deprecation cycle for that, even though it isn't actually an option it is just created by default?

I'm not sure I would even consider this a "breaking" change; I don't believe removing bolding from the cells could ever break any workflow. Even if it is considered breaking, I would lean toward adding this to the list of breaking changes in 3.0 in https://github.com/pandas-dev/pandas/issues/44823.
@rhshadrach I'll plan on updating my implementation so that spreadsheets have no formatting by default, and I'll update documentation so that we're telling users to use `Styler` for formatting.

This sounds like the changes that we agree on?
> This sounds like the changes that we agree on?

This is what I've suggested, but I think we need to make sure there is a consensus among those involved before saying "we agree".
> * ENH: Allow to assign/create custom body_style and header_style property to an instance of ExcelFormatter used in method df.to_excel() #52369

I have created an issue to highlight this to move forward: #54154 54154
Closing PR as work is continuing on #54154