In this article we want to talk about Working with Excel Files in Python: A Guide to Using xlrd for Reading .xls Files.
Introduction
Python is popular programming language and we can use it for different tasks including working with Excel files. One of the popular library for reading data from Excel files in the older .xls format is using xlrd library. In this article we will explore what xlrd is, how to install it and some examples of how to use it in Python.
What is xlrd ?
xlrd is Python library for reading data from Excel files in the older .xls format. It was created by John Machin and it is currently maintained by Python Packaging Authority. xlrd allows you to extract data from Excel files including cell values, formatting and formulas.
How to Install xlrd ?
Installing xlrd is a simple process. You can use pip, the package installer for Python, to install library. Open your terminal or command prompt and type the following command:
|
1 |
pip install xlrd |
This will download and install xlrd and its dependencies.
Examples of Using xlrd in Python
when you have installed xlrd, you can begin using it in your Python projects. these are some examples of how to use xlrd to read data from an Excel file.
1. Opening an Excel file
The first step in using xlrd is to open the Excel file you want to work with. you can do this by using open_workbook() function. this is an example:
|
1 2 3 |
import xlrd workbook = xlrd.open_workbook('example.xls') |
This will open the ‘example.xls’ file and store it in the workbook variable.
2. Accessing a Worksheet
Once you have opened the Excel file, you can access its worksheets using the sheet_by_index() method. This is an example:
|
1 |
worksheet = workbook.sheet_by_index(0) |
This will open the first worksheet in the Excel file and store it in the worksheet variable.
3. Accessing Cell Data
You can access the data in individual cells using the cell_value() method. Here’s an example:
|
1 |
value = worksheet.cell_value(0, 0) |
This will retrieve the value of the cell in the first row and first column of the worksheet.
4. Accessing Cell Formatting
xlrd also allows you to access cell formatting, such as the font and background color. This is an example:
|
1 2 3 |
cell = worksheet.cell(0, 0) print(cell.font) print(cell.background) |
This will print out the font and background color of the cell in the first row and first column of the worksheet.
5. Accessing Formula Results
xlrd can also retrieve the results of formulas in cells. Here’s an example:
|
1 2 3 |
value = worksheet.cell_value(0, 0) if worksheet.cell(0, 0).ctype == xlrd.XL_CELL_FORMULA: value = worksheet.cell_value(0, 0, evaluate_formulas=True) |
This will retrieve the formula in the first row and first column of the worksheet and evaluate it to get the result.
Learn More on Python
- Python-Docx: Creating Microsoft Word Documents with Python
- Python and Microsoft Word: A Beginner’s Guide to Automating Documents
- How to Install docx2python: Python Library for Word Documents
- Merge Microsoft Word Documents with Python Docxcompose
- Mastering Excel Manipulation with Python Openpyxl
- Python Treq: An Introduction to a Powerful HTTP Client Library
- Introduction to Python httplib2 Library
- An Introduction to Python’s urllib Library
- Python httpx: A High-Performance HTTP Client for Python 3
Final Thoughts
xlrd is powerful and famous library for working with Excel files in Python. It allows you to extract data, formatting and formulas from Excel files, which can be useful for wide range of applications. By using xlrd in your Python projects, you can make working with Excel files easier and more efficient. (Working with Excel Files in Python: A Guide to Using xlrd for Reading .xls Files)