一、背景
最近做了一个excel导出功能,最开始查阅资料,EasyPoi以及阿里出的EasyExcel都是不错的工具,二者对比,EasyExcel导出大数据量的效率优于EasyPoi,但是前者要比后者更消耗内存。(这个对比后续我会单独做总结,这篇文章主讲使用EasyPoi的导出excel的使用)
二、导出步骤
(1)maven
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>4.1.0</version>
</dependency>
(2)定义实体
/**
* @ClassName: MsgClient
* @Description:
* @Author: WM
* @Date: 2021-07-24 19:09
**/
@Data
@ExcelTarget("msgClient")
public class MsgClient {
@Excel(name = "客户姓名")
private String clientName;
@Excel(name = "客户电话号码")
private String clientPhone;
@Excel(name = "创建人")
private String createBy;
@Excel(name = "ID")
private String id;
@Excel(name = "标记")
private String remark;
@Excel(name = "生日")
private Date birthday;
}
(3)定义导出的excel的样式
/**
* @ClassName: ExcelStyleUtil
* @Description: Excel样式设置
* @Author: WM
* @Date: 2021-07-24 18:23
**/
public class ExcelStyleUtil implements IExcelExportStyler {
private static final short STRING_FORMAT = (short) BuiltinFormats.getBuiltinFormat("TEXT");
private static final short FONT_SIZE_TEN = 10;
private static final short FONT_SIZE_ELEVEN = 11;
private static final short FONT_SIZE_TWELVE = 12;
// 标题头样式
private CellStyle headerStyle;
// 数据列标题样式
private CellStyle titleStyle;
// 数据行样式
private CellStyle styles;
public ExcelStyleUtil(Workbook workbook) {
this.init(workbook);
}
/**
* 初始化样式
*
* @param workbook
*/
private void init(Workbook workbook) {
this.headerStyle = initHeaderStyle(workbook);
this.titleStyle = initTitleStyle(workbook);
this.styles = initStyles(workbook);
}
/**
* 标题头样式
*
* @param color
* @return
*/
@Override
public CellStyle getHeaderStyle(short color) {
return headerStyle;
}
/**
* 数据列标题样式
*
* @param color
* @return
*/
@Override
public CellStyle getTitleStyle(short color) {
return titleStyle;
}
/**
* 数据行样式
*
* @param parity 表示奇偶行
* @param excelExportEntity 数据内容
* @return
*/
@Override
public CellStyle getStyles(boolean parity, ExcelExportEntity excelExportEntity) {
return styles;
}
/**
* 数据行样式
*
* @param cell 单元格
* @param dataRow 数据行
* @param excelExportEntity 数据内容
* @param obj 对象
* @param data 数据
* @return
*/
@Override
public CellStyle getStyles(Cell cell, int dataRow, ExcelExportEntity excelExportEntity, Object obj, Object data) {
return getStyles(true, excelExportEntity);
}
/**
* 模板使用的样式设置
*
* @param b
* @param excelForEachParams
* @return
*/
@Override
public CellStyle getTemplateStyles(boolean b, ExcelForEachParams excelForEachParams) {
return null;
}
/**
* 初始化--标题头样式
*
* @param workbook
* @return
*/
private CellStyle initHeaderStyle(Workbook workbook) {
CellStyle style = getBaseCellStyle(workbook);
style.setFont(getFont(workbook, FONT_SIZE_TWELVE, true));
return style;
}
/**
* 初始化--数据列标题样式
*
* @param workbook
* @return
*/
private CellStyle initTitleStyle(Workbook workbook) {
CellStyle style = getBaseCellStyle(workbook);
style.setFont(getFont(workbook, FONT_SIZE_ELEVEN, false));
// 背景色
style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
return style;
}
/**
* 初始化--数据行样式
*
* @param workbook
* @return
*/
private CellStyle initStyles(Workbook workbook) {
CellStyle style = getBaseCellStyle(workbook);
style.setFont(getFont(workbook, FONT_SIZE_TEN, false));
style.setDataFormat(STRING_FORMAT);
return style;
}
/**
* 基础样式
*
* @return
*/
private CellStyle getBaseCellStyle(Workbook workbook) {
CellStyle style = workbook.createCellStyle();
// 下边框
style.setBorderBottom(BorderStyle.THIN);
// 左边框
style.setBorderLeft(BorderStyle.THIN);
// 上边框
style.setBorderTop(BorderStyle.THIN);
// 右边框
style.setBorderRight(BorderStyle.THIN);
// 水平居中
style.setAlignment(HorizontalAlignment.CENTER);
// 上下居中
style.setVerticalAlignment(VerticalAlignment.CENTER);
// 设置自动换行
style.setWrapText(true);
return style;
}
/**
* 字体样式
*
* @param size 字体大小
* @param isBold 是否加粗
* @return
*/
private Font getFont(Workbook workbook, short size, boolean isBold) {
Font font = workbook.createFont();
// 字体样式
font.setFontName("宋体");
// 是否加粗
font.setBold(isBold);
// 字体大小
font.setFontHeightInPoints(size);
return font;
}
}
(4)定义Excel导出工具类
/**
* @ClassName: ExcelExportUtil
* @Description: Excel导出工具类
* @Author: WM
* @Date: 2021-07-24 18:47
**/
@Slf4j
@Component
public class MyExcelExportUtil {
@Resource
private IExcelExportServer excelExportServer;
/**
* 小量数据允许导出的最大条数
*/
private static final Integer EXPORT_EXCEL_BASE_MAX_NUM = 100000;
/**
* 获取导出的 Workbook对象
* 普通导出
*
* @param title 大标题
* @param sheetName 页签名
* @param object 导出实体
* @param list 普通导出传入的数据集合
* @param list 数据集合
* @return Workbook
*/
public static Workbook getWorkbook(String title, String sheetName, Class<?> object, List<?> list, ExcelType excelType) {
// 判断导出数据是否为空
if (list == null) {
list = new ArrayList<>();
}
// 判断导出数据数量是否超过限定值
if (list.size() > EXPORT_EXCEL_BASE_MAX_NUM) {
title = "导出数据行数超过:" + EXPORT_EXCEL_BASE_MAX_NUM + "条,无法导出!";
list = new ArrayList<>();
}
// 获取导出参数
ExportParams exportParams = new ExportParams(title, sheetName, excelType);
// 设置导出样式
exportParams.setStyle(ExcelStyleUtil.class);
// 设置行高
exportParams.setHeight((short) 8);
// 普通导出,输出Workbook流
return ExcelExportUtil.exportExcel(exportParams, object, list);
}
/**
* 导出大数据量
*
* @param title
* @param sheetName
* @param object
* @param queryParams 大数据量导出,查询数据的参数条件
* @param excelType
* @return
*/
public Workbook getWorkbook(String title, String sheetName, Class<?> object, Object queryParams, ExcelType excelType) {
// 获取导出参数
ExportParams exportParams = new ExportParams(title, sheetName, excelType);
// 设置导出样式
exportParams.setStyle(ExcelStyleUtil.class);
// 设置行高
exportParams.setHeight((short) 6);
// 导出大数据量,输出Workbook流
return ExcelExportUtil.exportBigExcel(exportParams, object, excelExportServer, queryParams);
}
/**
* 获取导出的 Workbook对象
*
* @param path 模板路径
* @param map 导出内容map
* @return Workbook
*/
public static Workbook getWorkbook(String path, Map<String, Object> map) {
// 获取导出模板
TemplateExportParams params = new TemplateExportParams(path);
// 设置导出样式
params.setStyle(ExcelStyleUtil.class);
// 输出Workbook流
return ExcelExportUtil.exportExcel(params, map);
}
/**
* 导出Excel
*
* @param workbook workbook流
* @param fileName 文件名
* @param response 响应
*/
public static void exportExcel(Workbook workbook, String fileName, HttpServletResponse response) {
// 输出文件
try (OutputStream out = response.getOutputStream()) {
// 获取文件名并转码
String name = URLEncoder.encode(fileName, "UTF-8");
// 编码
response.setCharacterEncoding("UTF-8");
// 设置强制下载不打开
response.setContentType("application/force-download");
// 下载文件的默认名称
response.setHeader("Content-Disposition", "attachment;filename=" + name + ".xlsx");
// 输出表格
workbook.write(out);
} catch (IOException e) {
log.error("文件导出异常,详情如下:", e);
throw new RuntimeException("文件导出异常");
} finally {
try {
if (workbook != null) {
// 关闭输出流
workbook.close();
}
} catch (IOException e) {
log.error("文件导出异常,详情如下:", e);
}
}
}
}
(5)Controller
普通导出测试:
EasyPoi自带了小数据量导出的方法,当小于10w条数使用XSSFWorkbook,但是其在创建Excel、或者说写Excel的时候内存开销是很大的。当超过10w条数据使用SXSSFWorkbook。SXSSFWorkbook是apache的POI项目推出了一个实现大数据量的流式版本XSSFWorkbook,对于编写非常大的文件时不会耗尽内存,因为在任何时候只有行的可配置部分保存在内存中,例如合并区域,注释等。
查看ExcelExportUtil.exportExcel底层的代码发现:
/**
* @ClassName:ExcelExportController
* @Description:
* @Author:Deamer
* @Date:2020/6/15 22:42
**/
@RestController
public class ExcelExportController {
@Resource
private MyExcelExportUtil myExcelExportUtil;
@Resource
private IExcelExportServer exportBigExcel;
//普通导出
@GetMapping(value = "/export")
public void test(HttpServletResponse response) {
List<Object> list = new ArrayList<>();
for (int i = 0; i < 100000; i++) { //模拟导出的数据
MsgClient client = new MsgClient();
client.setBirthday(new Date());
client.setClientName("小明xxxsxsxsxsxsxsxsxsxsx" + i);
client.setClientPhone("18797" + i);
client.setCreateBy("JueYue");
client.setId("1" + i);
client.setRemark("测试" + i);
list.add(client);
}
try {
Date start = new Date();
Workbook workbook = myExcelExportUtil.getWorkbook("计算机一班学生", "学生", MsgClient.class, list, ExcelType.XSSF);
MyExcelExportUtil.exportExcel(workbook, String.valueOf(System.currentTimeMillis()), response);
System.out.println("export:" + (new Date().getTime() - start.getTime()));
} catch (Exception e) {
e.printStackTrace();
}
}
}
大数据量分批导出:
数据量较大的情况,一次性从数据库查这么多数据会给数据库带来很大的压力,此时可以使用数据分页导出的方法来减轻对数据库操作的压力。
//大数据量分页导出
@GetMapping(value = "/bigDataExport")
public void bigDataExport(HttpServletResponse response) throws IOException {
Date start = new Date();
ExportParams params = new ExportParams("大数据测试", "测试");
Workbook workbook = ExcelExportUtil.exportBigExcel(params, MsgClient.class, exportBigExcel, new Object());
MyExcelExportUtil.exportExcel(workbook, String.valueOf(System.currentTimeMillis()), response);
System.out.println("bigDataExport:" + (new Date().getTime() - start.getTime()));
}
IExcelExportServer的实现类:
查询50w条数据:
/**
* @ClassName: IExcelExportServerImpl
* @Description: 查询指定页码的数据
* @Author: WM
* @Date: 2021-07-24 19:23
**/
@Service
public class IExcelExportServerImpl implements IExcelExportServer {
@Override
public List<Object> selectListForExcelExport(Object obj, int page) {
// 在数据库中查询出数据集合
// 此处可以写dao层分页查询的实现方法,page为当前第几页,分批次循环查询导入数据至excel中
// obj:查询条件,page:当前第几页,pageSize:每页条数
// List<Test> testList = dao.xxx(obj,page,pageSize);
int pageSize = 10000;// 每页1w条数据
List<Object> list = new ArrayList<>();
for (int i = 0; i < 500000; i++) { //分批导出模拟测试数据,50w条
MsgClient client = new MsgClient();
client.setBirthday(new Date());
client.setClientName("小明" + i);
client.setClientPhone("18797" + i);
client.setCreateBy("JueYue");
client.setId("1" + i);
client.setRemark("测试" + i);
list.add(client);
}
List partList = new ArrayList();
if (page * pageSize <= 500000) {
partList = list.subList((page - 1) * pageSize, page * pageSize);
}
return partList;
}
}
说明:这里为什么要通过一个实现类去查询数据呢?因为要做分页,循环查出每页的数据,每次查询都要调用实现类中的查询方法。
进源码看一下:
public Workbook exportBigExcel(IExcelExportServer server, Object queryParams) {
int page = 1;
int var6 = page + 1;
for(List list = server.selectListForExcelExport(queryParams, page); list != null && list.size() > 0; list = server.selectListForExcelExport(queryParams, var6++)) {
this.appendData(list);
}
return this.closeExportBigExcel();
}
(6)测试
普通导出测试:
启动项目,浏览器输入:http://localhost:8080/export
导出结果:
excel内容展示:
大数据量分页导出测试:
启动项目,浏览器输入:http://localhost:8080/bigDataExport
导出结果:
从0计数的所以是50w条数据。
第一个“测试”sheet不知道为啥是9w条数据,我看底层代码限制的是10w条数据(见下图)有知道的小伙伴麻烦留个言哈。
三、样式整改-表格宽度调节
更改某列表格的宽度可以使用 @Excel(width = 100),但是如果想总体修改表格的宽度,需要自己改动源码了。通过查看底层源码,表格的宽度被写死了,宽度自适应的属性也不能设置了,于是乎,只能自己改源码去调节表格宽度了,如果想改成自适应的那种,改动太多了,不建议这么做了。
整改如下:
公共类MyExcelExportUtil中的修改:
自定义createExcel方法:
/**
* 生成Workbook
* @param entity
* @param pojoClass
* @param dataSet
* @return
*/
public static Workbook createExcel(ExportParams entity, Class<?> pojoClass, Collection<?> dataSet) {
Workbook workbook = getWorkbook(entity.getType(), dataSet.size());
(new MyExcelExportService()).createSheet(workbook, entity, pojoClass, dataSet);
return workbook;
}
private static Workbook getWorkbook(ExcelType type, int size) {
if (ExcelType.HSSF.equals(type)) {
return new HSSFWorkbook();
} else {
return size < USE_SXSSF_LIMIT ? new XSSFWorkbook() : new SXSSFWorkbook();
}
}
创建MyExcelExportService文件:
/**
* @ClassName: MyExcelExportService
* @Description:
* @Author: WM
* @Date: 2021-07-30 16:54
**/
@Slf4j
public class MyExcelExportService {
private static double EXCEL_WIDTH = 25.0D;// 自定义表格宽度
public void createSheet(Workbook workbook, ExportParams entity, Class<?> pojoClass, Collection<?> dataSet) {
if (log.isDebugEnabled()) {
log.debug("Excel export start ,class is {}", pojoClass);
log.debug("Excel version is {}", entity.getType().equals(ExcelType.HSSF) ? "03" : "07");
}
if (workbook != null && entity != null && pojoClass != null && dataSet != null) {
try {
List<ExcelExportEntity> excelParams = new ArrayList();
Field[] fileds = PoiPublicUtil.getClassFields(pojoClass);
ExcelTarget etarget = pojoClass.getAnnotation(ExcelTarget.class);
String targetId = etarget == null ? null : etarget.value();
ExcelExportService excelExportService = new ExcelExportService();
excelExportService.getAllExcelField(entity.getExclusions(), targetId, fileds, excelParams, pojoClass, null, (ExcelEntity) null);
excelParams.stream().forEach(x -> x.setWidth(EXCEL_WIDTH));
excelExportService.createSheetForMap(workbook, entity, excelParams, dataSet);
} catch (Exception var9) {
log.error(var9.getMessage(), var9);
throw new ExcelExportException(ExcelExportEnum.EXPORT_ERROR, var9.getCause());
}
} else {
throw new ExcelExportException(ExcelExportEnum.PARAMETER_ERROR);
}
}
}
效果图:
四、总结:
本篇文章主要讲了通过easypoi如何导出excel的实现以及修改表格的宽度,感兴趣的小伙伴可以看看它的源码的实现,这样理解起来更加深刻。
需要更多关于easypoi功能的可以查看官方文档:
easypoi官方文档:http://easypoi.mydoc.io
百万级数据导出到excel我会在下一篇进行讲解。
可能有说的不准确的地方,欢迎指正。