1.图表
Excel支持的图表类型还挺多的,包括柱状图、折线图、饼图、雷达图等等,2D和3D都有,而且支持很多自定义配置,例如颜色、大小、位置等。因为内容较多,所以我这里只举例折线图,其他图表类型大家可以参考官方文档
https://openpyxl.readthedocs.io/en/stable/charts/introduction.html
2.折线图代码
from openpyxl import Workbook
from openpyxl.chart import LineChart, Reference
wb = Workbook()
ws = wb.active
# 准备数据
rows = [
['月份', '桃子', '西瓜', '龙眼'],
[1, 38, 28, 29],
[2, 52, 21, 35],
[3, 39, 20, 69],
[4, 51, 29, 41],
[5, 29, 39, 31],
[6, 30, 41, 39],
]
for row in rows:
ws.append(row)
# 创建图表
c1 = LineChart()
c1.title = "折线图" # 标题
c1.style = 13 # 样式
c1.y_axis.title = '销量' # Y轴
c1.x_axis.title = '月份' # X轴
# 选择数据范围
data = Reference(ws, min_col=2, min_row=1, max_col=4, max_row=7)
c1.add_data(data, titles_from_data=True)
# 线条样式
s0 = c1.series[0]
s0.marker.symbol = "triangle" # triangle为三角形标记, 可选circle、dash、diamond、dot、picture、plus、square、star、triangle、x、auto
s0.marker.graphicalProperties.solidFill = "FF0000" # 填充颜色
s0.marker.graphicalProperties.line.solidFill = "0000FF" # 边框颜色
# s0.graphicalProperties.line.noFill = True # 改为True则隐藏线条,但显示标记形状
s1 = c1.series[1]
s1.graphicalProperties.line.solidFill = "00AAAA"
s1.graphicalProperties.line.dashStyle = "sysDot" # 线条点状样式
s1.graphicalProperties.line.width = 80000 # 线条大小,最大20116800EMUs
s2 = c1.series[2] # 采用默认设置
s2.smooth = True # 线条平滑
ws.add_chart(c1, "A8") # 图表位置
wb.save("line.xlsx")
大概过程是,创建一个图表(Chart)–指定数据范围(Reference)–设置系列(series)样式–添加到工作表中
代码很简单,基本上都做了注释,大家应该可以看得懂