专栏首页恩蓝脚本Python openpyxl 插入折线图实例

Python openpyxl 插入折线图实例

我就废话不多说了,还是直接看代码吧!

import os
import openpyxl
from datetime import date
from openpyxl import Workbook
from openpyxl.chart import (
Series,
LineChart,
Reference,
)
def add_line_chart(title, wss, min_col, min_row, max_col, max_row):
c1 = LineChart()
c1.title = title # 图的标题
c1.style = 12 # 线条的style
c1.y_axis.title = 'percent' # y坐标的标题
if 'IDC' not in title:
c1.x_axis.number_format = 'd-mmm' # 规定日期格式 这是月,年格式
c1.x_axis.majorTimeUnit = "Months" # 规定日期间隔 注意days;Months大写
c1.x_axis.title = "Date" # x坐标的标题
data = Reference(wss, min_col=min_col, min_row=min_row, max_col=max_col, max_row=max_row) # 图像的数据 起始行、起始列、终止行、终止列
c1.add_data(data, titles_from_data=True, from_rows=True)
dates = Reference(wss, min_col=2, min_row=1, max_col=max_col)
c1.set_categories(dates)
wss.add_chart(c1, "A6") # 将图表添加到 sheet中
def save_data_to_excel(file_name, target_sheet_name):
select_cursor = connect.cursor()
select_sql = "select phone_company, record_date, record_percent from phone_statistics where record_company = '%s'" % target_sheet_name
if target_sheet_name == "IDC":
select_sql = "select phone_company, record_q, record_percent from phone_statistics where record_company = '%s'" % target_sheet_name
select_cursor.execute(select_sql, ())
data_dic = {}
all_date = {}
all_phone_company = {}
for item in select_cursor:
if target_sheet_name == "IDC":
data_dic[item[0] + '_' + item[1]] = item[2]
else:
if type(item[1]) == str:
data_dic[item[0] + '_' + item[1]] = item[2]
else:
data_dic[item[0] + '_' + item[1].strftime("%Y-%m-%d")] = item[2]
all_date[item[1]] = 1
all_phone_company[item[0]] = 1
if os.path.exists(file_name):
wb = openpyxl.load_workbook(file_name)
else:
wb = Workbook()
try:
wb.remove_sheet(wb['Sheet'])
except Exception as e:
pass
try:
wb.remove_sheet(wb[target_sheet_name])
except Exception as e:
pass
try:
sheet = wb[target_sheet_name]
except Exception as e:
sheet = wb.create_sheet()
start_date_index = 'B'
for each_date in all_date.keys():
if target_sheet_name == "IDC":
sheet['%s1' % start_date_index] = each_date
else:
if type(each_date) == str:
sheet['%s1' % start_date_index] = each_date
else:
sheet['%s1' % start_date_index] = each_date.strftime("%Y-%m-%d")
start_date_index = chr(ord(start_date_index) + 1)
start_name_index = 2
for each_name in all_phone_company.keys():
sheet['A%d' % start_name_index] = each_name
start_name_index += 1
start_date_index = 'B'
start_name_index = 2
for each_date in all_date.keys():
for each_name in all_phone_company.keys():
if target_sheet_name == "IDC":
key = each_name + '_' + each_date
if key in data_dic:
sheet['%s%d' % (start_date_index, start_name_index)] = data_dic[key]
else:
if type(each_date) == str:
key = each_name + '_' + each_date
else:
key = each_name + '_' + each_date.strftime("%Y-%m-%d")
if key in data_dic:
sheet['%s%d' % (start_date_index, start_name_index)] = data_dic[key]
start_name_index += 1
start_date_index = chr(ord(start_date_index) + 1)
start_name_index = 2
sheet.title = target_sheet_name
sheet.column_dimensions['A'].width = 20
start_date_index = 'B'
for each_date in all_date.keys():
sheet.column_dimensions[start_date_index].width = 13
start_date_index = chr(ord(start_date_index) + 1)
add_line_chart(target_sheet_name.upper() + "'s Phone Statistics", sheet, 1, 2, len(all_date.keys()) + 1,
min(15, len(all_phone_company.keys()) + 1))
wb.save(file_name)
pass

补充知识:python plotly line chart 折线图

我就废话不多说了,还是直接看代码吧!

# 1 折线图数据
# trace1 - 基本格式
# trace2 - 更多参数
trace1 = go.Scatter(
x = x1,
y = y2,
)
trace2 = go.Scatter(
x = x2,
y = y2,
mode = 'lines', # 模式:lines 线,markers 点。可用“+”相连
name = 'line2', # 折线名,显示于图例
connectgaps = True # 连接缺失点两端 默认False
line = dict(
color = ('rgb(205, 12, 24)'), # 颜色
width = 4, #线宽
dash = 'dash') # 虚线: dash 一一,dot ···,dashdot 一·一
) 
)
# 2 打包数据
data = [trace1,trace2]
# 3 格式
layout = dict(title = '折线',
xaxis = dict(title = '时间'), # 横轴坐标
yaxis = dict(title = '数量'), # 总轴坐标
legend=dict(x=1.1,y=1) # 图例位置 
)
# 4 打包数据+格式
fig = dict(data=data, layout=layout)
# 5 画图
py.iplot(fig, filename='styled-line')

以上这篇Python openpyxl 插入折线图实例就是小编分享给大家的全部内容了,希望能给大家一个参考。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Python xlrd模块导入过程及常用操作

    砸漏
  • Python使用Excel将数据写入多个sheet

    砸漏
  • PHP使用PhpSpreadsheet操作Excel实例详解

    本文实例讲述了PHP使用PhpSpreadsheet操作Excel。分享给大家供大家参考,具体如下:

    砸漏
  • Automl框架katib浅析

    | 导语   AutoML是一种能将机器学习模型中特征工程、模型选择、超参选择等问题自动化解决的一种方案,使得机器学习模型无需人工干预即可被应用。而katib...

    腾讯大讲堂
  • 如何搭建 Elasticsearch-kibana 环境

    es使用java编写,安装es之前,需要先检查jdk环境,一般要求在1.7以上,如果没有安装jdk,建议直接安装1.8版本。安装过程参考:https://blo...

    IT云清
  • 技术 | ping网段的一些小技巧,你知道不?

    在PC的cmd上,for /L %D in (1,1,255) do ping 10.168.1.%D

    网络技术联盟站
  • iOS开发中利用YYModel进行归档

    版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/u010105969/article/details/...

    用户1451823
  • python 用装饰器重写类的方法

    def log_getattribute(cls): # Get the original implementation orig_getattribute...

    用户5760343
  • Laravel5.5+ 使用API Resources快速输出自定义JSON方法详解

    如果你熟悉使用API进行输出,构架前后端分离的网络应用,那么你应该会发现,当我们使用Eloquent从数据库中取出数据后,如果想以JSON格式进行输出,那么我们...

    砸漏
  • Linux下搭建Android开发环境

    上一次说到在window下搭建Android开发环境,总体比较简单。这一次就说说在Linux搭建Android开发环境,这里主要以很流行的Ubuntu 操作系统...

    飞雪无情

扫码关注云+社区

领取腾讯云代金券