使用EasyExcel实现通用导出功能
一、环境介绍
- JDK 1.8+
- EasyExcel 2.2.7
二、功能实现
此功能可以实现根据传入自定义的 导出实体类或Map 进行excel文件导出。若根据Map导出，导出列的顺序可以自定义。
话不多说,直接看代码
导出实体类
点击查看代码
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.write.style.*;
import com.*.core.tool.utils.DateUtil;
import lombok.Data;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import java.time.LocalDateTime;
/**
* excel导出对象实体类
*
* @author 热得快炸了
* @since 2023-4-3
*/
@Data
@HeadStyle(
borderBottom = BorderStyle.THIN,
borderLeft = BorderStyle.THIN,
borderRight = BorderStyle.THIN,
borderTop = BorderStyle.THIN
)
@ContentStyle(
borderBottom = BorderStyle.THIN,
borderLeft = BorderStyle.THIN,
borderRight = BorderStyle.THIN,
borderTop = BorderStyle.THIN,
wrapped = true,
horizontalAlignment = HorizontalAlignment.LEFT
)
@HeadFontStyle(fontHeightInPoints = (short) 16)
@ContentFontStyle(fontHeightInPoints = (short) 14)
public class ExportDataDTO {
private static final long serialVersionUID = 1L;
/**
* 序号
*/
@ColumnWidth(8)
@ExcelProperty({"文件登记簿", "序号"})
private Integer rowNum;
/**
* 标题
*/
@ColumnWidth(50)
@ExcelProperty({"文件登记簿", "姓名"})
private String name;
/**
* 业务类型
*/
@ColumnWidth(20)
@ExcelProperty({"文件登记簿", "年龄"})
private String age;
/**
* 业务类型
*/
@ColumnWidth(18)
@ExcelProperty({"文件登记簿", "性别"})
private String gender;
}
导出工具类
点击查看代码
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.converters.integer.IntegerNumberConverter;
import com.alibaba.excel.read.builder.ExcelReaderBuilder;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.util.DateUtils;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.handler.WriteHandler;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.*.core.excel.converter.BaseDateConverter;
import com.*.core.excel.listener.DataListener;
import com.*.core.excel.listener.ImportListener;
import com.*.core.excel.strategy.AdjustColumnWidthToFitStrategy;
import com.*.core.excel.support.ExcelException;
import com.*.core.excel.support.ExcelImporter;
import com.*.core.mp.support.Query;
import com.*.core.tool.utils.*;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.codec.Charsets;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;
import javax.annotation.Nullable;
import javax.servlet.http.HttpServletResponse;
import javax.validation.constraints.NotNull;
import java.io.*;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.*;
import java.util.function.BiFunction;
/**
* Excel工具类
*
* @author Chill
* @apiNote https://www.yuque.com/easyexcel/doc/easyexcel
*/
@Slf4j
public class ExcelUtil {
/**
* 导出excel
*
* @param response 响应类
* @param fileName 文件名
* @param sheetName sheet名
* @param dataList 数据列表
* @param clazz class类
* @param <T> 泛型
*/
@SneakyThrows
public static <T> void export(HttpServletResponse response, String fileName, String sheetName, List<T> dataList, Class<T> clazz) {
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding(Charsets.UTF_8.name());
fileName = URLEncoder.encode(fileName, Charsets.UTF_8.name());
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
EasyExcel.write(response.getOutputStream(), clazz).sheet(sheetName).doWrite(dataList);
}
/**
* 根据分页查询导出excel,根据exportClazz类导出
*
* @param dto DTO(分页查询对象)
* @param resp 响应对象
* @param exportClazz 需要导出的类
* @param fileName 文件名
* @param pageDataFunc 分页查询方法(须将字典值转为中文,可调用wrapper方法)
* @param strategyList 写入策略集合
* @param <D> DTO类
* @param <V> VO类
* @param <E> 导出类
*/
public static <D, V, E> void export(@NotNull D dto,
@NotNull HttpServletResponse resp,
@NotNull Class<E> exportClazz,
@Nullable String fileName,
@NotNull BiFunction<D, Query, ? extends IPage<V>> pageDataFunc,
@Nullable List<? extends WriteHandler> strategyList) {
log.info("==================开始导出excel==================");
fileName = fileName + ".xlsx";
String filePath = FileUtil.getTempDirPath() + fileName;
InputStream in = null;
OutputStream outp = null;
File file = new File(filePath);
try {
if (!file.getParentFile().exists()) {
file.getParentFile().mkdirs();
}
if (!file.exists()) {
file.createNewFile();
}
// 构造表格样式
List<WriteHandler> strategies = new ArrayList<>();
if (ObjectUtil.isNotEmpty(strategyList)) {
strategies.addAll(strategyList);
} else {
// 默认导出样式
strategies.addAll(getDefaultStrategy());
}
List<List<String>> content = new ArrayList<>();
// 构建excel写入对象
ExcelWriterBuilder writerBuilder = EasyExcel.write(file, exportClazz);
// 注册写入策略
strategies.forEach(writerBuilder::registerWriteHandler);
// 注册对象转换器
writerBuilder.registerConverter(new BaseDateConverter.LocalDateTimeConverter());
writerBuilder.registerConverter(new BaseDateConverter.LocalDateConverter());
writerBuilder.registerConverter(new BaseDateConverter.LocalTimeConverter());
writerBuilder.registerConverter(new IntegerNumberConverter());
ExcelWriter excelWriter = writerBuilder.build();
// 这里注意 如果同一个sheet只要创建一次
WriteSheet writeSheet = EasyExcel.writerSheet(DateUtil.format(DateUtil.now(), DateUtil.PATTERN_DATETIME_MINI)).build();
// 分页查询数据
Query query = new Query();
query.setSize(500); //mybatis-plus最大分页500条
query.setCurrent(0);
IPage<V> dataPage = pageDataFunc.apply(dto, query);
long total = dataPage.getTotal();
if (total > 50000) {
throw new ExcelException("数据量过大,请按条件筛选导出");
} else if (total <= 0) {
throw new ExcelException("没有可以导出的数据");
}
long totalPage = (long) (Math.ceil(((double) total / dataPage.getSize())));
for (int i = 1; i <= totalPage; i++) {
List<E> exportList = new ArrayList<>();
query.setCurrent(i);
dataPage = pageDataFunc.apply(dto, query);
List<V> dataList = new ArrayList<>(dataPage.getRecords());
exportList = BeanUtil.copyProperties(dataList, exportClazz);
for (int j = 0; j < exportList.size(); j++) {
E e = exportList.get(j);
List<Field> fields = getField(e);
Optional<Field> rowNumField = fields.stream().filter(field -> field.getName().equalsIgnoreCase("rowNum")).findFirst();
int rowNum = query.getSize() * (i - 1) + (j + 1);
rowNumField.ifPresent(field -> {
field.setAccessible(true);
try {
field.set(e, rowNum);
} catch (IllegalAccessException illegalAccessException) {
illegalAccessException.printStackTrace();
}
});
}
excelWriter.write(exportList, writeSheet);
}
// 千万别忘记finish 会帮忙关闭流
excelWriter.finish();
in = new FileInputStream(filePath);
outp = resp.getOutputStream();
//设置请求以及响应的内容类型以及编码方式
resp.setContentType("application/vnd.ms-excel;charset=UTF-8");
resp.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
outp = resp.getOutputStream();
//获取文件输入流
byte[] b = new byte[1024];
int i = 0;
//将缓冲区的数据输出到客户浏览器
while ((i = in.read(b)) > 0) {
outp.write(b, 0, i);
}
outp.flush();
log.info("============导出成功辣!!!!!!!!===========");
} catch (IOException e) {
e.printStackTrace();
log.error("============导出失败===========,异常信息:{}", e.getMessage());
} finally {
IoUtil.closeQuietly(in);
IoUtil.closeQuietly(outp);
FileUtil.deleteQuietly(file);
}
}
/**
* 根据分页查询导出excel,导出列的顺序由<code>exportFields</code>的顺序决定
*
* @param dto DTO(分页查询对象)
* @param resp 响应对象
* @param exportFields 需要导出的字段列表(有序map)
* @param fileName 文件名
* @param columnWidth 自定义列宽map,key为列下标,value为宽度,单位:1000=1cm
* @param pageDataFunc 分页查询方法(须将字典值转为中文,可调用wrapper方法)
* @param strategyList 写入策略集合
* @param <D> DTO泛型
* @param <V> VO泛型
*/
public static <D, V> void export(@NotNull D dto,
@NotNull HttpServletResponse resp,
@NotNull LinkedHashMap<String, String> exportFields,
@Nullable String fileName,
@NotNull Map<Integer, Integer> columnWidth,
@NotNull BiFunction<D, Query, ? extends IPage<V>> pageDataFunc,
@Nullable List<? extends WriteHandler> strategyList) {
log.info("==================开始导出excel==================");
fileName = fileName + ".xlsx";
String filePath = FileUtil.getTempDirPath() + fileName;
InputStream in = null;
OutputStream outp = null;
File file = new File(filePath);
try {
if (!file.getParentFile().exists()) {
file.getParentFile().mkdirs();
}
if (!file.exists()) {
file.createNewFile();
}
// 构造表格样式
List<WriteHandler> strategies = new ArrayList<>();
if (ObjectUtil.isNotEmpty(strategyList)) {
strategies.addAll(strategyList);
} else {
// 默认导出样式
strategies.addAll(getDefaultStrategy());
}
List<List<String>> head = new ArrayList<>();
List<List<String>> content = new ArrayList<>();
exportFields.forEach((key, value) -> head.add(Collections.singletonList(value)));
exportFields.forEach((key, value) -> content.add(Collections.singletonList(key)));
// 构建excel写入对象
ExcelWriterBuilder writerBuilder = EasyExcel.write(file).head(head);
// 注册写入策略
strategies.forEach(writerBuilder::registerWriteHandler);
// 注册对象转换器
writerBuilder.registerConverter(new BaseDateConverter.LocalDateTimeConverter());
writerBuilder.registerConverter(new BaseDateConverter.LocalDateConverter());
writerBuilder.registerConverter(new BaseDateConverter.LocalTimeConverter());
writerBuilder.registerConverter(new BaseDateConverter.IntegerConverter());
ExcelWriter excelWriter = writerBuilder.build();
// 这里注意 如果同一个sheet只要创建一次
WriteSheet writeSheet = EasyExcel.writerSheet(DateUtil.format(DateUtil.now(), DateUtil.PATTERN_DATETIME_MINI)).build();
writeSheet.setColumnWidthMap(ObjectUtil.isNotEmpty(columnWidth) ? columnWidth : null);
// 分页查询数据
Query query = new Query();
query.setSize(500); //mybatis-plus最大分页500条
query.setCurrent(0);
IPage<V> dataPage = pageDataFunc.apply(dto, query);
long total = dataPage.getTotal();
if (total > 50000) {
throw new ExcelException("数据量过大,请按条件筛选导出");
} else if (total <= 0) {
throw new ExcelException("没有可以导出的数据");
}
long totalPage = (long) (Math.ceil(((double) total / dataPage.getSize())));
for (int i = 1; i <= totalPage; i++) {
List<V> dataList = new ArrayList<>();
List<List<Object>> exportList = new ArrayList<>();
query.setCurrent(i);
dataPage = pageDataFunc.apply(dto, query);
dataList.addAll(dataPage.getRecords());
for (int j = 0; j < dataList.size(); j++) {
V dataVO = dataList.get(j);
List<Object> exportMap = new ArrayList<>();
for (List<String> s : content) {
String str = s.get(0);
List<Field> fieldList = getField(dataVO);
Field field = fieldList.stream().filter(o -> o.getName().equalsIgnoreCase(str))
.findFirst().orElseThrow(() -> new RuntimeException(StringUtil.format("找不到字段:{}", str)));
field.setAccessible(true);
exportMap.add(Optional.ofNullable(field.get(dataVO)).orElse(""));
}
exportList.add(exportMap);
}
excelWriter.write(exportList, writeSheet);
}
// 千万别忘记finish 会帮忙关闭流
excelWriter.finish();
in = new FileInputStream(filePath);
outp = resp.getOutputStream();
//设置请求以及响应的内容类型以及编码方式
resp.setContentType("application/vnd.ms-excel;charset=UTF-8");
resp.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
outp = resp.getOutputStream();
//获取文件输入流
byte[] b = new byte[1024];
int i = 0;
//将缓冲区的数据输出到客户浏览器
while ((i = in.read(b)) > 0) {
outp.write(b, 0, i);
}
outp.flush();
log.info("============导出成功辣!!!!!!!!===========");
} catch (IOException | IllegalAccessException e) {
e.printStackTrace();
log.error("============导出失败===========,异常信息:{}", e.getMessage());
} finally {
IoUtil.closeQuietly(in);
IoUtil.closeQuietly(outp);
FileUtil.deleteQuietly(file);
}
}
/**
* 默认导出样式
*
* @return
*/
private static List<WriteHandler> getDefaultStrategy() {
List<WriteHandler> writeHandlers = new ArrayList<>();
/* 默认样式 */
// 头的策略
WriteCellStyle headStyle = new WriteCellStyle();
WriteFont headFont = new WriteFont();
headFont.setFontHeightInPoints((short) 12);
headStyle.setWriteFont(headFont);
// 内容的策略
WriteCellStyle contentStyle = new WriteCellStyle();
WriteFont contentFont = new WriteFont();
contentFont.setFontHeightInPoints((short) 12);
contentStyle.setWriteFont(contentFont);
// 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headStyle, contentStyle);
writeHandlers.add(horizontalCellStyleStrategy);
/* 列宽自适应 */
writeHandlers.add(new AdjustColumnWidthToFitStrategy());
return writeHandlers;
}
/**
* 获取对象所有字段(包括父类)
*
* @param o
* @return
*/
private static List<Field> getField(Object o) {
Class c = o.getClass();
List<Field> fieldList = new ArrayList<>();
while (c != null) {
fieldList.addAll(new ArrayList<>(Arrays.asList(c.getDeclaredFields())));
c = c.getSuperclass();
}
return fieldList;
}
}
列宽自适应策略类
点击查看代码
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Sheet;
import org.springframework.util.CollectionUtils;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @Description EasyExcel列宽自适应策略类
* @date: 2023-5-17 10:06
* @author: 热得快炸了
*/
public class AdjustColumnWidthToFitStrategy extends AbstractColumnWidthStyleStrategy {
private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>();
@Override
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer integer, Boolean isHead) {
boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
if (needSetWidth) {
Map<Integer, Integer> maxColumnWidthMap = CACHE.get(writeSheetHolder.getSheetNo());
if (maxColumnWidthMap == null) {
maxColumnWidthMap = new HashMap<>();
CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
}
Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
if (columnWidth >= 0) {
if (columnWidth > 254) {
columnWidth = 254;
}
Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
Sheet sheet = writeSheetHolder.getSheet();
sheet.setColumnWidth(cell.getColumnIndex(), columnWidth * 200);
}
//设置单元格类型
cell.setCellType(CellType.STRING);
// 数据总长度
int length = cell.getStringCellValue().length();
// 换行数
int rows = cell.getStringCellValue().split("\n").length;
// 默认一行高为20
cell.getRow().setHeightInPoints(rows * 20);
}
}
}
/**
* 计算长度
*
* @param cellDataList
* @param cell
* @param isHead
* @return
*/
private Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) {
if (isHead) {
return cell.getStringCellValue().getBytes().length;
} else {
CellData cellData = cellDataList.get(0);
CellDataTypeEnum type = cellData.getType();
if (type == null) {
return -1;
} else {
switch (type) {
case STRING:
// 换行符（数据需要提前解析好）
int index = cellData.getStringValue().indexOf("\n");
return index != -1 ?
cellData.getStringValue().substring(0, index).getBytes().length + 1 : cellData.getStringValue().getBytes().length + 1;
case BOOLEAN:
return cellData.getBooleanValue().toString().getBytes().length;
case NUMBER:
return cellData.getNumberValue().toString().getBytes().length;
default:
return -1;
}
}
}
}
}
分页查询工具类
点击查看代码
package com.*.core.mp.support;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.experimental.Accessors;
/**
* 分页工具
*
* @author 热得快炸了
*/
@Data
@Accessors(chain = true)
@ApiModel(description = "查询条件")
public class Query {
/**
* 当前页
*/
@ApiModelProperty(value = "当前页")
private Integer current;
/**
* 每页的数量
*/
@ApiModelProperty(value = "每页的数量")
private Integer size;
/**
* 正排序规则
*/
@ApiModelProperty(hidden = true)
private String ascs;
/**
* 倒排序规则
*/
@ApiModelProperty(hidden = true)
private String descs;
}
三、如何使用
1、简单导出excel（需要定义导出实体类）
点击查看代码
public void export(UserDTO userDTO, HttpServletResponse response) {
List<User> userList = userService.getList(userDTO);
String fileName = "导出数据_" + System.currentTimeMillis();
ExcelUtil.export(response, fileName, "导出数据", userList, ExportDataDTO.class);
}
2、根据分页查询导出excel（需要定义导出实体类）
点击查看代码
public void export(UserDTO userDTO, HttpServletResponse response) {
String fileName = "导出数据_" + System.currentTimeMillis();
ExcelUtil.export(userDTO, response, ExportDataDTO.class, fileName,
// 将分页查询方法作为参数传入
(dto, query) -> getPage(query, dto),
// 此处可自定义excel写入策略
null);
}
3、根据分页查询导出excel,导出列顺序可调整（不需要定义导出实体类）
点击查看代码
public void export(UserDTO userDTO, HttpServletResponse response) {
String fileName = "导出数据_" + System.currentTimeMillis();
/* exportFields字段由用户在前端操作传入,字段顺序可自由调整
以下是前端传入参数样例
{
exportFields:
[
{rowNum: "序号"},
{name: "姓名"},
{age: "年龄"},
{gender: "性别"}
]
}
也可自定义为如下结构
LinkedHashMap<String, String> exportFields = new LinkedHashMap<>();
exportFields.put("subject","标题");
exportFields.put("businessTypeName","业务类型");
exportFields.put("instantLevel","紧急程度");
exportFields.put("operator","承办人");
exportFields.put("draftTime","拟稿时间");
exportFields.put("sendOrgName","发文单位");
自定义列宽示例
LinkedHashMap<Integer, Integer> columnWidth = new LinkedHashMap<>();
columnWidth.put(0, 20 * 1000);
columnWidth.put(1, 8 * 1000);
columnWidth.put(2, 5 * 1000);
columnWidth.put(3, 8 * 1000);
columnWidth.put(4, 8 * 1000);
columnWidth.put(5, 10 * 1000);
*/
List<Map<String, String>> exportFields = userDTO.getExportFields();
LinkedHashMap<String, String> exports = new LinkedHashMap<>();
exportFields.forEach(exports::putAll);
ExcelUtil.export(userDTO, resp, exports, fileName,
// 此参数为自定义列宽时使用, 若传入null则启用自适应列宽
null,
this::getPage, null);
}
