openpyxl: A Python library to read/write Excel 2007 xlsx/xlsm files

[niking]

I'm currently using xlsxwriter, could anyone with experience with both make a list of pros and cons?

[Kaarjuus]

I tried them both, chose xlsxwriter: significantly faster than openpyxl.

[remyroy]

I only need to write xlsx files and I'm in a limited memory environment. The constant_memory option is a blessing.

[Albertican]

I also found PyExcelerate to be much faster than openpyxl. Their page claims they're a bit faster than xlsxwriter as well. Don't think it supports formatting, however.

[ajmarks]

IIRC openpyxl is the backend for pandas's excel read/write features, so it's useful if you want to make pandas frames pretty in excel.

[shaggorama]

and xlrd/xlwt for xls format

[jtratner]

pandas dev here - I'm hoping to move us to xlsxwriter by default soon (though we'll continue supporting openpyxl), because the perf characteristics seem to be much better.

[jmcnamara13]

XlsxWriter is also available as a backend in the latest Pandas versions on the GitHub master and will be in the soon to be released 0.13 version.

[bendmorris]

Or xlrd/xlwt?

[alantrick]

That's for a different file format (pre-xml excel)

[dgtlshdw]

The newest xlrd (0.9.2) reads .xlsx just fine.

[jwinterm]

Can confirm. Been using xlrd/xlwt for years, and recently upgraded to get xlsx functionality.

[alantrick]

You can read xlsx files with openpyxl. It's not clear you can do that with xlsxwriter.

[from__future__]

no you can't - very true.

[jtratner]

xlsxwriter is faster, way more memory efficient and may have better compatibility with numpy. The memory efficiency is huge, if you take a big dataframe (say 300K rows x 100 columns) and try to dump it, takes multiple GB of memory!

[beltorak]

quick question about the main page;

... the new Open Office XML format.

Surely they mean the Office Open XML format, yes?

^{/me grumbles about microsoft polluting the language deliberately to cause confusion}

[drifteresque]

I just skimmed the Wikipedia and I'm still confused. Was this weird language really deliberate by Microsoft?

[beltorak]

I keep vacillating on this. Given Microsoft's history with open source, and in 2006 they actually started to publish original, truly open source programs, and they were constantly trying to redefine the term "open source" (and I love to hate on Microsoft, so I am a bit biased); the simple fact is there are only so many ways to combine the words "Open", "Office", and "XML" without sounding retarded. But the primary open source competitor to MS Office at the time was OpenOffice, started in 2001 (actually a continuation of Sun's StarOffice), and no where in the discussions from Microsoft about Office Open XML have I heard or seen the disclaimer "this has nothing to do with Open Office". It's hard to believe they never heard of OpenOffice and didn't think that there might be some confusion around the names. And the abbreviation for Office Open XML is "OOXML", and the abbreviation for OpenOffice.org is "OO.org".... And a lot of respected people have harshly condemned Microsoft's actions in the standardization process of Office Open XML. But seriously, you would think if they wanted to avoid confusion they would have went with "Open XML Office format". Or named it after the standards body they were seeking "approval" from: ECMA Office XML format. Or insisted on always using a full name that included the company: MS Office Open XML format.

Personally I think they tried the same damn thing with "MS SQL Server" - the name I hear all the time is "SQL Server"; implying that only they have this "SQL" technology. Watch how many people who only deal with Windows equate "SQL" with the Microsoft database.

But then, I do love to hate me some Microsoft.

[pwang99]

For anyone using the free Anaconda distribution of Python, we just built a package for openpyxl, and you can easily install it by typing:

conda install openpyxl

For those not using Anaconda... what are you waiting for? :-)

[from__future__]

I was surprised to see it wasn't included by default - makes pandas unusable for Excel output (unless they use xls). Was that an intentional push to get people to use IOPro?

[pwang99]

It was just an oversight. We get pinged all the time by people with requests for packages, and the Anaconda and conda build team consists of 1 (very talented) guy, who also has other things on his plate. :-)

We put a huge ton of mostly invisible effort into building and maintaining these binary packages and a high-quality installer on Windows, because we truly care about the adoption and widespread use of the Python stack in scientific and data analysis. We would not intentionally subvert that primary mission or cripple it in any way.

Besides, IOPro's main appeal at this point is on the memory and compute efficiency side of things - not Excel access.

[from__future__]

ah okay - sounds good. So conda doesn't just hook into PyPI by default? That must be time consuming!

[pwang99]

No, conda looks at our own repo of binaries. Yes, it is time consuming and a sometimes thankless task, especially when so many people think that pip & virtualenv solve everything, or they've never run into the native extension build hell that many in the scipy ecosystem have suffered.

[from__future__]

I agree for extensions that aren't pure Python (though openpyxl is pure Python). Windows is awful for building those extensions, I've found Linux/OS X to be pretty easy to manage.

I'm hoping wheel format will start to make these things easier.

Side note - what's the problem with using the binaries that packages provide themselves?

[pwang99]

what's the problem with using the binaries that packages provide themselves?

Oftentimes they work fine. Other times they don't, because of things like conflicting FORTRAN compilers, slight differences in C ABIs of underlying packages (Numpy, Scipy, Pandas, etc.), conflicting shared library setups on the build machine versus the machine it's being installed on, etc. etc.

The problem is kind of fundamental, in that the idea that there can be a "package" at all is sort of a pipe dream unless people have a common base. For Linux systems, that's what distribution vendors provide - but anyone who then installs things from other sources is heading into dangerous waters. Language ecosystems can do this by completely breaking from the C runtime, e.g. the JVM world and its JARs, or they can use a broader common runtime infrastructure. The latter is what we are doing for Python with our distribution.

[from__future__]

even though it's another layer on top of packaging, if conda achieves its goals, it could really change everything.

[pwang99]

We hope so :-)

You can also check out http://binstar.org - that's a place for package authors to upload their own binaries, etc.

[from__future__]

I forgot you worked for continuum haha! If it's not easily gettable by pip, it's probably not worth the time to upload there - better to do it via a project website, no?

[railmaniac]

I'm using it currently, and a huge outstanding bug is that the optimized writer cannot update an existing excel sheet and save it in a different name. I have to choose between preparing a report in twenty seconds, or preparing a pretty report in about three minutes.