Java EasyExcel 导入代码

Java EasyExcel 导入代码

导入方法

/*** 仓库库位导入** @param req* @param res* @param files* @throws Exception*/@RequestMapping(value = {"/import/line_store_locs"}, method = {RequestMethod.POST})@ResponseBodypublic void importStoreLoc(HttpServletRequest req, HttpServletResponse res, @RequestParam("file") MultipartFile[] files) throws Exception {//获取文件流List<LineStoreLocMode> tempList = new ArrayList<>();if (files != null && files.length != 0) {//获取文件流EasyExcel.read(files[0].getInputStream(), LineStoreLocMode.class, new PageReadListener<LineStoreLocMode>(dataList -> {for (LineStoreLocMode demoData : dataList) {tempList.add(demoData);}})).sheet().doRead();}if (ValueUtil.isEmpty(tempList)) {return;}// 仓库Query query = new Query();List<LineStore> lineStoreList = this.selectList("line_stores", query, LineStore.class);// 型号尺寸List<ModelSize> modelSizeList = this.selectList("model_sizes", query, ModelSize.class);List<LineStoreLoc> locList = new ArrayList<>();for(LineStoreLocMode vo : tempList){List<String> modelList = new ArrayList<>();if(vo.getModelSizeStr().contains("、")){String[] modelSizeArr = vo.getModelSizeStr().split("、");modelList = Arrays.asList(modelSizeArr);}else {modelList.add(vo.getModelSizeStr());}for(String modelName : modelList){LineStoreLoc lineStoreLoc = new LineStoreLoc();LineStore lineStore  = lineStoreList.stream().filter(e -> vo.getStoreName().equals(e.getName())).collect(Collectors.toList()).get(0);lineStoreLoc.setLineStoreId(lineStore.getId());   // 仓库lineStoreLoc.setSegment2Id(vo.getSegment2Id());   // 库区编码lineStoreLoc.setSegment3Id(vo.getSegment3Id());   // 库区名称lineStoreLoc.setName(vo.getName());     // 库位编码lineStoreLoc.setDescription(vo.getDescription());       // 库位名称lineStoreLoc.setProdSubcatName(vo.getProdSubcatName());  // 产品小分类lineStoreLoc.setWarehouseCapcity(vo.getWarehouseCapcity());     // 仓库容量lineStoreLoc.setCapcityUnit(vo.getCapcityUnit());    // 容量单位lineStoreLoc.setSegment1Id(vo.getSegment1Id());     // 备注ModelSize modelSize = modelSizeList.stream().filter(e -> modelName.equals(e.getName())).collect(Collectors.toList()).get(0);lineStoreLoc.setModelSizeId(modelSize.getId());   // 型号尺寸locList.add(lineStoreLoc);}}// 数据插入insertData(locList);}

50条插入一次的方法

private void insertData(List<LineStoreLoc> matCrList){// 每次只插入100条数据List<LineStoreLoc> insertInfoList = new ArrayList<>();int i = 0;for(LineStoreLoc vo : matCrList){insertInfoList.add(vo);i++;if(i >= 50){this.insertBatch(insertInfoList);i = 0;insertInfoList.clear();}}// 剩余条数全部保存this.insertBatch(insertInfoList);}

接收数据的实体

package xyz.xmes.cust.monitorsystem.model;import com.alibaba.excel.annotation.ExcelProperty;/*** @Description: 仓库库位管理导入* @Date: Create in 18:44 2023/12/27*/
public class LineStoreLocMode {@ExcelProperty("仓库编码")private String storeName;       // 关联到 LINE_STORES 中查询 Id@ExcelProperty("库区编码")private String segment2Id;@ExcelProperty("库区名称")private String segment3Id;@ExcelProperty("库位编码")private String name;@ExcelProperty("库位名称")private String description;@ExcelProperty("产品小分类")private String prodSubcatName;     // 不用拆分@ExcelProperty("型号尺寸")private String modelSizeStr;    // 需要拆分@ExcelProperty("仓库容量")private String warehouseCapcity;@ExcelProperty("容量单位")private String capcityUnit;@ExcelProperty("备注")private String segment1Id;public String getStoreName() {return storeName;}public void setStoreName(String storeName) {this.storeName = storeName;}public String getSegment2Id() {return segment2Id;}public void setSegment2Id(String segment2Id) {this.segment2Id = segment2Id;}public String getSegment3Id() {return segment3Id;}public void setSegment3Id(String segment3Id) {this.segment3Id = segment3Id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public String getDescription() {return description;}public void setDescription(String description) {this.description = description;}public String getProdSubcatName() {return prodSubcatName;}public void setProdSubcatName(String prodSubcatName) {this.prodSubcatName = prodSubcatName;}public String getModelSizeStr() {return modelSizeStr;}public void setModelSizeStr(String modelSizeStr) {this.modelSizeStr = modelSizeStr;}public String getWarehouseCapcity() {return warehouseCapcity;}public void setWarehouseCapcity(String warehouseCapcity) {this.warehouseCapcity = warehouseCapcity;}public String getCapcityUnit() {return capcityUnit;}public void setCapcityUnit(String capcityUnit) {this.capcityUnit = capcityUnit;}public String getSegment1Id() {return segment1Id;}public void setSegment1Id(String segment1Id) {this.segment1Id = segment1Id;}
}

Postman 上传文件