TTInfoDetailDataToolsImpl.java
10.7 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
package com.bsth.service.schedule.datatools;
import com.bsth.service.schedule.exception.ScheduleException;
import com.bsth.service.schedule.utils.DataToolsProperties;
import com.bsth.service.schedule.utils.DataToolsService;
import jxl.Cell;
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.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.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* 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 File uploadFile(String filename, byte[] filedata) throws ScheduleException {
try {
// 对上传的excel文件做处理，将第一个sheet名字设定为工作表1
File 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;
} 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文件名
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<>();
Workbook workbook = Workbook.getWorkbook(new File(filename));
Sheet sheet = workbook.getSheet(sheetname);
Cell[] cells = sheet.getRow(0);
for (int i = 0; i < cells.length; i++) {
if (i == 0) {
colList.add(cells[i].getContents().trim());
} else {
colList.add(cells[i].getContents() + i);
}
}
File fileCal = new File(filename + "_stringType.xls");
WritableWorkbook writableWorkbook = Workbook.createWorkbook(fileCal, workbook);
WritableSheet sheet1 = writableWorkbook.getSheet(sheetname);
for (int i = 0; i < sheet1.getColumns(); i++) { // 第一行数据
sheet1.addCell(new Label(i, 0, colList.get(i)));
}
for (int i = 1; i < sheet1.getRows(); i++) { // 第二行开始
Cell[] cells1 = sheet.getRow(i);
for (int j = 0; j < cells1.length; j++) {
sheet1.addCell(new Label(j, i, cells1[j].getContents()));
}
}
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 File 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")));
File 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 File exportDataForEdit(Integer xlid, Long ttid) throws ScheduleException {
try {
// 创建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));
return dataToolsService.exportData(ktrParms);
} catch (Exception exp) {
throw new ScheduleException(exp);
}
}
}