封装hutool导出工具类，实现注解便捷导出Excel
工具准备
需要使用的maven依赖
<!--工具包-->
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.7.22</version>
</dependency>
注解创建
/**
* 用于Excel导出时标识字段名
*
* @author sgz
* @see ExcelUtil#createExcel(List, File)
* @see ExcelUtil#readExcel(InputStream inputStream, Class clazz)
* @since 2023/04/04
*/
@Documented
@Target({ElementType.FIELD})
@Retention(RUNTIME)
public @interface ExcelColumn {
String value();
/**
* 时间格式化参数
*/
String pattern() default "";
}
样式构造器
/**
* Excel样式构建器
* @author sgz
* @since 2023/05/06
*/
public interface ExcelStyleBuilder {
/**
* 在数据写入前设置样式
* @param writer ExcelWriter
*/
void beforeSetStyle(ExcelWriter writer);
/**
* 在数据写入后设置样式
* @param writer ExcelWriter
* @param headList 表头
*/
void afterSetStyle(ExcelWriter writer, List<String> headList);
}
工具类：
package com.bxkc.utils;
import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.convert.Convert;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.io.IoUtil;
import cn.hutool.core.lang.TypeReference;
import cn.hutool.core.map.MapUtil;
import cn.hutool.core.util.ReflectUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelWriter;
import com.bxkc.annotation.ExcelColumn;
import com.bxkc.utils.builder.ExcelStyleBuilder;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.io.IOUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
@Slf4j
public class ExcelUtil {
public static final String SUFFIX = ".xlsx";
/**
* 读取Excel
*
* @param inputStream 输入流
* @param clazz 需要读取生成的类 class | 字段上必须含有 @ExcelColumn 注解
* @param <T> 对象类型
* @return 生成的对象
* @see ExcelColumn
*/
public static <T> List<T> readExcel(InputStream inputStream, Class<T> clazz) {
// 通过工具类创建reader
ExcelReader reader = cn.hutool.poi.excel.ExcelUtil.getReader(inputStream);
// 读取第一个sheet
List<Map<String, Object>> readAll = reader.readAll();
reader.close();
// 文件合法判断
if (readAll.size() == 0) {
return null;
}
// 获取所有字段
Field[] fields = ReflectUtil.getFields(clazz);
// 生成对象
List<T> list = new ArrayList<>();
for (Map<String, Object> map : readAll) {
T t = ReflectUtil.newInstance(clazz);
for (Field field : fields) {
// 获取注解上的别名
ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
if (annotation != null) {
Object value = map.get(annotation.value());
ReflectUtil.setFieldValue(t, field, value);
}
}
list.add(t);
}
return list;
}
/**
* 根据list生成excel(字段上含有 @ExcelColumn 注解才会被放入表头)
*
* @param data 数据
* @param <T> 数据类型
* @param fileOut 写入excel文件
* @see ExcelColumn
*/
public static <T> void createExcel(List<T> data, File fileOut) {
createExcel(data, fileOut, null, null);
}
/**
* 根据list生成excel(字段上含有 @ExcelColumn 注解才会被放入表头)
*
* @param data 数据
* @param <T> 数据类型
* @param fileOut 写入excel文件
* @param columnList 需要的表头
* @see ExcelColumn
*/
public static <T> void createExcel(List<T> data, File fileOut, List<String> columnList) {
createExcel(data, fileOut, columnList, null);
}
/**
* 根据list生成excel(字段上含有 @ExcelColumn 注解才会被放入表头)
*
* @param data 数据
* @param <T> 数据类型
* @param fileOut 写入excel文件
* @param builder 样式构造者
* @see ExcelColumn
*/
public static <T> void createExcel(List<T> data, File fileOut, ExcelStyleBuilder builder) {
createExcel(data, fileOut, null, builder);
}
/**
* 将Excel写入到输出流当中(字段上含有 @ExcelColumn 注解才会被放入表头)
*
* @param data 数据
* @param <T> 数据类型
* @param fileName 文件名
* @param response response返回对象
* @param columnList 需要的表头
* @param builder 样式构造者
* @see ExcelStyleBuilder
* @see ExcelColumn
*/
public static <T> void flushExcel(List<T> data, String fileName, HttpServletResponse response, List<String> columnList, ExcelStyleBuilder builder) throws IOException {
ExcelWriter writer = getWriter();
writeAndBuildStyle(writer, data, columnList, builder);
downloadExcel(writer, response, fileName);
}
/**
* 将Excel写入到输出流当中(字段上含有 @ExcelColumn 注解才会被放入表头)
*
* @param data 数据
* @param <T> 数据类型
* @param fileName 文件名
* @param response response返回对象
* @param builder 样式构造者
* @see ExcelStyleBuilder
* @see ExcelColumn
*/
public static <T> void flushExcel(List<T> data, String fileName, HttpServletResponse response, ExcelStyleBuilder builder) throws IOException {
ExcelWriter writer = getWriter();
writeAndBuildStyle(writer, data, null, builder);
downloadExcel(writer, response, fileName);
}
/**
* 将Excel写入到输出流当中(字段上含有 @ExcelColumn 注解才会被放入表头)
*
* @param data 数据
* @param <T> 数据类型
* @param fileName 文件名
* @param response response返回对象
* @see ExcelStyleBuilder
* @see ExcelColumn
*/
public static <T> void flushExcel(List<T> data, String fileName, HttpServletResponse response) throws IOException {
ExcelWriter writer = getWriter();
writeAndBuildStyle(writer, data, null, null);
downloadExcel(writer, response, fileName);
}
/**
* 将Excel写入到输出流当中(字段上含有 @ExcelColumn 注解才会被放入表头)
*
* @param data 数据
* @param <T> 数据类型
* @param outputStream 写入到输出流
* @param columnList 需要的表头
* @param builder 样式构造者
* @see ExcelStyleBuilder
* @see ExcelColumn
*/
public static <T> void flushExcel(List<T> data, OutputStream outputStream, List<String> columnList, ExcelStyleBuilder builder) {
ExcelWriter writer = getWriter();
writeAndBuildStyle(writer, data, columnList, builder);
writer.flush(outputStream);
writer.close();
}
private static ExcelWriter getWriter() {
// 通过工具类创建writer，默认创建xls格式
return cn.hutool.poi.excel.ExcelUtil.getWriter(true);
}
/**
* 将数据填充到Writer
*
* @param writer writer
* @param data 数据
* @param <T> 数据类型
* @param columnList 需要的表头
* @return 返回导出的头部列表
* @see ExcelColumn
*/
private static <T> List<String> fillWriter(ExcelWriter writer, List<T> data, List<String> columnList) {
// 获取data泛型类
T t = data.get(0);
Class<?> aClass = t.getClass();
// 获取所有字段
Field[] fields = ReflectUtil.getFields(aClass);
// 表头
List<String> headers = new ArrayList<>();
// 格式化Map k->字段名称，v -> Pattern;
Map<String, String> patternMap = new HashMap<>();
for (Field field : fields) {
// 获取注解上的别名
ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
if (annotation == null) {
continue;
}
// 如果没有想要的列则不进行写入
if (CollectionUtils.isNotEmpty(columnList) && !columnList.contains(annotation.value())) {
continue;
}
// 添加时间转换
if (StringUtils.isNotBlank(annotation.pattern())) {
patternMap.put(field.getName(), annotation.pattern());
}
writer.addHeaderAlias(field.getName(), annotation.value());
headers.add(annotation.value());
}
// 写入头部
writer.writeHeadRow(headers);
// 写入数据
for (T rowBean : data) {
Map<String, ?> rowMap = BeanUtil.beanToMap(rowBean, new LinkedHashMap<>(), false, false);
if (MapUtil.isEmpty(patternMap)) {
writer.writeRow(rowMap, false);
} else {
// 转换后写入
Map<String, String> copyMap = convertValueToString(rowMap, patternMap);
writer.writeRow(copyMap, false);
}
}
return headers;
}
/**
* 根据list生成excel(字段上含有 @ExcelColumn 注解才会被放入表头)
*
* @param data 数据
* @param <T> 数据类型
* @param fileOut 写入excel文件
* @param columnList 需要的表头
* @param builder 样式构造者
* @see ExcelStyleBuilder
* @see ExcelColumn
*/
public static <T> void createExcel(List<T> data, File fileOut, List<String> columnList, ExcelStyleBuilder builder) {
ExcelWriter writer = getWriter();
writeAndBuildStyle(writer, data, columnList, builder);
writer.flush(fileOut);
writer.close();
}
/**
* 将数据填充到writer并且构建样式
*
* @param writer writer
* @param data 数据
* @param columnList 需要的表头
* @param builder 样式构造者
* @param <T> 数据类型
*/
private static <T> void writeAndBuildStyle(ExcelWriter writer, List<T> data, List<String> columnList, ExcelStyleBuilder builder) {
if (builder != null){
builder.beforeSetStyle(writer);
}
List<String> headList = fillWriter(writer, data, columnList);
if (builder != null){
builder.afterSetStyle(writer, headList);
}
}
/**
* 根据pattern转换值为String类型
*
* @param rowMap 原始数据map
* @param patternMap 格式化map
* @return 转换后的数据
*/
private static Map<String, String> convertValueToString(Map<String, ?> rowMap, Map<String, String> patternMap) {
HashMap<String, String> convertMap = new HashMap<>();
rowMap.forEach((k, v) -> {
if (Objects.isNull(v)) {
convertMap.put(k, null);
} else {
String value;
if (patternMap.containsKey(k)) {
// 需要转换
value = DateUtil.format((Date) v, patternMap.get(k));
} else {
value = Convert.convert(String.class, v);
}
convertMap.put(k, value);
}
});
return convertMap;
}
private static void downloadExcel(ExcelWriter writer, HttpServletResponse response, String fileName) throws IOException {
// response为HttpServletResponse对象
fileName = new String(fileName.getBytes("gbk"), StandardCharsets.ISO_8859_1);
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
//out为OutputStream，需要写出到的目标流
ServletOutputStream out = response.getOutputStream();
writer.flush(out, true);
// 关闭writer，释放内存
writer.close();
// 关闭输出Servlet流
IoUtil.close(out);
}
}
使用示例
创建一个样式构造器
/**
* 通用样式构建器
*
* @author sgz
* @version 1.0.0
* @since 2023/7/6
*/
public class CommonExcelStyleBuilder implements ExcelStyleBuilder {
@Override
public void beforeSetStyle(ExcelWriter writer) {
// 设置表头高度
Sheet sheet = writer.getSheet();
// 设置默认列宽
sheet.setDefaultColumnWidth(15);
// 冻结首行
sheet.createFreezePane(0, 1, 0, 1);
// 设置头样式
// 设置字体
Font font = writer.createFont();
font.setColor(IndexedColors.BLACK.getIndex());
font.setBold(true);
CellStyle headCellStyle = writer.getHeadCellStyle();
headCellStyle.setFont(font); // 设置字体样式
headCellStyle.setAlignment(HorizontalAlignment.CENTER); // 设置水平对齐的样式为居中对齐;
headCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
headCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
// 无边框
headCellStyle.setWrapText(true);
// 表内样式
CellStyle cellStyle = writer.getCellStyle();
cellStyle.setWrapText(true);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setAlignment(HorizontalAlignment.CENTER);
}
@Override
public void afterSetStyle(ExcelWriter writer, List<String> headList) {
writer.getSheet().getRow(0).setHeight((short) 600);
}
}
导出字段上加上@ExcelColumn注解
@ExcelColumn(value = "审核时间", pattern = DatePattern.NORM_DATETIME_PATTERN)
private Date auditTime;
调用工具类
ExcelUtil.createExcel(data, new File(), new CommonExcelStyleBuilder)
这样Excel就给写入到File中，需要注意File需要自己定义,new File()只是作为示例
评论区