1.将导入的excel先保存到本地临时路径下
@PostMapping("/direct-funds-async")
@ApiOperation(value = "直达资金导入",notes = "直达资金导入")
@ApiImplicitParam(name = "multipartFile", value = "文件", required = true, dataType = "MultipartFile")
public AsyncTaskInfo importDirectFundsAsync(@RequestParam("file") MultipartFile multipartFile, HttpServletRequest request) {
LogHelper.info("三保项目", "直达资金导入", "直达资金导入");
ObjectMapper mapper = new ObjectMapper();
String paramJson = request.getParameter("param");
importParam param = null;
try {
mapper.configure(DeserializationFeature.FAIL_ON_UNKNOWN_PROPERTIES, false);
param = mapper.readValue(paramJson, importParam.class);
} catch (IOException e) {
logger.error("出错原因：" + e.getMessage(), e);
throw new RuntimeException("获取参数失败");
}
if (multipartFile == null) {
throw new RuntimeException("未找到文件");
}
//文件名
String fullFileName = multipartFile.getResource().getFilename();
if (StringUtils.isEmpty(fullFileName)) {
throw new RuntimeException("未找到文件名");
}
String fileType = fullFileName.substring(fullFileName.lastIndexOf(".") + 1);
if (!XLSX.equals(fileType) && !XLS.equals(fileType)) {
throw new RuntimeException("导入模板必须为excel文件");
}
param.setFileType(fileType);
// 文件必须先同步保存到本地，否则后续拿不到临时文件
String fileName = multipartFile.getOriginalFilename();
File sourcefile = new File(fileName);
fileName = sourcefile.getName();
SimpleDateFormat sfDate = new SimpleDateFormat("yyyyMMddHHmmssSSS");
String fileLocation = sfDate.format(new Date())+ OrderGenerator.newOrder();
String path = BatchExportConsts.UPLOADDIR + BatchExportConsts.SEPARATOR + fileLocation + BatchExportConsts.SEPARATOR;
File pathFile = new File(FilenameUtils.normalize(path));
if (!pathFile.exists()) {
pathFile.mkdirs();
}
File file = new File(pathFile.getPath() + BatchExportConsts.SEPARATOR + fileName);
try {
multipartFile.transferTo(file);
} catch (Exception e) {
logger.error("出错原因：" + e.getMessage(), e);
throw new RuntimeException("文件下载到本地失败");
}
param.setFileLocation(pathFile.getPath() + BatchExportConsts.SEPARATOR + fileName);
String asynTaskID = asyncTaskManager.publishAndExecuteTask(param, DataPickConfigConstants.ASYNCTASK_import);
AsyncTaskInfo asyncTaskInfo = new AsyncTaskInfo();
asyncTaskInfo.setId(asynTaskID);
asyncTaskInfo.setUrl("/api/asynctask/query?asynTaskID=");
return asyncTaskInfo;
}
2.读取本地存的临时文件，解析入库后删除文件夹
@Override
public importResultVo importDirectFundsAsync(AsyncTaskMonitor asyncTaskMonitor, importParam param) {
importResultVo vo = new importResultVo();
try {
long startTime = System.currentTimeMillis();
asyncTaskMonitor.progressAndMessage(0.01, "读取文件信息");
Workbook workbook = null;
File file = new File(param.getFileLocation());
MultipartFile multipartFile = FileToMultipartFileUtil.toMultipartFile(file.getName(), file);
if (multipartFile == null) {
throw new RuntimeException("未找到导入文件");
}
//文件名
String fullFileName = multipartFile.getResource().getFilename();
if (StringUtils.isEmpty(fullFileName)) {
throw new RuntimeException("未找到文件名");
}
String fileType = param.getFileType();
String fileName = fullFileName.substring(0, fullFileName.lastIndexOf("."));
try {
workbook = this.getWorkbook(multipartFile.getInputStream(), fileType);
Sheet sheet = workbook.getSheetAt(0);
workbook.close();
List fieldCodeList = getFieldData(sheet.getRow(0));
//获取明细数据(包含合计行)
List> dataList = convertSheet(sheet);
if (CollectionUtils.isEmpty(dataList)) {
throw new RuntimeException("sheet页" + fileName + "无明细数据");
}
asyncTaskMonitor.progressAndMessage(0.05, "组装参数信息");
//组装维度、浮动表名等相关信息
buildContextInf(param);
asyncTaskMonitor.progressAndMessage(0.1, "开始导入...");
//插入到浮动表中(全国单位包含合计行)
insertFloatData(fieldCodeList, dataList, param, vo, asyncTaskMonitor);
} catch (Exception e) {
logger.error("sheet页" + fileName + "导入失败", e);
throw new RuntimeException("sheet页" + fileName + "导入失败," + e.getMessage());
} finally {
if (workbook != null) {
workbook.close();
}
}
vo.setMessage("导入成功");
asyncTaskMonitor.finish("导入成功", JsonUtil.objectToJson(vo));
long endTime = System.currentTimeMillis();
logger.info("导入完成,耗时：" + (endTime - startTime) / 1000 + "秒");
} catch (Exception e) {
logger.error("出错原因：" + e.getMessage(), e);
vo.setMessage(e.getMessage());
asyncTaskMonitor.error(e.getMessage(), e);
} finally {
try {
FileUtil.deleteFiles(param.getFileLocation().substring(0, param.getFileLocation().lastIndexOf(File.separator)));
} catch (Exception e) {
logger.error("删除文件失败[" + param.getFileLocation() + "]", e);
}
}
logger.info(vo.toString());
return vo;
}
private List getFieldData(Row row) {
List fieldCode = new ArrayList<>();
for (int col = 0; col < row.getPhysicalNumberOfCells(); col++) {
Cell cell = row.getCell(col);
String title = cell.getStringCellValue();
//序号列
if (0 == col) {
fieldCode.add("xh");
continue;
}
//区划名称列
if (2 == col) {
fieldCode.add(QHMC);
continue;
}
if (StringUtils.isEmpty(title)) {
if (1 == col || 3 == col) {
throw new RuntimeException("第一行[地区编码/地区名称]不能为空");
}
continue;
}
if (title.indexOf("[") < 0 || title.indexOf("]") < 0) {
throw new RuntimeException("第一行第" + (col + 1) + "列文件内容格式不正确");
}
String code = title.substring(title.indexOf("[") + 1, title.indexOf("]"));
fieldCode.add(code);
}
return fieldCode;
}
private Workbook getWorkbook(InputStream inputStream, String fileType) throws Exception {
Workbook workbook = null;
if (fileType.equalsIgnoreCase(XLS)) {
workbook = new HSSFWorkbook(inputStream);
} else if (fileType.equalsIgnoreCase(XLSX)) {
workbook = new XSSFWorkbook(inputStream);
}
return workbook;
}
private List> convertSheet(Sheet sheet) {
List> sheetDataList = new ArrayList<>();
for (int rowNum = 4; rowNum < sheet.getPhysicalNumberOfRows(); rowNum++) {
Row row = sheet.getRow(rowNum);
List
