Excel导入一般处理方式

  1. 先将Excel中数据导入到POJO List中
  2. 在上一步中顺便校验相应的输入列是否合法,不合法直接返回具体信息;
  3. 将POJO List与数据库做对比,校验数据合法性,不合法直接返回具体信息
  4. 将POJO List与数据做对比,将某些列绑定到具体数据库中的数据,即有id信息
  5. 逻辑处理部分,将逻辑处理后的数据导入到数据库中,返回成功条数;

EbuyProductController.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
/**
* 批量导入体验店商品--保存
*
* @param request
* @return
* @throws IOException
*/
@RequestMapping("/saveBatchImport4JFQStore.html")
public ModelAndView saveBatchImport4JFQStore(HttpServletRequest request) throws IOException {
String result = "";
try {
result = ebuyProductService.saveBatchImport4JFQStore(request);
}catch (Exception e) {
result = "导入异常,请检查Excel中的数据";
logger.error(e.getMessage(), e);
}

request.setAttribute(JSPConstants.OprtResult, result);
request.setAttribute(JSPConstants.ToURL, "../ebuyProduct/list.html");

return new ModelAndView(JSPConstants.OprtSuccessPage);
}

EbuyProdctService.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
@Override
public String saveBatchImport4JFQStore(HttpServletRequest request) throws IOException {
String result = "导入成功";
if (request instanceof MultipartHttpServletRequest) {
MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
MultipartFile uploadExcelfile = multipartRequest.getFile("excelFile");
HSSFWorkbook wb = new HSSFWorkbook(uploadExcelfile.getInputStream());
HSSFSheet sheet = wb.getSheetAt(0);

List<EbuyProduct> ebuyProductUpdList = new ArrayList<EbuyProduct>();
List<EbuyProduct> ebuyProductAddNewList = new ArrayList<EbuyProduct>();
List<EbuyProductImport> epImportList = new ArrayList<EbuyProductImport>();

//excel校验
BigInteger storeId = new BigInteger(sysParamManager.getSysParaValue(SysParamKey.Experience_Store_Id));
String verifyResult = verifyPpInfoData(sheet, storeId, ebuyProductAddNewList, ebuyProductUpdList, epImportList);
if(!verifyResult.equals("通过校验")){
result = "校验失败:"+ verifyResult;
} else {
//导入数据
int i = 0;
if(!DataUtil.isEmpty(ebuyProductAddNewList)) {
CnfantasiaCommbusiUtil.newStandardList(ebuyProductAddNewList, SEQConstants.t_ebuy_product);
i += ebuyProductDao.insertEbuyProductBatch(ebuyProductAddNewList);
}

if(!DataUtil.isEmpty(ebuyProductUpdList)) {
ebuyProductDao.updateEbuyProductBatch(ebuyProductUpdList);
i += ebuyProductUpdList.size();
}

ebuyProductDao.upToShelfProductAfterImported(epImportList);

result = "成功导入"+ i + "条";
}
}
return result;
}

private String verifyPpInfoData(HSSFSheet sheet, BigInteger storeId, List<EbuyProduct> ebuyProductAddNewList, List<EbuyProduct> ebuyProductUpdList, List<EbuyProductImport> epImportList){
String resultInfo = "通过校验";

for(int i = 1; i < sheet.getLastRowNum() + 1; i++){
if(sheet.getRow(i) == null) //空行,跳过
continue;

EbuyProductImport epi = new EbuyProductImport();
epi.setPrdtCode(HSSFCellUtil.getStringValue(sheet.getRow(i).getCell(0)));
epi.setPrdtName(HSSFCellUtil.getStringValue(sheet.getRow(i).getCell(1)));

//没有条码,没有商品名称,认为是无效行,跳过
if(StringUtils.isEmpty(epi.getPrdtCode()) || StringUtils.isEmpty(epi.getPrdtName()))
continue;

epi.setShelfState(HSSFCellUtil.getStringValue(sheet.getRow(i).getCell(2)));
epi.setUpShelfState("上架".equals(epi.getShelfState()) ? 0 : 1);
epi.setShelfName(HSSFCellUtil.getStringValue(sheet.getRow(i).getCell(3)));
epi.setUnitName(HSSFCellUtil.getStringValue(sheet.getRow(i).getCell(4)));
epi.setMarketPrice(HSSFCellUtil.getNumbericValue(sheet.getRow(i).getCell(5)));
epi.setSellPrice(HSSFCellUtil.getNumbericValue(sheet.getRow(i).getCell(6)));
if (epi.getMarketPrice() <= epi.getSellPrice())//如果市场价比销售价还要低,自动调整为销售价的1.05倍
epi.setMarketPrice(epi.getSellPrice() * 1.05);
epi.setLeftCount(Integer.parseInt(HSSFCellUtil.getStringValue(sheet.getRow(i).getCell(7))));


if(epi.getSellPrice()<=0){
resultInfo = "第" + (i+1) +"行的销售价必须大于0";
return resultInfo;
}

epImportList.add(epi);
}

List<EbuyProduct> epExistsList = ebuyProductDao.getProductList_forImport(epImportList, storeId);
for (EbuyProductImport epi : epImportList) {
for (EbuyProduct ebuyProduct : epExistsList) {
if (epi.getPrdtCode().equals(ebuyProduct.getCode())) {//如果Code一样,则认为是同一个商品,需要update
EbuyProduct epUpdate = new EbuyProduct();
epUpdate.setId(ebuyProduct.getId());

epUpdate.setName(epi.getPrdtName());
epUpdate.setPriceUnit(epi.getUnitName());
epUpdate.setLeftCount(new BigInteger(epi.getLeftCount() + ""));
epUpdate.setPriceDiscount(PriceUtil.multiply100(epi.getSellPrice()));
epUpdate.setPurchasePrice(PriceUtil.multiply100(epi.getSellPrice()));
epUpdate.setPrice(PriceUtil.multiply100(epi.getMarketPrice()));
ebuyProductUpdList.add(epUpdate);

epi.setEbuyProduct(ebuyProduct);
epi.setFirstImport(0);
break;
}
}

if(epi.getEbuyProduct() == null){//需新增
EbuyProduct ebuyProductAddNew = new EbuyProduct();
ebuyProductAddNew.settSupplyMerchantFId(storeId);
ebuyProductAddNew.setCreateTime(DateUtils.getCurrentDate());
ebuyProductAddNew.setSelNum(BigInteger.ZERO);
ebuyProductAddNew.setPointType(1);
ebuyProductAddNew.setSpecialProductType(1);
ebuyProductAddNew.setIsHotSale(0);

ebuyProductAddNew.setName(epi.getPrdtName());
ebuyProductAddNew.setPriceUnit(epi.getUnitName());
ebuyProductAddNew.setLeftCount(new BigInteger(epi.getLeftCount() + ""));
ebuyProductAddNew.setPriceDiscount(PriceUtil.multiply100(epi.getSellPrice()));
ebuyProductAddNew.setPurchasePrice(PriceUtil.multiply100(epi.getSellPrice()));
ebuyProductAddNew.setPrice(PriceUtil.multiply100(epi.getMarketPrice()));
ebuyProductAddNew.setStatus(epi.getUpShelfState());
if(epi.getUpShelfState() == 0)
ebuyProductAddNew.setUpShelfTime(DateUtils.getCurrentDate());

ebuyProductAddNew.setCode(epi.getPrdtCode());
ebuyProductAddNew.setStatusAudit(EbuyProductConstant.ProductAuditStatus.AUDIT_STATUS_PASSED);
ebuyProductAddNew.setWlappType(1L);

ebuyProductAddNewList.add(ebuyProductAddNew);

epi.setFirstImport(1);
epi.setEbuyProduct(ebuyProductAddNew);
}
}

//建立货架类型id映射
EbuyProductType eptQry = new EbuyProductType();
eptQry.setSys0DelState(0);
eptQry.setWlappType(1L);
List<EbuyProductType> ebuyProductTypeList = ebuyProductTypeBaseDao.selectEbuyProductTypeByCondition(MapConverter.toMap(eptQry), false);
for (EbuyProductImport epi : epImportList) {
for (EbuyProductType ebuyProductType : ebuyProductTypeList) {
if(epi.getShelfName().equals(ebuyProductType.getTypeName())){
epi.setEbuyProductTypeId(ebuyProductType.getId());
epi.getEbuyProduct().settEbuyProductTypeFId(ebuyProductType.getId());
break;
}
}
}

return resultInfo;
}