COMPAT: Break in openpyxl

[tesla1060]

• 3.4 build is now using current version of openpyxl (2.0.2), fix for this, see here: https://travis-ci.org/pydata/pandas/jobs/25510797
• change ci/requirements-2.7.txt to use last version (1.8.6); will need same import fixes

When I try to use DataFrame to_excel, I was given the following error. I am using Pandas 0.13.1, openpyxl 2.0.2

from openpyxl.style import Style
ImportError: No module named style

[jreback]

I think that openpyxl broke back-compat AFAICT, here's my message to them: https://groups.google.com/forum/#!msg/openpyxl-users/oSNDZe6_YLg/gOXBHifN-C8J

I would suggest for now downgrade to the 1.x series (1.8.6)

it didn't seem to be trvial to fix pandas, though a PR would be welcom

[jreback]

you can also install xlsxwriter which is a faster writer for excel

[jreback]

cc @jmcnamara @jtratner @cpcloud @TomAugspurger ?

[neirbowj]

I'm starting to dig into this now. First stumbling block: I want to get Travis-CI to start testing against both versions of OpenPyXL, but I just learned about how install.sh grabs a bunch of things from http://pandas.pydata.org/pandas-build/dev/wheels/. Any suggestions how to handle this?

[jreback]

don't worry about the wheels
simply change ci/requirements-2.7.txt and change the version (the 3.4 build doesn't have a version because it will always pull the latest), maybe change 2.7 and 3.3 if u want (leave some of the old ones too)

u can do several versions of u want (in different builds)

I build the wheels - but since these r not compiled modules it doesn't make much difference (if it can't pull the wheel it will just get from pypi)

[neirbowj]

@jreback: Thank you for the fast response.

Sorry if I'm being dense. Are you suggesting I test against both versions of OpenPyXL by changing some reqt's but not all, and thereby having something like:

• python-2.7 + openpyxl-2.0.2
• python-3.3 + openpyxl-1.6.2

...but not...

• python-2.7 + openpyxl-1.6.2
• python-3.3 + openpyxl-2.0.2

?

This was my initial attempt: neirbowj/pandas@3b36527c

[jreback]

no don't add any builds (eg don't modify Travis.yml)
just edit ci/requirements-2.7.txt (and 3.3) (one line each - just change the version)
for say 2.02 and 1.86 (u can pick relevant ones)

pandas takes so long to build/run (even with a lot of caching) that we limit to 5 builds
plus a couple of more that run after (but don't affect the fail/ success)

[neirbowj]

Got it. Onward I merrily stumble.

[jreback]

haha np

that said if I think we ought to be testing different versions of other libs

pls say so

[jmcnamara]

On a different tack I've started to look at putting in a openpyxl backward compatible fix for this. It isn't trivial but it is doable.

The main issue is that the style objects used by openpyxl are now immutable after creation. However, that just means that we will have to implement a solutions similar to the ones already used by the xlwt and xlsxwriter engines.

I'll post a separate PR when I have something working.

[jreback]

@neirbowj any luck with this?

[neirbowj]

@jreback A little. I didn't have any time to work on it yesterday, and I won't today either. Tomorrow is looking promising.

I've gotten as far as teaching the unit test to be version aware, and teaching pandas.io.excel._OpenpyxlWriter to choose the best implementation of _convert_to_style() at class definition time. However, I haven't actually implemented the v2 flavor of that method, and am getting a little wrapped around the axle trying to figure out the best way to do it, and how to handle the style merging behavior inside the write_excel caller.

[jreback]

ok great

at least for 0.14.0 i would like it to not break
would be ok with raising a helpful error message as well
(in case the fix is going to take too long)

lmk on progress

[neirbowj]

I'll keep that in mind. Thank you.

[jreback]

@neirbowj not harassing, but .... :)

[neirbowj]

Today is still looking promising. When I start on it after $WORK, the first thing I'll do is make a simple patch that detects OpenPyXL 2, fails the unit test, and informatively disables _OpenpyxlWriter.

[jreback]

that sounds good thanks

[neirbowj]

Finishing for tonight. Will continue tomorrow afternoon.

[jreback]

ok...lmk when you are ready for us to take a look

[Themanwithoutaplan]

@jmcnamara re. immutable styles - we will probably modify these in 2.1 as the current implementation is a big performance hit. Client code won't really notice the difference but you won't have to be as verbose as it currently is. Performance will then be a closer to what can be reasonably be expected.

[tomanizer]

Has there been any development on this? Or is there a workaround to get it to work without downgrading?

[jreback]

this was fixed in 0.14.1. So any version of pandas >= 0.14.1 should work (0.15.1) is the current.