3 ms·
Working with Excel Files in Python
- deleted 2y ago[deleted]
- aidos 2y agoFor 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.
- matthewfelgate 2y agoI wrote some Python scripts last year (with the help of chatGPT) that use openpyxl and they work great.
- PyWoody 2y agoI've been using openpyxl for years and it's been rock solid. The optimized read or write only modes are really handy, too.
- nameless912 2y agoI 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.
- cm2187 2y agoIf 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).
- nameless912 2y agoNo pivot tables would have been a non starter on this project, and it _had_ to be python because of an edict from on high :)
- tmaly 2y agoI recall reading about xlwings allowing one to work with VBA in Excel through Python.
- markoman 2y agoI 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.
- nameless912 2y agoWere 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.
- Pinus 2y agoWhy 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?
- ocdbg 2y agoJob once had a requirement to generate reports in XLSB format. Failed to find any Linux-based FOSS solution. Had to run a Windows Server.
- snehk 2y agoI'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.
- JoeyBananas 2y agoThere 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.