办公自动化 -- openpyxl绘图

绘制条形图及柱状图

## 绘制条形图及柱状图

from openpyxl import Workbook

wb = Workbook()
ws = wb.create_sheet()
rows = [
    ('Number', 'Batch 1', 'Batch 2'),
    (2, 10, 30),
    (3, 40, 60),
    (4, 50, 70),
    (5, 20, 10),
    (6, 10, 40),
    (7, 50, 30),
]
for row in rows:
    ws.append(row)

from openpyxl.chart import BarChart, Series, Reference

chart1 = BarChart()     #创建条形图对象
chart1.type = 'col'     #设置类型为树状条形图
chart1.style = 14
chart1.title = 'Bar Chart'  #设置图表名称
chart1.y_axis.title = 'Test number' #设置y轴名称
chart1.x_axis.title = 'sample length(mm)'   #设置x轴名称
data = Reference(ws,min_col=2,max_col=3,min_row=1,max_row=7)    # 取数据的区域对象
cats = Reference(ws,min_col=1,min_row=2,max_row=7)      # 取数据的区域对象
chart1.add_data(data,titles_from_data=True)     # 将data添加为作图数据
chart1.shape = 3    #图表类型
ws.add_chart(chart1,'A10')

from copy import deepcopy

chart2 = deepcopy(chart1)   #深拷贝chart1图表数据
chart2.style = 11   #重置样式
chart2.type = 'bar' #重置title
ws.add_chart(chart2,'G10')
wb.save('bar.xlsx')

• 效果图
  

绘制折线图

## 绘制折线图

from openpyxl import Workbook
from datetime import date

wb = Workbook()
ws = wb.active
rows = [
['Date', 'Batch 1', 'Batch 2', 'Batch 3'],
    [date(2015,9, 1), 40, 30, 25],
    [date(2015,9, 2), 40, 25, 30],
    [date(2015,9, 3), 50, 30, 45],
    [date(2015,9, 4), 30, 25, 40],
    [date(2015,9, 5), 25, 35, 30],
    [date(2015,9, 6), 20, 40, 35],
]
for row in rows:
    ws.append(row)
from openpyxl.chart import LineChart,Reference
chart1 = LineChart()    #创建折线图对象
chart1.title = 'Line Chart'     #设置图表标题
chart1.y_axis.title = 'Size'    #设置y轴标题
chart1.x_axis.title = 'Test Number' #设置x轴标题
chart1.style = 13
data = Reference(ws,min_row=1,max_row=7,min_col=2,max_col=4)     #选择数据区域
chart1.add_data(data,titles_from_data=True) #将数据添加到作图数据中去
#设置线的样式
#chart1.series
s1 = chart1.series[0]   #获取第一条线
s1.marker.symbol = 'triangle'   #设置点的样式（三角形）
s1.marker.graphicalProperties.line.SolidFill = 'FF0000' #实心填充三角形的颜色（红色）
s1.marker.graphicalProperties.line.SolidFill = 'FF0000' #三角形的边框色
s1.graphicalProperties.line.noFill = True   #线不填充
s2 = chart1.series[1]   #获取第二条线
s2.graphicalProperties.line.SolidFill = '00AAAA'    #设置线的填充色（蓝色）
s2.graphicalProperties.line.dashStyle = 'sysDot'   #设置线的样式为虚线并且为点
s2.graphicalProperties.line.width = 100050  #设置宽度（单位为EMUS）
s3 = chart1.series[2]
s3.smooth = True    #光滑的曲线
ws.add_chart(chart1,'A10')
wb.save('line.xlsx')

• 效果图
  

绘制扇形图

## 绘制扇形图

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
date =[
    ['Pie', 'Sold'],
    ['Apple', 50],
    ['Cherry', 30],
    ['Pumpkin', 10],
    ['Chocolate', 40],
]
for row in date:
    ws.append(row)

from openpyxl.chart import PieChart,Reference

pie = PieChart()    #创建扇形图对象
lablels = Reference(ws,min_col=2,min_row=1,max_row=5)    #选择扇形图的标签区域
data = Reference(ws,min_col=2,min_row=1,max_row=5)  #选择作图数据
pie.add_data(data,titles_from_data=True) #将data添加到图表数据
pie.set_categories(lablels)    #将labalels设置为图形的分类
pie.title = 'Pies'
#将最比突出扇形图

from openpyxl.chart.series import DataPoint

slice = DataPoint(idx=0,explosion=20)
pie.series[0].data_points = [slice]  #设为重点数据（突出数据）为slice
ws.add_chart(pie,'D10')
wb.save('pie.xlsx')

• 效果图