java实现Excel导入

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

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

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

  1. <!-- excel -->
  2.         <dependency>
  3.             <groupId>org.apache.poi</groupId>
  4.             <artifactId>poi</artifactId>
  5.             <version>3.9</version>
  6.         </dependency>
  7.         <dependency>
  8.             <groupId>org.apache.poi</groupId>
  9.             <artifactId>poi-ooxml</artifactId>
  10.             <version>3.9</version>
  11.         </dependency>
  12.         <dependency>
  13.             <groupId>org.apache.poi</groupId>
  14.             <artifactId>poi-scratchpad</artifactId>
  15.             <version>3.9</version>
  16.         </dependency>
  17.         <dependency>
  18.             <groupId>xerces</groupId>
  19.             <artifactId>xercesImpl</artifactId>
  20.             <version>2.9.1</version>
  21.         </dependency>

二:Controller层:

  1. @RequestMapping("import")
  2.     @SecurityMapping(rcode = "productImport_web")
  3.     @InterfaceLog(name = "货品导入", operationName = "货品导入", operationType = OperationType.IMPORT)
  4.     public @ResponseBody JSONObject productImport(HttpServletRequest request, @RequestParam("file") MultipartFile file)
  5.             throws IOException, InvalidFormatException {
  6.         JSONObject json = new JSONObject();
  7.         InputStream stream = file.getInputStream();
  8.         Workbook workBook = WorkbookFactory.create(stream);
  9.         Sheet sheet = workBook.getSheet(MasterConstants.PRODUCT);
  11.         if (sheet != null) {
  12.             int rowstart = sheet.getFirstRowNum();
  13.             int rowEnd = sheet.getLastRowNum();
  14.             List<ProductInfo> productInfoList = new ArrayList<>();
  15.             List<ProductSku> list = new ArrayList<ProductSku>();
  16.             int rowIndex = rowstart;
  18.             List<String> columns = new ArrayList<>();
  19.             List<String> errList = new ArrayList<>();
  20.             int count = rowEnd - rowIndex + 1;
  21.             int currentProductIndex = 0;
  22.             for (; rowIndex <= rowEnd; rowIndex++) {
  23.                 int columnIndex = 0;
  24.                 Row row = sheet.getRow(rowIndex);
  25.                 if (null == row) {
  26.                     continue;
  27.                 } else if (rowIndex == 0) {
  28.                     String column;
  29.                     Cell cell;
  30.                     while ((cell = row.getCell(columnIndex++)) != null
  31.                             && StringUtils.isNotBlank((column = getStringCellValue(cell)))) {
  32.                         columns.add(column);
  33.                     }
  34.                     continue;
  35.                 }
  37.                 boolean success = true;
  38.                 StringBuilder sb = new StringBuilder("第" + rowIndex + "行<br/>");
  40.                 Cell cell = row.getCell(1);
  41.                 if (StringUtils.isBlank(getStringCellValue(cell))) {
  42.                     // 商品大类
  43.                     ProductInfo productInfo = new ProductInfo();
  45.                     columnIndex = 2;
  46.                     String isMH = getStringCellValue(row.getCell(columnIndex));
  47.                     if (StringUtils.isNotBlank(isMH) && "Y".equals(isMH)) {
  48.                         productInfo.setProductClass(IsEnabledConstants.disabled);
  49.                     } else {
  50.                         productInfo.setProductClass(IsEnabledConstants.enabled);
  51.                     }
  52.                     columnIndex++;
  54.                     String productDesc = getStringCellValue(row.getCell(columnIndex));
  55.                     if (StringUtils.isNotBlank(productDesc)) {
  56.                         productInfo.setAbbreviation(productDesc);
  57.                         productInfo.setProductDesc(productDesc);
  58.                     } else {
  59.                         success = false;
  60.                         sb.append(columns.get(columnIndex) + "为空;");
  61.                     }
  62.                     columnIndex++;
  64.                     String productName = getStringCellValue(row.getCell(columnIndex));
  65.                     if (StringUtils.isNotBlank(productDesc)) {
  66.                         productInfo.setProductName(productName);
  67.                     }
  69.                     columnIndex += 8;
  71.                     String isActive = getStringCellValue(row.getCell(columnIndex));
  72.                     if (StringUtils.isNotBlank(isActive) && "Y".equals(isActive)) {
  73.                         productInfo.setIsEnabled(IsEnabledConstants.enabled);
  74.                     } else {
  75.                         productInfo.setIsEnabled(IsEnabledConstants.disabled);
  76.                     }
  78.                     currentProductIndex = productInfoList.size();
  79.                     productInfoList.add(productInfo);
  80.                     if (!success) {
  81.                         errList.add(sb.toString());
  82.                     }
  83.                 } else {
  84.                     // SKU
  85.                     ProductSku productSku = new ProductSku();
  87.                     productSku.setProductId(currentProductIndex);
  88.                     columnIndex++;
  90.                     String skuId = getStringCellValue(row.getCell(columnIndex));
  91.                     if (StringUtils.isNotBlank(skuId)) {
  92.                         productSku.setSkuId(getStringCellValue(row.getCell(1)));
  93.                     } else {
  94.                         success = false;
  95.                         sb.append(columns.get(columnIndex) + "为空;");
  96.                     }
  97.                     columnIndex += 2;
  99.                     productSku.setSkuDesc(getStringCellValue(row.getCell(columnIndex)));
  100.                     columnIndex++;
  102.                     String skuName = getStringCellValue(row.getCell(columnIndex));
  103.                     if (StringUtils.isNotBlank(skuName)) {
  104.                         productSku.setSkuName(skuName);
  105.                     } else {
  106.                         success = false;
  107.                         sb.append(columns.get(columnIndex) + "为空;");
  108.                     }
  109.                     columnIndex++;
  111.                     productSku.setPackKind(getStringCellValue(row.getCell(columnIndex)));
  112.                     columnIndex++;
  114.                     productSku.setOrigin(getStringCellValue(row.getCell(columnIndex)));
  115.                     columnIndex++;
  117.                     int packNum = getIntCellValue(row.getCell(columnIndex));
  118.                     if (packNum > 0) {
  119.                         productSku.setPackNum(packNum);
  120.                     } else {
  121.                         success = false;
  122.                         sb.append(columns.get(columnIndex) + "不合法;");
  123.                     }
  124.                     columnIndex++;
  126.                     String size = getStringCellValue(row.getCell(columnIndex));
  127.                     if (StringUtils.isNotBlank(skuName)) {
  128.                         List<Integer> sizes = deal(size);
  129.                         if (sizes.size() == 1 || sizes.size() == 2) {
  130.                             if (sizes.size() > 0) {
  131.                                 productSku.setSize(sizes.get(0));
  132.                             }
  133.                             if (sizes.size() > 1) {
  134.                                 productSku.setSize(sizes.get(1));
  135.                             }
  136.                         } else {
  137.                             success = false;
  138.                             sb.append(columns.get(columnIndex) + size + "不合法;");
  139.                         }
  140.                     } else {
  141.                         success = false;
  142.                         sb.append(columns.get(columnIndex) + "为空;");
  143.                     }
  144.                     columnIndex++;
  146.                     int standardCardinal = getIntCellValue(row.getCell(columnIndex));
  147.                     if (standardCardinal > 0) {
  148.                         productSku.setStandardCardinal(standardCardinal);
  149.                     } else {
  150.                         success = false;
  151.                         sb.append(columns.get(columnIndex) + "不合法;");
  152.                     }
  153.                     columnIndex++;
  155.                     String limitedEdition = getStringCellValue(row.getCell(columnIndex));
  156.                     if (StringUtils.isNotBlank(limitedEdition) && "Y".equals(limitedEdition)) {
  157.                         productSku.setLimitedEdition(IsEnabledConstants.enabled);
  158.                     } else {
  159.                         productSku.setLimitedEdition(IsEnabledConstants.disabled);
  160.                     }
  161.                     columnIndex++;
  163.                     String isRfid = getStringCellValue(row.getCell(columnIndex));
  164.                     if (StringUtils.isNotBlank(isRfid) && "Y".equals(isRfid)) {
  165.                         productSku.setIsRfid(IsEnabledConstants.enabled);
  166.                     } else {
  167.                         productSku.setIsRfid(IsEnabledConstants.disabled);
  168.                     }
  169.                     columnIndex++;
  171.                     String isQr = getStringCellValue(row.getCell(columnIndex));
  172.                     if (StringUtils.isNotBlank(isQr) && "Y".equals(isQr)) {
  173.                         productSku.setIsQr(IsEnabledConstants.enabled);
  174.                     } else {
  175.                         productSku.setIsQr(IsEnabledConstants.disabled);
  176.                     }
  177.                     columnIndex++;
  179.                     String isActive = getStringCellValue(row.getCell(columnIndex));
  180.                     if (StringUtils.isNotBlank(isActive) && "Y".equals(isActive)) {
  181.                         productSku.setIsEnabled(IsEnabledConstants.enabled);
  182.                     } else {
  183.                         productSku.setIsEnabled(IsEnabledConstants.disabled);
  184.                     }
  185.                     columnIndex++;
  187.                     productSku.setLine(rowIndex + 1);
  189.                     list.add(productSku);
  190.                 }
  191.             }
  192.             if (errList.size() > 0) {
  193.                 json.put("code", 1);
  195.                 StringBuilder sb = new StringBuilder("本次共解析出" + count + "条数据，共" + errList.size() + "行数据格式错误！<br/>");
  196.                 sb.append(StringUtils.join(errList, "<br/>"));
  197.                 json.put("msg", sb.toString());
  198.             } else {
  199.                 json.put("code", 0);
  201.                 this.productService.saveProductSku(productInfoList, list);
  203.                 StringBuilder sb = new StringBuilder();
  204.                 sb.append("本次共解析出" + count + "条数据<br/>" + "，全部导入成功，相同数据已合并。");
  205.                 json.put("msg", sb.toString());
  206.             }
  207.         } else {
  208.             json.put("code", 1);
  210.             json.put("msg", "本次导入文件没有" + MasterConstants.PRODUCT + "Sheet！");
  211.         }
  213.         return json;
  214.     }
  216. private static String getStringCellValue(Cell cell) {
  217.         String value = "";
  218.         if (cell != null) {
  219.             switch (cell.getCellType()) {
  220.             case HSSFCell.CELL_TYPE_NUMERIC: // 数字
  221.                 double d = cell.getNumericCellValue();
  222.                 value = String.valueOf((int) d);
  223.                 break;
  224.             case HSSFCell.CELL_TYPE_STRING: // 字符串
  225.                 value = cell.getStringCellValue();
  226.                 break;
  227.             }
  228.         }
  229.         return value.trim();
  230.     }

发表评论

表情：
评论列表 （有 0 条评论，293人围观）

还没有评论，来说两句吧...

相关阅读

    相关 Java实现Excel导入和导出

    一 、环境准备 maven依赖 本次工具类的封装主要依赖于阿里巴巴的JSON包，以及表格处理的POI包，所以我们需要导入这两个库的依赖包，另外，我们还需要文件上传的

    r囧r小猫r囧r小猫/ 0 赞/ 135 阅读

    相关 Java实现Excel导入

    一、流程概念 我们需要把excel通过上传得方式导入数据库，需要以下几个步骤 将excel上传到服务器指定文件内并重命名（upload） 获取到文件公共路径

    短命女短命女/ 0 赞/ 62 阅读

    相关 java实现Excel导入

    需求背景:项目中存在导入Excel文件,将其中的数据保存到数据库,在页面显示保存的列表 一:在pom.xml文件中引入依赖 <!-- excel -->

    红太狼红太狼/ 0 赞/ 294 阅读