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

<!--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<>();

	@Override
	public void invoke(BatchEditDeptInfo data, AnalysisContext context) {
		list.add(data);
	}

	@Override
	public 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();

得到每个表格导入的数据，最后根据需求进行校验，获取有效数据即可。