EasyExcel导出多个sheet封装

导出多个sheet

在需求中，会有需要导出多种sheet的情况，那么这里使用easyexcel进行整合

步骤

1、导入依赖

        <dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId></dependency><dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.1.1</version></dependency>

2、Sheet类

主要封装了需要导出的sheet，类，数据等

package com.eshore.easyexcel.entity;import lombok.Builder;
import lombok.Data;import java.util.List;@Data
public class SheetInfoBean<T> {/*** sheet页名称*/private String sheetName;/*** sheet标题bean*/private Class<?> headClass;/*** sheet页数据*/private List<T> dataList;public SheetInfoBean(String sheetName, Class<?> headClass, List<T> dataList) {this.sheetName = sheetName;this.headClass = headClass;this.dataList = dataList;}
}

3、导出方法

package com.eshore.easyexcel.utils;import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.eshore.easyexcel.entity.SheetInfoBean;
import lombok.extern.slf4j.Slf4j;import java.io.File;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;@Slf4j
public class EasyExcelUtils {private final static String XLSX=".xlsx";/*** author:walker* time: 2024/6/4* description:  导出文件* prefix：前缀 如： D:/hello/* title：文件名称 如： test* @return*/public static String exportFile(List<SheetInfoBean> sheetInfoBeans,String prefix,String title){// 导出文件String path=prefix+title+XLSX;File dir = new File(prefix);if(!dir.exists()){dir.mkdirs();}File file = new File(path);try(ExcelWriter excelWriter = EasyExcel.write(file).build()) {WriteSheet writeSheet;List<String> exclueFields = new ArrayList<>();for (SheetInfoBean bean : sheetInfoBeans) {Class<?> headClass = bean.getHeadClass();Field[] declaredFields = headClass.getDeclaredFields();for (Field declaredField : declaredFields) {ExcelProperty annotation = declaredField.getAnnotation(ExcelProperty.class);if(annotation==null) {exclueFields.add(declaredField.getName());}}// 构建sheet对象writeSheet = EasyExcel.writerSheet(bean.getSheetName()).head(bean.getHeadClass()).excludeColumnFieldNames(exclueFields).build();// 写出sheet数据excelWriter.write(bean.getDataList(), writeSheet);}// 关流excelWriter.finish();} catch (Exception e) {log.error("导出失败，原因如下:",e);// do something you want}return path;}
}

注意：
里面做了一个处理，就是当没有@ExcelProperty的时候，该字段则不导出，否则需要对字段进行excludeColumnFieldNames的设置。相对来说麻烦一些

4、案例

需要使用@ExcelProperty对属性进行配置

1、学生类

package com.walker.sample.easyexcel;import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;@Data
public class Student {@ExcelProperty("姓名")private String name;@ExcelProperty("年龄")private Integer age;
}

2、老师类

package com.walker.sample.easyexcel;import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;@Data
public class Teacher {@ExcelProperty("姓名")private String name;@ExcelProperty("年龄")private Integer age;
}

3、测试方法

package com.walker.sample.easyexcel;import easyexcel.entity.SheetInfoBean;
import easyexcel.utils.EasyExcelUtils;
import lombok.extern.slf4j.Slf4j;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;import java.util.ArrayList;
import java.util.List;@Slf4j
@SpringBootTest
public class EasyExcelTest {@Testpublic void export(){List<Student> students = new ArrayList<>();for (int i = 0; i < 10; i++) {Student student = new Student();student.setName("学生"+i);student.setAge(i);students.add(student);}List<Teacher> teachers = new ArrayList<>();for (int i = 0; i < 10; i++) {Teacher teacher = new Teacher();teacher.setName("老师"+i);teacher.setAge(i);teachers.add(teacher);}List<SheetInfoBean> sheetInfoBeans = new ArrayList<>();sheetInfoBeans.add(new SheetInfoBean("学生",Student.class,students));sheetInfoBeans.add(new SheetInfoBean("老师",Teacher.class,teachers));String prefix="D:/";EasyExcelUtils.exportFile(sheetInfoBeans,prefix,"学生老师表");}
}