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版本的excel
	boolean 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;
}