What is the best library in python to deal with excel files

Question

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.

Answer 1

XlsxWriter and Xlwings are the best, in my experience. Both of them run quickly and rapidly, and they offer good Python and Excel compatibility.

There are two versions of Xlwings: Free and Pro (paid version). The free version has all of the features and can do practically anything with an Excel file. You can obtain extra capability and support with the subscription version, which developers (not average users) don't require most of the time.

XlsxWriter, on the other hand, is a fantastic option, and its user base is rapidly expanding. It includes everything you'll need to deal with an excel file.

Both of them are simple to set up using pip and conda.

Other libraries, such as xlrd and xlwt, were built to handle old version (.xls) files in the past. They don't even come close to the other two libraries I mentioned.

OpenPyXl is also a good library that can take care of most of your needs. More funding is required for the library to expand. It is not yet mature, in my opinion.

Reading and writing data to an Excel file is also possible with the Pandas and pyexcel libraries. Pandas is my preferred library since it is a mature and fast library that can handle large amounts of data. Working with pyexcel is more difficult because it is a wrapper API that is not as capable as Pandas.