java版poi+excel导入树形数据实例2

目标：

上传excel，进行poi解析成树结构并存入数据库

原理：

poi解析excel表格，装入map，组装成树结构，进行统一存储

导入jar包：

代码实例：

1.jsp代码

<form method="post" enctype="multipart/form-data" action="${ctxa}/product/productCategory/saveExcel.do">  <input id="file" type="file"/>  <button type="submit" class="btn btn-info"></button>  </form>

注意：form表单属性enctype="multipart/form-data"，是必须的

2.java代码

@RequestMapping(value = "saveExcel")public String saveExcel(@RequestParam("excel") MultipartFile excel, RedirectAttributes redirectAttributes) {//接取参数String status = request.getParameter("satus");//判断是否上传文件if (excel==null) {model.addAttribute("message", "请选择文件");return "error";}//获取文件后缀名String ext = "";int index = name.lastIndexOf(".");if (index != -1) {ext = name.substring(index);}//判断后缀名是否满足要求if (!(".xls".equalsIgnoreCase(ext) || ".xlsx".equalsIgnoreCase(ext))) {addMessage(redirectAttributes, "文件格式不符合");return "/error";}//读取文件大小byte[] data = null;try {data = file.getBytes();} catch (IOException e1) {logger.error("excel-读取失败", e1);}//清空,再导入if("2".equals(status)){Wrapper wrapper = new Wrapper();wrapper.and("1=",1);productCarService.deleteByWhere(wrapper);}// 判断是不是03版本的excelboolean is03Excel = excel.getOriginalFilename().matches("^.+\\.(?i)(xls)$");// 读取工作薄Workbook workbook=null;try {InputStream inputStream = excel.getInputStream();workbook = is03Excel ? new HSSFWorkbook(inputStream) : new XSSFWorkbook(inputStream);} catch (FileNotFoundException e) {logger.error("e1：",e);} catch (IOException e) {logger.error("e1：",e);}//获取的是物理行数，也就是不包括那些空行（隔行）的情况。Sheet sheet = workbook.getSheetAt(0);if (sheet.getPhysicalNumberOfRows() <= 1) {model.addAttribute("message", "Excel格式与模板格式不一致！");return "/error";}//将Excel的数据poi解析导入数据库中int fail = 0;//导入失败行数int success=0;//导入成功行数Map<String, ProductCar> map1 = new HashMap<String, ProductCar>();//1级分类Map<String,Map<String, ProductCar>> map2 = new HashMap<String,Map<String, ProductCar>>();//2级分类(键为上级名字)Map<String,Map<String, ProductCar>> map3 = new HashMap<String,Map<String, ProductCar>>();//3级分类(键为上级名字)for (int i = 1;  i< sheet.getPhysicalNumberOfRows(); i++) {// 读取单元格Row row = sheet.getRow(i);int colNum = row.getPhysicalNumberOfCells();for (int j = 0; j < colNum; j++) {if(j >= 0 && j <= 3){ProductCar productCarParent = new ProductCar();ProductCar productCar = new ProductCar();String name = getCellFormatValue(row.getCell((short) j));if(StringUtils.isNotBlank(name)){productCar.setName(name);if(j == 0){//1级//获取当前name在是否存在ProductCar car = map1.get(name);if(car == null){//不存在productCarParent.setCarId(0L);productCar.setParent(productCarParent);productCar.setParentIds("0,");productCarService.insertSelective(productCar);map1.put(name, productCar);success++;}}if(j == 1){//2级//获取当前name在1级是否存在String nameParent = getCellFormatValue(row.getCell((short) j-1));if(StringUtils.isNotBlank(nameParent)){ProductCar carParent = map1.get(nameParent);if(carParent!=null){Map<String, ProductCar> carMapParent = map2.get(carParent.getName());if(carMapParent == null || (carMapParent !=null && carMapParent.get(name) == null)){if(carMapParent == null){carMapParent = new HashMap<String, ProductCar>();}productCar.setParent(carParent);productCar.setParentIds(carParent.getParentIds() + carParent.getCarId() + ",");productCarService.insertSelective(productCar);carMapParent.put(name, productCar);map2.put(nameParent, carMapParent);success++;}}}}if(j == 2){//3级//获取当前name在2级是否存在String nameParentParent = getCellFormatValue(row.getCell((short) j-2));String nameParent = getCellFormatValue(row.getCell((short) j-1));if(StringUtils.isNotBlank(nameParentParent) && StringUtils.isNotBlank(nameParent)){Map<String, ProductCar> carMapParent = map2.get(nameParentParent);if(carMapParent !=null && carMapParent.get(name) == null){ProductCar carParent = carMapParent.get(nameParent); productCar.setParent(carParent);productCar.setParentIds(carParent.getParentIds() + carParent.getCarId() + ",");productCarService.insertSelective(productCar);carMapParent.put(name, productCar);map3.put(nameParent, carMapParent);success++;}}}if(j == 3){//4级//获取当前name在3级是否存在String nameParentParent = getCellFormatValue(row.getCell((short) j-2));String nameParent = getCellFormatValue(row.getCell((short) j-1));if(StringUtils.isNotBlank(nameParentParent) && StringUtils.isNotBlank(nameParent)){Map<String, ProductCar> carMapParent = map3.get(nameParentParent);if(carMapParent !=null && carMapParent.get(name) == null){ProductCar carParent = carMapParent.get(nameParent); productCar.setParent(carParent);productCar.setParentIds(carParent.getParentIds() + carParent.getCarId() + ",");productCarService.insertSelective(productCar);carMapParent.put(name, productCar);map3.put(nameParent, carMapParent);success++;}}}}}}}addMessage(redirectAttributes, "成功导入excel");return "redirect:"+Global.getAdminPath()+"/product/productCar/importExcel.do?success="+success+"&fail="+fail;}/** * 根据HSSFCell类型设置数据 * @param cell * @return */private String getCellFormatValue(Cell cell) {String cellvalue = "";if (!(cell == null || "".equals(cell))) {cell.setCellType(Cell.CELL_TYPE_STRING);cellvalue = cell.getStringCellValue();}if(StringUtils.isNotBlank(cellvalue)){cellvalue = cellvalue.trim();}return cellvalue;}