java版poi+excel导入实例1

目标：

上传excel，进行poi解析并存入数据库

导入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(value = "file", required = false) MultipartFile file, 
		HttpServletRequest request, RedirectAttributes redirectAttributes) {
	//获取参数
	String purchaesId = request.getParameter("id");
	String memId = request.getParameter("memId");
	String traderId = request.getParameter("traderId");

	//获取上传文件名
	String name = file.getOriginalFilename();

	//获取文件后缀名
	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 (data == null || data.length > 20971520 || data.length == 0) {
		addMessage(redirectAttributes, "文件大小不符合");
		return "/error";
	}

	//加密上传文件名
	FileStorage lf = FileStorageFcatory.get();
	String fileName = lf.fileName(ext);

	//写入存储服务器
	try {
		lf.write2(data, fileName);
	} catch (IOException e) {
		logger.error("后台-采购单excel-写入失败", e);
	}

	//poi解析excel
	InputStream inputStream = null;
	try {
		inputStream = lf.openInputStream(fileName);
		// 判断是不是03版本的excel
		boolean is03Excel = name.matches("^.+\\.(?i)(xls)$");
		// 读取工作薄
		Workbook workbook = is03Excel ? new HSSFWorkbook(inputStream) : new XSSFWorkbook(inputStream);
		Sheet sheet = workbook.getSheetAt(0);
		if (sheet.getPhysicalNumberOfRows() > 1) {
			List<PurchaseOrderItem> poiList = new ArrayList<>();
			//按照行进行循环，读取当前行的列
			for (int k = 1; k < sheet.getPhysicalNumberOfRows(); k++) {
				// 读取单元格
				Row row = sheet.getRow(k);
				PurchaseOrderItem purchaseOrderItem = new PurchaseOrderItem();
				purchaseOrderItem.setPurchaseOrderId(purchaesOrder.getId());
				Cell cell1 = row.getCell(0);//第一列
				if (!(cell1 == null || "".equals(cell1))) {
					cell1.setCellType(Cell.CELL_TYPE_STRING);
					purchaseOrderItem.setBrand(cell1.getStringCellValue());
				}
				Cell cell2 = row.getCell(1);//第二列
				if (!(cell2 == null || "".equals(cell2))) {
					cell2.setCellType(Cell.CELL_TYPE_STRING);
					purchaseOrderItem.setName(cell2.getStringCellValue());
				}
				Cell cell3 = row.getCell(2);//第三列
				if (!(cell3 == null || "".equals(cell3))) {
					cell3.setCellType(Cell.CELL_TYPE_STRING);
					purchaseOrderItem.setModel(cell3.getStringCellValue());
				}
				Cell cell4 = row.getCell(3);//第四列
				if (!(cell4 == null || "".equals(cell4))) {
					cell4.setCellType(Cell.CELL_TYPE_STRING);
					purchaseOrderItem.setEncapsulation(cell4.getStringCellValue());
				}
				Cell cell5 = row.getCell(4);//第五列
				if (!(cell5 == null || "".equals(cell5))) {
					cell5.setCellType(Cell.CELL_TYPE_STRING);
					purchaseOrderItem.setBak2(cell5.getStringCellValue());
				}
				Cell cell6 = row.getCell(5);//第六列
				if (!(cell6 == null || "".equals(cell6))) {
					cell6.setCellType(Cell.CELL_TYPE_STRING);
					purchaseOrderItem.setBak5(cell6.getStringCellValue());
				}
				Cell cell7 = row.getCell(6);//第七列
				if (!(cell7 == null || "".equals(cell7))) {
					cell7.setCellType(Cell.CELL_TYPE_STRING);
					purchaseOrderItem.setAmount(cell7.getStringCellValue());
				}
				Cell cell8 = row.getCell(7);//第八列
				if (!(cell8 == null || "".equals(cell8))) {
					cell8.setCellType(Cell.CELL_TYPE_STRING);
					purchaseOrderItem.setBak3(cell8.getStringCellValue());
				}
				Cell cell9 = row.getCell(8);//第九列
				if (!(cell9 == null || "".equals(cell9))) {
					cell9.setCellType(Cell.CELL_TYPE_STRING);
					purchaseOrderItem.setCycle(cell9.getStringCellValue());
				}
				Cell cell10 = row.getCell(9);//第十列
				if (!(cell10 == null || "".equals(cell10))) {
					cell10.setCellType(Cell.CELL_TYPE_STRING);
					purchaseOrderItem.setPriceRequirement(cell10.getStringCellValue());
				}
				purchaseOrderItem.setExcelId(productExcel.getId());
				purchaseOrderItem.setPurchaseOrderId(purchaesOrder.getId());
				purchaseOrderItem.setMemId(memId);
				purchaseOrderItem.setRecommend("0");
				purchaseOrderItem.setStatus("1");
				purchaseOrderItem.setBak4("2");
				purchaseOrderItem.setTraderId(traderId);
				// 如果一行数据都为空，不插入
				if ((cell1.getStringCellValue().trim() == null || "".equals(cell1.getStringCellValue().trim()))
						&& (cell2.getStringCellValue().trim() == null || "".equals(cell2.getStringCellValue().trim()))
						&& (cell3.getStringCellValue().trim() == null || "".equals(cell3.getStringCellValue().trim()))
						&& (cell4.getStringCellValue().trim() == null || "".equals(cell4.getStringCellValue().trim()))
						&& (cell5.getStringCellValue().trim() == null || "".equals(cell5.getStringCellValue().trim()))
						&& (cell6.getStringCellValue().trim() == null || "".equals(cell6.getStringCellValue().trim()))
						&& (cell7.getStringCellValue().trim() == null || "".equals(cell7.getStringCellValue().trim()))
						&& (cell8.getStringCellValue().trim() == null || "".equals(cell8.getStringCellValue().trim()))
						&& (cell9.getStringCellValue().trim() == null || "".equals(cell9.getStringCellValue().trim()))) {

				} else {
					poiList.add(purchaseOrderItem);
				}

			}
			// 分批插入，每批100条
			List<PurchaseOrderItem> poiList_part = new ArrayList<PurchaseOrderItem>();
			for (int i = 0; i < poiList.size(); i++) {
				poiList_part.add(poiList.get(i));
				if (i > 0 && i % 100 == 0) {
					int a = purchaseOrderItemService.insertSelectivePurchase(poi);
					System.out.println(a);
					poiList_part = new ArrayList<PurchaseOrderItem>();
				}
			}
			// 最后一批插入
			if (poiList_part.size() > 0) {
				purchaseOrderItemService.insertSelectivePurchase(poiList);
			}
		}

	} catch (IOException e) {
		logger.error("excel-poi解析失败", e);
	} finally {
		if (inputStream != null) {
			try {
				inputStream.close();
			} catch (IOException e) {
				logger.error("excel-关闭流失败", e);
			}
		}
	}
	addMessage(redirectAttributes, "采购单上传成功");
	return "redirect:" + adminPath() + "/buy/purchaesOrder.do";
}