★ FEATURED ARTICLE 直接说结论这三件套解决的是“读、写、改”Excel文件的历史级方案。xlrd负责读取xlwt负责生成xlutils负责在已有文件基础上做修改。虽然现在openpyxl和pandas的生态已经很成熟但老项目、自动化脚本、测试数据生成这类场景里这三件套依然大量存在。我最近在维护一个数据报表生成工具时又把这套东西完整摸了一遍踩了不少坑整理成这篇教程尽量把原理、步骤、坑位一次讲清楚。1. 三件套到底在解决什么问题1.1 各自的分工定位先理清角色。xlrd的英文全称是Excel Read专注读操作xlwt是Excel Write专注从零创建和写入xlutils是工具集核心能力是基于一个已存在的Excel文件做复制再把复制结果拿出来修改最后另存为新文件。三者配合起来就覆盖了“读原文件 - 改数据 - 写新文件”的完整链路。区别于openpyxl直接操作xlsx格式这三件套的核心战场是xls格式文件。xls是Excel 97-2003的老格式单Sheet最大65536行、256列今天看来限制不少但很多企业内部的老旧系统导出的数据、财务历史存档、银行回单等仍然大量使用xls。更关键的是xlwt写入xls文件的历史兼容性奇好生成的xls可以被WPS、老版本Excel、各种ETL工具直接打开不会出现新版本生成的xlsx在旧环境里打不开的问题。1.2 为什么还要选这套老方案有朋友会问现在都用openpyxl了为什么还要花时间学这个我的实际感受是三点。第一存量代码维护。很多公司内部的自动化报表、定时任务、数据清洗脚本几年前就是用这三件套写死的你接手后不懂它就动不了。第二写入速度。对于大批量生成xls格式的报表比如一次写几万行xlwt的性能并不拉胯而且内存占用比pandas写xlsx要低不少。第三格式兼容性。某些老旧的ERP系统、财务软件只认xls后缀你传xlsx过去它直接报“文件格式无效”。也就是说这套方案在“历史遗留系统对接”和“老格式文件处理”这两个场景里依然不可替代。本文的实操全程基于Python 3.8环境库的版本我会标注清楚避免你照抄代码时踩版本坑。2. 环境准备和版本坑位排查2.1 安装命令与版本选择三个库都是纯Python实现安装很简单pip install xlrd1.2.0 pip install xlwt1.3.0 pip install xlutils2.0.0这里必须强调xlrd的版本是一个大坑。xlrd从2.0.0版本开始移除了对xlsx格式的支持只保留xls读取能力。但2.0之后对xls的某些API行为也有调整比如不再返回xlsx单元格的格式化信息。我推荐锁死1.2.0版本这个版本功能最稳定兼容Python 3.8到3.11都没问题网上绝大多数教程也都是基于这个版本写的。xlutils同理最新版是2.0.0它依赖xlrd和xlwt装的时候会把xlrd自动拉成最新版所以安装顺序上手动指定了xlrd1.2.0再装xlutils靠谱。2.2 安装后的环境自检安装完顺手验证一下省得后面跑脚本报错找不到模块python -c import xlrd, xlwt, xlutils; print(xlrd, xlrd.__version__); print(xlwt, xlwt.__VERSION__); print(xlutils ok)正常输出类似xlrd 1.2.0 xlwt 1.3.0 xlutils ok如果xlrd版本显示2.x说明你被依赖自动升级坑了老老实实重新执行上面那三条pip命令。版本不对会导致后面读取xlsx时报各种错甚至直接抛InvalidFileException排查起来头疼。3. 用xlrd把Excel数据完整读出来3.1 打开工作簿并定位Sheetxlrd读取xls最基础的操作是打开工作簿然后拿到某个Sheet对象。import xlrd # 打开工作簿formatting_infoTrue 才能保留格式信息 wb xlrd.open_workbook(报表_2024.xls, formatting_infoTrue) # 方式一按Sheet索引拿0就是第一个Sheet sheet wb.sheet_by_index(0) # 方式二按Sheet名拿适合Sheet多的场景 sheet wb.sheet_by_name(销售明细) # 查看当前Sheet的行数和列数 print(f总行数: {sheet.nrows}, 总列数: {sheet.ncols})注意formatting_infoTrue这个参数。如果只是纯读数值不传也没事但如果你需要读取单元格的字体颜色、背景色、边框等样式信息必须传这个参数。它会把整个文件的格式映射加载到内存因此打开稍大的文件会慢一些。我做的一个测试里一个10MB的xls不传参打开大概0.3秒传了之后变成1.1秒。需要样式就忍不需要就别开。3.2 逐行读单元格数据的正确姿势拿到Sheet之后遍历数据是核心操作。我建议先取行数再逐行读取不要用for row in sheet:这种迭代方式因为遇到合并单元格时空行处理非常别扭。# 逐行遍历所有数据 for row_idx in range(sheet.nrows): row_data sheet.row_values(row_idx) # row_data 是每行所有列的列表 print(f第{row_idx}行: {row_data}) # 按行读取右上采样只要前5行、前3列 sample [sheet.row_values(i)[:3] for i in range(min(5, sheet.nrows))]有一个必须说明的坑row_values返回的列表里如果某个单元格是空的它返回空字符串而不是None。如果你拿这个数据去做空值判断用if value:会误判要用if value ! 来过滤。我见过不少新手在这里翻车导致清洗后的数据混入一堆空字符串。3.3 日期和数字类型怎么处理xlrd读取单元格时会自动映射类型主要对应关系如下Excel单元格类型xlrd返回类型说明文本str原样返回数字float即使看起来是整数也会变成1.0日期float需要借助xldate转换布尔boolTrue/False错误错误文本如#N/A最坑的是日期。Excel把日期存储为数字序列1900年1月1日为1所以你直接拿到的是一个浮点数。必须用xlrd.xldate_as_datetime()转换from datetime import datetime import xlrd wb xlrd.open_workbook(数据.xls, formatting_infoTrue) sheet wb.sheet_by_index(0) cell sheet.cell(3, 1) # 第4行第2列 cell_type cell.ctype cell_value cell.value if cell_type xlrd.XL_CELL_DATE: # 这里才是真正的日期单元格 dt xlrd.xldate_as_datetime(cell_value, wb.datemode) print(dt.strftime(%Y-%m-%d)) else: print(cell_value)wb.datemode在xls文件里通常是0表示使用1900日期系统某些特殊来源的文件可能是11904系统。不传它会用默认值遇到日期错位时先检查这里。我想特别提醒用row_values拿到的日期类型也是浮点数无法直接区分它到底是日期还是普通数字。判断方法就是检查单元格的ctype上面例子里的做法才是正确的。单纯看值的类型会被浮点数迷惑。4. 用xlwt从零创建一个带样式的xls4.1 基础写入创建工作簿并填充数据创建xls的流程分为三步新建Workbook、获取Worksheet、写入数据最后保存。代码非常直观import xlwt # 1. 创建工作簿 wb xlwt.Workbook(encodingutf-8) # 2. 创建Sheetcell_overwrite_okTrue 允许覆盖写入 ws wb.add_sheet(年度汇总, cell_overwrite_okTrue) # 3. 写入数据行索引、列索引、值 ws.write(0, 0, 项目) ws.write(0, 1, 金额) ws.write(1, 0, 营收) ws.write(1, 1, 1000000) # 4. 保存 wb.save(年度汇总.xls)cell_overwrite_ok这个参数很重要。默认情况下同一个单元格写入两次会直接抛异常。调试脚本时经常要改数据重跑开着这个参数能省很多事。但有代价它失去了对重复写入的检查你写错行号覆盖了重要数据时它不提醒。建议只在脚本调试期开正式生成报表时关掉。xlwt写入时不需要预先声明行数列数写入哪个位置哪个位置就会存在。行、列索引都是从0开始和xlrd保持一致。4.2 样式控制字体、对齐、边框加背景色如果说写入数据是基础那么样式才是报表的灵魂。xlwt的样式机制是通过xlwt.XFStyle对象实现的你需要分别构造各个样式组件再组合成一个完整样式。def create_header_style(): style xlwt.XFStyle() # 字体加粗、白色、16号 font xlwt.Font() font.name 微软雅黑 font.bold True font.colour_index 0x09 # 0x09是白色 font.height 16 * 20 # 16磅xlwt以twip为单位1磅20twip style.font font # 对齐居中自动换行 alignment xlwt.Alignment() alignment.horz xlwt.Alignment.HORZ_CENTER alignment.vert xlwt.Alignment.VERT_CENTER alignment.wrap 1 style.alignment alignment # 边框全边框细线 borders xlwt.Borders() borders.left xlwt.Borders.THIN borders.right xlwt.Borders.THIN borders.top xlwt.Borders.THIN borders.bottom xlwt.Borders.THIN style.borders borders # 背景色深蓝色 pattern xlwt.Pattern() pattern.pattern xlwt.Pattern.SOLID_PATTERN pattern.pattern_fore_colour 0x1F # 深蓝色索引 style.pattern pattern return style header_style create_header_style() ws.write(0, 0, 项目, header_style)这里有个容易懵的地方颜色索引。xlwt用颜色索引号指代颜色0-16是固定颜色区17开始是调色板索引。常用几个我先列出来颜色索引值黑色0x00白色0x09红色0x0A蓝色0x0C黄色0x0D绿色0x11深蓝0x1F如果你想知道某个颜色对应的索引用xlwt.Style.colour_map查它是个字典。现场调试时我经常打印它来找色值。字体高度单位是twip磅的20倍所以16号字要写16*20。直接填16出来的字会小得看不清。4.3 合并单元格、列宽设置和公式写入合并单元格和设置列宽是生成漂亮报表两个最常用的操作。# 合并单元格参数是起始行、结束行、起始列、结束列都含 ws.write_merge(0, 0, 0, 3, 年度经营数据汇总, header_style) # 设置列宽以字符为单位参数是列索引和宽度值 # 256是一个字符的宽度单位 ws.col(0).width 256 * 20 # 第0列宽20个字符 ws.col(1).width 256 * 15 ws.col(2).width 256 * 12 # 设置行高 ws.row(0).height 30 * 20 # 30磅高write_merge经常被误解。它的语义是把从起始行列到结束行列这块矩形区域合并成一个单元格并把指定的值写到这个合并区域的左上角。所以最容易被坑的是行列的顺序——先合并区域行范围列范围再写值。再直的干货是xlwt支持写入公式。写完公式后你用Excel打开文件公式会正常计算# 写入一个求和公式 ws.write(5, 4, xlwt.Formula(SUM(A1:A4))) # 也可以写普通数学公式 ws.write(5, 5, xlwt.Formula(B1*C1B2*C2))这是xlwt区别于pandas.to_excel的一大优势pandas写进去的是纯静态值而xlwt可以产生活的公式。在生成财务报表时让Excel自己去算合计、比例文件的可维护性好很多。4.4 一个完整的带样式报表写法理论拆完我来一个可以抄作业的完整示例。场景生成一份季度销售报表包含标题行、表头行、数据行、合计行带样式。import xlwt wb xlwt.Workbook(encodingutf-8) ws wb.add_sheet(季度销售, cell_overwrite_okTrue) # 样式定义 def base_style(boldFalse, color_indexNone, bg_indexNone, align_hcenter, borderTrue): style xlwt.XFStyle() font xlwt.Font() font.bold bold if color_index is not None: font.colour_index color_index font.name 微软雅黑 font.height 11 * 20 style.font font alignment xlwt.Alignment() if align_h center: alignment.horz xlwt.Alignment.HORZ_CENTER else: alignment.horz xlwt.Alignment.HORZ_LEFT alignment.vert xlwt.Alignment.VERT_CENTER alignment.wrap 1 style.alignment alignment if border: borders xlwt.Borders() borders.left xlwt.Borders.THIN borders.right xlwt.Borders.THIN borders.top xlwt.Borders.THIN borders.bottom xlwt.Borders.THIN style.borders borders if bg_index is not None: pattern xlwt.Pattern() pattern.pattern xlwt.Pattern.SOLID_PATTERN pattern.pattern_fore_colour bg_index style.pattern pattern return style # 数据 data [ (华东, 1200000), (华南, 980000), (华北, 750000), (西南, 460000), ] # 标题行 ws.write_merge(0, 0, 0, 1, 2024年Q3季度销售报表, base_style(boldTrue, bg_index0x1F, color_index0x09)) ws.row(0).height 25 * 20 # 表头行 header base_style(boldTrue, bg_index0x0C, color_index0x09) ws.write(1, 0, 区域, header) ws.write(1, 1, 销售额, header) ws.row(1).height 20 * 20 # 数据行 data_style base_style() total 0 for i, (region, amount) in enumerate(data, start2): ws.write(i, 0, region, data_style) ws.write(i, 1, amount, data_style) total amount # 合计行写入公式 sum_style base_style(boldTrue, bg_index0x0D) ws.write(len(data) 2, 0, 合计, sum_style) ws.write(len(data) 2, 1, xlwt.Formula(SUM(B2:B%d) % (len(data) 1)), sum_style) # 列宽 ws.col(0).width 256 * 12 ws.col(1).width 256 * 16 wb.save(季度销售报表.xls) print(报表生成完成)这个写法里我把样式封装成函数用参数控制加粗、颜色、背景、对齐比每次都new一堆对象干净很多。而且把样式定义和数据写入分开后续改样式只要动函数不动逻辑。5. 用xlutils在已有文件上原地修改5.1 copy的原理和正确姿势光能读能写还不够。很多实际需求是这样的你手上有一份填好了部分数据的Excel模版需要程序往里面补充录入一批数据然后另存为新文件。直接xlrd读出来再xlwt写是不行的xlwt不支持打开已有文件。这时候xlutils就派上用场了。它的核心操作是用xlrd读入原文件保留格式通过xlutils.copy转换成一个xlwt的Workbook对象接着像操作新文件一样往里面写数据最后保存。import xlrd import xlwt from xlutils.copy import copy # 1. 读取原文件必须保留格式信息 rb xlrd.open_workbook(模版.xls, formatting_infoTrue) # 2. 转换成可写对象 wb copy(rb) # 3. 拿到某个Sheet ws wb.get_sheet(0) # 按索引获取 # 4. 写入新数据比如往第5行第1列写数据 ws.write(4, 0, 新录入的数据) ws.write(4, 1, 12345) # 5. 保存为新文件 wb.save(模版_已填充.xls)流程看起来简单但有几个关键点必须注意。第一copy()要求源工作簿必须用formatting_infoTrue打开否则某些样式信息会丢生成的文件打开时会提示修复严重的直接损坏。第二通过get_sheet()拿到的ws对象写入时用的是和xlwt相同的write方法但它不会主动清空原单元格内容如果你往已有内容的单元格写新值是覆盖不是追加。5.2 三个高频事故现场事故一单元格样式被覆盖这是copy方案最经典的坑。你先往已有内容的单元格写入新值后发现字体变了、背景没了。原因在于write默认用的是空白样式把原单元格样式整个替换掉了。解决办法是从原Sheet里把样式取出来再应用回去。# 读取原单元格样式 r_sheet rb.sheet_by_index(0) ori_cell r_sheet.cell(4, 0) ori_xf_index r_sheet.cell_xf_index(4, 0) ori_xf rb.xf_list[ori_xf_index] # 构造一个新样式对象复制原文件的格式 new_style xlwt.XFStyle() new_style.font ori_xf.font new_style.num_format_str ori_xf.format_key new_style.alignment ori_xf.alignment new_style.borders ori_xf.border ws.write(4, 0, 新数据, new_style)这个代码必须结合自己的数据结构来调因为xf.xf_list里的font、border等对象是从原文件中读出来的直接赋值给XFStyle即可复用。事故二往合并单元格中间写值原文件的标题是合并单元格你却往合并区域的中间某个单元格写数据结果Excel打开发现合并区域被拆分或者报错。解决思路是先摸清原文件哪些区域是合并的写入时避开合并区域。用rb.sheet_by_index(0).merged_cells能拿到所有合并区域的元组列表。r_sheet rb.sheet_by_index(0) for merged in r_sheet.merged_cells: rlo, rhi, clo, chi merged print(f合并区域行{rlo}~{rhi-1}列{clo}~{chi-1})注意元组的范围是半开区间实际合并的是rlo到rhi-1行。事故三写入后原文件里的公式全没了这是xlutils最大的限制之一。copy不会保留原文件里的公式它把公式所在单元格变成一个静态缓存值。换句话说你原文件里用公式算出来的动态汇总copy之后不会再自动计算。我个人建议如果是模版中存在大量依赖公式的文件不要用xlutils直接用openpyxl读写xlsx格式。如果必须用xlutils就在copy之后重新用xlwt.Formula把关键公式补写一遍。6. 高频问题速查表问题一安装xlutils后xlrd版本变成了2.x解决办法手动卸载后按xlrd1.2.0重装xlutils依赖声明没有锁版本只能手动处理。问题二open_workbook报InvalidFileException文件明明是xls解决办法先检查后缀是不是真的.xls很可能是xlsx改了个名。用文件头检查xls开头字节是D0 CF 11 E0xlsx是50 4B 03 04。问题三读取的日期值是一个浮点数怎么转成真正日期解决办法参考3.3的代码用xlrd.xldate_as_datetime(value, datemode)转换不要自以为是的按数值天数去加。问题四用write_merge后文字没有居中解决办法合并单元格的样式必须在合并前定义好给write_merge传入的样式参数是合并后整个区域使用的样式。问题五从xlutils copy出来的文件在Excel里提示“文件损坏需要修复”解决办法先检查所在目录是否只读再看来源文件是否被占用。排除这些后核心原因是原文件打开时没有加formatting_infoTrue导致格式序列不对。问题六xls文件超过65536行怎么办解决办法这是xls格式硬上限无解。要么拆分多Sheet每个Sheet控制在65536行以内要么换xlsx格式、用openpyxl处理。我把上面这些内容整理成一张对照表方便你查场景推荐方案不推荐读xls数据xlrd 1.2.0pandas格式信息丢失创建带样式的xlsxlwtopenpyxl不支持xls修改已有xlsxlutils.copy手动读改写太重处理xlsx新格式openpyxlxlrd 2.x不支持大规模数据分析后再导出pandas openpyxl直接写xls最后说点实在的。这三件套看起来是老技术但它们是理解Excel格式本质的一个绝佳入口你会直观感受到单元格类型、格式化信息、文件结构这些概念而不是把一切都封装掉。我建议新项目能用xlsx就用xlsx但手里这三件套别扔。遇到老系统对接、历史数据处理的时候这套东西能帮你兜住最底层的那批数据资产。资源紧张的时候能用老技术解决的事别上新工具。
阅读完成 · 觉得有帮助？
