Java，导出Excel表格文件

引用三方封装的技术框架

Alibaba/EasyExcel

JAVA 解析Excel工具EasyExcel

Hutool

Hutool是一个小而全的Java工具类库，通过静态方法封装，降低相关API的学习成本，提高工作效率，使Java拥有函数式语言般的优雅，让Java语言也可以“甜甜的”

EasyPOI

让一个不懂导入导出的快速使用poi完成Excel和word的各种操作

代码Demo

引入jar包

<!--springboot-easyPoi-->
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-spring-boot-starter</artifactId>
    <version>4.0.0</version>
</dependency>

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

<!--hutool-->
<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-all</artifactId>
    <version>5.8.0.M3</version>
</dependency>

但是导入 springboot-easyPoi 的依赖后，启动报错了

The bean ‘beanNameViewResolver’, defined in class path resource [cn/afterturn/easypoi/configuration/EasyPoiAutoConfiguration.class], could not be registered. A bean with that name has already been defined in class path resource [org/springframework/boot/autoconfigure/web/servlet/error/ErrorMvcAutoConfiguration$WhitelabelErrorViewConfiguration.class] and overriding is disabled.
Consider renaming one of the beans or enabling overriding by setting spring.main.allow-bean-definition-overriding=true

已在类路径资源[org/springframework/boot/autoconfigure/web/servlet/error/ErrorMvcAutoConfiguration$WhitelabelErrorViewConfiguration.class]中定义了具有该名称的bean，并且已禁用重写。

解决方法
配置 yml

spring: 
  main:
  	# 相同名字的 bean允许覆盖注册
    allow-bean-definition-overriding: true

代码Demo

package com.geekbang.source_spring.domain;

import cn.afterturn.easypoi.excel.annotation.Excel;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;

import java.math.BigDecimal;
import java.util.Date;

@Data
public class TProject {
    
    //@Excel(name = "id")
    private String id;
    //项目编号
    @Excel(name = "projectCode")
    private String projectCode;
    //项目名称
    @Excel(name = "projectName")
    private String projectName;
    //批复金额
    @Excel(name = "money")
    private BigDecimal money;
    //规模
    @Excel(name = "scale")
    private Double scale;
    //部门
    @Excel(name = "department")
    private String department;
    //开始时间
    @Excel(name = "beginTime", format = "yyyy-MM-dd")
    //@JsonFormat注解; pattern:指定时间格式, timezone:设置时区
    @JsonFormat(pattern = "yyyy-MM-dd", timezone = "GMT-8")
    private Date beginTime;
    //结束时间
    @JsonFormat(pattern = "yyyy-MM-dd", timezone = "GMT-8")
    @Excel(name = "endTime", format = "yyyy-MM-dd")
    private Date endTime;
    //建设部门
    @Excel(name = "buildDepartment")
    private String buildDepartment;
    //城市
    @Excel(name = "city")
    private String city;
    //级别
    @Excel(name = "level")
    private Integer level;
}

这里给实体类的属性添加了 @Excel 注解,注解属性 name分别是属性名,(或者是属性含义,自己随意定义)；只要是带有 @Excel 注解的属性，都会对应的导出到 Excel表格文件中,没有带 @Excel注解的属性值,是不会导出到 Excel文件表格中的。每列的表头的名称就是@Excel 注解中name属性的值。

ContentType枚举

package com.geekbang.source_spring.enums;

public enum ContentTypeEnum {

    BMP("image/bmp"),
    GIF("image/gif"),
    JPEG("image/jpeg"),
    JPG("image/jpeg"),
    PNG("image/png"),
    HTML("text/html"),
    TXT("text/plain"),
    XML("text/xml"),
    VSD("application/vnd.visio"),
    PPT("application/vnd.ms-powerpoint"),
    PPTX("application/vnd.ms-powerpoint"),
    DOC("application/msword"),
    DOCX("application/msword"),
    XLS("application/msexcel"),
    XLSX("application/msexcel"),
    CSV("application/csv");

    private String contentType;

    ContentTypeEnum(String contentType) {
        this.contentType = contentType;
    }

    public String getContentType() {
        return contentType;
    }
}

第一种：使用 EasyPOI 完成的生成Excel表格文件

private static int num = 100;

/**
 * 将 Date 时间类转为指定格式的字符串
 *
 * @param date 时间类
 * @param type 1:年-月-日 时:分:秒; 2:年-月-日 时:分; 3:年-月-日; 4:年月日
 * @return String
 */
public static String dateToStr(Date date, int type) {
    SimpleDateFormat format;
    if (type == 0) {
        format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    } else if (type == 1) {
        format = new SimpleDateFormat("yyyy-MM-dd HH:mm");
    } else if (type == 3) {
        format = new SimpleDateFormat("yyyy-MM-dd");
    } else {
        format = new SimpleDateFormat("yyyyMMdd");
    }
    return format.format(date);
}

/**
 * 查询数据库数据导出为 excel(直接导出)
 *
 * @param objects  对象集合
 * @param response HttpServletResponse
 */
public static void exportExcel(List<?> objects, HttpServletResponse response) {
    //将 Date 时间类转为指定格式的字符串
    String str = DateUtils.dateToStr(new Date(), 4);
    str = str + num;
    Object object = objects.get(0);
    Class<?> clazz = object.getClass();
    //参数一:表头标题名称; 参数二:sheet页名称
    ExportParams exportParams = new ExportParams(clazz.getSimpleName(), clazz.getSimpleName());
    //是否创建表头
    exportParams.setCreateHeadRows(true);
    Workbook workbook = ExcelExportUtil.exportExcel(exportParams, clazz, objects);
    OutputStream outputStream = null;
    try {
        response.setContentType(ContentTypeEnum.XLS + ";charset=utf-8");
        response.setHeader("Content-disposition", "inline; filename = " + str + clazz.getSimpleName() + ".xls");
        //直接导出
        outputStream = response.getOutputStream();
        workbook.write(outputStream);
    } catch (IOException e) {
        e.printStackTrace();
    } finally {
        if (outputStream != null) {
            try {
                outputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
    num = num + 1;
}

/**
 * 查询数据库数据导出 excel到指定目录下
 *
 * @param objects  对象集合
 * @param url      导出目录
 * @param response HttpServletResponse
 */
public static void exportExcelPath(List<?> objects, String url, HttpServletResponse response) {
    String str = DateUtils.dateToStr(new Date(), 4);
    str = str + num;
    Object object = objects.get(0);
    Class<?> clazz = object.getClass();
    ExportParams exportParams = new ExportParams(clazz.getSimpleName(), clazz.getSimpleName());
    //不创建表头
    exportParams.setCreateHeadRows(true);
    Workbook workbook = ExcelExportUtil.exportExcel(exportParams, clazz, objects);
    FileOutputStream fileOutputStream = null;
    try {
        response.setContentType(ContentTypeEnum.XLS + ";charset=utf-8");
        response.setHeader("Content-disposition", "inline; filename = " + str + clazz.getSimpleName() + ".xls");
        //导出到指定目录下
        fileOutputStream = new FileOutputStream(url);
        workbook.write(fileOutputStream);
    } catch (IOException e) {
        e.printStackTrace();
    } finally {
        if (fileOutputStream != null) {
            try {
                fileOutputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
}

@GetMapping("/excelOne")
public void getExcelOne(@RequestParam("id") String id, HttpServletResponse response) {
    TProject project = projectService.findOne(id);
    List<TProject> projects = new ArrayList<>();
    projects.add(project);
    ExcelUtils.exportExcel(projects, response);
}

---

第二种：使用 Alibaba/EasyExcel；生成Excel表格文件

package com.geekbang.source_spring.domain;

import cn.afterturn.easypoi.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

import java.util.Date;

@Data
public class TExamined {

    @ExcelProperty(value = "id", index = 0)
    private String id;
    @ExcelProperty(value = "projectId", index = 1)
    private String projectId;
    @ExcelProperty(value = "projectCode", index = 2)
    private String projectCode;
    @ExcelProperty(value = "step", index = 3)
    private Integer step;
    @ExcelProperty(value = "handlerName", index = 4)
    private String handlerName;
    @ExcelProperty(value = "handlerId", index = 5)
    private Integer handlerId;
    @ExcelProperty(value = "telephone", index = 6)
    private String telephone;
    @ExcelProperty(value = "HandlerTime", index = 7)
    private Date HandlerTime;
    @ExcelProperty(value = "opinions", index = 8)
    private String opinions;
    @ExcelProperty(value = "createTime", index = 9)
    private Date createTime;
    @ExcelIgnore
    private Date updateTime;
}

AlibabaEasyExcel;实体类属性上加与不加 @ExcelProperty注解，都会参与读写导出,如果想要不参与读写,需要在属性值上添加 @ExcelIgnore注解。@ExcelProperty 注解的name属性值为列标题, index属性值为列的标号。

/**
 * 读取数据写入 excel,导出到指定路径
 *
 * @param list     对象数据集合
 * @param url      导出路径
 * @param fileName 文件名称
 * @param object   对象
 */
public static void createExcel(List<?> list, String url, String fileName, Object object) {
    String str = DateUtils.dateToStr(new Date(), 4);
    String file = url + str + fileName + ".xls";
    Class<?> objectClass = object.getClass();
    EasyExcel.write(file, objectClass).sheet(fileName).doWrite(list);
}

@GetMapping("/down")
public void createExcel() {
    List<TExamined> list = service.findAll();
    String url = "D:\\Desktop\\";
    ExcelUtils.createExcel(list, url, "TExamined", new TExamined());
}