-
Notifications
You must be signed in to change notification settings - Fork 4
Expand file tree
/
Copy pathexcel.py
More file actions
109 lines (97 loc) · 3.66 KB
/
Copy pathexcel.py
File metadata and controls
109 lines (97 loc) · 3.66 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
# coding:utf-8
from utils import androiddebug
import xlsxwriter
class WriteDate:
def __init__(self):
"""
初始化一个工作薄
:return:
"""
self.__workbook = xlsxwriter.Workbook(
"{location}{time} {device}.xlsx".format(location="./chart/", time=androiddebug.timestamp(),
device=androiddebug.get_device_name()), {'strings_to_numbers': True})
self.format = self.__workbook.add_format({'num_format': 'yyyy-mm-dd %H.%M.%S'})
self.format_head = self.__workbook.add_format({'bold': True})
def __sheet(self, sheet_name):
"""
创建工作表
:return:
"""
self.__worksheet = self.__workbook.add_worksheet(name=sheet_name)
return self.__worksheet.set_column("A:Z", 20)
def __write(self, heads, data):
"""
写入数据头及数据,自适应数据数组行列
:return:
"""
for index, value in enumerate(heads):
co = chr(66 + index)
row = "{colum}{index}".format(colum=co, index=1)
self.__worksheet.write("A1", "time", self.format_head)
self.__worksheet.write(str(row), value, self.format_head)
if len(data) == 1:
for one, values in enumerate(data):
for index, value in enumerate(values):
co = chr(65 + index)
row = "{colum}{index}".format(colum=co, index=2)
self.__worksheet.write(str(row), value)
else:
for more, values in enumerate(data):
for index, value in enumerate(values):
co = chr(65 + index)
row = "{colum}{index}".format(colum=co, index=2 + more)
self.__worksheet.write(str(row), value)
def __chart_series(self, sheetName, data):
"""
制表数据
:param sheetName:表名
:return:
"""
chartSeries = self.__workbook.add_chart({'type': 'line'})
chartSeries.add_series({
"name": '=%s!$B$1' % sheetName, # 数据名
"categories": '=' + sheetName + '!$A$2:$A$%s' % (len(data) + 1), # 数据列长度
"values": '=' + sheetName + '!$B$2:$B$%s' % (len(data) + 1), # 数据列值
"line": {'color': 'red'}
})
chartSeries.set_title({'name': sheetName}) # 图表标题
chartSeries.set_x_axis({'name': u'时间'}) # x轴标题
chartSeries.set_y_axis({'name': u'值'}) # y轴标题
return chartSeries
def __line_chart(self, sheetName, heads, data):
"""
绘图
:param sheetName:
:return:
"""
return self.__worksheet.insert_chart('%s2' % chr(len(heads) + 66), self.__chart_series(sheetName, data),
{'x_offset': 10, 'y_offset': 10})
def close(self):
"""
关闭工作薄
:return:
"""
self.__workbook.close()
def writeData(self, sheet_name, heads, data):
"""
写入流程
:param sheet_name:表名
:param heads:数据头
:param data:数据
:return:
"""
self.__sheet(sheet_name)
self.__write(heads, data)
self.__line_chart(sheet_name, heads, data)
# self.close() # 当前脚本调试时打开
if __name__ == '__main__':
wd = WriteDate()
l = []
li = ['2016-01-15', '20008', '13656']
lo = ['2016-01-15', '20028', '13676', '3328']
lp = ['2016-01-15', '20028']
l.append(li)
l.append(lo)
l.append(lp)
heads = ["a", "b", "c"]
wd.writeData(sheet_name="test", heads=heads, data=l)