/
OutputExcel.py
307 lines (258 loc) · 12.1 KB
/
OutputExcel.py
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
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
from concurrent.futures import ThreadPoolExecutor
import xlwt
import Constants
from logger.loggerFactory import logger
from model.Entities import ManufacturerBean
from service.LightInfosService import LightInfosService
from service.LightService import LightService
from service.ManufacturerService import ManufacturerService
from service.ModelService import ModelService
from service.TechnologyService import TechnologyService
from service.TypeService import TypeService
manufacturerService = ManufacturerService()
modelService = ModelService()
typeService = TypeService()
lightService = LightService()
technologyService = TechnologyService()
lightInfosService = LightInfosService()
workbook = xlwt.Workbook()
title_list = ["系列", "类型"]
default_style = xlwt.easyxf('align: wrap on, vert center;align: horiz center')
# 创建线程池
executor = ThreadPoolExecutor(50)
def create_sheet(sheet_name):
"""创建表"""
return workbook.add_sheet(sheet_name, cell_overwrite_ok=True) # 创建sheet
def set_common_style(name, bold, color_index, height, pattern_fore_colour):
"""单元格样式"""
style = xlwt.XFStyle() # 初始化样式
font = xlwt.Font() # 为样式创建字体
font.name = name
font.bold = bold
font.color_index = color_index
font.height = height
style.font = font
borders = xlwt.Borders()
borders.left = 1
borders.right = 1
borders.top = 1
borders.bottom = 1
borders.bottom_colour = 0x3A
style.borders = borders
alignment = xlwt.Alignment()
alignment.horz = xlwt.Alignment.HORZ_CENTER # 垂直对齐
alignment.vert = xlwt.Alignment.VERT_CENTER # 水平对齐
alignment.wrap = xlwt.Alignment.WRAP_AT_RIGHT # 自动换行
style.alignment = alignment
pattern = xlwt.Pattern()
pattern.pattern = xlwt.Pattern.SOLID_PATTERN
"""
May be: 8 through 63. 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta, 7 = Cyan, 16 = Maroon, 17 = Dark Green, 18 = Dark Blue, 19 = Dark Yellow , almost brown), 20 = Dark Magenta, 21 = Teal, 22 = Light Gray, 23 = Dark Gray, the list goes on...
"""
pattern.pattern_fore_colour = pattern_fore_colour
style.pattern = pattern
return style
def set_title_style(pattern_fore_colour=None):
"""单元格样式"""
if pattern_fore_colour is None:
pattern_fore_colour = 1
return set_common_style('宋体', True, 2, 15 * 20, pattern_fore_colour)
def set_content_style():
"""单元格样式"""
return set_common_style('宋体', False, 1, 12 * 20, 1)
def write_row(sheet, data, row_num, start_col_num=None, style=None):
# if style is None:
# style = set_content_style()
if start_col_num is None:
start_col_num = 0
"""写一行数据"""
if type(data) == list:
for col in range(start_col_num, start_col_num + len(data)):
if style is None:
sheet.write(row_num, col, data[col - start_col_num], default_style)
else:
sheet.write(row_num, col, data[col - start_col_num], style)
else:
raise RuntimeError('格式不正确,必须为list类型数据')
def get_manufacturer_data(manufacturerService, __manufacturer_id):
return manufacturerService.find_by_key(__manufacturer_id)
def get_light_titles(__manufacturer_data):
"""计算前、后、内 灯的表头"""
# 前灯集合
front_title_set = set()
# 后灯集合
rear_title_set = set()
# 内类集合
internal_title_set = set()
model_list = modelService.select_by_manufacturer(__manufacturer_data.manufacturer_id)
for mode in model_list:
type_list = mode.typeList
for type in type_list:
light_list = type.lightList
for light in light_list:
if light.pos_id in Constants.FRONT_POS_ID_LIST:
front_title_set.add(light.use_name)
elif light.pos_id in Constants.REAR_POS_ID_LIST:
rear_title_set.add(light.use_name)
elif light.pos_id in Constants.INTERNAL_POS_ID_LIST:
internal_title_set.add(light.use_name)
front_title_list = list(front_title_set)
rear_title_list = list(rear_title_set)
internal_title_list = list(internal_title_set)
front_title_list.sort()
rear_title_list.sort()
internal_title_list.sort()
logger.info("front_title_list = {}".format(front_title_list))
logger.info("rear_title_list = {}".format(rear_title_list))
logger.info("internal_title_list = {}".format(internal_title_list))
return front_title_list, rear_title_list, internal_title_list
def get_light_dataset(__manufacturer_data, front_title_list, rear_title_list, internal_title_list):
"""生成灯数据列表"""
light_list_data = list()
model_list = __manufacturer_data.modelList
for mode in model_list:
type_list = typeService.select_by_model(mode.model_id)
for type in type_list:
light_list = lightService.select_by_type(type.type_id)
light_dict = _init_light_dict(mode.model_name, type, front_title_list, rear_title_list, internal_title_list)
for light in light_list:
technology_list = technologyService.find_by_type_use(type.type_id, light.use_id)
light_info_str = ""
counter = 0
for technology in technology_list:
counter += 1
light_info_str += "["
light_info_str += technology.technology_name
light_info_str += "] "
lightInfos = lightInfosService.find_by_keys(type.type_id, light.use_id, technology.technology_id)
light_info_str += lightInfos.osram_ece
if counter != len(technology_list):
light_info_str += "\r\n"
light_dict[light.use_name] = light_info_str
light_list_data.append(light_dict)
return light_list_data
def _init_light_dict(model_name, type, front_title_list, rear_title_list, internal_title_list):
light_dict = dict()
light_dict["model_name"] = model_name
light_dict["type_name"] = "{} {}kW built {}.{}-{}.{}".format(type.type_name, type.type_kw,
type.type_from_month, type.type_from_year,
type.type_to_month, type.type_to_year)
for light_name in front_title_list:
light_dict[light_name] = ""
for light_name in rear_title_list:
light_dict[light_name] = ""
for light_name in internal_title_list:
light_dict[light_name] = ""
return light_dict
def create_title(front_title_list, rear_title_list, internal_title_list):
"""生成excel表头"""
pass
def export_excel():
manufacturer_list = manufacturerService.select_all(ManufacturerBean)
for manufacturer in manufacturer_list:
executor.submit(export_excel_work, manufacturer)
# export_excel_work(manufacturer_list[0])
def export_excel_work(manufacturer_data):
# 表头占2行
title_low_num = 2
# 系列 列开始合并的列
model_merge_start = title_low_num
sheet = create_sheet(manufacturer_data.manufacturer_name)
front_title_list, rear_title_list, internal_title_list = get_light_titles(manufacturer_data)
light_list = list()
light_list.extend(front_title_list)
light_list.extend(rear_title_list)
light_list.extend(internal_title_list)
title_len = len(title_list)
front_len = len(front_title_list)
rear_len = len(rear_title_list)
internal_len = len(internal_title_list)
sheet.write_merge(0, 0,
title_len,
title_len + front_len - 1,
"前灯", set_title_style(2))
sheet.write_merge(0, 0,
title_len + front_len,
title_len + front_len + rear_len - 1,
"后灯", set_title_style(3))
sheet.write_merge(0, 0,
title_len + front_len + rear_len,
title_len + front_len + rear_len + internal_len - 1,
"内灯", set_title_style(5))
for col_num in range(0, len(title_list)):
sheet.write_merge(0, 1, col_num, col_num, title_list[col_num], set_title_style())
write_row(sheet, light_list, 1, title_len, set_title_style())
for col_num in range(0, len(light_list) + len(title_list)):
sheet.col(col_num).width = 256 * 20
dataset = get_light_dataset(manufacturer_data, front_title_list, rear_title_list,
internal_title_list)
logger.info("-------------------")
for i in range(0, len(dataset)):
write_row(sheet, list(dataset[i].values()), i + 2, 0)
if i != 0:
if dataset[i]["model_name"] != dataset[i - 1]["model_name"]:
if i - 1 + title_low_num - model_merge_start > 1:
sheet.write_merge(model_merge_start, i - 1 + title_low_num, 0, 0,
dataset[i - 1]["model_name"], default_style)
model_merge_start = i + title_low_num
sheet.write_merge(model_merge_start, len(dataset) - 1 + title_low_num, 0, 0,
dataset[len(dataset) - 1]["model_name"], default_style)
workbook.save(Constants.EXCEL_PATH + "{}.xls".format(manufacturer_data.manufacturer_name))
# if __name__ == '__main__':
# 表头占2行
# title_low_num = 2
#
# # 系列 列开始合并的列
# model_merge_start = title_low_num
#
# manufacturerService = ManufacturerService()
# manufacturer_data = manufacturerService.find_by_key(1)
#
# outputExcel = OutputExcel(Constants.EXCEL_PATH + "{}.xls".format(manufacturer_data.manufacturer_name))
# sheet = outputExcel.create_sheet(manufacturer_data.manufacturer_name)
#
# front_title_list, rear_title_list, internal_title_list = outputExcel.get_light_titles(manufacturer_data)
# light_list = list()
# light_list.extend(front_title_list)
# light_list.extend(rear_title_list)
# light_list.extend(internal_title_list)
#
# title_len = len(outputExcel.title_list)
# front_len = len(front_title_list)
# rear_len = len(rear_title_list)
# internal_len = len(internal_title_list)
# sheet.write_merge(0, 0,
# title_len,
# title_len + front_len - 1,
# "前灯", outputExcel.set_title_style(2))
# sheet.write_merge(0, 0,
# title_len + front_len,
# title_len + front_len + rear_len - 1,
# "后灯", outputExcel.set_title_style(3))
# sheet.write_merge(0, 0,
# title_len + front_len + rear_len,
# title_len + front_len + rear_len + internal_len - 1,
# "内灯", outputExcel.set_title_style(5))
#
# for col_num in range(0, len(outputExcel.title_list)):
# sheet.write_merge(0, 1, col_num, col_num, outputExcel.title_list[col_num], outputExcel.set_title_style())
#
# outputExcel.write_row(sheet, light_list, 1, title_len, outputExcel.set_title_style())
#
# for col_num in range(0, len(light_list) + len(outputExcel.title_list)):
# sheet.col(col_num).width = 256 * 20
#
# dataset = outputExcel.get_light_dataset(manufacturer_data, front_title_list, rear_title_list, internal_title_list)
#
# for i in range(0, len(dataset)):
# outputExcel.write_row(sheet, list(dataset[i].values()), i + 2, 0)
# if i != 0:
# if dataset[i]["model_name"] != dataset[i - 1]["model_name"]:
# if i - 1 + title_low_num - model_merge_start > 1:
# sheet.write_merge(model_merge_start, i - 1 + title_low_num, 0, 0,
# dataset[i - 1]["model_name"], outputExcel.default_style)
# model_merge_start = i + title_low_num
#
# sheet.write_merge(model_merge_start, len(dataset) - 1 + title_low_num, 0, 0,
# dataset[len(dataset) - 1]["model_name"], outputExcel.default_style)
# outputExcel.save()