【全网最全】50个Python处理Excel示例代码，覆盖95%日常使用场景

前言

上篇文章主讲的是在不同场景下如何选择合适的Python工具来处理Excel文件，起因是女友单位的OA系统导出来的Excel格式太拉，她跟OA的技术人员磨了半天嘴皮子也没给她解决。她回来一肚子气自然的就撒到了我头上[坚强的微笑]，我用Python几分钟就帮她解决。于是决定写一些Python相关的内容。

为了彻底解决这个问题，不吃这种二手气，必须让她快速掌握Python来处理Excel文档，想着找些例子让她练练手，网上找了一圈硬是没找到足够全面合适的，于是花了一整天整理了50个Python处理Excel的示例。每个示例都写了详细注释，并逐一运行确保无误，且附带了运行效果图。

---

准备工作

首先，请确保你的Python版本是3系列的（我的版本是3.12.7，Python的安装这里就不赘述了）。确保已安装了必要的库：

# 或者用 pip3 install pandas openpyxl xlsxwriter xlrd xlwt xlwings tablib numpy
pip install pandas openpyxl xlsxwriter xlrd xlwt xlwings tablib numpy

然后，创建一个demo.py文件，把下面的示例代码复制粘贴进去运行即可。

# 或者用 python3 demo.py 
python demo.py 

---

功能分类

• 基础操作 (1-10): 读写、格式处理、大文件处理
• 样式美化 (11-20): 字体、颜色、边框、保护等
• 数据分析 (21-35): 筛选、排序、分组、透视表等
• 图表可视化 (36-42): 各类图表创建和自定义
• 高级功能 (43-50): 批处理、验证、公式、性能优化

这50个示例涵盖了Python处理Excel的各种常见场景：

---

基础操作 (1-10)

1. 创建并写入新的Excel文件

使用 openpyxl 创建一个全新的Excel工作簿，并向默认工作表中写入数据。

import openpyxl

# 创建一个新的工作簿
wb = openpyxl.Workbook()

# 获取活动的工作表
sheet = wb.active
sheet.title = "基础信息"

# 写入数据到单元格
sheet['A1'] = '姓名'
sheet['B1'] = '年龄'
sheet.cell(row=2, column=1, value='小虫')
sheet.cell(row=2, column=2, value=25)

# 保存Excel文件
wb.save("openpyxl_new.xlsx")

print("文件 'openpyxl_new.xlsx' 创建成功。")

2. 读取现有Excel文件的数据

使用 openpyxl 打开一个已存在的Excel文件，并读取指定单元格的数据。

import openpyxl

# 准备一个用于读取的Excel文件
# (您可以先运行上一个示例来创建此文件)
try:
    # 加载已存在的工作簿
    wb = openpyxl.load_workbook("openpyxl_new.xlsx")

    # 选择要操作的工作表
    sheet = wb["基础信息"]

    # 读取单元格数据
    name = sheet['A2'].value
    age = sheet['B2'].value

    print(f"从 'openpyxl_new.xlsx' 中读取到数据: 姓名={name}, 年龄={age}")

except FileNotFoundError:
    print("错误: 'openpyxl_new.xlsx' 文件未找到。请先运行上一个示例创建它。")

3. 向现有Excel文件追加数据

加载一个Excel文件，并在工作表的末尾添加新的数据行。

import openpyxl

# 假设 'append_sheet.xlsx' 已存在且包含一些数据
# 为了示例可独立运行，我们先创建它
wb = openpyxl.Workbook()
sheet = wb.active
sheet.append(['产品', '销量'])
sheet.append(['A', 100])
wb.save('append_sheet.xlsx')

# 加载工作簿并追加数据
wb = openpyxl.load_workbook('append_sheet.xlsx')
sheet = wb.active

# 追加一行数据
new_data = ['B', 150]
sheet.append(new_data)

# 保存更改
wb.save('append_sheet.xlsx')

print("数据已成功追加到 'append_sheet.xlsx'。")

4. 使用Pandas读取Excel数据

pandas 提供了更便捷的方式将整个工作表或指定范围的数据读入一个DataFrame，便于分析。

import pandas as pd
import os

# 准备数据文件 data.xlsx
# 为了示例可独立运行，我们先创建它
data = {'姓名': ['小红', '小刚'], '分数': [95, 88]}
df_to_write = pd.DataFrame(data)
df_to_write.to_excel("data.xlsx", index=False)

# 使用pandas读取Excel文件
if os.path.exists("data.xlsx"):
    df = pd.read_excel("data.xlsx")
    print("使用Pandas读取 'data.xlsx' 的内容:")
    print(df)
else:
    print("文件 'data.xlsx' 不存在.")

5. 将Pandas DataFrame写入Excel文件

将 pandas 的DataFrame数据结构轻松导出为Excel文件，index=False 参数可以避免将DataFrame的索引写入文件。

import pandas as pd

# 创建一个DataFrame
data = {
    'ID': [1, 2, 3, 4],
    'Product': ['Apple', 'Banana', 'Cherry', 'Date'],
    'Price': [0.5, 0.3, 2.5, 1.8]
}
df = pd.DataFrame(data)

# 将DataFrame写入Excel文件
# index=False表示不把DataFrame的索引写入Excel
df.to_excel("products.xlsx", sheet_name="ProductList", index=False)

print("DataFrame已成功写入 'products.xlsx'。")

6. 读取Excel文件中的特定工作表

当一个Excel文件包含多个工作表时，可以指定工作表的名称或索引来读取。

import pandas as pd

# 创建一个包含多个工作表的Excel文件
with pd.ExcelWriter('multi_sheet_data.xlsx') as writer:
    pd.DataFrame({'col1': [1, 2], 'col2': [3, 4]}).to_excel(writer, sheet_name='SheetA', index=False)
    pd.DataFrame({'colA': [5, 6], 'colB': [7, 8]}).to_excel(writer, sheet_name='SheetB', index=False)

# 通过名称读取第二个工作表 'SheetB'
df_b = pd.read_excel('multi_sheet_data.xlsx', sheet_name='SheetB')
print("读取工作表 'SheetB' 的内容:")
print(df_b)

# 通过索引读取第一个工作表 (索引从0开始)
df_a = pd.read_excel('multi_sheet_data.xlsx', sheet_name=0)
print("\n读取第一个工作表 (索引0) 的内容:")
print(df_a)

7. 遍历工作表中的行和列

使用 openpyxl 可以方便地逐行或逐列遍历数据，适用于需要对每个单元格进行处理的场景。

import openpyxl

# 准备数据文件
wb = openpyxl.Workbook()
sheet = wb.active
for i in range(1, 6):
    sheet.append([f'Row{i}ColA', f'Row{i}ColB', f'Row{i}ColC'])
wb.save('iteration_data.xlsx')

# 加载工作簿
wb = openpyxl.load_workbook('iteration_data.xlsx')
sheet = wb.active

# 遍历行
print("遍历所有行:")
for row in sheet.iter_rows(values_only=True):
    print(row)

# 遍历列
print("\n遍历第一列:")
for cell in sheet['A']:
    print(cell.value)

8. 获取工作簿中所有工作表的名称

快速列出Excel文件中所有的工作表名称。

import openpyxl

# 使用上一个示例创建的文件 'multi_sheet_data.xlsx'
# 如果文件不存在，则创建一个
try:
    wb = openpyxl.load_workbook('multi_sheet_data.xlsx')
except FileNotFoundError:
    wb = openpyxl.Workbook()
    wb.create_sheet("SheetA")
    wb.create_sheet("SheetB")
    wb.save('multi_sheet_data.xlsx')

# 获取所有工作表名称
sheet_names = wb.sheetnames
print(f"'multi_sheet_data.xlsx' 中的工作表有: {sheet_names}")

9. 在工作簿中创建新的工作表

在现有工作簿中动态添加新的工作表，并可以指定其名称和位置。

import openpyxl

# 加载或创建工作簿
try:
    wb = openpyxl.load_workbook('workbook_with_new_sheets.xlsx')
except FileNotFoundError:
    wb = openpyxl.Workbook()

# 创建一个名为 'NewSheet' 的新工作表
if 'NewSheet' not in wb.sheetnames:
    new_sheet = wb.create_sheet(title="NewSheet")
    new_sheet['A1'] = '这是新的工作表'

# 在指定位置（第一个）创建工作表
if 'FirstSheet' not in wb.sheetnames:
    first_sheet = wb.create_sheet(title="FirstSheet", index=0)
    first_sheet['A1'] = '这是第一个工作表'

wb.save('workbook_with_new_sheets.xlsx')
print("新的工作表已创建并保存到 'workbook_with_new_sheets.xlsx'。")
print(f"当前所有工作表: {wb.sheetnames}")

10. 处理大型Excel文件（只读模式）

当处理非常大的Excel文件时，为了节省内存，可以使用 openpyxl 的只读模式。这会以流的方式读取数据，而不会将整个文件加载到内存中。

import openpyxl

# 创建一个模拟的大型文件
print("正在创建一个模拟的大型文件 'large_file.xlsx'...")
wb = openpyxl.Workbook()
sheet = wb.active
# 写入10万行数据
for i in range(1, 100001):
    sheet.append([f'ID_{i}', i*10, f'Data-{i}'])
wb.save('large_file.xlsx')
print("大型文件创建完毕。")

# 使用只读模式高效读取
wb_read = openpyxl.load_workbook('large_file.xlsx', read_only=True)
sheet_read = wb_read.active

print("\n以只读模式开始处理大型文件...")
row_count = 0
# iter_rows() 在只读模式下是高效的
for row in sheet_read.iter_rows():
    row_count += 1
    # 这里可以添加处理每一行数据的逻辑
    # 为了演示，我们只打印前5行和最后5行
    if row_count <= 5 or row_count > 99995:
        print(f"读取行 {row_count}: {[cell.value for cell in row]}")

print(f"...处理完成，共读取 {row_count} 行。")

---

样式美化 (11-20)

11. 设置单元格字体样式（字体、大小、粗体、斜体）

使用 openpyxl.styles.Font 来定义并应用字体样式到单元格。

import openpyxl
from openpyxl.styles import Font

wb = openpyxl.Workbook()
sheet = wb.active

# 定义不同的字体样式
font_bold = Font(name='Calibri', size=12, bold=True)
font_italic_color = Font(name='Arial', size=10, italic=True, color='FF0000') # 红色

# 应用样式
sheet['A1'].value = '粗体文本'
sheet['A1'].font = font_bold

sheet['B2'].value = '红色斜体'
sheet['B2'].font = font_italic_color

wb.save('font_styles.xlsx')
print("已应用字体样式并保存到 'font_styles.xlsx'。")

12. 设置单元格背景填充颜色

使用 openpyxl.styles.PatternFill 可以为单元格设置纯色或图案填充。

import openpyxl
from openpyxl.styles import PatternFill

wb = openpyxl.Workbook()
sheet = wb.active

# 定义填充样式
fill_green = PatternFill(start_color='00FF00', end_color='00FF00', fill_type='solid')
fill_yellow = PatternFill(fill_type='solid', fgColor='FFFF00')

# 应用填充
sheet['A1'].value = '绿色背景'
sheet['A1'].fill = fill_green

sheet.cell(row=2, column=2, value='黄色背景').fill = fill_yellow

wb.save('fill_style.xlsx')
print("已应用背景填充并保存到 'fill_style.xlsx'。")

13. 设置单元格边框

通过 openpyxl.styles.Border 和 Side 对象可以精细控制单元格的每个边框。

import openpyxl
from openpyxl.styles import Border, Side

wb = openpyxl.Workbook()
sheet = wb.active

# 定义边框样式
thin_border = Border(
    left=Side(style='thin'), 
    right=Side(style='thin'), 
    top=Side(style='thin'), 
    bottom=Side(style='thin')
)
double_border_bottom = Border(bottom=Side(style='double', color='0000FF'))

# 应用边框
sheet['A1'].value = '四边框'
sheet['A1'].border = thin_border

sheet['B2'].value = '蓝色双下划线'
sheet['B2'].border = double_border_bottom

wb.save('border_styles.xlsx')
print("已应用边框样式并保存到 'border_styles.xlsx'。")

14. 设置单元格对齐方式

使用 openpyxl.styles.Alignment 控制文本在单元格内的水平和垂直对齐以及文本换行。

import openpyxl
from openpyxl.styles import Alignment

wb = openpyxl.Workbook()
sheet = wb.active

# 设置列宽和行高以便观察效果
sheet.column_dimensions['A'].width = 20
sheet.row_dimensions[1].height = 40

# 定义对齐样式
align_center_wrap = Alignment(horizontal='center', vertical='center', wrap_text=True)

# 应用对齐
sheet['A1'].value = '这是一个需要换行并居中对齐的长文本。'
sheet['A1'].alignment = align_center_wrap

wb.save('alignment_style.xlsx')
print("已应用对齐样式并保存到 'alignment_style.xlsx'。")

15. 合并与拆分单元格

merge_cells() 用于合并一个矩形区域的单元格，unmerge_cells() 用于拆分。

import openpyxl
from openpyxl.styles import Alignment, Font

wb = openpyxl.Workbook()
sheet = wb.active

# 合并单元格 A1到C1
sheet.merge_cells('A1:C1')
merged_cell = sheet['A1']
merged_cell.value = '合并后的标题'
merged_cell.alignment = Alignment(horizontal='center', vertical='center')
merged_cell.font = Font(bold=True)

# 写入一些其他数据
sheet['A2'] = '数据1'
sheet['B2'] = '数据2'
sheet['C2'] = '数据3'

# 拆分单元格 (示例)
# sheet.unmerge_cells('A1:C1')

wb.save('merge_cells.xlsx')
print("已合并单元格并保存到 'merge_cells.xlsx'。")

解开# sheet.unmerge_cells('A1:C1') 代码注释，再运行上述代码：

16. 设置行高和列宽

可以精确地调整特定行的高度和特定列的宽度。

import openpyxl

wb = openpyxl.Workbook()
sheet = wb.active

# 写入数据
sheet['A1'] = '自定义列宽'
sheet['B1'] = '自定义行高'
sheet['B2'] = '高单元格'

# 设置A列的宽度为25
sheet.column_dimensions['A'].width = 25

# 设置第2行的高度为50
sheet.row_dimensions[2].height = 50

wb.save('dimensions.xlsx')
print("已设置行高和列宽并保存到 'dimensions.xlsx'。")

17. 冻结窗格

冻结窗格可以在滚动表格时保持指定的行和列可见，便于查看大型数据集。

import openpyxl

wb = openpyxl.Workbook()
sheet = wb.active

# 冻结首行
sheet.freeze_panes = 'A2'

# 填充数据以演示滚动效果
sheet.append(['Header1', 'Header2', 'Header3'])
for i in range(1, 51):
    sheet.append([f'Data{i}-1', f'Data{i}-2', f'Data{i}-3'])

wb.save('freeze_panes.xlsx')
print("已冻结首行并保存到 'freeze_panes.xlsx'。打开文件滚动查看效果。")

18. 设置数字格式

控制单元格中数字的显示格式，如货币、百分比、日期等。

import openpyxl
from datetime import datetime

wb = openpyxl.Workbook()
sheet = wb.active

# 写入不同类型的数据
sheet['A1'] = 0.123
sheet['A2'] = 1500.50
sheet['A3'] = datetime.now()

# 应用数字格式
sheet['A1'].number_format = '0.00%'  # 百分比
sheet['A2'].number_format = '$#,##0.00' # 货币
sheet['A3'].number_format = 'YYYY-MM-DD HH:MM:SS' # 日期时间

sheet.column_dimensions['A'].width = 25

wb.save('number_formats.xlsx')
print("已设置数字格式并保存到 'number_formats.xlsx'。")

19. 使用Pandas Styler进行样式设置

Pandas的 Styler 对象提供了一种更“数据驱动”的方式来设置Excel样式，例如根据值高亮显示单元格。

import pandas as pd

# 创建DataFrame
data = {'科目': ['语文', '数学', '英语'], '分数': [85, 98, 78]}
df = pd.DataFrame(data)

# 定义样式函数：分数低于80的标为红色
def highlight_low_score(s):
    return ['background-color: #FFCDD2' if v < 80 else '' for v in s]

# 应用样式
styler = df.style.apply(highlight_low_score, subset=['分数'])

# 导出到Excel
styler.to_excel('styled_scores.xlsx', engine='openpyxl', index=False)

print("使用Pandas Styler设置样式并保存到 'styled_scores.xlsx'。")

20. 保护工作表

可以锁定工作表以防止意外修改，同时可以设置密码。

import openpyxl

wb = openpyxl.Workbook()
sheet = wb.active

sheet['A1'] = '这是受保护的数据'
sheet['B1'] = '此单元格可编辑'

# 锁定A1单元格（默认所有单元格都是锁定的）
sheet['A1'].protection = openpyxl.styles.Protection(locked=True)
# 解锁B1单元格
sheet['B1'].protection = openpyxl.styles.Protection(locked=False)

# 启用工作表保护，可以设置密码
sheet.protection.sheet = True
sheet.protection.password = 'password123' # 设置密码

wb.save('protected_sheet.xlsx')
print("已保护工作表并保存到 'protected_sheet.xlsx'。密码是 'password123'。")

---

数据分析 (21-35)

这部分主要利用 pandas 库的强大功能，它极大地简化了数据处理和分析的流程。

首先，我们创建一个通用的数据集用于本节的多数示例。

import pandas as pd
import numpy as np

# 创建一个示例销售数据DataFrame
data = {
    'Date': pd.to_datetime(['2023-01-01', '2023-01-01', '2023-01-02', '2023-01-02', '2023-01-03', '2023-01-03', '2023-01-01']),
    'Region': ['East', 'West', 'East', 'West', 'East', 'South', 'East'],
    'Product': ['A', 'A', 'B', 'A', 'B', 'C', 'A'],
    'Sales': [100, 150, 200, 50, 120, 80, 100],
    'Quantity': [10, 15, 20, 5, 12, 8, 10]
}
df_sales = pd.DataFrame(data)

# 增加一些缺失值用于后续演示
df_sales.loc[5, 'Sales'] = np.nan

# 保存到Excel文件
df_sales.to_excel('sales_data.xlsx', index=False)

print("销售数据已创建并保存到 'sales_data.xlsx'。")

21. 根据条件筛选数据

使用 pandas 可以轻松地根据列值筛选出符合条件的行。

import pandas as pd

# 读取数据
df = pd.read_excel('sales_data.xlsx')

# 筛选出 'Region' 为 'East' 且 'Sales' 大于 100 的数据
filtered_df = df[(df['Region'] == 'East') & (df['Sales'] > 100)]

print("筛选结果 (Region为East且Sales > 100):")
print(filtered_df)

filtered_df.to_excel('filtered_sales.xlsx', index=False)

22. 按列排序数据

按一列或多列对数据进行升序或降序排序。

import pandas as pd

# 读取数据
df = pd.read_excel('sales_data.xlsx')

# 按 'Region' 升序，然后按 'Sales' 降序排序
sorted_df = df.sort_values(by=['Region', 'Sales'], ascending=[True, False])

print("排序结果:")
print(sorted_df)

sorted_df.to_excel('sorted_sales.xlsx', index=False)

23. 删除重复数据

识别并移除数据集中的重复行。

import pandas as pd

# 读取数据 (sales_data.xlsx 中有重复行)
df = pd.read_excel('sales_data.xlsx')

# 删除基于所有列都重复的行
df_no_duplicates = df.drop_duplicates()

print("原始数据行数:", len(df))
print("去重后数据行数:", len(df_no_duplicates))
print("\n去重后的数据:")
print(df_no_duplicates)

df_no_duplicates.to_excel('deduplicated_sales.xlsx', index=False)

24. 分组聚合数据

按某个或多个分类列对数据进行分组，然后计算每组的统计量（如总和、平均值等）。

import pandas as pd

# 读取数据
df = pd.read_excel('sales_data.xlsx')

# 按 'Region' 分组，计算每个区域的销售总额和平均数量
region_summary = df.groupby('Region').agg(
    Total_Sales=('Sales', 'sum'),
    Avg_Quantity=('Quantity', 'mean')
).reset_index()

print("按区域分组聚合的结果:")
print(region_summary)

region_summary.to_excel('region_summary.xlsx', index=False)

25. 创建数据透视表

数据透视表是数据汇总的强大工具，可以灵活地重塑数据以进行多维度分析。

import pandas as pd

# 读取数据
df = pd.read_excel('sales_data.xlsx')

# 创建数据透视表：以 'Region' 为行，'Product' 为列，值为 'Sales' 的总和
pivot_table = df.pivot_table(index='Region', columns='Product', values='Sales', aggfunc='sum', fill_value=0)

print("数据透视表结果:")
print(pivot_table)

pivot_table.to_excel('sales_pivot_table.xlsx')

26. 基于现有列计算新列

通过对现有列进行算术运算或其他转换来创建新的数据列。

import pandas as pd

# 读取数据
df = pd.read_excel('sales_data.xlsx')

# 计算新列 'Price_Per_Unit' (单价)
df['Price_Per_Unit'] = df['Sales'] / df['Quantity']

print("添加了 'Price_Per_Unit' 列后的数据:")
print(df)

df.to_excel('sales_with_price.xlsx', index=False)

27. 模拟Excel的VLOOKUP功能

使用 pandas.merge 可以实现类似Excel中VLOOKUP的功能，用于合并两个相关联的数据集。

import pandas as pd

# 主数据表 (sales_data.xlsx)
df_sales = pd.read_excel('sales_data.xlsx')

# 创建一个产品信息查找表
product_info = pd.DataFrame({
    'Product': ['A', 'B', 'C'],
    'Category': ['Fruit', 'Vegetable', 'Fruit']
})

# 使用 merge 实现 VLOOKUP
merged_df = pd.merge(df_sales, product_info, on='Product', how='left')

print("合并了产品类别信息后的数据:")
print(merged_df)

merged_df.to_excel('sales_with_category.xlsx', index=False)

28. 对列应用自定义函数

使用 .apply() 方法可以将一个自定义函数应用到DataFrame的某一列或多列。

import pandas as pd

# 读取数据
df = pd.read_excel('sales_data.xlsx')

# 定义一个函数，根据销售额给产品评级
def get_sales_level(sales):
    if pd.isna(sales):
        return 'Unknown'
    if sales > 150:
        return 'High'
    elif sales > 80:
        return 'Medium'
    else:
        return 'Low'

# 应用函数创建新列 'Sales_Level'
df['Sales_Level'] = df['Sales'].apply(get_sales_level)

print("添加了销售评级列的数据:")
print(df)

df.to_excel('sales_with_level.xlsx', index=False)

29. 处理缺失值

数据分析前通常需要处理缺失值（NaN），可以选择填充一个特定值，或直接删除含有缺失值的行。

import pandas as pd

# 读取含有缺失值的数据
df = pd.read_excel('sales_data.xlsx')

# 填充缺失的 'Sales' 值为该列的平均值
mean_sales = df['Sales'].mean()
df_filled = df.fillna({'Sales': mean_sales})

print("用平均值填充缺失值后的数据:")
print(df_filled)

# 或者，删除含有任何缺失值的行
df_dropped = df.dropna()
print("\n删除缺失值行后的数据:")
print(df_dropped)

df_filled.to_excel('sales_filled_na.xlsx', index=False)

30. 获取描述性统计信息

快速生成数值列的摘要统计信息，如计数、均值、标准差、最小值、最大值和四分位数。

import pandas as pd

# 读取数据
df = pd.read_excel('sales_data.xlsx')

# 获取数值列的描述性统计
stats = df.describe()

print("销售数据的描述性统计:")
print(stats)

stats.to_excel('sales_statistics.xlsx')

31. 添加自动筛选器

使用 openpyxl 为表格的标题行添加自动筛选功能，就像在Excel中手动操作一样。

import openpyxl

# 使用之前创建的 'sorted_sales.xlsx' 文件
file_path = 'sorted_sales.xlsx'

# 加载工作簿和工作表
wb = openpyxl.load_workbook(file_path)
sheet = wb.active

# 为整个数据范围添加自动筛选器
sheet.auto_filter.ref = sheet.dimensions

wb.save(file_path)
print(f"已为 '{file_path}' 添加自动筛选器。")

32. 设置条件格式

根据单元格的值动态改变其样式，例如将高于平均值的销售额标绿。

import openpyxl
from openpyxl.styles import PatternFill
from openpyxl.formatting.rule import CellIsRule

# 使用 'sales_with_price.xlsx' 文件
file_path = 'sales_with_price.xlsx'
wb = openpyxl.load_workbook(file_path)
sheet = wb.active

# 定义样式：绿色填充
green_fill = PatternFill(start_color='C6EFCE', end_color='C6EFCE', fill_type='solid')

# 定义规则：单元格值大于150
rule = CellIsRule(operator='greaterThan', formula=[150], fill=green_fill)

# 应用规则到 'Sales' 列 (D列)
sheet.conditional_formatting.add('D2:D100', rule)

wb.save('sales_conditional_formatting.xlsx')
print("已添加条件格式并保存到 'sales_conditional_formatting.xlsx'。")

33. 将一列拆分为多列

当一个单元格内包含多个信息时（例如用逗号分隔），可以将其拆分到不同的列中。

import pandas as pd

# 创建一个包含复合列的DataFrame
df = pd.DataFrame({
    'ID': [1, 2, 3],
    'Name_City': ['John-NewYork', 'Anna-London', 'Peter-Tokyo']
})

# 按 '-' 拆分 'Name_City' 列
df[['Name', 'City']] = df['Name_City'].str.split('-', expand=True)

# 删除原始列
df = df.drop('Name_City', axis=1)

print("拆分列后的数据:")
print(df)

df.to_excel('split_column_data.xlsx', index=False)

34. 合并多个Excel文件

将多个结构相同的Excel文件中的数据合并到一个文件中。

import pandas as pd
import os

# 创建两个模拟的月度销售文件
df1 = pd.DataFrame({'Month': ['Jan'], 'Sales': [1000]})
df2 = pd.DataFrame({'Month': ['Feb'], 'Sales': [1200]})
df1.to_excel('sales_jan.xlsx', index=False)
df2.to_excel('sales_feb.xlsx', index=False)

# 获取所有要合并的文件列表
files_to_merge = ['sales_jan.xlsx', 'sales_feb.xlsx']

# 读取并合并
all_data = pd.concat((pd.read_excel(f) for f in files_to_merge), ignore_index=True)

print("合并后的数据:")
print(all_data)

all_data.to_excel('yearly_sales_summary.xlsx', index=False)

# 清理临时文件
os.remove('sales_jan.xlsx')
os.remove('sales_feb.xlsx')

35. 将数据写入多个工作表

将不同的DataFrame写入同一个Excel文件的不同工作表中。

import pandas as pd

# 准备两个不同的DataFrame
df_east = pd.DataFrame({'Product': ['A', 'B'], 'Sales': [200, 250]})
df_west = pd.DataFrame({'Product': ['A', 'C'], 'Sales': [300, 150]})

# 使用 ExcelWriter 将它们写入不同的工作表
with pd.ExcelWriter('regional_sales_report.xlsx') as writer:
    df_east.to_excel(writer, sheet_name='East_Region', index=False)
    df_west.to_excel(writer, sheet_name='West_Region', index=False)

print("已将不同区域的数据写入到 'regional_sales_report.xlsx' 的不同工作表中。")

---

图表可视化 (36-42)

这部分将展示如何使用 openpyxl 在Excel中创建和定制各种图表。

首先，我们创建一个通用的数据文件，用于本节的所有图表示例。

import openpyxl

# 创建一个工作簿和工作表
wb = openpyxl.Workbook()
sheet = wb.active
sheet.title = 'ChartData'

# 添加数据
headers = ['Category', 'Series 1', 'Series 2', 'Scatter X', 'Scatter Y']
sheet.append(headers)
data = [
    ('A', 20, 30, 1, 5),
    ('B', 40, 60, 2, 10),
    ('C', 50, 70, 3, 7),
    ('D', 30, 50, 4, 15),
    ('E', 25, 45, 5, 8)
]
for row in data:
    sheet.append(row)

# 保存文件
wb.save('chart_data.xlsx')
print("图表数据已创建并保存到 'chart_data.xlsx'。")

36. 创建柱状图/条形图

柱状图是比较不同类别数据量的常用图表。

import openpyxl
from openpyxl.chart import BarChart, Reference

# 加载工作簿
wb = openpyxl.load_workbook('chart_data.xlsx')
sheet = wb['ChartData']

# 创建一个柱状图对象
chart = BarChart()
chart.type = "col" # 'col' 是柱状图, 'bar' 是条形图
chart.title = "柱状图示例"
chart.x_axis.title = "类别"
chart.y_axis.title = "数值"

# 选择数据范围
data = Reference(sheet, min_col=2, min_row=1, max_col=3, max_row=6)
cats = Reference(sheet, min_col=1, min_row=2, max_row=6)

# 将数据添加到图表
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)

# 将图表添加到工作表的位置 E2
sheet.add_chart(chart, "E2")

wb.save('bar_chart_report.xlsx')
print("柱状图已创建并保存到 'bar_chart_report.xlsx'。")

37. 创建折线图

折线图适合展示数据随时间或连续间隔变化的趋势。

import openpyxl
from openpyxl.chart import LineChart, Reference

# 加载工作簿
wb = openpyxl.load_workbook('chart_data.xlsx')
sheet = wb['ChartData']

# 创建一个折线图对象
chart = LineChart()
chart.title = "折线图示例"
chart.x_axis.title = "类别"
chart.y_axis.title = "数值"

# 选择数据范围
data = Reference(sheet, min_col=2, min_row=1, max_col=3, max_row=6)
cats = Reference(sheet, min_col=1, min_row=2, max_row=6)

# 将数据添加到图表
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)

# 将图表添加到工作表的位置 G2
sheet.add_chart(chart, "G2")

wb.save('line_chart_report.xlsx')
print("折线图已创建并保存到 'line_chart_report.xlsx'。")

38. 创建饼图

饼图用于显示各部分占总体的百分比。

import openpyxl
from openpyxl.chart import PieChart, Reference

# 加载工作簿
wb = openpyxl.load_workbook('chart_data.xlsx')
sheet = wb['ChartData']

# 创建一个饼图对象
chart = PieChart()
chart.title = "饼图示例 (Series 1)"

# 选择数据范围 (只使用 Series 1)
labels = Reference(sheet, min_col=1, min_row=2, max_row=6)
data = Reference(sheet, min_col=2, min_row=1, max_row=6)

chart.add_data(data, titles_from_data=True)
chart.set_categories(labels)

# 将图表添加到工作表的位置 G2
sheet.add_chart(chart, "G2")

wb.save('pie_chart_report.xlsx')
print("饼图已创建并保存到 'pie_chart_report.xlsx'。")

39. 创建散点图

散点图用于展示两个数值变量之间的关系。

import openpyxl
from openpyxl.chart import ScatterChart, Reference, Series

# 加载工作簿
wb = openpyxl.load_workbook('chart_data.xlsx')
sheet = wb['ChartData']

# 创建一个散点图对象
chart = ScatterChart()
chart.title = "散点图示例"
chart.x_axis.title = "X 值"
chart.y_axis.title = "Y 值"

# 设置散点图样式
chart.style = 2  # 设置图表样式

# 定义X和Y轴的数据
x_values = Reference(sheet, min_col=4, min_row=2, max_row=6)
y_values = Reference(sheet, min_col=5, min_row=2, max_row=6)

# 创建一个数据系列并添加到图表
series = Series(y_values, x_values, title="观测点")
# 设置散点图的标记样式
series.marker.symbol = "circle"
series.marker.size = 7
series.graphicalProperties.line.noFill = True  # 不显示连接线
chart.series.append(series)

# 将图表添加到工作表的位置 G2
sheet.add_chart(chart, "G2")

wb.save('scatter_chart_report.xlsx')
print("散点图已创建并保存到 'scatter_chart_report.xlsx'。")

40. 创建面积图

面积图类似于折线图，但线下方的区域被填充，强调数量随时间变化的程度。

import openpyxl
from openpyxl.chart import AreaChart, Reference

# 加载工作簿
wb = openpyxl.load_workbook('chart_data.xlsx')
sheet = wb['ChartData']

# 创建一个面积图对象
chart = AreaChart()
chart.title = "面积图示例"
chart.x_axis.title = "类别"
chart.y_axis.title = "数值"

# 选择数据范围
data = Reference(sheet, min_col=2, min_row=1, max_col=3, max_row=6)
cats = Reference(sheet, min_col=1, min_row=2, max_row=6)

# 将数据添加到图表
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)

# 将图表添加到工作表的位置 G2
sheet.add_chart(chart, "G2")

wb.save('area_chart_report.xlsx')
print("面积图已创建并保存到 'area_chart_report.xlsx'。")

41. 自定义图表样式（标题、图例、颜色）

可以对图表的各个元素进行详细的定制，以满足报告的美观要求。

import openpyxl
from openpyxl.chart import LineChart, Reference
from openpyxl.chart.shapes import GraphicalProperties
from openpyxl.drawing.line import LineProperties

# 加载工作簿
wb = openpyxl.load_workbook('chart_data.xlsx')
sheet = wb['ChartData']

# 创建折线图
chart = LineChart()

# 自定义标题
chart.title = "自定义图表"
chart.style = 13 # 选择一个内置样式

# 自定义坐标轴
chart.y_axis.title = 'Y-Axis'
chart.x_axis.title = 'X-Axis'

# 自定义图例位置
chart.legend.position = 'b' # 'b' for bottom

# 选择数据
data = Reference(sheet, min_col=2, min_row=1, max_col=3, max_row=6)
cats = Reference(sheet, min_col=1, min_row=2, max_row=6)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)

# 自定义数据系列颜色
s1 = chart.series[0]
s1.graphicalProperties = GraphicalProperties(ln=LineProperties(solidFill="FF0000")) # 红色
s2 = chart.series[1]
s2.marker.symbol = "triangle" # 改变数据点标记
s2.graphicalProperties = GraphicalProperties(ln=LineProperties(solidFill="00B050")) # 绿色

# 添加图表
sheet.add_chart(chart, "G2")

wb.save('custom_chart_report.xlsx')
print("自定义图表已创建并保存到 'custom_chart_report.xlsx'。")

42. 创建带有次坐标轴的组合图

当需要比较两个单位和量级差异很大的数据系列时，组合图和次坐标轴非常有用。

import openpyxl
from openpyxl.chart import BarChart, LineChart, Reference

# 准备数据
wb = openpyxl.Workbook()
sheet = wb.active
sheet.append(['Month', 'Sales (Units)', 'Avg Temperature (°C)'])
for i in range(1, 7):
    sheet.append([f'Month {i}', 100 + i*20, 15 + i*2])

# 创建主图（柱状图）
bar_chart = BarChart()
data1 = Reference(sheet, min_col=2, min_row=1, max_row=7)
cats = Reference(sheet, min_col=1, min_row=2, max_row=7)
bar_chart.add_data(data1, titles_from_data=True)
bar_chart.set_categories(cats)
bar_chart.y_axis.title = "销量 (单位)"

# 创建次坐标轴的图（折线图）
line_chart = LineChart()
data2 = Reference(sheet, min_col=3, min_row=1, max_row=7)
line_chart.add_data(data2, titles_from_data=True)
line_chart.y_axis.axId = 200 # 设置为次坐标轴ID
line_chart.y_axis.title = "平均温度 (°C)"

# 将折线图的Y轴移到次坐标轴
bar_chart.y_axis.crosses = "max"
bar_chart += line_chart # 将折线图叠加到柱状图上

# 添加图表
sheet.add_chart(bar_chart, "E2")

wb.save('combo_chart_report.xlsx')
print("组合图已创建并保存到 'combo_chart_report.xlsx'。")

---

功能分类：高级功能 (43-50)

43. 批量处理文件夹中的所有Excel文件

自动化处理一个文件夹内的所有Excel文件，例如，为每个文件的特定列计算总和。

import pandas as pd
import os

# 创建一个临时文件夹和一些示例文件
if not os.path.exists('temp_excel_folder'):
    os.makedirs('temp_excel_folder')

pd.DataFrame({'Data': [10, 20]}).to_excel('temp_excel_folder/test1.xlsx', index=False)
pd.DataFrame({'Data': [30, 40]}).to_excel('temp_excel_folder/test2.xlsx', index=False)

# 目标文件夹
folder_path = 'temp_excel_folder'

# 遍历文件夹中的所有 .xlsx 文件
for filename in os.listdir(folder_path):
    if filename.endswith('.xlsx'):
        file_path = os.path.join(folder_path, filename)
        
        # 读取文件
        df = pd.read_excel(file_path)
        
        # 计算'Data'列的总和
        total = df['Data'].sum()
        
        print(f"文件 '{filename}' 的 'Data' 列总和是: {total}")

print("\n批量处理完成。")

44. 添加数据验证

为单元格设置数据验证规则，限制用户输入的数据类型或范围，例如，只允许输入列表中的特定值。

import openpyxl
from openpyxl.worksheet.datavalidation import DataValidation

wb = openpyxl.Workbook()
sheet = wb.active

# 创建数据验证对象
# 允许列表中的值
dv = DataValidation(type="list", formula1='"男,女"', allow_blank=True)

# 添加提示信息
dv.prompt = '请从列表中选择'
dv.promptTitle = '性别选择'

# 将验证规则添加到工作表
sheet.add_data_validation(dv)

# 将规则应用到B列的所有单元格
dv.add('B1:B1048576')

sheet['A1'] = '姓名'
sheet['B1'] = '性别 (请点击选择)'

wb.save('data_validation_example.xlsx')
print("已添加数据验证并保存到 'data_validation_example.xlsx'。")

45. 写入和读取Excel公式

可以直接向单元格写入Excel公式，openpyxl 在读取时可以通过 data_only=False 来获取公式本身，或 data_only=True 来获取公式计算后的结果。

import openpyxl

wb = openpyxl.Workbook()
sheet = wb.active

# 写入数据和公式
sheet['A1'] = 10
sheet['A2'] = 20
sheet['A3'] = '=SUM(A1:A2)'

# 保存文件
wb.save('formula_example.xlsx')
print("文件 'formula_example.xlsx' 已保存，A3包含公式。")

# --- 读取公式和结果 ---

# 读取公式本身
wb_formula = openpyxl.load_workbook('formula_example.xlsx', data_only=False)
sheet_formula = wb_formula.active
formula_in_a3 = sheet_formula['A3'].value
print(f"读取到的公式是: {formula_in_a3}")

# 读取公式计算出的值
wb_value = openpyxl.load_workbook('formula_example.xlsx', data_only=True)
sheet_value = wb_value.active
value_in_a3 = sheet_value['A3'].value
print(f"读取到的计算结果是: {value_in_a3}")

46. 将Excel表格转换为HTML

pandas 可以轻松地将DataFrame转换为HTML表格，便于在网页上展示。

import pandas as pd

# 创建一个DataFrame
data = {'排名': [1, 2, 3], '城市': ['上海', '北京', '深圳'], 'GDP (万亿)': [4.32, 3.99, 3.07]}
df = pd.DataFrame(data)

# 将DataFrame转换为HTML字符串
html_table = df.to_html(index=False, classes='table table-striped', justify='center')

# 将HTML保存到文件
with open('table.html', 'w', encoding='utf-8') as f:
    f.write(html_table)

print("Excel数据已转换为HTML并保存到 'table.html'。")

47. 从网页抓取表格并存入Excel

pandas 的 read_html 功能非常强大，可以自动识别并读取网页中的表格数据。

import pandas as pd

# 这是一个包含表格的示例URL 
# 注意：此功能需要安装 lxml 库 (pip install lxml)
url = 'https://www.bigbug.net/table.html'

try:
    # read_html 返回一个DataFrame的列表
    tables = pd.read_html(url)
    
    # 通常第一个或第二个表格是我们需要的
    df_population = tables[0]

    #print(df_population)
    
    # 清理和重命名列
    df_population = df_population.iloc[:, [1, 2]] # 选择需要的列
    df_population.columns = ['新标题1', '新标题2']
    
    # 保存到Excel
    df_population.head(2).to_excel('new_table.xlsx', index=False)
    
    print("已成功从网页中获取到数据并保存前2条到 'new_table.xlsx'。")

except Exception as e:
    print(f"抓取失败，可能是网络问题或网页结构已改变: {e}")

48. 性能优化：使用 openpyxl 的 write_only 模式

对于生成非常大的Excel文件，write_only 模式可以显著降低内存消耗，因为它以流式方式写入数据，不会在内存中保留整个工作簿结构。

import openpyxl

# 创建一个只写模式的工作簿
wb = openpyxl.Workbook(write_only=True)
ws = wb.create_sheet()

print("开始以只写模式生成大型文件 'large_write_only.xlsx'...")

# 写入标题行
ws.append(['ID', 'Value1', 'Value2'])

# 写入大量数据（10万行）
for i in range(100000):
    ws.append([i, i*2, i*3])

# 保存文件
wb.save('large_write_only.xlsx')

print("大型文件生成完毕。")

49. 将Excel转换为CSV

将Excel文件转换为CSV（逗号分隔值）格式，这是一种更通用、更轻量级的数据存储格式。

import pandas as pd

# 准备一个Excel文件
pd.DataFrame({'A': [1, 2], 'B': ['x', 'y']}).to_excel('data_to_convert.xlsx', index=False)

# 读取Excel
df = pd.read_excel('data_to_convert.xlsx')

# 保存为CSV
# index=False 避免写入pandas的索引
# encoding='utf-8-sig' 确保中文字符在Excel中正确显示
df.to_csv('converted_data.csv', index=False, encoding='utf-8-sig')

print("文件 'data_to_convert.xlsx' 已成功转换为 'converted_data.csv'。")

50. 从CSV读取数据并存入Excel

与上一个示例相反，将CSV文件的数据读入并保存为具有更多格式化选项的Excel文件。

import pandas as pd

# 准备一个CSV文件
csv_data = "col1,col2\n10,20\n30,40"
with open('data.csv', 'w') as f:
    f.write(csv_data)

# 读取CSV
df = pd.read_csv('data.csv')

# 保存为Excel
df.to_excel('from_csv.xlsx', index=False, sheet_name='MyData')

print("文件 'data.csv' 已成功转换为 'from_csv.xlsx'。")

---

总结

至此，这些示例代码已经涵盖了50个从基础到高级的Python处理Excel的常见场景。你可以直接使用或根据具体需求进行调整。

工具选择指南：

1. 数据清洗分析 → pandas

   • 适用于筛选、排序、聚合、处理缺失值、数据透视等复杂的数据操作。

2. 格式控制/公式/图表 → openpyxl / xlsxwriter

   • openpyxl 擅长对现有 .xlsx 文件进行精细的格式修改。
   • xlsxwriter 专注于从头创建格式复杂的报告，尤其在图表和高级功能方面表现出色。

3. 大文件处理 → openpyxl (read_only模式) 或 pandas 分块

   • openpyxl 的只读模式能显著降低大文件读取时的内存消耗。
   • pandas 通过分块读取，可以处理超出内存大小的数据集。

4. Excel 自动化 → xlwings

   • 能与正在运行的 Excel 应用程序进行实时交互，适合需要VBA式自动化的场景。

5. 旧版 .xls 文件 → xlrd / xlwt

   • 这两个库是处理 Excel 2003 及更早版本 .xls 格式的标准选择。

6. 格式转换 → Tablib

   • 轻量级库，可以方便地在 Excel, CSV, JSON, HTML 等多种格式间进行数据转换。

7. 混合场景 → pandas + openpyxl 组合

   • 这是最强大和灵活的组合：利用 pandas 进行高效的数据分析，然后借助 openpyxl 引擎进行精美的格式化输出。

性能优化要点

1. 大文件优先使用 read_only=True 或分块处理：避免一次性将大文件全部加载到内存中，这是处理大数据的关键。

2. 数据类型优化减少内存占用：在 pandas 中，使用 astype() 将列转换为更节省空间的类型（如将 float64 转为 float32，或将数字列转为 category 类型）可以有效减少内存占用。

3. 及时释放不需要的对象：在处理完大型数据对象（如 DataFrame）后，使用 del 关键字并调用垃圾回收 (import gc; gc.collect()) 可以帮助及时释放内存。

4. 批处理时注意内存管理：在循环处理多个文件或数据块时，确保在每次迭代结束时清理不再需要的变量，防止内存泄漏。

希望这些示例能对您有所帮助！(*￣︶￣)