Excel的导入并对数据库批量操作

最新推荐文章于 2022-06-01 15:00:36 发布

品一杯java咖啡

最新推荐文章于 2022-06-01 15:00:36 发布

阅读量235

点赞数

分类专栏： java 文章标签： Java

本文链接：https://blog.csdn.net/qq_43622789/article/details/99407341

版权

java 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

js中的ajax：

var formData = new FormData();
//单个文件
formData.append("file", $("根据id取值")[0].file[0])
//多个文件
var formData = new FormData();
var files = $('根据id取值')[0].files;
for (var i = 0; i < files.length; i++) {
       formData.append('file', files[i]);
	}

$.ajax({
		type: 'post',
		contentType: false,  //ajax中contentType设置为false是为了避免JQuery对其操作从而死去分界符，而服务不能正常解析文件
		processData: false,  //默认是true 默认情况下会将数据序列化以适应默认的内容类型；如果想发送的不想转换的信息的时候需要手动将其设置为false
		url: "项目方法路径",
		data: formData，
		success: function(data){
			//处理结果集
			//给予提示信息
		}
		error: function(){
			//给予提示信息
		}
	})

工具类：

//Service中会调用这个类
public class ExcelImportUtils {
// @描述：是否是2003的excel，返回true是2003
public static boolean isExcel2003(String filePath)  {
    return filePath.matches("^.+\\.(?i)(xls)$");
}

//@描述：是否是2007的excel，返回true是2007
public static boolean isExcel2007(String filePath)  {
        return filePath.matches("^.+\\.(?i)(xlsx)$");
    }

    /**
     * 验证EXCEL文件
     * @param filePath
     * @return
     */
    public static boolean validateExcel(String filePath){
        if (filePath == null || !(isExcel2003(filePath)) || (isExcel2007(filePath))){
            return false;
        }
        return true;
    }
}

Controller层：

@Controller
@RequestMapping("/admin/blacklist")
public class BlackListImportController {

    @Autowired
    BlackListImportService blackListImportService;

    @ResponseBody
    @RequestMapping("/importExcel")
    public Map<String, Object> importExcel( @RequestParam(value = "file") MultipartFile file)throws ParseException, IOException {
        return blackListImportService.importExcel(file);
    }
}

Service层：

@Service
public class BlackListImportService {

	@Autowired
    ClientMstMapper clientMstMapper;

    @Autowired
    BlacklistHisoryMapper blacklistHisoryMapper;
	
	//对文件进行一些验证 根据实际导入的Excel的
	public Map<String,Object> importExcel(MultipartFile mfile){
		Map<String, Object> responseMap = new HashMap<>();
		if(mfile == null){
		        responseMap.put("state", 0);
		        responseMap.put("msg", "文件不能为空!");
		        return responseMap;
		}
		String filename = mfile.getOriginalFilename
		if(!ExcelImportUtils .validateExcel(filename)){
				responseMap.put("state", 0);
		        responseMap.put("msg", "文件格式不对!");
		        return responseMap;
		}
		if(filename == "" || filename == null || mfile.getSize() == 0){
				responseMap.put("state", 0);
		        responseMap.put("msg", "文件不可以为空!");
		        return responseMap;
		}
		InputStream is = null;
		Workbook = null;
		try{
			is = mfile.getInputStream();
			if(ExcelImportUtils.isExcel2003(filename)){
				book = new HSSFWorkbook(is);  //2003的版本用HSSF
			}	else{
				book = new XSSFWorkbook(is);  //2007的版本用XSSF
			}
			String statInfor = readExcelValue(book);
			if (stateInfor.equals("导入文件中黑名单履历数据为空")){
                return ServiceUtil.generateResponseMap(null,Constants.STATE_FAILED,"导入文件中黑名单履历数据为空!");
            }else if (stateInfor.equals("导入成功")){
                return ServiceUtil.generateResponseMap(null,Constants.STATE_SUCCESS,"导入成功!");
            }else if (stateInfor.equals("黑名单表新增导入失败")){
                return ServiceUtil.generateResponseMap(null,Constants.STATE_FAILED,"黑名单表新增导入失败!");
            }else if (stateInfor.equals("用户表黑名单状态修改失败")){
                return ServiceUtil.generateResponseMap(null,Constants.STATE_FAILED,"用户表黑名单状态修改失败!");
            }
		} catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (book != null){
                    book.close();
                }
                if (is != null){
                    is.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        	responseMap.put("state", 0);
		    responseMap.put("msg", "导入失败!");
         return responseMap;
	}
	private String readExcelValue(Workbook book){
		StringBuffer errorMsg = new StringBuffer();  //错误信息接收器，用于返回状态消息
		Sheet sheet = null;
		int rows = 0;
		int sheetNum = book.getNumberOfSheets();  //获取excel文件的个数
		List<BlacklistHistory> blacklistHistories = new ArrayList<>(); //用来储存下面循环excel中值存入实体类中的对象
		for(int i = 0; i < sheetNum; i++){
			sheet = book.getSheetAt(i); //获取其中一个excel文件
			rows = sheet.getLastRowNum(); //获取这个excel中的总行数
			for (int x = 1; x < rows; x++){
				//如果不判断空列终止掉循,他会循环全部行大约6万多行
                if (sheet.getRow(x).getCell(0).toString().trim().equals(null) || sheet.getRow(x).getCell(0).toString().trim()  == null || sheet.getRow(x).getCell(0).toString().trim() == "" || sheet.getRow(x).getCell(0).toString().trim().equals("")){
                    break;
                }
                BlacklistHisory blacklistHisory = new BlacklistHisory();
                //getRow() 获取行 未知的需要遍历
                //getCell() 获取列 已知的参数里可以写死值
                //getRow().getCell()  获取某行某列值 参数控制某行某列
                blacklistHisory.setClientMstCode(sheet.getRow(x).getCell(0).toString().trim());
                ClientMstCriteria clientMstCriteria = new ClientMstCriteria();
                clientMstCriteria.createCriteria().andClientMstNameEqualTo(sheet.getRow(x).getCell(1).toString().trim());
                List<ClientMst> clientMstList = clientMstMapper.selectByExample(clientMstCriteria);
                if (clientMstList.size() > 0){
                    blacklistHisory.setClientMstId(clientMstList.get(0).getClientMstId());
                }else {
                    blacklistHisory.setClientMstId(null);
                }
                if ("设置黑名单".equals(sheet.getRow(x).getCell(2).toString().trim())){
                    blacklistHisory.setBlacklistFlg(true);
                }else{
                    blacklistHisory.setBlacklistFlg(false);
                }
                blacklistHisory.setReason(sheet.getRow(3).getCell(x).toString().trim());
                blacklistHisory.setClientDel(false);
                //userID
                blacklistHisory.setCreateUserId(18);
                blacklistHisory.setCreateDate(new Date());
                if (blacklistHisory != null || blacklistHisory.toString().trim().length() !=0){
                    blacklistHisories.add(blacklistHisory);
                }
            }
		}
		if(blacklistHistories.size() == 0){
			errorMsg.append("导入excel的数据为空")
		}
		
        int state = impBlacklistHisory(blacklistHisories);
        if (state == 1){
            errorMsg.append("导入成功");
        }else if (state == -2){
            errorMsg.append("黑名单表新增导入失败");
        }else if (state == -3){
            errorMsg.append("用户表黑名单状态修改失败");
        }else if(state == 0){
            errorMsg.append("导入失败");
        }
        return errorMsg.toString();
	}
	private int impBlacklistHistory(List<BlacklistHistory> dataList){
		int success = 1;
		int insertFail = -2;
		int updateFail = -3;
		if(dataList.size() == 0){
			return 0;
		}
		BlacklistHistory blacklistHistory;
		List<BlacklistHistory> blacklistHistorylist = new ArrayList<>();
		HashMap<String, BlacklistHistory> blackListHistoryHashMap = new HashMap<>();
		//当dataList中只有一列值得时候走这个
		StringBuffer codeCond = new StringBuffer();
		codeCond.append("'") //里面是单引号
		codeCond.append(dataList.get(0).getClientMstCode.toUpperCase())
		codeCond.append("'") //里面是单引号
		blackListHistoryHashMap.put(dataList.get(0).getClientMstCode,dataList.get(0));
		for(int i = 1; i < dataList.size(); i++){
			codeCond.append(",");
			codeCond.append("'"); //单引号
			codeCond.append(dataList.get(i).getClientMstCode.toUpperCase())；
			 codeCond.append("'"); //单引号
            blacklistHisoryHashMap.put(dataList.get(i).getClientMstCode(),dataList.get(i));
		}
		  List<ClientMst> clientMstsList = clientMstMapper.selectIn(codeCond); //根据拼接成的字符串去 in（）查询
		  //对excel中的数据和用户表匹配 进行验证
		  if (clientMstsList.size() > 0){
            for (ClientMst list:clientMstsList){
                blacklistHisory = blacklistHisoryHashMap.get(list.getClientMstCode());
                blacklistHisory.setClientMstId(list.getClientMstId());
                if (blacklistHisory.getBlacklistFlg() == list.getHouseMstBlacklistFlg()){
                    continue;
                }
                blacklistHisoryList.add(blacklistHisory);
            }
        }
		int insertNum = blacklistHisoryMapper.forInsert(blacklistHisoryList);
        int updateNum = clientMstMapper.forUpdate(blacklistHisoryList);
        if (insertNum > 0 && updateNum >0){
            return success;
        }else if(insertNum < 0){
            return insertFail;
        }else if (updateNum < 0){
            return updateFail;
        }
        return Constants.STATE_FAILED;  
	}
}