Add an API to create an Excel Table

[jnothman]

Tables provide benefits such as:

• styling with banded rows
• quick filtering, pivot tables and slicers
• column referencing in formulas

Possible API adding as_table to to_excel:

• .to_excel(..., as_table=True):
  • Creates a table from A1 to last cell per https://openpyxl.readthedocs.io/en/stable/worksheet_tables.html
  • Sets showFirstColumn if the frame has a single index
  • Raises an error if columns is a MultiIndex (for now) or is not unique
  • Raises an error if row_to_add_filters is also set
• .to_excel(..., as_table={"name": "TableStyleMedium9", showRowStripes=True}): same, but with a TableStyleInfo set.

[DeepSpace2]

Interesting idea, thanks!

I think the suggested API is a bit problematic when to_excel is used in the following use cases:

• saving multiple dataframes to the same sheet
• saving dataframes to several sheets
• saving a single dataframe to a single sheet but with startrow and/or startcol arguments

And of course any combination of these use cases will be challenging because the data does not start at A1 anymore.

Perhaps this can/should be set on the Styler class level, but of course only when using it to define a style for the entire dataframe (ie StyleFrame(..., Styler(...)) or through a method, sf.style_as_table(...)), but enforcing that will require some introspecting.

BTW, as an intermediate solution, one can use a combination of apply_headers_style and style_alternate_rows to achieve a somehow "sophisticated" design :)

[jnothman]

I don't think this should be handled at a Styler level; the point of a Table is to recognise that a matrix of cells makes a whole, so it is appropriate to apply this at the data frame level, but yes the specifics don't suit those cases.

[DeepSpace2]

I agree, so it seems like sf.style_as_table(...) (or some other name) is the way to go.

[jnothman]

sf.style_as_table(...) seems to be at the sub-dataframe level, no?

[DeepSpace2]

No, each sf (A StyleFrame instance) represents an entire dataframe.

[DeepSpace2]

Upon further inspection, this can be achieved with the already available API, and quite succinctly.

Going with this example, which produces this output:

Consider this code using the existing API:

from styleframe import StyleFrame, Styler

sf = StyleFrame({'Fruit': ['Apples', 'Pears', 'Bananas', 'Oranges'],
                 2011: [10000, 2000, 6000, 500],
                 2012: [5000, 3000, 6000, 300],
                 2013: [8000, 4000, 65000, 200],
                 2014: [6000, 5000, 6000, 700]},
                Styler(font='calibri', font_size=11))
sf.apply_headers_style(Styler(font='calibri', font_size=11))
sf.to_excel(row_to_add_filters=0).save()

which produces this output:

[jnothman]

It appears the same, but:

• The table ribbon section does not appear, which allows for use of Slicers, quick Pivot table generation and styling (if the background of the cells is transparent, see API to style categorical variables #104)
• The columns cannot be referenced by name in formulas as they can with a Table

[jnothman]

I.e. filters are one of a few features available for Tables.