Reading and Writing Excel Files in Python Using xlrd/xlwt and openpyxl

2. Preparation

2.1. Introduction to Excel

Microsoft Excel is a spreadsheet application for Windows and macOS.

2.2. Format differences

Excel files come in two formats, xls and xlsx , which differ in file structure, version, and compatibility.

File format: xls is a binary format based on a compound document structure, while xlsx uses an XML‑based compressed structure, making it smaller.

Version: xls is generated by Excel 2003 and earlier; xlsx is generated by Excel 2007 and later.

Compatibility: xlsx is backward compatible and can open xls files.

2.3. Libraries

Python's standard library includes xlrd and xlwt for handling xls files; for xlsx files the third‑party library openpyxl is required.

2.4. Overall approach

Although the libraries expose Excel concepts as Python objects, cell‑by‑cell iteration can be inefficient, so the author provides six utility functions that simplify common read/write/append operations.

3. Code demonstration

3.1. xls format

3.1.1. Read xls file

def read_xls_excel(url, index):
    '''
    读取xls格式文件
    参数：
        url:文件路径
        index：工作表序号（第几个工作表，传入参数从1开始数）
    返回：
        data:表格中的数据
    '''
    # 打开指定的工作簿
    workbook = xlrd.open_workbook(url)
    # 获取工作簿中的所有表格
    sheets = workbook.sheet_names()
    # 获取工作簿中所有表格中的的第 index 个表格
    worksheet = workbook.sheet_by_name(sheets[index-1])
    data = []
    for i in range(0, worksheet.nrows):
        da = []
        for j in range(0, worksheet.ncols):
            da.append(worksheet.cell_value(i, j))
        data.append(da)
    return data

3.1.2. Write xls file

def write_xls_excel(url, sheet_name, two_dimensional_data):
    '''
    写入xls格式文件
    参数：
        url:文件路径
        sheet_name:表名
        two_dimensional_data：将要写入表格的数据（二维列表）
    '''
    workbook = xlwt.Workbook()
    sheet = workbook.add_sheet(sheet_name)
    for i in range(0, len(two_dimensional_data)):
        for j in range(0, len(two_dimensional_data[i])):
            sheet.write(i, j, two_dimensional_data[i][j])
    workbook.save(url)
    print('写入成功')

3.1.3. Append to xls file

def write_xls_excel_add(url, two_dimensional_data, index):
    '''
    追加写入xls格式文件
    参数：
        url:文件路径
        two_dimensional_data：将要写入表格的数据（二维列表）
        index：指定要追加的表的序号（第几个工作表，传入参数从1开始数）
    '''
    workbook = xlrd.open_workbook(url)
    sheets = workbook.sheet_names()
    worksheet = workbook.sheet_by_name(sheets[index-1])
    rows_old = worksheet.nrows
    new_workbook = copy(workbook)
    new_worksheet = new_workbook.get_sheet(index-1)
    for i in range(0, len(two_dimensional_data)):
        for j in range(0, len(two_dimensional_data[i])):
            new_worksheet.write(i+rows_old, j, two_dimensional_data[i][j])
    new_workbook.save(url)
    print('追加写入成功')

3.2. xlsx format

3.2.1. Read xlsx file

def read_xlsx_excel(url, sheet_name):
    '''
    读取xlsx格式文件
    参数：
        url:文件路径
        sheet_name:表名
    返回：
        data:表格中的数据
    '''
    workbook = openpyxl.load_workbook(url)
    sheet = workbook[sheet_name]
    data = []
    for row in sheet.rows:
        da = []
        for cell in row:
            da.append(cell.value)
        data.append(da)
    return data

3.2.2. Write xlsx file

def write_xlsx_excel(url, sheet_name, two_dimensional_data):
    '''
    写入xlsx格式文件
    参数：
        url:文件路径
        sheet_name:表名
        two_dimensional_data：将要写入表格的数据（二维列表）
    '''
    workbook = openpyxl.Workbook()
    sheet = workbook.active
    sheet.title = sheet_name
    for i in range(0, len(two_dimensional_data)):
        for j in range(0, len(two_dimensional_data[i])):
            sheet.cell(row=i+1, column=j+1, value=str(two_dimensional_data[i][j]))
    workbook.save(url)
    print('写入成功')

3.2.3. Append to xlsx file

def write_xlsx_excel_add(url, sheet_name, two_dimensional_data):
    '''
    追加写入xlsx格式文件
    参数：
        url:文件路径
        sheet_name:表名
        two_dimensional_data：将要写入表格的数据（二维列表）
    '''
    workbook = openpyxl.load_workbook(url)
    sheet = workbook[sheet_name]
    for tdd in two_dimensional_data:
        sheet.append(tdd)
    workbook.save(url)
    print('追加写入成功')

4. Result testing

4.1. Read test

Two Excel files were prepared (shown below). The test code reads each file and prints the extracted data, confirming that reading works correctly.

4.2. Write test

The write function overwrites the existing content, as shown in the output image.

4.3. Append test

The append function adds new rows without disturbing the original data, which is verified by the displayed result.

5. Summary

The author argues that data analysis should focus on the data itself rather than spending excessive effort on Excel styling, and hopes this tutorial helps Python learners; readers are invited to like and bookmark the article.