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);
  10. if (sheet != null) {
  11. int rowstart = sheet.getFirstRowNum();
  12. int rowEnd = sheet.getLastRowNum();
  13. List<ProductInfo> productInfoList = new ArrayList<>();
  14. List<ProductSku> list = new ArrayList<ProductSku>();
  15. int rowIndex = rowstart;
  16. List<String> columns = new ArrayList<>();
  17. List<String> errList = new ArrayList<>();
  18. int count = rowEnd - rowIndex + 1;
  19. int currentProductIndex = 0;
  20. for (; rowIndex <= rowEnd; rowIndex++) {
  21. int columnIndex = 0;
  22. Row row = sheet.getRow(rowIndex);
  23. if (null == row) {
  24. continue;
  25. } else if (rowIndex == 0) {
  26. String column;
  27. Cell cell;
  28. while ((cell = row.getCell(columnIndex++)) != null
  29. && StringUtils.isNotBlank((column = getStringCellValue(cell)))) {
  30. columns.add(column);
  31. }
  32. continue;
  33. }
  34. boolean success = true;
  35. StringBuilder sb = new StringBuilder("第" + rowIndex + "行<br/>");
  36. Cell cell = row.getCell(1);
  37. if (StringUtils.isBlank(getStringCellValue(cell))) {
  38. // 商品大类
  39. ProductInfo productInfo = new ProductInfo();
  40. columnIndex = 2;
  41. String isMH = getStringCellValue(row.getCell(columnIndex));
  42. if (StringUtils.isNotBlank(isMH) && "Y".equals(isMH)) {
  43. productInfo.setProductClass(IsEnabledConstants.disabled);
  44. } else {
  45. productInfo.setProductClass(IsEnabledConstants.enabled);
  46. }
  47. columnIndex++;
  48. String productDesc = getStringCellValue(row.getCell(columnIndex));
  49. if (StringUtils.isNotBlank(productDesc)) {
  50. productInfo.setAbbreviation(productDesc);
  51. productInfo.setProductDesc(productDesc);
  52. } else {
  53. success = false;
  54. sb.append(columns.get(columnIndex) + "为空;");
  55. }
  56. columnIndex++;
  57. String productName = getStringCellValue(row.getCell(columnIndex));
  58. if (StringUtils.isNotBlank(productDesc)) {
  59. productInfo.setProductName(productName);
  60. }
  61. columnIndex += 8;
  62. String isActive = getStringCellValue(row.getCell(columnIndex));
  63. if (StringUtils.isNotBlank(isActive) && "Y".equals(isActive)) {
  64. productInfo.setIsEnabled(IsEnabledConstants.enabled);
  65. } else {
  66. productInfo.setIsEnabled(IsEnabledConstants.disabled);
  67. }
  68. currentProductIndex = productInfoList.size();
  69. productInfoList.add(productInfo);
  70. if (!success) {
  71. errList.add(sb.toString());
  72. }
  73. } else {
  74. // SKU
  75. ProductSku productSku = new ProductSku();
  76. productSku.setProductId(currentProductIndex);
  77. columnIndex++;
  78. String skuId = getStringCellValue(row.getCell(columnIndex));
  79. if (StringUtils.isNotBlank(skuId)) {
  80. productSku.setSkuId(getStringCellValue(row.getCell(1)));
  81. } else {
  82. success = false;
  83. sb.append(columns.get(columnIndex) + "为空;");
  84. }
  85. columnIndex += 2;
  86. productSku.setSkuDesc(getStringCellValue(row.getCell(columnIndex)));
  87. columnIndex++;
  88. String skuName = getStringCellValue(row.getCell(columnIndex));
  89. if (StringUtils.isNotBlank(skuName)) {
  90. productSku.setSkuName(skuName);
  91. } else {
  92. success = false;
  93. sb.append(columns.get(columnIndex) + "为空;");
  94. }
  95. columnIndex++;
  96. productSku.setPackKind(getStringCellValue(row.getCell(columnIndex)));
  97. columnIndex++;
  98. productSku.setOrigin(getStringCellValue(row.getCell(columnIndex)));
  99. columnIndex++;
  100. int packNum = getIntCellValue(row.getCell(columnIndex));
  101. if (packNum > 0) {
  102. productSku.setPackNum(packNum);
  103. } else {
  104. success = false;
  105. sb.append(columns.get(columnIndex) + "不合法;");
  106. }
  107. columnIndex++;
  108. String size = getStringCellValue(row.getCell(columnIndex));
  109. if (StringUtils.isNotBlank(skuName)) {
  110. List<Integer> sizes = deal(size);
  111. if (sizes.size() == 1 || sizes.size() == 2) {
  112. if (sizes.size() > 0) {
  113. productSku.setSize(sizes.get(0));
  114. }
  115. if (sizes.size() > 1) {
  116. productSku.setSize(sizes.get(1));
  117. }
  118. } else {
  119. success = false;
  120. sb.append(columns.get(columnIndex) + size + "不合法;");
  121. }
  122. } else {
  123. success = false;
  124. sb.append(columns.get(columnIndex) + "为空;");
  125. }
  126. columnIndex++;
  127. int standardCardinal = getIntCellValue(row.getCell(columnIndex));
  128. if (standardCardinal > 0) {
  129. productSku.setStandardCardinal(standardCardinal);
  130. } else {
  131. success = false;
  132. sb.append(columns.get(columnIndex) + "不合法;");
  133. }
  134. columnIndex++;
  135. String limitedEdition = getStringCellValue(row.getCell(columnIndex));
  136. if (StringUtils.isNotBlank(limitedEdition) && "Y".equals(limitedEdition)) {
  137. productSku.setLimitedEdition(IsEnabledConstants.enabled);
  138. } else {
  139. productSku.setLimitedEdition(IsEnabledConstants.disabled);
  140. }
  141. columnIndex++;
  142. String isRfid = getStringCellValue(row.getCell(columnIndex));
  143. if (StringUtils.isNotBlank(isRfid) && "Y".equals(isRfid)) {
  144. productSku.setIsRfid(IsEnabledConstants.enabled);
  145. } else {
  146. productSku.setIsRfid(IsEnabledConstants.disabled);
  147. }
  148. columnIndex++;
  149. String isQr = getStringCellValue(row.getCell(columnIndex));
  150. if (StringUtils.isNotBlank(isQr) && "Y".equals(isQr)) {
  151. productSku.setIsQr(IsEnabledConstants.enabled);
  152. } else {
  153. productSku.setIsQr(IsEnabledConstants.disabled);
  154. }
  155. columnIndex++;
  156. String isActive = getStringCellValue(row.getCell(columnIndex));
  157. if (StringUtils.isNotBlank(isActive) && "Y".equals(isActive)) {
  158. productSku.setIsEnabled(IsEnabledConstants.enabled);
  159. } else {
  160. productSku.setIsEnabled(IsEnabledConstants.disabled);
  161. }
  162. columnIndex++;
  163. productSku.setLine(rowIndex + 1);
  164. list.add(productSku);
  165. }
  166. }
  167. if (errList.size() > 0) {
  168. json.put("code", 1);
  169. StringBuilder sb = new StringBuilder("本次共解析出" + count + "条数据,共" + errList.size() + "行数据格式错误!<br/>");
  170. sb.append(StringUtils.join(errList, "<br/>"));
  171. json.put("msg", sb.toString());
  172. } else {
  173. json.put("code", 0);
  174. this.productService.saveProductSku(productInfoList, list);
  175. StringBuilder sb = new StringBuilder();
  176. sb.append("本次共解析出" + count + "条数据<br/>" + ",全部导入成功,相同数据已合并。");
  177. json.put("msg", sb.toString());
  178. }
  179. } else {
  180. json.put("code", 1);
  181. json.put("msg", "本次导入文件没有" + MasterConstants.PRODUCT + "Sheet!");
  182. }
  183. return json;
  184. }
  185. private static String getStringCellValue(Cell cell) {
  186. String value = "";
  187. if (cell != null) {
  188. switch (cell.getCellType()) {
  189. case HSSFCell.CELL_TYPE_NUMERIC: // 数字
  190. double d = cell.getNumericCellValue();
  191. value = String.valueOf((int) d);
  192. break;
  193. case HSSFCell.CELL_TYPE_STRING: // 字符串
  194. value = cell.getStringCellValue();
  195. break;
  196. }
  197. }
  198. return value.trim();
  199. }

发表评论

表情:
评论列表 (有 0 条评论,293人围观)

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

相关阅读

    相关 Java实现Excel导入和导出

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

    相关 Java实现Excel导入

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

    相关 java实现Excel导入

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