public UserImportResultVo importFromFile(HttpServletRequest request, BlacklistPo po) throws ServiceException {
/**
* 1.保存上传文件到服务器
*/
//获取项目在容器中的实际发布运行的根路径
String basePath = request.getSession().getServletContext().getRealPath("/");//获取服务器Session路径
//根据前端的key值获取文件名
MultipartHttpServletRequest multipartHttpservletRequest = (MultipartHttpServletRequest) request;
MultipartFile file = multipartHttpservletRequest.getFile("fileImport");
String origName = file.getOriginalFilename();
//截取后缀来检查
String ext = FilenameUtils.getExtension(origName).toLowerCase();
SystemLogUtils.Debug("上传的导入文件:" + origName);
if (!("xls".equals(ext) || "xlsx".equals(ext))) {
throw new ServiceException("请选择要导入的excel文件");
}
//构建导入文件保存路径
String datePath = DateConstants.DATE_FORMAT_NUM_SHORT().format(new Date());
path = path + "upload/" + user.getEntId() + "/blacklistImport/" + datePath;
//导入文件路径不存在就生成
File dir = new File(path);
if (!dir.exists()) {
dir.mkdirs();
}
//构建导入文件新的名称
String fileName = user.getEntId() + "_" + DateConstants.DATE_FORMAT_NUM().format(new Date()) + new Random().nextInt(10) + "." + ext;
String destPath = path + "/" + fileName;
//新建文件
File destFile = new File(destPath);
try {
//复制文件到新目录
file.transferTo(destFile);
} catch (IllegalStateException | IOException e) {
e.printStackTrace();
throw new ServiceException("上传文件失败");
}
/**
* 2.解析excel,添加名单到库,返回导入结果及结果文件路径
*/
try {
//本地文件测试
//path = path + "upload/" + user.getEntId();
//String fileName = "7766114_blacklist_data.xlsx";
//String destPath = path + "/" + fileName;
resultVo = parseExcel(destPath, po, request, user);
} catch (ServiceException e) {
throw new ServiceException(e.getMessage());
}
return resultVo;
}
@Transactional
private UserImportResultVo parseExcel(String path, BlacklistPo po, HttpServletRequest request, SsoUserVo user) throws ServiceException {
String entId = user.getEntId();
List<DBObject> failedList = new ArrayList<DBObject>();
UserImportResultVo resultVo = new UserImportResultVo();
List<String> failList = new ArrayList<String>();// 失败记录列表
FileOutputStream fileOut = null; //导入结果输出文件
//构建导入结果返回路径
String resultPath = request.getSession().getServletContext().getRealPath("/");//获取服务器Session路径
String datePath = DateConstants.DATE_FORMAT_NUM_SHORT().format(new Date());
String relativePath = "upload/" + entId + "/blacklistImport/" + datePath + "/result/";
resultPath = resultPath + relativePath;
try {
File file = new File(path);
Workbook workbook = null; //多态,两种格式支持。
if (path.endsWith("xlsx")) {
workbook = new XSSFWorkbook(file.getPath());
} else {
workbook = new HSSFWorkbook(new FileInputStream(file));
}
Sheet sheet = workbook.getSheetAt(0); // 获得第1个工作薄
int totalNum = 0;
int successNum = 0;
// 加入导入数量限制
int maxRow = sheet.getLastRowNum();
if(maxRow>20001){
throw new Exception("批量添加导入数量过多,请限制在2万条以内! ");
}
// 创建导出结果标题行
Row hssfRow0 = sheet.getRow(0);
/**
* 解析excel文件表头对应字段key,返回map
*/
Map<String, Integer> map = BlacklistTitleUtils.parseExcelTitle(hssfRow0);
if (map == null || map.size() == 0) {
throw new ServiceException("Excel文件表头读取失败,请检查模板是否正确。");
}
// 获取excel文件总列数,新建导出结果的列
int physicalNumberOfCells = hssfRow0.getPhysicalNumberOfCells();
//excel的cell是从0开始算的,所以cell的数量就是下个cell的坐标
hssfRow0.createCell(physicalNumberOfCells).setCellValue("导入结果");
sheet.setColumnWidth(physicalNumberOfCells, 50 * 256);
// 循环行Row,从第1行开始
for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
Row hssfRow = sheet.getRow(rowNum);
if (hssfRow == null) {
continue;
}
totalNum++;
DBObject dbo = new BasicDBObject(); // 新建names数据库插入对象
/**
* 从excel这一行中,读取所有字段的值并存入dbo
*/
for (Map.Entry<String, Integer> entry : map.entrySet()) {
int cellNum = entry.getValue();
String key = entry.getKey();
String value = ExcelUtil.getValue(hssfRow.getCell(cellNum)).trim();
dbo.put(key, value);
}
//固定字段
dbo.put("entId", entId);
dbo.put("creatorId", user.getUserId());
dbo.put("creatorName", user.getUserName());
dbo.put("createTime", createTime);
try {
if (StringUtils.isBlank((String) dbo.get("telPhone"))) {
//为空抛出异常,然后捕获异常记录失败
throw new ServiceException("电话号码不能为空!");
//或者直接记录失败然后continnue;
/*String sv = "导入失败:第 " + rowNum + " 行, 添加名单失败!";
hssfRow.createCell(physicalNumberOfCells).setCellValue(sv);
failList.add(sv);
failedList.add(dbo); //记录失败
continue;*/
}
if (StringUtils.isBlank((String) dbo.get("userName"))) {
throw new ServiceException("客户姓名不能为空!");
}
int success = blacklistDao.addBlacklist(dbo, entId); //保存名单到数据库。
if (success == 0) {
// 添加成功
successNum++;
hssfRow.createCell(physicalNumberOfCells).setCellValue("导入成功");
} else {
String sv = "导入失败:第 " + rowNum + " 行, 添加名单失败!";
hssfRow.createCell(physicalNumberOfCells).setCellValue(sv);
failList.add(sv);
failedList.add(dbo); //记录失败
continue;
}
} catch (ServiceException addE1) {
String s = "导入失败:第 " + rowNum + " 行, " + addE1.desc;
hssfRow.createCell(physicalNumberOfCells).setCellValue(s);
failList.add(s);
failedList.add(dbo); //记录失败
continue;
} catch (Exception addE2) {
// 失败
addE2.printStackTrace();
String s = "导入失败:第 " + rowNum + " 行, 导入失败!";
failList.add(s);
hssfRow.createCell(physicalNumberOfCells).setCellValue(s);
failedList.add(dbo); //记录失败
continue;
}
}
//没数据直接返回失败
if (totalNum == 0)
throw new ServiceException("没有有效数据");
//有导入错误,则生成结果文件返回
int failNum = totalNum - successNum;
if (failNum != 0) {
String resultFileName = entId + "_blacklist_ImportResult" + new Random().nextInt(10) + ".xlsx";
File dir = new File(resultPath);
if (!dir.exists()) {
dir.mkdirs();
}
File destFile = new File(resultPath + resultFileName);
if (!destFile.exists()) {
destFile.createNewFile();
}
SystemLogUtils.Debug("上传结果文件保存路径:" + resultPath + resultFileName);
fileOut = new FileOutputStream(resultPath + resultFileName);
workbook.write(fileOut);
fileOut.close();
resultVo.setResultFilePath(relativePath + resultFileName);
}
workbook.close();
resultVo.setTotalNum(totalNum + "");// 总记录条数
resultVo.setImportSuccessNum(successNum + "");
resultVo.setImportFailNum(failNum + "");
resultVo.setFailList(failList);
return resultVo;
} catch (Exception e) {
e.printStackTrace();
throw new ServiceException("解析excel文件失败,异常:" + e.getMessage());
}
}