简介本资源是一套面向Python初学者与办公自动化开发者的Excel操作实战代码集聚焦新版.xlsx与旧版.xls文件的读写、格式设置与批量修改等高频需求。资源共2094个文件以990个Python源码.py为核心辅以988个编译字节码.pyc、17个说明文本.txt、8个.xls和3个.xlsx示例文件以及配套工具脚本与环境配置文件整体压缩包大小为7.91MB。已有759人学习下载覆盖从基础语法到复杂格式控制如行高列宽调整、单元格合并、xlutils.copy动态修订的完整链路。所有案例均基于Python3环境清晰区分openpyxl与xlrd/xlutils的适用边界并提供可直接运行的脚本、典型错误处理逻辑及环境激活配置含activate.csh、pyvenv.cfg等便于快速复现与工程化迁移。1. 项目缘起为什么Python处理Excel是刚需如果你经常和数据打交道无论是做数据分析、自动化报表还是处理业务数据Excel几乎是一个绕不开的工具。但手动操作Excel尤其是面对成百上千个文件、需要重复执行合并、清洗、计算等任务时不仅效率低下还极易出错。这时候Python就成为了解放生产力的利器。我最初接触Python操作Excel就是因为每周都要手动从十几个部门的Excel报告中提取数据、汇总成一张总表这个过程耗时耗力还总担心复制粘贴出错。自从用Python脚本自动化后原本半天的工作现在几分钟就能搞定准确率100%。Python操作Excel的库有很多但最经典、应用最广的莫过于openpyxl和xlrd以及它的搭档xlwt、xlutils。openpyxl擅长读写.xlsx格式的现代Excel文件功能全面而xlrd则是读取老旧.xls格式的“老将”。虽然xlrd在2.0版本后停止了对.xlsx的支持专攻.xls但在处理历史遗留数据时依然不可或缺。网上教程很多但要么只讲openpyxl要么把几个库混在一起讲得不清不楚新手容易在环境、版本和API差异上踩坑。这篇文章我就结合自己多年的实战经验手把手带你从环境搭建到核心案例彻底掌握这两个库并厘清它们的使用边界和配合方式。2. 环境准备与库选型搞清你的Excel文件版本动手之前第一件事不是盲目安装而是先看清楚你要处理的Excel文件是什么格式。这直接决定了你该用哪个库以及如何安装。2.1 核心依赖库辨析与安装目前处理Excel的主流Python库形成了一个“分工明确”的生态openpyxl (推荐用于 .xlsx)角色全能型选手主打读写.xlsx格式Excel 2007及以上。能力创建、读取、修改Excel文件支持公式但默认不计算、图表、图像、单元格样式字体、颜色、边框、对齐、数据验证、过滤等高级功能。安装pip install openpyxlxlrd (专用于读取 .xls)角色老牌读取器仅用于读取.xls格式Excel 97-2003。重要版本变化xlrd在 2.0.0 版本之后移除了对.xlsx格式的支持。所以如果你需要读.xlsx请用openpyxl或pandas如果你需要读老的.xls就安装xlrd。安装pip install xlrd1.2.0建议指定1.2.0版本这是最后一个同时支持.xls和.xlsx的版本但为了清晰我建议按需安装新版专库专用。xlwt (专用于写入 .xls)角色xlrd的写入搭档仅用于写入.xls格式。注意它功能相对简单不支持.xlsx的很多新特性。安装pip install xlwtxlutils (桥梁工具)角色提供在xlrd和xlwt之间转换的实用工具比如用xlrd读取一个.xls文件后用xlutils.copy复制一份再通过xlwt进行修改并保存。安装pip install xlutilspandas (数据分析首选)角色数据分析领域的王者。它底层调用了openpyxl或xlrd来读写Excel但提供了极其简洁、强大的DataFrame数据结构来进行数据操作。如果你主要是做数据清洗、分析和计算pandas是更高效的选择。安装pip install pandas openpyxl xlrd通常一起安装我的选型建议处理现代 .xlsx 文件且需要精细控制格式、图表首选openpyxl。处理老旧 .xls 文件使用xlrd读 xlwt写组合或通过xlutils桥接。核心是数据操作、分析对格式要求不高直接用pandas它的read_excel()和to_excel()方法会自动根据文件后缀选择引擎非常省心。项目需要同时兼容 .xls 和 .xlsx可以结合使用。用pandas做统一入口是最优雅的或者自己写逻辑判断文件后缀分别调用openpyxl和xlrd。注意很多教程会直接pip install xlrd然后用来读.xlsx报错就是因为版本问题。务必确认你的xlrd版本和文件格式匹配。一个简单的检查方法是安装后在Python中import xlrd; print(xlrd.__version__)查看版本。2.2 虚拟环境与依赖管理为了避免不同项目间的库版本冲突强烈建议使用虚拟环境。这里以venv为例Python 3.3 内置# 在你的项目目录下 python3 -m venv excel_env # 激活虚拟环境 # Windows: excel_env\Scripts\activate # macOS/Linux: source excel_env/bin/activate # 激活后命令行提示符前会出现 (excel_env)表示已在虚拟环境中 # 然后安装所需库 pip install openpyxl xlrd1.2.0 xlwt xlutils pandas3. openpyxl 核心操作实战从零创建到复杂格式openpyxl的对象模型非常清晰主要围绕三个核心对象Workbook工作簿、Worksheet工作表、Cell单元格。我们通过一个完整的案例来串联。假设我们要创建一个员工月度绩效报表包含数据、公式和简单格式。3.1 创建新工作簿与写入数据import openpyxl from openpyxl.styles import Font, Alignment, Border, Side, PatternFill from openpyxl.utils import get_column_letter # 1. 创建一个新的工作簿 wb openpyxl.Workbook() # 获取默认激活的工作表 ws wb.active ws.title 三月绩效 # 给工作表重命名 # 2. 写入表头 headers [员工ID, 姓名, 部门, 工时, 任务完成量, 绩效得分] for col_num, header in enumerate(headers, start1): col_letter get_column_letter(col_num) ws[f{col_letter}1] header # 顺便设置一下表头样式 ws[f{col_letter}1].font Font(boldTrue, size12, colorFFFFFF) ws[f{col_letter}1].fill PatternFill(start_color366092, end_color366092, fill_typesolid) ws[f{col_letter}1].alignment Alignment(horizontalcenter) # 3. 写入示例数据 data [ [101, 张三, 技术部, 160, 45, None], # 绩效得分留空后面用公式计算 [102, 李四, 市场部, 155, 38, None], [103, 王五, 技术部, 175, 52, None], [104, 赵六, 行政部, 150, 30, None], ] for row_num, row_data in enumerate(data, start2): # 从第2行开始 for col_num, cell_value in enumerate(row_data, start1): ws.cell(rowrow_num, columncol_num, valuecell_value) # 4. 插入公式假设绩效得分 任务完成量 * 10 / 工时 for row in range(2, len(data)2): # 在F列第6列写入公式引用同行的D列工时和E列任务量 ws.cell(rowrow, column6).value fE{row}*10/D{row} # 设置数字格式为保留两位小数 ws.cell(rowrow, column6).number_format 0.00 # 5. 调整列宽根据内容自动调整这是个常用技巧 for column in ws.columns: max_length 0 column_letter get_column_letter(column[0].column) # 获取列字母 for cell in column: try: if len(str(cell.value)) max_length: max_length len(str(cell.value)) except: pass adjusted_width (max_length 2) ws.column_dimensions[column_letter].width adjusted_width # 6. 保存工作簿 wb.save(月度绩效报表.xlsx) print(Excel文件 月度绩效报表.xlsx 创建成功)代码解读与避坑点ws.cell(row1, column1).value和ws[A1].value是等价的后者更简洁。公式写入直接给cell.value赋值一个以开头的字符串即可。注意openpyxl默认只保存公式不计算结果。文件在Excel中打开时才会计算。如果你需要提前得到计算结果可以设置wb openpyxl.load_workbook(filename, data_onlyTrue)来加载但这样公式本身会丢失只保留最后一次计算的值。样式设置openpyxl.styles模块提供了丰富的样式类。样式是赋值给cell对象的属性如cell.font。一个常见的坑是如果你需要给多个单元格设置相同样式最好先创建一个样式对象然后重复赋值而不是每次循环都创建新对象这样效率更高。列宽调整openpyxl没有真正的“自动调整列宽”函数上面的代码是一个常用的模拟方法。ws.column_dimensions[column_letter].width设置的是字符数。3.2 读取与修改现有Excel文件现在我们来读取刚才创建的文件给绩效得分高于4.0的员工高亮显示。import openpyxl from openpyxl.styles import PatternFill # 加载已存在的工作簿注意默认是可读写模式 wb openpyxl.load_workbook(月度绩效报表.xlsx) ws wb[三月绩效] # 通过工作表名获取也可以用 wb.active # 定义高亮填充样式 highlight_fill PatternFill(start_colorFFFF00, end_colorFFFF00, fill_typesolid) # 黄色填充 # 遍历数据行从第2行到最后有数据的行 for row in ws.iter_rows(min_row2, max_col6, max_rowws.max_row, values_onlyFalse): # row 是一个由Cell对象组成的元组 performance_cell row[5] # 第6列绩效得分 (索引从0开始) # 注意直接读取的公式单元格其.value是公式字符串不是计算结果。 # 我们需要用data_only模式加载或者这里我们假设文件已被Excel计算过并保存。 # 更稳妥的方式用data_only模式重新加载一次。 # 为了演示我们这里假设单元格已经是数值。 try: score float(performance_cell.value) if score 4.0: # 高亮该行所有单元格 for cell in row: cell.fill highlight_fill except (TypeError, ValueError): # 如果单元格不是数字跳过 continue # 保存到新文件避免覆盖原文件 wb.save(月度绩效报表_高亮.xlsx) print(高亮处理完成保存为 月度绩效报表_高亮.xlsx)关键点ws.iter_rows()是遍历行的推荐方法values_onlyFalse返回Cell对象可以修改样式values_onlyTrue只返回值速度快但无法修改。ws.max_row和ws.max_column属性可以获取工作表的最大行和列但要注意它们可能包含之前操作过但已清空内容的单元格不一定完全准确。公式计算结果读取这是openpyxl的一个经典大坑。如果文件上次被Excel打开并保存过计算结果会被缓存。用load_workbook(filename, data_onlyTrue)加载可以读到缓存的计算结果但读不到公式本身。如果文件从未被Excel计算过比如纯Python生成的那么data_only模式读到的公式单元格将是None。处理包含公式的文件时务必明确你的需求。3.3 处理多个工作表与复杂操作openpyxl还能轻松处理多工作表、合并单元格、插入图表等。import openpyxl from openpyxl.chart import BarChart, Reference # 假设我们有一个包含多个部门数据的字典 department_data { 技术部: {工时: [160, 175, 168], 任务量: [45, 52, 48]}, 市场部: {工时: [155, 148, 162], 任务量: [38, 42, 40]}, 行政部: {工时: [150, 145, 152], 任务量: [30, 28, 33]}, } wb openpyxl.Workbook() for dept_name, data in department_data.items(): # 为每个部门创建一个新的工作表 if dept_name wb.active.title: ws wb.active ws.title dept_name else: ws wb.create_sheet(titledept_name) # 写入部门数据 ws.append([员工序号, 工时, 任务完成量]) for i in range(len(data[工时])): ws.append([i1, data[工时][i], data[任务量][i]]) # 创建图表 chart BarChart() chart.type col # 柱状图 chart.title f{dept_name} 工时与任务量对比 chart.x_axis.title 员工 chart.y_axis.title 数值 # 确定数据范围 data_ref Reference(ws, min_col2, min_row1, max_col3, max_rowlen(data[工时])1) cats_ref Reference(ws, min_col1, min_row2, max_rowlen(data[工时])1) chart.add_data(data_ref, titles_from_dataTrue) chart.set_categories(cats_ref) # 将图表添加到工作表的指定位置 ws.add_chart(chart, E2) # 删除默认创建的空白工作表如果有的话 if Sheet in wb.sheetnames: std_sheet wb[Sheet] wb.remove(std_sheet) wb.save(部门数据汇总_带图表.xlsx) print(多工作表图表文件创建成功)4. xlrd/xlwt/xlutils 处理遗留 .xls 文件尽管.xls格式已逐渐淘汰但大量历史数据仍以此格式存在。处理它们我们需要xlrd读和xlwt写。4.1 使用 xlrd 读取 .xls 文件import xlrd # 打开一个 .xls 文件 workbook xlrd.open_workbook(历史数据_2003.xls) # 1. 获取所有工作表名 sheet_names workbook.sheet_names() print(f工作表列表: {sheet_names}) # 2. 通过索引或名称获取工作表 sheet workbook.sheet_by_index(0) # 第一个工作表 # 或者 sheet workbook.sheet_by_name(Sheet1) # 3. 获取工作表的基本信息 print(f工作表名称: {sheet.name}) print(f行数: {sheet.nrows}) print(f列数: {sheet.ncols}) # 4. 读取单元格数据 # 方法1通过行列索引从0开始 cell_value sheet.cell_value(rowx0, colx0) # 读取A1单元格 print(fA1单元格的值: {cell_value}) # 方法2通过单元格名称需要转换xlrd不直接支持A1表示法通常用索引 # 但可以读取合并单元格的信息 cell sheet.cell(0, 0) print(f单元格类型: {cell.ctype}) # 类型 0 empty, 1 string, 2 number, 3 date, 4 boolean, 5 error print(f单元格值: {cell.value}) # 5. 按行或列读取 print(\n读取前3行数据:) for row_index in range(min(3, sheet.nrows)): # 避免行数不足 row_values sheet.row_values(row_index) print(row_values) # 6. 处理日期.xls中的日期是浮点数 if cell.ctype xlrd.XL_CELL_DATE: date_tuple xlrd.xldate_as_tuple(cell.value, workbook.datemode) print(f日期元组: {date_tuple}) # (年, 月, 日, 时, 分, 秒) # 可以转换为datetime对象 from datetime import datetime py_date datetime(*date_tuple[:6]) print(fPython日期: {py_date})xlrd 读取的注意事项索引从0开始这是与openpyxl从1开始和 Excel 自身A1表示法的主要区别很容易搞混。日期处理Excel内部将日期存储为浮点数从某个基准日算起的天数。xlrd可以识别并转换。workbook.datemode很重要0代表1900日期系统Windows默认1代表1904日期系统Mac默认。性能对于非常大的.xls文件sheet.row_values()或sheet.col_values()比循环cell_value更快。只读xlrd只能读不能修改。4.2 使用 xlwt 写入 .xls 文件xlwt的API相对古老和简单。import xlwt # 1. 创建一个新的工作簿 wb xlwt.Workbook(encodingutf-8) # 2. 添加一个工作表 ws wb.add_sheet(员工信息) # 3. 设置样式可选 style_header xlwt.easyxf(font: bold on; align: horiz center) style_currency xlwt.easyxf(num_format_str#,##0.00) # 4. 写入数据 headers [姓名, 部门, 薪资] for col, header in enumerate(headers): ws.write(0, col, header, style_header) # 第0行第col列 data [ [张三, 技术部, 15000], [李四, 市场部, 12000], [王五, 行政部, 8000], ] for row_idx, row_data in enumerate(data, start1): for col_idx, cell_data in enumerate(row_data): if col_idx 2: # 薪资列应用货币格式 ws.write(row_idx, col_idx, cell_data, style_currency) else: ws.write(row_idx, col_idx, cell_data) # 5. 保存文件 wb.save(员工信息_2003.xls) print(.xls 文件写入成功)xlwt 的局限性不支持.xlsx格式。不支持Excel 2007的某些高级功能如超过65536行、256列但.xls本身也不支持。样式系统 (easyxf) 功能有限且语法与openpyxl不同。4.3 使用 xlutils 修改现有 .xls 文件xlutils.copy模块是连接xlrd和xlwt的桥梁它允许你复制一个xlrd的只读工作簿得到一个xlwt的可写工作簿副本从而实现对原有文件的修改。from xlrd import open_workbook from xlutils.copy import copy # 1. 用 xlrd 打开一个现有的 .xls 文件 rb open_workbook(历史数据_2003.xls, formatting_infoTrue) # formatting_infoTrue 会保留原有格式但可能增加内存消耗 # 2. 复制一份得到一个可写的 xlwt 工作簿对象 wb copy(rb) # 3. 获取要操作的工作表通过索引 ws wb.get_sheet(0) # 注意这里用的是 get_sheet返回的是 xlwt.Worksheet 对象 # 4. 修改内容例如在A10单元格写入新数据 ws.write(9, 0, 修改后的数据) # 第10行第1列索引从0开始 # 5. 保存为新文件不能直接覆盖原文件实际上可以但建议先保存为新文件以防出错 wb.save(历史数据_2003_修改后.xls) print(文件修改并保存成功)重要提示xlutils.copy在复制时formatting_infoTrue参数会尝试复制原文件的格式信息但这并非完美复杂的格式可能会丢失或出错。对于重要的格式保留需求需要谨慎测试。5. 实战案例一个通用的Excel数据清洗与合并脚本结合以上知识我们来看一个真实的实战场景你有一批以日期命名的.xlsx和.xls格式的销售日报需要将它们合并到一个总表中并做简单的数据清洗如去除空行、统一日期格式。import os import glob from datetime import datetime import openpyxl import xlrd import pandas as pd def read_excel_file(file_path): 根据文件后缀使用合适的库读取Excel文件返回一个DataFrame列表每个工作表一个 _, ext os.path.splitext(file_path) dfs [] try: if ext.lower() .xlsx: # 使用 openpyxl 引擎通过 pandas 读取 # pandas的read_excel默认用openpyxl读.xlsx xl pd.ExcelFile(file_path, engineopenpyxl) elif ext.lower() .xls: # 使用 xlrd 引擎 xl pd.ExcelFile(file_path, enginexlrd) else: print(f跳过不支持的文件格式: {file_path}) return dfs for sheet_name in xl.sheet_names: df xl.parse(sheet_name) # 添加来源信息列 df[_source_file] os.path.basename(file_path) df[_source_sheet] sheet_name dfs.append(df) except Exception as e: print(f读取文件 {file_path} 时出错: {e}) return dfs def clean_and_merge_data(data_dir, output_file合并报表.xlsx): 清洗并合并指定目录下的所有Excel文件 all_data_frames [] # 查找目录下所有 .xls 和 .xlsx 文件 excel_files glob.glob(os.path.join(data_dir, *.xls)) glob.glob(os.path.join(data_dir, *.xlsx)) if not excel_files: print(f在目录 {data_dir} 中未找到Excel文件。) return print(f找到 {len(excel_files)} 个Excel文件开始处理...) for file in excel_files: print(f 正在处理: {file}) dfs read_excel_file(file) all_data_frames.extend(dfs) if not all_data_frames: print(没有读取到任何有效数据。) return # 合并所有DataFrame merged_df pd.concat(all_data_frames, ignore_indexTrue, sortFalse) # 数据清洗示例 # 1. 删除完全为空的行 merged_df.dropna(howall, inplaceTrue) # 2. 重置索引 merged_df.reset_index(dropTrue, inplaceTrue) # 3. 假设有一个日期列尝试统一格式 if 日期 in merged_df.columns: # 尝试转换为datetime格式errorscoerce将转换失败的设为NaT merged_df[日期] pd.to_datetime(merged_df[日期], errorscoerce) # 将日期格式化为字符串 YYYY-MM-DD merged_df[日期] merged_df[日期].dt.strftime(%Y-%m-%d) print(f数据合并完成总行数: {len(merged_df)}) # 使用 openpyxl 引擎保存为 .xlsx with pd.ExcelWriter(output_file, engineopenpyxl) as writer: merged_df.to_excel(writer, sheet_name合并数据, indexFalse) # 可以在这里利用 openpyxl 的更多功能比如调整列宽、添加格式 workbook writer.book worksheet writer.sheets[合并数据] # 自动调整列宽近似 for column in worksheet.columns: max_length 0 column_letter openpyxl.utils.get_column_letter(column[0].column) for cell in column: try: cell_value_length len(str(cell.value)) if cell_value_length max_length: max_length cell_value_length except: pass adjusted_width min(max_length 2, 50) # 设置最大宽度为50 worksheet.column_dimensions[column_letter].width adjusted_width # 冻结首行 worksheet.freeze_panes A2 print(f合并后的文件已保存为: {output_file}) # 使用示例 if __name__ __main__: # 假设你的日报文件都在 ./daily_reports 目录下 data_directory ./daily_reports clean_and_merge_data(data_directory, 2024年第一季度销售汇总.xlsx)这个脚本的亮点与心得智能识别格式通过文件后缀自动选择pandas的读取引擎统一了API代码更简洁。pandas的read_excel是处理这类任务的神器。数据溯源在合并时添加了_source_file和_source_sheet列方便后续追踪数据来源这在处理大量文件时非常有用。健壮性处理使用了try...except捕获单个文件读取错误避免一个坏文件导致整个任务失败。pd.to_datetime的errorscoerce参数将无法解析的日期设为空值而不是直接报错。结合优势用pandas做核心的数据操作读取、合并、清洗用openpyxl做最终的格式美化调整列宽、冻结窗格发挥了各自的长处。性能考虑对于超大型文件一次性读入所有数据可能内存不足。在实际生产环境中可能需要分批读取或使用pandas的chunksize参数。6. 常见问题排查与性能优化心得在长期使用中我积累了一些踩坑经验和优化技巧。6.1 编码与日期问题中文乱码xlwt写入时确保Workbook(encodingutf-8)。openpyxl对UTF-8支持良好一般没问题。如果从其他系统生成的文件读取出乱码可能是文件本身编码问题可以尝试用xlrd打开时指定编码但选项有限。日期错位这是最常遇到的问题。核心是区分“1900年日期系统”和“1904年日期系统”。Mac版Excel默认使用1904系统。用xlrd读取时workbook.datemode属性指明了是哪种系统。用openpyxl写入日期时直接赋值datetime对象即可库会帮你处理转换。建议在代码中统一使用Python的datetime或pandas.Timestamp对象进行操作只在最后写入单元格时让库去转换。6.2 内存与性能优化处理几十MB甚至上百MB的Excel文件时需要特别注意。openpyxl的只读/只写模式openpyxl.load_workbook(filename, read_onlyTrue)以只读模式加载适用于从超大文件读取数据不关心样式。它不会将整个文件加载到内存而是流式读取。openpyxl.Workbook(write_onlyTrue)以只写模式创建适用于生成超大文件。你不能读取或修改已写入的单元格但内存占用极低。# 只写模式示例 from openpyxl import Workbook wb Workbook(write_onlyTrue) ws wb.create_sheet() # 必须使用 ws.append() 添加整行数据 for row in large_data_set: ws.append(row) # row 是一个列表或元组 wb.save(huge_file.xlsx)pandas的chunksize用pandas.read_excel读取超大文件时如果内存不足可以考虑先将其导出为CSV或使用数据库或者如果文件是多个工作表的分表读取。避免不必要的样式操作创建和赋值样式对象有一定开销。如果需要给大量单元格设置相同样式务必在循环外创建一次样式对象然后在循环内赋值。6.3 公式与链接处理公式不计算如前所述openpyxl保存的是公式字符串。如果需要Python计算有几种思路使用data_onlyTrue加载已被Excel计算过的文件。不使用Excel公式而用Python计算好结果再写入。使用pycel或formulas等第三方库在Python中评估Excel公式比较复杂。外部链接/引用如果Excel文件包含指向其他文件的链接openpyxl默认会保留它们但打开时可能会提示更新链接。如果不想要可以在加载时设置keep_linksFalse。6.4 版本兼容性.xls文件限制.xls格式最大支持65536行、256列IV列。如果你的数据超出这个范围必须使用.xlsx格式。库版本始终注意xlrd2.0.0 不再支持.xlsx。使用pandas时确保安装了正确版本的引擎openpyxl用于.xlsxxlrd2.0.0或xlrd用于.xls。pandas1.3.0以后默认用openpyxl读.xlsx。最后我个人最深的体会是不要试图用openpyxl或xlrd去模拟所有Excel手工操作。它们的优势在于自动化、批量化处理。对于极其复杂的格式、图表或宏有时用Python生成数据然后调用Excel模板通过openpyxl填充数据或使用COM接口在Windows上如pywin32可能是更可行的方案。但对于90%的日常数据处理和报表自动化需求掌握好openpyxl和pandas配合xlrd处理历史文件的组合就足以让你从重复劳动中彻底解放出来。本文还有配套的精品资源点击获取