I have big excel with lot of sheets and formulas interlinked within the sheets. I need to populate input in one sheet using the code and recalculate all the formulas to get my output from another sheet.
I am able to do this with apache POI using Java but it is too slow in formula recalculation. Looking for libraries in Python to do the same.
The best ones as I worked with them are XlsxWriter and Xlwings.
Both of them are working smoothly and efficiently, and they have good compatibility between Python and Excel.
Xlwings has two versions, Free and Pro (paid version). Free version has the complete ability and can do almost anything you need to work with an Excel file. With the paid version, you can get more functionality and support, which developers (not regular users) do not need most of the time.
On the other hand, XlsxWriter is also an excellent choice, and its users' community is growing fast recently. It supports all you need to work with an excel file.
Both of them can be installed simply with pip and conda.
The other libraries, such as xlrd, xlwt are designed in the past for handling the old version (.xls) files. They are not comparable with the other two libraries that I mentioned.
OpenPyXl also is a decent library that can handle most of your needs. The library needs more support to grow. In my opinion, it is not well mature yet.
Pandas and pyexcel libraries are also suitable for reading and writing data to an Excel file. I prefer Pandas because it is a mature and fast library that can handle big data. pyexcel is a wrapper API that is not capable as Pandas, and working with it is more complicated.
PyXLL is a professional library that can handle almost everything a user wants in Excel with Python. One of the famous companies working on Python distributions, Enthought, is supporting the library. Unfortunately, there is no free or community version of it, and you can only choose a 30 days trial of the pro version. After that, you must pay at least $29 per month. It is powerful, but it is an expensive choice for a single developer.
Of course, there are more Libraries, Wrapers and APIs for handling excel files, but I mentioned the most mature and popular libraries.
What is the best library in python to deal with excel files?