处理 Excel 文件

By QgeniusPython文档处理

Excel 是产品经理最常用的数据工具之一，我们经常用它来整理需求、汇总数据、生成报告等。然而当 Excel 处理大数据时很容易发生卡顿。或者有时我们需要跨表计算、批量数据处理时，直接使用 Excel 操作往往很繁琐，尤其是要批量处理大量文件时，颇为费时费力。而 Python 能轻松高效处理，避免出错，甚至可实现自动化操作，一键批量完成重复任务。

但是，Python 本身并没有内置的用于直接操作 Excel 文件的标准库。但不用担心，Python 的功能非常强大，除了官方提供的内置库，还可以通过社区开发的第三方库来扩展功能。我们处理 Excel 文件时就经常会使用到： Openpyxl 和 Pandas 这 2 个第三方库。

第三方库是由开发者社区提供，它们虽然功能强大，但它们不是Python 自带的官方库，因此需要单独安装。

在 Python 中，第三方库需要通过 包管理工具 pip （在Mac 中通常用 pip3 来替代）进行安装。

安装方法
1. 打开命令行（Terminal 或 CMD）。
2. 输入以下命令安装库：

  • 安装 Openpyxl：
pip install openpyxl
  • 安装 Pandas：：
pip install pandas

3. 检查安装是否成功：

pip show openpyxl pandas

4. 建议定期更新第三方库，升级使用的命令如下：

pip install --upgrade pandas

使用第三方库时要注意：首先你需要在程序代码中通过 import 语句加载第三方库。例如：

import pandas as pd
import openpyxl

安装第三方库时，还要注意兼容性问题。某些库的功能依赖于特定的 Python 版本或其他库，安装前检查库的文档说明。有时，还会遇到网络问题。如果安装速度慢，可以使用国内镜像源。

使用第三方库时，请结合清晰的注释，帮助团队成员理解代码。此外，建议不要滥用第三方库。对于简单任务，优先使用官方库。例如，读取小型 CSV 文件时，csv 模块可能更简单直接。

Openpyxl 库简介

  • 主要用于操作 Excel 文件（xlsx 格式）。
  • 功能包括创建、读取、写入和格式化 Excel 表格。
  • 适合需要对 Excel 进行表格样式处理的场景，如生成带格式的报告。

示例：

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.title = "数据统计"
ws.append(["用户ID", "反馈"])
ws.append([101, "非常满意"])
wb.save("example.xlsx")

Pandas 库简介

  • 主要用于数据分析和处理。
  • 提供强大的表格数据操作功能，支持 Excel 文件读取和写入。
  • 更擅长批量数据处理、统计和分析。

示例：

import pandas as pd

data = {"用户ID": [101, 102], "反馈": ["非常满意", "需要更多自定义选项"]}
df = pd.DataFrame(data)
df.to_excel("example.xlsx", index=False)

现在我们来了解如何运用 Openpyxl 库来完成 Excel 文件的操作。

1、读取和写入 Excel 文件。
示例：读取并修改文件内容。我们可以尝试用提示词让AI生成代码。

参考提示词：

请生成一个 Python 程序，使用 Openpyxl 库完成以下任务：

  1. 加载名为 feedback.xlsx 的 Excel 文件，并激活默认工作表。
  2. 在工作表的第 1 行第 3 列（即单元格 C1）添加一个新的表头 日期。
  3. 在表格末尾添加一行数据 [103, "性能很好，但偶尔崩溃", "2024-11-19"]。
  4. 将修改后的文件保存为 feedback_modified.xlsx。

要求代码清晰简洁，适合初学者理解和使用，并注重文件保存的正确性。

程序代码：

from openpyxl import load_workbook

wb = load_workbook("feedback.xlsx")
ws = wb.active

# 修改表格内容
ws["C1"] = "日期"
ws.append([103, "性能很好，但偶尔崩溃", "2024-11-19"])

wb.save("feedback_modified.xlsx")

2、自动创建带格式的 Excel 文件

案例：生成用户反馈报告。你可以使用下列提示词来生成代码：

参考提示词：

请生成一个 Python 程序，使用 Openpyxl 库完成以下任务：

  1. 创建一个新的 Excel 文件，并将默认工作表命名为 用户反馈报告。
  2. 在工作表的第 1 行添加表头 用户ID、反馈 和 日期，并将表头的字体设置为加粗。
  3. 在表格中添加以下数据：
    • [101, "非常满意", "2024-11-18"]
    • [102, "需要更多自定义选项", "2024-11-18"]
  4. 将最终的文件保存为 feedback_report.xlsx。

要求代码清晰易读，适合初学者理解，同时展示如何使用 Openpyxl 进行单元格样式设置（如加粗）。

说明：在用户反馈报告中你可能要设置表格中的单元格样式，比如设置字体（Font）、填充颜色（PatternFill）、边框（Border）、对齐方式。我们可以用到 Openpyxl 提供的 styles 模块。其中，Font 是 styles 模块中的一个类，用于定义字体的样式。

程序代码：

from openpyxl import Workbook
from openpyxl.styles import Font

wb = Workbook()
ws = wb.active
ws.title = "用户反馈报告"

# 添加表头并设置加粗
ws.append(["用户ID", "反馈", "日期"])
for cell in ws[1]:
    cell.font = Font(bold=True)

# 添加数据
data = [
    [101, "非常满意", "2024-11-18"],
    [102, "需要更多自定义选项", "2024-11-18"],
]
for row in data:
    ws.append(row)

wb.save("feedback_report.xlsx")

接下来我们了解如何使用 Pandas 操作 Excel 文件。

1、快速读取和分析数据

示例：读取反馈数据并统计关键词出现次数，我们可以用提示词让AI生成代码。

参考提示词：

请生成一个 Python 程序，使用 Pandas 库来完成以下任务：

  1. 读取名为 feedback.xlsx 的 Excel 文件。
  2. 假设文件中有一列名为 “反馈”，程序需要统计其中包含关键词“满意”的条目数量。
  3. 打印输出统计结果，格式为：包含‘满意’的反馈数量：<数量>。

要求代码简洁易懂，适合初学者，并确保考虑可能需要处理中文内容的情况。

程序代码：

import pandas as pd

# 读取 Excel 文件
df = pd.read_excel("feedback.xlsx")

# 统计包含 "满意" 的反馈数量
satisfied_count = df["反馈"].str.contains("满意").sum()
print(f"包含‘满意’的反馈数量：{satisfied_count}")

2、批量处理多个文件

案例：合并多个 Excel 文件。在这里我们会用到 glob 模块。 glob 是 Python 内置的文件操作模块，用于查找符合特定文件名模式的路径名。它支持使用通配符（如 * 和 ?）来匹配文件或目录，能够显著简化文件路径获取的代码量，增强程序的灵活性和自动化能力，非常适合需要处理多个文件的场景。你可以用下列提示词来生成代码：

参考提示词：

请生成一个 Python 程序，使用 Pandas 库和 glob 模块来完成以下任务：

  1. 扫描名为 data 的文件夹，查找其中所有扩展名为 .xlsx 的 Excel 文件。
  2. 读取这些 Excel 文件的内容，并将它们合并为一个完整的表格数据（忽略索引，重新编号）。
  3. 将合并后的表格数据保存为一个新文件，文件名为 merged_feedback.xlsx。
  4. 程序运行完成后，打印消息 文件已成功合并！。

要求代码简洁易懂，适合初学者，能高效处理多个文件的数据合并任务。

程序代码：

import pandas as pd
import glob

# 读取所有 Excel 文件
file_list = glob.glob("data/*.xlsx")
df_list = [pd.read_excel(file) for file in file_list]

# 合并所有文件数据
merged_df = pd.concat(df_list, ignore_index=True)

# 保存合并后的文件
merged_df.to_excel("merged_feedback.xlsx", index=False)
print("文件已成功合并！")

3、数据清理与重命名

案例：清理用户反馈数据。你可以用下列提示词来生成代码：

参考提示词：

请生成一个 Python 程序，使用 Pandas 库来完成以下任务：

  1. 读取名为 feedback.xlsx 的 Excel 文件。
  2. 对数据进行清理：
    • 删除文件中的空值行。
    • 删除文件中的重复行。
  3. 将表格中的列名 用户ID 和 反馈 分别重命名为 User ID 和 Feedback。
  4. 将清理后的数据保存到一个新文件中，文件名为 cleaned_feedback.xlsx，保存时不需要索引。

要求代码简洁清晰，适合初学者理解和使用。

程序代码：

import pandas as pd

df = pd.read_excel("feedback.xlsx")

# 去除空值和重复数据
df = df.dropna()  # 删除空行
df = df.drop_duplicates()  # 删除重复行

# 重命名列
df = df.rename(columns={"用户ID": "User ID", "反馈": "Feedback"})

# 保存清理后的文件
df.to_excel("cleaned_feedback.xlsx", index=False)

现在再来看几个产品经理日常工作中的案例。

案例 1：自动生成日报

任务描述：根据当天的用户反馈生成一份带格式的日报，包含用户 ID、反馈内容和统计数据。

提示词如下：

参考提示词：

请为以下任务生成一个 Python 程序，使用 Openpyxl 和 datetime 库完成需求：

任务描述：自动生成日报

  1. 创建一个新的 Excel 文件，并将默认工作表命名为 用户反馈日报。
  2. 在工作表的第一行添加表头，包括 用户ID、反馈 和 日期。
  3. 使用 Python 的 datetime 模块，生成当天的日期。
  4. 向表格中添加以下用户反馈数据，每条数据附加当天日期：
    • [101, "非常满意", <当天日期>]
    • [102, "需要更多自定义选项", <当天日期>]
  5. 将生成的日报保存为 daily_report.xlsx。
  6. 打印消息 日报已生成！ 提示用户任务完成。

要求代码清晰易懂，适合初学者理解，同时展示如何结合 Openpyxl 和 datetime 进行自动化日报生成。

程序代码：

from openpyxl import Workbook
from datetime import datetime

# 创建 Excel 文件
wb = Workbook()
ws = wb.active
ws.title = "用户反馈日报"

# 添加表头
ws.append(["用户ID", "反馈", "日期"])
feedbacks = [
    [101, "非常满意", datetime.now().strftime("%Y-%m-%d")],
    [102, "需要更多自定义选项", datetime.now().strftime("%Y-%m-%d")],
]
for row in feedbacks:
    ws.append(row)

# 保存文件
wb.save("daily_report.xlsx")
print("日报已生成！")

案例 2：跨部门数据整合

任务描述：合并市场部和运营部的用户数据，清理重复项，生成一份全公司用户清单。

提示词如下：

参考提示词：

请为以下任务生成一个 Python 程序，使用 Pandas 库完成需求：

任务描述：跨部门数据整合

  1. 从 Excel 文件中分别读取市场部和运营部的用户数据，文件名为 market.xlsx 和 operations.xlsx。
  2. 将两个部门的数据合并为一个表格，并清理重复的用户记录。
  3. 将最终的合并结果保存为一个新文件，文件名为 company_users.xlsx。
  4. 打印消息 全公司用户清单已生成！ 提示任务完成。

要求代码简洁易懂，适合初学者，展示如何使用 Pandas 的 read_excel、concat 和 drop_duplicates 方法进行数据整合和清理。

程序代码：

import pandas as pd

# 读取两个部门的数据
market_df = pd.read_excel("market.xlsx")
ops_df = pd.read_excel("operations.xlsx")

# 合并数据并去重
combined_df = pd.concat([market_df, ops_df]).drop_duplicates()

# 保存清单
combined_df.to_excel("company_users.xlsx", index=False)
print("全公司用户清单已生成！")

案例 3：自动化分析用户行为

任务描述：从行为日志中提取用户点击次数，生成行为统计报告。

解决方案：

参考提示词：

请为以下任务生成一个 Python 程序，使用 Pandas 库完成需求：

任务描述：自动化分析用户行为

  1. 从名为 user_logs.xlsx 的 Excel 文件中读取用户行为日志数据，日志文件中包含 User ID 和 Clicks 等字段。
  2. 根据 User ID 对点击次数进行分组统计，计算每个用户的总点击次数。
  3. 将统计结果保存为一个新的 Excel 文件，文件名为 click_report.xlsx。
  4. 打印消息 行为统计报告已生成！ 提示任务完成。

要求代码清晰易懂，适合初学者，展示如何使用 Pandas 的 read_excel、groupby 和 to_excel 方法完成数据分组统计和导出任务。

程序代码：

import pandas as pd

# 读取用户行为数据
df = pd.read_excel("user_logs.xlsx")

# 统计每个用户的点击次数
click_stats = df.groupby("User ID")["Clicks"].sum().reset_index()

# 保存统计报告
click_stats.to_excel("click_report.xlsx", index=False)
print("行为统计报告已生成！")

练习：

  1. 使用 Pandas 读取 feedback.xlsx 文件，筛选出所有包含 “but” 的反馈，并保存为新文件。
  2. 使用 Openpyxl 创建一个带有表头加粗和数据居中的 Excel 文件。
  3. 合并 2023_Q1.xlsx 和 2023_Q2.xlsx 数据，生成年度统计报告。

你可以自己尝试撰写，也可以设计自己的提示词，让大语言模型帮你完成这3个编程任务。试着比较你们写的内容有什么不同。

AI 助教

提示：您可在此提出学习中遇到的问题。回答由 AI 生成，可能存在错误，请注意甄别。