Java 使用esayExcel进行导出、导入包含多个sheet页面

2023-08-19 21:32

短信预约 -IT技能 免费直播动态提醒

<!--easyexcel --> <dependency>     <groupId>com.alibaba</groupId>     <artifactId>easyexcel</artifactId>     <version>2.2.6</version> </dependency>

本文教程适合小白直接使用：先导出excel(含多个sheet)，后导入，以上为导入依赖

导出excel(含多个sheet页，第一个sheet导出现有的数据，第二个sheet导出无数据)

        HttpServletResponse response = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getResponse();        try {        //先设置了导出表头，详细在csdn查看如何设置导出表头            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");            response.setCharacterEncoding("utf-8");            String fileName = URLEncoder.encode("AI机器人自动武器填充", "UTF-8").replaceAll("\\+", "%20");            response.setHeader("Content-Disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");            response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();            Workbook workbook = EasyExcelUtil.getWorkbook(excelWriter);            //第一个表格            WriteSheet batchEditDepartment = EasyExcel.writerSheet(0, "AI全方位攻略顶实验室").head(BatchEditDeptInfo.class).build();            //第一个表格是有数据的，所以去数据库随便查一个表，查完后，为了便于用户使用要针对数据处理            List<BatchEditDeptInfo> vos = this.deptInfoMapper.queryAllDeptInfoByCompanyCode(companyCode);            Map<String, String> deptCodeAndDeptNameMap = new HashMap<>();            if (CollectionUtils.isNotEmpty(vos)) {                for (BatchEditDeptInfo vo : vos) {                    deptCodeAndDeptNameMap.put(vo.getDeptCode(),vo.getDeptName());                }            }            if (CollectionUtils.isNotEmpty(vos)) {                for (BatchEditDeptInfo vo : vos) {                    //上级部门名称                    if (StringUtils.isNotEmpty(vo.getParentCode())) {                        if (!"0".equals(vo.getParentCode())) {String parentName = deptCodeAndDeptNameMap.get(vo.getParentCode());vo.setParentName(parentName);                        }                    }                    //部门负责人                    List<String> employeeCodes = departmentHeadService.queryDepartmentHeadListByDeptCode(vo.getDeptCode());                    if (CollectionUtils.isNotEmpty(employeeCodes)) {                        StringBuilder chargeMan = new StringBuilder();                        StringBuilder chargeManPhone = new StringBuilder();                        for (int i = 0; i < employeeCodes.size(); i++) {EmployeeInfoVO info = employeeInfoService.queryEmployeeInfoByEmployeeCode(employeeCodes.get(i));if (i == employeeCodes.size() - 1) {    if (StringUtils.isNotEmpty(info.getEmployeeName())) {        chargeMan.append(info.getEmployeeName());    }    if (StringUtils.isNotEmpty(info.getPhoneNum())) {        chargeManPhone.append(info.getPhoneNum());    }} else {    if (StringUtils.isNotEmpty(info.getEmployeeName())) {        chargeMan.append(info.getEmployeeName()).append('、');    }    if (StringUtils.isNotEmpty(info.getPhoneNum())) {        chargeManPhone.append(info.getPhoneNum()).append('、');    }}                        }                        vo.setPhone(chargeManPhone.toString());                        vo.setEmployeeName(chargeMan.toString());                    }                    //部门类型                    if (StringUtils.isNotEmpty(vo.getDepartmentType())) {                        if ("0".equals(vo.getDepartmentType())) {vo.setDepartmentType("AI黑客部");                        } else if ("1".equals(vo.getDepartmentType())) {vo.setDepartmentType("AI军事部");                        } else if ("-1".equals(vo.getDepartmentType())){vo.setDepartmentType("AI能源部");                        }                    }                    //部门类别                    if (ObjectUtils.isNotEmpty(vo.getCategoryId())) {                        CompanyDepartmentCategory category = categoryMapper.selectById(vo.getCategoryId());                        vo.setCategoryValue(category.getCategoryValue());                    }                }            }            excelWriter.write(vos, batchEditDepartment);            //设置导出的字体(第一个表格)            Font firstSheetFirstFont = workbook.createFont();            firstSheetFirstFont.setColor(IndexedColors.RED1.getIndex());            //单元格风格 one:控制特殊字体 TWO:控制通用字体            CellStyle cellStyleOne = workbook.createCellStyle();            cellStyleOne.setAlignment(HorizontalAlignment.CENTER);            cellStyleOne.setVerticalAlignment(VerticalAlignment.CENTER);            cellStyleOne.setFillBackgroundColor(IndexedColors.WHITE.getIndex());            cellStyleOne.setFont(firstSheetFirstFont);            //单元格风格 2            CellStyle cellStyleTwo = workbook.createCellStyle();            cellStyleTwo.setAlignment(HorizontalAlignment.CENTER);            cellStyleTwo.setVerticalAlignment(VerticalAlignment.CENTER);            cellStyleTwo.setFillBackgroundColor(IndexedColors.WHITE.getIndex());            //第一个表的表头风格设置            Row firstSheetRow = workbook.getSheet("AI全方位攻略顶实验室").getRow(0);            firstSheetRow.getCell(1).setCellStyle(cellStyleOne);            firstSheetRow.getCell(8).setCellStyle(cellStyleOne);            firstSheetRow.getCell(2).setCellStyle(cellStyleOne);            firstSheetRow.getCell(0).setCellStyle(cellStyleTwo);            firstSheetRow.getCell(3).setCellStyle(cellStyleTwo);            firstSheetRow.getCell(4).setCellStyle(cellStyleTwo);            firstSheetRow.getCell(5).setCellStyle(cellStyleTwo);            firstSheetRow.getCell(6).setCellStyle(cellStyleTwo);            firstSheetRow.getCell(7).setCellStyle(cellStyleTwo);            firstSheetRow.getCell(9).setCellStyle(cellStyleTwo);            //冻结1：excel的冻结需要锁表            Sheet sheetAt = workbook.getSheetAt(0);            int totalData = sheetAt.getPhysicalNumberOfRows();//            sheetAt.createFreezePane(1,totalData,2,totalData);            //冻结第一列            sheetAt.createFreezePane(2,0,2,0);//            //锁表//            sheetAt.protectSheet("111");            //可以选择：隐藏第第一列            sheetAt.setColumnHidden(1,true);            List<DeptTemplateExport2> exportArrayList = new ArrayList<>();            //第二个sheet页面，空数据            WriteSheet firstSheet = EasyExcel.writerSheet(1, "AI失控自毁列表").head(DeptTemplateExport2.class).build();            excelWriter.write(exportArrayList, firstSheet);            //给第一个导出的表格设置字体和样式            Row firstSheetFirstRow = workbook.getSheet("AI失控自毁数据").getRow(0);            firstSheetFirstRow.getCell(0).setCellStyle(cellStyleOne);            firstSheetFirstRow.getCell(1).setCellStyle(cellStyleOne);            firstSheetFirstRow.getCell(2).setCellStyle(cellStyleOne);            firstSheetFirstRow.getCell(7).setCellStyle(cellStyleOne);            firstSheetFirstRow.getCell(3).setCellStyle(cellStyleTwo);            firstSheetFirstRow.getCell(4).setCellStyle(cellStyleTwo);            firstSheetFirstRow.getCell(5).setCellStyle(cellStyleTwo);            firstSheetFirstRow.getCell(6).setCellStyle(cellStyleTwo);            firstSheetFirstRow.getCell(8).setCellStyle(cellStyleTwo);            excelWriter.finish();        } catch (IOException e) {            logger.error("导出异常:{}", e.getMessage());            throw new ServiceNewException(FoundationErrorMapping.GENERAL_SERVICE_EXCEPTION, "导出异常");        }

以上就是导出excel含2个sheet页的基础代码，若是代码太多，建议处理：两个表都用 new ArrayList<自定义实体类>()，填充数据即可，导出导入亲测无问题。

读取刚刚导出的excel表格(多sheet页)

esayExcel要读取多个sheet页提供了监视器，针对每个sheet建立一个监视器，以下代码可以直接复制粘贴

public class BatchEditDeptInfoListener extends AnalysisEventListener<BatchEditDeptInfo> {private List<BatchEditDeptInfo> list = new ArrayList<>();@Overridepublic void invoke(BatchEditDeptInfo data, AnalysisContext context) {list.add(data);}@Overridepublic void doAfterAllAnalysed(AnalysisContext context) {}public List<BatchEditDeptInfo> getData() {return list;}}

第二个表格的监视器

public class DeptTemplateExport2Listener extends AnalysisEventListener<DeptTemplateExport2> {    private List<DeptTemplateExport2> list = new ArrayList<>();    @Override    public void invoke(DeptTemplateExport2 data, AnalysisContext context) {        list.add(data);    }    @Override    public void doAfterAllAnalysed(AnalysisContext context) {    }    public List<DeptTemplateExport2> getData() {        return list;    }}

获取表格中的数据

ExcelReader excelReader = EasyExcel.read(file.getInputStream()).build();                BatchEditDeptInfoListener batchEditDeptInfoListener = new BatchEditDeptInfoListener();                DeptTemplateExport2Listener deptTemplateExport2Listener = new DeptTemplateExport2Listener();                ReadSheet sheet1 = EasyExcel.readSheet(0).head(BatchEditDeptInfo.class).registerReadListener(batchEditDeptInfoListener).build();                ReadSheet sheet2 = EasyExcel.readSheet(1).head(DeptTemplateExport2.class).registerReadListener(deptTemplateExport2Listener).build();                excelReader.read(sheet1,sheet2);                excelReader.finish();              List<BatchEditDeptInfo>  readExcelZero = batchEditDeptInfoListener.getData();              List<DeptTemplateExport2>  readExcelOne = deptTemplateExport2Listener.getData();