1. 为什么需要自己编写Excel读写程序在日常工作中Excel文件处理是几乎每个岗位都会遇到的常规需求。你可能需要从ERP系统导出数据到Excel进行分析将数据库查询结果生成报表批量修改成百上千个Excel文件中的特定内容自动化处理定期收到的同格式Excel文件手动操作不仅效率低下而且容易出错。我曾经处理过一个案例财务同事需要每月合并12个分公司的Excel报表手动复制粘贴经常导致数据错位后来用Python脚本自动化后处理时间从2小时缩短到2分钟准确率提升到100%。2. 主流Excel操作库对比选型2.1 Python生态中的Excel处理库目前Python中最常用的Excel处理库主要有以下几个库名称读写支持文件格式支持特点适用场景openpyxl读写.xlsx功能全面支持样式修改需要精细控制Excel格式xlrd/xlwt读/写.xls(旧版)轻量级速度快处理旧版Excel文件pandas读写.xlsx, .xls, .csv数据分析友好接口简洁数据清洗和分析xlsxwriter写.xlsx生成复杂报表支持图表需要生成专业报表pyexcel读写多种格式统一接口简化操作需要处理多种文件格式2.2 为什么选择openpyxlpandas组合经过多个项目实践我发现openpyxlpandas的组合最能兼顾灵活性和效率pandas提供DataFrame这一强大数据结构特别适合表格数据处理openpyxl可以精细控制Excel的样式、公式等细节两者结合可以先用pandas处理数据再用openpyxl调整格式提示如果只是简单读写pandas的read_excel/to_excel就足够了如果需要复杂格式控制再引入openpyxl3. 环境准备与基础配置3.1 安装必要的库建议使用pip安装以下库pip install pandas openpyxl xlrdxlrd虽然主要用于读取旧版.xls文件但作为pandas的可选依赖建议一并安装。3.2 基础读写示例先来看一个最简单的读写示例import pandas as pd # 读取Excel文件 df pd.read_excel(input.xlsx, sheet_nameSheet1) # 简单数据处理 - 添加一列 df[New_Column] df[Existing_Column] * 2 # 写入新文件 df.to_excel(output.xlsx, indexFalse)这个基础脚本已经可以完成大多数简单的Excel处理任务。4. 高级Excel操作技巧4.1 处理大型Excel文件当处理超过10万行的Excel文件时直接读取可能会导致内存不足。这时可以采用分块读取# 分块读取大文件 chunk_size 50000 chunks pd.read_excel(large_file.xlsx, chunksizechunk_size) for i, chunk in enumerate(chunks): # 处理每个数据块 processed_chunk do_some_processing(chunk) # 如果是第一个块写入新文件包含表头 if i 0: processed_chunk.to_excel(output_large.xlsx, indexFalse) # 后续块追加写入不包含表头 else: with pd.ExcelWriter(output_large.xlsx, modea, engineopenpyxl) as writer: processed_chunk.to_excel(writer, indexFalse, headerFalse)4.2 保留原始格式的修改如果需要修改Excel但保留原有格式如单元格颜色、边框等可以使用openpyxl直接操作from openpyxl import load_workbook # 加载现有工作簿 wb load_workbook(template.xlsx) ws wb[Sheet1] # 修改特定单元格的值但保留格式 ws[A1].value New Value # 添加条件格式 from openpyxl.formatting.rule import ColorScaleRule color_scale_rule ColorScaleRule(start_typemin, start_colorFF0000, end_typemax, end_color00FF00) ws.conditional_formatting.add(B2:B100, color_scale_rule) # 保存修改 wb.save(modified_template.xlsx)4.3 处理多Sheet工作簿实际业务中经常遇到包含多个Sheet的Excel文件# 读取所有Sheet名 xls pd.ExcelFile(multi_sheet.xlsx) sheet_names xls.sheet_names # 创建ExcelWriter对象用于写入多个Sheet with pd.ExcelWriter(output_multi.xlsx) as writer: for sheet in sheet_names: df pd.read_excel(multi_sheet.xlsx, sheet_namesheet) # 对每个Sheet的数据进行处理 processed_df process_data(df) # 写入到输出文件的对应Sheet processed_df.to_excel(writer, sheet_namesheet, indexFalse)5. 实战案例销售报表自动化处理让我们通过一个真实案例来综合运用这些技术。假设我们需要从销售系统导出的原始Excel中读取数据计算每个销售员的月度销售额生成带有格式的最终报表5.1 数据读取与清洗import pandas as pd from datetime import datetime # 读取原始数据 raw_df pd.read_excel(sales_raw.xlsx, parse_dates[order_date], dtype{salesperson_id: str}) # 数据清洗 clean_df raw_df.dropna(subset[amount]) # 删除金额为空的行 clean_df clean_df[clean_df[amount] 0] # 只保留正数金额 # 提取月份 clean_df[month] clean_df[order_date].dt.to_period(M)5.2 数据汇总与分析# 按销售员和月份汇总 monthly_sales clean_df.groupby([salesperson_id, salesperson_name, month])[amount].sum().reset_index() # 计算排名 monthly_sales[rank] monthly_sales.groupby(month)[amount].rank(ascendingFalse) # 添加绩效评级 def get_performance_level(rank): if rank 3: return A elif rank 10: return B else: return C monthly_sales[performance] monthly_sales[rank].apply(get_performance_level)5.3 生成格式化的最终报表from openpyxl.styles import Font, PatternFill, Border, Side from openpyxl.utils.dataframe import dataframe_to_rows # 创建新工作簿 wb load_workbook() ws wb.active ws.title Monthly Report # 写入数据 for r in dataframe_to_rows(monthly_sales, indexFalse, headerTrue): ws.append(r) # 设置样式 header_font Font(boldTrue, colorFFFFFF) header_fill PatternFill(start_color4F81BD, end_color4F81BD, fill_typesolid) thin_border Border(leftSide(stylethin), rightSide(stylethin), topSide(stylethin), bottomSide(stylethin)) # 应用表头样式 for cell in ws[1]: cell.font header_font cell.fill header_fill # 应用边框 for row in ws.iter_rows(min_row2, max_rowws.max_row, max_colws.max_column): for cell in row: cell.border thin_border # 设置列宽 ws.column_dimensions[A].width 12 ws.column_dimensions[B].width 20 ws.column_dimensions[C].width 12 ws.column_dimensions[D].width 15 ws.column_dimensions[E].width 10 ws.column_dimensions[F].width 12 # 保存最终文件 wb.save(monthly_sales_report.xlsx)6. 常见问题与解决方案6.1 中文乱码问题当Excel中包含中文时可能会遇到乱码问题。解决方法确保文件保存时使用UTF-8编码df.to_excel(output.xlsx, indexFalse, encodingutf-8)读取时指定编码df pd.read_excel(input.xlsx, encodingutf-8)如果使用openpyxl直接操作中文一般不会出问题6.2 日期格式处理Excel中的日期经常会出现格式混乱建议# 读取时明确指定日期列 df pd.read_excel(data.xlsx, parse_dates[order_date, delivery_date]) # 写入时格式化日期 with pd.ExcelWriter(output.xlsx, datetime_formatYYYY-MM-DD) as writer: df.to_excel(writer)6.3 性能优化技巧处理大型Excel文件时可以采用以下优化方法禁用不必要的功能pd.read_excel(large.xlsx, engineopenpyxl, usecols[col1, col2], # 只读取需要的列 nrows1000) # 测试时限制行数使用更快的库# 使用pyxlsb读取.xlsb二进制文件速度更快 df pd.read_excel(data.xlsb, enginepyxlsb)内存映射模式# 对于特别大的文件考虑使用内存映射 df pd.read_excel(huge.xlsx, memory_mapTrue)7. 扩展应用与其他工具集成7.1 与数据库交互典型的ETL流程从数据库读取 → 处理 → 导出到Excelimport sqlalchemy import pandas as pd # 创建数据库连接 engine sqlalchemy.create_engine(postgresql://user:passwordlocalhost:5432/dbname) # 读取SQL查询结果 query SELECT * FROM sales WHERE order_date 2023-01-01 df pd.read_sql(query, engine) # 数据处理... # 导出到Excel df.to_excel(sales_report.xlsx, indexFalse)7.2 自动化邮件发送处理完Excel后自动发送邮件import smtplib from email.mime.multipart import MIMEMultipart from email.mime.base import MIMEBase from email import encoders msg MIMEMultipart() msg[From] your_emailexample.com msg[To] recipientexample.com msg[Subject] Monthly Sales Report # 添加附件 with open(monthly_sales_report.xlsx, rb) as f: part MIMEBase(application, octet-stream) part.set_payload(f.read()) encoders.encode_base64(part) part.add_header(Content-Disposition, attachment, filenamereport.xlsx) msg.attach(part) # 发送邮件 server smtplib.SMTP(smtp.example.com, 587) server.starttls() server.login(your_emailexample.com, password) server.send_message(msg) server.quit()7.3 定时自动运行使用Windows任务计划或Linux的cron设置定时任务让脚本定期自动运行。对于Windows可以创建批处理文件echo off python C:\path\to\your\script.py然后使用任务计划程序设置每天/每周运行。8. 最佳实践与经验分享经过多个Excel自动化项目的实践我总结了以下经验防御性编程总是检查文件是否存在处理前备份原始文件捕获并妥善处理异常import os from shutil import copyfile input_file important_data.xlsx if not os.path.exists(input_file): raise FileNotFoundError(f{input_file} not found!) # 创建备份 backup_file fbackup_{datetime.now().strftime(%Y%m%d_%H%M%S)}.xlsx copyfile(input_file, backup_file) try: # 处理文件... except Exception as e: print(fError processing file: {str(e)}) # 可以考虑恢复备份日志记录 添加详细的日志记录便于排查问题import logging logging.basicConfig( levellogging.INFO, format%(asctime)s - %(levelname)s - %(message)s, handlers[ logging.FileHandler(excel_processor.log), logging.StreamHandler() ] ) logging.info(Starting Excel processing...)配置与代码分离 将文件路径、参数等放在配置文件中config.ini:[input] file_path data/raw_input.xlsx sheet_name SalesData [output] file_path reports/monthly_report.xlsx date_format YYYY-MM-DD然后在代码中读取import configparser config configparser.ConfigParser() config.read(config.ini) input_path config[input][file_path]单元测试 为关键功能编写测试用例import unittest from your_module import calculate_sales class TestSalesCalculations(unittest.TestCase): def test_calculate_sales(self): test_data pd.DataFrame({ amount: [100, 200, 300] }) result calculate_sales(test_data) self.assertEqual(result[total_sales][0], 600) if __name__ __main__: unittest.main()性能监控 添加简单的性能统计import time start_time time.time() # 处理代码... end_time time.time() logging.info(fProcessing completed in {end_time - start_time:.2f} seconds)这些实践看似增加了额外工作但在长期运行和维护中能节省大量时间。特别是在处理关键业务数据时稳健性比开发速度更重要。
1. 为什么需要自己编写Excel读写程序在日常工作中Excel文件处理是几乎每个岗位都会遇到的常规需求。你可能需要从ERP系统导出数据到Excel进行分析将数据库查询结果生成报表批量修改成百上千个Excel文件中的特定内容自动化处理定期收到的同格式Excel文件手动操作不仅效率低下而且容易出错。我曾经处理过一个案例财务同事需要每月合并12个分公司的Excel报表手动复制粘贴经常导致数据错位后来用Python脚本自动化后处理时间从2小时缩短到2分钟准确率提升到100%。2. 主流Excel操作库对比选型2.1 Python生态中的Excel处理库目前Python中最常用的Excel处理库主要有以下几个库名称读写支持文件格式支持特点适用场景openpyxl读写.xlsx功能全面支持样式修改需要精细控制Excel格式xlrd/xlwt读/写.xls(旧版)轻量级速度快处理旧版Excel文件pandas读写.xlsx, .xls, .csv数据分析友好接口简洁数据清洗和分析xlsxwriter写.xlsx生成复杂报表支持图表需要生成专业报表pyexcel读写多种格式统一接口简化操作需要处理多种文件格式2.2 为什么选择openpyxlpandas组合经过多个项目实践我发现openpyxlpandas的组合最能兼顾灵活性和效率pandas提供DataFrame这一强大数据结构特别适合表格数据处理openpyxl可以精细控制Excel的样式、公式等细节两者结合可以先用pandas处理数据再用openpyxl调整格式提示如果只是简单读写pandas的read_excel/to_excel就足够了如果需要复杂格式控制再引入openpyxl3. 环境准备与基础配置3.1 安装必要的库建议使用pip安装以下库pip install pandas openpyxl xlrdxlrd虽然主要用于读取旧版.xls文件但作为pandas的可选依赖建议一并安装。3.2 基础读写示例先来看一个最简单的读写示例import pandas as pd # 读取Excel文件 df pd.read_excel(input.xlsx, sheet_nameSheet1) # 简单数据处理 - 添加一列 df[New_Column] df[Existing_Column] * 2 # 写入新文件 df.to_excel(output.xlsx, indexFalse)这个基础脚本已经可以完成大多数简单的Excel处理任务。4. 高级Excel操作技巧4.1 处理大型Excel文件当处理超过10万行的Excel文件时直接读取可能会导致内存不足。这时可以采用分块读取# 分块读取大文件 chunk_size 50000 chunks pd.read_excel(large_file.xlsx, chunksizechunk_size) for i, chunk in enumerate(chunks): # 处理每个数据块 processed_chunk do_some_processing(chunk) # 如果是第一个块写入新文件包含表头 if i 0: processed_chunk.to_excel(output_large.xlsx, indexFalse) # 后续块追加写入不包含表头 else: with pd.ExcelWriter(output_large.xlsx, modea, engineopenpyxl) as writer: processed_chunk.to_excel(writer, indexFalse, headerFalse)4.2 保留原始格式的修改如果需要修改Excel但保留原有格式如单元格颜色、边框等可以使用openpyxl直接操作from openpyxl import load_workbook # 加载现有工作簿 wb load_workbook(template.xlsx) ws wb[Sheet1] # 修改特定单元格的值但保留格式 ws[A1].value New Value # 添加条件格式 from openpyxl.formatting.rule import ColorScaleRule color_scale_rule ColorScaleRule(start_typemin, start_colorFF0000, end_typemax, end_color00FF00) ws.conditional_formatting.add(B2:B100, color_scale_rule) # 保存修改 wb.save(modified_template.xlsx)4.3 处理多Sheet工作簿实际业务中经常遇到包含多个Sheet的Excel文件# 读取所有Sheet名 xls pd.ExcelFile(multi_sheet.xlsx) sheet_names xls.sheet_names # 创建ExcelWriter对象用于写入多个Sheet with pd.ExcelWriter(output_multi.xlsx) as writer: for sheet in sheet_names: df pd.read_excel(multi_sheet.xlsx, sheet_namesheet) # 对每个Sheet的数据进行处理 processed_df process_data(df) # 写入到输出文件的对应Sheet processed_df.to_excel(writer, sheet_namesheet, indexFalse)5. 实战案例销售报表自动化处理让我们通过一个真实案例来综合运用这些技术。假设我们需要从销售系统导出的原始Excel中读取数据计算每个销售员的月度销售额生成带有格式的最终报表5.1 数据读取与清洗import pandas as pd from datetime import datetime # 读取原始数据 raw_df pd.read_excel(sales_raw.xlsx, parse_dates[order_date], dtype{salesperson_id: str}) # 数据清洗 clean_df raw_df.dropna(subset[amount]) # 删除金额为空的行 clean_df clean_df[clean_df[amount] 0] # 只保留正数金额 # 提取月份 clean_df[month] clean_df[order_date].dt.to_period(M)5.2 数据汇总与分析# 按销售员和月份汇总 monthly_sales clean_df.groupby([salesperson_id, salesperson_name, month])[amount].sum().reset_index() # 计算排名 monthly_sales[rank] monthly_sales.groupby(month)[amount].rank(ascendingFalse) # 添加绩效评级 def get_performance_level(rank): if rank 3: return A elif rank 10: return B else: return C monthly_sales[performance] monthly_sales[rank].apply(get_performance_level)5.3 生成格式化的最终报表from openpyxl.styles import Font, PatternFill, Border, Side from openpyxl.utils.dataframe import dataframe_to_rows # 创建新工作簿 wb load_workbook() ws wb.active ws.title Monthly Report # 写入数据 for r in dataframe_to_rows(monthly_sales, indexFalse, headerTrue): ws.append(r) # 设置样式 header_font Font(boldTrue, colorFFFFFF) header_fill PatternFill(start_color4F81BD, end_color4F81BD, fill_typesolid) thin_border Border(leftSide(stylethin), rightSide(stylethin), topSide(stylethin), bottomSide(stylethin)) # 应用表头样式 for cell in ws[1]: cell.font header_font cell.fill header_fill # 应用边框 for row in ws.iter_rows(min_row2, max_rowws.max_row, max_colws.max_column): for cell in row: cell.border thin_border # 设置列宽 ws.column_dimensions[A].width 12 ws.column_dimensions[B].width 20 ws.column_dimensions[C].width 12 ws.column_dimensions[D].width 15 ws.column_dimensions[E].width 10 ws.column_dimensions[F].width 12 # 保存最终文件 wb.save(monthly_sales_report.xlsx)6. 常见问题与解决方案6.1 中文乱码问题当Excel中包含中文时可能会遇到乱码问题。解决方法确保文件保存时使用UTF-8编码df.to_excel(output.xlsx, indexFalse, encodingutf-8)读取时指定编码df pd.read_excel(input.xlsx, encodingutf-8)如果使用openpyxl直接操作中文一般不会出问题6.2 日期格式处理Excel中的日期经常会出现格式混乱建议# 读取时明确指定日期列 df pd.read_excel(data.xlsx, parse_dates[order_date, delivery_date]) # 写入时格式化日期 with pd.ExcelWriter(output.xlsx, datetime_formatYYYY-MM-DD) as writer: df.to_excel(writer)6.3 性能优化技巧处理大型Excel文件时可以采用以下优化方法禁用不必要的功能pd.read_excel(large.xlsx, engineopenpyxl, usecols[col1, col2], # 只读取需要的列 nrows1000) # 测试时限制行数使用更快的库# 使用pyxlsb读取.xlsb二进制文件速度更快 df pd.read_excel(data.xlsb, enginepyxlsb)内存映射模式# 对于特别大的文件考虑使用内存映射 df pd.read_excel(huge.xlsx, memory_mapTrue)7. 扩展应用与其他工具集成7.1 与数据库交互典型的ETL流程从数据库读取 → 处理 → 导出到Excelimport sqlalchemy import pandas as pd # 创建数据库连接 engine sqlalchemy.create_engine(postgresql://user:passwordlocalhost:5432/dbname) # 读取SQL查询结果 query SELECT * FROM sales WHERE order_date 2023-01-01 df pd.read_sql(query, engine) # 数据处理... # 导出到Excel df.to_excel(sales_report.xlsx, indexFalse)7.2 自动化邮件发送处理完Excel后自动发送邮件import smtplib from email.mime.multipart import MIMEMultipart from email.mime.base import MIMEBase from email import encoders msg MIMEMultipart() msg[From] your_emailexample.com msg[To] recipientexample.com msg[Subject] Monthly Sales Report # 添加附件 with open(monthly_sales_report.xlsx, rb) as f: part MIMEBase(application, octet-stream) part.set_payload(f.read()) encoders.encode_base64(part) part.add_header(Content-Disposition, attachment, filenamereport.xlsx) msg.attach(part) # 发送邮件 server smtplib.SMTP(smtp.example.com, 587) server.starttls() server.login(your_emailexample.com, password) server.send_message(msg) server.quit()7.3 定时自动运行使用Windows任务计划或Linux的cron设置定时任务让脚本定期自动运行。对于Windows可以创建批处理文件echo off python C:\path\to\your\script.py然后使用任务计划程序设置每天/每周运行。8. 最佳实践与经验分享经过多个Excel自动化项目的实践我总结了以下经验防御性编程总是检查文件是否存在处理前备份原始文件捕获并妥善处理异常import os from shutil import copyfile input_file important_data.xlsx if not os.path.exists(input_file): raise FileNotFoundError(f{input_file} not found!) # 创建备份 backup_file fbackup_{datetime.now().strftime(%Y%m%d_%H%M%S)}.xlsx copyfile(input_file, backup_file) try: # 处理文件... except Exception as e: print(fError processing file: {str(e)}) # 可以考虑恢复备份日志记录 添加详细的日志记录便于排查问题import logging logging.basicConfig( levellogging.INFO, format%(asctime)s - %(levelname)s - %(message)s, handlers[ logging.FileHandler(excel_processor.log), logging.StreamHandler() ] ) logging.info(Starting Excel processing...)配置与代码分离 将文件路径、参数等放在配置文件中config.ini:[input] file_path data/raw_input.xlsx sheet_name SalesData [output] file_path reports/monthly_report.xlsx date_format YYYY-MM-DD然后在代码中读取import configparser config configparser.ConfigParser() config.read(config.ini) input_path config[input][file_path]单元测试 为关键功能编写测试用例import unittest from your_module import calculate_sales class TestSalesCalculations(unittest.TestCase): def test_calculate_sales(self): test_data pd.DataFrame({ amount: [100, 200, 300] }) result calculate_sales(test_data) self.assertEqual(result[total_sales][0], 600) if __name__ __main__: unittest.main()性能监控 添加简单的性能统计import time start_time time.time() # 处理代码... end_time time.time() logging.info(fProcessing completed in {end_time - start_time:.2f} seconds)这些实践看似增加了额外工作但在长期运行和维护中能节省大量时间。特别是在处理关键业务数据时稳健性比开发速度更重要。
![[C++]内存管理：串顺序存储的内存回收](http://pic.xiahunao.cn/yaotu/[C++]内存管理：串顺序存储的内存回收)
