`Python`的`Excel`操作：`pandas`和`openpyxl`的`高级`用法。

Python Excel 操作：Pandas 与 Openpyxl 高级用法

大家好，今天我们来深入探讨 Python 中使用 Pandas 和 Openpyxl 操作 Excel 的高级技巧。Excel 作为数据处理和报告生成的常用工具，掌握高效的 Python 操作方法至关重要。我们将结合实际案例，讲解如何利用这两个库提升数据处理和报告生成效率。

一、 Pandas 高级用法

Pandas 提供了强大的数据分析和处理能力，与 Excel 的交互也非常便捷。以下是一些 Pandas 的高级用法，能够更高效地处理 Excel 数据。

1. 多 Sheet 读取与写入

通常一个 Excel 文件包含多个 Sheet，我们需要能够方便地读取和写入多个 Sheet。

• 读取多个 Sheet:

  使用 pd.ExcelFile 对象可以一次性读取 Excel 文件中的所有 Sheet，然后按 Sheet 名称或索引访问。

  import pandas as pd
  
  excel_file = pd.ExcelFile('multi_sheet.xlsx')
  
  # 获取所有 sheet 名称
  sheet_names = excel_file.sheet_names
  print(f"Sheet names: {sheet_names}")
  
  # 按 sheet 名称读取
  df_sheet1 = excel_file.parse('Sheet1')
  df_sheet2 = excel_file.parse('Sheet2') # 或者 excel_file.parse(0) 用 index 读取
  
  print("Sheet1 data:")
  print(df_sheet1.head())
  print("Sheet2 data:")
  print(df_sheet2.head())

• 写入多个 Sheet:

  使用 ExcelWriter 对象可以同时写入多个 DataFrame 到不同的 Sheet。

  import pandas as pd
  
  df1 = pd.DataFrame({'col1': [1, 2], 'col2': [3, 4]})
  df2 = pd.DataFrame({'colA': ['a', 'b'], 'colB': ['c', 'd']})
  
  with pd.ExcelWriter('multi_sheet_output.xlsx') as writer:
      df1.to_excel(writer, sheet_name='Sheet1', index=False)
      df2.to_excel(writer, sheet_name='Sheet2', index=False)
  
  print("Multi-sheet Excel file created successfully!")

2. 数据清洗与转换

Pandas 提供了丰富的数据清洗和转换功能，可以在读取 Excel 数据后进行预处理，例如处理缺失值、转换数据类型、去除重复项等。

• 处理缺失值:

  import pandas as pd
  import numpy as np
  
  # 创建包含缺失值的 DataFrame
  data = {'col1': [1, 2, np.nan, 4], 'col2': [5, np.nan, 7, 8]}
  df = pd.DataFrame(data)
  print("Original DataFrame:")
  print(df)
  
  # 填充缺失值
  df_filled = df.fillna(0) # 用 0 填充
  print("nDataFrame after filling NaN with 0:")
  print(df_filled)
  
  df_filled_mean = df.fillna(df.mean()) # 用均值填充
  print("nDataFrame after filling NaN with mean:")
  print(df_filled_mean)
  
  # 删除包含缺失值的行
  df_dropna = df.dropna()
  print("nDataFrame after dropping rows with NaN:")
  print(df_dropna)

• 数据类型转换:

  import pandas as pd
  
  # 创建 DataFrame
  data = {'col1': ['1', '2', '3'], 'col2': ['4.5', '5.6', '6.7']}
  df = pd.DataFrame(data)
  print("Original DataFrame:")
  print(df.dtypes)
  print(df)
  
  # 转换数据类型
  df['col1'] = pd.to_numeric(df['col1'])
  df['col2'] = pd.to_numeric(df['col2'])
  print("nDataFrame after converting data types:")
  print(df.dtypes)
  print(df)

• 去除重复项:

  import pandas as pd
  
  # 创建包含重复项的 DataFrame
  data = {'col1': [1, 2, 2, 3], 'col2': ['a', 'b', 'b', 'c']}
  df = pd.DataFrame(data)
  print("Original DataFrame:")
  print(df)
  
  # 去除重复项
  df_deduplicated = df.drop_duplicates()
  print("nDataFrame after removing duplicates:")
  print(df_deduplicated)
  
  # 基于特定列去除重复项
  df_deduplicated_col1 = df.drop_duplicates(subset=['col1'])
  print("nDataFrame after removing duplicates based on 'col1':")
  print(df_deduplicated_col1)

3. 数据筛选与排序

Pandas 提供了灵活的数据筛选和排序功能，可以根据条件提取数据，并按指定列进行排序。

• 数据筛选:

  import pandas as pd
  
  # 创建 DataFrame
  data = {'col1': [1, 2, 3, 4, 5], 'col2': ['a', 'b', 'c', 'b', 'a']}
  df = pd.DataFrame(data)
  print("Original DataFrame:")
  print(df)
  
  # 根据条件筛选数据
  df_filtered = df[df['col1'] > 2]
  print("nDataFrame after filtering 'col1' > 2:")
  print(df_filtered)
  
  df_filtered_multiple = df[(df['col1'] > 2) & (df['col2'] == 'b')]
  print("nDataFrame after filtering 'col1' > 2 and 'col2' == 'b':")
  print(df_filtered_multiple)
  
  df_filtered_isin = df[df['col2'].isin(['a', 'c'])]
  print("nDataFrame after filtering 'col2' in ['a', 'c']:")
  print(df_filtered_isin)

• 数据排序:

  import pandas as pd
  
  # 创建 DataFrame
  data = {'col1': [3, 1, 4, 2, 5], 'col2': ['c', 'a', 'd', 'b', 'e']}
  df = pd.DataFrame(data)
  print("Original DataFrame:")
  print(df)
  
  # 按单列排序
  df_sorted = df.sort_values(by='col1')
  print("nDataFrame after sorting by 'col1':")
  print(df_sorted)
  
  # 按多列排序
  df_sorted_multiple = df.sort_values(by=['col2', 'col1'])
  print("nDataFrame after sorting by 'col2' then 'col1':")
  print(df_sorted_multiple)
  
  # 降序排序
  df_sorted_descending = df.sort_values(by='col1', ascending=False)
  print("nDataFrame after sorting by 'col1' in descending order:")
  print(df_sorted_descending)

4. 数据分组与聚合

Pandas 提供了强大的数据分组和聚合功能，可以根据指定列对数据进行分组，并进行统计分析。

import pandas as pd

# 创建 DataFrame
data = {'Category': ['A', 'A', 'B', 'B', 'A', 'C', 'C'],
        'Value': [10, 15, 20, 25, 12, 30, 35]}
df = pd.DataFrame(data)
print("Original DataFrame:")
print(df)

# 数据分组与聚合
grouped = df.groupby('Category')['Value'].sum()
print("nGrouped data (sum of 'Value' by 'Category'):")
print(grouped)

grouped_multiple = df.groupby('Category')['Value'].agg(['sum', 'mean', 'count'])
print("nGrouped data (sum, mean, count of 'Value' by 'Category'):")
print(grouped_multiple)

grouped_apply = df.groupby('Category')['Value'].apply(lambda x: x / x.sum()) # 计算每个 category 内 value 的占比
print("nGrouped data (Value percentage within each Category):")
print(grouped_apply)

5. 使用 pd.read_excel 的 converters 参数进行复杂数据类型处理

pd.read_excel 的 converters 参数允许我们在读取 Excel 时，对特定列应用自定义函数进行数据类型转换或数据清洗。

import pandas as pd

def clean_phone_number(phone_number):
    """清理电话号码，移除非数字字符并确保格式正确"""
    if isinstance(phone_number, str):
        cleaned_number = ''.join(filter(str.isdigit, phone_number))
        if len(cleaned_number) == 11 and cleaned_number.startswith('86'):  # 假设中国号码
            return cleaned_number[2:]  # 去除前缀 86
        elif len(cleaned_number) == 10: # 假设美国号码
            return cleaned_number
        else:
            return None # 无效号码
    else:
        return None # 非字符串类型，返回None

# 创建包含需要清洗的电话号码的 Excel 文件
data = {'Name': ['Alice', 'Bob', 'Charlie'],
        'Phone': ['86-13800000000', '123-456-7890', 'invalid']}
df = pd.DataFrame(data)
df.to_excel('phone_numbers.xlsx', index=False)

# 使用 converters 参数读取 Excel 文件并清洗电话号码
df_cleaned = pd.read_excel('phone_numbers.xlsx', converters={'Phone': clean_phone_number})

print(df_cleaned)

二、 Openpyxl 高级用法

Openpyxl 提供了更底层的 Excel 操作能力，可以精细控制 Excel 文件的格式、样式和公式。以下是一些 Openpyxl 的高级用法。

1. 创建 Excel 文件并写入数据

from openpyxl import Workbook

# 创建新的 Workbook 对象
workbook = Workbook()

# 获取当前活跃的 Sheet
sheet = workbook.active

# 写入数据
sheet['A1'] = 'Hello'
sheet['B1'] = 'World'

# 写入多行数据
data = [
    ['Name', 'Age', 'City'],
    ['Alice', 30, 'New York'],
    ['Bob', 25, 'London'],
    ['Charlie', 35, 'Paris']
]

for row_idx, row_data in enumerate(data):
    for col_idx, cell_value in enumerate(row_data):
        sheet.cell(row=row_idx + 1, column=col_idx + 1, value=cell_value)

# 保存 Excel 文件
workbook.save('example.xlsx')

print("Excel file created successfully!")

2. 读取 Excel 文件数据

from openpyxl import load_workbook

# 加载 Excel 文件
workbook = load_workbook('example.xlsx')

# 获取 Sheet
sheet = workbook['Sheet'] # 或者 workbook.active

# 读取单元格数据
cell_value = sheet['A1'].value
print(f"Value of A1: {cell_value}")

# 遍历所有行
for row in sheet.iter_rows(min_row=1, max_row=sheet.max_row, min_col=1, max_col=sheet.max_column, values_only=True):
    print(row)

# 遍历特定列
for cell in sheet['A']:
    print(cell.value)

3. 格式化单元格样式

Openpyxl 允许我们自定义单元格的字体、颜色、对齐方式等样式。

from openpyxl import Workbook
from openpyxl.styles import Font, Color, Alignment, PatternFill

# 创建 Workbook
workbook = Workbook()
sheet = workbook.active

# 设置字体样式
font = Font(name='Arial', size=12, bold=True, italic=True, color='FF0000')
sheet['A1'].font = font
sheet['A1'] = 'Formatted Text'

# 设置对齐方式
alignment = Alignment(horizontal='center', vertical='center')
sheet['B1'].alignment = alignment
sheet['B1'] = 'Aligned Text'

# 设置背景颜色
fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')
sheet['C1'].fill = fill
sheet['C1'] = 'Colored Background'

# 设置行高和列宽
sheet.row_dimensions[1].height = 30
sheet.column_dimensions['A'].width = 20

workbook.save('formatted.xlsx')
print("Excel file with formatting created successfully!")

4. 添加公式

Openpyxl 支持在单元格中添加 Excel 公式，并自动计算结果。

from openpyxl import Workbook

# 创建 Workbook
workbook = Workbook()
sheet = workbook.active

# 写入数据
sheet['A1'] = 10
sheet['A2'] = 20

# 添加公式
sheet['A3'] = '=SUM(A1:A2)'  # 计算 A1 和 A2 的和

# 保存 Excel 文件
workbook.save('formula.xlsx')
print("Excel file with formula created successfully!")

5. 插入图表

Openpyxl 允许我们在 Excel 中插入各种类型的图表，例如柱状图、折线图、饼图等。

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

# 创建 Workbook
workbook = Workbook()
sheet = workbook.active

# 写入数据
data = [
    ['Category', 'Value'],
    ['A', 10],
    ['B', 40],
    ['C', 50],
    ['D', 30]
]

for row_idx, row_data in enumerate(data):
    for col_idx, cell_value in enumerate(row_data):
        sheet.cell(row=row_idx + 1, column=col_idx + 1, value=cell_value)

# 创建柱状图
chart = BarChart()
chart.title = 'Bar Chart Example'
chart.x_axis.title = 'Category'
chart.y_axis.title = 'Value'

# 定义数据范围
data_values = Reference(sheet, min_col=2, min_row=2, max_col=2, max_row=5)
categories = Reference(sheet, min_col=1, min_row=2, max_col=1, max_row=5)

# 添加数据
chart.add_data(data_values, titles_from_data=True)
chart.set_categories(categories)

# 将图表添加到 Sheet
sheet.add_chart(chart, 'E2')

# 保存 Excel 文件
workbook.save('chart.xlsx')
print("Excel file with chart created successfully!")

6. 合并单元格

from openpyxl import Workbook

workbook = Workbook()
sheet = workbook.active

sheet.merge_cells('A1:B2')
sheet['A1'] = "Merged Cell"

workbook.save('merged_cells.xlsx')
print("Excel file with merged cells created successfully!")

7. 使用 Openpyxl 调整列宽以适应内容

Openpyxl 本身没有直接提供自动调整列宽以适应内容的功能，但我们可以通过计算单元格中最长内容的长度来手动调整列宽。

from openpyxl import Workbook
from openpyxl.utils import get_column_letter

def adjust_column_width(sheet):
    for col in sheet.columns:
        max_length = 0
        column = col[0].column_letter  # 获取列的字母标识

        for cell in col:
            try:  # 单元格可能包含公式，图片等，需要 try except
                if len(str(cell.value)) > max_length:
                    max_length = len(str(cell.value))
            except:
                pass

        adjusted_width = (max_length + 2)  # 添加一些额外的空间
        sheet.column_dimensions[column].width = adjusted_width

# 创建 Workbook
workbook = Workbook()
sheet = workbook.active

# 写入数据
data = [
    ['Product', 'Description', 'Price'],
    ['Laptop', 'A high-performance laptop with a 15-inch display and a powerful processor', 1200],
    ['Smartphone', 'A cutting-edge smartphone with a stunning camera and long battery life', 800],
    ['Tablet', 'A versatile tablet for work and entertainment', 300]
]

for row_idx, row_data in enumerate(data):
    for col_idx, cell_value in enumerate(row_data):
        sheet.cell(row=row_idx + 1, column=col_idx + 1, value=cell_value)

# 调整列宽
adjust_column_width(sheet)

# 保存 Excel 文件
workbook.save('adjusted_column_width.xlsx')
print("Excel file with adjusted column width created successfully!")

三、 Pandas 与 Openpyxl 结合使用

Pandas 擅长数据处理，Openpyxl 擅长格式控制。我们可以将两者结合起来，先使用 Pandas 处理数据，然后使用 Openpyxl 对 Excel 文件进行格式化。

import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Font, Alignment

# 1. 使用 Pandas 读取 Excel 数据
df = pd.DataFrame({'col1': [1, 2, 3], 'col2': ['a', 'b', 'c']})
excel_file = 'combined.xlsx'
with pd.ExcelWriter(excel_file, engine='openpyxl') as writer:
    df.to_excel(writer, sheet_name='Sheet1', index=False)

# 2. 使用 Openpyxl 加载 Excel 文件
workbook = load_workbook(excel_file)
sheet = workbook['Sheet1']

# 3. 格式化表头
header_font = Font(bold=True, color='0000FF')
header_alignment = Alignment(horizontal='center')

for cell in sheet[1]: # 第一行是表头
    cell.font = header_font
    cell.alignment = header_alignment

# 保存 Excel 文件
workbook.save(excel_file)
print("Combined Pandas and Openpyxl example completed successfully!")

在这个例子中，我们首先使用 Pandas 创建一个 DataFrame，并将其写入 Excel 文件。然后，使用 Openpyxl 加载 Excel 文件，并对表头进行格式化，例如设置为粗体、蓝色字体和居中对齐。

四、 实际案例：生成销售报告

假设我们需要根据销售数据生成一份 Excel 报告，包含以下内容：

• 总销售额
• 按产品类别统计销售额
• 按销售区域统计销售额
• 绘制销售额柱状图

首先，我们准备一份包含销售数据的 Excel 文件 sales_data.xlsx，包含以下列：Product, Category, Region, Sales。

然后，我们可以使用 Pandas 和 Openpyxl 结合来实现报告生成。

import pandas as pd
from openpyxl import load_workbook
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl.chart import BarChart, Reference
from openpyxl.styles import Font, Alignment

# 1. 读取销售数据
sales_data = pd.read_excel('sales_data.xlsx')

# 2. 计算总销售额
total_sales = sales_data['Sales'].sum()

# 3. 按产品类别统计销售额
category_sales = sales_data.groupby('Category')['Sales'].sum().reset_index()

# 4. 按销售区域统计销售额
region_sales = sales_data.groupby('Region')['Sales'].sum().reset_index()

# 5. 创建 Excel 文件
report_file = 'sales_report.xlsx'
with pd.ExcelWriter(report_file, engine='openpyxl') as writer:
    # 写入总销售额
    total_sales_df = pd.DataFrame({'Metric': ['Total Sales'], 'Value': [total_sales]})
    total_sales_df.to_excel(writer, sheet_name='Summary', index=False, startrow=0, startcol=0)

    # 写入按产品类别统计
    category_sales.to_excel(writer, sheet_name='Category Sales', index=False)

    # 写入按销售区域统计
    region_sales.to_excel(writer, sheet_name='Region Sales', index=False)

# 6. 使用 Openpyxl 格式化 Excel 文件
workbook = load_workbook(report_file)

# 格式化 Summary sheet
summary_sheet = workbook['Summary']
summary_sheet['A1'].font = Font(bold=True)
summary_sheet['B1'].font = Font(bold=True)
summary_sheet['A1'].alignment = Alignment(horizontal='center')
summary_sheet['B1'].alignment = Alignment(horizontal='center')

# 绘制 Category Sales 柱状图
category_sheet = workbook['Category Sales']
chart = BarChart()
chart.title = 'Category Sales Chart'
chart.x_axis.title = 'Category'
chart.y_axis.title = 'Sales'

data = Reference(category_sheet, min_col=2, min_row=1, max_col=2, max_row=category_sheet.max_row)
categories = Reference(category_sheet, min_col=1, min_row=2, max_col=1, max_row=category_sheet.max_row)

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

category_sheet.add_chart(chart, 'D2')

# 保存 Excel 文件
workbook.save(report_file)
print("Sales report generated successfully!")

这个案例展示了如何使用 Pandas 进行数据处理和分析，然后使用 Openpyxl 对 Excel 文件进行格式化和添加图表，最终生成一份完整的销售报告。

总结

掌握 Pandas 和 Openpyxl 的高级用法，能够极大地提升 Python 在 Excel 数据处理和报告生成方面的效率。Pandas 擅长数据清洗、转换、筛选、排序和聚合，而 Openpyxl 擅长 Excel 文件的格式化、公式添加和图表插入。结合使用这两个库，可以实现复杂且精美的 Excel 报告生成。

进一步提升的方向

• 错误处理和日志记录
• 性能优化（例如使用 openpyxl.cell.read_only=True 读取大型 Excel 文件）
• 更复杂的图表定制
• 自动化报告生成流程（例如定时任务）