背景介绍
大家好。之前给大家介绍pandas+openpyxl+calamine: 利用python轻松操作excel类文件(写入图表数据)~ ，今天给大家介绍一个神器xlsxwriter库，它提供非常详细的函数接口，可以快速方便的定制化你的excel文件内容，轻松实现自动化办公。本文附带详细的python代码，干货满满。接下来进入今天的主题~ 本文涉及的python库版本信息如下:
# !pip install pyjanitor==0.26.0
# !pip install plottable==0.1.5
# !pip install mpl_font==1.1.0
# !pip install mplcyberpunk
# !pip install xlsxwriter==3.2.0
import numpy as np # linear algebra
import pandas as pd
import pandas as pd
import numpy as np
import os
import janitor
import matplotlib as mpl
import matplotlib.pyplot as plt
import seaborn as sns
# 完全屏蔽所有警告
import warnings
warnings.filterwarnings("ignore")
print("pandas: ", pd.__version__) # pandas: 2.2.0
print("numpy: ", np.__version__) # numpy: 1.26.4
print("matplotlib: ", mpl.__version__) # matplotlib: 3.7.5
print("seaborn: ", sns.__version__) # seaborn: 0.12.2
本文目录
xlsxWriter库操作
xlsxWriter库(其官方github：github.com/jmcnamara/X… 下面我给搭建介绍2个案例，以利用python自动插入迷你图和精美图表在excel文件中。
案例展示
自动向excel文件中插入迷你图
下面是一个使用python来自动向excel表格中
# 导入xlsxwriter库，用于创建和编辑Excel文件
import xlsxwriter
# 使用with语句创建一个新的Excel工作簿（文件名："自动化生成excel文件.xlsx"）
with xlsxwriter.Workbook("自动化生成excel文件.xlsx") as workbook:
row = 1
# 添加1个工作表（worksheet）到工作簿中
worksheet1 = workbook.add_worksheet(name='sheet1') # 第一个工作表
worksheet2 = workbook.add_worksheet(name='sheet2')# 第二个工作表
# 设置工作表1的第一列和第二列宽度以便于展示数据
worksheet1.set_column("A:A", 60) # 设置第一列为60个字符宽
worksheet1.set_column("B:B", 50) # 设置第二列为50个字符宽
worksheet1.set_zoom(150) # 设置工作表1的缩放比例为150%
# 创建一个居中对齐并加粗的单元格格式
centered_format = workbook.add_format({"align": 'center', 'valign': 'vcenter', "bold": True})
# 在工作表1的顶部写入标题行
worksheet1.write("A1", "Sparkline", centered_format)
worksheet1.write("B1", "Description", centered_format)
# -------------------------------------------------------------------------
# 开始逐个创建并配置sparklines（迷你图表）
# 并在每个sparkline下方写出对应的描述文字
# 默认线型sparkline，范围来自工作表2的A1:J1
text = 'A default "line" sparkline.'
worksheet1.add_sparkline("A2", {"range": "Sheet2!A1:J1"})
worksheet1.write(row, 1, text) # 写入描述
row += 1 # 行数递增1
# 默认柱形sparkline，范围来自工作表2的A2:J2
text = 'A default "column" sparkline.'
worksheet1.add_sparkline("A3", {"range": "Sheet2!A2:J2", "type": "column"})
worksheet1.write(row, 1, text)
row += 1
# 默认胜败图sparkline，范围来自工作表2的A3:J3
text = 'A default "win/loss" sparkline.'
worksheet1.add_sparkline("A4", {"range": "Sheet2!A3:J3", "type": "win_loss"})
worksheet1.write(row, 1, text)
row += 2
# 线型sparkline带标记点
text = "Line with markers."
worksheet1.add_sparkline("A6", {"range": "Sheet2!A1:J1", "markers": True})
worksheet1.write(row, 1, text)
row += 1
# 线型sparkline带有高点和低点突出显示
text = "Line with high and low points."
worksheet1.add_sparkline(
"A7", {"range": "Sheet2!A1:J1", "high_point": True, "low_point": True}
)
worksheet1.write(row, 1, text)
row += 1
# 线型sparkline带有首尾标记点
text = "Line with first and last point markers."
worksheet1.add_sparkline(
"A8", {"range": "Sheet2!A1:J1", "first_point": True, "last_point": True}
)
worksheet1.write(row, 1, text)
row += 1
# 线型sparkline带有负值标记点
text = "Line with negative point markers."
worksheet1.add_sparkline("A9", {"range": "Sheet2!A1:J1", "negative_points": True})
worksheet1.write(row, 1, text)
row += 1
# 线型sparkline带有坐标轴
text = "Line with axis."
worksheet1.add_sparkline("A10", {"range": "Sheet2!A1:J1", "axis": True})
worksheet1.write(row, 1, text)
row += 2
# 柱形sparkline使用默认样式1
text = "Column with default style (1)."
worksheet1.add_sparkline("A12", {"range": "Sheet2!A2:J2", "type": "column"})
worksheet1.write(row, 1, text)
row += 1
# 柱形sparkline使用预设样式2
text = "Column with style 2."
worksheet1.add_sparkline("A13", {"range": "Sheet2!A2:J2", "type": "column", "style": 2})
worksheet1.write(row, 1, text)
row += 1
# 柱形sparkline使用预设样式3至样式6
for style_num in range(3, 7):
text = f"Column with style {style_num}."
worksheet1.add_sparkline(
f"A{row}", {"range": "Sheet2!A2:J2", "type": "column", "style": style_num}
)
worksheet1.write(row, 1, text)
row += 1
# 柱形sparkline使用自定义颜色
text = "Column with a user defined color."
worksheet1.add_sparkline(
"A18", {"range": "Sheet2!A2:J2", "type": "column", "series_color": "#E965E0"}
)
worksheet1.write(row, 1, text)
row += 2
# 胜败图sparkline
text = "A win/loss sparkline."
worksheet1.add_sparkline("A20", {"range": "Sheet2!A3:J3", "type": "win_loss"})
worksheet1.write(row, 1, text)
row += 1
# 胜败图sparkline带有负值突出显示
text = "A win/loss sparkline with negative points highlighted."
worksheet1.add_sparkline(
"A21", {"range": "Sheet2!A3:J3", "type": "win_loss", "negative_points": True}
)
worksheet1.write(row, 1, text)
row += 2
# 从左至右排列的柱形sparkline（默认）
text = "A left to right column (the default)."
worksheet1.add_sparkline(
"A23", {"range": "Sheet2!A4:J4", "type": "column", "style": 20}
)
worksheet1.write(row, 1, text)
row += 1
# 从右至左排列的柱形sparkline
text = "A right to left column."
worksheet1.add_sparkline(
"A24", {"range": "Sheet2!A4:J4", "type": "column", "style": 20, "reverse": True}
)
worksheet1.write(row, 1, text)
row += 1
# 单元格内同时包含sparkline与文本
text = "Sparkline and text in one cell."
worksheet1.add_sparkline(
"A25", {"range": "Sheet2!A4:J4", "type": "column", "style": 20}
)
worksheet1.write(row, 0, "Growth") # 写入类别标签
worksheet1.write(row, 1, text)
row += 2
# 组合sparkline示例，三个sparkline共享相同的配置变化
text = "A grouped sparkline. Changes are applied to all three."
worksheet1.add_sparkline(
"A27",
{
"location": ["A27", "A28", "A29"],
"range": ["Sheet2!A5:J5", "Sheet2!A6:J6", "Sheet2!A7:J7"],
"markers": True,
},
)
worksheet1.write(row, 1, text)
row += 1
# -------------------------------------------------------------------------
# 在第二个工作表中填充数据以供第一个工作表中的sparklines引用
# 设置工作表2各列宽度为11个字符
worksheet2.set_column("A:J", 11)
# 定义不同类型的样本数据
data = [
# 简单的线型数据
[-2, 2, 3, -1, 0, -2, 3, 2, 1, 0],
# 简单的柱形数据
[30, 20, 33, 20, 15, 5, 5, 15, 10],
# 简单的胜败图数据
[1, 1, -1, -1, 1, -1, 1, 1, 1, -1],
# 不平衡直方图数据
[5, 6, 7, 10, 15, 20, 30, 50, 70, 100],
# 用于组合sparkline例子的数据
[-2, 2, 3, -1, 0, -2, 3, 2, 1, 0],
# 组合sparkline例子的第二组数据
[3, -1, 0, -2, 3, 2, 1, 0, 2, 1],
# 组合sparkline例子的第三组数据
[0, -2, 3, 2, 1, 0, 1, 2, 3, 1],
]
# 将样本数据写入到工作表2的对应位置
worksheet2.write_row("A1", data[0]) # 写入线型数据
worksheet2.write_row("A2", data[1]) # 写入柱形数据
worksheet2.write_row("A3", data[2]) # 写入胜败图数据
worksheet2.write_row("A4", data[3]) # 写入不平衡直方图数据
worksheet2.write_row("A5", data[4]) # 写入组合sparkline的第一组数据
worksheet2.write_row("A6", data[5]) # 写入组合sparkline的第二组数据
worksheet2.write_row("A7", data[6]) # 写入组合sparkline的第三组数据
可视化结果:
保存精美matplotlib可视化图表为对应的图片
import matplotlib.pyplot as plt
import mplcyberpunk
import mpl_font.noto
with plt.style.context("cyberpunk"):
categories = ['成都', '重庆', '上海', '西安', '南京']
values = [25, 67, 19, 45, 10]
colors = ["C0", "C1", "C2", "C3", "C4"]
fig, axes =plt.subplots(2,2,figsize=(10,6))
## 绘制图一
ax = axes[0,0]
bars = ax.bar(categories, values, color=colors, zorder=1)
ax.bar_label(bars)
mplcyberpunk.add_bar_gradient(bars=bars,ax=ax)
## 绘制图四
ax =axes[0,1]
ax.plot([1, 3, 9, 5, 2, 1, 1], marker='o')
ax.plot([4, 5, 5, 7, 9, 8, 6], marker='o')
mplcyberpunk.add_glow_effects(ax=ax, gradient_fill=False) #使图形中的下方开启发光效果
# ## 等价于
# mplcyberpunk.make_lines_glow(ax=ax, alpha_line=0.3) # 使线条具有发光效果
# mplcyberpunk.add_underglow(ax=ax,alpha_underglow=0.1) # 使线条下面发光效果
## 绘制图三
ax = axes[1,0]
x = np.linspace(0,7,20)
y = np.sin(x) + 1
ax.step(x, y, where='mid', marker='o', markersize=3)
mplcyberpunk.add_gradient_fill(ax,alpha_gradientglow= 0.6, gradient_start='top')
# gradient_start in('min','max','bottom','top','zero')
ax.legend(['mid'])
## 绘制图二
axes[1,1].scatter([2, 3, 3, 5, 7, 8, 12],[3, 5, 5, 6, 6, 7, 6], marker='*')
mplcyberpunk.make_scatter_glow(ax=axes[1,1],alpha=0.3) # 使散点图有发光效果
fig.suptitle('mplcyberpunk赛博朋克科技主题风格效果展示', fontsize=18) # 调整字体大小和垂直位置
fig.tight_layout()
fig.savefig("demo.png",dpi=400)
可视化效果:
将图片结果插入对应的excel的页面指定的位置
# 导入xlsxwriter库，用于创建和编辑Excel文件
import xlsxwriter
# 使用with语句创建一个新的Excel工作簿（文件名："自动化生成excel文件.xlsx"）
with xlsxwriter.Workbook("自动化生成excel文件.xlsx") as workbook:
# -------------------------------------------------------------------------
# 在第二个工作表中填充数据以供第一个工作表中的sparklines引用
worksheet2 = workbook.add_worksheet(name='sheet2') # 第二个工作表
# 设置工作表2各列宽度为11个字符
worksheet2.set_column("A:J", 11)
# 定义不同类型的样本数据
data = [
# 简单的线型数据
[-2, 2, 3, -1, 0, -2, 3, 2, 1, 0],
# 简单的柱形数据
[30, 20, 33, 20, 15, 5, 5, 15, 10],
# 简单的胜败图数据
[1, 1, -1, -1, 1, -1, 1, 1, 1, -1],
# 不平衡直方图数据
[5, 6, 7, 10, 15, 20, 30, 50, 70, 100],
# 用于组合sparkline例子的数据
[-2, 2, 3, -1, 0, -2, 3, 2, 1, 0],
# 组合sparkline例子的第二组数据
[3, -1, 0, -2, 3, 2, 1, 0, 2, 1],
# 组合sparkline例子的第三组数据
[0, -2, 3, 2, 1, 0, 1, 2, 3, 1],
]
# 将样本数据写入到工作表2的对应位置
worksheet2.write_row("A1", data[0]) # 写入线型数据
worksheet2.write_row("A2", data[1]) # 写入柱形数据
worksheet2.write_row("A3", data[2]) # 写入胜败图数据
worksheet2.write_row("A4", data[3]) # 写入不平衡直方图数据
worksheet2.write_row("A5", data[4]) # 写入组合sparkline的第一组数据
worksheet2.write_row("A6", data[5]) # 写入组合sparkline的第二组数据
worksheet2.write_row("A7", data[6]) # 写入组合sparkline的第三组数据
# 在工作表2插入一个图片（例如：'demo.png'）
worksheet2.insert_image('A10', 'demo.png')
结果展示:
参考文档
z先生说
大家好，今天给大家分享神器XlsxWriter库结合pandas等利用python轻松实现自动化办公excel文件定制化展示，简单易用，新人也可快速入手，功能丰富~
如果本文对你有帮助，还请你点赞在看转发。你的支持就是我创作的最大动力，关注下面公众号不迷路~
