Excel文件导入数据
1、Controller
@RequestMapping(value = "/importBlackCatLadingBill", method = RequestMethod.POST, produces = "multipart/form-data; charset=utf-8")
@ApiOperation(httpMethod = "POST", value = "导入")
public String importLadingbillData(@RequestParam("file") MultipartFile file, HttpServletRequest request) {
return service.importCatData(request.getHeader("accountId"), file);
}2、Service层， 控制能够导入的类型，将导入时的信息存入对应的表中（可有可无，根据业务来写），最后解析Excel，将数据封装到对象并且保存导数据库中
public String importCatData(String accountId, MultipartFile file) {
ApiOutParamsInfo<Boolean> result = new ApiOutParamsInfo<Boolean>();
LoginAccount loginAccount = (LoginAccount) redisUtil.hget("ACCOUNTINFO", accountId + "_login");
TImportManagement tImportManagement = new TImportManagement();
Map<String, Object> excelMap = new HashMap<>();
// List<Object> list = null;
// 将导入文件的信息记录下来（可有可无，根据具体业务来写）
tImportManagement.setImportPersion(loginAccount.getAccountName());
tImportManagement.setFileName(file.getOriginalFilename());
tImportManagement.setAffixId("");
tImportManagement.setImportTime(new Timestamp(System.currentTimeMillis()));
tImportManagement.setNumberOfReadFiles(1);
tImportManagement.setImportStatus(0);
tImportManagement.setDeleted(0);
baseDao.save(tImportManagement);
String fileName = file.getOriginalFilename();
String fileSuffix = fileName.substring(fileName.lastIndexOf(".") + 1).toLowerCase();
// 判断文件后缀来判断该文件是否能够导入
if (fileSuffix.equals("zip")) {
result.setCode("2");
result.setMsg("导入失败,不能传zip");
result.setResult(false);
return JSON.toJSONString(result);
} else if (fileSuffix.equals("xls") || fileSuffix.equals("xlsx")) {
try {
excelMap = excelParseBlcakCat(POIPostalUtil.readCommonExcel2(file.getInputStream(), 0, 1));
} catch (IOException e) {
e.printStackTrace();
}
} else {
result.setCode("2");
result.setMsg("不支持上传" + fileSuffix + "类型文件");
result.setResult(false);
return JSON.toJSONString(result);
}
// if (list == null || list.size() < 1) {
// result.setCode("0");
// result.setMsg("导入失败更新数据条数未0");
// result.setResult(false);
// return JSON.toJSONString(result);
// }
// 通过foreach 循环写入excel中的数据（每一行都时一个对象）
for (TBlackCatInfo tBlackCatInfo : (List<TBlackCatInfo>) excelMap.get("list")) {
//没有才插入
baseDao.save(tBlackCatInfo);
}
if ((int) excelMap.get("count") == 2) {
result.setCode("0");
result.setMsg("导入成功");
tImportManagement.setImportStatus(1);
baseDao.update(tImportManagement);
result.setResult(true);
return JSON.toJSONString(result);
} else {
result.setCode("2");
result.setMsg(String.valueOf(excelMap.get("msg")));
tImportManagement.setImportStatus(2);
baseDao.update(tImportManagement);
return JSON.toJSONString(result);
}
}4、将Excel解析成为List
public static List<List<String>> readCommonExcel(InputStream inputStream, Integer sheetPosition, Integer columnPosition) {
List<String> columnList = null;
List<List<String>> list = new ArrayList<List<String>>();
Workbook wb = null;
Sheet sheet = null;
try {
wb = WorkbookFactory.create(inputStream);
sheet = wb.getSheetAt(sheetPosition); // 读取sheet 0
int firstRowIndex = columnPosition;
int lastRowIndex = sheet.getLastRowNum();
boolean flag = false;
for (int rIndex = firstRowIndex; rIndex <= lastRowIndex; rIndex++) { // 遍历行
columnList = new ArrayList<String>();
Row row = sheet.getRow(rIndex);
if (row == null) {
return list;
}
for (int i = 0; i < row.getLastCellNum(); i++) { // 遍历本次行的单元格
Cell cell = row.getCell(i, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell.setCellType(CellType.STRING);
if (i == 0) {
if (cell.toString().equals("")) {
flag = true;
break;
}
}
columnList.add(new DataFormatter().formatCellValue(row.getCell(i)));
}
if (flag) {
break;
}
list.add(columnList);
}
} catch (Exception e) {
e.printStackTrace();
}
return list;
}5、将Excel解析后的数据遍历后封装为为对象，然后存入数据库
public Map<String, Object> excelParseBlcakCat(List<List<String>> lists) {
Date date = new Date();
Map<String, Object> map = new HashMap<>();
List<TBlackCatInfo> tBlackCatInfos = new ArrayList<>();
int successCount = 0;
StringBuilder errMsg = new StringBuilder();
Map<String, String> queryBillNoCheck = new HashMap<>();
Map<String, String> custOrderNoCheck = new HashMap<>();
Map<String, String> tradeNoCheck = new HashMap<>();
// 通过foreach遍历Excel中的数据，并且根据业务做对应的处理
for (List<String> list : lists) {
String str = JSON.toJSONString(lists);
System.out.println(str);
if (queryBillNoCheck.containsKey(list.get(1))) {
errMsg.append("客户单号" + list.get(1) + "客户单号\n");
continue;
} else {
queryBillNoCheck.put(list.get(1), list.get(1));
String hql = "from TBlackCatInfo where waybillNum = :waybillNum";
Map<String, Object> param = new HashMap<>();
param.put("waybillNum", list.get(1));
TBlackCatInfo tBlackCatInfo1 = (TBlackCatInfo) baseDao.getByHQL(hql, param);
System.out.println("aa" + list.get(1) + "aa");
System.out.println("aa" + tBlackCatInfo1 + "aa");
if (tBlackCatInfo1 != null) {
errMsg.append("客户单号" + list.get(1) + "单号重复\n");
continue;
}
}
if (custOrderNoCheck.containsKey(list.get(3))) {
errMsg.append("日本单号" + list.get(3) + "单号重复\n");
continue;
} else {
custOrderNoCheck.put(list.get(3), list.get(3));
String hql = "from TBlackCatInfo where changeNum = :changeNum";
Map<String, Object> param = new HashMap<>();
param.put("changeNum", list.get(3));
TBlackCatInfo tBlackCatInfo2 = (TBlackCatInfo) baseDao.getByHQL(hql, param);
if (tBlackCatInfo2 != null) {
errMsg.append("日本单号" + list.get(3) + "单号重复\n");
continue;
}
}
if (list.get(2) != null && list.get(2) != "" && list.get(2) != "null") {
if (tradeNoCheck.containsKey(list.get(2))) {
errMsg.append("换单号" + list.get(2) + "单号重复\n");
continue;
} else {
tradeNoCheck.put(list.get(2), list.get(2));
String hql = "from TBlackCatInfo where tradeNum = :tradeNum";
// String hql = "from TBlackCatInfo where tradeNum = :waybillNum";
Map<String, Object> param = new HashMap<>();
param.put("tradeNum", list.get(2));
TBlackCatInfo tBlackCatInfo2 = (TBlackCatInfo) baseDao.getByHQL(hql, param);
if (tBlackCatInfo2 != null) {
errMsg.append("换单号" + list.get(2) + "单号重复\n");
continue;
}
}
}
// 会员编号只能为数字和英文字母
if (list.get(0).matches(LETTER_DIGIT_REGEX) != true) {
errMsg.append("转单号" + list.get(3) + "会员编号只能为英文字母和数字\n");
continue;
}
// 地址验证
TBlackCatInfo tBlackCatInfo = new TBlackCatInfo();
boolean flag = false;
String hql = "from TBlackCatMail t1 where t1.postCode= :postCode";
Map<String, Object> params = new HashMap<String, Object>() {
private static final long serialVersionUID = 7873919242529938351L;
{
put("postCode", list.get(11).replaceAll("-", ""));
}
};
log.info("postCode ===> {}", params.get("postCode"));
List<TBlackCatMail> tBlackCatMails = baseDao.getListByHQL(hql, params);
log.info(String.format("tBlackCatMail ==> %s", JSON.toJSONString(tBlackCatMails)));
String addressOne = EDIUtil.ToSBC(list.get(12));
for (TBlackCatMail tBlackCatMail : tBlackCatMails) {
String address = tBlackCatMail.getCity() + tBlackCatMail.getState() + tBlackCatMail.getStreet();
if ("".equals(tBlackCatMail.getCity()) && tBlackCatMail.getCity() == null && "".equals(tBlackCatMail.getState())
&& tBlackCatMail.getState() == null && "".equals(tBlackCatMail.getStreet()) && tBlackCatMail.getStreet() == null) {
tBlackCatInfo.setReceAddOne(addressOne);
tBlackCatInfo.setSimpleZipCode(tBlackCatMail.getPostCodeShort());
tBlackCatInfo.setReceZipCode(list.get(11));
flag = true;
continue;
} else {
log.info("address{}" + address);
log.info("addressOne{}" + addressOne);
// 使用StartsWith验证addressOne是否以address开头
if (addressOne.replace("の", "ノ").startsWith(address.replace("の", "ノ"))
|| addressOne.replace("ノ", "の").startsWith(address.replace("ノ", "の"))) {
// 判断在郊区后面有没有数字
String addressAfter = addressOne.substring(address.length());
log.info("addressAfter{}" + addressAfter);
if (hasDigit(EDIUtil.ToDBC(addressAfter))) {
// 派送地址1
tBlackCatInfo.setReceAddOne(addressOne);
tBlackCatInfo.setSimpleZipCode(tBlackCatMail.getPostCodeShort());
tBlackCatInfo.setReceZipCode(list.get(11));
flag = true;
continue;
}
}
}
}
if (!flag) {
errMsg.append("转单号" + list.get(3) + "邮编对应的地址有误\n");
continue;
}
// 封装数据入库
tBlackCatInfo.setIsEdi(0);
tBlackCatInfo.setIsPrint((short) 0);
tBlackCatInfo.setImportTime(new Timestamp(date.getTime()));
tBlackCatInfo.setReceName(EDIUtil.ToSBC(list.get(9)));
tBlackCatInfo.setArriveNameEn(EDIUtil.ToSBC(list.get(10)));
tBlackCatInfo.setReceAddOne(EDIUtil.ToSBC(list.get(12)));
tBlackCatInfo.setWeight(new BigDecimal(list.get(13)).setScale(1, BigDecimal.ROUND_HALF_UP).toString());
// String sql = "select * from t_cat_post_code_config where post_code =:postCode";
// Map<String, Object> param = new HashMap<>();
// param.put("postCode", tBlackCatInfo.getReceZipCode().replace("-", ""));
// Map<String, Object> rstMap = (Map<String, Object>) baseDao.getBySQL(sql, param);
// if (rstMap != null) {
// tBlackCatInfo.setSimpleZipCode(rstMap.get("post_code_short").toString());
// }
// 判断客户邮编格式对不对
String[] mailCodeSplit = tBlackCatInfo.getReceZipCode().split("-");
if (mailCodeSplit.length != 2 || mailCodeSplit[0].length() != 3 || mailCodeSplit[1].length() != 4) {
errMsg.append("转单号" + list.get(3) + "邮编格式不对\n");
continue;
}
tBlackCatInfo.setProductName(EDIUtil.ToSBC(list.get(19)));
if (tBlackCatInfo.getProductName().length() > 25) {
errMsg.append("转单号" + list.get(3) + "品名长度不能大于25");
continue;
}
if (list.get(18) != null && !"".equals(list.get(18))) {
tBlackCatInfo.setDeclareCurrency(list.get(18));
}
if (list.get(22) != null && !"".equals(list.get(22))) {
tBlackCatInfo.setPrice(new BigDecimal(list.get(22)));
}
tBlackCatInfo.setCount(list.get(21));
tBlackCatInfo.setTradeNum(list.get(2));
tBlackCatInfo.setBoxCode(list.get(4));
tBlackCatInfo.setReceiverTel(list.get(8));
tBlackCatInfo.setPrintTimes(0);
// tBlackCatInfo.setSenderName(EDIUtil.ToSBC(list.get(41)));
// tBlackCatInfo.setDepAddOne(EDIUtil.ToSBC(list.get(42)));
tBlackCatInfo.setSenderName(EDIUtil.ToSBC("LED&TBS CO.,ltd"));
tBlackCatInfo.setDepAddOne(EDIUtil.ToSBC("大阪府泉南市新家2073-2"));
tBlackCatInfo.setDepZipCode("590-0503");
tBlackCatInfo.setWaybillNum(list.get(1));
tBlackCatInfo.setChangeNum(list.get(3));
tBlackCatInfo.setMemberNum(list.get(0));
tBlackCatInfos.add(tBlackCatInfo);
successCount++;
}
// 一条都未校验通过
if (successCount == 0) {
map.put("count", 0);
// 部分校验通过
} else if (successCount < lists.size()) {
map.put("count", 1);
} else {
// 全部通过
map.put("count", 2);
}
map.put("list", tBlackCatInfos);
map.put("msg", errMsg.toString());
return map;
}
// 判断一个字符串是否含有数字
public static boolean hasDigit(String content) {
boolean flag = false;
Pattern p = Pattern.compile(".*\\d+.*");
Matcher m = p.matcher(content);
if (m.matches())
flag = true;
return flag;
}