4 ms·
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
by nameless912 2y ago
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.
- 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.