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.
For those that don’t know - xlsx files are just zips. Change the extension and open them to see all the xml lurking within.<p>For what it’s worth we use xlsxwriter and are happy with it.
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.<p>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, <i>and</i> 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.