Controller层方法
@PostMapping("uploadIntentionList") @ResponseBody @ApiOperation("导入文档") public String uploadIntentionFile(@RequestParam("file") MultipartFile file, @RequestParam String type) { String tableName = "intention".equals(type) ? "CRD_DATA_INTEND_CUSTOMER" : ""; if (StringUtils.isBlank(tableName)) { return WebUtil.returnError("清单类型不正确"); } String sql = "INSERT INTO " + tableName + "(ID,ID_CARD,NAME,CELL,CAR_LINES,STATUS,DESCRI) VALUES(?,?,?,?,?,?,?)"; boolean result = false; try { //读取excel List<List<String>> dataList = WebUtil.readExcel(file.getInputStream(), FileValidateUtil.isExcel2003(file.getOriginalFilename()), 0); //读取Excel文件,读取指定sheet //去除表头 if (CollectionUtils.isNotEmpty(dataList) && dataList.get(0).get(0).contains("身份证号码")) { dataList.remove(0); } result = bathSaveIntentionListData(sql, dataList); //批量保存方法 } catch (SQLException e) { return WebUtil.returnError("未完成导入,请检查文件"); } catch (IOException e) { log.error("[清单管理]", e); } if (!result) { return WebUtil.returnError("未完成导入,请检查文件"); } return WebUtil.returnOk("success"); }
WebUtil工具类主要使用方法:
/** * @return java.util.List * @throws * @description 读取excel文件,读取指定sheet * @Param [inputStream, isExcel2003, sheetIndex] * @create 2018/5/18 11:25 */ public static List<List<String>> readExcel(InputStream inputStream, boolean isExcel2003, int sheetIndex) throws IOException { /** 根据版本选择创建Workbook的方式 */ @Cleanup Workbook wb = null; if (isExcel2003) { wb = new HSSFWorkbook(inputStream); } else { wb = new XSSFWorkbook(inputStream); } if (sheetIndex < wb.getNumberOfSheets()) { return read(wb, sheetIndex); } else { return null; } }
批量保存导入的文档方法:
@Transactional(rollbackFor = Exception.class) private Boolean bathSaveIntentionListData(String sql, List<List<String>> datas) throws SQLException { List<Object[]> list = new ArrayList<>(); if (CollectionUtils.isEmpty(datas)) { return false; } for (List<String> data : datas) { if (data.size() < 2) { continue; } //ID,CITYNAME,CITYPRICE,COUNTYPRICE,INPUT_DATE,STATUS,DESCRI String status = ""; if(StringUtils.equals(data.get(4).trim().toString(),"启用")){ status = "1"; }else if(StringUtils.equals(data.get(4).trim().toString(),"停用")){ status = "0"; } Object[] obj = new Object[]{WebUtil.getUUID(), data.get(0).trim(), data.get(1).trim(),data.get(2).trim(),data.get(3).trim(),status,data.get(5).trim()}; list.add(obj); } if (org.apache.commons.collections.CollectionUtils.isEmpty(list)) { return false; } int result = 0; //设置1000次一提交 int flag = 1000; int size = datas.size(); int tempSize = size / flag + 1; boolean special = size % flag == 0; List<Object[]> currentList = null; for (int i = 0; i < tempSize; i++) { if (i == tempSize - 1) { if (special) { break; } currentList = list.subList(flag * i, size); } else { currentList = list.subList(flag * i, flag * (i + 1)); } result += jdbcTemplate.batchUpdate(sql, currentList).length; } return result == datas.size(); }