java实现Excel导入

红太狼 2022-05-19 02:46 226阅读 0赞

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

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

<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();
    	}