from docxtpl import DocxTemplate
from docxtpl import InlineImage
from docx.shared import Mm
from snapshot_pyppeteer import snapshot
from pyecharts.render import make_snapshot
from pyecharts.charts import Radar
from pyecharts.charts import Bar
from pyecharts.globals import ThemeType
from pyecharts import options as opts
from pyecharts.charts import Pie
from pyecharts.faker import Faker
import pymysql
def conn_string():
config = {
"host": "localhost",
"user": "admin",
"password": "admin",
"database": "demo2019",
"port": 6688
}
db = pymysql.connect(**config)
cursor = db.cursor(cursor=pymysql.cursors.DictCursor)
return cursor, db
def conn_db():
cursor, db = conn_string()
sql = 'select * from v_report'
cursor.execute(sql)
data = cursor.fetchall()
lista = []
listb = []
for i in data:
lista.append(i['v_zb'])
listb.append(i['v_value'])
dic = dict(zip(lista, listb)) # 组装键值对
# print(dic)
cursor.close()
db.close()
return dic
def conn_group(v_where):
cursor, db = conn_string()
sql = "SELECT v_verbose_name,v_value from v_report p where p.v_group='{}'".format(v_where)
cursor.execute(sql)
data = cursor.fetchall()
x_data = []
y_data = []
for i in data:
x_data.append(i['v_verbose_name'])
y_data.append(i['v_value'])
data = [list(z) for z in zip(x_data, y_data)]
cursor.close()
db.close()
return data
# 雷达图渲染
def render_radar():
data = [{"value": [380, 230, 200, 300, 200, 100], "name": "预算分配"}]
c_schema = [
{"name": "销售", "max": 400, "min": 100},
{"name": "管理", "max": 400, "min": 100},
{"name": "技术", "max": 400, "min": 100},
{"name": "客服", "max": 400, "min": 100},
{"name": "研发", "max": 400, "min": 100},
{"name": "市场", "max": 400, "min": 100},
]
c = (
Radar(init_opts=opts.InitOpts(width="800px", height="600px"))
.set_colors(["#4587E7"])
.add_schema(
schema=c_schema,
shape="circle",
center=["50%", "50%"],
radius="80%",
angleaxis_opts=opts.AngleAxisOpts(
min_=0,
max_=360,
is_clockwise=False,
interval=5,
axistick_opts=opts.AxisTickOpts(is_show=False),
axislabel_opts=opts.LabelOpts(is_show=False),
axisline_opts=opts.AxisLineOpts(is_show=False),
splitline_opts=opts.SplitLineOpts(is_show=False),
),
radiusaxis_opts=opts.RadiusAxisOpts(
min_=0,
max_=400,
interval=100,
splitarea_opts=opts.SplitAreaOpts(
is_show=True, areastyle_opts=opts.AreaStyleOpts(opacity=1)
),
),
polar_opts=opts.PolarOpts(),
splitarea_opt=opts.SplitAreaOpts(is_show=False),
splitline_opt=opts.SplitLineOpts(is_show=False),
textstyle_opts=opts.TextStyleOpts(color="#000", font_size=15),
)
.add(
series_name="预算",
data=data,
areastyle_opts=opts.AreaStyleOpts(opacity=0.1),
linestyle_opts=opts.LineStyleOpts(width=1),
)
# .render("radar_angle_radius_axis.html")
)
make_snapshot(snapshot, c.render(), "bar2.png")
# 柱状图渲染（最大、最小及平均）
def render_Bar_MarkLine():
c = (
Bar(init_opts=opts.InitOpts(theme=ThemeType.HALLOWEEN))
.add_xaxis(Faker.choose())
.add_yaxis("商家A", Faker.values())
.set_global_opts(title_opts=opts.TitleOpts(title="Bar-MarkLine（指定类型）"))
.set_series_opts(
label_opts=opts.LabelOpts(is_show=False),
markline_opts=opts.MarkLineOpts(
data=[
opts.MarkLineItem(type_="min", name="最小值"),
opts.MarkLineItem(type_="max", name="最大值"),
opts.MarkLineItem(type_="average", name="平均值"),
]
),
)
)
make_snapshot(snapshot, c.render(), "bar3.png")
# 渲染饼状图
def render_pie(data, p_title, png_name):
c = (
Pie()
.add(
"",
data,
radius=["40%", "75%"],
label_opts=opts.LabelOpts(font_size=100),
)
.set_global_opts(
title_opts=opts.TitleOpts(title=p_title, pos_left="center", pos_bottom="20"),
legend_opts=opts.LegendOpts(is_show=False),
)
# font_size 调整字体大小
.set_series_opts(label_opts=opts.LabelOpts(formatter="{b}: {c}", font_size=15),
)
)
path = "output/{}.png".format(png_name)
make_snapshot(snapshot, c.render(), path)
return path
def render_table():
cursor, db = conn_string()
sql = 'SELECT p.*, ( CASE WHEN MOD ( p.rownum, 2 ) = 0 THEN "B7DDE8" ELSE "ffffff" END ) bgcolor ' \
'FROM (SELECT @rownum := @rownum + 1 AS rownum, GroupName, Target, Budget, PlayerName ' \
'FROM ( SELECT @rownum := 0 ) r, create_group ) p'
cursor.execute(sql)
data = cursor.fetchall()
cursor.close()
db.close()
return data
def make_doc(context):
doc = DocxTemplate("templates/work_report.docx")
context = conn_db()
context['myimage'] = InlineImage(doc, 'output/render_pie.png', width=Mm(140))
doc.render(context)
doc.save("output/work_report_bak.docx")
if __name__ == "__main__":
doc = DocxTemplate("templates/work_report.docx")
# # 文件常规变量渲染
context = conn_db()
context['create_group'] = render_table()
context['bg'] = 'FF0000'
# print(context)
# 插入学历图
data = conn_group('学历占比')
path_png = render_pie(data, '学历占比', 'image_xlfb')
print(path_png)
context['image_xlfb'] = InlineImage(doc, path_png, width=Mm(140))
# 插入性别图
data = conn_group('性别占比')
path_png = render_pie(data, '性别占比', 'image_sex')
print(path_png)
context['image_sex'] = InlineImage(doc, path_png, width=Mm(140))
print(context)
# 文件渲染
doc.render(context)
doc.save("output/work_report_bak.docx")
