使用Python、Box SDK和OpenPyXL自动化Box中的Excel工作流程

在许多组织中，MS Excel仍然是存储和共享结构化数据的首选工具，无论是跟踪项目进度、管理审计日志还是维护员工或资源详细信息。然而，一个令人惊讶的常见挑战依然存在：数据仍然在手动复制和更新。

跨不同职能的团队，特别是管理和DevOps团队，经常发现自己手动并重复地将数据从一个源输入或同步到Excel电子表格中。这不仅耗费时间，还引入了错误和不一致的可能性。

例如：

定期从项目板获取数据到Excel以跟踪进度的经理
跟踪跨环境资源利用率的DevOps工程师
需要将内部工具中的日志同步到Box中存储的Excel表格以进行合规性审查的审计员

这些任务非常适合自动化。

Box是许多组织用来存储与项目和员工相关的文件的流行云存储提供商。借助Box Python SDK、openpyxl和pandas等现代工具，可以直接在代码中读写Excel文件，无需下载或手动编辑。

在这篇博客文章中，我们将探讨如何：

使用Python连接到Box
读取存储在Box中的Excel文件
更新和追加新数据到工作表
将更新后的文件上传回Box

这个工作流程对于需要跨系统保持操作数据同步的DevOps、SRE和团队负责人特别有用，或者对于希望自动化常规Excel更新的经理和分析师也很有用。

设置Box应用

步骤1：创建Box开发者应用

转到Box开发者控制台
点击"创建新应用"
选择"自定义应用"作为应用类型
为应用命名并提供描述
选择"使用JWT的OAuth 2.0（服务器身份验证）"

这将生成一个可以使用服务器端身份验证与您的Box帐户交互的新应用程序。

步骤2：配置应用权限

创建应用后：

转到应用的配置选项卡
在"应用程序范围"下，启用：
- 读取和写入存储在Box中的所有文件和文件夹

重要提示：配置后，您需要将应用提交给Box管理员授权（如果您的帐户是Box Enterprise的一部分）。在获得批准之前，API调用将受到限制或被拒绝。

步骤3：生成并下载配置文件

回到配置选项卡：

向下滚动到"应用凭据"部分
点击"生成公钥/私钥对"
将下载一个配置JSON文件，其中包含：
- 客户端ID和密钥
- JWT公钥ID
- 私钥（用于签名JWT）
- 私钥的密码
- 企业ID

步骤4：与Box应用自动化用户共享Box文件夹/文件

转到应用的常规设置
在"服务帐户ID"下，复制自动化用户ID
与此自动化用户ID共享box文件夹/文件。这将授予box应用读写权限

请妥善保管此文件并保持私密——它授予完整的API访问权限。

安装所需库

在深入代码之前，让我们安装所需的Python库。这些将启用与Box的交互和Excel文件的操作。

1

pip install boxsdk openpyxl pandas

以下是每个库功能的简要概述：

boxsdk：Box API的官方Python SDK。它允许您使用Box开发者API进行身份验证、访问文件、上传/下载内容和管理文件夹
openpyxl：一个强大的库，用于在Python中读写Excel .xlsx文件。它允许您处理单个单元格、公式、样式和工作表
pandas：Python中的数据分折库。当您想要使用DataFrames以表格格式处理或过滤Excel数据时非常有用

在Python中与Box进行身份验证

要通过SDK与Box交互，您首先需要对应用程序进行身份验证。

1
2
3
4


def init_box_client():
    auth = JWTAuth.from_settings_file('<path/to/config.json>')
    client = Client(auth)
    return client

身份验证后，客户端现在可以访问您的应用具有权限的任何文件、文件夹或元数据（是的，您需要将创建的应用添加为您要自动化的文件的编辑器）。

从Box读取Excel文件

身份验证后，您可以通过文件ID或搜索文件名来访问Box中的Excel文件。

1. 通过ID访问文件

ID是box文件URL的最后部分。

1

box_file = client.file(file_id)

2. 使用BytesIO下载到内存

您不需要将文件写入磁盘。可以直接将其加载到内存中：

1
2
3
4
5
6
7


import io
from openpyxl import load_workbook

file_stream = io.BytesIO()
box_file.download_to(file_stream)
file_stream.seek(0)  # 回到开头
workbook = load_workbook(file_stream)

3. 访问特定工作表

1

sheet = workbook["Sheet1"]

或动态获取第一个工作表：

1

sheet = workbook.active

4. 读入Pandas DataFrame（可选）

如果您更喜欢使用pandas进行数据分析：

1
2
3
4


import pandas as pd

file_stream.seek(0)  # 确保指针在开头
df = pd.read_excel(file_stream, sheet_name="Sheet1")

总体而言，读取Excel函数将如下所示：

1
2
3
4
5
6
7
8
9


def load_excelsheet_from_box(client, id, sheet_name):
    box_file_ref = client.file(id)
    box_file_metadata = box_file_ref.get()
    file_stream = io.BytesIO()
    box_file_ref.download_to(file_stream)
    file_stream.seek(0)
    workbook = load_workbook(file_stream)
    sheet = workbook[sheet_name]
    return [box_file_ref, box_file_metadata, workbook, sheet]

处理Excel数据

加载Excel工作表后，您现在可以使用openpyxl或pandas读取和操作行。

1. 使用openpyxl迭代行

1
2


for row in sheet.iter_rows(min_row=2, values_only=True):
    print(row)

这将从第二行开始打印数据（假设第1行是标题）。

2. 访问标题和过滤

您可以使用以下方式提取标题：

1

headers = [cell.value for cell in sheet[1]]

您可以使用它来过滤或映射列位置。

3. 使用pandas进行繁重处理

如果要过滤、透视或合并数据，请将其加载到DataFrame中：

1
2


df = pd.read_excel(file_stream)
filtered_df = df[df["Status"] == "Active"]

在处理大型Excel工作表或进行分析时，这种方法非常强大。

将更改保存回Box

修改Excel工作簿后，下一步是保存更新后的文件并将其上传回Box。

1. 保存到内存

我们使用BytesIO避免写入磁盘：

1
2
3


updated_stream = io.BytesIO()
workbook.save(updated_stream)
updated_stream.seek(0)

2. 上传到Box

Box允许您使用文件ID替换文件内容：

1

client.file(file_id).update_contents_with_stream(updated_stream)

这将在Box中创建文件的新版本，保留版本历史记录，并允许协作而不会丢失旧数据的风险。

端到端示例：同步缺失名称

这是一个完整的示例，演示了从Box读取、检查缺失名称、追加新条目并将更新后的文件上传回Box。

用例

假设您要跟踪所有正在加入您平台的员工的详细信息。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40


from boxsdk import Client, JWTAuth
from openpyxl import load_workbook
import io

# 步骤1：身份验证
auth = JWTAuth.from_settings_file("box_config.json")
client = Client(auth)

# 步骤2：从Box下载Excel文件
file_id = "1234567890"
box_file = client.file(file_id)
file_stream = io.BytesIO()
box_file.download_to(file_stream)
file_stream.seek(0)

# 步骤3：加载并读取Excel工作表
workbook = load_workbook(file_stream)
sheet = workbook["Sheet1"]
existing_names = {
    (row[0] or "").strip().lower()
    for row in sheet.iter_rows(min_row=2, values_only=True)
    if row[0]
}

# 示例新数据。这应该来自您的平台API。
new_rows = [
    {"name": "Suresh", "id": "A001", "owner": "Team A"},
    {"name": "Ramesh", "id": "B002", "owner": "Team B"},
]
# 步骤4：追加新条目
for entry in new_rows:
    name = (entry.get("name") or "").strip().lower()
    if name and name not in existing_names:
        sheet.append([entry["name"], entry.get("id", ""), entry.get("owner", "")])

# 步骤5：保存并重新上传
updated_stream = io.BytesIO()
workbook.save(updated_stream)
updated_stream.seek(0)
box_file.update_contents_with_stream(updated_stream)

结论

使用Box自动化Excel任务可以每周节省数小时的手动工作。无论您是同步报告的管理团队还是跟踪基础设施资源的DevOps团队，此工作流程都有助于：

减少人为错误
提高效率
确保数据一致性

通过结合Box SDK、openpyxl和pandas，您解锁了一套强大的工具来在云中操作Excel文件，甚至无需打开UI。从每日报告到审计跟踪，一旦设置完成，这些自动化将改变您的生产力。

您可以参考Box文档以获取有关可用Box API的详细信息：

使用Python、Box SDK和OpenPyXL自动化Excel工作流

本文详细介绍如何使用Python、Box SDK和OpenPyXL自动化Excel工作流程，包括连接Box、读取Excel文件、更新数据并重新上传，帮助团队减少手动操作错误，提高数据同步效率。