ENH: Implement _Openpyxl2Writer for pandas.io.excel
State: closed

This patch is an implementation of a `pandas.io.excel.ExcelWriter` subclass that is compatible with the style interface of `openpyxl` 2.0.0 or later.

closes #7177


Labels:
name: IO Excel
description: read_excel, to_excel
name: Compat
description: pandas objects compatability with Numpy or Python functions

Comments:
Is their a reason you are not inheriting from the OpenpyXL V1 engine at all?

The reason was that I had to re-implement the bulk of the class anyway, without doing some refactoring, so I decided to implement it quasi-independently first to reduce the drag I was already feeling. I just pushed some more commits that wind up inheriting `__init__`, `save`, and some class attributes.

I'm going on vacation tomorrow, and doubt I will work on this any more for the next ten days. Feel free to accept as-is, defer, or modify as necessary.

@neirbowj ok thanks...this is slated for 0.15 in any event.

Please review, test, and give me feedback.

this makes `to_excel()` break if you have openpyxl v2 installed on your system, plus it exposes the user to some unnecessary complexity in choosing `openpyxl` vs. `openpyxl2`.

``` python
>>> import pandas as pd
>>> df = pd.DataFrame([{'a': 1}])
>>> df.to_excel('output.xlsx')
```

Now this is because of what's going on at the end of `pandas/core/config_init.py` because it assumes `openpyxl` if `xlsxwriter` isn't installed.

Luckily, there's a really easy fix :)
1. change `_OpenpyxlWriter` to be `_OpenpyxlWriter1` (and name its engine '`openpyxl1`').
2. register `_OpenpyxlWriter1` to `'openpyxl'`(so we don't error out on the option)
3. Add an extra `_OpenpyxlWriter` subclass of `_OpenpyxlWriter1` so we don't unnecessarily break people's hacks.
4. in `get_writer` add a conditional check to see if the engine is openpyxl, and if so, pick the correct version:

``` python
if engine == 'openpyxl':
    try:
        import openpxyl

        # with version-less openpyxl engine
        # make sure we make the intelligent choice for the user
        if LooseVersion(openpyxl) < '2.0.0':
             return _OpenpyxlWriter1
        else:
             return _OpenpyxlWriter2
    except ImportError:
         return _OpenpyxlWriter
```

and we still don't have to force an import of `openpyxl` before someone calls `to_excel()`

also, before we can accept this, we need to add openpyxl v2 to the build matrix.

Thank you for the highly constructive feedback. I expect to be able to implement these changes and push some new commits tomorrow evening.

once you have that I'll take a deeper look - didn't have a ton of time to
review. Thanks for putting this together!

On Wed, Jul 9, 2014 at 7:21 PM, neirbowj notifications@github.com wrote:

> Thank you for the highly constructive feedback. I expect to be able to
> implement these changes and push some new commits tomorrow evening.
> 
> —
> Reply to this email directly or view it on GitHub
> https://github.com/pydata/pandas/pull/7565#issuecomment-48558380.

My implementation of the proposed fixes somehow broke `pandas.io.tests.test_excel.raise_wrapper` when openpyxl v2 is available. I'm getting a mess of failures of the form,

```
======================================================================
FAIL: test_tsframe (pandas.io.tests.test_excel.OpenpyxlTests_NoMerge)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/Users/obrienjw/src/github/pandas/pandas/io/tests/test_excel.py", line 1140, in wrapped
    orig_method(self, *args, **kwargs)
  File "/Users/obrienjw/src/github/pandas/pandas/util/testing.py", line 1522, in __exit__
    raise AssertionError("{0} not raised.".format(name))
AssertionError: ValueError not raised.
```

I'm going to make another pass through to try to understand what happened, rebase to the latest master, and push the new commits whether or not I've found or fixed the problem.

I figured it out. `OpenpyxlTests_NoMerge` grew the ability to run against either version of openpyxl, so I just needed to delete its `raise_on_incompat_version` decorator. Tests are passing locally. I'll go ahead with the rebase, retest locally, and then push to this branch.

@jtratner wrote:

> also, before we can accept this, we need to add openpyxl v2 to the build matrix.

`ci/requirements-2.6.txt` already specifies openpyxl 2.0.3, and the 3.4 tests get 2.0.4 implicitly. Is there something more to do?

thats fine

``` python
>>> import pandas as pd
>>> df = pd.DataFrame([{'a': 1}])
>>> df.to_excel('output.xlsx')
```

now works with either openpyxl 1 or 2, and all tests are passing on Travis. I respectfully await further review and direction.

Beautiful - thanks! I'll take a look at it tonight or tomorrow.
On Jul 13, 2014 7:09 PM, "neirbowj" notifications@github.com wrote:

> > > > import pandas as pd>>> df = pd.DataFrame([{'a': 1}])>>> df.to_excel('output.xlsx')
> 
> now works with either openpyxl 1 or 2, and all tests are passing on
> Travis. I respectfully await further review and direction.
> 
> —
> Reply to this email directly or view it on GitHub
> https://github.com/pydata/pandas/pull/7565#issuecomment-48859731.

@jtratner, have you had a chance to look at this yet? I know I'll need to push a commit with some doc and changelog updates. One way or another I'll aim to do that by the end of next week. I want to make sure this gets a bit more time to bake in `master` before the 0.15.0 milestone.

@neirbowj - sorry I went AWOL there. I tested this out and it all looks good - thanks!  Do you have any other doc changes you want to include?

@neirbowj can you rebase and squash?

@neirbowj also need updates to `io.rst` in the excel section and `v0.15.0.txt` showing/explaining new functionaility.

@jtratner @jreback Yes, I will prepare some doc updates and include them in the rebase/squash.

I'm thinking that it might be best to leave the styling capabilities of the ExcelWriter engines as an undocumented feature, and just to leave an example here for ambitious users for now.

``` python
>>> from pandas.io.excel import ExcelWriter
>>> from pandas.core.format import ExcelCell
>>> writer = ExcelWriter('output_openpyxl2.xlsx', engine='openpyxl2')
>>> red_text = {'font': {'color': '00FF0000'}}
>>> dashed_blue_over = {'border': {'top': {'style': 'dashed', 'color': '000000FF'}}}
>>> cell1 = ExcelCell(row=0, col=0, val=3, style=red_text)
>>> cell2 = ExcelCell(row=2, col=1, val='shall be the number', style=dashed_blue_over)
>>> writer.write_cells([cell1, cell2], sheet_name='Antioch')
>>> writer.close()
```

hmm, maybe put that example in the doc-string?

I think you can put it somewhere (and just put experimental on it).

Personally, I'd leave it out... there are a couple open issues about more granular control of Excel output (e.g., different number formats or styling). We might not want to tie ourselves so tightly to the current separation of `ExcelCell` and `ExcelWriter`.

Is there any decision on the question of how much to document? I have a patch ready to go that puts a version of my example above into the doc string for the `_OpenpyxlWriter2` class. Like so:

``` python
    """
    This ``ExcelWriter`` subclass is specialized for OpenPyxl v2.0.0 and
    later. It aims to map all of the style options available natively from
    nested dicts of strings. Interoperability with inherent OpenPyxl
    flexibility and with the styling interfaces of other engines is
    provided through synonyms.

    The following example shows how one might (experimentally) use this
    engine to emit styled cells directly, instead of via ``df.to_excel``.

        >>> from pandas.io.excel import ExcelWriter
        >>> from pandas.core.format import ExcelCell
        >>> writer = ExcelWriter('output_openpyxl2.xlsx', engine='openpyxl2')
        >>> red_text = {'font': {'color': '00FF0000'}}
        >>> dashed_blue_side = {'style': 'dashed', 'color:' '000000FF'}
        >>> styled_top = {'border': {'top': dashed_blue_side}
        >>> c1 = ExcelCell(row=0, col=0, val=3, style=red_text)
        >>> c2 = ExcelCell(row=2, col=1, val='shall be the', style=styled_top)
        >>> c3 = ExcelCell(row=4, col=2, val='number', style=red_text)
        >>> writer.write_cells([c1, c2], sheet_name='Antioch')
        >>> writer.close()
    """
```

This doesn't seem like the kind of thing that would be widely adopted by pandas users. If they want to be able to write arbitrary, styled values to Excel files, wouldn't they just use OpenPyxl directly than by borrowing our internal, leaky abstraction? If we want to provide more control over Excel styling, it would make more sense to me to add it as a feature of `pandas.core.format.ExcelFormatter`, and that goes well beyond the scope of this PR.

I think it's fine to leave this smaller in scope for now and not add that to the docstring - is everything else okay with this pull request at this point? 

From my perspective, yes, it's ready to go.

@jtratner have a look?

I think this looks fine overall and the note on experimental-ness is good.  My 2c: applying styles to excelwriter output is difficult and non-standard by version. xlsxwriter and openpyxl have somewhat different ideas about how things should be styled and I'd argue that it's a losing proposition for pandas to try to impose a consistent API there.

If Openpyxlv2 doesn't support a particular way of setting styles, there's no reason pandas should provide a shim.

Anyways, it looks good and I'm on board with merging.

@neirbowj thanks !
very nice patch 

You're welcome. I appreciate the careful review and feedback.

@neirbowj thanks for putting this together!