Python从入门到精通day24

Python读写Excel文件-1

上一节我们学习了Python读写CSV文件的方法，CSV文件适合简单的结构化数据交换，但在日常办公和商业项目中，Excel文件的使用场景更为广泛——它支持复杂格式、公式计算、图表生成等功能，是办公自动化和数据处理中不可或缺的文件格式。本节课将讲解如何使用Python的xlrd、xlwt、xlutils三个三方库，操作Excel 2007以前的xls格式文件（读、写、样式设置、公式计算），为后续办公自动化和进阶数据分析打下基础。

一.Excel简介

1. 什么是Excel

Excel是微软（Microsoft）为Windows和macOS操作系统开发的一款专业电子表格软件，凭借直观的可视化界面、强大的计算功能、丰富的图表工具，以及广泛的兼容性，成为全球最流行的个人计算机数据处理软件之一。

补充说明：Excel并非唯一的电子表格软件，其竞品包括Google Sheets（在线）、LibreOffice Calc（开源免费）、Numbers（苹果专属）等，这些竞品基本都能兼容Excel的核心功能，至少支持读写较新版本的Excel文件，本次教程重点聚焦Python操作Excel本身，不展开讲解竞品差异。

2. Python操作Excel的价值与库选择

掌握Python操作Excel的技能，核心价值有两点：一是实现办公自动化，批量处理重复的Excel操作（如数据录入、格式调整、多文件合并），节省大量时间；二是支撑商业项目开发，Excel文件的导入、导出是商业项目中极为常见的功能（如报表生成、数据备份）。

Python本身不支持直接操作Excel，需借助三方库，不同Excel格式对应不同的库，核心分类如下（重点讲解xls格式）：

• xls格式：Excel 2007以前的旧版本格式，体积小、兼容性强，使用 xlrd（读）+ xlwt（写）+ xlutils（辅助，实现读改写结合）；

• xlsx格式：Excel 2007及以后的新版本格式，支持更大数据量和更复杂功能，使用 openpyxl 库（后续教程单独讲解）。

3. 三方库安装

本节课重点使用xlrd、xlwt、xlutils三个库，打开终端（或CMD），输入以下命令一键安装（国内用户可添加清华源加速）：

pip install xlwt xlrd xlutils

二.读Excel文件

xlrd库的核心作用是读取xls格式Excel文件，核心逻辑：打开工作簿（Workbook）→ 获取工作表（Worksheet）→ 读取单元格（Cell）数据，支持获取行/列数据、单元格类型等操作。

例如在当前文件夹下有一个名为“阿里巴巴2020年股票数据.xls”的 Excel 文件，如果想读取并显示该文件的内容，可以通过如下所示的代码来完成。

import xlrd# 使用xlrd模块的open_workbook函数打开指定Excel文件并获得Book对象（工作簿）wb = xlrd.open_workbook('阿里巴巴2020年股票数据.xls')# 通过Book对象的sheet_names方法可以获取所有表单名称sheetnames = wb.sheet_names()print(sheetnames)# 通过指定的表单名称获取Sheet对象（工作表）sheet = wb.sheet_by_name(sheetnames[0])# 通过Sheet对象的nrows和ncols属性获取表单的行数和列数print(sheet.nrows, sheet.ncols)for row inrange(sheet.nrows):for col inrange(sheet.ncols):# 通过Sheet对象的cell方法获取指定Cell对象（单元格）# 通过Cell对象的value属性获取单元格中的值        value = sheet.cell(row, col).value# 对除首行外的其他行进行数据格式化处理if row > 0:# 第1列的xldate类型先转成元组再格式化为“年月日”的格式if col == 0:# xldate_as_tuple函数的第二个参数只有0和1两个取值# 其中0代表以1900-01-01为基准的日期，1代表以1904-01-01为基准的日期                value = xlrd.xldate_as_tuple(value, 0)                value = f'{value[0]}年{value[1]:>02d}月{value[2]:>02d}日'# 其他列的number类型处理成小数点后保留两位有效数字的浮点数else:                value = f'{value:.2f}'print(value, end='\t')print()# 获取最后一个单元格的数据类型# 0 - 空值，1 - 字符串，2 - 数字，3 - 日期，4 - 布尔，5 - 错误last_cell_type = sheet.cell_type(sheet.nrows - 1, sheet.ncols - 1)print(last_cell_type)# 获取第一行的值（列表）print(sheet.row_values(0))# 获取指定行指定列范围的数据（列表）# 第一个参数代表行索引，第二个和第三个参数代表列的开始（含）和结束（不含）索引print(sheet.row_slice(3, 0, 5))

提示：上面代码中使用的Excel文件“阿里巴巴2020年股票数据.xls”可以通过后面的百度云盘地址进行获取。链接:https://pan.baidu.com/s/1rQujl5RQn9R7PadB2Z5g_g 提取码:e7b4。

相信通过上面的代码，大家已经了解到了如何读取一个 Excel 文件，如果想知道更多关于xlrd模块的知识，可以阅读它的官方文档。

三.写Excel文件

xlwt库的核心作用是写入xls格式Excel文件，核心逻辑：创建工作簿（Workbook）→ 添加工作表（Worksheet）→ 写入单元格数据 → 保存工作簿，支持写入文本、数值等数据。

1. 实战需求

随机生成5名学生（关羽、张飞、赵云、马超、黄忠）的三门课程（语文、数学、英语）成绩（50~100分），写入xls格式Excel文件（考试成绩表.xls），要求包含表头（姓名、语文、数学、英语），工作表名称为“一年级二班”。

2. 代码实现

import randomimport xlwtstudent_names = ['关羽', '张飞', '赵云', '马超', '黄忠']scores = [[random.randrange(50, 101) for _ inrange(3)] for _ inrange(5)]# 创建工作簿对象（Workbook）wb = xlwt.Workbook()# 创建工作表对象（Worksheet）sheet = wb.add_sheet('一年级二班')# 添加表头数据titles = ('姓名', '语文', '数学', '英语')for index, title inenumerate(titles):    sheet.write(0, index, title)# 将学生姓名和考试成绩写入单元格for row inrange(len(scores)):    sheet.write(row + 1, 0, student_names[row])for col inrange(len(scores[row])):        sheet.write(row + 1, col + 1, scores[row][col])# 保存Excel工作簿wb.save('考试成绩表.xls')

四.调整单元格样式

在写Excel文件时，我们还可以为单元格设置样式，主要包括字体（Font）、对齐方式（Alignment）、边框（Border）和背景（Background）的设置，xlwt对这几项设置都封装了对应的类来支持。要设置单元格样式需要首先创建一个XFStyle对象，再通过该对象的属性对字体、对齐方式、边框等进行设定，例如在上面的例子中，如果希望将表头单元格的背景色修改为黄色，可以按照如下的方式进行操作。

header_style = xlwt.XFStyle()pattern = xlwt.Pattern()pattern.pattern = xlwt.Pattern.SOLID_PATTERN# 0 - 黑色、1 - 白色、2 - 红色、3 - 绿色、4 - 蓝色、5 - 黄色、6 - 粉色、7 - 青色pattern.pattern_fore_colour = 5header_style.pattern = patterntitles = ('姓名', '语文', '数学', '英语')for index, title inenumerate(titles):    sheet.write(0, index, title, header_style)

如果希望为表头设置指定的字体，可以使用Font类并添加如下所示的代码。

font = xlwt.Font()# 字体名称font.name = '华文楷体'# 字体大小（20是基准单位，18表示18px）font.height = 20 * 18# 是否使用粗体font.bold = True# 是否使用斜体font.italic = False# 字体颜色font.colour_index = 1header_style.font = font

注意：上面代码中指定的字体名（font.name）应当是本地系统有的字体，例如在我的电脑上有名为“华文楷体”的字体。

如果希望表头垂直居中对齐，可以使用下面的代码进行设置。

align = xlwt.Alignment()# 垂直方向的对齐方式align.vert = xlwt.Alignment.VERT_CENTER# 水平方向的对齐方式align.horz = xlwt.Alignment.HORZ_CENTERheader_style.alignment = align

如果希望给表头加上黄色的虚线边框，可以使用下面的代码来设置。

borders = xlwt.Borders()props = (    ('top', 'top_colour'), ('right', 'right_colour'),    ('bottom', 'bottom_colour'), ('left', 'left_colour'))# 通过循环对四个方向的边框样式及颜色进行设定for position, color in props:# 使用setattr内置函数动态给对象指定的属性赋值setattr(borders, position, xlwt.Borders.DASHED)setattr(borders, color, 5)header_style.borders = borders

如果要调整单元格的宽度（列宽）和表头的高度（行高），可以按照下面的代码进行操作。

# 设置行高为40pxsheet.row(0).set_style(xlwt.easyxf(f'font:height {20 * 40}'))titles = ('姓名', '语文', '数学', '英语')for index, title inenumerate(titles):# 设置列宽为200px    sheet.col(index).width = 20 * 200# 设置单元格的数据和样式    sheet.write(0, index, title, header_style)

五.公式计算

对于前面打开的“阿里巴巴2020年股票数据.xls”文件，如果要统计全年收盘价（Close字段）的平均值以及全年交易量（Volume字段）的总和，可以使用Excel的公式计算即可。我们可以先使用xlrd读取Excel文件夹，然后通过xlutils三方库提供的copy函数将读取到的Excel文件转成Workbook对象进行写操作，在调用write方法时，可以将一个Formula对象写入单元格。

实现公式计算的代码如下所示。

import xlrdimport xlwtfrom xlutils.copy import copywb_for_read = xlrd.open_workbook('阿里巴巴2020年股票数据.xls')sheet1 = wb_for_read.sheet_by_index(0)nrows, ncols = sheet1.nrows, sheet1.ncolswb_for_write = copy(wb_for_read)sheet2 = wb_for_write.get_sheet(0)sheet2.write(nrows, 4, xlwt.Formula(f'average(E2:E{nrows})'))sheet2.write(nrows, 6, xlwt.Formula(f'sum(G2:G{nrows})'))wb_for_write.save('阿里巴巴2020年股票数据汇总.xls')

说明：上面的代码有一些小瑕疵，有兴趣的读者可以自行探索并思考如何解决。

六.今日总结

本节课我们掌握了Python使用xlrd、xlwt、xlutils三个库操作xls格式Excel文件的核心方法，重点总结如下：

1. 库的分工：xlrd负责“读”，xlwt负责“写”，xlutils负责“读改写联动”，三者配合可完成xls格式Excel的大部分基础操作；

2. 核心流程：

1. 读取：打开工作簿 → 获取工作表 → 读取单元格/行/列数据；

2. 写入：创建工作簿 → 添加工作表 → 写入数据/样式/公式 → 保存文件；

3. 公式计算：读取原始文件 → 复制为可写入对象 → 写入Excel公式 → 保存。

3. 易错点：xlrd高版本不兼容xls格式，需安装指定版本；xlwt仅支持xls格式，保存时后缀不能错；中文乱码需指定encoding='utf-8'。

实战建议：

• 课后练习：读取“考试成绩表.xls”，计算每个学生的平均分、每门课程的平均分，写入汇总行并设置样式；

• 实际应用：若需处理Excel 2007及以后的xlsx格式文件，需学习openpyxl库（下一节课讲解）；

• 效率提升：若需批量处理大量Excel数据或进行复杂数据分析，后续可学习pandas库（封装了更简洁的Excel读写方法）。