有关使用Excel读取用户上传模板问题
需求是这样的：
用户根据模板填写对应的信息，后端代码对模板进行解析，然后使用Map进行存储，返回map集合。
解决方案：
if(null==multipartFile || multipartFile.getSize()<=0){ result.setMsg("文件不可为空,请重新上传"); result.setCode(BaseResponseEnum.ParamError.getState()); result.setSuccess(false); result.setT(null); return result; } String filename = multipartFile.getOriginalFilename(); if(!(filename.endsWith(".xls")|| filename.endsWith(".xlsx"))){ result.setMsg("文件上传的格式不正确,请重新上传"); result.setCode(BaseResponseEnum.ParamError.getState()); result.setSuccess(false); result.setT(null); return result; } Map<String,List<ExcelPo>> resultMap=new HashMap<>(); try{ if(filename.endsWith(".xls")){ resultMap=readXLS(multipartFile); }else{ resultMap=readXLSX(multipartFile); } result.setT(resultMap); return result; }catch (Exception e){ result.setMsg("内容读取失败，请重试尝试上传！"); return result; } } //excel后缀为XLSX格式的方法 private Map<String,List<ExcelPo>> readXLSX(MultipartFile multipartFile) throws IOException { ArrayList<ExcelPo> list = new ArrayList<>(); Map<String,List<ExcelPo>> map= new HashMap<>(); InputStream inputStream = multipartFile.getInputStream(); XSSFWorkbook Workbook = new XSSFWorkbook(inputStream); XSSFSheet sheet = Workbook.getSheetAt(0); int lastRowNum = sheet.getLastRowNum(); for (int rowNum = 1; rowNum <= lastRowNum; rowNum++) { XSSFRow row = sheet.getRow(rowNum); if (row != null) { ExcelPo excelPo = new ExcelPo(); //把对应不是为String的列 设置单元列为字符 row.getCell(2).setCellType(CellType.STRING); row.getCell(0).setCellType(CellType.STRING); excelPo.setOrder(Integer.parseInt(row.getCell(0).getStringCellValue())); excelPo.setHotelId(Long.parseLong(row.getCell(2).getStringCellValue())); //判断map中的Key是否已经包含对应的Id,包含的话 则把对应的ExcelPO信息添加作为value if( map.containsKey(row.getCell(1).getStringCellValue())){ map.get(row.getCell(1).getStringCellValue()).add(hotelExcelPo); }else { //否则 创建新的集合，作为value 添加到map List<ExcelPo> list1 = new ArrayList<>(); list1.add(excelPo); map.put(row.getCell(1).getStringCellValue(),list1); } } } return map; } //excel后缀为XLS格式的方法 private Map<String,List<ExcelPo>> readXLS(MultipartFile multipartFile) throws IOException { ArrayList<ExcelPo> list = new ArrayList<>(); Map<String,List<HotelExcelPo>> map=new HashMap<>(); InputStream inputStream = multipartFile.getInputStream(); HSSFWorkbook workbook = new HSSFWorkbook(inputStream); HSSFSheet sheet = workbook.getSheetAt(0); //遍历每一行Excel获取内容 for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) { HSSFRow row = sheet.getRow(rowNum); if (row!=null){ ExcelPo excelPo = new ExcelPo(); row.getCell(0).setCellType(CellType.STRING); row.getCell(2).setCellType(CellType.STRING); excelPo.setOrder(Integer.parseInt(row.getCell(0).getStringCellValue())); excelPo.setHotelId(Long.parseLong(row.getCell(2).getStringCellValue())); if( map.containsKey(row.getCell(1).getStringCellValue())){ map.get(row.getCell(1).getStringCellValue()).add(excelPo); }else { List< ExcelPo> list1 = new ArrayList<>(); list1.add(hotelExcelPo); map.put(row.getCell(1).getStringCellValue(),list1); } } } return map; }
