How to Read and Write Excel Files in Python Using XLRD

Working with spreadsheets is a crucial part of data analysis and visualization in Python. XLRD is a library for reading data and formatting information from Excel files. In this tutorial, we will see how to read and write Excel files in Python using XLRD.

Step 1: Install XLRD Python Library

To start with, it is necessary to install the XLRD Python library for reading Excel files. It can be installed via pip:

1
pip install xlrd

Step 2: Import XLRD

Once the installation is complete, you can import the XLRD library to begin working with Excel files.

1
import xlrd

Step 3: Open the Excel file

After importing the library, the next step is to open the Excel file. This can be done using the xlrd.open_workbook() function.

1
workbook = xlrd.open_workbook("example.xls")

Step 4: Read the Excel file

To read content from an Excel file, you must specify the sheet you want to read. This could be either by the sheet’s name or its index.

1
sheet = workbook.sheet_by_index(0)

Then, you can use the sheet’s row_values method to get a list of all values in a row. Using a for loop, you can read all the rows in the Excel file:

1
2
for row_idx in range(sheet.nrows):
    print(sheet.row_values(row_idx))

Step 5: Writing to an Excel file

XLRD does not provide a method for writing to Excel files. This can be achieved using another Python library called openpyxl.

Similarly, to XLRD, the openpyxl library can be installed using pip:

1
pip install openpyxl

After installing openpyxl, you can write to an Excel file using a similar process as reading files:

1
2
3
4
5
6
7
import openpyxl
workbook = openpyxl.Workbook()
sheet = workbook.active
data = [["Fruit", "Quantity"], ["Apples", 5], ["Oranges", 3]]
for row in data:
    sheet.append(row)
workbook.save(filename="example.xlsx")

The Entire Code

Below is the entire code used in this tutorial:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# read
import xlrd
workbook = xlrd.open_workbook("example.xls")
sheet = workbook.sheet_by_index(0)
for row_idx in range(sheet.nrows):
    print(sheet.row_values(row_idx))
 
# write
import openpyxl
workbook = openpyxl.Workbook()
sheet = workbook.active
data = [["Fruit", "Quantity"], ["Apples", 5], ["Oranges", 3]]
for row in data:
    sheet.append(row)
workbook.save(filename="example.xlsx")

Conclusion

In this tutorial, you learned how to read and write Excel files in Python using XLRD and Openpyx library, respectively. These are powerful libraries that enable you to interact with Excel data programmatically. With this skill, you can now automate and streamline your data analysis and visualization tasks. Happy coding!