1. 项目概述从基础写入到实战进阶上次我们聊了用Python写Excel最基础的openpyxl和pandas算是把工具领进门了。但真到了干活的时候你会发现光会to_excel()是远远不够的。比如老板扔给你一个每天都要跑的脚本要求把爬下来的数据追加到现有的报表里还要保持原有的格式和公式或者财务同事要求生成的表格必须兼容老旧的.xls格式方便他们用古董软件打开。这些场景才是考验我们“内功”的时候。所以这篇“Python之Excel数据写入(2)”我们就深入一步不再满足于创建新文件而是聚焦于几个更贴近实际工作的核心痛点如何向现有Excel文件追加数据而不破坏原有内容如何高效处理大型数据集避免内存爆炸以及如何跨版本兼容.xls和.xlsx格式掌握这些你的数据处理脚本才能真正融入生产流水线成为一个可靠的工具而不是一个随时可能出错的玩具。我会用到openpyxl、pandas以及专门处理.xls的xlwt库通过具体的代码示例把每个操作背后的逻辑和踩过的坑都讲清楚。无论你是需要做日报自动化还是构建复杂的数据导出服务这里面的技巧都能直接用上。2. 核心场景与方案选型为什么是它们在动手写代码之前先搞清楚我们要解决什么问题以及为什么选择特定的工具。盲目选库后面可能会遇到性能瓶颈或功能缺失折腾半天还得推倒重来。2.1 场景一向现有文件追加数据这是最常见的需求。比如你有一个每日更新的销售总表sales_report.xlsx每天凌晨需要把前一天的销售明细追加到表格的最后一行。这里的关键是“追加”和“保持原样”。为什么不用pandas直接读再写如果你用pd.read_excel()读出来合并新数据再用df.to_excel()写回去原文件里的所有格式如单元格颜色、边框、列宽、公式、图表等都会被抹掉只剩下光秃秃的数据。这绝对是灾难。openpyxl的优势openpyxl允许我们以“加载现有工作簿”的模式打开文件直接定位到最后一个有效行之后然后像操作列表一样追加行。它可以最大程度地保留工作簿的原有状态虽然对复杂格式的完全无损支持也有局限但比pandas强得多。2.2 场景二处理超大型数据集当你需要写入几十万甚至上百万行数据时直接操作会非常慢甚至因为内存不足而崩溃。pandas的瓶颈pandas的DataFrame需要将所有数据载入内存构建完整的二维数据结构对于海量数据来说内存消耗是巨大的。流式写入与优化我们需要采用更“节俭”的方式。openpyxl的只写模式write_onlyTrue可以边生成数据边写入文件而不是在内存中构建整个工作表对象非常适合数据导出。pandas也可以通过分块chunk处理来降低内存峰值。2.3 场景三兼容古老的.xls格式尽管.xlsx已经是主流但很多企业遗留系统、特定行业软件如某些财务、工业控制软件仍然只认.xls格式。这是硬性兼容需求。openpyxl和pandas的局限它们主要针对.xlsx。pandas的ExcelWriter虽然可以指定enginexlwt来写.xls但功能受限且xlwt库已停止维护不支持.xlsx的新特性如超过256列、65536行。专用库的必要性对于.xls我们需要请出“老将”xlwt写和xlrd读。虽然古老但在兼容性上是唯一可靠的选择。注意方案选型没有银弹。一个项目里你可能需要根据不同的输出要求混合使用多个库。比如主流程用pandas做数据清洗和转换最终根据文件名后缀判断用openpyxl写.xlsx用xlwt写.xls。3. 实战演练向现有Excel文件追加数据理论说完我们直接上代码。假设我们有一个现有的daily_sales.xlsx文件里面已经有了一些历史数据表头为[“日期” “产品” “销量” “销售额”]。3.1 使用openpyxl进行精确追加openpyxl提供了对工作表单元格级别的精细控制非常适合这种“外科手术”式的操作。from openpyxl import load_workbook from openpyxl.utils import get_column_letter import datetime def append_data_with_openpyxl(file_path, new_data): 向现有Excel文件追加数据。 :param file_path: 现有Excel文件路径 :param new_data: 要追加的数据列表每个元素是一个代表一行的列表或元组 # 1. 加载现有工作簿保持原有属性 # keep_vba参数通常用于包含宏的文件我们这里不需要。 wb load_workbook(filenamefile_path) # 假设数据在第一个工作表 ws wb.active # 2. 找到最后一行的行号。注意openpyxl的max_row返回的是有内容的最大行。 # 但如果有空行这个方法可能不准。更稳健的方法是找到第一个所有单元格都为None的行。 next_row ws.max_row 1 # 3. 遍历新数据写入单元格 for row_idx, row_data in enumerate(new_data, startnext_row): for col_idx, cell_value in enumerate(row_data, start1): # 可以在这里添加简单的数据类型处理比如日期 if isinstance(cell_value, datetime.date): cell_value cell_value.strftime(‘%Y-%m-%d’) ws.cell(rowrow_idx, columncol_idx, valuecell_value) # 4. 保存文件。注意这会覆盖原文件。 wb.save(file_path) print(f“数据已追加到 {file_path} 从第 {next_row} 行开始。”) # 模拟新数据 today datetime.date.today() new_sales [ [today, “产品A”, 150, 7500.00], [today, “产品B”, 89, 4450.00], [today, “产品C”, 200, 12000.00], ] # 调用函数 append_data_with_openpyxl(‘daily_sales.xlsx’ new_sales)关键点解析与避坑指南load_workbookvsWorkbook这里是load_workbook用于加载已存在的文件。如果是创建新文件才用from openpyxl import Workbook。确定插入位置ws.max_row是最简单的方法但它只返回工作表对象中已分配单元格的最大行号。如果表格中间有被清空内容但格式还在的行它可能不准确。对于要求极高的场景可以写一个函数从最后一行向上扫描直到找到有内容的行。单元格坐标ws.cell(row行 column列 value值)。列参数可以用数字1代表A也可以用get_column_letter(列数字)得到的字母如‘A’。保存操作wb.save(file_path)会直接覆盖原文件。极其重要的安全操作在生产环境中强烈建议先保存为临时文件验证无误后再替换原文件或者使用版本备份。例如temp_path file_path.replace(‘.xlsx’ ‘_temp.xlsx’) wb.save(temp_path) # ... 这里可以添加一些验证逻辑 ... import shutil shutil.move(temp_path file_path) # 移动文件进行覆盖性能考虑如果一次性追加的数据量很大比如上万行在循环内频繁调用ws.cell()会有性能开销。可以考虑先在一个列表里构建好要写入的单元格对象或者对于超大数据量考虑下一节讲的只写模式。3.2 使用pandas追加谨慎使用如前所述pandas会丢失格式但在某些“仅数据、无格式”且需要复杂数据合并的场景下它可能更简单。import pandas as pd def append_data_with_pandas(existing_file_path, new_data_df): 使用pandas追加数据。警告会丢失原文件所有格式 :param existing_file_path: 现有文件路径 :param new_data_df: 要追加的DataFrame其列结构应与原文件一致 # 1. 读取现有文件数据 try: existing_df pd.read_excel(existing_file_path) except FileNotFoundError: # 如果文件不存在则创建一个空的DataFrame existing_df pd.DataFrame() # 2. 合并数据 # 使用concatignore_indexTrue重置索引 combined_df pd.concat([existing_df, new_data_df], ignore_indexTrue) # 3. 写回文件覆盖 # 指定indexFalse避免将DataFrame索引写入Excel combined_df.to_excel(existing_file_path, indexFalse) print(f“数据已通过pandas追加/覆盖至 {existing_file_path}。”) # 示例假设new_data是一个字典列表很容易转成DataFrame new_data_list [ {“日期”: “2023-10-27” “产品”: “产品D” “销量”: 300 “销售额”: 15000}, {“日期”: “2023-10-27” “产品”: “产品E” “销量”: 95 “销售额”: 4750}, ] new_df pd.DataFrame(new_data_list) append_data_with_pandas(‘daily_sales.xlsx’ new_df)实操心得用pandas追加本质上是一次“读取-合并-覆盖”的操作。仅适用于原文件本身就是由该脚本生成的、纯数据、无任何手动调整格式的情况。如果文件曾被人工编辑过调整过列宽、加了颜色、设置了公式请务必使用openpyxl方案。在决定方案前先用测试文件验证一下输出结果是否符合预期这是一个好习惯。4. 高效写入应对海量数据的策略当数据量达到10万行以上时我们需要不同的策略。核心思想是减少内存中的对象数量采用流式或分块处理。4.1 使用openpyxl的只写模式Write-Only Mode这是openpyxl为大数据量写入提供的优化模式。它不允许你读取或修改现有单元格只能一路向前写因此内存占用极低。from openpyxl import Workbook from openpyxl.writer.excel import save_virtual_workbook def write_large_data_openpyxl(file_path, data_generator): 使用只写模式写入大量数据。 :param file_path: 输出文件路径 :param data_generator: 一个生成器每次yield一行数据列表 # 1. 创建只写模式的工作簿和工作表 wb Workbook(write_onlyTrue) ws wb.create_sheet(title“海量数据”) # 2. 写入表头如果需要 header [“ID” “Name” “Value”] ws.append(header) # 3. 通过生成器逐行追加数据 # 假设data_generator是一个能生成数十万行数据的生成器 row_count 0 for row in data_generator: ws.append(row) # append方法在只写模式下被优化过 row_count 1 # 可选每写入一定行数打印进度 if row_count % 10000 0: print(f“已写入 {row_count} 行...”) # 4. 保存文件 wb.save(file_path) print(f“写入完成共 {row_count 1} 行含表头。文件已保存至 {file_path}”) # 模拟一个大数据生成器 def mock_large_data_gen(total_rows100000): for i in range(1, total_rows 1): # yield 返回一行数据 yield [i, f“Item_{i}” i * 1.5] # 使用 write_large_data_openpyxl(‘large_dataset.xlsx’ mock_large_data_gen(50000))为什么这样更快更省内存在只写模式下ws.append()不会在内存中创建完整的单元格对象树而是将数据直接序列化到磁盘的临时结构中。这意味着你可以处理远超常规内存限制的数据集。4.2 使用pandas的分块处理Chunking如果你的数据源本身很大比如一个巨大的CSV或数据库查询结果无法一次性读入内存可以结合pandas的分块读取和写入功能。import pandas as pd def write_large_csv_to_excel_in_chunks(csv_file_path, excel_file_path, chunk_size10000): 将大型CSV文件分块读取并写入Excel。 注意此方法使用pandas的ExcelWriter并指定引擎如openpyxl。 # 1. 创建一个Excel writer对象模式为‘a’追加或‘w’写入 # 使用engineopenpyxlmodea可以追加到现有文件但通常大数据我们写新文件。 with pd.ExcelWriter(excel_file_path, engine‘openpyxl’) as writer: # 2. 分块读取CSV chunk_reader pd.read_csv(csv_file_path, chunksizechunk_size) for i, chunk_df in enumerate(chunk_reader): # 3. 将每个数据块写入Excel # startrow参数指定从哪一行开始写。第一块写表头后面的块不写表头。 startrow 0 if i 0 else writer.sheets[‘Sheet1’].max_row chunk_df.to_excel( writer, sheet_name‘Sheet1’ indexFalse, header(i 0) # 只有第一个块写入表头 startrowstartrow ) print(f“已处理第 {i1} 个数据块 行数 {len(chunk_df)}”) print(f“CSV文件 {csv_file_path} 已成功分块写入 {excel_file_path}。”) # 注意这个方法实际上在内存中会同时存在一个数据块和一个逐渐增长的Excel写入对象。 # 对于极大的数据openpyxl引擎可能仍会遇到性能瓶颈此时可以考虑先输出为多个CSV再用其他工具合并或者使用专门的ETL工具。注意事项分块写入Excel时pandas的ExcelWriter在背后仍然依赖于openpyxl或xlsxwriter引擎。当写入的行数非常多时例如超过50万行即使分块最终生成的文件打开和操作也可能非常缓慢因为.xlsx文件格式本身在处理超大数据时就有局限。对于真正海量的数据千万行级别更好的选择是输出为多个Excel文件、Parquet文件、或直接写入数据库。5. 兼容性写入处理.xls旧格式当需求明确要求输出.xls文件时xlwt是经典选择。它API简单但功能也相对基础。5.1 使用xlwt创建.xls文件import xlwt from datetime import datetime def write_xls_with_xlwt(file_path, data): 使用xlwt库写入.xls格式文件。 # 1. 创建工作簿和工作表 workbook xlwt.Workbook(encoding‘utf-8’) worksheet workbook.add_sheet(‘销售数据’) # 2. 定义样式可选但.xls格式支持有限 style_header xlwt.easyxf(‘font: bold on; align: horiz center’) style_date xlwt.easyxf(num_format_str‘YYYY-MM-DD’) style_currency xlwt.easyxf(num_format_str‘###0.00’) # 3. 写入表头 headers [“日期” “产品” “销量” “销售额”] for col_idx, header in enumerate(headers): worksheet.write(0, col_idx, header, style_header) # 设置列宽一个单位约等于1/256个字符宽度 worksheet.col(col_idx).width 256 * 15 # 4. 写入数据行 for row_idx, row_data in enumerate(data, start1): # start1表示从第1行开始0是表头 date_val, product, quantity, revenue row_data # 写入日期应用日期样式 worksheet.write(row_idx, 0, date_val, style_date) # 写入文本 worksheet.write(row_idx, 1, product) # 写入整数 worksheet.write(row_idx, 2, quantity) # 写入金额应用货币样式 worksheet.write(row_idx, 3, revenue, style_currency) # 5. 保存文件 workbook.save(file_path) print(f“.xls 文件已保存 {file_path}”) # 准备数据 xls_data [ (datetime(2023 10 26) “产品A” 100, 5000.0), (datetime(2023 10 26) “产品B” 75, 3750.0), (datetime(2023 10 27) “产品A” 120, 6000.0), ] write_xls_with_xlwt(‘legacy_sales.xls’ xls_data)5.2 使用pandas配合xlwt引擎如果你更习惯pandas的接口也可以用它来写.xls但需要指定引擎。import pandas as pd df pd.DataFrame({ “日期”: [“2023-10-26” “2023-10-26” “2023-10-27”] “产品”: [“产品A” “产品B” “产品A”] “销量”: [100, 75, 120] “销售额”: [5000.0, 3750.0, 6000.0] }) # 关键指定enginexlwt with pd.ExcelWriter(‘output_via_pandas.xls’ engine‘xlwt’) as writer: df.to_excel(writer, indexFalse, sheet_name‘Sheet1’)重要限制提醒xlwt不支持.xlsx格式。.xls格式最大支持65536行2^16和256列2^8即IV列。如果你的数据超出这个范围xlwt会报错。务必在写入前检查数据维度xlwt的样式设置相对openpyxl更简单但功能也少。复杂格式如条件格式、迷你图等无法实现。xlwt库已停止新功能开发仅做维护。对于新的项目如果可能应尽量推动使用.xlsx格式。6. 常见问题、排查技巧与实战心得在实际操作中你会遇到各种各样的问题。下面是我总结的一些典型坑点和解决方法。6.1 文件被占用或权限错误问题描述在运行脚本时报错PermissionError: [Errno 13] Permission denied或者openpyxl提示文件已被打开。原因你要写入的文件正被其他程序如Excel、WPS、甚至你之前的Python进程未正确关闭文件句柄占用。解决方案确保手动关闭在Excel中关闭文件。代码层面确保关闭使用with语句上下文管理器来操作文件这是最推荐的方式。对于openpyxlload_workbook和save本身不直接支持with但你可以确保在save后不再操作对象。# pandas的ExcelWriter完美支持with with pd.ExcelWriter(‘file.xlsx’) as writer: df.to_excel(writer) # 退出with块后文件会自动关闭保存异常处理与重试在自动化脚本中可以加入重试逻辑和更清晰的错误提示。import time def safe_save(workbook, path, retries3, delay2): for i in range(retries): try: workbook.save(path) print(“保存成功。”) return True except PermissionError as e: if i retries - 1: print(f“文件被占用第{i1}次重试... ({e})”) time.sleep(delay) else: print(f“保存失败文件可能被其他程序锁定。请手动关闭。 ({e})”) raise e return False6.2 写入后数字变成文本或日期格式错乱问题描述在Excel中打开生成的文件发现数字列左上角有绿色三角提示为文本或者日期显示为一串数字。原因Python中的数据类型如整数、浮点数、datetime对象在写入Excel时如果没有被正确识别为对应的单元格格式可能会被存储为通用文本。解决方案对于openpyxl确保写入的是正确的Python类型。openpyxl会自动将datetime.datetime或datetime.date对象识别为日期将int、float识别为数字。如果你从字符串转换而来务必先转成对应类型。# 错误写入的是字符串形式的数字 ws[‘A1’] “123.45” # Excel会将其视为文本 # 正确写入浮点数 ws[‘A1’] 123.45 # 或者如果你从文本读入先转换 value_from_str float(“123.45”) ws[‘A1’] value_from_str对于xlwt必须显式地设置单元格样式num_format_str如前面示例中的style_date和style_currency。对于pandaspandas通常能很好地处理内置类型。确保你的DataFrame的dtype是正确的例如日期列是datetime64类型。6.3 性能瓶颈与内存优化问题描述写入几万行数据就非常慢或者内存占用飙升。排查与优化诊断工具使用Python的memory_profiler或tracemalloc来监控内存使用找出是数据准备阶段还是写入阶段耗内存。应用前述策略对于openpyxl切换到只写模式Workbook(write_onlyTrue)。对于pandas使用分块处理chunksize。通用原则避免在内存中同时持有原始数据、中间处理数据和最终的Excel对象。使用生成器yield逐行处理数据。关闭不必要的功能在openpyxl的只读或只写模式下可以禁用不需要的属性计算来提升速度。考虑替代格式如果最终用户同意对于纯数据交换.csv或.parquet格式的写入速度和压缩比远高于Excel。6.4 格式丢失与保留问题描述用pandas或openpyxl的某些方式写入后原有的单元格颜色、公式、列宽等都没了。根本原因Excel文件不仅仅是数据还是包含样式、公式、宏等信息的压缩包.xlsx本质是ZIP。简单的数据写入操作不会携带这些信息。最佳实践明确需求如果文件是给人看的报告格式很重要优先使用openpyxl加载现有模板文件然后在指定位置填充数据。使用模板创建一个带有所有格式、公式、图表的工作簿作为“模板”。脚本只打开这个模板向特定单元格或区域写入数据然后另存为新文件。这样能完美保留格式。编程设置格式如果格式是固定的可以用openpyxl的API在写入数据后重新应用样式字体、颜色、边框、对齐方式。但这通常比用模板更繁琐。6.5 编码与中文乱码问题描述写入的中文在Excel中显示为乱码。原因与解决.xlsx格式通常使用UTF-8编码openpyxl和pandas默认支持良好很少出问题。如果遇到检查Python源文件本身的编码确保是UTF-8以及数据源的编码。.xls格式xlwtxlwt默认使用ASCII编码。必须在创建Workbook时指定encoding‘utf-8’或encoding‘gbk’取决于你的系统环境如xlwt.Workbook(encoding‘utf-8’)。文件路径包含中文确保文件路径字符串在Python中是正确的Unicode字符串。在Python 3中字符串默认是Unicode通常没问题。6.6 版本兼容性与依赖管理问题描述脚本在本地运行正常放到服务器或同事电脑上就报错提示找不到模块或版本不兼容。解决方案使用requirements.txt为你的项目创建依赖清单。# requirements.txt openpyxl3.1.2 pandas2.1.1 xlwt1.3.0注意pandas的引擎pandas的to_excel方法默认引擎可能因系统安装的库而变化。为了稳定建议显式指定# 写.xlsx df.to_excel(‘output.xlsx’ engine‘openpyxl’) # 写.xls df.to_excel(‘output.xls’ engine‘xlwt’)测试不同环境在部署前在干净的环境如虚拟环境中测试安装和运行。最后我个人的一个强烈建议是为你的Excel写入函数编写单元测试。测试用例可以覆盖写入少量数据是否正确、追加数据是否到位、写入大量数据是否抛出异常、生成的文件能否被Excel正常打开等。自动化测试能极大减少手动验证的工作量尤其是在脚本被频繁修改或用于关键任务时。你可以使用pytest框架结合openpyxl或pandas读回数据来进行断言比较。这看似多花了一点时间但长期来看是保证代码质量和稳定性的最有效投资。
Python Excel数据写入实战：追加数据、处理海量与兼容旧格式
1. 项目概述从基础写入到实战进阶上次我们聊了用Python写Excel最基础的openpyxl和pandas算是把工具领进门了。但真到了干活的时候你会发现光会to_excel()是远远不够的。比如老板扔给你一个每天都要跑的脚本要求把爬下来的数据追加到现有的报表里还要保持原有的格式和公式或者财务同事要求生成的表格必须兼容老旧的.xls格式方便他们用古董软件打开。这些场景才是考验我们“内功”的时候。所以这篇“Python之Excel数据写入(2)”我们就深入一步不再满足于创建新文件而是聚焦于几个更贴近实际工作的核心痛点如何向现有Excel文件追加数据而不破坏原有内容如何高效处理大型数据集避免内存爆炸以及如何跨版本兼容.xls和.xlsx格式掌握这些你的数据处理脚本才能真正融入生产流水线成为一个可靠的工具而不是一个随时可能出错的玩具。我会用到openpyxl、pandas以及专门处理.xls的xlwt库通过具体的代码示例把每个操作背后的逻辑和踩过的坑都讲清楚。无论你是需要做日报自动化还是构建复杂的数据导出服务这里面的技巧都能直接用上。2. 核心场景与方案选型为什么是它们在动手写代码之前先搞清楚我们要解决什么问题以及为什么选择特定的工具。盲目选库后面可能会遇到性能瓶颈或功能缺失折腾半天还得推倒重来。2.1 场景一向现有文件追加数据这是最常见的需求。比如你有一个每日更新的销售总表sales_report.xlsx每天凌晨需要把前一天的销售明细追加到表格的最后一行。这里的关键是“追加”和“保持原样”。为什么不用pandas直接读再写如果你用pd.read_excel()读出来合并新数据再用df.to_excel()写回去原文件里的所有格式如单元格颜色、边框、列宽、公式、图表等都会被抹掉只剩下光秃秃的数据。这绝对是灾难。openpyxl的优势openpyxl允许我们以“加载现有工作簿”的模式打开文件直接定位到最后一个有效行之后然后像操作列表一样追加行。它可以最大程度地保留工作簿的原有状态虽然对复杂格式的完全无损支持也有局限但比pandas强得多。2.2 场景二处理超大型数据集当你需要写入几十万甚至上百万行数据时直接操作会非常慢甚至因为内存不足而崩溃。pandas的瓶颈pandas的DataFrame需要将所有数据载入内存构建完整的二维数据结构对于海量数据来说内存消耗是巨大的。流式写入与优化我们需要采用更“节俭”的方式。openpyxl的只写模式write_onlyTrue可以边生成数据边写入文件而不是在内存中构建整个工作表对象非常适合数据导出。pandas也可以通过分块chunk处理来降低内存峰值。2.3 场景三兼容古老的.xls格式尽管.xlsx已经是主流但很多企业遗留系统、特定行业软件如某些财务、工业控制软件仍然只认.xls格式。这是硬性兼容需求。openpyxl和pandas的局限它们主要针对.xlsx。pandas的ExcelWriter虽然可以指定enginexlwt来写.xls但功能受限且xlwt库已停止维护不支持.xlsx的新特性如超过256列、65536行。专用库的必要性对于.xls我们需要请出“老将”xlwt写和xlrd读。虽然古老但在兼容性上是唯一可靠的选择。注意方案选型没有银弹。一个项目里你可能需要根据不同的输出要求混合使用多个库。比如主流程用pandas做数据清洗和转换最终根据文件名后缀判断用openpyxl写.xlsx用xlwt写.xls。3. 实战演练向现有Excel文件追加数据理论说完我们直接上代码。假设我们有一个现有的daily_sales.xlsx文件里面已经有了一些历史数据表头为[“日期” “产品” “销量” “销售额”]。3.1 使用openpyxl进行精确追加openpyxl提供了对工作表单元格级别的精细控制非常适合这种“外科手术”式的操作。from openpyxl import load_workbook from openpyxl.utils import get_column_letter import datetime def append_data_with_openpyxl(file_path, new_data): 向现有Excel文件追加数据。 :param file_path: 现有Excel文件路径 :param new_data: 要追加的数据列表每个元素是一个代表一行的列表或元组 # 1. 加载现有工作簿保持原有属性 # keep_vba参数通常用于包含宏的文件我们这里不需要。 wb load_workbook(filenamefile_path) # 假设数据在第一个工作表 ws wb.active # 2. 找到最后一行的行号。注意openpyxl的max_row返回的是有内容的最大行。 # 但如果有空行这个方法可能不准。更稳健的方法是找到第一个所有单元格都为None的行。 next_row ws.max_row 1 # 3. 遍历新数据写入单元格 for row_idx, row_data in enumerate(new_data, startnext_row): for col_idx, cell_value in enumerate(row_data, start1): # 可以在这里添加简单的数据类型处理比如日期 if isinstance(cell_value, datetime.date): cell_value cell_value.strftime(‘%Y-%m-%d’) ws.cell(rowrow_idx, columncol_idx, valuecell_value) # 4. 保存文件。注意这会覆盖原文件。 wb.save(file_path) print(f“数据已追加到 {file_path} 从第 {next_row} 行开始。”) # 模拟新数据 today datetime.date.today() new_sales [ [today, “产品A”, 150, 7500.00], [today, “产品B”, 89, 4450.00], [today, “产品C”, 200, 12000.00], ] # 调用函数 append_data_with_openpyxl(‘daily_sales.xlsx’ new_sales)关键点解析与避坑指南load_workbookvsWorkbook这里是load_workbook用于加载已存在的文件。如果是创建新文件才用from openpyxl import Workbook。确定插入位置ws.max_row是最简单的方法但它只返回工作表对象中已分配单元格的最大行号。如果表格中间有被清空内容但格式还在的行它可能不准确。对于要求极高的场景可以写一个函数从最后一行向上扫描直到找到有内容的行。单元格坐标ws.cell(row行 column列 value值)。列参数可以用数字1代表A也可以用get_column_letter(列数字)得到的字母如‘A’。保存操作wb.save(file_path)会直接覆盖原文件。极其重要的安全操作在生产环境中强烈建议先保存为临时文件验证无误后再替换原文件或者使用版本备份。例如temp_path file_path.replace(‘.xlsx’ ‘_temp.xlsx’) wb.save(temp_path) # ... 这里可以添加一些验证逻辑 ... import shutil shutil.move(temp_path file_path) # 移动文件进行覆盖性能考虑如果一次性追加的数据量很大比如上万行在循环内频繁调用ws.cell()会有性能开销。可以考虑先在一个列表里构建好要写入的单元格对象或者对于超大数据量考虑下一节讲的只写模式。3.2 使用pandas追加谨慎使用如前所述pandas会丢失格式但在某些“仅数据、无格式”且需要复杂数据合并的场景下它可能更简单。import pandas as pd def append_data_with_pandas(existing_file_path, new_data_df): 使用pandas追加数据。警告会丢失原文件所有格式 :param existing_file_path: 现有文件路径 :param new_data_df: 要追加的DataFrame其列结构应与原文件一致 # 1. 读取现有文件数据 try: existing_df pd.read_excel(existing_file_path) except FileNotFoundError: # 如果文件不存在则创建一个空的DataFrame existing_df pd.DataFrame() # 2. 合并数据 # 使用concatignore_indexTrue重置索引 combined_df pd.concat([existing_df, new_data_df], ignore_indexTrue) # 3. 写回文件覆盖 # 指定indexFalse避免将DataFrame索引写入Excel combined_df.to_excel(existing_file_path, indexFalse) print(f“数据已通过pandas追加/覆盖至 {existing_file_path}。”) # 示例假设new_data是一个字典列表很容易转成DataFrame new_data_list [ {“日期”: “2023-10-27” “产品”: “产品D” “销量”: 300 “销售额”: 15000}, {“日期”: “2023-10-27” “产品”: “产品E” “销量”: 95 “销售额”: 4750}, ] new_df pd.DataFrame(new_data_list) append_data_with_pandas(‘daily_sales.xlsx’ new_df)实操心得用pandas追加本质上是一次“读取-合并-覆盖”的操作。仅适用于原文件本身就是由该脚本生成的、纯数据、无任何手动调整格式的情况。如果文件曾被人工编辑过调整过列宽、加了颜色、设置了公式请务必使用openpyxl方案。在决定方案前先用测试文件验证一下输出结果是否符合预期这是一个好习惯。4. 高效写入应对海量数据的策略当数据量达到10万行以上时我们需要不同的策略。核心思想是减少内存中的对象数量采用流式或分块处理。4.1 使用openpyxl的只写模式Write-Only Mode这是openpyxl为大数据量写入提供的优化模式。它不允许你读取或修改现有单元格只能一路向前写因此内存占用极低。from openpyxl import Workbook from openpyxl.writer.excel import save_virtual_workbook def write_large_data_openpyxl(file_path, data_generator): 使用只写模式写入大量数据。 :param file_path: 输出文件路径 :param data_generator: 一个生成器每次yield一行数据列表 # 1. 创建只写模式的工作簿和工作表 wb Workbook(write_onlyTrue) ws wb.create_sheet(title“海量数据”) # 2. 写入表头如果需要 header [“ID” “Name” “Value”] ws.append(header) # 3. 通过生成器逐行追加数据 # 假设data_generator是一个能生成数十万行数据的生成器 row_count 0 for row in data_generator: ws.append(row) # append方法在只写模式下被优化过 row_count 1 # 可选每写入一定行数打印进度 if row_count % 10000 0: print(f“已写入 {row_count} 行...”) # 4. 保存文件 wb.save(file_path) print(f“写入完成共 {row_count 1} 行含表头。文件已保存至 {file_path}”) # 模拟一个大数据生成器 def mock_large_data_gen(total_rows100000): for i in range(1, total_rows 1): # yield 返回一行数据 yield [i, f“Item_{i}” i * 1.5] # 使用 write_large_data_openpyxl(‘large_dataset.xlsx’ mock_large_data_gen(50000))为什么这样更快更省内存在只写模式下ws.append()不会在内存中创建完整的单元格对象树而是将数据直接序列化到磁盘的临时结构中。这意味着你可以处理远超常规内存限制的数据集。4.2 使用pandas的分块处理Chunking如果你的数据源本身很大比如一个巨大的CSV或数据库查询结果无法一次性读入内存可以结合pandas的分块读取和写入功能。import pandas as pd def write_large_csv_to_excel_in_chunks(csv_file_path, excel_file_path, chunk_size10000): 将大型CSV文件分块读取并写入Excel。 注意此方法使用pandas的ExcelWriter并指定引擎如openpyxl。 # 1. 创建一个Excel writer对象模式为‘a’追加或‘w’写入 # 使用engineopenpyxlmodea可以追加到现有文件但通常大数据我们写新文件。 with pd.ExcelWriter(excel_file_path, engine‘openpyxl’) as writer: # 2. 分块读取CSV chunk_reader pd.read_csv(csv_file_path, chunksizechunk_size) for i, chunk_df in enumerate(chunk_reader): # 3. 将每个数据块写入Excel # startrow参数指定从哪一行开始写。第一块写表头后面的块不写表头。 startrow 0 if i 0 else writer.sheets[‘Sheet1’].max_row chunk_df.to_excel( writer, sheet_name‘Sheet1’ indexFalse, header(i 0) # 只有第一个块写入表头 startrowstartrow ) print(f“已处理第 {i1} 个数据块 行数 {len(chunk_df)}”) print(f“CSV文件 {csv_file_path} 已成功分块写入 {excel_file_path}。”) # 注意这个方法实际上在内存中会同时存在一个数据块和一个逐渐增长的Excel写入对象。 # 对于极大的数据openpyxl引擎可能仍会遇到性能瓶颈此时可以考虑先输出为多个CSV再用其他工具合并或者使用专门的ETL工具。注意事项分块写入Excel时pandas的ExcelWriter在背后仍然依赖于openpyxl或xlsxwriter引擎。当写入的行数非常多时例如超过50万行即使分块最终生成的文件打开和操作也可能非常缓慢因为.xlsx文件格式本身在处理超大数据时就有局限。对于真正海量的数据千万行级别更好的选择是输出为多个Excel文件、Parquet文件、或直接写入数据库。5. 兼容性写入处理.xls旧格式当需求明确要求输出.xls文件时xlwt是经典选择。它API简单但功能也相对基础。5.1 使用xlwt创建.xls文件import xlwt from datetime import datetime def write_xls_with_xlwt(file_path, data): 使用xlwt库写入.xls格式文件。 # 1. 创建工作簿和工作表 workbook xlwt.Workbook(encoding‘utf-8’) worksheet workbook.add_sheet(‘销售数据’) # 2. 定义样式可选但.xls格式支持有限 style_header xlwt.easyxf(‘font: bold on; align: horiz center’) style_date xlwt.easyxf(num_format_str‘YYYY-MM-DD’) style_currency xlwt.easyxf(num_format_str‘###0.00’) # 3. 写入表头 headers [“日期” “产品” “销量” “销售额”] for col_idx, header in enumerate(headers): worksheet.write(0, col_idx, header, style_header) # 设置列宽一个单位约等于1/256个字符宽度 worksheet.col(col_idx).width 256 * 15 # 4. 写入数据行 for row_idx, row_data in enumerate(data, start1): # start1表示从第1行开始0是表头 date_val, product, quantity, revenue row_data # 写入日期应用日期样式 worksheet.write(row_idx, 0, date_val, style_date) # 写入文本 worksheet.write(row_idx, 1, product) # 写入整数 worksheet.write(row_idx, 2, quantity) # 写入金额应用货币样式 worksheet.write(row_idx, 3, revenue, style_currency) # 5. 保存文件 workbook.save(file_path) print(f“.xls 文件已保存 {file_path}”) # 准备数据 xls_data [ (datetime(2023 10 26) “产品A” 100, 5000.0), (datetime(2023 10 26) “产品B” 75, 3750.0), (datetime(2023 10 27) “产品A” 120, 6000.0), ] write_xls_with_xlwt(‘legacy_sales.xls’ xls_data)5.2 使用pandas配合xlwt引擎如果你更习惯pandas的接口也可以用它来写.xls但需要指定引擎。import pandas as pd df pd.DataFrame({ “日期”: [“2023-10-26” “2023-10-26” “2023-10-27”] “产品”: [“产品A” “产品B” “产品A”] “销量”: [100, 75, 120] “销售额”: [5000.0, 3750.0, 6000.0] }) # 关键指定enginexlwt with pd.ExcelWriter(‘output_via_pandas.xls’ engine‘xlwt’) as writer: df.to_excel(writer, indexFalse, sheet_name‘Sheet1’)重要限制提醒xlwt不支持.xlsx格式。.xls格式最大支持65536行2^16和256列2^8即IV列。如果你的数据超出这个范围xlwt会报错。务必在写入前检查数据维度xlwt的样式设置相对openpyxl更简单但功能也少。复杂格式如条件格式、迷你图等无法实现。xlwt库已停止新功能开发仅做维护。对于新的项目如果可能应尽量推动使用.xlsx格式。6. 常见问题、排查技巧与实战心得在实际操作中你会遇到各种各样的问题。下面是我总结的一些典型坑点和解决方法。6.1 文件被占用或权限错误问题描述在运行脚本时报错PermissionError: [Errno 13] Permission denied或者openpyxl提示文件已被打开。原因你要写入的文件正被其他程序如Excel、WPS、甚至你之前的Python进程未正确关闭文件句柄占用。解决方案确保手动关闭在Excel中关闭文件。代码层面确保关闭使用with语句上下文管理器来操作文件这是最推荐的方式。对于openpyxlload_workbook和save本身不直接支持with但你可以确保在save后不再操作对象。# pandas的ExcelWriter完美支持with with pd.ExcelWriter(‘file.xlsx’) as writer: df.to_excel(writer) # 退出with块后文件会自动关闭保存异常处理与重试在自动化脚本中可以加入重试逻辑和更清晰的错误提示。import time def safe_save(workbook, path, retries3, delay2): for i in range(retries): try: workbook.save(path) print(“保存成功。”) return True except PermissionError as e: if i retries - 1: print(f“文件被占用第{i1}次重试... ({e})”) time.sleep(delay) else: print(f“保存失败文件可能被其他程序锁定。请手动关闭。 ({e})”) raise e return False6.2 写入后数字变成文本或日期格式错乱问题描述在Excel中打开生成的文件发现数字列左上角有绿色三角提示为文本或者日期显示为一串数字。原因Python中的数据类型如整数、浮点数、datetime对象在写入Excel时如果没有被正确识别为对应的单元格格式可能会被存储为通用文本。解决方案对于openpyxl确保写入的是正确的Python类型。openpyxl会自动将datetime.datetime或datetime.date对象识别为日期将int、float识别为数字。如果你从字符串转换而来务必先转成对应类型。# 错误写入的是字符串形式的数字 ws[‘A1’] “123.45” # Excel会将其视为文本 # 正确写入浮点数 ws[‘A1’] 123.45 # 或者如果你从文本读入先转换 value_from_str float(“123.45”) ws[‘A1’] value_from_str对于xlwt必须显式地设置单元格样式num_format_str如前面示例中的style_date和style_currency。对于pandaspandas通常能很好地处理内置类型。确保你的DataFrame的dtype是正确的例如日期列是datetime64类型。6.3 性能瓶颈与内存优化问题描述写入几万行数据就非常慢或者内存占用飙升。排查与优化诊断工具使用Python的memory_profiler或tracemalloc来监控内存使用找出是数据准备阶段还是写入阶段耗内存。应用前述策略对于openpyxl切换到只写模式Workbook(write_onlyTrue)。对于pandas使用分块处理chunksize。通用原则避免在内存中同时持有原始数据、中间处理数据和最终的Excel对象。使用生成器yield逐行处理数据。关闭不必要的功能在openpyxl的只读或只写模式下可以禁用不需要的属性计算来提升速度。考虑替代格式如果最终用户同意对于纯数据交换.csv或.parquet格式的写入速度和压缩比远高于Excel。6.4 格式丢失与保留问题描述用pandas或openpyxl的某些方式写入后原有的单元格颜色、公式、列宽等都没了。根本原因Excel文件不仅仅是数据还是包含样式、公式、宏等信息的压缩包.xlsx本质是ZIP。简单的数据写入操作不会携带这些信息。最佳实践明确需求如果文件是给人看的报告格式很重要优先使用openpyxl加载现有模板文件然后在指定位置填充数据。使用模板创建一个带有所有格式、公式、图表的工作簿作为“模板”。脚本只打开这个模板向特定单元格或区域写入数据然后另存为新文件。这样能完美保留格式。编程设置格式如果格式是固定的可以用openpyxl的API在写入数据后重新应用样式字体、颜色、边框、对齐方式。但这通常比用模板更繁琐。6.5 编码与中文乱码问题描述写入的中文在Excel中显示为乱码。原因与解决.xlsx格式通常使用UTF-8编码openpyxl和pandas默认支持良好很少出问题。如果遇到检查Python源文件本身的编码确保是UTF-8以及数据源的编码。.xls格式xlwtxlwt默认使用ASCII编码。必须在创建Workbook时指定encoding‘utf-8’或encoding‘gbk’取决于你的系统环境如xlwt.Workbook(encoding‘utf-8’)。文件路径包含中文确保文件路径字符串在Python中是正确的Unicode字符串。在Python 3中字符串默认是Unicode通常没问题。6.6 版本兼容性与依赖管理问题描述脚本在本地运行正常放到服务器或同事电脑上就报错提示找不到模块或版本不兼容。解决方案使用requirements.txt为你的项目创建依赖清单。# requirements.txt openpyxl3.1.2 pandas2.1.1 xlwt1.3.0注意pandas的引擎pandas的to_excel方法默认引擎可能因系统安装的库而变化。为了稳定建议显式指定# 写.xlsx df.to_excel(‘output.xlsx’ engine‘openpyxl’) # 写.xls df.to_excel(‘output.xls’ engine‘xlwt’)测试不同环境在部署前在干净的环境如虚拟环境中测试安装和运行。最后我个人的一个强烈建议是为你的Excel写入函数编写单元测试。测试用例可以覆盖写入少量数据是否正确、追加数据是否到位、写入大量数据是否抛出异常、生成的文件能否被Excel正常打开等。自动化测试能极大减少手动验证的工作量尤其是在脚本被频繁修改或用于关键任务时。你可以使用pytest框架结合openpyxl或pandas读回数据来进行断言比较。这看似多花了一点时间但长期来看是保证代码质量和稳定性的最有效投资。