The best ones, as I worked with them, are XlsxWriter and xlwings. Both work smoothly and efficiently and are compatible with Python and Excel.
Xlwings Has two versions: Free and Pro (paid version). The free version 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 usually need.
On the other hand, XlsxWriter It is also an excellent choice. Its user community has been growing fast recently. It supports everything you need to work with an Excel file.
Both can be installed simply with uv, pip and conda.
The other libraries, such as xlrd and xlwt (unfortunately, the maintainer hasn't updated it since Aug 22, 2017), were designed in the past to handle the old version (.xls) files. They are not comparable to the other two libraries that I mentioned.
OpenPyXl is also a decent library that can handle most of your needs. However, it needs more support to grow. In my opinion, it is not yet mature.
The pandas and the PyExcel libraries are also suitable for reading and writing data to an Excel file. I prefer pandas because it is a mature and efficient library that can handle large datasets. pyexcel is a wrapper API that is less capable than Pandas and more complicated to work with.
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 supports the library. Unfortunately, there is no free or community version of it, and you can only choose a 30-day trial of the pro version. After that, you must pay at least $29 (USD) per month, or $299 (USD) per year. It is powerful, but it is an expensive choice for a single developer.
IronXL is another library that offers a 30-day free trial. However, after that, the cheapest license (Lite) is $749 USD for one year, and you must pay for renewal each year with a discount, which is still expensive. I personally didn't try it, but as I saw what they offer, I don't know why it is so expensive.
Of course, there are more. Libraries, Wrapers and APIs for handling Excel files (for example, a list of some open source libraries can be found in Python for Excel), but the ones that I mentioned are the most mature and popular libraries.
Hi Community, I am new to Python and want do a project in Python with an Excel file. I want to read the data from two different cols and compair each pair to look if it is correct pair or not. What library would you recommend to do this, and what should I take in mind? Because I heard that working with excel is not easy task Thanks in advance
The best ones, as I worked with them, are XlsxWriter and xlwings. Both work smoothly and efficiently and are compatible with Python and Excel.
Xlwings Has two versions: Free and Pro (paid version). The free version 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 usually need.
On the other hand, XlsxWriter It is also an excellent choice. Its user community has been growing fast recently. It supports everything you need to work with an Excel file.
Both can be installed simply with uv, pip and conda.
The other libraries, such as xlrd and xlwt (unfortunately, the maintainer hasn't updated it since Aug 22, 2017), were designed in the past to handle the old version (.xls) files. They are not comparable to the other two libraries that I mentioned.
OpenPyXl is also a decent library that can handle most of your needs. However, it needs more support to grow. In my opinion, it is not yet mature.
The pandas and the PyExcel libraries are also suitable for reading and writing data to an Excel file. I prefer pandas because it is a mature and efficient library that can handle large datasets. pyexcel is a wrapper API that is less capable than Pandas and more complicated to work with.
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 supports the library. Unfortunately, there is no free or community version of it, and you can only choose a 30-day trial of the pro version. After that, you must pay at least $29 (USD) per month, or $299 (USD) per year. It is powerful, but it is an expensive choice for a single developer.
IronXL is another library that offers a 30-day free trial. However, after that, the cheapest license (Lite) is $749 USD for one year, and you must pay for renewal each year with a discount, which is still expensive. I personally didn't try it, but as I saw what they offer, I don't know why it is so expensive.
Of course, there are more. Libraries, Wrapers and APIs for handling Excel files (for example, a list of some open source libraries can be found in Python for Excel), but the ones that I mentioned are the most mature and popular libraries.
Below are a few libraries which do that. Recommend going through them as per your requirements.
PycelformulasPandasxlwtOpenpyxl
They all blend with excel really well. You can try these out.
Additional libraries for Python in Excel
which is best python module for creating excel files? - Software Recommendations Stack Exchange
What's the best excel/csv handler for python?
Need a recommended library for working with excel sheets.
AFAIK pandas only has rudimentary support for Excel files (that is: read or write entire workbooks from its DataFrame). openpyxl on the other hand was specifically made to manipulate Excel files, so that's probably your better pick.
Which Python library is best for lightweight Excel operations?
What should developers consider when choosing a Python Excel library?
What Python library is recommended for working with .XLSB files?
This probably boils down to a matter of taste.
I would recommend you to give preference to packages which still show some activities on the developer side as it might increase the longevity of your code.
Personally I used the packages xlwt and xlrd a lot. The former is for writing and the latter for reading Excel files. I had to process files with around 100'000 lines and memory or speed never was an issue.
For xlrd
Here is the documentation to xlrd and here are some simple examples to get you started in no time.
For xlwt
Here is the documentation. Here and here are some simple examples.
But as I said, it is probably a matter of taste and I'm not saying that the other packages you mentioned are any worse than xlrd. It is just the one I do have experience with and I always was pretty satisfied with it.
Hope that is of some help to you.
Slightly too long for a comment, while not a concrete answer, sorry.
personally, my Excel spreadsheets generally aren't. They are simply data, with no functions or calculations, where Excel an be used as a viewer. This is the simplest case and easily solved my treating the data as Comma Separated Variables (or Tabs, etc; just data with a structure and a delimiter between fields).
Just
import csvthenwith open("file.csv", mode="r", newline='',encoding="utf-8") as csvfile:
reader = csv.DictReader(csvfile)
for row in reader:very occasionally I have had to deal with real Excel (and MS Word) files. The best starting point of this is the excellent eBook “Automate the Boring Stuff with Python”, which you can buy or view for free. Specifically Chapter 14: Excel spreadhseets
if you use Office 365, you can use Python directly within individual cells of your spreadsheet. e.g.
=PY("import pandas as pd; df = pd.DataFrame({'A':[1,2,3],'B':[4,5,6]}); df['C'] = df['A'] + df['B']; df")
Or, you can use VBA to run Python:
Sub RunPythonScript()
Dim objShell As Object
Set objShell = VBA.CreateObject("WScript.Shell")
objShell.Run "python C:\path\to\script.py"
End Sub
Note: This just executes the script; returning results to Excel requires writing them to a CSV or using COM interfaces.
- you might look into XLwings. BUT, while it is free for Open Source projects, it jumps to $1,600 / year for a single developer.
Aaaaaaand, I only just decided to search and there are a slew of useful, and free libraries. The finding of which is left as an excercise for the reader :-)