Python深度挖掘:openpyxl和pandas的使用详细_pandas openpyxl
文章目录
-
- 一、Excel处理在数据分析中的重要性
- 二、openpyxl基础与核心功能
-
- 2.1 openpyxl简介与安装
- 2.2 工作簿与工作表的基本操作
-
- 创建新工作簿
- 打开已有工作簿
- 工作表操作
- 2.3 单元格操作详解
-
- 基本单元格操作
- 批量操作单元格
- 特殊单元格操作
- 2.4 样式与格式设置
-
- 字体样式
- 对齐方式
- 边框设置
- 填充颜色
- 数字格式
- 2.5 公式与计算
- 2.6 图表与图像操作
-
- 创建图表
- 插入图像
- 2.7 高级功能
-
- 数据验证
- 条件格式
- 保护工作表
- 冻结窗格
- 三、pandas基础与核心功能
-
- 3.1 pandas简介与安装
- 3.2 DataFrame基础
-
- 创建DataFrame
- 基本属性
- 数据选择
- 数据修改
- 3.3 数据导入与导出
-
- 读取Excel文件
- 写入Excel文件
- 其他格式支持
- 3.4 数据清洗与预处理
-
- 处理缺失值
- 处理重复值
- 数据类型转换
- 字符串操作
- 日期处理
- 3.5 数据转换与计算
-
- 应用函数
- 分组聚合
- 数据透视表
- 合并与连接
- 3.6 高级数据分析功能
-
- 时间序列分析
- 统计函数
- 可视化
- 四、openpyxl与pandas的协同使用
-
- 4.1 结合使用的优势与场景
- 4.2 pandas DataFrame与openpyxl的转换
-
- DataFrame写入Excel并保持格式
- 从openpyxl读取数据到DataFrame
- 4.3 复杂报表生成案例
- 4.4 模板填充技术
- 4.5 性能优化技巧
- 五、实战应用案例
-
- 5.1 财务报表自动化
一、Excel处理在数据分析中的重要性
在现代数据分析工作中，Excel文件几乎无处不在。作为最广泛使用的电子表格工具，Excel在企业数据存储、报表生成和初步数据分析中扮演着重要角色。根据最新调查，超过80%的企业在日常运营中使用Excel作为主要的数据管理工具之一。因此，掌握Python处理Excel文件的能力对于数据分析师、财务人员和科研工作者来说至关重要。
Python提供了多种处理Excel文件的库，其中最常用的是openpyxl和pandas。openpyxl专注于直接操作Excel文件（特别是.xlsx格式），提供了单元格级别的精细控制；而pandas则是一个强大的数据分析库，可以方便地将Excel数据读入DataFrame进行复杂的数据处理和分析。
本文将深入探讨这两个库的使用方法，从基础操作到高级技巧，帮助读者全面掌握Python处理Excel文件的能力。我们将通过大量实际示例展示如何结合使用这两个库来完成各种复杂的Excel处理任务。
二、openpyxl基础与核心功能
2.1 openpyxl简介与安装
openpyxl是一个专门用于读写Excel 2010 xlsx/xlsm/xltx/xltm文件的Python库。它不依赖于Excel软件本身，可以直接操作Excel文件，非常适合自动化处理Excel报表。
安装openpyxl非常简单，使用pip命令即可：
pip install openpyxl如果需要处理图表，还需要安装额外的依赖：
pip install openpyxl[charts]2.2 工作簿与工作表的基本操作
创建新工作簿
from openpyxl import Workbook# 创建一个新工作簿wb = Workbook()# 获取活动的工作表ws = wb.active# 设置工作表标题ws.title = \"第一个工作表\"# 创建新的工作表ws1 = wb.create_sheet(\"第二个工作表\") # 默认插入到最后ws2 = wb.create_sheet(\"第三个工作表\", 0) # 插入到第一个位置# 保存工作簿wb.save(\"新工作簿.xlsx\")打开已有工作簿
from openpyxl import load_workbook# 打开一个已存在的工作簿wb = load_workbook(\'example.xlsx\')# 获取所有工作表名称print(wb.sheetnames)# 通过名称获取工作表ws = wb[\'Sheet1\']# 检查工作表是否存在if \'Sheet1\' in wb.sheetnames: print(\"Sheet1存在\")工作表操作
# 复制工作表source = wb[\'Sheet1\']target = wb.copy_worksheet(source)target.title = \"Sheet1的副本\"# 删除工作表del wb[\'Sheet1的副本\']# 遍历所有工作表for sheet in wb: print(sheet.title)2.3 单元格操作详解
基本单元格操作
# 获取单元格cell = ws[\'A1\']# 写入值ws[\'A1\'] = \"Hello\"ws[\'B1\'] = \"World\"# 使用单元格坐标ws.cell(row=1, column=3, value=\"!\")# 读取值print(ws[\'A1\'].value) # 输出: Hello# 单元格坐标print(cell.row, cell.column) # 输出: 1, 1print(cell.coordinate) # 输出: A1批量操作单元格
# 批量写入数据for row in range(1, 6): for col in range(1, 5): ws.cell(row=row, column=col, value=f\"R{row}C{col}\")# 批量读取数据for row in ws.iter_rows(min_row=1, max_row=3, min_col=1, max_col=3): for cell in row: print(cell.value, end=\"\\t\") print()# 使用values_only参数只获取值for row in ws.iter_rows(values_only=True): print(row)特殊单元格操作
# 合并单元格ws.merge_cells(\'A1:D1\')ws[\'A1\'] = \"合并的标题\"# 取消合并ws.unmerge_cells(\'A1:D1\')# 设置行高和列宽ws.row_dimensions[1].height = 30ws.column_dimensions[\'A\'].width = 20# 隐藏行列ws.column_dimensions[\'B\'].hidden = Truews.row_dimensions[2].hidden = True2.4 样式与格式设置
字体样式
from openpyxl.styles import Font, Color# 设置字体bold_font = Font(name=\'Arial\', size=12, bold=True, italic=False, color=\'FF0000\')ws[\'A1\'].font = bold_font# 修改现有单元格字体for row in ws.iter_rows(min_row=2, max_row=5): for cell in row: cell.font = Font(size=10, color=Color(rgb=\'0000FF\'))对齐方式
from openpyxl.styles import Alignment# 设置对齐方式center_aligned = Alignment(horizontal=\'center\', vertical=\'center\', wrap_text=True)ws[\'A1\'].alignment = center_aligned# 应用到范围for row in ws.iter_rows(min_row=1, max_row=10): for cell in row: cell.alignment = Alignment(horizontal=\'center\')边框设置
from openpyxl.styles import Border, Side# 定义边框样式thin_border = Border(left=Side(style=\'thin\'),right=Side(style=\'thin\'), top=Side(style=\'thin\'), bottom=Side(style=\'thin\'))# 应用边框for row in ws.iter_rows(min_row=1, max_row=5): for cell in row: cell.border = thin_border填充颜色
from openpyxl.styles import PatternFill, GradientFill# 纯色填充yellow_fill = PatternFill(start_color=\'FFFF00\', end_color=\'FFFF00\', fill_type=\'solid\')ws[\'A1\'].fill = yellow_fill# 渐变填充gradient_fill = GradientFill(stop=(\'FFFFFF\', \'0000FF\'))ws[\'B1\'].fill = gradient_fill数字格式
from openpyxl.styles import numbers# 设置数字格式ws[\'C1\'].value = 3.1415926ws[\'C1\'].number_format = numbers.FORMAT_NUMBER_00 # 显示两位小数# 自定义格式ws[\'D1\'].value = 0.85ws[\'D1\'].number_format = \'0.00%\' # 显示为百分比2.5 公式与计算
# 写入公式ws[\'E1\'] = \'=SUM(A1:D1)\'ws[\'E2\'] = \'=AVERAGE(A2:D2)\'ws[\'E3\'] = \'=IF(A3>B3, \"A大\", \"B大\")\'# 读取公式print(ws[\'E1\'].value) # 输出: =SUM(A1:D1)# 计算数据ws[\'F1\'] = \'结果\'ws[\'F2\'] = \'=E2*100\'2.6 图表与图像操作
创建图表
from openpyxl.chart import BarChart, Reference# 准备数据for i in range(1, 6): ws[f\'A{i}\'] = i ws[f\'B{i}\'] = i*i# 创建柱状图chart = BarChart()data = Reference(ws, min_col=2, min_row=1, max_row=5)categories = Reference(ws, min_col=1, min_row=2, max_row=5)chart.add_data(data, titles_from_data=True)chart.set_categories(categories)# 添加图表到工作表ws.add_chart(chart, \"D1\")插入图像
from openpyxl.drawing.image import Image# 插入图像img = Image(\'logo.png\')ws.add_image(img, \'A10\')# 调整图像大小img.width = 100img.height = 1002.7 高级功能
数据验证
from openpyxl.worksheet.datavalidation import DataValidation# 创建数据验证dv = DataValidation(type=\"list\", formula1=\'\"男,女\"\', allow_blank=True)dv.add(\'A1:A10\') # 应用到A1:A10范围ws.add_data_validation(dv)# 数字范围验证dv_num = DataValidation(type=\"whole\", operator=\"between\", formula1=1, formula2=100)dv_num.error = \"输入必须在1到100之间\"dv_num.add(\'B1:B10\')ws.add_data_validation(dv_num)条件格式
from openpyxl.formatting.rule import ColorScaleRule, FormulaRule# 色阶条件格式color_scale_rule = ColorScaleRule(start_type=\'min\', start_color=\'FF0000\', mid_type=\'percentile\', mid_value=50, mid_color=\'FFFF00\', end_type=\'max\', end_color=\'00FF00\')ws.conditional_formatting.add(\'C1:C10\', color_scale_rule)# 公式条件格式formula_rule = FormulaRule(formula=[\'ISBLANK(C1)\'], stopIfTrue=True, font=Font(color=\'FF0000\'))ws.conditional_formatting.add(\'C1:C10\', formula_rule)保护工作表
# 保护工作表ws.protection.sheet = Truews.protection.password = \'password\'ws.protection.enable()# 解锁特定单元格for row in ws.iter_rows(min_row=1, max_row=5): for cell in row: cell.protection = Protection(locked=False)冻结窗格
# 冻结第一行和第一列ws.freeze_panes = \'B2\'# 解冻ws.freeze_panes = None三、pandas基础与核心功能
3.1 pandas简介与安装
pandas是一个强大的开源数据分析和操作库，提供了高性能、易用的数据结构和数据分析工具。它特别适合处理表格数据（如Excel文件）和时间序列数据。
安装pandas及其依赖：
pip install pandas openpyxl xlrd注意：xlrd库用于读取旧版Excel文件(.xls)，而openpyxl则用于处理.xlsx文件。
3.2 DataFrame基础
DataFrame是pandas的核心数据结构，可以看作是一个二维表格，类似于Excel工作表。
创建DataFrame
import pandas as pd# 从字典创建data = { \'姓名\': [\'张三\', \'李四\', \'王五\'], \'年龄\': [25, 30, 35], \'城市\': [\'北京\', \'上海\', \'广州\']}df = pd.DataFrame(data)# 从列表创建data = [ {\'姓名\': \'张三\', \'年龄\': 25, \'城市\': \'北京\'}, {\'姓名\': \'李四\', \'年龄\': 30, \'城市\': \'上海\'}, {\'姓名\': \'王五\', \'年龄\': 35, \'城市\': \'广州\'}]df = pd.DataFrame(data)# 显示DataFrameprint(df)基本属性
# 查看前几行print(df.head(2))# 查看后几行print(df.tail(1))# 查看形状print(df.shape) # 输出: (3, 3)# 查看列名print(df.columns) # 输出: Index([\'姓名\', \'年龄\', \'城市\'], dtype=\'object\')# 查看索引print(df.index) # 输出: RangeIndex(start=0, stop=3, step=1)# 查看数据类型print(df.dtypes)数据选择
# 选择列print(df[\'姓名\']) # 选择单列print(df[[\'姓名\', \'年龄\']]) # 选择多列# 选择行print(df.iloc[0]) # 通过位置选择print(df.loc[0]) # 通过索引选择print(df[1:3]) # 切片选择# 条件选择print(df[df[\'年龄\'] > 28])print(df[(df[\'年龄\'] > 25) & (df[\'城市\'] == \'上海\')])数据修改
# 添加列df[\'性别\'] = [\'男\', \'女\', \'男\']# 修改值df.loc[0, \'年龄\'] = 26df[\'年龄\'] = df[\'年龄\'] + 1# 删除列df = df.drop(\'性别\', axis=1)# 删除行df = df.drop(0, axis=0)3.3 数据导入与导出
读取Excel文件
# 读取整个Excel文件df = pd.read_excel(\'data.xlsx\', sheet_name=\'Sheet1\')# 读取指定范围df = pd.read_excel(\'data.xlsx\', sheet_name=\'Sheet1\', usecols=\'A:C\', nrows=10)# 读取多个工作表with pd.ExcelFile(\'data.xlsx\') as xls: df1 = pd.read_excel(xls, \'Sheet1\') df2 = pd.read_excel(xls, \'Sheet2\')# 处理缺失值df = pd.read_excel(\'data.xlsx\', na_values=[\'NA\', \'N/A\', \'缺失\'])写入Excel文件
# 写入单个DataFramedf.to_excel(\'output.xlsx\', sheet_name=\'数据\', index=False)# 写入多个DataFramewith pd.ExcelWriter(\'output.xlsx\') as writer: df1.to_excel(writer, sheet_name=\'Sheet1\') df2.to_excel(writer, sheet_name=\'Sheet2\') # 追加模式写入with pd.ExcelWriter(\'output.xlsx\', mode=\'a\') as writer: df3.to_excel(writer, sheet_name=\'Sheet3\')# 设置格式df.to_excel(\'output.xlsx\', sheet_name=\'数据\', index=False, float_format=\"%.2f\", # 浮点数格式 freeze_panes=(1, 0)) # 冻结首行其他格式支持
# CSV文件df.to_csv(\'data.csv\', index=False)df = pd.read_csv(\'data.csv\')# JSONdf.to_json(\'data.json\', orient=\'records\')df = pd.read_json(\'data.json\')# SQL数据库from sqlalchemy import create_engineengine = create_engine(\'sqlite:///data.db\')df.to_sql(\'table_name\', engine, if_exists=\'replace\')df = pd.read_sql(\'SELECT * FROM table_name\', engine)3.4 数据清洗与预处理
处理缺失值
# 检测缺失值print(df.isnull().sum())# 删除缺失值df_cleaned = df.dropna() # 删除任何包含缺失值的行df_cleaned = df.dropna(subset=[\'年龄\']) # 只删除年龄列有缺失的行# 填充缺失值df_filled = df.fillna(0) # 用0填充df_filled = df.fillna(df.mean()) # 用均值填充数值列df_filled = df.fillna(method=\'ffill\') # 用前一个值填充处理重复值
# 检测重复行print(df.duplicated().sum())# 删除重复行df_unique = df.drop_duplicates()# 基于某些列删除重复df_unique = df.drop_duplicates(subset=[\'姓名\', \'城市\'])数据类型转换
# 查看数据类型print(df.dtypes)# 转换数据类型df[\'年龄\'] = df[\'年龄\'].astype(\'float64\')df[\'日期\'] = pd.to_datetime(df[\'日期\'])# 分类数据df[\'城市\'] = df[\'城市\'].astype(\'category\')字符串操作
# 字符串方法df[\'姓名\'] = df[\'姓名\'].str.upper() # 转为大写df[\'城市\'] = df[\'城市\'].str.replace(\'京\', \'都\') # 替换# 提取信息df[\'姓氏\'] = df[\'姓名\'].str[0] # 提取第一个字符df[\'名字\'] = df[\'姓名\'].str[1:] # 提取第二个字符之后# 拆分列df[[\'姓\', \'名\']] = df[\'姓名\'].str.split(expand=True)日期处理
# 解析日期df[\'日期\'] = pd.to_datetime(df[\'日期\'], format=\'%Y-%m-%d\')# 提取日期部分df[\'年\'] = df[\'日期\'].dt.yeardf[\'月\'] = df[\'日期\'].dt.monthdf[\'日\'] = df[\'日期\'].dt.daydf[\'星期\'] = df[\'日期\'].dt.day_name()# 日期运算df[\'年龄天数\'] = (pd.to_datetime(\'today\') - df[\'出生日期\']).dt.daysdf[\'年龄\'] = df[\'年龄天数\'] // 3653.5 数据转换与计算
应用函数
# 应用简单函数df[\'年龄加10\'] = df[\'年龄\'].apply(lambda x: x + 10)# 应用复杂函数def age_group(age): if age < 20: return \'少年\' elif age < 40: return \'青年\' else: return \'中年\' df[\'年龄段\'] = df[\'年龄\'].apply(age_group)# 向量化操作df[\'BMI\'] = df[\'体重\'] / (df[\'身高\']/100)**2分组聚合
# 基本分组grouped = df.groupby(\'城市\')# 聚合函数print(grouped[\'年龄\'].mean()) # 每个城市的平均年龄print(grouped.agg({\'年龄\': [\'mean\', \'min\', \'max\'], \'收入\': \'sum\'}))# 多级分组grouped = df.groupby([\'城市\', \'性别\'])print(grouped[\'年龄\'].mean())数据透视表
# 简单透视表pivot = pd.pivot_table(df, values=\'年龄\', index=\'城市\', aggfunc=\'mean\')# 复杂透视表pivot = pd.pivot_table(df, values=[\'年龄\', \'收入\'],index=[\'城市\', \'性别\'],columns=[\'教育程度\'],aggfunc={\'年龄\': \'mean\', \'收入\': [\'sum\', \'count\']},fill_value=0,margins=True)合并与连接
# 合并两个DataFramedf1 = pd.DataFrame({\'key\': [\'A\', \'B\', \'C\'], \'value\': [1, 2, 3]})df2 = pd.DataFrame({\'key\': [\'A\', \'B\', \'D\'], \'value\': [4, 5, 6]})# 内连接pd.merge(df1, df2, on=\'key\', how=\'inner\')# 左连接pd.merge(df1, df2, on=\'key\', how=\'left\')# 外连接pd.merge(df1, df2, on=\'key\', how=\'outer\')# 纵向合并pd.concat([df1, df2], axis=0)3.6 高级数据分析功能
时间序列分析
# 创建时间序列date_rng = pd.date_range(start=\'1/1/2020\', end=\'1/10/2020\', freq=\'D\')df = pd.DataFrame(date_rng, columns=[\'date\'])df[\'data\'] = np.random.randint(0,100,size=(len(date_rng)))# 设置为索引df = df.set_index(\'date\')# 重采样df.resample(\'W\').mean() # 按周平均df.resample(\'M\').sum() # 按月求和# 滚动窗口df.rolling(window=3).mean() # 3天移动平均统计函数
# 描述性统计print(df.describe())# 相关性print(df.corr())# 协方差print(df.cov())# 唯一值计数print(df[\'城市\'].value_counts())# 交叉表pd.crosstab(df[\'城市\'], df[\'性别\'])可视化
import matplotlib.pyplot as plt# 线图df.plot.line()# 柱状图df.plot.bar()# 直方图df[\'年龄\'].plot.hist(bins=20)# 箱线图df.plot.box()# 散点图df.plot.scatter(x=\'年龄\', y=\'收入\')plt.show()四、openpyxl与pandas的协同使用
4.1 结合使用的优势与场景
openpyxl和pandas各有优势，在实际工作中结合使用可以发挥更大威力：
-
数据流转：pandas适合数据处理和分析，openpyxl适合精细控制Excel格式，两者结合可以实现\"pandas处理数据→openpyxl调整格式\"的完整流程。
-
复杂报表：对于需要复杂格式的报表，可以先用pandas生成数据，再用openpyxl添加图表、条件格式等高级功能。
-
模板填充：使用Excel文件作为模板，pandas填充数据，openpyxl保持原有格式和公式。
-
大数据处理：当Excel文件很大时，可以先用pandas高效读取和处理数据，再用openpyxl进行必要的格式调整。
4.2 pandas DataFrame与openpyxl的转换
DataFrame写入Excel并保持格式
from openpyxl import load_workbookfrom openpyxl.utils.dataframe import dataframe_to_rows# 创建DataFramedf = pd.DataFrame({ \'产品\': [\'A\', \'B\', \'C\', \'D\'], \'销量\': [120, 150, 90, 200], \'单价\': [25.5, 32.0, 18.0, 40.5]})# 加载现有工作簿或创建新工作簿try: wb = load_workbook(\'report.xlsx\')except FileNotFoundError: wb = Workbook()# 选择或创建工作表if \'销售报告\' in wb.sheetnames: ws = wb[\'销售报告\']else: ws = wb.create_sheet(\'销售报告\')# 清空现有内容ws.delete_rows(1, ws.max_row)# 写入DataFrame数据for r in dataframe_to_rows(df, index=False, header=True): ws.append(r)# 添加公式ws[\'E1\'] = \'销售额\'for row in range(2, ws.max_row + 1): ws[f\'E{row}\'] = f\'=B{row}*C{row}\'# 设置格式header_font = Font(bold=True, color=\'FFFFFF\')header_fill = PatternFill(start_color=\'4F81BD\', end_color=\'4F81BD\', fill_type=\'solid\')for cell in ws[1]: cell.font = header_font cell.fill = header_fill# 保存工作簿wb.save(\'report.xlsx\')从openpyxl读取数据到DataFrame
from openpyxl import load_workbook# 加载工作簿wb = load_workbook(\'data.xlsx\')ws = wb.active# 将工作表数据转换为列表data = []for row in ws.iter_rows(values_only=True): data.append(row)# 转换为DataFramedf = pd.DataFrame(data[1:], columns=data[0])# 处理数据df[\'日期\'] = pd.to_datetime(df[\'日期\'])df[\'销售额\'] = df[\'数量\'] * df[\'单价\']print(df.head())4.3 复杂报表生成案例
import pandas as pdfrom openpyxl import Workbookfrom openpyxl.styles import Font, Alignment, Border, Side, PatternFillfrom openpyxl.chart import BarChart, Referencefrom openpyxl.drawing.image import Imagefrom datetime import datetime# 1. 准备数据sales_data = { \'月份\': [\'1月\', \'2月\', \'3月\', \'4月\', \'5月\', \'6月\'], \'产品A\': [1200, 1500, 1800, 2100, 2400, 2700], \'产品B\': [800, 950, 1100, 1250, 1400, 1550], \'产品C\': [500, 600, 700, 800, 900, 1000]}df = pd.DataFrame(sales_data)# 2. 创建Excel工作簿wb = Workbook()ws = wb.activews.title = \"销售报告\"# 3. 写入标题ws[\'A1\'] = \"2023年上半年销售报告\"ws[\'A1\'].font = Font(size=16, bold=True, name=\'微软雅黑\')ws.merge_cells(\'A1:D1\')# 4. 写入数据# 写入列标题columns = list(df.columns)for col_num, column_title in enumerate(columns, 1): cell = ws.cell(row=3, column=col_num, value=column_title) cell.font = Font(bold=True) cell.alignment = Alignment(horizontal=\'center\')# 写入数据for row_num, row_data in enumerate(df.values, 4): for col_num, cell_value in enumerate(row_data, 1): ws.cell(row=row_num, column=col_num, value=cell_value)# 5. 添加汇总行last_row = ws.max_row + 1ws.cell(row=last_row, column=1, value=\"总计\").font = Font(bold=True)for col_num in range(2, 5): col_letter = chr(64 + col_num) ws.cell(row=last_row, column=col_num, value=f\"=SUM({col_letter}4:{col_letter}{last_row-1})\")# 6. 设置格式# 设置边框thin_border = Border(left=Side(style=\'thin\'),right=Side(style=\'thin\'), top=Side(style=\'thin\'), bottom=Side(style=\'thin\'))for row in ws.iter_rows(min_row=3, max_row=last_row, min_col=1, max_col=4): for cell in row: cell.border = thin_border if cell.row > 3 and cell.column > 1: # 数据单元格 cell.number_format = \'#,##0\'# 设置对齐for row in ws.iter_rows(min_row=3, max_row=last_row, min_col=1, max_col=4): for cell in row: cell.alignment = Alignment(horizontal=\'center\')# 7. 创建图表chart = BarChart()chart.type = \"col\"chart.style = 10chart.title = \"产品销售趋势\"chart.y_axis.title = \"销售额\"chart.x_axis.title = \"月份\"data = Reference(ws, min_col=2, max_col=4, min_row=3, max_row=last_row-1)categories = Reference(ws, min_col=1, min_row=4, max_row=last_row-1)chart.add_data(data, titles_from_data=True)chart.set_categories(categories)ws.add_chart(chart, \"F3\")# 8. 添加页脚footer_row = last_row + 2ws.cell(row=footer_row, column=1, value=f\"报告生成时间: {datetime.now().strftime(\'%Y-%m-%d %H:%M:%S\')}\")# 9. 调整列宽ws.column_dimensions[\'A\'].width = 12ws.column_dimensions[\'B\'].width = 12ws.column_dimensions[\'C\'].width = 12ws.column_dimensions[\'D\'].width = 12# 10. 保存文件filename = f\"销售报告_{datetime.now().strftime(\'%Y%m%d\')}.xlsx\"wb.save(filename)print(f\"报表已生成: {filename}\")4.4 模板填充技术
from openpyxl import load_workbookfrom openpyxl.utils.dataframe import dataframe_to_rowsimport pandas as pd# 1. 加载模板文件template_path = \'report_template.xlsx\'wb = load_workbook(template_path)ws = wb[\'Data\']# 2. 准备数据data = { \'Region\': [\'North\', \'South\', \'East\', \'West\'], \'Q1\': [1200, 1500, 1800, 900], \'Q2\': [1300, 1600, 1900, 950], \'Q3\': [1400, 1700, 2000, 1000], \'Q4\': [1500, 1800, 2100, 1050]}df = pd.DataFrame(data)# 3. 清空模板中的数据区域 (保留格式)for row in ws.iter_rows(min_row=3, max_row=100, min_col=1, max_col=6): for cell in row: cell.value = None# 4. 写入新数据for r_idx, row in enumerate(dataframe_to_rows(df, index=False, header=False), 3): for c_idx, value in enumerate(row, 1): ws.cell(row=r_idx, column=c_idx, value=value)# 5. 更新公式 (假设模板中已有公式)last_data_row = 3 + len(df) - 1for row in range(3, last_data_row + 1): ws[f\'F{row}\'] = f\'=SUM(B{row}:E{row})\'# 6. 更新汇总公式ws[\'B20\'] = f\'=SUM(B3:B{last_data_row})\'ws[\'C20\'] = f\'=SUM(C3:C{last_data_row})\'ws[\'D20\'] = f\'=SUM(D3:D{last_data_row})\'ws[\'E20\'] = f\'=SUM(E3:E{last_data_row})\'ws[\'F20\'] = f\'=SUM(F3:F{last_data_row})\'# 7. 更新报告日期ws[\'A1\'] = f\"销售报告 - {pd.Timestamp.today().strftime(\'%Y-%m-%d\')}\"# 8. 保存新文件output_path = \'quarterly_report.xlsx\'wb.save(output_path)print(f\"报告已生成: {output_path}\")4.5 性能优化技巧
当处理大型Excel文件时，性能可能成为问题。以下是一些优化技巧：
- 只读模式：如果只需要读取数据而不修改文件，使用只读模式可以显著提高速度。
wb = load_workbook(\'large_file.xlsx\', read_only=True)- 只写模式：如果只需要写入大量数据而不读取现有内容，使用只写模式。
wb = Workbook(write_only=True)ws = wb.create_sheet()for row in data: ws.append(row)-
批量操作：尽量减少单个单元格操作，使用批量写入方法。
-
禁用计算：在写入大量公式时，临时禁用自动计算。
wb = load_workbook(\'file.xlsx\', data_only=False)wb.calculation = False# ... 写入公式 ...wb.calculation = Truewb.save(\'file.xlsx\')-
使用pandas处理大数据：对于数据操作，优先使用pandas，它比openpyxl的单元格操作高效得多。
-
内存优化：处理完数据后及时删除不需要的变量，特别是大型DataFrame和工作簿对象。
del large_dfdel wb五、实战应用案例
5.1 财务报表自动化
import pandas as pdfrom openpyxl import load_workbookfrom openpyxl.styles import Font, Alignment, numbersfrom datetime import datetimedef generate_financial_report(input_path, output_path): # 1. 使用pandas读取和处理数据 df = pd.read_excel(input_path, sheet_name=\'Transactions\') # 数据清洗 df = df.dropna(subset=[\'Amount\']) df[\'Date\'] = pd.to_datetime(df[\'Date\']) df[\'Month\'] = df[\'Date\'].dt.to_period(\'M\') # 分类汇总 income = df[df[\'Type\'] == \'Income\'].groupby(\'Month\')[\'Amount\'].sum() expense = df[df[\'Type\'] == \'Expense\'].groupby(\'Month\')[\'Amount\'].sum() profit = income - expense # 2. 准备报表数据 report_data = pd.DataFrame({ \'Month\': income.index.astype(str), \'Income\': income.values, \'Expense\': expense.values, \'Profit\': profit.values }) # 3. 加载模板文件 wb = load_workbook(\'financial_report_template.xlsx\') ws = wb[\'Report\'] # 4. 清空旧数据 (保留格式) for row in ws.iter_rows(min_row=5, max_row=100, min_col=1, max_col=4): for cell in row: cell.value = None # 5. 写入新数据 for i, row in report_data.iterrows(): ws.cell(row=5+i, column=1, value=row[\'Month\']) ws.cell(row=5+i, column=2, value=row[\'Income\']) ws.cell(row=5+i, column=3, value=row[\'Expense\']) ws.cell(row=5+i, column=4, value=row[\'Profit\']) # 6. 设置数字格式 for row in ws.iter_rows(min_row=5, max_row=5+len(report_data), min_col=2, max_col=4): for cell in row: cell.number_format = numbers.FORMAT_CURRENCY_USD_SIMPLE # 7. 更新汇总信息 last_row = 5 + len(report_data) - 1 ws[\'B1\'] = f\"财务报告 - {datetime.now().strftime(\'%Y-%m-%d\')}\" ws[\'B2\'] = f\"数据期间: {report_data[\'Month\'].iloc[0]} 至 {report_data[\'Month\'].iloc[-1]}\" ws[f\'B{last_row+2}\'] = \"总计:\" ws[f\'C{last_row+2}\'] = f\"=SUM(C5:C{last_row})\" ws[f\'D{last_row+2}\'] = f\"=SUM(D5:D{last_row})\" ws[f\'E{last_row+2}\'] = f\"=SUM(E5:E{last_row})\" # 8. 设置汇总行格式 for cell in ws[f\'B{last_row+2}\':f\'E{last_row+2}\'][0]: cell.font = Font(bold=True) if cell.column in [3,4,5]: cell.number_format = numbers.FORMAT_CURRENCY_USD_SIMPLE # 9. 保存报告 wb.save(output_path) print(f\"财务报表已生成: {output_path}\")# 使用示例generate_financial_report(\'transactions.xlsx\', \'financial_report_Q1.xlsx\')
