引用三方封装的技术框架
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());
}
