BUG: `Styler.to_excel` does not export styles and formats correctly

[attack68]

This is a tracker / explainer for the various issues:

• (Styler class doesn't apply formatting from Styler.format when writing to Excel #21221)
• (Styling of column and row headers not reflected when saving to excel.  #25185)
• (Potential issue in Styler.to_excel() with Styler.set_properties() #30008)
• (BUG: Styler.set_table_styles does not apply to Styler.to_excel #34438)

Essentially these issues record 3 things:

Set_table_styles

Styler.set_table_styles is not exported to excel. This will not be changed (at least by me). To write xlsx, excel styling needs to be attached on a per-cell basis, whereas in HTML indirect references can be created in the CSS language that the browser will parse, for example thead th will apply to all header cells in the header section, and tbody th:nth-child(3n+0) will apply to every third header cell in the body section starting with first. Without writing our own HTML to cell translator for the CSS language it is therefore impossible to map and account for all the complex CSS rules that can be used within set_table_styles. This is well documented.

Todo:

• We will instead propose changes that allow styling to header cells, to complement that already in place for body cells, (ENH: Styler.apply(map)_index made compatible with Styler.to_excel #41995)

Exporting formatting

The number of possible formatting constructs allowed by Python is greater than what excel offers. Excel also has specified structures that differ from Pythons structures. It is impossible to dynamically code these relationships in some ambiguous cases. Therefore this will not be implemented.

Todo:

• There is currently a pseudo CSS attribute: number-format which can be used to apply specific Excel based formatting. This should be much better documented with examples.

Border styles bug

Borders in CSS can be specified in many different ways, and the parsing code to translate this into excel's border structure is broken

Todo

• Review the border CSS translation code and propose a solution, if only to document a single way of getting this to work.
  (BUG/ENH: Translate CSS border properties for Styler.to_excel #45312)

Hiding and Concatening

The Styler uses the base implementation of DataFrame.to_excel. It does not do any preliminary filtering and/or alteration of the ctx object to format cells in the right place. It also doesnt react to hidden indexes and/or elements.

Todo

• document some of the missing features in Styler.to_excel.
• review how this can be implemented or insert a series of small PRs gradually improving the consistency.

[jnothman]

At one point I tried changing the backend to use existing CSS parsing libraries so that issues like border specification would be more generic, but those I considered required substantial modification, and I ran out of time to pursue this... What's out there might have changed a lot in four years.

[tehunter]

Regarding border styles, the error in #30008 appears when a border color is defined but border style is not (or border style is none). The source of the error is in pd.io.excel._XlsxStyler where the style mapping (("top", "style"), "top") causes the prop dictionary to set props["top"] = {"color": "#000000"}.

The root cause however is in io.formats.excel under _border_style. Whenever the function returns None, it should instead return "none" as a string. The reason for this is that "none" is a valid border style. When it is set to None, the style gets removed in remove_none during build_xlstyle, which leads to the TypeError.

In summary, I believe replacing each return None with return "none" in _border_style will fix the error. Alternatively, set color to None if the style is none in build_border.

[attack68]

do you know with this solution how to format your styles, e.g.:

• border: 2px solid red;
• border-bottom: 2px solid red + border-top: 2px solid red; etc..
• border-bottom-color: red; + border-bottom-style: solid; + border-bottom-width: medium; + ...

also with xlsxwriter and openpyxl as different writers?

[tehunter]

take