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

深碍√TFBOYSˉ_ 2024-03-16 12:26 31阅读 0赞

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

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