1. 为什么你需要掌握Excel与字典的互转？

如果你经常和Excel打交道，尤其是需要把表格数据拿到Python里处理，或者反过来，把Python里计算好的结果再写回Excel，那你肯定遇到过这样的场景：面对一个几十列、几百行的Excel文件，手动复制粘贴不仅效率低下，还容易出错。这时候，用Python来自动化处理就成了刚需。而在这个过程中，Excel表格和Python的字典（dict） 这两种数据结构之间的转换，可以说是最核心、最高频的操作之一。

我刚开始做数据分析的时候，也总在这件事上踩坑。比如，从Excel读出来的数据，怎么快速变成我熟悉的字典格式，方便后续用键（key）来访问值（value）？又比如，我有一堆字典形式的数据，怎么优雅地、格式漂亮地写进一个新的Excel文件，而不是生成一堆乱七八糟的文本？这些问题，用对了工具和方法，其实几分钟就能搞定。

今天要聊的，就是利用Python里两个非常强大的库——pandas和openpyxl——来解决这个痛点。pandas大家可能比较熟，它读Excel、做数据分析是一把好手；而openpyxl则更专注于Excel文件（尤其是.xlsx格式）的读写和样式编辑，控制力更强。把它们俩结合起来用，一个负责高效读取和初步转换，另一个负责精细化的写入和格式控制，就能实现从Excel到字典，再从字典到Excel的完整、高效的闭环。无论你是要做数据清洗、自动化报表生成，还是搭建一个小型的数据处理流水线，这套组合拳都能让你事半功倍。接下来，我就带你一步步拆解，看看具体怎么操作。

2. 环境准备与工具安装：万事开头易

工欲善其事，必先利其器。在开始写代码之前，我们得先把“厨房”收拾好，把需要的“厨具”备齐。整个过程非常简单，哪怕你是刚接触Python的新手，跟着做也能轻松搞定。

2.1 安装必备的Python库

我们需要安装两个核心库：pandas和openpyxl。打开你的命令行终端（Windows上是CMD或PowerShell，Mac或Linux上是Terminal），输入以下命令：

pip install pandas openpyxl

如果你用的是Anaconda环境，也可以用conda来安装：

conda install pandas openpyxl

这里简单解释一下这两个库的分工。pandas是一个功能极其强大的数据分析库，它内置的read_excel函数可以非常智能地读取Excel文件，并将其转换为DataFrame——这是一种类似表格的二维数据结构，处理起来非常方便。而openpyxl则是一个专门用于读写Excel 2010 xlsx/xlsm/xltx/xltm文件的库，它对Excel文件的支持非常底层和全面，可以精确控制单元格格式、公式、图表等。在我们的场景里，用pandas来读（因为它简单），用openpyxl来写（因为它灵活），算是各取所长。

安装过程通常很快。安装完成后，你可以在Python环境中导入它们试试，不报错就说明成功了：

import pandas as pd
from openpyxl import Workbook
print("库导入成功！")

2.2 准备一个示例Excel文件

为了让大家有更直观的感受，我建议你自己创建一个简单的Excel文件来跟着操作。你可以打开Excel或者WPS，创建一个包含以下几列数据的表格，并保存为sample_data.xlsx：

序号产品名称销售额成本
1产品A150008000
2产品B2200012000
3产品C95006000
4产品D1800010000

第一行是表头，下面四行是具体数据。把这个文件放在你方便找到的目录下，比如和你的Python脚本放在同一个文件夹里。记住这个文件路径，我们待会儿会用到。当然，你也可以直接用我后面代码里演示的路径，但最好自己动手创建一个，这样理解会更深刻。

3. 从Excel到字典：用pandas轻松读取与转换

现在，我们进入正题的第一部分：如何把Excel表格里的数据，变成Python里方便操作的字典。原始文章里给出的方法是一个基础循环，但用pandas，我们有更高效、更“Pythonic”的做法。

3.1 使用pandas读取Excel文件

首先，我们用pandas的read_excel函数把文件读进来。这个函数非常强大，参数也很多，但我们先从最常用的开始。

import pandas as pd

# 指定你的Excel文件路径
file_path = './sample_data.xlsx'  # 假设文件在当前目录下

# 读取Excel，header=0表示第一行作为列名（表头）
raw_data = pd.read_excel(file_path, header=0)

# 看看读进来的是什么
print("读取的DataFrame内容：")
print(raw_data)
print(f"\n数据类型：{type(raw_data)}")

运行这段代码，你会看到类似这样的输出：

读取的DataFrame内容：
   序号 产品名称   销售额    成本
0   1   产品A  15000   8000
1   2   产品B  22000  12000
2   3   产品C   9500   6000
3   4   产品D  18000  10000

数据类型：<class 'pandas.core.frame.DataFrame'>

看，pandas不仅把数据读进来了，还自动把第一行“序号”、“产品名称”等识别成了列名，并且给每一行加了一个从0开始的索引（最左边那列）。这个raw_data是一个DataFrame对象，你可以把它想象成一个增强版的、带标签的二维表格，是pandas进行所有数据分析的基石。

3.2 将DataFrame转换为字典的几种高效方法

原始文章里通过循环raw_data.values来构建字典，方法可行，但不够简洁。pandas的DataFrame本身提供了好几种直接转换成字典的方法，更推荐使用。

方法一：to_dict()方法，按行转换为记录列表 这是最常用的一种转换，得到的字典列表（list of dicts）格式，非常符合直觉，每一行数据变成一个字典。

# 使用‘records’参数，将每一行转换为一个字典，所有字典组成一个列表
dict_list = raw_data.to_dict(orient='records')
print("转换后的字典列表：")
print(dict_list)

输出会是：

转换后的字典列表：
[{'序号': 1, '产品名称': '产品A', '销售额': 15000, '成本': 8000},
 {'序号': 2, '产品名称': '产品B', '销售额': 22000, '成本': 12000},
 {'序号': 3, '产品名称': '产品C', '销售额': 9500, '成本': 6000},
 {'序号': 4, '产品名称': '产品D', '销售额': 18000, '成本': 10000}]

这种格式特别适合后续进行逐行处理，比如用for item in dict_list:来遍历每一条记录。

方法二：to_dict()方法，按列转换为字典 有时候我们需要按列来组织数据，比如想快速获取所有“产品名称”的列表。

# 使用‘list’参数，将每一列转换为一个键值对，值是该列所有数据的列表
dict_by_column = raw_data.to_dict(orient='list')
print("\n按列转换的字典：")
print(dict_by_column)

输出：

按列转换的字典：
{'序号': [1, 2, 3, 4],
 '产品名称': ['产品A', '产品B', '产品C', '产品D'],
 '销售额': [15000, 22000, 9500, 18000],
 '成本': [8000, 12000, 6000, 10000]}

方法三：构建自定义映射字典（类似原始文章） 如果你想像原始文章那样，用某一列的值作为键，另一列的值作为值，构建一个一对一的映射字典，可以这样做：

# 假设我们想以‘产品名称’为键，‘销售额’为值
mapping_dict = pd.Series(raw_data.销售额.values, index=raw_data.产品名称).to_dict()
print("\n自定义映射字典（产品->销售额）：")
print(mapping_dict)

输出：

自定义映射字典（产品->销售额）：
{'产品A': 15000, '产品B': 22000, '产品C': 9500, '产品D': 18000}

这里用到了pd.Series，你可以把它看作一个带索引的一维数组。我们把“产品名称”列设为索引，“销售额”列设为值，然后直接调用.to_dict()就得到了我们想要的映射关系。这种方法比写循环清晰多了。

提示：to_dict()方法还有其他几种orient参数，比如‘index’、‘split’、‘tight’等，分别适用于不同的场景。当你需要把DataFrame和其他工具交换数据时，可以查阅官方文档选择最合适的格式。

3.3 处理读取时的常见问题与坑

在实际操作中，你可能会遇到原始文章没提到的一些问题，这里我分享几个踩过的坑和解决办法。

坑一：文件路径问题。 Windows下的路径反斜杠\在Python字符串里是转义字符，直接写C:\Users\data.xlsx可能会出错。有三种解决方法：

  1. 使用原始字符串：file_path = r‘C:\Users\data.xlsx’
  2. 使用正斜杠：file_path = ‘C:/Users/data.xlsx’ (Windows也认)
  3. 使用双反斜杠：file_path = ‘C:\\Users\\data.xlsx’

坑二：表头不在第一行。 如果你的Excel文件第1行是空行或者说明，真正的表头在第3行，可以设置header=2（注意索引从0开始）。

坑三：读取指定工作表。 一个Excel文件可能有多个工作表（Sheet），read_excel默认读第一个。要读名为‘Sheet2’的工作表，可以加参数：pd.read_excel(file_path, sheet_name=‘Sheet2’)。

坑四：处理缺失值。 Excel里的空单元格，pandas读进来会变成NaN（Not a Number）。如果你后续的字典处理不接受NaN，可以在读取时或转换后进行处理，比如用fillna(0)填充为0。

把这些细节处理好，你的数据读取环节就会非常稳健。接下来，我们看看怎么把字典数据漂亮地写回Excel。

4. 从字典到Excel：用openpyxl进行精细化写入

数据在Python里处理完了，最终往往还是要以Excel的形式交付出去，因为这是业务方最熟悉的格式。原始文章介绍了用openpyxl写入的基本步骤，这里我会展开更多实用技巧，让你的输出文件不仅数据正确，而且格式美观、专业。

4.1 基础写入：从字典列表到Excel表格

我们先复现并深化原始文章的例子。假设我们有一个字典列表，每个字典代表一行数据。

from openpyxl import Workbook

# 我们的数据，一个字典列表
sales_data = [
    {"月份": "一月", "产品A": 15000, "产品B": 9000, "产品C": 12000},
    {"月份": "二月", "产品A": 18000, "产品B": 9500, "产品C": 11000},
    {"月份": "三月", "产品A": 22000, "产品B": 12000, "产品C": 15000},
]

# 1. 创建一个工作簿
workbook = Workbook()

# 2. 获取默认的活动工作表
sheet = workbook.active
sheet.title = "季度销售报告"  # 给工作表起个名字

# 3. 写入表头。我们需要从字典的键中提取表头，但要确保顺序。
# 假设我们想要‘月份’在第一列，然后是产品A、B、C。
headers = ["月份", "产品A", "产品B", "产品C"]
sheet.append(headers)

# 4. 逐行写入数据
for row_data in sales_data:
    # 按照headers的顺序，从每个字典中取出对应的值，组成一个列表
    row = [row_data[col] for col in headers]
    sheet.append(row)

# 5. 保存工作簿到文件
output_path = "./季度销售报告.xlsx"
workbook.save(output_path)
print(f"文件已保存至：{output_path}")

执行这段代码，你就会在当前目录下得到一个名为“季度销售报告.xlsx”的文件，打开后数据整整齐齐。这里的关键点在于表头的顺序控制。字典本身是无序的（Python 3.7后插入有序，但显式指定顺序仍是好习惯），我们通过预先定义headers列表，确保了写入Excel时列的顺序符合我们的要求。

4.2 进阶技巧：样式美化与多工作表操作

如果只是导出数据，上面那样就够了。但如果你想生成一份可以直接拿去汇报的报表，加点样式会显得更专业。openpyxl的样式功能非常丰富。

from openpyxl.styles import Font, Alignment, Border, Side, PatternFill
from openpyxl.utils import get_column_letter

# ... 沿用之前创建workbook和sheet，并写入数据的代码 ...

# 6. 定义样式
#   表头字体加粗、居中、背景色
header_font = Font(bold=True, color="FFFFFF")  # 白色加粗字体
header_fill = PatternFill(start_color="366092", end_color="366092", fill_type="solid")  # 蓝色填充
alignment_center = Alignment(horizontal="center", vertical="center")
thin_border = Border(left=Side(style='thin'),
                     right=Side(style='thin'),
                     top=Side(style='thin'),
                     bottom=Side(style='thin'))

# 7. 应用样式到表头行（第一行）
for col_num, header in enumerate(headers, start=1):
    cell = sheet.cell(row=1, column=col_num)
    cell.font = header_font
    cell.fill = header_fill
    cell.alignment = alignment_center
    cell.border = thin_border

# 8. 应用样式和数据边框到数据行
for row in sheet.iter_rows(min_row=2, max_row=sheet.max_row, min_col=1, max_col=sheet.max_column):
    for cell in row:
        cell.alignment = Alignment(horizontal="center")  # 数据居中
        cell.border = thin_border  # 加上边框

# 9. 自动调整列宽（近似）
for column in sheet.columns:
    max_length = 0
    column_letter = get_column_letter(column[0].column)  # 获取列字母
    for cell in column:
        try:
            if len(str(cell.value)) > max_length:
                max_length = len(str(cell.value))
        except:
            pass
    adjusted_width = (max_length + 2)
    sheet.column_dimensions[column_letter].width = adjusted_width

# 保存
workbook.save("./美化后的季度报告.xlsx")

现在打开新生成的文件，你会发现表头有了醒目的蓝色背景和白色粗体字，所有单元格都居中对齐并带有细边框，列宽也根据内容自动调整了，看起来就像精心制作的报表一样。

多工作表操作也很常见。比如，你想把不同品类的数据放到同一个Excel文件的不同Sheet里。

# 假设我们有不同品类的数据
category_a_data = [...]  # 品类A的数据，字典列表格式
category_b_data = [...]  # 品类B的数据

workbook = Workbook()

# 默认Sheet写品类A
sheet_a = workbook.active
sheet_a.title = "品类A"
# ... 写入品类A数据及样式 ...

# 创建新的Sheet写品类B
sheet_b = workbook.create_sheet(title="品类B")
# ... 写入品类B数据及样式 ...

# 可以删除默认创建的多余Sheet（如果有的话）
if "Sheet" in workbook.sheetnames:
    std_sheet = workbook["Sheet"]
    workbook.remove(std_sheet)

workbook.save("./多品类报告.xlsx")

通过这些进阶操作，你基本上就能应对绝大部分数据导出的需求了，从简单的数据转储到复杂的报表生成，都能胜任。

5. 实战案例：构建一个自动化数据处理小脚本

光说不练假把式。我们把前面学到的知识串起来，设计一个完整的、有点实用价值的小案例。假设你是某电商的数据员，每天会收到一份“原始订单.xlsx”，你需要做两件事：1. 统计每个客户的累计消费金额；2. 生成一份格式清晰的“客户消费统计.xlsx”报表。

步骤1：读取并处理原始数据 假设“原始订单.xlsx”文件格式如下：

订单ID客户名消费金额日期

我们用pandas读取并处理。

import pandas as pd

# 读取原始订单数据
orders_df = pd.read_excel('./原始订单.xlsx')

# 看看数据
print("原始订单数据预览：")
print(orders_df.head())

# 按‘客户名’分组，汇总‘消费金额’
customer_summary = orders_df.groupby('客户名')['消费金额'].sum().reset_index()
customer_summary.columns = ['客户名', '累计消费金额']  # 重命名列

# 按消费金额降序排列
customer_summary = customer_summary.sort_values(by='累计消费金额', ascending=False)

# 转换为字典列表格式，方便后续openpyxl处理
summary_dict_list = customer_summary.to_dict(orient='records')

print("\n客户消费统计：")
print(customer_summary)

步骤2：用openpyxl生成美化报表 现在我们把summary_dict_list这个字典列表，写入一个新的Excel文件，并加上排名、百分比等列。

from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, Border, Side, PatternFill, numbers

# 创建新工作簿
wb = Workbook()
ws = wb.active
ws.title = "客户消费排名"

# 定义更详细的表头
headers = ["排名", "客户名", "累计消费金额", "占比(%)", "等级"]

# 写入表头
ws.append(headers)

# 计算总金额，用于后续计算占比
total_amount = customer_summary['累计消费金额'].sum()

# 写入数据行，并计算排名和占比
for idx, record in enumerate(summary_dict_list, start=1):
    customer_name = record['客户名']
    amount = record['累计消费金额']
    percentage = (amount / total_amount) * 100

    # 简单分级逻辑
    if percentage > 20:
        level = "VIP"
    elif percentage > 5:
        level = "重要"
    else:
        level = "普通"

    row = [idx, customer_name, amount, percentage, level]
    ws.append(row)

# --- 应用样式（精简版示例）---
# 表头样式
header_fill = PatternFill(start_color="C5D9F1", end_color="C5D9F1", fill_type="solid")  # 浅蓝填充
for col in range(1, len(headers) + 1):
    cell = ws.cell(row=1, column=col)
    cell.font = Font(bold=True)
    cell.fill = header_fill
    cell.alignment = Alignment(horizontal="center")

# 设置金额和百分比列的格式
for row in ws.iter_rows(min_row=2, max_row=ws.max_row, min_col=3, max_col=4):
    for cell in row:
        cell.number_format = numbers.FORMAT_NUMBER_COMMA_SEPARATED1 if cell.column == 3 else numbers.FORMAT_PERCENTAGE_00

# 自动调整列宽
for column_cells in ws.columns:
    length = max(len(str(cell.value)) for cell in column_cells)
    ws.column_dimensions[column_cells[0].column_letter].width = length + 2

# 保存文件
output_file = "./客户消费统计.xlsx"
wb.save(output_file)
print(f"\n报表已生成：{output_file}")

运行这个脚本，你就能一键完成从原始数据到分析报表的全过程。你可以把这个脚本设置为定时任务（比如用Windows的任务计划或Linux的cron），实现真正的日报自动化。这个案例融合了pandas的数据分组聚合能力和openpyxl的格式化输出能力，体现了二者结合的优势。

6. 性能考量与最佳实践

当数据量变大时，比如要处理几万甚至几十万行数据，我们就需要考虑效率和内存问题了。这里分享一些我积累的经验。

关于读取性能： pandas的read_excel在读取非常大的Excel文件时，可能会比较慢且耗内存。你可以尝试以下方法：

  1. 指定列：如果只需要其中几列，使用usecols参数，例如pd.read_excel(file, usecols=[‘A’, ‘C’, ‘E’])或usecols=‘A:C,E’，能显著减少内存占用和读取时间。
  2. 分块读取：对于超大型文件，pandas本身没有很好的分块读取Excel功能。一个变通方法是先借助openpyxl进行一些预处理，或者考虑将Excel文件转换为CSV再用pandas的chunksize读取。但对于日常几兆到几十兆的Excel文件，read_excel直接读通常没问题。
  3. 引擎选择：read_excel默认可能使用openpyxl或xlrd引擎。对于.xlsx文件，确保安装并使用了openpyxl。

关于写入性能： 用openpyxl的append方法逐行写入，对于几万行数据是很快的。但如果数据量极大（数十万行），连续调用append也可能变慢。这时可以考虑：

  1. 批量操作单元格：对于超大数据，可以先将数据组装成一个大的列表，然后直接赋值给一片单元格区域，但这需要更精细的坐标计算。对于绝大多数场景，append足够了。
  2. 内存模式：openpyxl提供了只写模式（write_only=True），在创建Workbook时指定。这种模式下，数据是流式写入磁盘的，内存占用极小，非常适合生成超大型文件。但代价是不能回头修改已写入的内容，也不能使用很多样式功能。除非你确定要生成一个上百万行的、无复杂格式的文件，否则一般用默认模式就好。
from openpyxl import Workbook
wb = Workbook(write_only=True)  # 启用只写模式
ws = wb.create_sheet()
# 然后只能使用 ws.append(row) 来添加行，不能使用cell赋值或样式

最佳实践总结：

  1. 明确分工：pandas主内（数据处理与分析），openpyxl主外（格式控制与写入）。这是核心思路。复杂的计算、筛选、分组用pandas；对单元格样式、公式、多Sheet有严格要求时用openpyxl。
  2. 善用to_dict：在pandas内部完成数据到字典的转换，比手动循环高效且不易错。
  3. 样式模板化：如果你经常要生成格式固定的报表，可以把样式设置代码封装成函数，甚至将带有格式的Excel文件作为模板，用openpyxl加载模板后只填充数据，这样效率更高。
  4. 路径管理：将输入输出路径、Sheet名称等配置信息放在脚本开头或单独的配置文件中，方便修改和维护。
  5. 异常处理：在实际脚本中，记得用try...except包裹文件读写操作，并给出友好的错误提示，比如文件不存在、格式错误等。

掌握了这些，你就能游刃有余地处理日常工作中大部分与Excel相关的数据搬运和报表生成任务了。这套方法的关键在于理解每个工具最适合做什么，然后把它们组合起来，形成一条流畅的自动化流水线。

# 自动化
Logo
乐奇 Rokid 开放社区

更多推荐

cover

一期一会 | 什么是抬头显示器（HUD）？

avatar 乐奇 Rokid 开放社区
cover

极刻觅镜 | AI眼镜周报｜Rokid四款硬件发布与Android XR工具转稳定版

avatar 乐奇 Rokid 开放社区
cover

极刻觅镜 | AI眼镜日报｜京东智能眼镜西安站活动与千问AI眼镜OTA升级

avatar 乐奇 Rokid 开放社区