使用原生excel方式导入excel
conreoller层
@RequestMapping(value = \"/importSave\", method = RequestMethod.POST) public ResultInfo importSave(@RequestParam(\"file\") MultipartFile file,HttpServletRequest request) throws Exception { PfUserInfo curUserInfo = (PfUserInfo) ControllerUtils.getCurUserInfo(request); LinkedHashMap<Integer, List> errMap = pjProjectInfoService.readExcel(file,curUserInfo); if (errMap != null && errMap.size() > 0) { JSONArray json = new JSONArray(); JSONObject obj = null; JSONArray errJson = null; List list = null; JSONObject errObj = null; for (Map.Entry<Integer, List> entry : errMap.entrySet()) { obj = new JSONObject(); obj.put(\"row\", entry.getKey()); list = entry.getValue(); errJson = new JSONArray(); for (String err : list) { errObj = new JSONObject(); errObj.put(\"error\", err); errJson.add(errObj); } obj.put(\"errList\", errJson); json.add(obj); } return ResultInfo.ok(json); } return ResultInfo.ok(); }service层
@Override public LinkedHashMap<Integer, List> readExcel(MultipartFile file, PfUserInfo curUserInfo) throws Exception { LinkedHashMap<Integer, List> errMap = new LinkedHashMap(); //检查文件 this.checkFile(file); //获得Workbook工作薄对象 Workbook workbook = getWorkBook(file); boolean isXSSFWorkbook = !(workbook instanceof HSSFWorkbook); //获取文档指定sheet中所有图片：图片位置、文件流 Map pictures; //创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回 List list = new ArrayList(); if (workbook != null) { //获得当前sheet工作表 Sheet sheet = workbook.getSheetAt(1); if (sheet == null) { return null; } if (isXSSFWorkbook) { pictures = getSheetPictures07((XSSFSheet) sheet, (XSSFWorkbook) workbook); } else { pictures = getSheetPictures03((HSSFSheet) sheet, (HSSFWorkbook) workbook); } //获得当前sheet的结束行 int lastRowNum = sheet.getLastRowNum(); //循环除了第一行的所有行 this.rowList(sheet, lastRowNum, pictures, errMap, curUserInfo); workbook.close(); } else { throw new BusinessException(\"导入的文件没有对应的内容\"); } return errMap; }上述service层中有调用其他方法
private void checkFile(MultipartFile file) throws IOException { //判断文件是否存在 if (null == file) { throw new FileNotFoundException(\"文件不存在！\"); } //获得文件名 String fileName = file.getOriginalFilename(); //判断文件是否是excel文件 if (!fileName.endsWith(\"xls\") && !fileName.endsWith(\"xlsx\")) { throw new IOException(fileName + \"不是excel文件\"); } } private Workbook getWorkBook(MultipartFile file) { //获得文件名 String fileName = file.getOriginalFilename(); //创建Workbook工作薄对象，表示整个excel Workbook workbook = null; try { //获取excel文件的io流 InputStream is = file.getInputStream(); //根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象 if (fileName.endsWith(\"xls\")) { //2007往下 workbook = new HSSFWorkbook(is); } else if (fileName.endsWith(\"xlsx\")) { //2007往上 workbook = new XSSFWorkbook(is); } } catch (IOException e) { e.printStackTrace(); } return workbook; } @SneakyThrows private void rowList(Sheet sheet, int lastRowNum, Map pictures, LinkedHashMap<Integer, List> errMap, PfUserInfo curUserInfo) { List errList; PjProjectInfo pjProjectInfo = new PjProjectInfo(); pjProjectInfo.setProjectCode(this.nextProjectCode()); pjProjectInfo.setSoType(PjProjectInfo.SOTYPE_NEWMODEL); pjProjectInfo.setManager(curUserInfo); pjProjectInfo.setCreateTime(new Date()); //获取第一行数据，进行项目名称获取 Row row = sheet.getRow(0); if (Optional.ofNullable(row).isPresent()) { pjProjectInfo.setProjectName(String.valueOf(row.getCell(0))); } //获取第二行数据，供应商获取 row = sheet.getRow(1); //具体的数据明细行 int startRow = 4; //导入的行数据对象封装 List list = new ArrayList(); if (lastRowNum < startRow) return; // 零件信息 LinkedHashMap projectList = new LinkedHashMap(); // 模具信息 LinkedHashMap<String, List> moldList = new LinkedHashMap(); // 图片信息 LinkedHashMap imageList = new LinkedHashMap(); Cell cell; for (int rowNum = startRow; rowNum <= lastRowNum; rowNum++) { //获得当前行 row = sheet.getRow(rowNum); if (!Optional.ofNullable(row).isPresent()) { continue; } errList = new ArrayList(); //获得当前行的开始列 int firstColumn = row.getFirstCellNum(); //获得当前行的列数 int lastColumn = row.getPhysicalNumberOfCells(); //行内所有列的值 String[] cells = new String[lastColumn]; //循环当前行的所有列 for (int column = firstColumn; column < lastColumn; column++) { //遍历中的某列 cell = row.getCell(column); // 判断是否合并行 boolean isMerge = isMergedRow(sheet, rowNum, column); if (isMerge) { PictureData image = pictures.get(row.getRowNum() + \"_\" + column); //是合并行， cells[column] = getMergedRegionValue(sheet, rowNum, column); if (image != null) { imageList.put((cells[1] + \"__\" + cells[2]), image); } } else { //默认当前列内值 cells[column] = ExcelUtil.getValue(cell); } } list.add(cells); // 验证数据 this.verifyData(cells, rowNum + 1, errList, errMap, projectList, moldList); } // 保存数据 if (errMap.size() == 0) { this.saveData(pjProjectInfo, projectList, moldList, imageList, curUserInfo); } } private boolean isMergedRow(Sheet sheet, int row, int column) { //当前表格(sheet)中，所有合单元格总数 int sheetMergeCount = sheet.getNumMergedRegions(); //遍历所有合并单元格 for (int i = 0; i = firstRow && row = firstColumn && column <= lastColumn) { return true; } } } return false; } private String getMergedRegionValue(Sheet sheet, int row, int column) { // 获得该sheet所有合并单元格数量 int sheetMergeCount = sheet.getNumMergedRegions(); for (int i = 0; i = firstRow && row = firstColumn && column <= lastColumn) { Row firRow = sheet.getRow(firstRow); //获取单元格内容方法 return getCellValue(firRow, firstColumn).toString(); } } } // 如果该单元格行号列号不在任何一个合并区域，则返回null return null; } private Object getCellValue(Row row, int column) { if (row == null) { return row; } Object val = \"\"; try { Cell cell = row.getCell(column); switch (cell.getCellTypeEnum()) { //数值 case NUMERIC: // 如果是date类型则 ，获取该cell的date值 if (DateUtil.isCellDateFormatted(cell)) { val = DateUtil.getJavaDate(cell.getNumericCellValue()); } else { // 纯数字 val = cell.getNumericCellValue(); } break; //小数 case FORMULA: if ((Double) cell.getNumericCellValue() % 1 != 0) { val = new BigDecimal(cell.getNumericCellValue()); } else { val = new DecimalFormat(\"0\").format(cell.getNumericCellValue()); } break; // 字符串型 case STRING: val = cell.getStringCellValue(); break; // 布尔值 case BOOLEAN: val = cell.getBooleanCellValue(); break; // 故障 case ERROR: val = cell.getErrorCellValue(); break; default: val = cell.getRichStringCellValue().toString(); } } catch (Exception e) { e.printStackTrace(); } return val; }
