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.读取本地存的临时文件，解析入库后删除文件夹
@Overridepublic 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<String> fieldCodeList = getFieldData(sheet.getRow(0));//获取明细数据(包含合计行)List<List<Object>> 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;}/*** 获取表头指标* 模板说明：A列至D列依次为序号、地区编码、区划名称和地区名称，数据自E列起；合计行为第5行，分省数据自第6行起。* 请在蓝色背景处，选择导入数据列在系统中的对应指标名称。** @param row* @return*/private List<String> getFieldData(Row row) {List<String> 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;}/*** 获取一个Excel文件对象** @param inputStream* @param fileType* @return*/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;}/*** 转换Sheet表为List<Object>集合** @param sheet* @return*/private List<List<Object>> convertSheet(Sheet sheet) {List<List<Object>> sheetDataList = new ArrayList<>();for (int rowNum = 4; rowNum < sheet.getPhysicalNumberOfRows(); rowNum++) {Row row = sheet.getRow(rowNum);List<Object> rowDataList = convertRow(row);sheetDataList.add(rowDataList);}return sheetDataList;}/*** 转换行为List<Object>集合** @param row* @return*/private List<Object> convertRow(Row row) {List<Object> rowDataList = new ArrayList<>();String xh = null;for (int cellNum = 0; cellNum < row.getPhysicalNumberOfCells(); cellNum++) {Cell cell = row.getCell(cellNum);Object value = convertCell(cell);if (cellNum == 0) {xh = value == null ? "" : value.toString();}if (value == null || StringUtils.isEmpty(value.toString())) {if (cellNum == 1 || cellNum == 3) {throw new RuntimeException("第" + xh + "条数据[地区编码/地区名称]不能为空");}}rowDataList.add(value);}return rowDataList;}/*** 转换每个cell单元格为Object对象** @param cell* @return*/private Object convertCell(Cell cell) {CellType cellType = cell.getCellType();Object value = null;switch (cellType) {case _NONE:case BLANK:case ERROR:break;case STRING:value = cell.getStringCellValue();break;case BOOLEAN:value = cell.getBooleanCellValue();break;case NUMERIC:value = cell.getNumericCellValue();break;case FORMULA:value = cell.getCellFormula();break;}return value;}
