Introduction to xlrd

xlrd is a Python library for reading data and formatting information from Excel files in the .xls format. It is commonly used for data extraction and manipulation tasks. In this article, we’ll introduce you to xlrd and explore its various APIs with examples.

Getting Started with xlrd

First, make sure to install the xlrd library using pip:

pip install xlrd

Opening a Workbook

To open an Excel workbook, use the open_workbook function:

 import xlrd
workbook = xlrd.open_workbook('example.xls') 

Accessing Sheets

Access a specific sheet using sheet_by_index or sheet_by_name:

 sheet = workbook.sheet_by_index(0) sheet = workbook.sheet_by_name('Sheet1') 

Reading Data from a Sheet

You can read cell data using cell_value:

 value = sheet.cell_value(rowx=0, colx=0) 

To read an entire row or column:

 row_values = sheet.row_values(0) col_values = sheet.col_values(0) 

Getting Sheet Information

Retrieve the total number of rows and columns in a sheet:

 num_rows = sheet.nrows num_cols = sheet.ncols 

Iterating Through Cells

You can loop through cells in a sheet:

 for row in range(sheet.nrows):
    for col in range(sheet.ncols):
        cell = sheet.cell(row, col)
        print(cell.value)

Date and time values

To handle dates and times properly, use xldate_as_tuple:

 import xlrd from xlrd.xldate import xldate_as_tuple
workbook = xlrd.open_workbook('example.xls') sheet = workbook.sheet_by_index(0)
cell_value = sheet.cell_value(1, 0) date_tuple = xldate_as_tuple(cell_value, workbook.datemode) 

Using Formatting

Read formatting information such as font and color:

 xf_index = sheet.cell_xf_index(0, 0) xf = workbook.xf_list[xf_index]
font = workbook.font_list[xf.font_index] print(font.name, font.height) 

App Example Using xlrd

This is a simple application example that reads data from an Excel file and prints it in a structured format:

 import xlrd
def read_excel(file_name):
    workbook = xlrd.open_workbook(file_name)
    sheet = workbook.sheet_by_index(0)
    
    data = []
    for row in range(1, sheet.nrows):  # assuming first row is header
        row_data = {}
        for col in range(sheet.ncols):
            cell_value = sheet.cell_value(row, col)
            row_data[sheet.cell_value(0, col)] = cell_value
        data.append(row_data)
    return data

if __name__ == "__main__":
    file_name = 'data.xls'
    data = read_excel(file_name)
    print(data)

Hash: 6baab75838f232a50afdc176e6eb379e8eccc0b5534b81c4ade2134dc3f181d7