一、动态表头easyExcel导出。
上次用easyExcel做了个动态表头导入(https://blog.csdn.net/qq_37337660/article/details/110288393),不过,当时用的版本过低,并且也没有详细说明,这次整了个easyExcel动态表头的导出。
固定表头的有实体类,可以用注解,比较简单(下面参考罗列一些我觉得有用的处理办法),因为业务需要所以整动态表头(表头名称和数目都不固定)。还有经常出的错误
官网:https://www.yuque.com/easyexcel 有问题可以里面找解决办法。
1、加依赖
<!--easyExcel-->这个是必须,版本最新了。
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.7</version>
</dependency>
这个和第四个是升级了,支持某些功能和解决报错
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.0</version>
</dependency>
这个是解决乱七八糟报错当时
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.0</version>
</dependency>
2、自己搞了个工具类
package com.trs.ai.ty.utils;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.poi.ss.usermodel.*;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.List;
/**
* easyExcel工具类
*
* @author li.chengzhen
* @version 1.0
* @date 2021/3/2 16:34
*/
public class EasyExcelUtil {
/**
* 导出
*
* @param response HttpServletResponse
* @param headFiled 表头
* @param data 表数据
*/
public static void export(
HttpServletResponse response, List<List<String>> headFiled, List<List<Object>> data)
throws IOException {
EasyExcel.write(response.getOutputStream())
.head(headFiled)
// 样式
.registerWriteHandler(getHorizontalCellStyleStrategy())
.autoCloseStream(Boolean.FALSE)
.sheet(0)
.doWrite(data);
}
/**
* 设置请求头、文件名
*
* @param fileName excel文件名
*/
public static void setResponse(HttpServletResponse response, String fileName) {
// 编码设置成UTF-8,excel文件格式为.xlsx
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding(StandardCharsets.UTF_8.name());
// 这里URLEncoder.encode可以防止中文乱码 和easyexcel本身没有关系
fileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8);
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
}
/**
* excel首列序号列样式
*
* @param workbook Workbook
* @return org.apache.poi.ss.usermodel.CellStyle
*/
public static CellStyle firstCellStyle(Workbook workbook) {
CellStyle cellStyle = workbook.createCellStyle();
// 居中
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cellStyle.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());
// 设置边框
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setBorderTop(BorderStyle.THIN);
// 文字
Font font = workbook.createFont();
font.setBold(Boolean.TRUE);
cellStyle.setFont(font);
return cellStyle;
}
/**
* 用于设置excel导出时的样式 easyexcel 导出样式
*
* @return com.alibaba.excel.write.style.HorizontalCellStyleStrategy
*/
public static HorizontalCellStyleStrategy getHorizontalCellStyleStrategy() {
// 头的策略
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
// 背景设置
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 字体
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short) 11);
headWriteCellStyle.setWriteFont(headWriteFont);
// 内容的策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
// 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了
// 背景设置
contentWriteCellStyle.setFillForegroundColor(IndexedColors.AUTOMATIC.getIndex());
// 文字
WriteFont contentWriteFont = new WriteFont();
contentWriteFont.setFontHeightInPoints((short) 11);
contentWriteCellStyle.setWriteFont(contentWriteFont);
// 边框
contentWriteCellStyle.setBorderBottom(BorderStyle.NONE);
contentWriteCellStyle.setBorderLeft(BorderStyle.NONE);
contentWriteCellStyle.setBorderRight(BorderStyle.NONE);
contentWriteCellStyle.setBorderTop(BorderStyle.NONE);
// 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
}
}
先调用setResponse(),再调用export()方法,就可以直接返回给前端。
样式什么的都比较简单,可以自己设置,我想说的是不是所有的数据都是string,所以导出的时候就需要特殊处理。
3、自定义converter处理特殊类型:
我数据是直接去数据库查,当有日期的时候,返回的直接就是java.sql.Date。
这里自定义了一个SqlDateConverter ,这个转换器的convertToExcelData()就是导出的时候会调用的方法,我这里定义了Date怎么处理,偷懒直接借鉴easyExcel自带的DateNumberConverter的处理办法。自己处理反而不好搞。
/**
* sqlDate转换器。将日期转换成Excel的数字形式
*/
@Component
public class SqlDateConverter implements Converter<Date> {
@Override
public Class supportJavaTypeKey() {
return Date.class;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.STRING;
}
@Override
public Date convertToJavaData(
CellData cellData,
ExcelContentProperty contentProperty,
GlobalConfiguration globalConfiguration) {
return null;
}
@Override
public CellData convertToExcelData(
Date value,
ExcelContentProperty contentProperty,
GlobalConfiguration globalConfiguration) {
if (contentProperty == null || contentProperty.getDateTimeFormatProperty() == null) {
return new CellData(
BigDecimal.valueOf(
DateUtil.getExcelDate(
value, globalConfiguration.getUse1904windowing())));
} else {
return new CellData(
BigDecimal.valueOf(
DateUtil.getExcelDate(
value,
contentProperty
.getDateTimeFormatProperty()
.getUse1904windowing())));
}
}
}
这样肯定是没法生效的。所以还需要一个加载器,我这里直接定义一个:看我把SqlDateConverter 放在哪就知道了。
import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.converters.ConverterKeyBuild;
import com.alibaba.excel.converters.DefaultConverterLoader;
import com.alibaba.excel.converters.bigdecimal.BigDecimalBooleanConverter;
import com.alibaba.excel.converters.bigdecimal.BigDecimalNumberConverter;
import com.alibaba.excel.converters.bigdecimal.BigDecimalStringConverter;
import com.alibaba.excel.converters.booleanconverter.BooleanBooleanConverter;
import com.alibaba.excel.converters.booleanconverter.BooleanNumberConverter;
import com.alibaba.excel.converters.booleanconverter.BooleanStringConverter;
import com.alibaba.excel.converters.bytearray.BoxingByteArrayImageConverter;
import com.alibaba.excel.converters.bytearray.ByteArrayImageConverter;
import com.alibaba.excel.converters.byteconverter.ByteBooleanConverter;
import com.alibaba.excel.converters.byteconverter.ByteNumberConverter;
import com.alibaba.excel.converters.byteconverter.ByteStringConverter;
import com.alibaba.excel.converters.date.DateNumberConverter;
import com.alibaba.excel.converters.date.DateStringConverter;
import com.alibaba.excel.converters.doubleconverter.DoubleBooleanConverter;
import com.alibaba.excel.converters.doubleconverter.DoubleNumberConverter;
import com.alibaba.excel.converters.doubleconverter.DoubleStringConverter;
import com.alibaba.excel.converters.file.FileImageConverter;
import com.alibaba.excel.converters.floatconverter.FloatBooleanConverter;
import com.alibaba.excel.converters.floatconverter.FloatNumberConverter;
import com.alibaba.excel.converters.floatconverter.FloatStringConverter;
import com.alibaba.excel.converters.inputstream.InputStreamImageConverter;
import com.alibaba.excel.converters.integer.IntegerBooleanConverter;
import com.alibaba.excel.converters.integer.IntegerNumberConverter;
import com.alibaba.excel.converters.integer.IntegerStringConverter;
import com.alibaba.excel.converters.longconverter.LongBooleanConverter;
import com.alibaba.excel.converters.longconverter.LongNumberConverter;
import com.alibaba.excel.converters.longconverter.LongStringConverter;
import com.alibaba.excel.converters.shortconverter.ShortBooleanConverter;
import com.alibaba.excel.converters.shortconverter.ShortNumberConverter;
import com.alibaba.excel.converters.shortconverter.ShortStringConverter;
import com.alibaba.excel.converters.string.StringBooleanConverter;
import com.alibaba.excel.converters.string.StringErrorConverter;
import com.alibaba.excel.converters.string.StringNumberConverter;
import com.alibaba.excel.converters.string.StringStringConverter;
import com.alibaba.excel.converters.url.UrlImageConverter;
import com.trs.ai.ty.listener.SqlDateConverter;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import java.lang.reflect.Field;
import java.util.HashMap;
import java.util.Map;
/**
* easyExcel:自定义全局加载器
*
*/
@Configuration
public class CustomerDefaultConverterLoader {
/** 存放写时用到的converter */
private static final String ALL_CONVERTER = "allConverter";
/** 存放所有的converter */
private static final String WRITE_CONVERTER = "defaultWriteConverter";
@Bean
public DefaultConverterLoader init() throws IllegalAccessException {
DefaultConverterLoader converters = new DefaultConverterLoader();
Field[] fields = converters.getClass().getDeclaredFields();
for (Field field : fields) {
field.setAccessible(true);
if (field.getType() == Map.class) {
Map<String, Converter> oldMap = (Map<String, Converter>) field.get(converters);
// 兼容高版本(2.2.0+)通过静态代码块初始化 复用旧代码 节省空间
if (oldMap != null && !oldMap.isEmpty()) {
if (WRITE_CONVERTER.equalsIgnoreCase(field.getName())) {
putWriteConverter(oldMap, new SqlDateConverter());
} else if (ALL_CONVERTER.equalsIgnoreCase(field.getName())) {
putAllConverter(oldMap, new SqlDateConverter());
}
field.set(converters, oldMap);
} else {
setConverter(converters, field);
}
}
}
return converters;
}
private void setConverter(DefaultConverterLoader converters, Field field)
throws IllegalAccessException {
if (WRITE_CONVERTER.equalsIgnoreCase(field.getName())) {
Map<String, Converter> map = new HashMap<>(32);
putWriteConverter(map, new SqlDateConverter());
putWriteConverter(map, new BigDecimalNumberConverter());
putWriteConverter(map, new BooleanBooleanConverter());
putWriteConverter(map, new ByteNumberConverter());
putWriteConverter(map, new DateStringConverter());
putWriteConverter(map, new DoubleNumberConverter());
putWriteConverter(map, new FloatNumberConverter());
putWriteConverter(map, new IntegerNumberConverter());
putWriteConverter(map, new LongNumberConverter());
putWriteConverter(map, new ShortNumberConverter());
putWriteConverter(map, new StringStringConverter());
putWriteConverter(map, new FileImageConverter());
putWriteConverter(map, new InputStreamImageConverter());
putWriteConverter(map, new ByteArrayImageConverter());
putWriteConverter(map, new BoxingByteArrayImageConverter());
putWriteConverter(map, new UrlImageConverter());
field.set(converters, map);
} else if (ALL_CONVERTER.equalsIgnoreCase(field.getName())) {
Map<String, Converter> map = new HashMap<>(64);
putAllConverter(map, new SqlDateConverter());
putAllConverter(map, new BigDecimalBooleanConverter());
putAllConverter(map, new BigDecimalNumberConverter());
putAllConverter(map, new BigDecimalStringConverter());
putAllConverter(map, new BooleanBooleanConverter());
putAllConverter(map, new BooleanNumberConverter());
putAllConverter(map, new BooleanStringConverter());
putAllConverter(map, new ByteBooleanConverter());
putAllConverter(map, new ByteNumberConverter());
putAllConverter(map, new ByteStringConverter());
putAllConverter(map, new DateNumberConverter());
putAllConverter(map, new DateStringConverter());
putAllConverter(map, new DoubleBooleanConverter());
putAllConverter(map, new DoubleNumberConverter());
putAllConverter(map, new DoubleStringConverter());
putAllConverter(map, new FloatBooleanConverter());
putAllConverter(map, new FloatNumberConverter());
putAllConverter(map, new FloatStringConverter());
putAllConverter(map, new IntegerBooleanConverter());
putAllConverter(map, new IntegerNumberConverter());
putAllConverter(map, new IntegerStringConverter());
putAllConverter(map, new LongBooleanConverter());
putAllConverter(map, new LongNumberConverter());
putAllConverter(map, new LongStringConverter());
putAllConverter(map, new ShortBooleanConverter());
putAllConverter(map, new ShortNumberConverter());
putAllConverter(map, new ShortStringConverter());
putAllConverter(map, new StringBooleanConverter());
putAllConverter(map, new StringNumberConverter());
putAllConverter(map, new StringStringConverter());
putAllConverter(map, new StringErrorConverter());
field.set(converters, map);
}
}
private void putWriteConverter(Map<String, Converter> map, Converter converter) {
map.put(ConverterKeyBuild.buildKey(converter.supportJavaTypeKey()), converter);
}
private void putAllConverter(Map<String, Converter> map, Converter converter) {
map.put(
ConverterKeyBuild.buildKey(
converter.supportJavaTypeKey(), converter.supportExcelTypeKey()),
converter);
}
}
到这里,几乎没什么问题了,有问题欢迎询问。不得不说easyExcel真的比较好用,上手也快,升级到现版本已经很能满足我的需求了,当然现在我的数据非常小,以后大了需要处理一下。
4、错误
-
com.alibaba.excel.exception.ExcelDataConvertException: Can not find 'Converter' support class Timestamp.
从timestamp到单元格,easyExcel不知道怎么处理。这里选择自定义一个转换器。 -
WARNING: An illegal reflective access operation has occurred WARNING: Illegal reflective access by org.apache.poi.openxml4j.util.ZipSecureFile$1 (file:/D:/.m2/repository/org/apache/poi/poi-ooxml/3.17/poi-ooxml-3.17.jar) to field java.io.FilterInputStream.in WARNING: Please consider reporting this to the maintainers of org.apache.poi.openxml4j.util.ZipSecureFile$1 WARNING: Use --illegal-access=warn to enable warnings of further illegal reflective access operations WARNING: All illegal access operations will be denied in a future release
。这个警告是导入的时候会提示
通过升级子依赖解决。开头提到了:
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.0</version>
</dependency>
ERROR 35820 --- [nio-8080-exec-3] o.a.c.c.C.[.[.[.[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [/wdf] threw exception [Request processing failed; nested exception is com.alibaba.excel.exception.ExcelAnalysisException: java.lang.NoClassDefFoundError: org/apache/poi/POIXMLTypeLoader] with root cause
java.lang.ClassNotFoundException: org.apache.poi.POIXMLTypeLoader
添加了上面依赖就报这个错,然后再加入以下依赖解决:
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.1.0</version>
</dependency>
参考:官网,
easyExcel导出 无模版 实体类 导出 自定义表头,
EasyExcel自定义Converter全局加载器以及加载Converter的个人总结,
报错处理:An illegal reflective access operation has occurred Apache POI,
使用 EasyExcel 读取Excel(两种方式),
阿里的Easyexcel读取Excel文件(最新版本),
Spring Boot整合EasyExcel(完整版包含上传解析excel和下载模板)