1 import xlsxwriter
2 import time
3
4 def charts(product_name, dict_data, crash_data_list, report_path):
5 if len(dict_data) <= 0 or len(crash_data_list) <= 0:
6 return
7 current_time = time.localtime(time.time())
8 current_time = time.strftime("%Y%m%d%H%M%S", current_time)
9
10 plot_file_path = 'product_plot_{}.xlsx' 11 workbook = xlsxwriter.Workbook(plot_file_path)
12 worksheet = workbook.add_worksheet(product_name)
13
14 worksheet_date = workbook.add_worksheet('date')
15 worksheet_date_headings = ['date', 'date_count']
16 bold = workbook.add_format({'bold': 1}) #设置格式
17 19 data = list()
20 date_list = list()
21 date_crash_list = list()
22
23 sort_dump_dict = {}
24 for i in sorted (dict_data):
25 sort_dump_dict[i] = dict_data[i]
26
27 for k, v in sort_dump_dict.items():
28 date_list.append(k)
29 date_crash_list.append(len(v))
30
31 data.append(date_list)
32 data.append(date_crash_list)
33 data.append([crash_data_list[0]])
34 data.append([crash_data_list[1]])
35 data.append([crash_data_list[2]])
36 data.append([crash_data_list[3]])
37 data.append([crash_data_list[4]])
38 data.append([crash_data_list[5]])
39
40 format = workbook.add_format()
41 format.set_text_wrap()
42 worksheet.set_column('A:A', 10, format)
#上述构造数据
44
45 print('exlce_data: ', data)
46 worksheet.write_column('A100', data[0])
47 worksheet.write_column('B100', data[1])
48
49 worksheet_date.write_row('A1', worksheet_date_headings, bold)
50 worksheet_date.write_column('A2', data[0])
51 worksheet_date.write_column('B2', data[1])
52
53 data_format = {
54 'align': 'right', # 水平位置设置：右对齐
55 'valign': 'vcenter' # 垂直位置设置，居中
56 }
57 right_format = workbook.add_format(data_format)
58 worksheet.write_row('A1', ["product_name"], bold)
59 worksheet.write_row('A2', ["effective_data"], bold)
60 worksheet.write_row('A3', ["other_data"], bold)
61 worksheet.write_row('A4', ["count"], bold)
62 worksheet.write_row('A5', ["point"], bold)
63 worksheet.write_row('A6', ["DUA"], bold)
64 worksheet.write_row('A7', ["rate"], bold)
65
#行写入数据 注意第二参数是list，第三参数的格式需要添加进工作簿才能生效
66 worksheet.write_row('B1', [product_name], right_format)
67 worksheet.write_row('B2', [crash_data_list[0]], right_format)
68 worksheet.write_row('B3', [crash_data_list[1]], right_format)
69 worksheet.write_row('B4', [crash_data_list[2]], right_format)
70 worksheet.write_row('B5', [crash_data_list[3]], right_format)
71 worksheet.write_row('B6', [crash_data_list[4]], right_format)
72 worksheet.write_row('B7', [crash_data_list[5]], right_format)
73
74 # 创建一个图表，类型是column
75 columnar_chart = workbook.add_chart({'type': 'column'})
76 line_chart = workbook.add_chart({'type': 'line'})
77
78 # 配置series,这个和前面worksheet是有关系的。
79 # 指定图表的数据范围
80 columnar_chart.add_series({
81 'name': '={}!$B$1'.format(product_name),
82 'categories': '={}!$A$100:$A${}'.format(product_name, 100 + len(date_list)),
83 'values': '={}!$B$100:$B${}'.format(product_name, 100 + len(date_crash_list)),
84 'data_labels': {'value': True},
85 })
86 #设置列宽 列宽自适应 像素点转磅
87 worksheet.set_column('A1:A{}'.format(len(date_list)), 33)
88 worksheet.set_column('B1:B{}'.format(len(date_crash_list)), 13)
89 worksheet_date.set_column('A1:A{}'.format(len(date_list)), 15)
90 worksheet_date.set_column('B1:B{}'.format(len(date_crash_list)), 24)
91
92 # 添加图表标题和标签
93 columnar_chart.set_title({'name': 'Results of datga analysis'})
94 columnar_chart.set_x_axis({'name': 'datas analysis result'})
95 columnar_chart.set_y_axis({'name': 'Sample length (mm)'})
96
97 # 设置图表风格
98 columnar_chart.set_style(11)
99 columnar_chart.set_size({'width': 750, 'height': 400})
100 columnar_chart.set_legend({'position': 'top'})
101 columnar_chart.set_y_axis({
102 'major_gridlines': {
103 'visible': False,
104 'line': {'none': True}
105 },
106 'line': {'none': True},
107 'num_font': {'color': '#FFFFFF'},
108
109 })
110 # 在D2单元格插入图表（带偏移）
111 columnar_chart.combine(line_chart)
112 worksheet.insert_chart('F1', columnar_chart)
113
114 pie_chart = workbook.add_chart({'type': 'pie'})
115 pie_chart.set_style(3)
116 pie_chart.set_size({'width': 500, 'height': 420})
117 pie_chart.set_legend({'position': 'bottom'})
118 pie_chart.add_series({
119 # "name":"饼形图",
120 "categories": "={}!$A$2:$A$3".format(product_name),
121 "values": "={}!$B$2:$B$3".format(product_name),
122 # 定义各饼块的颜色
123 "points": [
124 {"fill": {"color": "red"}},
125 {"fill": {"color": "green"}}
126 ],
127 'data_labels': {
128 # 显示数值
129 'value': True,
130 # 显示百分比
131 'percentage':True,
132 'leader_lines': True,
133 'category': True,
134 'separator': "\n",
135 'border': {'color': '#7F7F7F','transparency': 100}, #使用transparency关键字来设置透明度
136 }, #显示标签
137 })
138 pie_chart.set_title({"name": "data_type"})
139 worksheet.insert_chart('H21', pie_chart)
140 workbook.close()
