ENH: support Styler in ExcelFormatter
State: closed

Pandas is a nice way to export data from Python to Excel.  It even has an internal representation of Excel cell styling.  It also has a data-driven framework for styling Pandas dataframes with CSS.

This PR connects those features together in a rudimentary and experimental way, for advanced users only.  It allows `ExcelFormatter` to accept a `Styler` in place of a `DataFrame` and to provide a `style_converter` callback for conversion from CSS styles to the styles understood by `ExcelWriter`.

While I acknowledge that I am extending a hidden feature (to make it more usable) and that high-coverage conversion of CSS to Excel styles is no small feat so ?all prospective usages will be a hack, I would enjoy using this feature and think others would too.


I wasn't sure how to test this as no tests directly instantiate `ExcelFormatter`. (I could of course alter the `to_excel` parameters to provide the new feature, but I thought it might best remain hidden. WDYT?)

A test follows:
```python

import pandas as pd
from pandas.formats.format import ExcelFormatter


def test_style_converter():
    df = pd.DataFrame({'a': list(range(3))})

    header_style = '!unset'
    # default Styler provided
    formatter = ExcelFormatter(df)
    for cell in formatter.get_formatted_cells():
        if cell.row < 1 or cell.col < 1:
            if header_style == '!unset':
                header_style = cell.style
                assert header_style is not None
            else:
                assert cell.style == header_style
        else:
            assert cell.style is None

    styler = df.style.background_gradient(cmap='Greys', low=.5, high=0)
    expected = ['c6c6c6', '686868', '000000']
    formatter = ExcelFormatter(styler)
    for cell in formatter.get_formatted_cells():
        if cell.row < 1 or cell.col < 1:
            assert cell.style is header_style
        else:
            assert cell.style == {'fill': {'fgColor': expected[cell.row - 1],
                                           'patternType': 'solid'}}

    styler.to_excel('/tmp/out.xlsx', engine='openpyxl')
    with open('/tmp/out.html', 'w') as f:
        f.write(styler.render())

```

 - [x] closes #1663
 - [x] tests added / passed
 - [x] passes ``git diff upstream/master | flake8 --diff``
 - [x] whatsnew entry


Labels:
name: Output-Formatting
description: __repr__ of pandas objects, to_string
name: IO Excel
description: read_excel, to_excel

Comments:
is this intended to address https://github.com/pandas-dev/pandas/issues/1663?
> is this intended to address #1663?

I suppose so. And it seems I hacked a bit quickly and need to fix some test failures.
I couldn't tell whether #1663 had anything to do with `Styler` objects.
also have a look at comments https://github.com/pandas-dev/pandas/pull/7565
> also have a look at comments #7565

I think I've got the gist. Obviously this builds on that support together with `Styler`
# [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/15530?src=pr&el=h1) Report
> Merging [#15530](https://codecov.io/gh/pandas-dev/pandas/pull/15530?src=pr&el=desc) into [master](https://codecov.io/gh/pandas-dev/pandas/commit/f114af045f68ed960cba02d234a959301ad97a79?src=pr&el=desc) will **increase** coverage by `0.03%`.
> The diff coverage is `97.51%`.

[![Impacted file tree graph](https://codecov.io/gh/pandas-dev/pandas/pull/15530/graphs/tree.svg?height=150&width=650&src=pr&token=eZ4WkYLtcO)](https://codecov.io/gh/pandas-dev/pandas/pull/15530?src=pr&el=tree)

```diff
@@            Coverage Diff             @@
##           master   #15530      +/-   ##
==========================================
+ Coverage   90.79%   90.82%   +0.03%     
==========================================
  Files         156      159       +3     
  Lines       50534    50794     +260     
==========================================
+ Hits        45883    46135     +252     
- Misses       4651     4659       +8
```

| Flag | Coverage Δ | |
|---|---|---|
| #multiple | `88.6% <97.51%> (+0.04%)` | :arrow_up: |
| #single | `40.36% <17.6%> (-0.09%)` | :arrow_down: |

| [Impacted Files](https://codecov.io/gh/pandas-dev/pandas/pull/15530?src=pr&el=tree) | Coverage Δ | |
|---|---|---|
| [pandas/io/formats/format.py](https://codecov.io/gh/pandas-dev/pandas/pull/15530?src=pr&el=tree#diff-cGFuZGFzL2lvL2Zvcm1hdHMvZm9ybWF0LnB5) | `95.02% <100%> (-0.1%)` | :arrow_down: |
| [pandas/io/formats/style.py](https://codecov.io/gh/pandas-dev/pandas/pull/15530?src=pr&el=tree#diff-cGFuZGFzL2lvL2Zvcm1hdHMvc3R5bGUucHk=) | `96.36% <100%> (+0.07%)` | :arrow_up: |
| [pandas/core/frame.py](https://codecov.io/gh/pandas-dev/pandas/pull/15530?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvZnJhbWUucHk=) | `97.64% <100%> (-0.02%)` | :arrow_down: |
| [pandas/io/formats/css.py](https://codecov.io/gh/pandas-dev/pandas/pull/15530?src=pr&el=tree#diff-cGFuZGFzL2lvL2Zvcm1hdHMvY3NzLnB5) | `100% <100%> (ø)` | |
| [pandas/io/formats/common.py](https://codecov.io/gh/pandas-dev/pandas/pull/15530?src=pr&el=tree#diff-cGFuZGFzL2lvL2Zvcm1hdHMvY29tbW9uLnB5) | `94.44% <94.44%> (ø)` | |
| [pandas/io/formats/excel.py](https://codecov.io/gh/pandas-dev/pandas/pull/15530?src=pr&el=tree#diff-cGFuZGFzL2lvL2Zvcm1hdHMvZXhjZWwucHk=) | `96.64% <96.64%> (ø)` | |
| [pandas/plotting/\_converter.py](https://codecov.io/gh/pandas-dev/pandas/pull/15530?src=pr&el=tree#diff-cGFuZGFzL3Bsb3R0aW5nL19jb252ZXJ0ZXIucHk=) | `63.54% <0%> (-1.82%)` | :arrow_down: |
| [pandas/core/series.py](https://codecov.io/gh/pandas-dev/pandas/pull/15530?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvc2VyaWVzLnB5) | `94.97% <0%> (ø)` | :arrow_up: |
| ... and [5 more](https://codecov.io/gh/pandas-dev/pandas/pull/15530?src=pr&el=tree-more) | |

------

[Continue to review full report at Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/15530?src=pr&el=continue).
> **Legend** - [Click here to learn more](https://docs.codecov.io/docs/codecov-delta)
> `Δ = absolute <relative> (impact)`, `ø = not affected`, `? = missing data`
> Powered by [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/15530?src=pr&el=footer). Last update [f114af0...c7a51ca](https://codecov.io/gh/pandas-dev/pandas/pull/15530?src=pr&el=lastupdated). Read the [comment docs](https://docs.codecov.io/docs/pull-request-comments).

can you show a picture of what this does? e.g. the excel picture.
While I like the idea of using `Styler` to define Excel styles, I'm not sure about this api.  I do understand that this would be intended to advanced / experimental.

I would prefer it if a default `style_converter` was provided, and extensible in some way.  Handle simple things like background color, text color, etc by default.

okay. I was hoping to pose this as an intermediate step, seeing as we can
surely only support a subset of CSS, ideally corresponding to what is
currently generated through Styler's declarative API. (There are many ways
to specify border styles, colours, etc in CSS.) I am not sure of the time
frame in which I can make a solid attempt at that. I see that providing a
default converter has the advantage of not exposing the internal excel
styling language to users.

Yes, @jreback there seem to be problems with rendering. Will get back to
you shortly.

On 1 Mar 2017 8:07 am, "chris-b1" <notifications@github.com> wrote:

> While I like the idea of using Styler to define Excel styles, I'm not
> sure about this api. I do understand that this would be intended to
> advanced / experimental.
>
> I would prefer it if a default style_converter was provided, and
> extensible in some way. Handle simple things like background color, text
> color, etc by default.
>
> —
> You are receiving this because you authored the thread.
> Reply to this email directly, view it on GitHub
> <https://github.com/pandas-dev/pandas/pull/15530#issuecomment-283160648>,
> or mute the thread
> <https://github.com/notifications/unsubscribe-auth/AAEz63qaoL5UHJpxwZYSryYveorRv4zHks5rhIyWgaJpZM4MORU3>
> .
>

Sorted. I needed a `patternType` and to use `fgColor` rather than `bgColor`.
I have added writing to the test in the PR description above... with paths in /tmp/ so not actually to be used in a test. Output is as follows:

![screen shot 2017-03-01 at 10 12 22 am](https://cloud.githubusercontent.com/assets/78827/23438827/ccca0740-fe67-11e6-9e09-2e0469d8e475.png)

@chris-b1, in the PR description I have also drafted a `CSSToExcelStyle` class with an architecture for a more full-fledged converter. WDYT?
Note that openpyxl cannot support conversion of the `linear-gradient` used for bar chart backgrounds without https://bitbucket.org/openpyxl/openpyxl/issues/771 being fixed.
If we go down @c123w's path, can we have a dependency on [`tinycss`](https://tinycss.readthedocs.io) or [`tinycss2`](http://tinycss2.readthedocs.io/en/latest/) when this feature is used to avoid replicating a full-fledged CSS parser here?

Also, what would be the public API? `df.style.apply(...).to_excel()` seems most natural.
I do have some concern that exporting to HTML and using Excel's import may be appropriate and much simpler in many cases.
@jnothman would not be a problem to have an optional  dep like that
@jnothman - thanks - a couple thoughts.

Your `CSSToExcelStyle` mockup is basically what I was thinking - though I would probably make the handlers return a dict of style rather than mutating.  My concern was that a relatively complex callback is too hard to think about (at least for me!) - something like that, which can be used as an example / extended is probably friendlier.

And to be clear, I personally would be fine initially supporting a tiny subset of CSS (even just font color and backround).  Could take the dependency if needed, although I'm not sure how much it would really add in this case?




``df.style.apply(...).to_excel()`` would be nice.

we de-facto have ``.to_html()`` now via ``_repr_html_`` 

you might want to define this new style sheet as ``_repr_excel_``

@TomAugspurger 
Thanks @jreback for the feedback on API.

@chris-b1:

> though I would probably make the handlers return a dict of style rather than mutating.

I considered this. I mutate in order to take advantage of `setdefault`. This allows setting the `patternType` to `'solid'` only if it was not set to something else. On the other hand, this may be construed as `ExcelWriter` having a bug in that it doesn't default to `patternType='solid'`. I would rather not have to modify `ExcelWriter` every time something like this is needed, so mutating is best, IMO.

> And to be clear, I personally would be fine initially supporting a tiny subset of CSS (even just font color and backround). Could take the dependency if needed, although I'm not sure how much it would really add in this case?

I'm not yet certain about the need for a CSS parser, but as is the way of these things, you can implement something that works for the limited cases you've thought of, but then someone will break it, even just by specifying "red" or "hsl(0, 100%, 50%)" or "rgb(255, 0, 0)" or "rgba(255, 0, 0, 100)" instead of "#ff0000", let alone by inserting a comment or unexpected use of whitespace, etc.

> I personally would be fine initially supporting a tiny subset of CSS (even just font color and backround)

Font and background colour seem a good place to start, followed by borders. Ideally we'd support the bar charting provided by `Styler`, but will have to [wait on `openpyxl` for that](https://bitbucket.org/openpyxl/openpyxl/issues/771).
I've attempted to implement a few handlers. Tricky parsing cases include `font` which can be composed of identifiers, strings for font name (comma separated for a back-off strategy which won't be handled in Excel except to set font family) and numeric quantities (for size). Functions may also be present, at least in theory, and while we would ignore them, splitting on whitespace becomes insufficent. The many expressions of `font` would still need handling with `tinycss`, but at least it would identify the token types and handle string escaping.

Tricky not in terms of parsing but in terms of conversion are elements that require state to be stored that is not naturally represented in the Excel style dicts. One (not very useful) example is that `text-decoration-style` may indicate `single`, `double` etc, but it applies to `underline`, `overline` (not supported in Excel) or `line-through` (double not supported in openpyxl) depending on `text-decoration-line`. So we can provide limited support, or need to maintain state that is not part of the output.
I have wasted far too much time on this, but I think the principled way to go about CSS to Excel conversion for a list of CSS declarations:
1. remove comments, normalize whitespace, ?tokenize and parse function-argument structures
2. expand CSS shorthands (e.g. `font`, `border-width`) to atomic properties
3. override earlier values for same property with later values
4. resolve values relative to parent/initial settings (does not need to be fully fledged for our application, but `font-size: 1.5em` needs to be interpreted)
5. compute excel styles with reference to calculated CSS properties (rather than by processing each CSS declaration in turn)

A full CSS interpreter would also deal with matching selectors to contexts and ordering declarations by specificity.

[`xhtml2pdf`](https://github.com/xhtml2pdf/xhtml2pdf) does some of these things in a different context, but I can't at a glance commend its implementation. I haven't found other Python implementations of this CSS resolution process.
status on this @chris-b1 
@jnothman - thoughts?  I'm not opposed to exposing something small / experimental, like your top comment.  
I'll see if i can give this a few minutes today, but I'm generally quite
time poor atm

On 4 Apr 2017 3:28 am, "chris-b1" <notifications@github.com> wrote:

> @jnothman <https://github.com/jnothman> - thoughts? I'm not opposed to
> exposing something small / experimental, like your top comment.
>
> —
> You are receiving this because you were mentioned.
> Reply to this email directly, view it on GitHub
> <https://github.com/pandas-dev/pandas/pull/15530#issuecomment-291213956>,
> or mute the thread
> <https://github.com/notifications/unsubscribe-auth/AAEz63fcI5va_oL5GpFZwJ5B_srjetU5ks5rsSxMgaJpZM4MORU3>
> .
>

This is quite a beast.

Apart from relying on string values rather than tinycss AST nodes (and hence not handling CSS strings, comments and other quirks), this is now much closer to the ideal design for processing CSS and building Excel styles from it. It's more or less in line with https://github.com/pandas-dev/pandas/pull/15530#issuecomment-283270915.

Unfortunately, the only test case I've used so far is the one in the PR description, again. And there's a hell of a lot to test. I'm not sure in what timeframe I'll be able to do so.
This is close to done for a first substantial attempt. Do you think it's going in the right direction? or a waste of time?
How's this looking in terms of modularity?
In terms of `to_excel`, my preferred design is to create `ExcelFormatter.write(writer_or_path)`. Since almost all of the `to_excel` parameters are for the `ExcelFormatter` constructor this reduces error in documentation and passing variables. It also mimics the `save` on `CSVFormatter` and the `to_*` called by DataFrame on `DataFrameFormatter`. Let me know if you strongly prefer a function in `pandas.io.excel` doing the `ExcelFormatter` construction.

I note that `to_excel`'s `encoding` parameter goes nowhere. Should it be deprecated? In another PR (ideally by someone else!)?
> there are prob some excel tests in test_format.py that should be moved (i see u have a todo)

No, there were no direct tests of `ExcelFormatter` that I could see. All were tested implicitly through `ExcelWriter` in `tests/io/test_excel.py`
Proposal in ExcelFormatter:
```python

    def write(self, writer, sheet_name='Sheet1', startrow=0,
              startcol=0, freeze_panes=None, engine=None):
        """
        writer : string or ExcelWriter object
            File path or existing ExcelWriter
        sheet_name : string, default 'Sheet1'
            Name of sheet which will contain DataFrame
        startrow :
            upper left cell row to dump data frame
        startcol :
            upper left cell column to dump data frame
        freeze_panes : tuple of integer (length 2), default None
            Specifies the one-based bottommost row and rightmost column that
            is to be frozen
        engine : string, default None
            write engine to use if writer is a path - you can also set this
            via the options ``io.excel.xlsx.writer``, ``io.excel.xls.writer``,
            and ``io.excel.xlsm.writer``.
        """
        from pandas.io.excel import ExcelWriter
        need_save = False
        if isinstance(writer, string_types):
            writer = ExcelWriter(writer, engine=engine)
            need_save = True

        formatted_cells = self.get_formatted_cells()
        writer.write_cells(formatted_cells, sheet_name,
                           startrow=startrow, startcol=startcol,
                           freeze_panes=freeze_panes)
        if need_save:
            writer.save()
```
called by `DataFrame.to_excel()` and `Styler.to_excel()`.

Another alternative is to just put this code only in the styler. If there is no style, it won't try to convert. Perhaps the only issue here is that calling `frame.style` does extra importing.
Reason not to go through df.style is that some df's can't be styled.

Is it okay to make `pandas.formats.format.ExcelFormatter` disappear if no longer needed internally, or was it considered public API?