java实现excel批量导入

相关坐标

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.9</version>
  </dependency> 
    
 <dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>3.9</version>
 </dependency>

<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml-schemas</artifactId>
	<version>3.9</version>
</dependency>

1 控制层方法

/**
     * 通过excel文件 批量导入 个人访客
     * @author xujianjian
     * @param request
     * @param response
     * @param filePath
     * @param session
     * @return
     * @throws Exception
     */
    @RequestMapping("/importFromExcel")
    @ResponseBody
    public Map<String, Object> importWorkerExcel(HttpServletRequest request, HttpServletResponse response, String filePath, HttpSession session) throws Exception {
   
        Map<String, Object> resultMap = new HashMap<String, Object>();
        String msg = "服务器异常，请稍后重试！";
        int flag = APPConstant.BACK_STATUS_ERROR;
        try {
   
            if (filePath == null || "".equals(filePath)) {
   
                msg = "没有获取到Excel路径，请重新上传！";
            } else {
   
                resultMap = technologyPersonalVisitorService.importFromExcel(filePath,session);
                return resultMap;
            }
            File file = new File(filePath);
            // 路径为文件且不为空则进行删除
            if (file.isFile() && file.exists()) {
   
                file.delete();
            }
        }
        catch (Exception e) {
   
            e.printStackTrace();
            msg = "服务器异常，请稍后重试！";
        }

        resultMap.put("flag", flag);
        resultMap.put("msg", msg);
        return resultMap;
    }

2.业务层方法

 /**
     * 批量导入
     *
     * @param filePath 上传的excel文件在硬盘上的地址
     * @param session
     * @return
     * @throws Exception
     */
    public Map<String, Object> importFromExcel(String filePath, HttpSession session) throws Exception {
   
        Map<String, Object> resultMap = new HashMap<String, Object>();
        int flag = APPConstant.BACK_STATUS_ERROR;
        //1.对excel表格文件内容进行解析,返回解析结果
        Map<String, Object> analysisResult = this.analysisFromExcel(filePath, session);
        //2.从返回结果中获取解析信息
        Boolean isOk = (Boolean) analysisResult.get("isOk");
        String resultMassage = (String) analysisResult.get("msg");
        //如果 解析成功，即 代表 上传的模板为其他模板
        if (isOk) {
   
            int successNum = 0; //成功个数
            int errorNum = 0; //失败个数
            //解析成功的数据集合
            List<Map> rightList = (List<Map>) analysisResult.get("rightResult");
            //解析失败的数据集合
            List<Map<String, Object>> errorResult = (List<Map<String, Object>>) analysisResult.get("errorResult");

            //循环将解析成功的数据存入数据库
            if (rightList != null && rightList.size() > 0) {
   
                UserBean user=(UserBean) session.getAttribute(SysConstant.SESSION_CURRENT_USER);
                for (Map map : rightList) {
   
                    TechnologyPersonalVisitor item = JSON.parseObject(JSONObject.toJSONString(map), TechnologyPersonalVisitor.class);
                    item.setRegisterOper(user.getUser_id