Python Excel Automation with xlwt, xlrd, and xlutils: A Step‑by‑Step Tutorial

If you can achieve report automation, you will save a lot of time and work more efficiently. This article explains how to use Python for report automation.

The article introduces common functions of xlwt, xlrd, and xlutils, the use of formulas when writing Excel with xlwt, and how to write xlwt files to specific directories.

1. Preparation: Install xlwt

Run pip install xlwt or easy_install xlwt to import the xlwt package.

import xlwt # write

2. Basic tutorial: Create a workbook and add sheets

Create a workbook and add sheets:

f = xlwt.Workbook()  # 创建工作簿
sheet1 = f.add_sheet(u'sheet1', cell_overwrite_ok=True)

Write data to a sheet using sheet.write(row, col, value, style) . The fourth parameter (style) is optional.

sheet1.write(i, j, '第i行第j列存放此内容', style)

Merge cells and write content:

sheet1.write_merge(x, x + m, y, y + n, '内容', style)

3. Practical example

Create a workbook with two sheets and write sample data:

#coding=utf-8
import xlwt
f = xlwt.Workbook()  # 创建工作簿
sheet1 = f.add_sheet(u'表一', cell_overwrite_ok=True)
sheet2 = f.add_sheet(u'表二', cell_overwrite_ok=True)
row = 0
temp = [u'姓名', u'年龄', u'学校', u'专业']
for pos, v in enumerate(temp):
    sheet1.write(row, pos, v)
row += 1
sheet1.write(row, 0, u'张三')
sheet1.write(row, 1, 18)
sheet1.write(row, 2, u'清华大学')
row += 1
sheet1.write(row, 0, u'李四')
sheet1.write(row, 1, 20)
sheet1.write(row, 2, u'北京大学')
f.save('xlwt_tutorial')

Merge rows 2‑3 and column 4:

sheet1.write_merge(1, 2, 3, 3, u'汉语言文学')

Python xlrd read Excel

Install xlrd:

pip install xlrd

Open an Excel file and list sheet names:

#coding=utf-8
import xlrd, uniout
f = xlrd.open_workbook(r'xlwt_tutorial')
print f.sheet_names()  # 输出：[u'表一', u'表二']

Iterate sheets and read content:

for i in range(len(f.sheet_names())):
    sheet1 = workbook.sheet_by_index(i)

f = xlrd.open_workbook(r'xlwt_tutorial')
sheet1 = f.sheet_by_index(0)  # 打开第一个sheet
sheet2 = f.sheet_by_name(u'表二')  # 打开名字为表二的sheet
print sheet1.name, sheet1.nrows, sheet1.ncols
print sheet2.name, sheet2.nrows, sheet2.ncols
print sheet1.row_values(1)  # 获取第二行内容
print sheet1.col_values(2)  # 获取第三列内容
print sheet1.cell(1,0).value  # 获取单元格内容
print sheet1.cell(1,1).ctype  # 获取单元格内容的数据类型

xlutils common functions

Install xlutils:

pip install xlutils

Use xlutils copy to edit a read‑only workbook:

import xlrd
from xlutils.copy import copy
f = xlrd.open_workbook(r'xlwt_tutorial')
wb = copy(f)  # 将f拷贝到wb
sheet1 = wb.get_sheet(0)  # 打开sheet
sheet1.write(3, 0, 'change')
wb.save('xlwt_tutorial')  # 输出为：表一输出的表格已经改变。

xlwt write formulas

Write a score table and calculate totals using formulas:

#coding=utf-8
import xlwt
f = xlwt.Workbook()
sheet1 = f.add_sheet(u'得分统计', cell_overwrite_ok=True)
mdict = {"monkey": {"writing":80, "reading":60, "speaking":70, "listening":60},
        "grape": {"writing":100, "reading":80, "speaking":70, "listening":60}}
# (write headers and data ...)
for i in range(len(mdict)):
    forstr = chr(65+i+1)+'2+'+chr(65+i+1)+'3+'+chr(65+i+1)+'4+'+chr(65+i+1)+'5'
    sheet1.write(5, i+1, xlwt.Formula(forstr))
# B2+B3+B4+B5, C2+C3+C4+C5
f.save('得分统计')

Write xlwt file to a specific directory

Use shutil.move to move the generated file:

#coding=utf-8
import xlwt, os, shutil
path = '../sheet/'
if not os.path.exists(path):
    os.makedirs(path)
# (create workbook and write data ...)
f.save('得分统计')
shutil.move(u'得分统计', path)

- END -