Python办公自动化：openpyxl与xlrd库实战Excel数据处理

简介本资源是一套面向Python初学者与办公自动化开发者的Excel操作实战代码集聚焦Python3环境下新版.xlsx与旧版.xls文件的高效读写与格式控制。资源涵盖openpyxl库对.xlsx文件的完整操作含单元格写入、行高列宽设置、合并单元格等以及xlrdx1utils.copy组合对.xls文件的读取与修订流程解决日常数据处理中跨版本Excel兼容性难题。压缩包共2094个文件主体为990个可运行Python源码.py与988个编译缓存.pyc辅以17个说明文本、8个示例Excel.xls/.xlsx及若干环境配置与安装文件如.cfg、.exe、.wheel整体大小7.91MB结构清晰、即下即用。目前已有759人学习下载提供从基础语法到工程化修改的完整链路脚本包含多场景实操案例、常见报错处理提示及目录模块化组织便于对照调试与快速复用。1. 项目概述为什么Python处理Excel是程序员的必备技能在数据驱动的今天Excel表格依然是业务流转、数据分析和报告生成的核心载体。无论是市场部门的销售报表还是研发团队的测试用例清单甚至是个人管理自己的月度账单Excel无处不在。作为一名开发者我们经常需要与这些表格打交道从里面读取数据进行分析将程序处理的结果写入表格生成报告或者批量修改成百上千个文件中的格式。如果每次都手动操作不仅效率低下而且极易出错。这时候Python就成为了我们的“瑞士军刀”。Python之所以能成为处理Excel的利器主要得益于其丰富且强大的第三方库生态。其中openpyxl和xlrd是两个绕不开的名字。openpyxl专注于读写.xlsx格式的Excel文件功能全面支持单元格样式、图表、公式等高级特性而xlrd则是一个经典的读取库虽然在新版本中已停止对.xlsx的更新支持但其读取.xls老格式文件的能力依然稳定可靠。掌握这两个库意味着你能应对绝大多数与Excel交互的自动化需求。这篇文章我将从一个有十多年编码经验的开发者视角带你深入这两个库的核心。我不会只给你一堆冰冷的API列表而是结合真实的业务场景拆解每一步操作背后的逻辑分享我踩过的坑和总结出的高效技巧。无论你是刚接触Python的新手还是想系统提升办公自动化能力的老手这篇内容都能让你获得即学即用的实战能力。2. 核心库选型与项目环境搭建2.1 openpyxl vs xlrd如何根据场景做选择面对“用Python操作Excel”这个需求新手最容易犯的错就是库没选对导致代码写到一半发现功能不支持又得推倒重来。openpyxl和xlrd各有其明确的定位和适用场景理解它们的差异是第一步。openpyxl全能型现代选手openpyxl是处理.xlsx格式Excel 2007及以上版本的首选库。它的核心优势在于“读写兼备”且“功能完整”。核心能力不仅能读取单元格数据还能创建新工作簿、写入数据、设置复杂的单元格样式字体、颜色、边框、对齐方式、插入图片、创建图表如柱状图、折线图、散点图甚至能处理一些简单的公式。如果你的需求涉及生成一个格式美观、带有图表的数据报告openpyxl是唯一选择。性能考量它提供了两种读取模式默认的load_workbook()会将整个工作簿加载到内存适合文件不大或需要频繁修改的场景而read_onlyTrue模式则以只读、流式的方式加载对于处理几十MB甚至上百MB的超大Excel文件能极大节省内存。适用场景自动化生成报告、批量修改表格样式和内容、从数据库数据生成可视化Excel图表、处理包含公式和图片的复杂模板。xlrd专精读取的“老将”xlrd库的历史更悠久其设计哲学是“专注于读取”。需要注意的是从xlrd2.0.0版本开始它移除了对.xlsx格式的支持只专注于读取旧的.xls格式Excel 97-2003。因此它的定位非常清晰。核心能力快速、稳定地读取.xls文件中的数据、单元格格式数据类型、合并单元格信息、工作表名称等。它的API简洁直观对于纯读取操作特别是处理遗留系统生成的.xls文件非常高效。性能与局限它不支持任何写入操作也不支持.xlsx格式。如果你的数据源全是老旧的.xls文件且只需要读取那么xlrd轻量且稳定。适用场景处理历史遗留的.xls格式数据文件、快速进行数据抽取和清洗只读、与仅支持.xls格式的旧系统交互。选型决策流程图心法文件格式是什么.xlsx选openpyxl.xls且只需读取选xlrd。需要写入或修改吗如果需要只能选openpyxl。文件是否巨大50MB如果是且只需读取.xlsx用openpyxl的只读模式。需要处理图表、图片或复杂样式吗如果是选openpyxl。一个常见的组合拳是使用xlrd快速读取老.xls数据然后用openpyxl写入并美化新的.xlsx报告。2.2 一步到位的环境配置与依赖管理很多教程只告诉你pip install但忽略了环境隔离的重要性。直接在系统Python里装库日后项目一多版本冲突会让你头疼不已。我强烈推荐使用虚拟环境。使用venv创建纯净环境Windows/macOS/Linux通用# 1. 在你的项目目录下打开终端或命令行 # 2. 创建虚拟环境环境文件夹命名为 venv (你可以用其他名字) python -m venv venv # 3. 激活虚拟环境 # Windows (CMD或PowerShell): venv\Scripts\activate # macOS/Linux: source venv/bin/activate # 激活后命令行提示符前通常会显示 (venv)表示你已进入该环境。安装依赖库 在激活的虚拟环境下执行安装命令。这里我们安装openpyxl和xlrd注意我们安装的是兼容.xls的1.2.0版本。pip install openpyxl pip install xlrd1.2.0 # 指定安装1.2.0版本以确保支持.xls注意pip install xlrd默认会安装最新的2.x版本该版本不支持.xls。所以必须指定1.2.0这个版本号。这是新手最容易踩的坑之一。验证安装 安装完成后可以启动Python交互界面简单测试。import openpyxl import xlrd print(openpyxl.__version__) print(xlrd.__version__)如果没有报错并输出版本号如3.1.2和1.2.0说明环境配置成功。3. openpyxl核心操作全解析从读写到图表3.1 工作簿与工作表一切操作的起点使用openpyxl一切操作都始于工作簿Workbook对象。你可以加载一个已存在的文件也可以从头创建一个新的。加载与创建from openpyxl import load_workbook, Workbook # 场景1读取一个已存在的Excel文件 excel_path ‘销售数据.xlsx‘ wb load_workbook(filenameexcel_path) # 默认加载所有内容 # 场景2处理超大文件使用只读模式节省内存 wb_readonly load_workbook(filename‘大数据文件.xlsx‘, read_onlyTrue) # 场景3创建一个全新的工作簿 wb_new Workbook() # 新建的工作簿会自动包含一个名为‘Sheet‘的活动工作表工作表的获取与操作 工作簿包含一个或多个工作表Worksheet我们需要先获取到具体的工作表对象才能进行单元格操作。# 方法1通过名称获取特定工作表 sheet wb[‘2024年Q1‘] # 获取名为‘2024年Q1‘的工作表 # 方法2获取活动工作表当前选中的表 active_sheet wb.active # 方法3获取所有工作表名称 sheet_names wb.sheetnames print(f“所有工作表{sheet_names}“) # 创建新的工作表 wb.create_sheet(title‘汇总页‘, index0) # 在索引0的位置最前面创建 wb.create_sheet(title‘分析页‘) # 默认在最后创建 # 删除工作表 if ‘临时表‘ in wb.sheetnames: del wb[‘临时表‘] # 方式一 # wb.remove(wb[‘临时表‘]) # 方式二效果相同 # 修改工作表名称 sheet.title ‘第一季度数据已清洗‘3.2 单元格操作数据的精准读写与样式美化单元格是Excel的基石。openpyxl提供了多种方式来定位和操作单元格。单元格定位与值读写# 方式A通过类似Excel的‘A1‘坐标字符串 cell_a1 sheet[‘A1‘] print(cell_a1.value) # 读取值 cell_a1.value ‘产品名称‘ # 写入值 # 方式B通过行列索引注意索引从1开始不是0 cell_b2 sheet.cell(row2, column2, value‘这是B2单元格‘) # 可以在获取时直接赋值 print(sheet.cell(row1, column3).value) # 读取C1单元格 # 方式C批量操作一行或一列 for row in sheet.iter_rows(min_row2, max_row10, min_col1, max_col5): # row是一个元组包含该行的多个单元格对象 for cell in row: print(cell.value, end‘\t‘) print() # 换行 # 获取最大行和最大列用于动态遍历 max_row sheet.max_row max_column sheet.max_column单元格样式设置让你的报表更专业 仅仅有数据不够好的格式能极大提升报表的可读性。openpyxl的样式功能非常强大。from openpyxl.styles import Font, Alignment, Border, Side, PatternFill from openpyxl.styles.numbers import FORMAT_CURRENCY_USD_SIMPLE # 1. 字体与颜色 header_font Font(name‘微软雅黑‘, size12, boldTrue, color‘FF0000‘) # 红色加粗 sheet[‘A1‘].font header_font # 2. 对齐方式 center_alignment Alignment(horizontal‘center‘, vertical‘center‘, wrap_textTrue) sheet[‘B2‘].alignment center_alignment # 水平垂直居中并允许自动换行 # 3. 边框 thin_border Border(leftSide(style‘thin‘, color‘000000‘), rightSide(style‘thin‘, color‘000000‘), topSide(style‘thin‘, color‘000000‘), bottomSide(style‘thin‘, color‘000000‘)) sheet[‘C3‘].border thin_border # 给C3单元格加上细黑边框 # 4. 填充背景色 yellow_fill PatternFill(start_color‘FFFF00‘, end_color‘FFFF00‘, fill_type‘solid‘) sheet[‘D4‘].fill yellow_fill # 设置D4单元格背景为黄色 # 5. 数字格式 sheet[‘E5‘].value 1234.56 sheet[‘E5‘].number_format FORMAT_CURRENCY_USD_SIMPLE # 设置为美元货币格式显示为 $1,234.56 # 也可以使用Excel自定义格式字符串 sheet[‘F6‘].number_format ‘0.00%‘ # 设置为百分比格式保留两位小数合并单元格与公式# 合并A1到D1的单元格常用于制作标题 sheet.merge_cells(‘A1:D1‘) sheet[‘A1‘].value ‘2024年度销售总览‘ sheet[‘A1‘].alignment Alignment(horizontal‘center‘) # 写入公式。注意openpyxl只负责写入公式字符串计算结果由Excel打开时计算。 sheet[‘E10‘].value ‘SUM(E2:E9)‘ # 在E10单元格写入求和公式 # 如果你需要Python端也得到计算结果可以借助data_onlyTrue模式加载已计算好的文件但无法直接计算。3.3 高级功能图表生成与图像插入这是openpyxl区别于简单读写库的亮点能让你自动生成带图表的专业报告。生成图表以散点图为例 网络热词中提到了“openpyxl 制作散点图”和“openpyxl 的series 参数有什么”这里详细展开。from openpyxl import Workbook from openpyxl.chart import ScatterChart, Reference, Series wb Workbook() ws wb.active # 1. 准备一些示例数据 data [ [‘月份‘, ‘销售额‘, ‘成本‘], [1, 120, 80], [2, 150, 95], [3, 180, 110], [4, 200, 130], ] for row in data: ws.append(row) # 2. 创建散点图对象 chart ScatterChart() chart.title “销售额与成本趋势分析“ chart.x_axis.title ‘月份‘ chart.y_axis.title ‘金额‘ chart.legend.position ‘r‘ # 图例放在右侧 # 3. 定义数据区域Reference对象 # 参数工作表对象最小行最小列最大行最大列 x_data Reference(ws, min_col1, min_row2, max_row5) # 月份数据 (A2:A5) y1_data Reference(ws, min_col2, min_row2, max_row5) # 销售额数据 (B2:B5) y2_data Reference(ws, min_col3, min_row2, max_row5) # 成本数据 (C2:C5) # 4. 创建系列Series对象并添加到图表 # Series参数详解 # values: Y轴数据Reference对象 # xvalues: X轴数据Reference对象散点图特有 # title: 该系列在图例中显示的名称 series1 Series(valuesy1_data, xvaluesx_data, title“销售额“) series2 Series(valuesy2_data, xvaluesx_data, title“成本“) chart.series.append(series1) chart.series.append(series2) # 5. 将图表添加到工作表的指定位置 ws.add_chart(chart, “E2“) # 将图表左上角锚定在E2单元格 wb.save(“带散点图的报告.xlsx“)实操心得Series的xvalues参数是散点图、折线图等需要X轴序列的图表才需要的。对于柱状图BarChart通常只需要values参数X轴的类别标签通过chart.set_categories()来设置。理解图表类型和数据引用Reference的关系是关键。插入图片from openpyxl.drawing.image import Image # 假设有一张公司logo图片‘logo.png‘ logo Image(‘logo.png‘) # 调整图片尺寸可选 logo.width 100 logo.height 50 # 将图片添加到工作表的A1单元格附近 ws.add_image(logo, ‘A1‘) # 图片的左上角将对齐到A1单元格3.4 保存工作簿最后的临门一脚所有操作完成后必须调用save方法才能将更改持久化到磁盘。# 保存到新文件 wb.save(‘新报告.xlsx‘) # 覆盖保存原文件谨慎操作建议先备份 # wb.save(‘销售数据.xlsx‘)重要提示openpyxl的save()操作是覆盖性的。对于重要文件我习惯先保存一个副本如原文件_备份.xlsx或者使用不同的文件名进行保存避免误操作导致数据丢失。4. xlrd核心操作精讲专注高效的.xls文件读取如前所述xlrd2.x版本已不再支持.xlsx。我们使用1.2.0版本它稳定且高效。4.1 打开工作簿与获取工作表信息xlrd的API非常直接主要围绕open_workbook函数展开。import xlrd # 打开一个.xls文件 book xlrd.open_workbook(‘历史数据_2003.xls‘) # 获取所有工作表名称 sheet_names book.sheet_names() print(f“工作表列表{sheet_names}“) # 通过索引或名称获取工作表对象 # 方式1通过索引从0开始 sheet_by_index book.sheet_by_index(0) # 方式2通过名称 sheet_by_name book.sheet_by_name(‘Sheet1‘)4.2 读取单元格数据与遍历技巧xlrd读取数据主要通过工作表对象的cell_value方法或者直接遍历行。sheet book.sheet_by_index(0) # 1. 读取特定单元格的值行列索引从0开始 value_a1 sheet.cell_value(rowx0, colx0) # 读取A1单元格 print(f“A1的值是{value_a1}“) # 2. 获取工作表维度 nrows sheet.nrows # 总行数 ncols sheet.ncols # 总列数 print(f“该表有{nrows}行{ncols}列“) # 3. 逐行读取最常用 for row_index in range(sheet.nrows): # 获取一整行的数据返回一个列表 row_values sheet.row_values(row_index) print(row_values) # 例如[‘张三‘, 28, ‘工程师‘] # 4. 逐列读取特定场景下有用 for col_index in range(sheet.ncols): col_values sheet.col_values(col_index) print(f“第{col_index1}列数据{col_values}“) # 5. 读取单元格的数据类型有时很重要 cell sheet.cell(0, 0) # 获取单元格对象而不仅仅是值 print(f“单元格类型代码{cell.ctype}“) print(f“单元格实际值{cell.value}“) # ctype: 0empty, 1string, 2number, 3date, 4boolean, 5error # 对于日期类型ctype3需要使用xlrd的xldate_as_tuple转换为Python日期 if cell.ctype xlrd.XL_CELL_DATE: date_tuple xlrd.xldate_as_tuple(cell.value, book.datemode) print(f“这是一个日期{date_tuple}“) # (2024, 5, 17, 0, 0, 0)4.3 处理特殊内容日期、合并单元格处理日期 Excel内部用浮点数存储日期xlrd可以帮我们正确转换。if cell.ctype xlrd.XL_CELL_DATE: # 方法1转换为元组 (年, 月, 日, 时, 分, 秒) date_tuple xlrd.xldate_as_tuple(cell.value, book.datemode) # 方法2直接转换为datetime对象需要datetime模块 from datetime import datetime date_value xlrd.xldate.xldate_as_datetime(cell.value, book.datemode) print(date_value.strftime(‘%Y-%m-%d‘))处理合并单元格 合并单元格在读取时需要特别注意因为只有左上角的单元格有值其他被合并的单元格读出来是空的。# 获取工作表内所有的合并单元格范围 merged_cells sheet.merged_cells # 返回一个列表如 [(0, 3, 0, 2)] 表示行0到2不含3列0到1不含2被合并 for (rlow, rhigh, clow, chigh) in merged_cells: print(f“合并区域行{rlow1}-{rhigh} 列{clow1}-{chigh}“) # 只有左上角(rlow, clow)有值 main_cell_value sheet.cell_value(rlow, clow) # 如果你想填充整个合并区域的值可以自己处理 for row in range(rlow, rhigh): for col in range(clow, chigh): # 实际上除了(rlow, clow)其他位置cell_value读出来是空字符串或0 # 这里只是演示逻辑 pass踩坑记录曾经处理过一个报表因为忽略了合并单元格导致用简单遍历row_values的方式丢失了大量数据。后来改用先获取合并信息再手动填充逻辑才正确解析。对于结构复杂的表格一定要先sheet.merged_cells查看合并情况。5. 综合实战案例销售数据清洗与报告自动化让我们用一个完整的案例串联xlrd和openpyxl模拟一个真实的办公自动化场景。场景市场部每天会从旧系统导出一份.xls格式的原始销售数据格式混乱你需要用Python自动完成以下工作用xlrd读取原始数据。清洗数据剔除无效行销售员为空将“销售额”列的单位从“万元”转换为“元”并计算每个人的“提成”假设为销售额的5%。用openpyxl生成一份格式美观的.xlsx日报包含汇总表格和一张展示销售额排名的柱状图。原始数据 (sales_raw.xls)可能长这样销售员区域销售额(万元)备注张三华北120华东95临时顶替李四华南150王五华中88实现代码import xlrd from openpyxl import Workbook from openpyxl.styles import Font, Alignment, Border, Side, PatternFill from openpyxl.chart import BarChart, Reference # 第一步使用xlrd读取并清洗原始.xls数据 def read_and_clean_data(file_path): 读取.xls文件清洗并返回结构化数据列表 book xlrd.open_workbook(file_path) sheet book.sheet_by_index(0) cleaned_data [] for row_idx in range(1, sheet.nrows): # 跳过标题行 salesperson sheet.cell_value(row_idx, 0).strip() region sheet.cell_value(row_idx, 1) # 处理销售额并转换单位 sales_raw sheet.cell_value(row_idx, 2) if sales_raw ‘‘: # 处理空值 continue sales_in_wan float(sales_raw) sales_in_yuan sales_in_wan * 10000 # 计算提成 commission sales_in_yuan * 0.05 # 只保留销售员不为空的数据 if salesperson: cleaned_data.append({ ‘salesperson‘: salesperson, ‘region‘: region, ‘sales‘: sales_in_yuan, ‘commission‘: commission }) return cleaned_data # 第二步使用openpyxl生成报告 def generate_report(data, output_path): 根据清洗后的数据生成.xlsx报告 wb Workbook() ws wb.active ws.title “销售日报“ # 1. 写入标题和表头 title_cell ws[‘A1‘] title_cell.value ‘每日销售业绩汇总‘ title_cell.font Font(name‘微软雅黑‘, size16, boldTrue) ws.merge_cells(‘A1:E1‘) title_cell.alignment Alignment(horizontal‘center‘) headers [‘销售员‘, ‘区域‘, ‘销售额(元)‘, ‘提成(元)‘, ‘备注‘] for col_idx, header in enumerate(headers, start1): cell ws.cell(row3, columncol_idx, valueheader) cell.font Font(boldTrue) cell.fill PatternFill(start_color‘CCCCCC‘, end_color‘CCCCCC‘, fill_type‘solid‘) cell.alignment Alignment(horizontal‘center‘) # 2. 写入清洗后的数据 start_row 4 for idx, record in enumerate(data): ws.cell(rowstart_rowidx, column1, valuerecord[‘salesperson‘]) ws.cell(rowstart_rowidx, column2, valuerecord[‘region‘]) ws.cell(rowstart_rowidx, column3, valuerecord[‘sales‘]) ws.cell(rowstart_rowidx, column4, valuerecord[‘commission‘]) # 设置数字格式 ws.cell(rowstart_rowidx, column3).number_format ‘#,##0‘ ws.cell(rowstart_rowidx, column4).number_format ‘#,##0.00‘ # 3. 添加汇总行例如总销售额 total_sales_row start_row len(data) ws.cell(rowtotal_sales_row, column2, value‘总计‘).font Font(boldTrue) ws.cell(rowtotal_sales_row, column3, valuef‘SUM(C{start_row}:C{total_sales_row-1})‘) ws.cell(rowtotal_sales_row, column3).number_format ‘#,##0‘ ws.cell(rowtotal_sales_row, column3).font Font(boldTrue, color‘FF0000‘) # 4. 调整列宽 for column in ws.columns: max_length 0 column_letter column[0].column_letter 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 # 5. 生成柱状图 chart BarChart() chart.title “销售员业绩排名“ chart.x_axis.title “销售员“ chart.y_axis.title “销售额元“ # 数据引用销售额数据 data_ref Reference(ws, min_col3, min_rowstart_row-1, max_rowtotal_sales_row-2) # 包含标题行和数据行 # 类别引用销售员姓名 cats_ref Reference(ws, min_col1, min_rowstart_row, max_rowtotal_sales_row-2) chart.add_data(data_ref, titles_from_dataTrue) chart.set_categories(cats_ref) # 将图表放在数据右侧 ws.add_chart(chart, f“G{start_row}“) # 6. 保存文件 wb.save(output_path) print(f“报告已生成{output_path}“) # 主程序执行 if __name__ ‘__main__‘: raw_file ‘sales_raw.xls‘ report_file ‘销售日报_20240517.xlsx‘ cleaned_data read_and_clean_data(raw_file) print(f“共清洗出{len(cleaned_data)}条有效数据。“) generate_report(cleaned_data, report_file)这个案例涵盖了从数据读取、清洗、计算到格式化输出和图表的完整流程是xlrd和openpyxl协同工作的典型范例。6. 避坑指南与性能优化实战在实际使用中你肯定会遇到各种问题。下面是我总结的一些常见“坑”和优化技巧。6.1 编码与日期格式的“暗雷”中文编码问题 使用xlrd读取老.xls文件时如果单元格包含中文且出现乱码可能是因为文件本身的编码问题。虽然xlrd1.x版本对此处理得较好但如果遇到可以尝试在打开时指定编码尽管open_workbook官方参数不支持。更常见的做法是确保源文件保存时使用正确的编码或者在读取后对字符串进行解码/编码处理。openpyxl对UTF-8支持良好一般无此问题。日期读取的陷阱xlrd读取日期时一定要使用book.datemode0代表1900日期系统1代表1904日期系统。用错datemode会导致转换出来的日期相差4年。一个简单的判断方法是看看转换后的年份是否合理。# 安全的日期转换函数 def safe_xldate_to_date(cell, workbook): if cell.ctype xlrd.XL_CELL_DATE: try: return xlrd.xldate.xldate_as_datetime(cell.value, workbook.datemode) except Exception as e: print(f“日期转换错误在单元格({cell.row}, {cell.col}): {e}“) return cell.value # 返回原始值 else: return cell.valueopenpyxl写入公式但不计算 这是特性不是bug。openpyxl写入的是公式字符串。当你在Excel中打开文件时公式会自动计算。如果你需要Python端也得到计算结果有两种思路用openpyxl的data_onlyTrue模式去打开一个已经被Excel计算并保存过的文件此时读取的是缓存的计算结果。对于简单公式如SUM直接用Python计算好结果写入而不是写入公式字符串。6.2 处理大文件的性能优化策略当Excel文件有几十万行时不当的操作会非常慢甚至内存溢出。使用openpyxl的只读模式 如果你只需要读取.xlsx大文件的数据而不修改它务必使用read_onlyTrue。这个模式下openpyxl不会将整个文件加载到内存而是按需流式读取。from openpyxl import load_workbook wb load_workbook(filename‘超大文件.xlsx‘, read_onlyTrue) ws wb.active for row in ws.iter_rows(values_onlyTrue): # values_onlyTrue只返回值不返回单元格对象更快 # 处理每一行数据 pass wb.close() # 只读模式记得关闭注意只读模式下你不能修改工作簿也不能使用ws[‘A1‘]这样的单元格访问方式必须用iter_rows迭代。使用openpyxl的只写模式 如果你需要生成一个非常大的.xlsx文件使用write_onlyTrue模式可以显著提升写入性能并降低内存占用。from openpyxl import Workbook from openpyxl.writer.excel import save_virtual_workbook wb Workbook(write_onlyTrue) # 创建只写工作簿 ws wb.create_sheet() # 只写模式下必须使用append方法添加整行数据 for data_row in huge_data_generator(): # 假设这是一个生成大量数据的生成器 ws.append(data_row) # data_row是一个列表或元组 wb.save(‘生成的超大文件.xlsx‘)只写模式同样不能回头修改已写入的单元格它适用于顺序生成大量数据的场景。优化xlrd的读取xlrd本身在读取.xls时效率很高。对于超大.xls文件避免在内存中一次性构建巨大的数据结构如一个包含所有行的列表。应该采用流式处理读一行处理一行或者分批处理。6.3 常见错误排查速查表错误现象可能原因解决方案ModuleNotFoundError: No module named ‘openpyxl‘未安装openpyxl库或在错误的Python环境中。在正确的虚拟环境中运行pip install openpyxl。KeyError: ‘工作表名‘使用wb[‘工作表名‘]时工作表名不存在或拼写错误大小写敏感。使用wb.sheetnames打印所有工作表名进行核对。BadZipFile: File is not a zip file用openpyxl打开了一个非.xlsx文件如.xls或损坏的文件。确认文件扩展名.xls文件需用xlrd读取。检查文件是否完整。AttributeError: ‘Sheet‘ object has no attribute ‘cell‘在xlrd中工作表对象是xlrd.sheet.Sheet其方法是cell_value不是cell。使用sheet.cell_value(rowx, colx)来读取值。获取单元格对象用sheet.cell(rowx, colx)。写入的公式在Excel中显示为字符串不计算。在openpyxl中公式字符串必须以等号开头。确保赋值时字符串以开头如ws[‘A1‘] ‘SUM(B1:B10)‘。用xlrd读取.xlsx文件报错或警告。安装了xlrd2.0.0它已不支持.xlsx。降级安装xlrd1.2.0或改用openpyxl读取.xlsx。生成的Excel文件用WPS打开样式错乱。WPS对某些Office高级样式的兼容性问题。尽量使用基础的样式字体、颜色、对齐避免过于复杂的边框和填充组合。生成后用主流Office软件测试。内存占用过高程序崩溃。使用默认模式加载了超大Excel文件。对于.xlsx使用read_onlyTrue或write_onlyTrue模式。对于.xls流式读取并处理。掌握这些排查技巧能让你在遇到问题时快速定位而不是漫无目的地搜索。7. 扩展思路与其他工具的协同Python处理Excel的生态不止openpyxl和xlrd。了解其他工具能在不同场景下选择最优解。pandas数据分析的首选如果你的核心是数据分析而不仅仅是读写单元格那么pandas是更强大的选择。它底层也使用openpyxl或xlrd但提供了极其方便的DataFrame数据结构。import pandas as pd # 一行代码读取Excel得到一个DataFrame df pd.read_excel(‘数据.xlsx‘, sheet_name‘Sheet1‘) # 进行复杂的数据筛选、分组、聚合、透视 df_filtered df[df[‘销售额‘] 10000] grouped df_filtered.groupby(‘区域‘)[‘销售额‘].sum() # 将处理结果写回Excel grouped.to_excel(‘分析结果.xlsx‘, sheet_name‘汇总‘)pandas的read_excel和to_excel函数功能非常强大能处理大多数读写需求。但在需要精细控制单元格样式、生成复杂图表时仍需回归openpyxl。xlwt / xlutils写入.xls的老牌组合如果需要写入.xls格式历史上常用xlwt写配合xlrd读和xlutils修改。但xlwt已停止维护且不支持.xlsx。对于新项目除非有强制性的.xls输出需求否则一律推荐使用openpyxl生成.xlsx。第三方云服务与API对于需要在线协作、或与Google Sheets等云端表格交互的场景可以考虑对应的API如Google Sheets API, Microsoft Graph API。Python有相应的客户端库可以实现更高级的自动化流程。选择工具的核心原则是用最简单的工具解决当前问题。如果只是简单读写openpyxl/xlrd足够如果涉及复杂数据处理先上pandas如果需要精美格式和图表再结合openpyxl进行后期美化。本文还有配套的精品资源点击获取