Excel导入与导出

Mr_Huang_1

已于 2022-09-05 11:55:59 修改

阅读量334

点赞数

文章标签： java 前端开发语言

于 2022-09-05 11:34:16 首次发布

本文链接：https://blog.csdn.net/Mr_Huang_1/article/details/126700697

版权

一、读取Excel数据

1、Controller代码

    /**
    * 读取Excel数据
    * file->文件
    * fileId->保存到数据库的唯一标识，用来区别不同批次
    */
    @RequestMapping(value = "/readExcelToDB", method = {RequestMethod.POST, RequestMethod.GET})
    @ApiOperation(value = "读取Excel数据,写入数据库")
    @ResponseBody
    public CommonRsp<?> readExcelToDB(@RequestParam("file") MultipartFile file,     @RequestParam("fileId") String fileId) {
        String fileidNew = ruleService.readExcelToDB(file, fileId);
        if (StringUtils.isNotBlank(fileidNew)) {
            return CommonRsp.successSys(fileidNew);
        } else {
            return CommonRsp.errorSys400("500", "上传失败，请联系管理员");
        }
    }

2、Service代码

    @Override
    @Transactional
    public String readExcelToDB(MultipartFile file, String fileId) {
        // 文件id：如果为空，是初始导入；如果非空，是重新导入
        // 是否重新导入
        boolean reUpload = true;
        // 根据oldFileid是否为空确定是导入还是重新导入！！
        if (org.apache.commons.lang.StringUtils.isBlank(fileId) || "null".equals(fileId)) {
            reUpload = false;
            // 初次导入，随机生成文件id
            fileId = UUIDUtil.randomUUID();
        }
        // 文件是否为空校验
        if (file.isEmpty()) {
            UP_STAT.remove(fileId);
            throw new BusinessException(CommonRsp.errorMsg(HttpStatus.OK, SuccessEnum.SUCCESS.getCode(), "请选择文件"));
        }
        try {
            Workbook workbook = new XSSFWorkbook(file.getInputStream());
            // 重新定义变量用于新线程（优化部分，暂未使用）
            String finalFileId = fileId;
            boolean finalReUpload = reUpload;
            Workbook finalWorkbook = workbook;
            // 核心代码
            uploadCore(finalFileId, finalReUpload, finalWorkbook);
        } catch (IOException e) {
            throw new BusinessException(CommonRsp.errorMsg(HttpStatus.OK, SuccessEnum.SUCCESS.getCode(), "获取文件失败"));
        } catch (Exception e) {
            throw new BusinessException(CommonRsp.errorMsg(HttpStatus.OK, SuccessEnum.SUCCESS.getCode(), "上传文件异常"));
        }
        return fileId;
    }

    /**
     * 上传文件核心代码
     */
    private void uploadCore(String fileId, boolean reUpload, Workbook workbook) throws Exception {
        // 两种获取sheet页方法
        Sheet ruleSheet = workbook.getSheet("规则维护");
        Sheet ruleSkillSheet = workbook.getSheetAt(2);
        if (ruleSheet == null || ruleSkillSheet == null) {
            throw new BusinessException(CommonRsp.errorMsg(HttpStatus.OK, SuccessEnum.SUCCESS.getCode(), "请勿修改模板"));
        }
        // 获取行数
        int lastRowNum = ruleSheet.getLastRowNum();
        if (lastRowNum < 1) {
            throw new BusinessException(CommonRsp.errorMsg(HttpStatus.OK, SuccessEnum.SUCCESS.getCode(), "没有读取到任何数据"));
        }
        // 将Excel内容读取到资源DO
        List<RuleImportDO> ruleImportDOList = readExcelToImportDO(ruleSheet, lastRowNum);
        if (ruleImportDOList.isEmpty()) {
            throw new BusinessException(CommonRsp.errorMsg(HttpStatus.OK, SuccessEnum.SUCCESS.getCode(), "没有读取到任何数据"));
        }
        // 将Excel内容读取到资源DO
        List<RuleSkillImportDO> ruleSkillImportDOList = readExcelToSkillImportDO(ruleSkillSheet);
        if (ruleSkillImportDOList.isEmpty()) {
            throw new BusinessException(CommonRsp.errorMsg(HttpStatus.OK, SuccessEnum.SUCCESS.getCode(), "没有读取到任何数据"));
        }
        // 各种校验，同时保存数据到临时表
        checkAndSave(ruleImportDOList, ruleSkillImportDOList, fileId, reUpload);
    }

    /**
    *各种校验，同时保存数据到临时表
    */
    private void checkAndSave(List<RuleImportDO> ruleImportDOList, List<RuleSkillImportDO> ruleSkillImportDOList, String fileId, boolean reUpload) {
        //遍历循环规则维护校验数据
        for (RuleImportDO excelVO : ruleImportDOList) {
            excelVO.setFileId(fileId);
            // 异常信息
            StringBuilder exceptionMsg = new StringBuilder();
            // 细节校验(细节校验，略)
            checkRuleSheet(exceptionMsg, excelVO);

            // 有报错信息，属于报错组
            if (!exceptionMsg.toString().equals("")) {
                excelVO.setRowType(3);
                excelVO.setExceptionMsg(exceptionMsg.toString());
                continue;
            }
            // 系统中存在相同规则编号属于修改组，不存在为新增组
            RuleDO ruleDO = ruleService.getOne(new QueryWrapper<RuleDO>().eq("qt_number", excelVO.getQtNumber()));
            if (ruleDO != null) {
                excelVO.setRowType(2);
            } else {
                excelVO.setRowType(1);
            }

        }
        //遍历循环规则脚本维护校验数据
        for (RuleSkillImportDO excelVO : ruleSkillImportDOList) {
            excelVO.setFileId(fileId);
            // 异常信息
            StringBuilder exceptionMsg = new StringBuilder();
            // 细节校验(细节校验，略)
            checkRuleSkillSheet(exceptionMsg, excelVO, ruleSkillImportDOList);
            // 有报错信息，属于报错组
            if (!exceptionMsg.toString().equals("")) {
                excelVO.setRowType(3);
                excelVO.setExceptionMsg(exceptionMsg.toString());
                continue;
            }
            // 区分新增组、修改组、报错组
            setSkillType(excelVO, ruleImportDOList);
        }
        //在规则维护添加脚本报错提示，并把规则维护设置为报错组
        for (RuleSkillImportDO ruleSkillImportDO : ruleSkillImportDOList) {
            for (RuleImportDO ruleImportDO : ruleImportDOList) {
                if (ruleSkillImportDO.getRowType() == 3) {
                    if (ruleSkillImportDO.getQtNumber().equals(ruleImportDO.getQtNumber())) {
                        ruleImportDO.setExceptionMsg(ruleImportDO.getExceptionMsg() + "【数据库类型】为：" + ruleSkillImportDO.getQsDbType() + "的脚本存在错误数据；");
                        ruleImportDO.setRowType(3);
                    }
                }
            }
        }
        // 重新导入删除相同内容
        if (reUpload) {
            deleteCommonData(fileId, ruleImportDOList, ruleSkillImportDOList);
        }
        // 保存第一个sheet数据到t_dg_qa_rule_import
        SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
        RuleImportMapper mapper = session.getMapper(RuleImportMapper.class);
        for (int i = 0; i < ruleImportDOList.size(); i++) {
            mapper.insertRuleOne(ruleImportDOList.get(i));
            commitSession(session, i);
        }
        commitSession(session, 999);
        // 保存第二个sheet数据到t_dg_qa_rule_skill_import
        for (int i = 0; i < ruleSkillImportDOList.size(); i++) {
            mapper.insertRuleSkillOne(ruleSkillImportDOList.get(i));
            commitSession(session, i);
        }
        commitSession(session, 999);
        session.close();
    }

二、查看Excel上传数据

1、Controller代码

    /**
     * 分页读取excel数据
     * @param params
     * @return
     */
    @PostMapping("/excelList")
    @ApiOperation(value = "上传后列表")
    public CommonRsp<?> excelList(@RequestBody Map<String, Object> params) {
        PageResult page = ruleService.readExcelPage(params);
        return CommonRsp.successSys(page);
    }

2、Service代码

    @Override
    public PageResult readExcelPage(Map<String, Object> params) {
        // 参数校验
        String fileId = (String) params.get("fileId");
        if (org.apache.commons.lang.StringUtils.isBlank(fileId)) {
            throw new BusinessException(CommonRsp.errorMsg(HttpStatus.OK, SuccessEnum.SUCCESS.getCode(), "fileId不能为空"));
        }
        Integer rowType = (Integer) params.get("rowType");
        if (rowType != 1 && rowType != 2 && rowType != 3) {
            throw new BusinessException(CommonRsp.errorMsg(HttpStatus.OK, "400", "rowType只能为1,2,3中的一个数字，分别表示新增，修改，报错"));
        }
        // 分页
        Integer pageNum = (Integer) params.get("pageNum");
        Integer pageSize = (Integer) params.get("pageSize");
        // 查询所有数据
        Integer sheetNum = (Integer) params.get("sheetNum");
        if (sheetNum == null) {
            throw new BusinessException(CommonRsp.errorMsg(HttpStatus.OK, "400", "sheetNum必填，且可选值[1,2]"));
        } else if (sheetNum == 1) {
            List<RuleImportDO> ruleImportDOList = ruleImportMapper.selectList(new QueryWrapper<RuleImportDO>().eq("file_id", fileId).eq("row_type", rowType));
            Page<Object> page = new Page<>(pageNum, pageSize, ruleImportDOList.size());
            List<RuleImportDO> pageRule = ruleImportDOList.stream().skip((pageNum - 1) * pageSize).limit(pageSize).collect(Collectors.toList());
            if (rowType == 2) {
                // 修改列表需要同时展示修改前后数据（略）
                return PageUtil.convert(page, genCompareRule(pageRule));
            }
            return PageUtil.convert(page, pageRule);
        } else {
            List<RuleSkillImportDO> ruleSkillImportDOList = ruleSkillImportMapper.selectList(new QueryWrapper<RuleSkillImportDO>().eq("file_id", fileId).eq("row_type", rowType));
            Page<Object> page = new Page<>(pageNum, pageSize, ruleSkillImportDOList.size());
            List<RuleSkillImportDO> pageRuleSkill = ruleSkillImportDOList.stream().skip((pageNum - 1) * pageSize).limit(pageSize).collect(Collectors.toList());
            if (rowType == 2) {
                // 修改列表需要同时展示修改前后数据（略）
                return PageUtil.convert(page, genCompareRuleSkill(pageRuleSkill));
            }
            return PageUtil.convert(page, pageRuleSkill);
        }
    }

三、查看数据无误后确认上传

1、Controller代码

     /**
     * 确认导入
     * @param fileId
     * @return
     */
    @PostMapping("/importExcelRule")
    @ApiOperation(value = "确认导入")
    public CommonRsp<?> importExcelRule(@RequestParam String fileId) {
        return CommonRsp.successSys(ruleService.importExcelRule(fileId));
    }

2、Service代码

    @Override
    @Transactional
    public Map<String, String> importExcelRule(String fileId) {
        // 响应参数
        Map<String, String> resMap = new HashMap<>();
        resMap.put("type", "1");//1-成功（默认）；2-有部分数据异常
        resMap.put("description", "导入成功");// 修改时系统内数据被删除，给出提示，并丢弃要修改规则（默认导入成功）
        resMap.put("fileId", "");// 新增时规则编号重复，将编号更新并存入临时表
        // 判断数据是否为空
        List<RuleImportDO> ruleSaveList = ruleImportMapper.selectList(new QueryWrapper<RuleImportDO>().eq("file_id", fileId).eq("row_type", 1));
        List<RuleImportDO> ruleUpdateList = ruleImportMapper.selectList(new QueryWrapper<RuleImportDO>().eq("file_id", fileId).eq("row_type", 2));
        List<RuleSkillImportDO> ruleSkillSaveList = ruleSkillImportMapper.selectList(new QueryWrapper<RuleSkillImportDO>().eq("file_id", fileId).eq("row_type", 1));
        List<RuleSkillImportDO> ruleSkillUpdateList = ruleSkillImportMapper.selectList(new QueryWrapper<RuleSkillImportDO>().eq("file_id", fileId).eq("row_type", 2));
        if (ruleSaveList == null && ruleUpdateList == null && ruleSkillSaveList == null && ruleSkillUpdateList == null) {
            throw new BusinessException(CommonRsp.errorMsg(HttpStatus.OK, "400", "没有新增或修改任何数据"));
        }
        // 新增
        if (ruleSaveList != null && ruleSkillSaveList != null) {
           // （业务代码）略
        }

        // 修改
        if (ruleUpdateList != null && ruleSkillUpdateList != null) {
           // （业务代码）略
        }
        // 删除临时表新增组和修改组数据
        ruleImportMapper.delete(new QueryWrapper<RuleImportDO>().eq("file_id", fileId).in("row_type", Arrays.asList(1, 2)));
        ruleSkillImportMapper.delete(new QueryWrapper<RuleSkillImportDO>().eq("file_id", fileId).in("row_type", Arrays.asList(1, 2)));
        return resMap;
    }

四、导出错误数据

1、Controller代码

    /**
     * 导出问题数据
     */
    @GetMapping(value = "/exportProblemData")
    @ApiOperation(value = "导出问题数据")
    public void exportProblemData(@RequestParam String fileId, HttpServletResponse response) {
        ruleService.exportProblemData(response, fileId);
    }

2、Service代码

    @Override
    public void exportProblemData(HttpServletResponse resp, String fileId) {
        if (org.apache.commons.lang.StringUtils.isBlank(fileId)) {
            throw new BusinessException(CommonRsp.errorMsg(HttpStatus.OK, SuccessEnum.SUCCESS.getCode(), "fileId不能为空"));
        }
        try {
            InputStream is = new FileInputStream(DOWNLOAD_FILE_PATH_RULE);
            //本地测试
            //InputStream is = new FileInputStream("C:\\Users\\dell\\Desktop\\excel\\模板\\质量规则模板.xlsx");
            OutputStream os = resp.getOutputStream();
            // 查询并将数据设置到Excel中（可能返回txt文件）
            //Workbook workbook = new SXSSFWorkbook(new XSSFWorkbook(is));
            //返回xlsx文件
            XSSFWorkbook workbook = new XSSFWorkbook(is);
            //写数据
            setExceptionData(fileId, workbook);
            // 设置Response头
            resp.reset();
            resp.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            resp.setCharacterEncoding(CHARSET_UTF_8);
            resp.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("exceptionData.xlsx", CHARSET_UTF_8));
            //resp.setHeader("Content-Disposition", "attachment;filename=");
            workbook.write(os);
            workbook.close();
        } catch (IOException e) {
            log.error("下载失败，请联系管理员", e);
            throw new BusinessException(CommonRsp.errorSys400(SuccessEnum.SUCCESS.getCode(), "下载失败：" + e.getMessage()));
        }
    }

    private void setExceptionData(String fileId, Workbook workbook) {
        // 查询临时表中的错误数据
        List<RuleImportDO> ruleImportDOList = ruleImportMapper.selectList(new QueryWrapper<RuleImportDO>().eq("file_id", fileId).eq("row_type", 3));
        if (ruleImportDOList.isEmpty()) {
            throw new BusinessException(CommonRsp.errorMsg(HttpStatus.OK, SuccessEnum.SUCCESS.getCode(), "无数据可下载"));
        }
        List<RuleSkillImportDO> ruleSkillImportDOList = ruleSkillImportMapper.selectList(new QueryWrapper<RuleSkillImportDO>().eq("file_id", fileId).eq("row_type", 3));
        // 写入Workbook
        writeRule(workbook, ruleImportDOList);
        writeRuleSkill(workbook, ruleSkillImportDOList);
    }