Styling in DataFrame.to_excel

[wesm]

from mailing list

Hello everyone,

I wonder if it's possible to apply style to the outputed excel file or should I have to code a similar function with my style ?

Maybe by overloadind ?

[gerigk]

openpyxl supports styles but afaik you can't use them with the optimized writer than pandas uses by default. So instead of using sheet.append_row(row) one has to access each cell independently.
You can set the column width by column but attributes like colour or number format have to be set by cell and this slows everything down dramatically.
It's probably not too hard to add faster "by column" styles to openpyxl but unfortunately the project is very inactive (I added cell comments in May and never got any feedback for my pull request).

[aflaxman]

I have been looking into this as well recently, and found this blog post and related gist on the matter.

@gerigk can you add a link to your PR? I can't find it.

@wesm interested in a PR on this? What does it need to do to be accepted?

[jreback]

@aflaxman see this as well: #7565

always interested in a PR!

pls write tests / code / update doc-strings

see here: https://github.com/pydata/pandas/wiki

[aflaxman]

Ha ha, I just looked at the day and month, not the year. Does #7565 expose something analogous to the style parameter in https://gist.github.com/dmvianna/4602492#file-xlpandas2-py-L50 ? I couldn't find it with a quick search.

[jreback]

excel has multiple engines (xlsxwriter, xlwt, openpyxl), so is different for each.

[jreback]

closed by using this PR: #7565

[jtratner]

@jreback #7565 didn't really cover this, still need to decide how we'd actually want to expose styling, I'm thinking per-column styles could get us 90% of the way there. @aflaxman - if you have ideas for how an API ought to work, please feel free to comment here or in a new issue.

[jreback]

@jtratner sure

[aflaxman]

For my purposes, it would get 90% of the way to have a few defaults for highlighting the first row and/or the first column, and bonus points for zebra-striping a la excel. But the case that inspired me to comment on this ticket was a series of tables where there were one or two cells that I need to highlight, and for that I think some sort of table-painter interface would be best. Pandas already has plenty of functionality for this, so maybe just allowing an optional style dict in the .to_excel functions, e.g.

df.to_excel(path, style=style_df)

where style_df.shape = df.shape, and non-empty entries of style_df are style dicts a la the examples here #7565 (comment)

I'll try it out and report back here.

[aflaxman]

Here is a notebook with a start in the direction I am thinking. It makes this:

Comments welcome!

[jreback]

cc @neirbowj

this is using the new openpyxl2 stuff?

[neirbowj]

Yup, it sure looks that way. 😄

[aflaxman]

yes, speaking of which, is the style dictionary documented any more than in the #7565 comments now?

[neirbowj]

@aflaxman: No, it is not. However, the short, short version of the docs would go something like this: any keyword or literal value that the native openpyxl v2 style interface accepts, the pandas style dict should accept as well. Symbolic constants are the main exception that I can think of off hand (e.g. {'color':'RED'}).

So, for example, the default font, per the openpyxl docs, would look like this:

""""
Excerpt --
    font=Font(name='Calibri',
        size=11,
        bold=False,
        italic=False,
        vertAlign=None,
        underline='none',
        strike=False,
        color='FF000000')
""""
font = {
    'font': {
        'name': 'Calibri',
        'size': 11,
        'bold': False,
        'italic': False,
        'vertAlign': None,
        'underline': 'none',
        'strike': False,
        'color': 'FF000000',
    }
}