赛博红兔的科技博客

大家好，我是赛博红兔。今天我们要聊聊Python实现Excel自动化的模块openpyxl。可能有人要问，一般处理excel或者csv数据的都是用pandas，为什么我们要学openpyxl呢？大家可以来看看这张两者区别对照表。pandas专注于数据分析和处理，适合大规模数据计算。而openpyxl专注于Excel文件本身的一些操作，适合单元格的格式化、插入图表、公式等等的Excel细节处理。所以在批量处理Excel文件本身的自动化操作时候，也就是平常的办公自动化，学会openpyxl就足够了。

首先，确保你的 Python 环境里安装了 openpyxl。没有的话，直接在命令窗口用pip install openpyxl的指令来安装。

1. 创建 & 保存 Excel

我们先学着用openpyxl来创建一个Excel文件，我们一会可以用它来作为演示文件。我们先导入openpyxl模块。用openpyxl.Workbook()创建一个新的Excel文件，这里wb.active是选中默认的工作表。然后，我们给这个默认的工作表起个名字叫“成绩表”，再放上列表的表头选项，分别是姓名、数学、语文、英语和总分，用append方法加入到工作表里。接下来，用列表的形式把具体列表里的数据循环加入到工作表里，也是用append方法。最后，用save方法把这个Excel文件保存，名字叫data。我们运行一下看一看效果。

import openpyxl

wb = openpyxl.Workbook()

ws = wb.active

ws.title = "成绩表"

headers = ["姓名", "数学", "语文", "英语", "总分"]

ws.append(headers)

data = [

    ["小红", 90, 85, 92, ],

    ["小明", 85, 80, 88, ],

    ["小强", 92, 87, 94, ],

    ["小花", 88, 84, 91, ],

    ["张三", 76, 81, 79, ],

    ["李四", 95, 89, 97, ],

]

for row in data:

    ws.append(row)

wb.save("data.xlsx")

print("data.xlsx已成功创建!")

2. 读取Excel文件

之后，我们来看看怎么来读取这个新建的Excel文件里的数据啊。我们用openpyxl.load_workbook方法打开这个新建文件，然后赋值为wb。接着用wb[“成绩表”]获取我们新建的工作表——成绩表。我们可以用ws[“A1”].value来读取 A1单元格的值，也就是这个姓名。也可以用ws.cell(1, 1).value来读取这个姓名，第一行一列么。你可以选一个喜欢的方式。如果 Excel 里有很多数据，我们需要批量读取，那么iter_rows() 就能派上用场了。我们用了嵌套循环遍历了表格的每一行里的每一个单元格，然后打印出来。这里的values_only=True作用是只返回单元格的数据，而不是整个单元格的对象。可以让程序运行得更加快一些。

wb = openpyxl.load_workbook("data.xlsx")

ws = wb["成绩表"]

# 单元格读取

value = ws["A1"].value

# value = ws.cell(1, 1).value

print("A1单元格的内容:", value)

# 批量读取

for row in ws.iter_rows(values_only=True):

    for cell in row:

        print(cell, end="  ")

    print()

3. 修改或者计算数据

修改某一个单元格的时候，我们可以直接把工作表某一个单元格重新赋值然后保存即可。如果想要批量修改或者计算的时候，就可以用循环来实现。比如，这里我们想要计算所有学生的总分并且填写到“总分”这一项里。可以直接用Excel里的SUM求和公式来写，这块公式的写法其实是和Excel一样的。

ws["A2"] = "赛博红兔"

wb.save("data.xlsx")

print("Excel数据已修改!")

# 遍历每行并计算总分

for row in range(2, ws.max_row + 1):

    ws[f"E{row}"] = f"=SUM(B{row}:D{row})"

wb.save("data.xlsx")

print("总分计算完毕!")

4. 单元格格式化

在 Excel 里，格式化数据是非常重要的，比如说选择字体、加粗、斜体、调整大小，还有背景颜色、字体颜色，单元格对齐之类的。这时候，我们需要从openpyxl.styles里导入Alignment, Font 和 PatternFill。我们可以设置Font里的各种参数来调整字体。比如说这里，我们把第一个单元格的字体改成加粗+斜体+红色的14号字体。通过设置PatternFill里的参数来改单元格背景颜色，这里我们改成黄色背景。通过设置Alignment里的参数来改对齐方式，这里我们改成水平和竖直居中。我们来运行一下看看效果。我们还可以导入Border和Side来修改边框，这里我们就不演示了。

from openpyxl.styles import Alignment, Font, PatternFill

cell = ws["A1"]

cell.font = Font(name="Calibri", bold=True, italic=True,

                 color="FF0000", size=14)

cell.fill = PatternFill(fill_type="solid", fgColor="FFFF00")

cell.alignment = Alignment(horizontal="center", vertical="center")

wb.save("data.xlsx")

print("Excel格式化完成!")

5. 插入图片

接下来我们来看看怎样插入图片。非常的简单！我们先从openpyxl.drawing.image里倒入Image类。我们在这个项目的文件夹里有一张咱们频道logo的图片，我们只需要把它创建成一个Image的实例，然后用add_image方法加入到工作表里，就可以了。一起来看看效果。

from openpyxl.drawing.image import Image

img = Image("logo.png")

ws.add_image(img, "F1")

wb.save("image.xlsx")

print("图片已插入!")

6. 生成Excel图表

数据可视化一直是Excel的核心功能之一，最后我们来看看如何用openpyxl生成图表！我们要先从openpyxl.chart里导入BarChart和Reference类。这里，我给大家展示一下柱状图是怎么画的。我们用BarChart创建一个新的柱状图，然后加上了图的名称”学生总分对比图”和x，y坐标的名称，分别是”学生”和”总分”。用Reference选择第五列也就是总分那一列的第一行到最后一行作为图表的数据。然后用add_data把数据加入到图里。注意这里titles_from_data=True就是告诉它我们选择的数据是包括列表的表头标题的，所以上面我们需要把第一行总分这个表头标题也加到图里去。然后，同样我们用Reference选择第一列的第二行到第七行的姓名作为标签名，用set_categories加入到图里去。最后，我们用add_chart方法把柱状图加入到工作表里，从A9这个单元格也就是原始表格的正下方位置加入柱状图并且保存。我们一起来看看运行之后的效果。怎么样？非常完美的一张总分柱状图啊！

from openpyxl.chart import BarChart, Reference

chart = BarChart()

chart.title = "学生总分对比图"

chart.x_axis.title = "学生"

chart.y_axis.title = "总分"

data_range = Reference(ws, min_col=5, min_row=1, max_row=7)

chart.add_data(data_range, titles_from_data=True)

labels = Reference(ws, min_col=1, min_row=2, max_row=7)

chart.set_categories(labels)

ws.add_chart(chart, "A9")

wb.save("data.xlsx")

print("总分柱状图生成完毕!")

好啦，openpyxl模块就先学到这里！这样一来我们在面对大量同样格式的Excel表格时候一个一个手动去修改了。本频道将同步在B站和油管上更新，如果你喜欢我的内容，请不要忘记点赞、订阅和分享，这样就不会错过我更新的内容，欢迎在评论区提出你的想法和建议。今天就到这里，再见吧！