Python Excel Automation: Using xlrd, xlwt, and xlutils for Reading, Writing, and Modifying Excel Files

0. Python Excel Library Comparison

The article begins with a brief overview of nine Python libraries that can manipulate Excel files, highlighting xlrd for reading and xlwt for writing.

1. Python xlrd – Reading Excel

1.1 Introduction xlrd is introduced as a fast, read‑only library useful for UI or API automation data maintenance.

Official documentation: https://xlrd.readthedocs.io/en/latest/

1.2 Installation

pip install xlrd

On Anaconda the library may already be installed.

1.3 Usage

Common cell data types include empty, string, number, date, boolean, error, and blank.

1.4 Practical Example

import xlrd
xlsx = xlrd.open_workbook('./3_1 xlrd 读取 操作练习.xlsx')
table = xlsx.sheet_by_index(0)
value = table.cell_value(2, 1)
print("第3行2列值为", value)
nrows = table.nrows
print("表格一共有", nrows, "行")
name_list = [str(table.cell_value(i, 3)) for i in range(1, nrows)]
print("第4列所有的值：", name_list)

2. Python xlwt – Writing Excel (xls format only)

xlwt can create new Excel files or modify existing ones quickly.

Official documentation: https://xlwt.readthedocs.io/en/latest/

2.1 Installation

pip install xlwt

On Anaconda the library may already be present.

2.2 Creating a New Workbook

import xlwt
workbook = xlwt.Workbook(encoding='ascii')
worksheet = workbook.add_sheet('My new Sheet')
worksheet.write(0, 0, '内容1')
worksheet.write(2, 1, '内容2')
workbook.save('新创建的表格.xls')

2.3 Setting Font Styles

style = xlwt.XFStyle()
font = xlwt.Font()
font.name = 'Times New Roman'
font.bold = True
font.underline = True
font.italic = True
style.font = font
worksheet.write(0, 0, '内容1', style)
worksheet.write(2, 1, '内容2', style)
workbook.save('新创建的表格.xls')

2.4 Setting Column Width

worksheet.col(0).width = 256 * 20
workbook.save('新创建的表格.xls')

2.5 Setting Row Height

style = xlwt.easyxf('font:height 360;')
row = worksheet.row(0)
row.set_style(style)
workbook.save('新创建的表格.xls')

2.6 Merging Cells

worksheet.write_merge(1, 2, 0, 3, 'Merge Test')
workbook.save('新创建的表格.xls')

2.7 Adding Borders

borders = xlwt.Borders()
borders.left = xlwt.Borders.DASHED
borders.right = xlwt.Borders.DASHED
borders.top = xlwt.Borders.DASHED
borders.bottom = xlwt.Borders.DASHED
style = xlwt.XFStyle()
style.borders = borders
worksheet.write(0, 0, '内容1', style)
workbook.save('新创建的表格.xls')

2.8 Setting Background Color

pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 5  # Yellow
style = xlwt.XFStyle()
style.pattern = pattern
worksheet.write(2, 1, '内容2', style)
workbook.save('新创建的表格.xls')

2.9 Cell Alignment

al = xlwt.Alignment()
al.horz = 0x02  # Center horizontally
al.vert = 0x01  # Center vertically
style = xlwt.XFStyle()
style.alignment = al
worksheet.write(2, 1, '内容2', style)
workbook.save('新创建的表格.xls')

3. Python xlutils – Copying and Modifying Excel

xlutils works together with xlrd to copy an existing workbook and then modify it.

Official documentation: https://xlutils.readthedocs.io/en/latest/

3.1 Installation

pip install xlutils

3.2 Copying a Source File

import xlrd
from xlutils.copy import copy
workbook = xlrd.open_workbook('3_3 xlutils 修改操作练习.xlsx')
new_workbook = copy(workbook)
new_workbook.save('new_test.xls')

3.3 Reading and Writing

workbook = xlrd.open_workbook('3_3 xlutils 修改操作练习.xlsx')
new_workbook = copy(workbook)
sheet = workbook.sheet_by_index(0)
col2 = sheet.col_values(1)
cell_value = sheet.cell_value(1, 1)
print(col2)
print(cell_value)
write_sheet = new_workbook.get_sheet(0)
write_sheet.write(0, 0, 'xlutils写入！')
new_workbook.save('new_test.xls')

The copied workbook retains data but loses original formatting.

At the end of the article a QR code is shown for a free Python public course and a collection of learning resources.