excel 饼状图绘制

案例1

from openpyxl import Workbookfrom openpyxl.chart import PieChart, Referencefrom openpyxl.chart.marker import DataPointwb = 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.插入数据-sheetfor 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 添加图 到 sheetsheet.add_chart(pie, f'A{len(data) + 3}')# 4.保存wb.save("hello.xlsx")

案例2

# 1.导包from openpyxl import Workbookwb = Workbook()sheet = wb.active# 组织数据data = [['门店', '售卖量'],['金燕龙店', 110],['昌平店', 140],['顺义店', 188]]# 插入数据 sheetfor i in data:sheet.append(i)from openpyxl.chart import PieChart, PieChart3D, Reference# 1.图的对象pie = PieChart()pie = PieChart3D()# 2. 设置标题pie.title = "北京营业额"# 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 Workbookfrom openpyxl.chart import LineChart, Referencewb = Workbook()sheet = wb.active# 设置第一列的宽度sheet.column_dimensions['A'].width = 15# 数据rows = [['日期', 'python 1', 'python 2', 'python 3'],[date(, 9, 1), 40, 30, 25],[date(, 9, 2), 40, 25, 30],[date(, 9, 3), 50, 30, 45],[date(, 9, 4), 30, 25, 40],[date(, 9, 5), 25, 35, 30],[date(, 9, 6), 20, 40, 35],]# 写入 excel 数据for data in rows:sheet.append(data)# 画图 折线图# 一个图一个对象 -实例化linechart = LineChart()# 2.设置属性-- 图的标题--图的类型-X轴标题-Y轴标题linechart.title = 'python版本的占比'linechart.style = 13linechart.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 LinePropertiesprint(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 randintfrom datetime import timefrom openpyxl import Workbook# 2.创建对象from openpyxl.chart import LineChart, Reference, LineChart3Dwb = Workbook()# 3. 获取默认表sheet = wb.activerows = [["时间", "服务人数"],]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 datetimedate_one_str = "-12-19"date_two_str = "12月19日"date_three_str = "/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")