Using Python xlwt, xlrd, and xlutils to Create, Read, and Modify Excel Files

Python can manipulate Excel files through three popular libraries: xlwt for creating and writing new workbooks, xlrd for reading existing workbooks, and xlutils (specifically its copy function) for copying and modifying workbooks.

First, xlwt is used to generate a new Excel file that contains the classic 9×9 multiplication table. The code creates a workbook, adds a sheet, writes each multiplication expression into the appropriate cell, and saves the file as 九九乘法表.xls :

import xlwt

workbook = xlwt.Workbook()
sheet = workbook.add_sheet('Multiplication')
for i in range(1, 10):
    for j in range(1, 10):
        sheet.write(i-1, j-1, f"{i}*{j}={i*j}")
workbook.save('九九乘法表.xls')

After execution, the script creates an Excel file named “九九乘法表” that displays the full multiplication table, as shown in the accompanying screenshot.

Next, xlrd reads the data back from the newly created workbook. The script opens the file, accesses the first sheet, and prints each cell’s value to the console:

import xlrd

workbook = xlrd.open_workbook('九九乘法表.xls')
sheet = workbook.sheet_by_index(0)
for row_idx in range(sheet.nrows):
    for col_idx in range(sheet.ncols):
        print(sheet.cell_value(row_idx, col_idx), end='\t')
    print()

The console output reproduces the multiplication table, confirming that the data was written correctly.

Finally, xlutils.copy is employed to modify the existing Excel file without overwriting the original. By copying the workbook, changing specific cells, and saving the result as a new file (e.g., “九九乘法表改版.xls”), the script demonstrates how to update Excel data programmatically:

import xlrd
from xlutils.copy import copy

rb = xlrd.open_workbook('九九乘法表.xls')
wb = copy(rb)
ws = wb.get_sheet(0)
# Example: change the value of cell (0,0)
ws.write(0, 0, 'Modified')
wb.save('九九乘法表改版.xls')

Running this code produces a second Excel file named “九九乘法表改版” that reflects the modifications, as illustrated by the final screenshot.

The article concludes with a brief note that the content is provided for learning purposes only.