Excel得导入数据处理示例

最新推荐文章于 2024-04-03 09:59:08 发布

ZhenBin_Shen

最新推荐文章于 2024-04-03 09:59:08 发布

阅读量253

点赞数

分类专栏：后端

本文链接：https://blog.csdn.net/ZhenBin_Shen/article/details/103591703

版权

后端专栏收录该内容

13 篇文章 0 订阅

订阅专栏

1.MultipartFile file 最为重要得接收文件参数

@RequestMapping(value = "doImportExcelToUpdate.json", method = RequestMethod.POST)
    @ResponseBody
    public ResponseBean doImportExcelToUpdate(@RequestParam(value = "file", required = false) MultipartFile file) throws InvalidFormatException {
        Integer result = 0;
        try {
            Workbook book = WorkbookFactory.create(file.getInputStream());
            result = hrEvalManager.doImportExcelToUpdate(book);
        } catch (ValidateException e) {
            LogUtil.error(e, "【导入绩效失败】:" + e.getMessage());
            return new ResponseBean(false,e.getMessage());
        } catch (IOException e) {
            LogUtil.error(e, "【导入】文件读取异常");
            return new ResponseBean(false,e.getMessage());
        } catch (EncryptedDocumentException e) {
            LogUtil.error(e, "【导入绩效失败】:" + e.getMessage());
            return new ResponseBean(false,e.getMessage());
        } catch (BizException e) {
            LogUtil.error(e, "【导入绩效失败】:" + e.getMessage());
            return new ResponseBean(false,e.getErrorMsg());
        } catch (Exception e) {
            LogUtil.error(e, "【导入绩效失败】:" + e.getMessage());
            return new ResponseBean(false,e.getMessage());
        }
        return new ResponseBean(result);
    }

2.数据通过Workbook book = WorkbookFactory.create（xx，xx）转换成Excel格式处理数据

@Override
    public Integer doImportExcelToUpdate(Workbook book) {
        //存放Excel所倒入得数据信息
        ArrayList<MiHrEvalCus> dataList = new ArrayList<>();
        //获取第一页数据
        Sheet sheet = book.getSheetAt(0);
        //判断是否有数据
        int lastRowNum = sheet.getLastRowNum();
        if (lastRowNum == 0) {
            return 1;
        }
        for (int row = 1; row <= sheet.getLastRowNum(); row++) {
            MiHrEvalCus cus = new MiHrEvalCus();
            //获取行数据
            Row rowData = sheet.getRow(row);
            //过滤没有年度评级得数据
            if (rowData.getCell(12) != null) {
                cus.setEvaluateeYear(rowData.getCell(0) == null ? "" : rowData.getCell(0).toString());//年份
                cus.setOrgName(rowData.getCell(1) == null ? "" : rowData.getCell(1).toString());//部门
                cus.setEvaluateeName(rowData.getCell(2) == null ? "" : rowData.getCell(2).toString());//姓名
                cus.setNianDuPinJi(rowData.getCell(12) == null ? "" : rowData.getCell(12).toString());//年度评分
                System.out.println("年度评级" + rowData.getCell(12).toString());
                if (cus.getNianDuPinJi().equals("") || cus.getNianDuPinJi().equals("BE") || cus.getNianDuPinJi().equals("MSE") || cus.getNianDuPinJi().equals("ME") ||
                        cus.getNianDuPinJi().equals("EE") || cus.getNianDuPinJi().equals("SEE")) {
                    dataList.add(cus);
                } else {
                    throw new BizException("", "年度评分参数格式异常！");
                }
            }
        }
        if (CollectionUtils.isEmpty(dataList)) {
            return 1;
        }
        //年度评级得更新
        Integer result = doUpdateNianDuFenShu(dataList);
        return result;
    }