java实现Excel导入

需求背景:项目中存在导入Excel文件,将其中的数据保存到数据库,在页面显示保存的列表

一:在pom.xml文件中引入依赖

 <!-- excel -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.9</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.9</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-scratchpad</artifactId>
            <version>3.9</version>
        </dependency>
        <dependency>
            <groupId>xerces</groupId>
            <artifactId>xercesImpl</artifactId>
            <version>2.9.1</version>
        </dependency>

二:Controller层:

@RequestMapping("import")
	@SecurityMapping(rcode = "productImport_web")
	@InterfaceLog(name = "货品导入", operationName = "货品导入", operationType = OperationType.IMPORT)
	public @ResponseBody JSONObject productImport(HttpServletRequest request, @RequestParam("file") MultipartFile file)
			throws IOException, InvalidFormatException {
		JSONObject json = new JSONObject();
		InputStream stream = file.getInputStream();
		Workbook workBook = WorkbookFactory.create(stream);
		Sheet sheet = workBook.getSheet(MasterConstants.PRODUCT);

		if (sheet != null) {
			int rowstart = sheet.getFirstRowNum();
			int rowEnd = sheet.getLastRowNum();
			List<ProductInfo> productInfoList = new ArrayList<>();
			List<ProductSku> list = new ArrayList<ProductSku>();
			int rowIndex = rowstart;

			List<String> columns = new ArrayList<>();
			List<String> errList = new ArrayList<>();
			int count = rowEnd - rowIndex + 1;
			int currentProductIndex = 0;
			for (; rowIndex <= rowEnd; rowIndex++) {
				int columnIndex = 0;
				Row row = sheet.getRow(rowIndex);
				if (null == row) {
					continue;
				} else if (rowIndex == 0) {
					String column;
					Cell cell;
					while ((cell = row.getCell(columnIndex++)) != null
							&& StringUtils.isNotBlank((column = getStringCellValue(cell)))) {
						columns.add(column);
					}
					continue;
				}

				boolean success = true;
				StringBuilder sb = new StringBuilder("第" + rowIndex + "行<br/>");

				Cell cell = row.getCell(1);
				if (StringUtils.isBlank(getStringCellValue(cell))) {
					// 商品大类
					ProductInfo productInfo = new ProductInfo();

					columnIndex = 2;
					String isMH = getStringCellValue(row.getCell(columnIndex));
					if (StringUtils.isNotBlank(isMH) && "Y".equals(isMH)) {
						productInfo.setProductClass(IsEnabledConstants.disabled);
					} else {
						productInfo.setProductClass(IsEnabledConstants.enabled);
					}
					columnIndex++;

					String productDesc = getStringCellValue(row.getCell(columnIndex));
					if (StringUtils.isNotBlank(productDesc)) {
						productInfo.setAbbreviation(productDesc);
						productInfo.setProductDesc(productDesc);
					} else {
						success = false;
						sb.append(columns.get(columnIndex) + "为空;");
					}
					columnIndex++;

					String productName = getStringCellValue(row.getCell(columnIndex));
					if (StringUtils.isNotBlank(productDesc)) {
						productInfo.setProductName(productName);
					}

					columnIndex += 8;

					String isActive = getStringCellValue(row.getCell(columnIndex));
					if (StringUtils.isNotBlank(isActive) && "Y".equals(isActive)) {
						productInfo.setIsEnabled(IsEnabledConstants.enabled);
					} else {
						productInfo.setIsEnabled(IsEnabledConstants.disabled);
					}

					currentProductIndex = productInfoList.size();
					productInfoList.add(productInfo);
					if (!success) {
						errList.add(sb.toString());
					}
				} else {
					// SKU
					ProductSku productSku = new ProductSku();

					productSku.setProductId(currentProductIndex);
					columnIndex++;

					String skuId = getStringCellValue(row.getCell(columnIndex));
					if (StringUtils.isNotBlank(skuId)) {
						productSku.setSkuId(getStringCellValue(row.getCell(1)));
					} else {
						success = false;
						sb.append(columns.get(columnIndex) + "为空;");
					}
					columnIndex += 2;

					productSku.setSkuDesc(getStringCellValue(row.getCell(columnIndex)));
					columnIndex++;

					String skuName = getStringCellValue(row.getCell(columnIndex));
					if (StringUtils.isNotBlank(skuName)) {
						productSku.setSkuName(skuName);
					} else {
						success = false;
						sb.append(columns.get(columnIndex) + "为空;");
					}
					columnIndex++;

					productSku.setPackKind(getStringCellValue(row.getCell(columnIndex)));
					columnIndex++;

					productSku.setOrigin(getStringCellValue(row.getCell(columnIndex)));
					columnIndex++;

					int packNum = getIntCellValue(row.getCell(columnIndex));
					if (packNum > 0) {
						productSku.setPackNum(packNum);
					} else {
						success = false;
						sb.append(columns.get(columnIndex) + "不合法;");
					}
					columnIndex++;

					String size = getStringCellValue(row.getCell(columnIndex));
					if (StringUtils.isNotBlank(skuName)) {
						List<Integer> sizes = deal(size);
						if (sizes.size() == 1 || sizes.size() == 2) {
							if (sizes.size() > 0) {
								productSku.setSize(sizes.get(0));
							}
							if (sizes.size() > 1) {
								productSku.setSize(sizes.get(1));
							}
						} else {
							success = false;
							sb.append(columns.get(columnIndex) + size + "不合法;");
						}
					} else {
						success = false;
						sb.append(columns.get(columnIndex) + "为空;");
					}
					columnIndex++;

					int standardCardinal = getIntCellValue(row.getCell(columnIndex));
					if (standardCardinal > 0) {
						productSku.setStandardCardinal(standardCardinal);
					} else {
						success = false;
						sb.append(columns.get(columnIndex) + "不合法;");
					}
					columnIndex++;

					String limitedEdition = getStringCellValue(row.getCell(columnIndex));
					if (StringUtils.isNotBlank(limitedEdition) && "Y".equals(limitedEdition)) {
						productSku.setLimitedEdition(IsEnabledConstants.enabled);
					} else {
						productSku.setLimitedEdition(IsEnabledConstants.disabled);
					}
					columnIndex++;

					String isRfid = getStringCellValue(row.getCell(columnIndex));
					if (StringUtils.isNotBlank(isRfid) && "Y".equals(isRfid)) {
						productSku.setIsRfid(IsEnabledConstants.enabled);
					} else {
						productSku.setIsRfid(IsEnabledConstants.disabled);
					}
					columnIndex++;

					String isQr = getStringCellValue(row.getCell(columnIndex));
					if (StringUtils.isNotBlank(isQr) && "Y".equals(isQr)) {
						productSku.setIsQr(IsEnabledConstants.enabled);
					} else {
						productSku.setIsQr(IsEnabledConstants.disabled);
					}
					columnIndex++;

					String isActive = getStringCellValue(row.getCell(columnIndex));
					if (StringUtils.isNotBlank(isActive) && "Y".equals(isActive)) {
						productSku.setIsEnabled(IsEnabledConstants.enabled);
					} else {
						productSku.setIsEnabled(IsEnabledConstants.disabled);
					}
					columnIndex++;

					productSku.setLine(rowIndex + 1);

					list.add(productSku);
				}
			}
			if (errList.size() > 0) {
				json.put("code", 1);

				StringBuilder sb = new StringBuilder("本次共解析出" + count + "条数据，共" + errList.size() + "行数据格式错误！<br/>");
				sb.append(StringUtils.join(errList, "<br/>"));
				json.put("msg", sb.toString());
			} else {
				json.put("code", 0);

				this.productService.saveProductSku(productInfoList, list);

				StringBuilder sb = new StringBuilder();
				sb.append("本次共解析出" + count + "条数据<br/>" + "，全部导入成功，相同数据已合并。");
				json.put("msg", sb.toString());
			}
		} else {
			json.put("code", 1);

			json.put("msg", "本次导入文件没有" + MasterConstants.PRODUCT + "Sheet！");
		}

		return json;
	}

private static String getStringCellValue(Cell cell) {
		String value = "";
		if (cell != null) {
			switch (cell.getCellType()) {
			case HSSFCell.CELL_TYPE_NUMERIC: // 数字
				double d = cell.getNumericCellValue();
				value = String.valueOf((int) d);
				break;
			case HSSFCell.CELL_TYPE_STRING: // 字符串
				value = cell.getStringCellValue();
				break;
			}
		}
		return value.trim();
	}