Python｜玩转 Excel：Pandas、openpyxl、pywin32

引言

Excel 是在数据处理和分析中一种最常用的工具，而 Python 是一种强大的编程语言。结合 Python 的数据处理库，可以方便地操作 Excel 文件，进行数据读取、处理、分析和报告生成等工作。本文将介绍如何使用 Python 操作 Excel 文件，包括读取、写入、数据处理、样式设置、数据验证、公式支持和图表创建等方面的内容。

在 Python 中，有多种库和方法可以操作 Excel 文件。下面我们捡一些常用的库及其基本操作进行介绍。

Pandas

Pandas 是一个强大的数据分析和处理库，非常适合处理表格数据。它可以读取 Excel 文件：pd.read_excel()；写入 Excel 文件：DataFrame.to_excel()；处理 DataFrame，对数据进行筛选、聚合等操作。

安装 Pandas

pip install pandas

导入 Pandas

import pandas as pd

读取 Excel

Pandas 可以读取 .xls 和 .xlsx 文件。可以指定读取特定的 sheet。

import pandas as pd

# 读取整个 Excel 文件
df = pd.read_excel('file.xlsx')

# 读取特定的 sheet
df_sheet1 = pd.read_excel('file.xlsx', sheet_name='Sheet1')

# 读取多个 sheet
dfs = pd.read_excel('file.xlsx', sheet_name=['Sheet1', 'Sheet2'])

写入 Excel

Pandas 可以将 DataFrame 保存为 Excel 文件。可以选择是否写入索引。

# 创建一个简单的 DataFrame
data = {
   
    'Name': ['Alice', 'Bob', 'Charlie'],
    'Age': [24, 30, 22]
}
df = pd.DataFrame(data)

# 写入 Excel 文件
df.to_excel('output.xlsx', index=False)  # 不写入索引

数据操作

Pandas 可以对 DataFrame 进行数据筛选、分组、合并和聚合等。

# 假设 df 包含以下数据
#    Name   Age   Salary
# 0  Alice   24   50000
# 1    Bob   30   60000
# 2 Charlie   22   45000
# 3    Eve   35   70000

# 数据筛选：筛选出年龄大于 25 的数据
filtered_df = df[df['Age'] > 25]

# 数据分组和聚合：计算每个年龄的平均工资
grouped_df = df.groupby('Age').mean()

# 合并多个 DataFrame：合并 df1 和 df2
df1 = pd.DataFrame({
   'Name': ['Alice', 'Bob'], 'Age': [24, 30]})
df2 = pd.DataFrame({
   'Name': ['Charlie', 'David'], 'Age': [22, 28]})
result = pd.concat([df1, df2])

样式设置

在 Pandas 中通过 Styler 类进行样式设置，可以在写入文件时使用。

# 使用样式设置
styled_df = df.style.highlight_max(axis=0)  # 高亮最大值
styled_df.to_excel('styled_output.xlsx', engine='openpyxl')  # 需要指定引擎

数据验证

Pandas 本身不直接提供数据验证的功能，但可以使用 openpyxl 或 xlsxwriter 进行进一步的样式和数据验证。

import pandas as pd

# 假设已有 DataFrame df
data = {
   
    'Names': ['Alice', 'Bob', 'Charlie'],
    'Ages': [24, 30, 'thirty']
}
df = pd.DataFrame(data)

# 数据验证（过滤掉不合格的年龄）
df['Ages'] = pd.to_numeric(df['Ages'], errors='coerce')  # 转为数值类型，不合格的设置为 NaN
valid_ages = df[df['Ages'].notnull()]

公式支持

Pandas 自身不支持添加公式，但可以通过 openpyxl 来实现。

import pandas as pd
from openpyxl import load_workbook

# 创建 DataFrame
data = {
   
    'Numbers': [1, 2, 3, 4],
    'Doubles': [2, 4, 6, 8]
}
df = pd.DataFrame(data)

# 写入 Excel
df.to_excel('output_with_formula.xlsx', index=False)