Python 在 excel 中画 饼状图 折线图

excel 饼状图绘制

案例1

from openpyxl import Workbook
from openpyxl.chart import PieChart, Reference
from openpyxl.chart.marker import DataPoint

wb = Workbook()
sheet = wb.active

# 1.饼状图的数据
data = [
    ['Pie', 'Sold'],
    ['苹果', 50, 50, 50, 50, 50],
    ['樱桃', 30, 50, 40, 40, 10],
    ['南瓜', 10, 50, 30, 30, 20],
    ['巧克力', 40, 50, 20, 30, 30],
]

# 2.插入数据-sheet
for i in data:
    sheet.append(i)

# 3.饼状图-PieChart
# 3.1 实例化对象
pie = PieChart()
pie.title = "商店的销售"
# 3.2 获取数据
data = Reference(worksheet=sheet, min_col=2, min_row=1, max_row=len(data))
# 指定某一列数据画图
# data = Reference(worksheet=sheet, range_string="Sheet!C1:C5")
# 标注
lables = Reference(worksheet=sheet, min_col=1, min_row=2, max_row=len(data))

# 3.3 添加数据到 图
pie.add_data(data, titles_from_data=True)
pie.set_categories(lables)

# 指定 第一个 扇形 --偏离圆心 20
# 设置偏离度       idx 哪个扇形   explosion 偏离多远
slice = DataPoint(idx=0, explosion=50)
#给  扇形位置 赋值  series 只有一个对象
pie.series[0].data_points = [slice]
print(pie.series)

# 3.4 添加图 到 sheet
sheet.add_chart(pie, f'A{len(data) + 3}')

# 4.保存
wb.save("hello.xlsx")

案例2

# 1.导包
from openpyxl import Workbook
wb = Workbook()
sheet = wb.active

# 组织数据
data = [
    ['门店', '售卖量'],
    ['金燕龙店', 110],
    ['昌平店', 140],
    ['顺义店', 188]
]

# 插入数据 sheet
for i in data:
    sheet.append(i)

from openpyxl.chart import PieChart, PieChart3D, Reference

# 1.图的对象
pie = PieChart()
pie = PieChart3D()

# 2. 设置标题
pie.title = "2020年北京营业额"
# 3. 图的数据
data = Reference(sheet, min_col=2, min_row=1, max_row=len(data))
# 4. 图的标注数据
lables = Reference(sheet, min_col=1, min_row=2, max_row=len(data))
# 5. 图添加 数据
pie.add_data(data, titles_from_data=True)
# 6. 图添加 标注
pie.set_categories(lables)

# 7. 表添加 图
sheet.add_chart(pie, anchor="D1")

# 4.保存
wb.save("hello.xlsx")

excel 折线图绘制

案例1

from datetime import date
# 1.导包
from openpyxl import Workbook
from openpyxl.chart import LineChart, Reference
wb = Workbook()
sheet = wb.active

# 设置第一列的宽度
sheet.column_dimensions['A'].width = 15

# 数据
rows = [
    ['日期', 'python 1', 'python 2', 'python 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],
]

# 写入 excel 数据
for data in rows:
    sheet.append(data)

# 画图 折线图
# 一个图一个对象 -实例化
linechart = LineChart()

# 2.设置属性-- 图的标题--图的类型-X轴标题-Y轴标题
linechart.title = 'python版本的占比'
linechart.style = 13
linechart.x_axis.title = '数字'
linechart.y_axis.title = '占比大小'

# 3.给图添加数据
data = Reference(worksheet=sheet, min_col=2, min_row=1, max_col=4, max_row=7)
linechart.add_data(data, titles_from_data=True)

# 设置线条的样式-- 线样式--线颜色--是否是 实心线条--平滑度
"""
'circle', 'dash', 'diamond', 'dot', 'picture',
'plus', 'square', 'star', 'triangle', 'x', 'auto'
"""

# 设置第一条折线数据
line1 = linechart.series[0]
line1.marker.symbol = "x"
 # marker 填充颜色
line1.marker.graphicalProperties.solidFill = "FF0000"
# marker线颜色
line1.marker.graphicalProperties.line.solidFill = "FF0000"
# 是否填充连接线
line1.graphicalProperties.line.noFill = True

# 设置第二条折线数据
line2 = linechart.series[1]
line2.graphicalProperties.solidFill = "00AAAA"

"""
'solid', 'dot', 'dash', 'lgDash', 'dashDot',
'lgDashDot', 'lgDashDotDot', 'sysDash', 'sysDot',
 'sysDashDot','sysDashDotDot'
"""
line2.graphicalProperties.line.dashStyle = "sysDot"
from openpyxl.drawing.line import LineProperties
print(line2.graphicalProperties.line)
line2.graphicalProperties.line.width = 100050

# 设置第三条折线数据
line3 = linechart.series[2]
 # 平滑曲线
line3.smooth = True

# 4.给表添加  折线图
sheet.add_chart(linechart, 'A10')

# 4.保存
wb.save("hello.xlsx")

案例2

# 1.导包
from random import randint
from datetime import time
from openpyxl import Workbook

# 2.创建对象
from openpyxl.chart import LineChart, Reference, LineChart3D

wb = Workbook()

# 3. 获取默认表
sheet = wb.active

rows = [
    ["时间", "服务人数"],
]

for i in range(8, 24):
    rows.append([time(i), randint(0, 200)])

for data in rows:
    sheet.append(data)

# 折线图
# 1.一个图一个对象 -实例化
linechart = LineChart()
linechart = LineChart3D()

# 2.设置属性-- 图的标题--图的类型-X轴标题-Y轴标题
linechart.title = "客服绩效图当天"
linechart.y_axis.title = "服务人数"
linechart.x_axis.title = "时间"

# 图的数据
data = Reference(worksheet=sheet,
                 min_col=2,
                 min_row=1,
                 max_col=2,
                 max_row=len(rows), )
linechart.add_data(data, titles_from_data=True)

# 设置 X轴 显示的内容
linechart.x_axis.number_format = "HH:MM"
x_title = Reference(worksheet=sheet, min_col=1, min_row=2, max_row=len(rows))
linechart.set_categories(x_title)

sheet.add_chart(linechart, "A20")
# 4.保存
wb.save("hello.xlsx")

坐标轴中可能涉及到的日期格式

from datetime import datetime

date_one_str = "2020-12-19"
date_two_str = "2020年12月19日"
date_three_str = "2020/12/19"

today_date = datetime.today()

# 将 日期格式 --转换成 字符串
today_date.strftime('%Y-%m-%d %H:%M:%S')
today_date.strftime('%Y年%m月%d日')
today_date.strftime('%Y/%m/%d')

# 将 字符串 --转换成 日期格式
datetime.strptime(date_one_str, "%Y-%m-%d")
datetime.strptime(date_two_str, "%Y年%m月%d日")
datetime.strptime(date_three_str, "%Y/%m/%d")