Python深度挖掘:openpyxl和pandas的使用详细

Python 深度挖掘：openpyxl 和 pandas 的使用详细指南（2026 最新版）

openpyxl和pandas是 Python 数据处理领域的两大核心库，尤其在处理 Excel 文件和数据分析时常结合使用。

• openpyxl：专注于 Excel 文件（.xlsx）的读写和操作，是纯 Python 实现，无需安装 Excel 软件。常用于自动化报表、数据导出等场景。
• pandas：强大的数据分析库，以 DataFrame 为核心，处理结构化数据（如表格）。它可以无缝集成 openpyxl 来读写 Excel。

截至 2026 年，openpyxl 最新版为 3.2.x，pandas 为 2.3.x。两者结合使用，能高效处理大数据量的 Excel 操作。

本文从安装、基础使用、进阶功能、结合案例一步步深度讲解。所有代码基于 Python 3.10+ 测试，确保可运行。

一、安装与环境准备

pipinstallopenpyxl pandas# 如果需要绘图（pandas 常用）pipinstallmatplotlib seaborn

• 依赖：openpyxl 无外部依赖；pandas 依赖 NumPy（自动安装）。
• 测试安装：

  importopenpyxlimportpandasaspdprint(openpyxl.__version__)# 如 3.2.0print(pd.__version__)# 如 2.3.0

二、openpyxl 详细使用（Excel 操作专家）

openpyxl 提供对 Excel 的完整控制，包括工作簿、工作表、单元格、公式、样式、图表等。

1. 基础概念

• Workbook：Excel 文件（.xlsx）。
• Worksheet：工作表（Sheet）。
• Cell：单元格。

2. 创建与保存 Excel 文件

fromopenpyxlimportWorkbook# 创建新工作簿wb=Workbook()# 获取默认工作表（Sheet）ws=wb.active ws.title="MySheet"# 重命名# 写入数据ws['A1']="姓名"# 单元格 A1ws['B1']="年龄"ws.cell(row=2,column=1,value="张三")# 行2，列1ws.cell(row=2,column=2,value=30)# 追加行ws.append(["李四",25])# 保存文件wb.save("example.xlsx")

3. 读取 Excel 文件

fromopenpyxlimportload_workbook# 加载文件（只读模式提高性能）wb=load_workbook("example.xlsx",read_only=True)# 获取指定工作表ws=wb["MySheet"]# 读取单元格print(ws['A1'].value)# "姓名"# 迭代行forrowinws.iter_rows(min_row=2,max_row=3,min_col=1,max_col=2,values_only=True):print(row)# 输出如：('张三', 30)# 获取维度print(ws.dimensions)# 如 "A1:B3"

• 进阶读取：iter_rows(values_only=True)返回元组，提高效率；read_only=True适合大文件。

4. 公式与计算

ws['C1']="总分"ws['C2']="=SUM(B2:B3)"# 公式会自动计算wb.save("example_with_formula.xlsx")

• 加载后，公式值可通过data_only=True获取计算结果（需先用 Excel 打开保存一次）。

5. 样式与格式

fromopenpyxl.stylesimportFont,PatternFill,Alignment,Border,Side# 字体：加粗、红色ws['A1'].font=Font(bold=True,color="FF0000")# 填充：黄色背景ws['B1'].fill=PatternFill(start_color="FFFF00",end_color="FFFF00",fill_type="solid")# 对齐：居中ws['C1'].alignment=Alignment(horizontal="center",vertical="center")# 边框：细线thin=Side(border_style="thin",color="000000")ws['A1'].border=Border(top=thin,left=thin,right=thin,bottom=thin)# 列宽/行高ws.column_dimensions['A'].width=15ws.row_dimensions[1].height=20wb.save("styled.xlsx")

6. 图表插入

fromopenpyxl.chartimportBarChart,Reference# 数据准备ws.append(["产品","销量"])ws.append(["苹果",50])ws.append(["香蕉",30])# 创建柱状图chart=BarChart()data=Reference(ws,min_col=2,min_row=2,max_row=3)categories=Reference(ws,min_col=1,min_row=2,max_row=3)chart.add_data(data)chart.set_categories(categories)chart.title="销量图"ws.add_chart(chart,"D1")# 放置位置wb.save("chart.xlsx")

7. 进阶功能：合并单元格、冻结窗格、过滤器

# 合并单元格ws.merge_cells('A1:B1')# 冻结首行ws.freeze_panes='A2'# 添加过滤器ws.auto_filter.ref="A1:B3"wb.save("advanced.xlsx")

8. 处理大文件（性能优化）

• 用read_only=True和write_only=True模式。
• 批量操作：使用ws.append()而非逐个单元格写入。
• 对于超大文件（>10万行），考虑结合 pandas。

三、pandas 详细使用（数据分析神器）

pandas 以DataFrame（表格）和Series（列）为核心，擅长数据清洗、转换、分析、可视化。

1. 基础概念

• DataFrame：二维表格，带标签的行/列。
• Series：一维数组，带标签。

2. 创建 DataFrame

importpandasaspdimportnumpyasnp# 从字典创建data={'姓名':['张三','李四','王五'],'年龄':[30,25,28],'分数':[85.5,92.0,78.5]}df=pd.DataFrame(data)# 从列表创建df2=pd.DataFrame(np.random.rand(3,2),columns=['A','B'],index=['row1','row2','row3'])print(df)# 输出：# 姓名 年龄 分数# 0 张三 30 85.5# 1 李四 25 92.0# 2 王五 28 78.5

3. 数据访问与操作

• 访问：

  print(df['姓名'])# Series: 张三 李四 王五print(df.loc[0])# 第一行print(df.iloc[:,1:3])# 列1到2（年龄、分数）print(df[df['年龄']>25])# 过滤：年龄 > 25 的行

• 修改：

  df['奖金']=df['分数']*10# 新列：分数 * 10df.loc[0,'年龄']=31# 修改特定值df=df.drop('奖金',axis=1)# 删除列

• 统计：

  print(df.describe())# 均值、标准差等print(df['分数'].mean())# 平均分

4. 数据清洗与转换

• 缺失值：

  df.loc[1,'分数']=np.nan df=df.fillna(0)# 填 0df=df.dropna()# 删含 NaN 行

• 分组聚合：

  grouped=df.groupby('年龄').agg({'分数':'mean'})# 按年龄分组，求分数平均

• 合并：

  df_other=pd.DataFrame({'姓名':['张三','赵六'],'城市':['北京','上海']})merged=pd.merge(df,df_other,on='姓名',how='left')# 左连接

5. 读写文件（与 openpyxl 集成）

pandas 默认用 openpyxl 作为 Excel 引擎（engine=‘openpyxl’）。

• 读 Excel：

  df=pd.read_excel("example.xlsx",sheet_name="MySheet",engine="openpyxl")

• 写 Excel：

  df.to_excel("output.xlsx",sheet_name="Data",index=False,engine="openpyxl")# 多工作表写入withpd.ExcelWriter("multi.xlsx",engine="openpyxl")aswriter:df.to_excel(writer,sheet_name="Sheet1")df2.to_excel(writer,sheet_name="Sheet2")

• 大文件优化：用pd.read_excel(usecols=['A:B'], nrows=1000)指定列/行。

6. 可视化

df.plot(kind='bar',x='姓名',y='分数')# 柱状图df['分数'].hist(bins=10)# 直方图

• 集成 Seaborn/Matplotlib 更美观。

7. 进阶：时间序列、透视表、多索引

• 时间序列：

  dates=pd.date_range('20260101',periods=5)ts_df=pd.DataFrame(np.random.randn(5),index=dates)ts_df.resample('D').mean()# 按天重采样

• 透视表：

  pivot=pd.pivot_table(df,values='分数',index='姓名',aggfunc='mean')

四、openpyxl 与 pandas 结合实战案例

场景：读取 Excel 数据，用 pandas 分析后，写入新 Excel 并添加样式/图表。

importpandasaspdfromopenpyxlimportload_workbookfromopenpyxl.chartimportLineChart,Reference# 1. 用 pandas 读入df=pd.read_excel("input.xlsx",engine="openpyxl")# 2. pandas 分析df['总分']=df['数学']+df['英语']summary=df.describe()# 3. 写入新文件（pandas 先写）df.to_excel("output.xlsx",index=False,engine="openpyxl")summary.to_excel("output.xlsx",sheet_name="Summary",engine="openpyxl")# 4. 用 openpyxl 添加样式和图表wb=load_workbook("output.xlsx")ws=wb.active# 默认 Sheet# 添加样式forcellinws["A1":"D1"]:forcincell:c.font=Font(bold=True)# 添加图表chart=LineChart()data=Reference(ws,min_col=2,min_row=1,max_col=4,max_row=len(df)+1)chart.add_data(data,titles_from_data=True)ws.add_chart(chart,"F1")wb.save("output.xlsx")

优势：pandas 负责数据逻辑，openpyxl 负责 Excel 细节（如样式、图表）。

五、性能优化与常见坑

• 大文件：pandas 用pd.read_excel(chunksize=10000)分块读；openpyxl 用 write_only 模式。
• 常见坑：
  • openpyxl 不支持 .xls（旧格式），用 xlrd 或 pandas 转换。
  • pandas 读 Excel 时，指定dtype避免类型推断错误。
  • 公式：pandas 写公式需用=SUM(...)，但计算需加载 data_only。
• 资源消耗：pandas DataFrame 内存大，用df.info()检查；openpyxl 适合逐行处理。

六、总结与进阶建议

• openpyxl：适合纯 Excel 操作，深度定制样式/图表。
• pandas：数据分析主力，结合 openpyxl 读写 Excel 更强大。
• 结合使用：80% 场景 pandas 足够，复杂样式再用 openpyxl 后处理。

进阶资源：

• 官方文档：openpyxl.readthedocs.io / pandas.pydata.org
• 书籍：《Python 数据分析》（Wes McKinney）
• 社区：Stack Overflow / Pandas GitHub

如果需要特定案例代码（如批量处理 Excel、自动化报表、机器学习数据准备），或运行示例输出，告诉我，我可以进一步扩展！