Python操作Excel文件完整指南

一、常用库对比二、pandas操作Excel推荐1. 读取Excel文件import pandas as pd# 读取整个Excel文件df pd.read_excel(input.xlsx, sheet_nameSheet1)# 读取特定列df pd.read_excel(input.xlsx, usecols[A, C])# 读取多个工作表with pd.ExcelFile(input.xlsx) as xls:df1 pd.read_excel(xls, Sheet1)df2 pd.read_excel(xls, Sheet2)2. 写入Excel文件# 创建示例数据data {姓名: [张三, 李四, 王五],年龄: [25, 32, 28],城市: [北京, 上海, 广州]}df pd.DataFrame(data)# 写入单个工作表df.to_excel(output.xlsx, indexFalse)# 写入多个工作表with pd.ExcelWriter(multi_sheet.xlsx) as writer:df.to_excel(writer, sheet_name员工信息, indexFalse)# 创建第二个工作表summary df.groupby(城市).mean()summary.to_excel(writer, sheet_name城市统计)3. 数据处理示例# 读取Exceldf pd.read_excel(sales.xlsx)# 数据清洗df df.dropna() # 删除空行df df[df[销售额] 0] # 过滤无效数据# 数据转换df[日期] pd.to_datetime(df[日期])df[月份] df[日期].dt.month# 数据分析monthly_sales df.groupby(月份)[销售额].sum()# 保存结果monthly_sales.to_excel(月度销售额.xlsx, header[总销售额])三、openpyxl操作Excel精细控制1. 创建Excel文件from openpyxl import Workbookfrom openpyxl.styles import Font, Alignment, Border, Side# 创建工作簿wb Workbook()ws wb.activews.title 员工信息# 添加表头headers [姓名, 部门, 工资]for col, header in enumerate(headers, 1):cell ws.cell(row1, columncol, valueheader)cell.font Font(boldTrue)cell.alignment Alignment(horizontalcenter)# 添加数据employees [[张三, 技术部, 15000],[李四, 市场部, 12000],[王五, 财务部, 18000]]for row, employee in enumerate(employees, 2):for col, value in enumerate(employee, 1):ws.cell(rowrow, columncol, valuevalue)# 设置列宽ws.column_dimensions[A].width 15ws.column_dimensions[B].width 15ws.column_dimensions[C].width 12# 添加边框thin_border Border(leftSide(stylethin),rightSide(stylethin),topSide(stylethin),bottomSide(stylethin))for row in ws.iter_rows(min_row1, max_rowlen(employees)1, max_collen(headers)):for cell in row:cell.border thin_border# 保存文件wb.save(员工信息.xlsx)2. 读取和修改Excelfrom openpyxl import load_workbook# 加载工作簿wb load_workbook(员工信息.xlsx)ws wb.active# 读取数据for row in ws.iter_rows(min_row2, values_onlyTrue):print(f{row[0]} ({row[1]}): ¥{row[2]:,})# 修改数据# 给技术部员工加薪10%for row in range(2, ws.max_row 1):department ws.cell(rowrow, column2).valueif department 技术部:salary ws.cell(rowrow, column3).valuews.cell(rowrow, column3, valuesalary * 1.1)# 添加公式 - 计算平均工资ws.cell(rowws.max_row1, column2, value平均工资).font Font(boldTrue)ws.cell(rowws.max_row, column3, valuefAVERAGE(C2:C{ws.max_row-1}))# 保存修改wb.save(员工信息_更新.xlsx)四、高级应用技巧1. 合并多个Excel文件import osimport pandas as pdfolder_path 销售数据all_files [f for f in os.listdir(folder_path) if f.endswith(.xlsx)]combined_df pd.DataFrame()for file in all_files:file_path os.path.join(folder_path, file)df pd.read_excel(file_path)combined_df pd.concat([combined_df, df], ignore_indexTrue)combined_df.to_excel(合并销售数据.xlsx, indexFalse)2. 添加图表使用xlsxwriterimport pandas as pd# 创建示例数据data {月份: [1月, 2月, 3月, 4月, 5月],销售额: [120, 150, 180, 90, 210]}df pd.DataFrame(data)# 使用xlsxwriter引擎writer pd.ExcelWriter(销售图表.xlsx, enginexlsxwriter)df.to_excel(writer, sheet_name报告, indexFalse)workbook writer.bookworksheet writer.sheets[报告]# 创建柱状图chart workbook.add_chart({type: column})chart.add_series({name: 报告!$B$1,categories: 报告!$A$2:$A$6,values: 报告!$B$2:$B$6,})# 设置图表样式chart.set_title({name: 月度销售额})chart.set_x_axis({name: 月份})chart.set_y_axis({name: 销售额 (万元)})# 将图表插入工作表worksheet.insert_chart(D2, chart)# 保存文件writer.save()3. 自动化报表生成import pandas as pdfrom datetime import datetime# 读取数据sales_df pd.read_excel(销售数据.xlsx)products_df pd.read_excel(产品信息.xlsx)# 合并数据merged_df pd.merge(sales_df, products_df, on产品ID)# 计算指标report merged_df.groupby([月份, 产品类别]).agg({销售额: sum,数量: sum}).reset_index()# 添加当前日期current_date datetime.now().strftime(%Y-%m-%d)report[生成日期] current_date# 创建Excel文件with pd.ExcelWriter(f销售报告_{current_date}.xlsx) as writer:# 主报告report.to_excel(writer, sheet_name销售汇总, indexFalse)# 添加数据透视表pivot report.pivot_table(index产品类别,columns月份,values销售额,aggfuncsum)pivot.to_excel(writer, sheet_name数据透视)# 添加图表工作表workbook writer.bookchart_sheet workbook.add_worksheet(图表)# 创建图表chart workbook.add_chart({type: column})# 添加数据系列for i, month in enumerate(report[月份].unique(), 1):chart.add_series({name: month,categories: 销售汇总!$B$2:$B$5,values: f销售汇总!$C$2:$C$5})# 插入图表chart_sheet.insert_chart(A1, chart)五、最佳实践处理大数据集使用chunksize参数分块读取避免在内存中存储整个数据集chunk_size 10000 chunks pd.read_excel(large_file.xlsx, chunksizechunk_size) for chunk in chunks: process_chunk(chunk) # 自定义处理函数性能优化关闭自动计算openpyxl中使用data_onlyTrue批量写入在循环外收集所有修改一次性写入错误处理try: df pd.read_excel(file.xlsx) except FileNotFoundError: print(文件不存在) except Exception as e: print(f读取文件时出错: {str(e)})保护敏感数据# 移除敏感列 df.drop([密码, 身份证号], axis1, inplaceTrue) # 保存时加密 with pd.ExcelWriter(output.xlsx, engineopenpyxl) as writer: df.to_excel(writer) writer.book.security.workbookPassword your_password自动化任务调度结合Windows任务计划或Linux cron定时运行Python脚本