导入excel文件:
@Override
@Transactional(rollbackFor = Exception.class)
public Object importShopBlack(MultipartFile file, HttpServletResponse response, long userId) {
try {
//定义总数为0;
int coun = 0;
int fai = 0;
Map<Object, Object> map1 = new HashMap<>();
ArrayList<FwdcBlacklist> succlist = new ArrayList<>();
List<Map<String,String>> errorList = new ArrayList<>();
List<String> shopIdUpdate = new ArrayList<>();
String fileName = file.getOriginalFilename();
if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
return ResultDataUtils.failResult("上传文件格式不正确");
}
InputStream is = file.getInputStream();
Workbook wb = null;
wb = new HSSFWorkbook(is);
Sheet sheet = wb.getSheetAt(0);
if (sheet == null) {
throw new RuntimeException("文件数据错误");
}
coun=sheet.getLastRowNum();
ExecutorService pool=new ThreadPoolExecutor(5,10,
1L, TimeUnit.SECONDS,
new LinkedBlockingQueue<>(3),
Executors.defaultThreadFactory(),
new ThreadPoolExecutor.AbortPolicy());
final CountDownLatch endGate = new CountDownLatch(coun);
ArrayList<Map<String, String>> maps = new ArrayList<>();
for (int r = 1; r <= sheet.getLastRowNum(); r++) {
Row row = sheet.getRow(r);
if (isRowEmpty(row)) {
continue;
}
//获取excel中数据
Map<String,String> map = new HashMap<>();
map.put("sdmerid",getRowValue(row,0));
map.put("shopName",getRowValue(row,1));
map.put("creditCode",getRowValue(row,2));
map.put("idcard",getRowValue(row,3));
map.put("isOffline",getRowValue(row,4));
map.put("isDraw",getRowValue(row,5));
map.put("isNet",getRowValue(row,6));
maps.add(map);
}
pool.submit(()->{
for (Map<String, String> map : maps) {
try {
//开始判断数据是否正确
//第一步判断是否填写营业执照编号或者身份证号,二者必填其一
if (StrUtil.isEmpty(map.get("creditCode")) && StrUtil.isEmpty(map.get("idcard"))){
map.put("error","营业执照编号或者身份证号必填一项");
errorList.add(map);
continue;
}
//第二步判断是否下线,是否屏蔽金融,是否允许再次入网三项是否填写
if (StrUtil.isEmpty(map.get("isOffline")) || StrUtil.isEmpty(map.get("isDraw")) || StrUtil.isEmpty(map.get("isNet"))){
map.put("error","是否下线,是否屏蔽金融,是否允许再次入网三项必须填写");
errorList.add(map);
continue;
}
//三四五步的查询sql,判断有没有填写商编,没有填写商编不进入下面判断
List<Map<String,Object>> shopDate=new ArrayList<>();
shopDate=fwdcBlacklistMapper.getShopDate(map.get("creditCode"),map.get("idcard"),null);
if (StrUtil.isNotEmpty(map.get("sdmerid"))){
shopDate=fwdcBlacklistMapper.getShopDate(null,null,map.get("sdmerid"));
if (CollUtil.isNotEmpty(shopDate)){
//第四步如果填写身份证判断身份证是否和商编匹配
if (StrUtil.isNotEmpty(map.get("idcard")) && StrUtil.isNotEmpty(map.get("sdmerid")) && !(map.get("idcard").equals(shopDate.get(0).get("cardid")))){
map.put("error","身份证与商编不匹配");
errorList.add(map);
continue;
}
//第三步如果填写营业执照判断营业执照是否和商编匹配
if (StrUtil.isNotEmpty(map.get("creditCode")) && StrUtil.isNotEmpty(map.get("sdmerid")) && !(map.get("creditCode").equals(shopDate.get(0).get("creditcode")))){
map.put("error","营业执照与商编不匹配");
errorList.add(map);
continue;
}
//第五步商编与商户名称匹配
if (StrUtil.isNotEmpty(map.get("shopName")) && StrUtil.isNotEmpty(map.get("sdmerid")) && !(map.get("shopName").equals(shopDate.get(0).get("shopName")))){
map.put("error","商编与商户名称不匹配");
errorList.add(map);
continue;
}
}else {
map.put("error","商编不存在");
errorList.add(map);
continue;
}
}
// errorList.add(map);
//组装屏蔽内容
ArrayList<String> arr = new ArrayList<>();
if (StrUtil.isNotEmpty(map.get("isOffline")) && map.get("isOffline").toString().equals("是")){
arr.add("1");
}
if (StrUtil.isNotEmpty(map.get("isDraw"))&& map.get("isDraw").toString().equals("是")){
arr.add("2");
}
if (StrUtil.isNotEmpty(map.get("isNet"))&& map.get("isNet").toString().equals("否")){
arr.add("3");
}
if(arr == null || arr.size()<= 0){
map.put("error","屏蔽功能不能都无效");
errorList.add(map);
continue;
}
//插入数据
//通过shopid去查询所关联的商户信息,同时该商户不在黑名单表中
if (CollUtil.isNotEmpty(shopDate)){
for (Map<String, Object> selectByShopId : shopDate) {
FwdcBlacklist fwdcBlacklist = new FwdcBlacklist();
fwdcBlacklist.setShopId(Long.valueOf(selectByShopId.get("shopId").toString()));
fwdcBlacklist.setId(IdUtil.getSnowflake().nextIdStr());
fwdcBlacklist.setShieldFunction(String.join(",",arr));
fwdcBlacklist.setRealShieldFunction(String.join(",",arr));
fwdcBlacklist.setAddTime(new Date());
fwdcBlacklist.setAddUserId(userId);
fwdcBlacklist.setCreditCode(selectByShopId.get("creditcode")==null?"":selectByShopId.get("creditcode").toString());
fwdcBlacklist.setIdcard(selectByShopId.get("cardid")==null?"":selectByShopId.get("cardid").toString());
fwdcBlacklist.setSdmerid(selectByShopId.get("sdmerid")==null?"":selectByShopId.get("sdmerid").toString());
fwdcBlacklist.setShopName(selectByShopId.get("shopName")==null?"":selectByShopId.get("shopName").toString());
fwdcBlacklist.setUpdateTime(new Date());
fwdcBlacklist.setUpdateUserId(userId);
succlist.add(fwdcBlacklist);
//添加商户到下线数组中
if(arr.contains("1")){
shopIdUpdate.add(fwdcBlacklist.getShopId().toString());
}
}
}else if (StrUtil.isNotBlank(map.get("idcard"))){
FwdcBlacklist fwdcBlacklist = new FwdcBlacklist();
fwdcBlacklist.setId(IdUtil.getSnowflake().nextIdStr());
fwdcBlacklist.setShieldFunction(String.join(",",arr));
fwdcBlacklist.setRealShieldFunction(String.join(",",arr));
fwdcBlacklist.setAddTime(new Date());
fwdcBlacklist.setAddUserId(userId);
fwdcBlacklist.setIdcard(map.get("idcard"));
fwdcBlacklist.setUpdateTime(new Date());
fwdcBlacklist.setUpdateUserId(userId);
succlist.add(fwdcBlacklist);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
endGate.countDown();
}
}
return errorList;
});
try {
endGate.await();
} catch (InterruptedException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
pool.shutdown();
String error = JSON.toJSONString(errorList);
//判断失败次数大于0,就生成失败表,不导入数据
if (coun-succlist.size()>0){
//用redis缓存失败数据
String shopBlackErrorFile = "shopBlack"+userId;
redisUtil.set(shopBlackErrorFile, error ); // 没有新增返回true,存在返回false
}else {
// 没有失败的数据则进行数据插入,
for (FwdcBlacklist fwdcBlacklist : succlist) {
baseMapper.insertFwdcBlacklist(fwdcBlacklist);
}
}
//插入suc,失败条数fai
map1.put("suc", coun);
map1.put("fai", coun-succlist.size());
map1.put("addUserId", userId);
return ResultDataUtils.successResult(map1);
} catch (Exception e) {
logger.info("【{}批量导入黑名单商户信息失败!】", "");
return ResultDataUtils.failResult(e.getMessage());
}
}
/**
* 获取行列值
**/
public static String getRowValue(Row row, int i) {
if (row.getCell(i) == null) {
return null;
}
row.getCell(i).setCellType(Cell.CELL_TYPE_STRING);
return row.getCell(i) == null || row.getCell(i).toString().isEmpty() ? null
: row.getCell(i).getStringCellValue();
}
导出错误文件:
/**
* 黑名单错误信息表导出
*
* @param request
* @return
*/
@RequestMapping("/errorFile")
public void errMsgExportExcel(HttpServletRequest request, HttpServletResponse response) {
logger.info("【黑名单错误信息表导入】导出错误信息开始......");
try {
Map<String, Object> map = new HashMap<>();
long startTime = System.currentTimeMillis();
//组装redis键
String addUserId = request.getParameter("addUserId") == null ? null : request.getParameter("addUserId");
String keyRedis="shopBlack"+addUserId;
List<Map> list =null;
if (redisUtil.hasKey(keyRedis)){
list = JSONArray.parseArray(redisUtil.get(keyRedis).toString(), Map.class);
}
HSSFWorkbook wb = new HSSFWorkbook();
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
HSSFSheet sheet = wb.createSheet("错误信息表");
for (int i = 0; i <= 22; i++) {
sheet.setColumnWidth(i, 20 * 256);
}
HSSFRow rowNext = null;
HSSFRow row = sheet.createRow(0);
row.setHeight((short) (26.25 * 20));
// 设置列名
row.createCell(0).setCellValue("易生商编");
row.createCell(1).setCellValue("商户名称");
row.createCell(2).setCellValue("营业执照编号");
row.createCell(3).setCellValue("身份证号");
row.createCell(4).setCellValue("是否下线");
row.createCell(5).setCellValue("是否屏蔽金融");
row.createCell(6).setCellValue("是否允许再次入网");
row.createCell(7).setCellValue("错误信息");
if (CollUtil.isNotEmpty(list) && list.size() > 0) {
for (int i = 0; i < list.size(); i++) {
rowNext = sheet.createRow(i + 1);
rowNext.setHeight((short) (26.25 * 15));
rowNext.createCell(0).setCellValue(
list.get(i).get("sdmerid") == null || list.get(i).get("sdmerid") == "" ? null
: list.get(i).get("sdmerid").toString());
rowNext.createCell(1).setCellValue(
list.get(i).get("shopName") == null || list.get(i).get("shopName") == "" ? null
: list.get(i).get("shopName").toString());
rowNext.createCell(2).setCellValue(
list.get(i).get("creditCode") == null || list.get(i).get("creditCode") == "" ? null
: list.get(i).get("creditCode").toString());
rowNext.createCell(3).setCellValue(
list.get(i).get("idcard") == null || list.get(i).get("idcard") == "" ? null
: list.get(i).get("idcard").toString());
rowNext.createCell(4).setCellValue(
list.get(i).get("isOffline") == null || list.get(i).get("isOffline") == "" ? null
: list.get(i).get("isOffline").toString());
rowNext.createCell(5).setCellValue(
list.get(i).get("isDraw") == null || list.get(i).get("isDraw") == "" ? null
: list.get(i).get("isDraw").toString());
rowNext.createCell(6).setCellValue(
list.get(i).get("isNet") == null || list.get(i).get("isNet") == "" ? null
: list.get(i).get("isNet").toString());
rowNext.createCell(7).setCellValue(
list.get(i).get("error") == null || list.get(i).get("error") == "" ? null
: list.get(i).get("error").toString());
}
}
redisUtil.del(keyRedis);
response.setContentType("application/vnd.ms-excel;charset=utf-8");
OutputStream os = response.getOutputStream();
response.setHeader("Content-disposition",
"attachment;filename=" + DownChineseEncode.setFileDownHeader(request, response, "错误信息表.xlsx"));
wb.write(os);
os.flush();
os.close();
long endTime = System.currentTimeMillis();
// 删除redis缓存
logger.info("【黑名单错误信息表导入派发】导出错误信息结束，耗时：【{}】", (endTime - startTime));
} catch (Exception e) {
logger.error("【黑名单错误信息表导入派发】导出错误信息异常：【{}】", e);
}
}
971
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
