Working with Excel Files in Python | Hacker News Reader

Working with Excel Files in Python

python-excel.org

22 pointskamaraju15 comments

I tried to do this in a previous job, it's _hard_. Our requirements were to use the math in a spreadsheet to calculate some results (basically actuaries created a set of formulae in the spreadsheet, we paste in the data and pull out the result). The best solution we found was to actually utilize LibreOffice and manipulate the spreadsheet using LibreOffice's Python API. There's no other solution that provides relatively complete implementations of Excel's functionality, and also updates cell values correctly taking into account complex formulae. And it was _painful_ and involved a very fiddly docker container image, a custom shell script with some LD_LIBRARY_PRELOAD magic, and some other hacks I don't even remember anymore.

If you have access to .net, the library spreadsheetgear has a pretty complete implementation of the excel calculation engine. Main limitations: no pivot tables, no xlsb support, no VBA or third party addins (obviously).

No pivot tables would have been a non starter on this project, and it _had_ to be python because of an edict from on high :)

I recall reading about xlwings allowing one to work with VBA in Excel through Python.

I realize you've long since moved on, but this type of story is the type of thing that well-received Medium articles are based on. Given the way you've described it, I know I'd be interested to read about this & I don't even do much Excel work in Python. Please consider it.

Were I not also raising a toddler, learning how to throw pottery, and writing a novel on top of my day job I would consider it :) the last two years have taught me though that touching grass is way more important than maintaining a tech blog.

For those that don’t know - xlsx files are just zips. Change the extension and open them to see all the xml lurking within.

For what it’s worth we use xlsxwriter and are happy with it.

I've also used xlswriter and had the same experience - very happy with it. I used it to generate bar and pie charts on one of the sheets.

same with docx. We don't use any docx libraries to generate docx files from templates. we just: unzip, string replace, zip

I wrote some Python scripts last year (with the help of chatGPT) that use openpyxl and they work great.

I've been using openpyxl for years and it's been rock solid. The optimized read or write only modes are really handy, too.

I'm so happy that, when working with .xlsx files, I usually only need the results. That's why I always convert them (put them in a DB) and then do all the work there and export the results.

I read that Pandas works well, too, but I haven't really tried it.

Why so many packages? Does it mean that all of them have some critical flaw, and are so hard to fix (for some reason) that everyone just starts from scratch instead of fixing an existing one?

There is a certain type of office drone that likes to force bad ideas that involve abusing Excel spreadsheets to do something stupid. By doing what they want, you are doing them a disservice. It's like feeding a stray cat or other urban pest.

Job once had a requirement to generate reports in XLSB format.

Failed to find any Linux-based FOSS solution. Had to run a Windows Server.

Reply on news.ycombinator.com