easypoi导出excel可自定义表头

1，简介

easypoi功能如同名字easy,主打的功能就是容易,让一个没见接触过poi的人员就可以方便的写出Excel导出,Excel模板导出,Excel导入,Word模板导出,通过简单的注解和模板语言(熟悉的表达式语法),完成以前复杂的写法

官网：http://doc.wupaas.com/docs/easypoi

测试代码地址：easypoi导出excel测试地址

2，添加easypoi依赖

1.  
   <dependency>
2.  
   <groupId>cn.afterturn</groupId>
3.  
   <artifactId>easypoi-base</artifactId>
4.  
   <version>4.3.0</version>
5.  
   </dependency>
6.  
   <dependency>
7.  
   <groupId>cn.afterturn</groupId>
8.  
   <artifactId>easypoi-web</artifactId>
9.  
   <version>4.3.0</version>
10.  
    </dependency>
11.  
    <dependency>
12.  
    <groupId>cn.afterturn</groupId>
13.  
    <artifactId>easypoi-annotation</artifactId>
14.  
    <version>4.3.0</version>
15.  
    </dependency>

注：版本尽量保存高点，版本过低可能出现图片导不出，表头无法自动合并等问题。

3，实战测试

准备工具：在游览器导出进行测试

1.  
   @Component
2.  
   public class StreamServiceImpl {
3.  
    
4.  
   /**
5.  
   * 获取导出流
6.  
   * @param response
7.  
   * @param fileName 导出文件名
8.  
   * @return
9.  
   */
10.  
    public ServletOutputStream getOutputStream(HttpServletResponse response,String fileName) throws IOException {
11.  
     
12.  
    fileName = URLEncoder.encode(fileName ".xlsx", "UTF-8");
13.  
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
14.  
    response.setHeader("Content-Disposition", "attachment;filename=" fileName);
15.  
    // 客户端不缓存
16.  
    response.addHeader("Pragma", "no-cache");
17.  
    response.addHeader("Cache-Control", "no-cache");
18.  
     
19.  
    ServletOutputStream stream = response.getOutputStream();
20.  
     
21.  
    return stream;
22.  
    }
23.  
    }

3.1，基于注解导出excel

3.1.1，主要注解

@Excel

@ExcelCollection

一对多的集合注解,用以标记集合是否被数据以及集合的整体排序

@ExcelEntity

标记是不是导出excel 标记为实体类,一遍是一个内部属性类,标记是否继续穿透,可以自定义内部id

@ExcelIgnore

忽略这个属性,多使用需循环引用中,

@ExcelTarget

限定一个到处实体的注解,以及一些通用设置,作用于最外面的实体

3.1.2，测试

实体类：

1.  
   @Getter
2.  
   @Setter
3.  
   @AllArgsConstructor
4.  
   @NoArgsConstructor
5.  
   @ExcelTarget("courseEntity")
6.  
   public class CourseEntity {
7.  
   /** 主键 */
8.  
   private String id;
9.  
   /** 课程名称 */
10.  
    @Excel(name = "课程名称", orderNum = "1", width = 25,needMerge = true)
11.  
    private String name;
12.  
    /** 老师主键 */
13.  
    @ExcelEntity(id = "absent")
14.  
    private TeacherEntity mathTeacher;
15.  
     
16.  
    @Excel(name = "logo",type = 2,imageType = 1,needMerge = true)
17.  
    private String logoUrl;
18.  
     
19.  
    @ExcelCollection(name = "学生", orderNum = "4")
20.  
    private List<StudentEntity> students;
21.  
    }
22.  
     
23.  
     
24.  
    @Getter
25.  
    @Setter
26.  
    @AllArgsConstructor
27.  
    @NoArgsConstructor
28.  
    @ExcelTarget("studentEntity")
29.  
    public class StudentEntity {
30.  
    /**
31.  
    * id
32.  
    */
33.  
    private String id;
34.  
    /**
35.  
    * 学生姓名
36.  
    */
37.  
    @Excel(name = "学生姓名", height = 20, width = 30, isImportField = "true_st")
38.  
    private String name;
39.  
    /**
40.  
    * 学生性别
41.  
    */
42.  
    @Excel(name = "学生性别", replace = { "男_1", "女_2" }, suffix = "生", isImportField = "true_st")
43.  
    private int sex;
44.  
     
45.  
    @Excel(name = "出生日期", databaseFormat = "yyyyMMddHHmmss", format = "yyyy-MM-dd", isImportField = "true_st", width = 20)
46.  
    private Date birthday;
47.  
     
48.  
    @Excel(name = "进校日期", databaseFormat = "yyyyMMddHHmmss", format = "yyyy-MM-dd")
49.  
    private Date registrationDate;
50.  
    }
51.  
     
52.  
     
53.  
    @Getter
54.  
    @Setter
55.  
    @AllArgsConstructor
56.  
    @NoArgsConstructor
57.  
    @ExcelTarget("teacherEntity")
58.  
    public class TeacherEntity {
59.  
    private String id;
60.  
    /** name */
61.  
    @Excel(name = "主讲老师_major,代课老师_absent", orderNum = "1", isImportField = "true_major,true_absent",needMerge = true)
62.  
    private String name;
63.  
    }

导出代码：

1.  
   /**
2.  
   * 基于注解导出excel
3.  
   */
4.  
   @RestController
5.  
   @RequestMapping("/annotation")
6.  
   public class AnnotationController {
7.  
   @Autowired
8.  
   private StreamServiceImpl streamService;
9.  
    
10.  
    @GetMapping("/exportExcel")
11.  
    public void exportExcel(HttpServletResponse response) throws IOException {
12.  
    //填充数据
13.  
    List<CourseEntity> list = new ArrayList<>();
14.  
     
15.  
    for (int i = 0; i < 3; i ) {
16.  
    CourseEntity course = new CourseEntity();
17.  
    course.setId("" i);
18.  
    course.setName("张三-" i);
19.  
    course.setLogoUrl("http://www.zhcgdsj.suzhou.gov.cn/others//1677053432441.jpeg");
20.  
    course.setMathTeacher(new TeacherEntity("1","法外狂徒"));
21.  
     
22.  
    List<StudentEntity> studentEntityList = new ArrayList<>();
23.  
    for (int j = 0; j < 2; j ) {
24.  
    StudentEntity student = new StudentEntity();
25.  
    student.setId("" j);
26.  
    student.setName("李四" j);
27.  
    student.setSex(0);
28.  
    student.setBirthday(new Date());
29.  
    student.setRegistrationDate(new Date());
30.  
    studentEntityList.add(student);
31.  
    }
32.  
    course.setStudents(studentEntityList);
33.  
    list.add(course);
34.  
    }
35.  
     
36.  
    Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("基于注解导出excel", "测试"), CourseEntity.class, list);
37.  
    ServletOutputStream stream = streamService.getOutputStream(response, "基于注解导出excel");
38.  
    workbook.write(stream);
39.  
    stream.flush();
40.  
    stream.close();
41.  
    workbook.close();
42.  
    }
43.  
    }

测试结果：

3.2，基于模板导出excel

excel模板：

导出代码：

1.  
   @GetMapping("/exportExcel")
2.  
   public void exportExcel(HttpServletResponse response) throws IOException {
3.  
    
4.  
   TemplateExportParams params = new TemplateExportParams(
5.  
   "excel/templates.xls");
6.  
   Map<String, Object> map = new HashMap<String, Object>();
7.  
   map.put("date", "2014-12-25");
8.  
   map.put("money", 2000000.00);
9.  
   map.put("upperMoney", "贰佰万");
10.  
    map.put("company", "执笔潜行科技有限公司");
11.  
    map.put("bureau", "财政局");
12.  
    map.put("person", "JueYue");
13.  
    map.put("phone", "1879740****");
14.  
    List<Map<String, String>> listMap = new ArrayList<Map<String, String>>();
15.  
    for (int i = 0; i < 4; i ) {
16.  
    Map<String, String> lm = new HashMap<String, String>();
17.  
    lm.put("id", i 1 "");
18.  
    lm.put("zijin", i * 10000 "");
19.  
    lm.put("bianma", "A001");
20.  
    lm.put("mingcheng", "设计");
21.  
    lm.put("xiangmumingcheng", "EasyPoi " i "期");
22.  
    lm.put("quancheng", "开源项目");
23.  
    lm.put("sqje", i * 10000 "");
24.  
    lm.put("hdje", i * 10000 "");
25.  
     
26.  
    listMap.add(lm);
27.  
    }
28.  
    map.put("maplist", listMap);
29.  
     
30.  
    Workbook workbook = ExcelExportUtil.exportExcel(params, map);
31.  
     
32.  
    ServletOutputStream stream = streamService.getOutputStream(response, "基于模板导出excel");
33.  
    workbook.write(stream);
34.  
    stream.flush();
35.  
    stream.close();
36.  
    workbook.close();
37.  
    }

测试结果：

3.3，基于自定义表头导出excel

3.3.1，使用代码实现

导出代码：

1.  
   @GetMapping("/exportExcel")
2.  
   public void exportExcel(HttpServletResponse response) throws IOException {
3.  
   List<ExcelExportEntity> colList = new ArrayList<>();
4.  
    
5.  
   //基础信息
6.  
   ExcelExportEntity baseGroup = new ExcelExportEntity("基础信息", "base");
7.  
   List<ExcelExportEntity> baseList = new ArrayList<>();
8.  
   baseList.add(new ExcelExportEntity("序号","id"));
9.  
   baseList.add(new ExcelExportEntity("所属城区","areaName"));
10.  
    baseList.add(new ExcelExportEntity("所属街道","streetName"));
11.  
    baseList.add(new ExcelExportEntity("商铺名称","shopName"));
12.  
    baseGroup.setList(baseList);
13.  
    colList.add(baseGroup);
14.  
     
15.  
     
16.  
    //招牌信息
17.  
    for (int i = 1; i <= 3; i ) {
18.  
    ExcelExportEntity signGroup = new ExcelExportEntity("招牌信息" i, "sign" i);
19.  
    List<ExcelExportEntity> signList = new ArrayList<>();
20.  
    signList.add(new ExcelExportEntity("招牌内容","signContent"));
21.  
    signList.add(new ExcelExportEntity("招牌形式","signFormName"));
22.  
    signList.add(new ExcelExportEntity("安装时间","installTime"));
23.  
    signGroup.setList(signList);
24.  
    colList.add(signGroup);
25.  
    }
26.  
     
27.  
     
28.  
    //封装数据
29.  
     
30.  
    List<Map<String,Object>> list = new ArrayList<>();
31.  
    for (int i = 0; i < 3; i ) {
32.  
    Map<String,Object> dataMap = new HashMap<>();
33.  
     
34.  
    List<Map<String,Object>> baseDataList = new ArrayList<>();
35.  
    Map<String, Object> map = new HashMap<>();
36.  
    map.put("id",i 1);
37.  
    map.put("areaName", "上海" i);
38.  
    map.put("streetName", "闵行" i);
39.  
    map.put("shopName", "五福" i);
40.  
    baseDataList.add(map);
41.  
     
42.  
    dataMap.put("base",baseDataList);
43.  
     
44.  
    //招牌信息
45.  
    for (int j = 0; j < 3; j ) {
46.  
    List<Map<String,Object>> mapList = new ArrayList<>();
47.  
    Map<String,Object> signMap = new HashMap<>();
48.  
    signMap.put("signFormName","自定义" i);
49.  
    signMap.put("installTime","2023-04-02");
50.  
    signMap.put("signContent","无" i);
51.  
    mapList.add(signMap);
52.  
    dataMap.put("sign" (j 1),mapList);
53.  
    }
54.  
     
55.  
    list.add(dataMap);
56.  
     
57.  
    }
58.  
     
59.  
     
60.  
    ExportParams exportParams = new ExportParams(null, "数据信息");
61.  
    exportParams.setType(ExcelType.XSSF);
62.  
     
63.  
    Workbook workbook = ExcelExportUtil.exportExcel(exportParams, colList, list);
64.  
     
65.  
    ServletOutputStream stream = streamService.getOutputStream(response, "自定义表头导出excel");
66.  
    workbook.write(stream);
67.  
    stream.flush();
68.  
    stream.close();
69.  
    workbook.close();
70.  
    }

测试结果：

3.3.2，使用excel模板实现

excel模板：

 导出代码：

1.  
   @GetMapping("/exportExcelForTemplates")
2.  
   public void exportExcelForTemplates(HttpServletResponse response) throws IOException {
3.  
   TemplateExportParams params = new TemplateExportParams(
4.  
   "excel/test.xlsx");
5.  
   params.setColForEach(true);
6.  
   Map<String, Object> map = new HashMap<String, Object>();
7.  
    
8.  
   //横向表头信息
9.  
   List<Map<String, Object>> listMap = new ArrayList<Map<String, Object>>();
10.  
    for (int i = 0; i < 3; i ) {
11.  
    Map<String,Object> headMap = new HashMap<>();
12.  
    headMap.put("sign","店招牌匾" i);
13.  
    headMap.put("signContent","招牌内容");
14.  
    headMap.put("signFormName","招牌形式");
15.  
    headMap.put("installTime","安装时间");
16.  
     
17.  
    headMap.put("content","t.content" i);
18.  
    headMap.put("formName","t.formName" i);
19.  
    headMap.put("time","t.time" i);
20.  
     
21.  
    listMap.add(headMap);
22.  
    }
23.  
    map.put("list",listMap);
24.  
    map.put("map", map);
25.  
     
26.  
     
27.  
    //数据信息
28.  
    List<Map<String,Object>> baseList = new ArrayList<>();
29.  
    for (int i = 0; i < 3; i ) {
30.  
    Map<String,Object> baseMap = new HashMap<>();
31.  
    baseMap.put("id", i);
32.  
    baseMap.put("area", "上海市" i);
33.  
    baseMap.put("street", "闵行区" i);
34.  
    baseMap.put("storeName", "上海松江大学城");
35.  
     
36.  
    baseMap.put("content0","内容0");
37.  
    baseMap.put("formName0","样式0");
38.  
    baseMap.put("time0","时间0");
39.  
     
40.  
    baseMap.put("content1","内容1");
41.  
    baseMap.put("formName1","样式1");
42.  
    baseMap.put("time1","时间1");
43.  
     
44.  
    baseMap.put("content2","内容2");
45.  
    baseMap.put("formName2","样式2");
46.  
    baseMap.put("time2","时间2");
47.  
     
48.  
    baseList.add(baseMap);
49.  
    }
50.  
    map.put("baseList",baseList);
51.  
     
52.  
    Workbook workbook = ExcelExportUtil.exportExcel(params, map);
53.  
     
54.  
    ServletOutputStream stream = streamService.getOutputStream(response, "自定义表头导出excel");
55.  
    workbook.write(stream);
56.  
    stream.flush();
57.  
    stream.close();
58.  
    workbook.close();

测试结果：

这篇好文章是转载于：学新通技术网