TTInfoDetailDataToolsImpl.java
17.1 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
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
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
package com.bsth.service.schedule.datatools;
import com.bsth.service.schedule.exception.ScheduleException;
import com.bsth.service.schedule.utils.*;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Row;
import org.joda.time.DateTime;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.EnableConfigurationProperties;
import org.springframework.stereotype.Service;
import java.io.File;
import java.io.PrintWriter;
import java.io.StringWriter;
import java.util.*;
/**
* Created by xu on 17/5/16.
*/
@EnableConfigurationProperties(DataToolsProperties.class)
@Service(value = "ttInfoDetail_dataTool")
public class TTInfoDetailDataToolsImpl implements DataToolsService, TTInfoDetailForEdit {
/** 日志记录器 */
private final static Logger LOGGER = LoggerFactory.getLogger(TTInfoDetailDataToolsImpl.class);
@Autowired
@Qualifier(value = "dataToolsServiceImpl")
private DataToolsService dataToolsService;
@Autowired
private DataToolsProperties dataToolsProperties;
@Override
public DataToolsFile uploadFile(String filename, byte[] filedata) throws ScheduleException {
try {
// 对上传的excel文件做处理，将第一个sheet名字设定为工作表1
DataToolsFile file = dataToolsService.uploadFile(filename, filedata);
// Workbook workbook = Workbook.getWorkbook(file);
// Sheet sheet = workbook.getSheet(0);
//
// File fileCal = new File(file.getAbsolutePath() + "_sheetChange.xls");
// WritableWorkbook writableWorkbook = Workbook.createWorkbook(fileCal);
// WritableSheet writableSheet = writableWorkbook.createSheet("工作表1", 0);
// for (int i = 0; i < sheet.getRows(); i++) {
// Cell[] cells = sheet.getRow(i);
// for (int j = 0; j < cells.length; j++) {
// writableSheet.addCell(new Label(j, i, cells[j].getContents()));
// }
// }
// writableWorkbook.write();
// writableWorkbook.close();
//
// return fileCal;
return file;
} catch (Exception exp) {
throw new ScheduleException(exp);
}
}
@Override
public void importData(File file, Map<String, Object> params) throws ScheduleException {
try {
LOGGER.info("//---------------- 导入时刻表明细 start... ----------------//");
String filename = file.getAbsolutePath(); // xls xlsx 文件名
String sheetname = String.valueOf(params.get("sheetname")); // sheet名字
Long ttid = Long.valueOf(String.valueOf(params.get("ttid"))); // 时刻表id
Long xlid = Long.valueOf(String.valueOf(params.get("xlid"))); // 线路id
Integer lineid = Integer.valueOf(String.valueOf(params.get("lineinfo"))); // 线路标准id
String xlname = String.valueOf(params.get("xlname")); // 线路名字
String ttname = String.valueOf(params.get("ttname")); // 时刻表名字
LOGGER.info("参数1, xls文件名={},sheet名字={}", filename, sheetname);
LOGGER.info("参数2, 线路id={},线路名字={}", xlid, xlname);
LOGGER.info("参数3, 时刻表id={},时刻表名字={}", ttid, ttname);
LOGGER.info("转换xls文件格式成文本格式...");
// 1、修改已经上传的excel文件，在每个起点站后标示数字，表示第几个班次
// 2、由于格式问题，需要把内容都转换成字符串
List<String> colList = new ArrayList<>();
// poi api，并读取第一行数据，组合成站点列表，逗号分隔
org.apache.poi.ss.usermodel.Workbook poi_workbook;
org.apache.poi.ss.usermodel.Sheet poi_sheet;
if (DataToolsFileType.XLS.isThisType(file)) {
poi_workbook = DataToolsFileType.XLS.getWorkBook(file);
} else if (DataToolsFileType.XLSX.isThisType(file)) {
poi_workbook = DataToolsFileType.XLSX.getWorkBook(file);
} else {
throw new Exception("不是xls xlsx文件!");
}
poi_sheet = poi_workbook.getSheet(sheetname);
int rownums = poi_sheet.getLastRowNum() + 1;
int colnums = poi_sheet.getRow(0).getLastCellNum();
Row firstrow = poi_sheet.getRow(0);
for (int i = 0; i < colnums; i++) {
org.apache.poi.ss.usermodel.Cell cell = firstrow.getCell(i);
if (i == 0) {
colList.add(PoiUtils.getStringValueFromCell(cell).trim());
} else {
colList.add(PoiUtils.getStringValueFromCell(cell) + i);
}
}
// jxl api
File fileCal = new File(filename + "_stringType.xls");
WritableWorkbook writableWorkbook = Workbook.createWorkbook(fileCal);
WritableSheet sheet1 = writableWorkbook.createSheet(sheetname, 0);
for (int i = 0; i < colnums; i++) { // 第一行数据
sheet1.addCell(new Label(i, 0, colList.get(i)));
}
for (int i = 1; i < rownums; i++) { // 第二行开始
for (int j = 0; j < colnums; j++) {
// poi读
String cellContent = PoiUtils.getStringValueFromCell(
poi_sheet.getRow(i).getCell(j)
);
// jxl写
sheet1.addCell(new Label(j, i, cellContent));
}
}
writableWorkbook.write();
writableWorkbook.close();
// 2、删除原有数据
// 操作在ktr内部执行
// 3、导入时刻表
// 计算表头参数
Workbook book = Workbook.getWorkbook(fileCal);
Sheet sheet_exp = book.getSheet(sheetname);
List<String> columnames = new ArrayList<>();
for (int i = 0; i < sheet_exp.getColumns(); i++) { // 获取第一行，数据，作为列名
columnames.add(sheet_exp.getCell(i, 0).getContents());
}
LOGGER.info("表头={}", StringUtils.join(columnames.toArray(), ","));
// 创建ktr转换所需参数
Map<String, Object> ktrParms = new HashMap<>();
File ktrFile = new File(this.getClass().getResource(
dataToolsProperties.getTtinfodetailMetadatainputktr()).toURI());
// File ktrFile2 = new File(this.getClass().getResource(
// dataToolsProperties.getTtinfodetailDatainputktr()).toURI());
File ktrFile2 = new File(this.getClass().getResource(
dataToolsProperties.getTtinfodetailDatainputktr2()).toURI());
// 通用参数，转换文件路径，excel输入文件路径，错误输出文件路径
ktrParms.put("transpath", ktrFile.getAbsolutePath());
ktrParms.put("filepath", fileCal.getAbsolutePath());
ktrParms.put("erroroutputdir", dataToolsProperties.getTransErrordir());
// 附加参数
ktrParms.put("injectktrfile", ktrFile2.getAbsolutePath()); // 注入元数据的ktr文件
ktrParms.put("sheetname", sheetname); // sheet工作区的名字
ktrParms.put("lineinfoid", lineid); // 线路标准id
ktrParms.put("xlname", xlname); // 线路名称
ktrParms.put("ttinfoname", ttname); // 时刻表名称
ktrParms.put("ttid", ttid.intValue()); // 时刻表id
ktrParms.put("excelfieldnames", StringUtils.join(columnames.toArray(), ",")); // 时刻表excel输入字段名，以逗号连接
columnames.remove(0);
ktrParms.put("normalizefieldnames", StringUtils.join(columnames.toArray(), ",")); // 数据范式化字段名，以逗号连接
dataToolsService.importData(fileCal, ktrParms);
LOGGER.info("//---------------- 导入时刻表明细 success... ----------------//");
} catch (Exception exp) {
LOGGER.info("//---------------- 导入时刻表明细 failed... ----------------//");
StringWriter sw = new StringWriter();
exp.printStackTrace(new PrintWriter(sw));
LOGGER.info(sw.toString());
throw new ScheduleException(exp.getMessage());
}
}
@Override
public DataToolsFile exportData(Map<String, Object> params) throws ScheduleException {
try {
LOGGER.info("//---------------- 导出时刻表明细 start... ----------------//");
// 创建ktr转换所需参数
Map<String, Object> ktrParms = new HashMap<>();
File ktrFile = new File(this.getClass().getResource(
dataToolsProperties.getTtinfodetailMetaoutput()).toURI());
File ktrFile2 = new File(this.getClass().getResource(
dataToolsProperties.getTtinfodetailOutput()).toURI());
// 通用参数，转换文件路径，excel输出文件名
ktrParms.put("transpath", ktrFile.getAbsolutePath());
ktrParms.put("filename", String.format("时刻表_(id=%s)_download-", String.valueOf(params.get("ttinfoid"))));
// 附加参数
ktrParms.put("injectktrfile", ktrFile2.getAbsolutePath()); // 注入元数据的ktr文件
ktrParms.put("ttinfoid", String.valueOf(params.get("ttinfoid")));
DataToolsFile file = dataToolsService.exportData(ktrParms);
LOGGER.info("//---------------- 导出时刻表明细 success... ----------------//");
return file;
} catch (Exception exp) {
LOGGER.info("//---------------- 导出时刻表明细 failed... ----------------//");
StringWriter sw = new StringWriter();
exp.printStackTrace(new PrintWriter(sw));
LOGGER.info(sw.toString());
throw new ScheduleException(exp.getMessage());
}
}
@Override
public EditInfo getEditInfo(Integer xlid, Long ttid, Long maxfcno) throws ScheduleException {
try {
LOGGER.info("//---------------- 时刻表编辑用数据输出 start... ----------------//");
// 创建ktr转换所需参数
Map<String, Object> ktrParms = new HashMap<>();
File ktrFile = new File(this.getClass().getResource(
dataToolsProperties.getTtinfodetailForeditktr()).toURI());
// 通用参数，转换文件路径，excel输出文件名，错误输出文件路径
ktrParms.put("transpath", ktrFile.getAbsolutePath());
ktrParms.put("filename", "todo");
// 附加参数
String outputFilePath = String.format("ttinfodetail_(id=%s)_foredit-%s",
String.valueOf(ttid), new DateTime().toString("yyyyMMddHHmmss"));
ktrParms.put("tempfilepath", dataToolsProperties.getTransTempdir() + File.separator + outputFilePath); // 数据输出文件路径
ktrParms.put("xlid", String.valueOf(xlid));
ktrParms.put("ttid", String.valueOf(ttid));
dataToolsService.exportData(ktrParms);
EditInfo editInfo = new EditInfo(); // 输出数据
// 1.6、获取最大的发车数，用于输出数据的数量
LOGGER.info("最大发车顺序号={}", maxfcno);
if (maxfcno != null) {
// 2、读取ktr生成的excel数据，组织编辑用数据返回
// 2-1、读取Excel文件
Workbook book = Workbook.getWorkbook(new File(dataToolsProperties.getTransTempdir() +
File.separator + outputFilePath + ".xls"));
Sheet sheet = book.getSheet(0);
// 2-2、处理数据
int all_bc_ks = 0; // 总空驶班次
int all_bc_yy = 0; // 总营运班次
double all_lc_ks = 0; // 总空驶里程
double all_lc_yy = 0; // 总营运里程
String[] headarrays = new String[maxfcno.intValue() + 3];
headarrays[0] = "路牌";
headarrays[maxfcno.intValue() + 1] = "空驶班次/空驶里程";
headarrays[maxfcno.intValue() + 2] = "运营班次/运营里程";
for (int r = 1; r < sheet.getRows(); r++) {
List<FcInfo> fcInfos = new ArrayList<>();
// 每行第一列都是路牌
fcInfos.add(new FcInfo(null, null, sheet.getCell(0, r).getContents(), null, null, null, null)); // 用fcsj放置路牌显示
int bc_ks = 0; // 空驶班次
int bc_yy = 0; // 营运班次
double lc_ks = 0; // 空驶里程
double lc_yy = 0; // 营运里程
for (int c = 1; c <= maxfcno; c++) {
String content_str = sheet.getCell(c, r).getContents();
try {
String[] content = StringUtils.isEmpty(content_str) ? null : content_str.split(","); // 总的内容
String ttdid_str = content == null ? "" : content[0]; // 时刻表明细id
String fcsj = content == null ? "" : content[1]; // 发车时间
String jhlc = content == null ? "" : content[2]; // 计划里程
String fzdname = content == null ? "" : content[3]; // 发车站点名称
String bctype = content == null ? "" : content[4]; // 班次类型
String xldir = content == null ? "" : content[5]; // 线路上下行
String isfb = content == null ? "" : content[6]; // 是否分班
String qdzCode = content == null ? "" : content[7]; // 起点站编码
String zdzCode = content == null ? "" : content[8]; // 终点站编码
FcInfo fcInfo = new FcInfo(ttdid_str, bctype, fcsj, xldir, isfb, qdzCode, zdzCode);
if (StringUtils.isNotEmpty(fzdname))
headarrays[c] = fzdname;
fcInfos.add(fcInfo);
// 计算班次里程
if (StringUtils.isNotEmpty(jhlc)) {
if ("in".equals(bctype) || "out".equals(bctype) || "ldks".equals(bctype)) {
bc_ks += 1;
lc_ks += Double.valueOf(jhlc);
all_bc_ks += 1;
all_lc_ks += Double.valueOf(jhlc);
} else {
bc_yy += 1;
lc_yy += Double.valueOf(jhlc);
all_bc_yy += 1;
all_lc_yy += Double.valueOf(jhlc);
}
}
} catch (Exception exp) {
exp.printStackTrace();
LOGGER.info("第{}行，第{}列数据有问题，数据={}，异常message={}", r, c, content_str, exp.getCause());
break;
}
}
// 添加一列 空驶班次/空驶里程，fcsj放置数据
fcInfos.add(new FcInfo(null, null, String.format("%d/%.3f", bc_ks, lc_ks), null, null, null, null));
// 添加一列 营运班次/营运里程，fcsj放置数据
fcInfos.add(new FcInfo(null, null, String.format("%d/%.3f", bc_yy, lc_yy), null, null, null, null));
editInfo.getContents().add(fcInfos);
}
editInfo.getHeader().addAll(Arrays.asList(headarrays));
editInfo.setYy_desc(String.format("空驶班次=%d,营运班次=%d,空驶里程=%.3f,营运里程=%.3f,总里程=%.3f", all_bc_ks, all_bc_yy, all_lc_ks, all_lc_yy, all_lc_ks + all_lc_yy));
}
LOGGER.info("//---------------- 时刻表编辑用数据输出 success... ----------------//");
return editInfo;
} catch (Exception exp) {
LOGGER.info("//---------------- 时刻表编辑用数据输出 failed... ----------------//");
StringWriter sw = new StringWriter();
exp.printStackTrace(new PrintWriter(sw));
LOGGER.info(sw.toString());
throw new ScheduleException(exp.getMessage());
}
}
}