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.
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.
Best Excel Library?
Reading/parsing Excel (xls) files with Python - Stack Overflow
Python library for handling Excel files (xls | xlsx) - Stack Overflow
Python library for reading and writing both .xls and .xlsx files.
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
I highly recommend xlrd for reading .xls files. But there are some limitations(refer to xlrd github page):
Warning
This library will no longer read anything other than .xls files. For alternatives that read newer file formats, please see http://www.python-excel.org/.
The following are also not supported but will safely and reliably be ignored:
- Charts, Macros, Pictures, any other embedded object, including embedded worksheets. - VBA modules - Formulas, but results of formula calculations are extracted. - Comments - Hyperlinks - Autofilters, advanced filters, pivot tables, conditional formatting, data validationPassword-protected files are not supported and cannot be read by this library.
voyager mentioned the use of COM automation. Having done this myself a few years ago, be warned that doing this is a real PITA. The number of caveats is huge and the documentation is lacking and annoying. I ran into many weird bugs and gotchas, some of which took many hours to figure out.
UPDATE:
For newer .xlsx files, the recommended library for reading and writing appears to be openpyxl (thanks, Ikar Pohorský).
You can use pandas to do this, first install the required libraries:
$ pip install pandas openpyxl
See code below:
import pandas as pd
xls = pd.ExcelFile(r"yourfilename.xls") # use r before absolute file path
sheetX = xls.parse(2) #2 is the sheet number+1 thus if the file has only 1 sheet write 0 in paranthesis
var1 = sheetX['ColumnName']
print(var1[1]) #1 is the row number...
I know this thread hasn't been active in a while, but I thought it would be nice to add an answer here since I made a new solution to this problem.
I had this same issue so I went ahead and created a small library that includes python-excel (xlrd, xlwt) and openpyxl within it. You can find it here: https://github.com/camyoung1234/spreadsheet
Then to use it you type the exact same code as openpyxl, except you replace openpyxl with spreadsheet. When you load and save files it looks at the extension and determines which library to use for handling it.
To install it just download it, extract it, rename the folder spreadsheet-master to spreadsheet and place it in PythonXX/Lib/site-packages/ (I've only tested with Python 2.7 but it should work with others)
The README has a few examples to help you get started.
Python excel looks like a go: http://www.python-excel.org/
Also OpenPyXl may have the features you need: http://packages.python.org/openpyxl/