Spring Boot 集成 easypoi实现excel的导入导出、excel导入导出含图片
目录
一、Easypoi概述
1.Easypoi
easypoi功能如同名字easy,主打的功能就是容易,让一个没见接触过poi的人员,就可以方便的写出Excel导出,Excel模板导出,Excel导入,Word模板导出
,通过简单的注解和模板语言
(熟悉的表达式语法),完成以前复杂的写法。
2.为什么要使用easypoi?
Easypoi主打的功能就是容易且易上手
。它可以让一个从没见接触过poi的开发人员,就可以很轻松写出Excel导入导出
,而不再是看很多api才可以完成这样的工作。
它通过简单的注解和模板语言(熟悉的表达式语法),完成以前复杂的写法,EasyPoi快速实现excel导入导出功能,且修改导出格式简单粗暴,快速有效。
3.easypoi提供了那些功能?
基于注解的导入导出,修改注解就可以修改Excel。
支持常用的样式自定义。
基于map可以灵活定义的表头字段。
支持一对多的导出,导入。
支持模板的导出,一些常见的标签,自定义标签。
支持HTML/Excel转换,如果模板还不能满足用户的变态需求,请用这个功能
支持word的导出,支持图片,Excel
4.什么场景该用哪个方法
1.
ExcelExportUtil
Excel导出(普通导出,模板导出
)
2.ExcelImportUtil
Excel导入
3.WordExportUtil
Word导出
(只支持docx ,doc版本poi存在图片的bug,暂不支持)
4.1 导出
ExcelExportUtil.exportExcel
(常用)、ExcelExportUtil.exportBigExcel
、ExcelExportUtil.exportExcelClone
、 ExcelXorHtmlUtil.htmlToExcel
、CsvExportUtil.exportCsv
、WordExportUtil.exportWord07
(常用)
(1)正规excel导出 (格式简单,数据量可以,5W以内吧)
注解方式:
ExcelExportUtil.exportExcel
(ExportParams entity,Class<?> pojoClass
,Collection<?> dataSet)
(2)不定多少列,但是格式依然简单数据库不大
自定义方式:
ExcelExportUtil.exportExcel
(ExportParams entity,List<ExcelExportEntity> entityList
,Collection<?> dataSet)
(3)数据量大超过5W,还在100W以内
注解方式:
ExcelExportUtil.exportBigExcel
(ExportParams entity, Class<?> pojoClass,IExcelExportServer server, Object queryParams)
自定义方式:
ExcelExportUtil.exportBigExcel
(ExportParams entity, List excelParams,IExcelExportServer server, Object queryParams)
(4)样式复杂,数据量尽量别大
模板导出:
ExcelExportUtil.exportExcel
(TemplateExportParams params
, Map<String, Object> map)
(5)一次导出多个风格不一致的sheet
模板导出:
ExcelExportUtil.exportExcel
(Map<Integer, Map<String, Object>> map,TemplateExportParams params)
(6)一个模板但是要导出非常多份
模板导出:
ExcelExportUtil.exportExcelClone
(Map<Integer, List<Map<String, Object>>> map,TemplateExportParams params)
(7)模板无法满足你的自定义,试试html
自己构造html,然后转成excel:
ExcelXorHtmlUtil.htmlToExcel
(String html, ExcelType type)
(8)数据量过百万级了.放弃excel吧,csv导出
注解方式:
CsvExportUtil.exportCsv
(CsvExportParams params, Class<?> pojoClass, OutputStream outputStream)
自定义方式:
CsvExportUtil.exportCsv
(CsvExportParams params, List entityList, OutputStream outputStream)
(9)word导出
模板导出:
WordExportUtil.exportWord07
(String url, Map<String, Object> map)
(9)PDF导出
模板导出: TODO
4.2 导入
ExcelImportUtil.importExcel
(常用)、ExcelImportUtil.importExcelMore
(常用)、ExcelImportUtil.importExcelBySax
、 CsvImportUtil.importCsv
如果想提高性能 ImportParams 的concurrentTask 可以帮助并发导入
,仅单行,最小1000。excel有单个的那种特殊读取,readSingleCell 参数可以支持
(1)不需要检验,数据量不大(5W以内)
注解或者MAP:
ExcelImportUtil.importExcel
(File file, Class<?> pojoClass, ImportParams params)
(2)需要导入,数据量不大
注解或者MAP:
ExcelImportUtil.importExcelMore
(InputStream inputstream, Class<?> pojoClass, ImportParams params)
(3)数据量大了,或者你有特别多的导入操作,内存比较少,仅支持单行
SAX方式:
ExcelImportUtil.importExcelBySax
(InputStream inputstream, Class<?> pojoClass, ImportParams params, IReadHandler handler)
(4)数据量超过EXCEL限制,CSV读取
小数据量:
CsvImportUtil.importCsv
(InputStream inputstream, Class<?> pojoClass,CsvImportParams params)
大数据量:
CsvImportUtil.importCsv
(InputStream inputstream, Class<?> pojoClass,CsvImportParams params, IReadHandler readHandler)
5.Easypoi常用注解
5.1 @Excel 注解
@Excel注解是作用到Filed上面,是对Excel一列的一个描述
,这个注解是必须要的注解
,其属性如下:
5.2 @ExcelCollection 注解
@ExcelCollection 注解表示一个集合,主要针对一对多的导出
比如一个老师对应多个科目,科目就可以用集合表示,作用在一个类型是List的属性上面,属性如下:
5.3 @ExcelTarget注解
作用于最外层的对象,描述这个对象的id,以便支持一个对象可以针对不同导出做出不同处理
5.4 @ExcelEntity注解
@ExcelEntity注解表示一个继续深入导出的实体
,是作用一个类型为实体的属性上面
5.5 @ExcelIgnore 注解
@ExcelIgnore 和名字一样表示这个字段被忽略跳过这个导出
二、创建Excel导入导出工具类
package com.example.exceldemo.utils;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import java.util.NoSuchElementException;
/**
* Excel 导入导出工具类
* @author qzz
*/
public class ExcelUtils {
/**
* excel 导出
*
* @param list 数据列表
* @param fileName 导出时的excel名称
* @param response
*/
public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws IOException {
defaultExport(list, fileName, response);
}
/**
* 默认的 excel 导出
*
* @param list 数据列表
* @param fileName 导出时的excel名称
* @param response
*/
private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws IOException {
//把数据添加到excel表格中
Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
downLoadExcel(fileName, response, workbook);
}
/**
* excel 导出
*
* @param list 数据列表
* @param pojoClass pojo类型
* @param fileName 导出时的excel名称
* @param response
* @param exportParams 导出参数(标题、sheet名称、是否创建表头,表格类型)
*/
private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) throws IOException {
//把数据添加到excel表格中
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
downLoadExcel(fileName, response, workbook);
}
/**
* excel 导出
*
* @param list 数据列表
* @param pojoClass pojo类型
* @param fileName 导出时的excel名称
* @param exportParams 导出参数(标题、sheet名称、是否创建表头,表格类型)
* @param response
*/
public static void exportExcel(List<?> list, Class<?> pojoClass, String fileName, ExportParams exportParams, HttpServletResponse response) throws IOException {
defaultExport(list, pojoClass, fileName, response, exportParams);
}
/**
* excel 导出
*
* @param list 数据列表
* @param title 表格内数据标题
* @param sheetName sheet名称
* @param pojoClass pojo类型
* @param fileName 导出时的excel名称
* @param response
*/
public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response) throws IOException {
defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName, ExcelType.XSSF));
}
/**
* excel 导出
*
* @param list 数据列表
* @param title 表格内数据标题
* @param sheetName sheet名称
* @param pojoClass pojo类型
* @param fileName 导出时的excel名称
* @param isCreateHeader 是否创建表头
* @param response
*/
public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, boolean isCreateHeader, HttpServletResponse response) throws IOException {
ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.XSSF);
exportParams.setCreateHeadRows(isCreateHeader);
defaultExport(list, pojoClass, fileName, response, exportParams);
}
/**
* excel下载
*
* @param fileName 下载时的文件名称
* @param response
* @param workbook excel数据
*/
private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws IOException {
try {
response.setCharacterEncoding("UTF-8");
//设置信息头,告诉浏览器内容为excel类型
response.setHeader("content-Type", "application/vnd.ms-excel");
//设置下载名称
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".xlsx", "UTF-8"));
//写入
workbook.write(response.getOutputStream());
} catch (Exception e) {
throw new IOException(e.getMessage());
}
}
/**
* excel 导入
*
* @param file excel文件
* @param pojoClass pojo类型
* @param <T>
* @return
*/
public static <T> List<T> importExcel(MultipartFile file, Class<T> pojoClass) throws IOException {
return importExcel(file, 1, 1, pojoClass);
}
/**
* excel 导入
*
* @param filePath excel文件路径
* @param titleRows 表格内数据标题行
* @param headerRows 表头行
* @param pojoClass pojo类型
* @param <T>
* @return
*/
public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws IOException {
if (StringUtils.isBlank(filePath)) {
return null;
}
ImportParams params = new ImportParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
params.setNeedSave(true);
params.setSaveUrl("/excel/");
try {
return ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
} catch (NoSuchElementException e) {
throw new IOException("模板不能为空");
} catch (Exception e) {
throw new IOException(e.getMessage());
}
}
/**
* excel 导入
*
* @param file 上传的文件
* @param titleRows 表格内数据标题行
* @param headerRows 表头行
* @param pojoClass pojo类型
* @param <T>
* @return
*/
public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws IOException {
if (file == null) {
return null;
}
try {
return importExcel(file.getInputStream(), titleRows, headerRows, pojoClass);
} catch (Exception e) {
throw new IOException(e.getMessage());
}
}
/**
* excel 导入
*
* @param inputStream 文件输入流
* @param titleRows 表格内数据标题行
* @param headerRows 表头行
* @param pojoClass pojo类型
* @param <T>
* @return
*/
public static <T> List<T> importExcel(InputStream inputStream, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws IOException {
if (inputStream == null) {
return null;
}
ImportParams params = new ImportParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
params.setSaveUrl("/excel/");
params.setNeedSave(true);
try {
return ExcelImportUtil.importExcel(inputStream, pojoClass, params);
} catch (NoSuchElementException e) {
throw new IOException("excel文件不能为空");
} catch (Exception e) {
throw new IOException(e.getMessage());
}
}
}
三、Excel导出导入实例
1.Excel导出(一对一数据)
1.1 添加项目依赖配置
在pom.xml文件中,添加Easypoi依赖,具体依赖如下:
<!--easypoi依赖,实现excel文件导入导出、word导出等-->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>4.4.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>4.4.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>4.4.0</version>
</dependency>
上述依赖说明:
easypoi-base:
导入导出的工具包
,可以完成Excel导入导出,Word导出等功能。
easypoi-web:耦合了spring-mvc基于AbstractView,极大的简化spring-mvc下的导出功能。
easypoi-annotation:基础注解包,作用与实体对象上,拆分后方便maven多工程的依赖管理
但随着springboot的越来越流行,不可免俗的easypoi也为我们推出了easypoi-spring-boot-starter,方便我们使用。
所以我们就可以不再需要引入那么多相关的依赖啦,省的pom文件看着太长不好找。
如下就是easypoi的starter,大家自行复制,粘贴到本地的pom.xml中即可:
<!--easypoi依赖,实现excel文件导入导出-->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-spring-boot-starter</artifactId>
<version>4.4.0</version>
</dependency>
1.2 配置application.yml
# 服务端口
server:
port: 8083
# 数据源配置
spring:
datasource:
name: test
url: jdbc:mysql://localhost:3306/db_user?&allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useSSl=false
username: root
password: root
driver-class-name: com.mysql.jdbc.Driver
type: com.alibaba.druid.pool.DruidDataSource
## 配置连接池信息
## 初始化大小,最小,最大
initialSize: 5
minIdle: 5
maxActive: 30
## 配置获取连接等待超时的时间
maxWait: 60000
# 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
timeBetweenEvictionRunsMillis: 60000
# 配置一个连接在池中最小生存的时间,单位是毫秒
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1 FROM DUAL
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
poolPreparedStatements: true
maxPoolPreparedStatementPerConnectionSize: 20
# 配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙
filters: stat,wall
# 通过connectProperties属性来打开mergeSql功能;慢SQL记录
connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
# 超过时间限制是否回收
removeAbandoned: true
# 超时时间;单位为秒。180秒=3分钟
removeAbandonedTimeout: 180
# 关闭abanded连接时输出错误日志
logAbandoned: true
#静态资源路径
mvc:
static-path-pattern: /**
web:
resources:
static-locations: classpath\:/META-INF/resources/,classpath\:/resources/,classpath\:/static/,classpath\:/public/,file\:${
web.upload-path}
##配置打印sql
mybatis:
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
#默认扫描mapper.xml
mapper-locations: classpath:/mapper/*.xml
#mybatis&&通用mapper
mapper:
mappers: com.example.exceldemo.mybatis.MyMapper
identity: MYSQL
upload:
dir: F:\images\company\
1.2 定义一个导出的对象
我们先来定义一个导出vo,目的是可以对每个导出字段进行长宽等样式进行自定义化,比如我就按照数据长度判断,依次给每个字段加上width 属性,定义每个字段的展示长度。
package com.example.exceldemo.excel;
import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.Data;
import java.io.Serializable;
import java.util.Date;
/**
* 学生
* @author qzz
*/
@Data
public class StudentExcel implements Serializable {
private static final long serialVersionUID = 1L;
private Integer id;
/**
* @Excel 作用在一个filed上面,对列的描述
* @param name 列名
* @param orderNum 下标,从0开始。
*/
@Excel(name = "学生姓名", width = 30)
private String name;
/**
* 学生性别 1:男 2:女
*/
@Excel(name = "学生性别", replace = {
"男_1","女_0"},suffix = "生")
private Integer sex;
/**
* 出生日期
* 如果数据库是string类型,这个需要设置这个数据库时间格式(databaseFormat) format:输出时间格式
*/
@Excel(name = "出生日期", databaseFormat = "yyyyMMddHHmmss",format = "yyyy-MM-dd",width = 20)
private Date birthday;
/**
* 进校日期
* 字段是Date类型则不需要设置databaseFormat
*/
@Excel(name = "出生日期", format = "yyyy-MM-dd",width = 20)
private Date registrationDate;
private Date createTime;
private String createUser;
private Date updateTime;
private String updateUser;
}
说明:
@Excel 作用在一个filed上面,对列的描述。name:列名。orderNum:下标,从0开始。
没有被注解@Excel标注的字段,不会导出。
学生姓名定义了我们的列的行高
学生性别因为我们基本上都是存在数据库都是数字所以我们转换下。
两个日期进行了格式化输出
@Excel(databaseFormat="xxxx"):如果数据库字段是string类型,则需要添加该属性,并加上该时间格式,比如:databaseFormat = "yyyyMMdd"。
@Excel(replace={“xxx_0”, “xxx_1”,“xxx_2”},addressList = true
):表示单元格下拉框展示,_0、_1表示下拉值的前后顺序,从0往后排。要实现字段下拉,addressList属性必不可少
。
@Excel(suffix="xxx"):表示自动添加该xxx为你字段文字的后缀
@Excel(imageType=“xxx”):表示导出类型,imageType=1:从file读取;imageType=2:从数据库中读取;默认是文件,同样导入也是一样的。
1.3 在控制器中添加一个导出类
StudentController:
/**
* 导出
* @param name
*/
@RequestMapping("/exportExcel")
public void exportExcel(@RequestParam(name = "name",required = false) String name,HttpServletResponse response){
try {
StudentRequestJson requestJson = new StudentRequestJson();
if(StringUtils.isNotEmpty(name)){
requestJson.setName(name);
}
//1.从数据库查询到数据
List<Student> list =studentService.getStudentList(requestJson);
//2.导出时的excel名称
String dateString = String.valueOf(System.currentTimeMillis());
String fileName = "学生信息表" + dateString;
//3.查看结果集转成导出vo(转成对应的类型;要不然会报错)