1. 为什么还在折腾python读写xls先从一个真实的报表场景说起上周部门同事递给我一个压箱底的.xls文件里面是过去三年几百行的销售流水夹杂着合并单元格、跨行表头、几列被误存成文本的数字还有一堆脏数据。他问我能不能快速提取出“每个大区的季度销售额”。我打开Python二十多行代码打发了整个过程。他看我的眼神像看魔术师但对写代码的人来说这不过是“python读写xls”这个再基础不过的日常操作。很多人会问现在不都是.xlsx了吗连Office都默认保存xlsx为什么还要专门学xls的读写这恰恰是实际工作中最扎心的地方。老系统的导出接口、财务软件的历史备份、甲方发来的上古表格大量仍是.xls格式。它本质上是Excel 97-2003的二进制格式扩展名是.xls而.xlsx是基于OpenXML的压缩包格式。这两者的底层结构完全不同所以处理它们的工具库也不通用。这就造成了一个非常具体的痛点你以为会了openpyxl就能通吃结果碰到.xls文件直接报错。这篇文章不玩虚的我会直接把xls读写这件事彻底拆开覆盖环境准备、xlrd/xlwt/openpyxl/pandas这四个主力库的分工与取舍、详细的读写示例、格式异常处理以及大文件、日期、编码、合并单元格等高频坑。无论你是用Python做办公自动化、爬虫数据落库还是写数据分析脚本这篇文章都能当一份可直接“抄作业”的速查手册。而且我会用自己在实际项目中踩过的坑来帮你绕路省下那些凭白浪费的调试时间。2. 动手前的环境准备与工具选型2.1 库的分工别指望一个库包打天下处理xls读写绕不开这几个Python库但它们的职责差别很大选错库就是入坑的开始。xlrd老牌读取库专门用来读.xls文件。需要注意版本分化。xlrd 2.0.0之后的版本移除了对xlsx的支持只保留对纯.xls格式的读取能力这一点历史上坑过很多人。它的主要用处是读取老式.xls文件中的单元格数据、行数、列数、日期等。xlwt对应的写库可以创建和写入.xls文件。它比较老了功能基础不支持.xlsx不支持样式有限制比如单个sheet最多65536行但对传统.xls生成足够用。xlutils通常和xlrd、xlwt搭配用于复制一个已有工作簿并修改内容实现xls文件的“原地编辑”。openpyxl现代主力库读写.xlsx格式也支持.xlsm等操作方便支持样式、图表、公式、数据筛选等。但它不处理.xls这一点必须记住。pandas数据分析核心库它的read_excel/to_excel方法内部实际上会调用xlrd、openpyxl作为引擎。所以pandas不直接处理文件而是委托给底层引擎。用pandas处理时文件扩展名决定了引擎选择.xls走xlrd.xlsx走openpyxl。pyexcel、pyxlsb等属于其他备选库处理特殊格式实际工作用得少不展开。一句话总结读.xls优先用xlrd写.xls优先用xlwt读写.xlsx用openpyxl想快速做数据分析用pandas。2.2 安装与版本坑一条命令解决但要注意大版本环境准备很简单pip安装就行pip install xlrd xlwt xlutils openpyxl pandas如果只需要读xls最低要求是xlrd。但这里必须严肃提醒版本问题。网上大量老教程还在展示xlrd.open_workbook(xxx.xlsx)这种写法结果在新版本xlrd中直接报错XLRDError: Excel xlsx file; not supported。因为xlrd 2.0.0之后移除了xlsx支持。如果你真的需要读取xlsx请不要用xlrd去读改用openpyxl或pandas或者把xlrd降级到1.2.0版本不推荐旧版本有安全问题。我在实际项目中的做法是读xls用xlrd 2.x写xls用xlwt读写xlsx统一openpyxl需要分析表格数据时用pandas。安装时如果只想读xls直接pip install xlrd就够了只想写xlspip install xlwt。但完整开发环境建议一次性把上面五个库都装上省得后面临时缺这缺那。还要注意pandas依赖的底层引擎版本也可能互相冲突。比如pandas旧版本可能依赖xlrd 1.x当你升级xlrd到2.0后某些pandas函数会报错。解决方法很简单把pandas升级到较新版本并确保xlrd版本不低于2.0。如果你用的是Python 3.9以上直接装最新版基本没啥问题。3. 核心读写实操从零到一的完整示范3.1 用xlrd读取xls文件最经典也没得选的方案读取xlsxlrd是绕不过去的。我们先看基础玩法。import xlrd workbook xlrd.open_workbook(sales_2023.xls) sheet workbook.sheet_by_index(0) # 按索引取第一个sheet # 也可以按名字取sheet workbook.sheet_by_name(销售额) print(总行数:, sheet.nrows) print(总列数:, sheet.ncols) # 读取第2行第3列行和列都从0开始 cell_value sheet.cell_value(1, 2) print(单元格内容:, cell_value)是不是很简单但实际场景往往没这么干净。典型情况是表头占了两行前几列是合并单元格还有空格行。这时要做的第一步不是读取而是“侦察”。我一般先打印前10行的所有单元格内容用肉眼快速了解表结构。for row_idx in range(min(10, sheet.nrows)): row_data [sheet.cell_value(row_idx, col_idx) for col_idx in range(sheet.ncols)] print(row_data)看到打印结果后再确定数据区起始行。比如表头在第2行索引1数据从第3行索引2开始那就用for row_idx in range(2, sheet.nrows)去遍历。读取单元格时还需要区分数据类型。xlrd提供了cell_type来判断常见类型有0为空empty、1为文本、2为数字、3为日期、4为布尔、5为错误。比如cell sheet.cell(1, 2) if cell.ctype xlrd.XL_CELL_DATE: # 此处需要转换日期 date_value xlrd.xldate_as_datetime(cell.value, workbook.datemode) elif cell.ctype xlrd.XL_CELL_NUMBER: number_value int(cell.value) if float(cell.value).is_integer() else cell.value else: value cell.value为什么日期这么麻烦因为xls内部把日期存成浮点数表示相对于某种基准日期的天数。如果你不转换读出来一串莫名其妙的小数比如43831.0代表2020年1月1日。这个问题极其常见我会在后面专门展开。还有一个细节某些单元格明明看着是文本数字比如“00123”xlrd读出来可能是数字123导致前导零丢失。这种情况在后面“常见问题”里详细说。3.2 用xlwt写入xls文件生成老式报表写xls比读更简单。如果只是生成一个新的.xls文件xlwt非常直接import xlwt workbook xlwt.Workbook(encodingutf-8) sheet workbook.add_sheet(销售额) # 写入表头 sheet.write(0, 0, 大区) sheet.write(0, 1, 销售额) sheet.write(0, 2, 日期) # 写入数据行 data [ (华东, 123456, 2024-01-01), (华北, 654321, 2024-01-02), ] for row_idx, row_data in enumerate(data, start1): sheet.write(row_idx, 0, row_data[0]) sheet.write(row_idx, 1, row_data[1]) sheet.write(row_idx, 2, row_data[2]) workbook.save(report.xls)这里有几个容易被忽略的点。第一Workbook(encodingutf-8)里的编码设置很重要。如果不设置或设置成ASCII写入中文可能报错或产生乱码。虽然Python3字符串默认Unicode但xlwt内部处理旧格式时依赖这个编码声明。第二sheet.write能自动识别部分类型但如果你传入的是Python的int、float、str、datetime.datetime等都能正常写入。但不要传None会导致写失败建议传空字符串。第三样式设置与列宽调整。默认生成的xls样式很原始列宽可能是固定宽度。可以这样加一点基础样式style xlwt.XFStyle() font xlwt.Font() font.bold True style.font font # 设置列宽 sheet.col(0).width 256 * 15 # 20个字符宽度256为长度单位基数 sheet.write(0, 0, 大区, style)第四单个sheet的行数限制是65536行别指望用xlwt写十万行数据这是xls格式的天花板。如果数据量很大要么改用xlsx格式要么拆成多个sheet。3.3 用openpyxl读写xlsx现代项目的主选方案虽然标题是xls但实际工作中大概率同时会碰到xlsx。openpyxl在xlsx领域非常成熟。读取示例import openpyxl workbook openpyxl.load_workbook(data.xlsx) sheet workbook.active # 默认sheet # 读取单元格 print(sheet[A1].value) # 获取最大行列 print(sheet.max_row, sheet.max_column) # 遍历指定区域 for row in sheet.iter_rows(min_row2, max_rowsheet.max_row, values_onlyTrue): print(row)写入示例import openpyxl workbook openpyxl.Workbook() sheet workbook.active sheet.title 结果 sheet.append([大区, 销售额, 日期]) sheet.append([华东, 123456, 2024-01-01]) workbook.save(result.xlsx)openpyxl的取值范围是1开始的这跟xlrd从0开始不同切换时容易搞混。我经常写混所以干脆在代码里写清楚注释。openpyxl还有一些比较高级的特性比如合并单元格、公式、冻结窗格、图表但基本读写用append和iter_rows就够。需要注意openpyxl读取包含公式的单元格时默认返回公式字符串如SUM(A1:A10)而不是计算后的值。如果你需要读取值可以使用data_onlyTrue参数但这要求文件本身是用Excel打开过并保存过的否则缓存里没有值会返回None。这个坑遇到的人很多我一般会同时保存两个工作簿一个带公式一个用data_only读值。3.4 用pandas一把梭大多数场景下的最优解如果只是想把Excel里的数据装进DataFrame做分析然后写回Excelpandas是效率最高的路径。读取import pandas as pd df pd.read_excel(data.xls, sheet_name销售额, header2) print(df.head())写入df.to_excel(output.xlsx, indexFalse)参数说明一下。sheet_name可以用索引或名字其默认值为0也就是读第一个sheet。header指定表头行索引默认0就是第一行。如果文件里前两行是标题第三行才是列名就设header2。还有一个usecols参数可以只读某些列比如usecolsA:D或usecols[0, 1, 3]大数据文件时能显著加快读取速度。但pandas也不是万能的。它的底层引擎是自动根据文件扩展名选择的读xls用xlrd读xlsx用openpyxl。如果你的环境中没有安装对应引擎pandas会报错。还有pandas会把整张表读进内存对大文件不太友好。另外读xls时如果遇到合并单元格pandas会把合并区域的上半部分填充值下半部分填充NaN。这些都需要额外处理。对于大多数表格清洗、加工、汇总的需求pandas绝对是首选。我处理几十个xls文件合并时基本就是循环读取然后concat成一个DataFrame最后to_excel输出。代码量少逻辑也清晰。4. 常见问题与排查实录我踩过的最多的坑4.1 文件格式与路径的坑错误AXLRDError: Excel xlsx file; not supported。这个大概率是xlrd版本太新新版已经不支持xlsx。解决方案确认文件扩展名如果真是xlsx换用openpyxl或pandas。如果是老.xls还要确认文件是否被另存为“网页版xls”或其他伪装格式。有些系统导出的所谓xls其实是HTML表格套了xls的壳。此时xlrd读取会报错强制用xlrd.open_workbook(..., formatting_infoFalse)也没用。可以尝试先用open看看文件头或者直接换pandas它会尝试解析HTML。但最稳妥的方案是让文件来源方重新导出。错误B路径带有中文或空格导致读取失败。特别是在Windows平台明明是正确路径Python却报找不到文件。先用os.path.exists检查建议统一使用绝对路径并确保路径字符串前加r防止转义。例如rD:\work\data.xls。还会遇到Excel文件被打开占用后程序写xls文件时报权限错误PermissionError。解决方法是先关闭Excel再执行写操作或者把输出文件换个名字。4.2 日期、编码、数字格式问题日期处理是xls读写最大的拦路虎。我在3.1已经简单提过这里展开。xlrd读到的日期类型是浮点数要转换成datetime.datetimeif cell.ctype xlrd.XL_CELL_DATE: date_tuple xlrd.xldate_as_tuple(cell.value, workbook.datemode) date_value datetime.datetime(*date_tuple)注意workbook.datemode字段1900或1904模式。如果修该不对日期会差很多天通常1900模式足够。如果你用openpyxl读xlsx日期类型已经转换好了不需要这个步骤。用pandas也一样。数字前导零丢失也是经典问题。Excel里的“001”显示成001但xlrd读出来是1。如果这个字段是编号、工号、电话等需要保持字符串。解决思路第一先检查单元格格式如果是文本格式xlrd会返回字符串。第二如果已经是数字类型可以在读取后格式化code int(cell.value) formatted f{code:05} # 补零到5位但问题是原来几位零长度未知这需要业务上下文。最稳妥的办法是在数据源头要求文本格式。如果文件已经固定只能手动处理。中文乱码问题。xlwt写入时如果没有设置encodingutf-8可能出现乱码。另外读取历史xls文件时如果内部编码不是utf-8可能读到奇怪的字符。xlrd本身会处理大部分情况但遇到一些来自老旧系统的文件还是会出现乱码。可以尝试用xlrd.open_workbook(..., encoding_overridegbk)指定编码。实测下来很多国内老文件的字符集是GBKencoding_override设为gbk能解决问题。4.3 合并单元格与空行脏数据合并单元格处理大概是办公自动化中最消耗耐心的工作。xlrd读取合并单元格时只有左上角单元格有值其他被合并的单元格返回空字符串。如果要填充相同的值需要自行处理合并区间映射merged_cells sheet.merged_cells # 返回列表如 [(row_lo, row_hi, col_lo, col_hi), ...] for rlo, rhi, clo, chi in merged_cells: cell_value sheet.cell_value(rlo, clo) for row_x in range(rlo, rhi): for col_y in range(clo, chi): # 这里可以填充到自己的二维数组中 matrix[row_x][col_y] cell_value用pandas遇到合并单元格会自动填充上面那个方向吗不pandas默认保留格式对合并单元格的实际处理是合并区域的首行为值其他行为NaN。处理起来还是得遍历。空行脏数据也常见。比如表格中间有整行空白读取后大量空字符串。方案很简单过滤掉全空的行。用pandas时直接df.dropna(howall)但如果某行只有个别列有值需要业务判断再过滤。还有一个很难防备的坑单元格里存在换行符\n导致打印时信息乱糟糟。可以统一str(value).strip()必要时把换行替换成空格。4.4 大文件的读取性能能跑但别硬扛xls格式本身最大65536行按说数据量不会特别大。但如果你读取的是xlsxopenpyxl在大文件面前就力不从心了。比如几万行、几十列的文件openpyxl内存占用会飙升。我试用20万行xlsx做测试内存占用接近1GB速度缓慢。优化思路有三个方向一是用pandas的read_excel(usecols...)只读所需列二是用read_only模式。openpyxl支持workbook openpyxl.load_workbook(big.xlsx, read_onlyTrue, data_onlyTrue)这个模式迭代读取内存占用低很多。三是数据量实在太大时放弃Excel改用CSV或parquet等格式。Excel本身就不是为大数据设计的。我在项目里一般超过5万行就考虑使用CSV或数据库Excel只保留给最终展示用。还有写入大文件时的性能问题。openpyxl往单元格逐格写入很慢优化方法是使用sheet.append()一次性写入整行。实测用append比循环write快一个数量级。如果涉及几十万行xlsx写入可以考虑openpyxl.Workbook(write_onlyTrue)配合ws.append()速度会好很多。5. 将读写能力串进实际场景一个完整的脚本模板前面讲了一堆库和函数下面给出一个我在实际工作中经常使用的模板用来处理一批xls文件清洗、合并并输出为xlsx。你可以直接复制改改就用。假设场景某目录下有一批销售数据xls文件文件名格式为“销售_202401.xls”结构统一第1-2行是大标题第3行是列名之后是数据。我需要把全部文件合并成一个总的DataFrame并输出为xlsx。import os import glob import pandas as pd def read_sales_file(file_path): # 前两行是标题跳过 df pd.read_excel(file_path, sheet_name0, header2) # 删除全空行 df df.dropna(howall) # 从文件名中提取月份放入新列 import re month_match re.search(r(\d{6}), os.path.basename(file_path)) df[月份] month_match.group(1) if month_match else None return df all_files glob.glob(data_dir/*.xls) all_dfs [] for file_path in all_files: print(正在处理:, file_path) try: df read_sales_file(file_path) all_dfs.append(df) except Exception as e: print(处理失败:, file_path, e) continue result pd.concat(all_dfs, ignore_indexTrue) result.to_excel(合并结果.xlsx, indexFalse)这个模板看起来很平淡但真正有价值的是其中的容错处理。很多人在循环读取时遇到一个坏文件就整个崩溃我加了try/except后就能跳过坏文件同时打印哪些文件失败方便后续单独修复。这比“一次全报错”要人性得多。实际项目中除了读取往往还要做数据清洗。常见操作包括把金额列转为数字pd.to_numeric、处理缺失值fillna、统一日期格式pd.to_datetime、去除前后空格str.strip()等。这些属于pandas基本功不细说。如果遇到复杂的报表可以在循环读完后逐列处理。用pandas的好处就是整个过程的可读性强每一步都在DataFrame上操作思路清晰。如果是需要保留原表格式的就只能用xlrd/xlwt手工操作单元格效率低但能做到精确控制。个人建议数据分析用pandas格式定制用openpyxl/xlwt两者搭配基本覆盖全部需求。6. 最后分享一条我多年养成的习惯写代码处理Excel时我总会先探明文件真实格式再决定用哪个库。因为“扩展名是xls”并不能保证文件真是xls比如有些系统导出的实际上是CSV或HTML。我用一段代码快速判断def check_file_type(file_path): with open(file_path, rb) as f: header f.read(8) if header.startswith(b\xD0\xCF\x11\xE0): return OLE2 (旧版xls) elif header.startswith(bPK\x03\x04): return ZIP (xlsx/docx等) elif header.startswith(b\xEF\xBB\xBF) or header[:1] b\xff\xfe: return 带BOM的文本文件如CSV elif header.startswith(b\x3C\x3F\x78\x6D\x6C): return XML/HTML else: return 其他这个函数只需要几行却能在第一步就拦截掉很多“假xls”文件节省大量排错时间。你也可以把文件拖进文本编辑器看开头字符OLE文件的文件头会显示一些乱码和D0 CF 11 E0字样ZIP文件则能看到PK开头。另外一个习惯是处理客户给的Excel时永远不要直接修改原始文件。我都是复制一份副本在副本上做读写操作。原因很简单一旦代码写错原始数据还在大不了重来。这个习惯救过我很多次尤其是在写回xls时xlwt只能整体重写文件一旦覆盖原文件就没了。还有读写Excel时尽量把输出编码固定为utf-8。对xlsx格式来说openpyxl默认就是用UTF-8处理xml内容问题不大。xls比较特殊xlwt设置encodingutf-8之后也遇到过某些老Excel打开时显示乱码但实际数据没问题。遇到这种情况不要慌先用Python读回验证即可。希望这篇从环境、选型、实操到排坑全覆盖的内容能帮你把python读写xls这件事彻底搞明白。它虽然不是高深技术但每一个坑都真实存在于项目里。按自己的数据形态选好库写好容错再加一份谨慎你就能在这条路上走得比别人稳。