Excel导入

 

模板下载

    //下载权限Excel模板
    @ApiOperation(value = "文档下载，下载权限Excel导入模板")
    @RequestMapping(value = "/download",method = RequestMethod.GET)
    public void download(String fileName, HttpServletRequest request, HttpServletResponse response)throws IOException {
        ServletOutputStream out = null;
        FileInputStream ips = null;
        fileName = "inportAuth.xlsx";
        String path  = DOWNLOAD_PATH_PREFIX + fileName;
        try {
            //获取图片存放路径
            ips = new FileInputStream(new File(path));
            out = response.getOutputStream();
            //读取文件流
            int len = 0;
            byte[] buffer = new byte[1024 * 10];
            while ((len = ips.read(buffer)) != -1){
                out.write(buffer,0,len);
            }
            out.flush();
        }catch (Exception e){
            log.error("下载失败",e);
        }finally {
            out.close();
            ips.close();
        }
    }

 上传导入的Excel

  //上传Excel 带上权限类型参数
    @ApiOperation(value = "权限Excel导入，（系统设置-权限数据管理,Excel导入（系统权限类型version（1新核心，2旧核心，0总公司周边系统，3分公司自建系统））")
    @RequestMapping(value = "/importAuth", method = RequestMethod.POST)
    public DataResult importAuth(MultipartFile file,String version, HttpServletRequest request) {
        DataResult result = DataResult.success();
        List<AuthDataResp> list = new ArrayList<>();
        try {
            XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream() );

            XSSFSheet sheet = workbook.getSheetAt(0);
            for (int i =3;i<sheet.getLastRowNum()+1;i++){
                AuthDataResp authDataResp = new AuthDataResp();
                XSSFRow row = sheet.getRow(i);
                if (row.getCell(1) == null){
                    authDataResp.setJgradecode(null);
                }else {
                    authDataResp.setJgradecode(row.getCell(1).getStringCellValue().trim());
                }
                if (row.getCell(2) == null){
                    authDataResp.setJgradename(null);
                }else {
                    authDataResp.setJgradename(row.getCell(2).getStringCellValue().trim());
                }
                if (row.getCell(3) == null){
                    authDataResp.setSystemcode(null);
                }else {
                    authDataResp.setSystemcode(row.getCell(3).getStringCellValue().trim());
                }
                if (row.getCell(4) == null){
                    authDataResp.setGradeattributename(null);
                }else {
                    authDataResp.setGradeattributename(row.getCell(4).getStringCellValue().trim());
                }
                if (row.getCell(5) == null){
                    authDataResp.setSortnum(null);
                }else {
                    authDataResp.setSortnum((int) row.getCell(5).getNumericCellValue());
                }
                authDataResp.setCreatetime(DateUtil.getTime());

                //权限导入数据库
                list.add(authDataResp);
               // System.out.println(authDataResp);
            }
           // System.out.println(list.size());
            //不导入已存在的数据
            int size = authDataService.SaveAndFilterByVersion(list, version);
            result.setMessage("成功导入"+size+"条数据");
        }catch (Exception e){
            log.error("权限导入失败"+e.getMessage());
            result.setCode(400);
            result.setMessage("权限导入失败");
        }
        return result;
    }

list插入数据库

<insert id="insertForeachByPeripheral" parameterType="list">
        insert into t_core_authority_peripheral
        (jgradecode,jgradename,systemcode,gradeattributename,createtime,sortnum)
        values
        <foreach collection="list" item="item" index="index" separator=",">
            (
            #{item.jgradecode},
            #{item.jgradename},
            #{item.systemcode},
            #{item.gradeattributename},
            #{item.createtime},
            #{item.sortnum}
            )
        </foreach>
    </insert>