Python自动化办公之 Excel 自动绘制图表

昨晚十一点多吧，我还在公司楼下抽烟呢，我们组那个小李微信给我轰炸：“哥，明早九点要给老板看周报，Excel 里图表还没画，手都快抽筋了……”我当时就笑了，我说你别画了，画了也不一定对，改个数据你还得重新拖一遍范围，最烦的是老板临时说“把折线换成柱状再叠个均线”，你就直接原地去世，对吧。

然后我就顺手回了他一句：用 Python 让 Excel 自己长图表，数据一更新，图表跟着更新，你人就只负责喝咖啡就行。说完我自己也困死了，但还是把代码扔给他了……嗯，今天就把这个事儿掰扯一下，顺便把我踩过的坑也唠两句，省得你们跟我一样半夜骂 Excel。

你们平时自动化办公，最常见就两种需求嘛：一种是“我从系统导出来一堆数据，想直接生成带图的报表文件”；另一种是“我有个固定模板，帮我把数据塞进去，然后把图表刷新好”。这俩我都给你整个能跑的例子，代码都是我现写的，不是网上复制那种一坨看不懂的。

先说选型啊，别纠结太多：

• 你要生成 xlsx 并且画图表，用 openpyxl 就行（不依赖 Excel 客户端）。
• 你要更花哨的样式、写起来更爽，也可以 xlsxwriter，但它不支持“读已有文件再改”，更偏“从零生成”。 我这边用 openpyxl，因为办公场景经常是拿模板改。

下面这段代码干的事很直白：造一份“日期-销量-成本”的数据，然后自动插入一张“柱状（销量）+ 折线（成本）”的组合图。你让小李明天交周报，就用这个套路，把数据换成真实的就行。

from datetime import date, timedelta
from random import randint

from openpyxl import Workbook
from openpyxl.chart import BarChart, LineChart, Reference
from openpyxl.chart.label import DataLabelList


defbuild_report_xlsx(path: str, days: int = 14) -> None:
    wb = Workbook()
    ws = wb.active
    ws.title = "周报数据"

# 表头
    ws.append(["日期", "销量", "成本"])

# 造点数据：你换成数据库/接口导出的真实数据就行
    start = date.today() - timedelta(days=days - 1)
for i in range(days):
        d = start + timedelta(days=i)
        sales = randint(80, 180)
        cost = int(sales * randint(35, 55) / 100)  # 成本比例瞎写的，别较真哈
        ws.append([d, sales, cost])

# 日期列格式（不然 Excel 里显示可能怪怪的）
for cell in ws["A"][1:]:
        cell.number_format = "yyyy-mm-dd"

# 画柱状图：销量
    bar = BarChart()
    bar.type = "col"
    bar.title = "销量&成本（自动生成）"
    bar.y_axis.title = "数量/金额"
    bar.x_axis.title = "日期"

    data_sales = Reference(ws, min_col=2, min_row=1, max_row=days + 1)  # 含表头
    cats = Reference(ws, min_col=1, min_row=2, max_row=days + 1)       # 不含表头
    bar.add_data(data_sales, titles_from_data=True)
    bar.set_categories(cats)
    bar.dataLabels = DataLabelList()
    bar.dataLabels.showVal = False# 你想每根柱子都显示数字就改 True，但一般会很挤

# 叠一条折线：成本
    line = LineChart()
    data_cost = Reference(ws, min_col=3, min_row=1, max_row=days + 1)
    line.add_data(data_cost, titles_from_data=True)
    line.y_axis.axId = 200# 给它一个不同的轴 ID，避免和柱状轴打架
    line.y_axis.title = "成本"
    line.y_axis.crosses = "max"# 放右侧坐标轴（看起来更像“组合图”）

# 合并成组合图
    bar += line

# 把图放到表右边，别挡数据
    ws.add_chart(bar, "E2")

    wb.save(path)


if __name__ == "__main__":
    build_report_xlsx("周报_自动带图.xlsx", days=14)

你看就这么点东西，小李当时看完说“卧槽还能这样”，我说你别卧槽了，明早别迟到就行…… 这里有几个很容易翻车的小点，我随口说一下哈（我之前真踩过）：

一个是数据类型。你如果从 CSV 读出来，日期可能是字符串，销量成本可能也是字符串，图表会画得很离谱甚至不出图。最稳的做法是：日期转 datetime/date，数字转 int/float，别偷懒。

另一个是引用范围。Reference 里 min_row=1 是为了带上表头，让 titles_from_data=True 能拿到系列名。你要是从第二行开始引用，图表系列名就会变成“Series1”，老板看了还以为你糊弄他。

还有一个很现实的需求：你们公司通常有模板嘛，上面已经摆好了 Logo、格式、页眉页脚、甚至图表位置也固定。那你就要“打开模板 -> 写数据 -> 让图表引用新范围”。 openpyxl 对“刷新已有图表”的支持吧……怎么说呢，有点像我昨晚的精神状态，能用但别指望太智能。更稳的做法是：模板里别放图，或者放也行但我们直接删掉再生成一个新的图，省事。

我给你个“模板写入 + 重建图表”的版本，你就理解成：老板给你一个 template.xlsx，你把数据灌进去，然后把旧图干掉换新图。

from openpyxl import load_workbook
from openpyxl.chart import BarChart, Reference

deffill_template_and_chart(template_path: str, out_path: str, rows: list[tuple]):
"""
    rows: [(日期date/datetime, 销量int, 成本int), ...]
    """
    wb = load_workbook(template_path)
    ws = wb["周报数据"] if"周报数据"in wb.sheetnames else wb.active

# 清空旧数据（保留表头）
    ws.delete_rows(2, ws.max_row)

# 写新数据
for r in rows:
        ws.append(list(r))

    n = len(rows)
if n == 0:
        wb.save(out_path)
return

# 删掉旧图（如果有）
# openpyxl 里图表存在 ws._charts，属于“能用但不优雅”的那种字段…反正就办公嘛
    ws._charts = []

# 重建一个简单柱状图：销量
    chart = BarChart()
    chart.title = "销量（自动更新）"
    chart.y_axis.title = "销量"
    chart.x_axis.title = "日期"

    data = Reference(ws, min_col=2, min_row=1, max_row=n + 1)
    cats = Reference(ws, min_col=1, min_row=2, max_row=n + 1)
    chart.add_data(data, titles_from_data=True)
    chart.set_categories(cats)

    ws.add_chart(chart, "E2")
    wb.save(out_path)

你看，这种写法就很适合“每天导一次数据、每天生成一份图表周报”的场景。你甚至可以再往上套一层：从数据库查一把、或者从钉钉/飞书导出 csv 读一把，最后一键出文件。反正你要真做成脚本，放到定时任务里，早上八点半自动生成，九点会议你就装作一切都是你提前准备的……哎我这话说得有点欠揍，但确实就这样。

对了还有人会问：能不能把图表做得“更像手工做的那种”，什么配色、线条、数据标签、次坐标轴、趋势线……能是能，但 openpyxl 对“趋势线”这种 Excel 高级功能支持一般，你如果特别追求效果，反而建议走“模板里用 Excel 做好图表样式，我们只负责更新数据区域”，这个路线更稳。

我最后再插一句很真实的：自动化办公这玩意，别一上来就追求一键全自动到极致，你先把“重复劳动”干掉就赢一半了。像小李这种，周报每周都一模一样，图表每周都一样，就差数据不一样，那你还手搓……那真是跟自己过不去。

行了我这会儿又有点困了，昨晚那根烟抽完我就该回去了，结果还在那儿给他改代码……算了不说了，我去看看群里谁又把环境弄炸了。