EasyExcel是Alibaba开源的一个Java处理Excel的工具。
官网解读：快速、简洁、解决大文件内存溢出的java处理Excel工具
快速
快速的读取excel中的数据。
简洁
映射excel和实体类，让代码变的更加简洁。
大文件
在读写大文件的时候使用磁盘做缓存，更加的节约内存。
官网地址：https://easyexcel.opensource.alibaba.com/
感兴趣可自己琢磨，该工具简单易上手，且性能相对比较高。
本文主要处理的问题是该工具读取Excel空数据行的问题。
首先解释为什么会产生空数据行：简单解释就是你在Excel中设置了单元的样式，却没有给单元格设值。因此，该工具在读取数据时便没有判断这一步，直接读取到整行数据均为null。
理解了核心问题后，要解决这个问题，实现思路也不难。
莫非就是把这种空数据行过滤即可。
本文是基于批处理监听器实现数据读取的，自定义集成该监听器（com.alibaba.excel.read.listener.PageReadListener），实现自己的逻辑即可解决问题。
下面是自定义监听器
package xin.cosmos.basic.easyexcel.framework;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.PageReadListener;
import com.alibaba.excel.util.ListUtils;
import lombok.extern.slf4j.Slf4j;
import xin.cosmos.basic.util.ObjectsUtil;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.function.Consumer;
import java.util.stream.Collectors;
@Slf4j
public class BatchPageReadListener<T> extends PageReadListener<T> {
private List<T> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
private final Consumer<List<T>> consumer;
public BatchPageReadListener(Consumer<List<T>> consumer) {
super(consumer);
this.consumer = consumer;
}
@Override
public void invoke(T data, AnalysisContext context) {
// 如果一行Excel数据均为空值，则不装载该行数据
if (isLineNullValue(data)) {
return;
}
cachedDataList.add(data);
if (cachedDataList.size() >= BATCH_COUNT) {
consumer.accept(cachedDataList);
cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
if (ObjectsUtil.isNull(cachedDataList)) {
return;
}
consumer.accept(cachedDataList);
}
private boolean isLineNullValue(T data) {
if (data instanceof String) {
return ObjectsUtil.isNull(data);
}
try {
List<Field> fields = Arrays.stream(data.getClass().getDeclaredFields())
.filter(f -> f.isAnnotationPresent(ExcelProperty.class))
.collect(Collectors.toList());
List<Boolean> lineNullList = new ArrayList<>(fields.size());
for (Field field : fields) {
field.setAccessible(true);
Object value = field.get(data);
if (ObjectsUtil.isNull(value)) {
lineNullList.add(Boolean.TRUE);
} else {
lineNullList.add(Boolean.FALSE);
}
}
return lineNullList.stream().allMatch(Boolean.TRUE::equals);
} catch (Exception e) {
log.error("读取数据行[{}]解析失败: {}", data, e.getMessage());
}
return true;
}
}下面是我对EasyExcel封装的工具类
package xin.cosmos.basic.easyexcel.helper;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.read.listener.PageReadListener;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.fastjson.JSON;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.multipart.MultipartFile;
import xin.cosmos.basic.define.ResultVO;
import xin.cosmos.basic.easyexcel.framework.BatchPageReadListener;
import xin.cosmos.basic.easyexcel.template.HeadVO;
import xin.cosmos.basic.exception.PlatformException;
import xin.cosmos.basic.util.BeanMapUtil;
import xin.cosmos.basic.util.ObjectsUtil;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.*;
@Slf4j
public class EasyExcelHelper {
public static <T> List<T> doReadExcelData(InputStream stream, Class<T> entityClass) {
List<T> data = new LinkedList<>();
EasyExcelFactory.read(stream, entityClass, new PageReadListener<T>(data::addAll)).sheet().doRead();
return data;
}
public static <T> List<T> doReadExcelData(InputStream stream, Class<T> entityClass, Comparator<T> comparator) {
List<T> data = new LinkedList<>();
EasyExcelFactory.read(stream, entityClass, new BatchPageReadListener<T>(list -> {
if (comparator != null) {
list.sort(comparator);
}
data.addAll(list);
})).sheet().doRead();
return data;
}
@SneakyThrows
public static <T> List<T> doReadExcelData(MultipartFile file, Class<T> entityClass) {
return doReadExcelData(file.getInputStream(), entityClass);
}
@SneakyThrows
public static <T> List<T> doReadExcelData(File file, Class<T> entityClass) {
List<T> data = new LinkedList<>();
EasyExcelFactory.read(file, entityClass, new BatchPageReadListener<T>(data::addAll)).sheet().doRead();
return data;
}
@SneakyThrows
public static <T> void downloadExcel(String pathName, List<T> data, Class<T> entityClazz) {
try {
// 构建Excel表头及数据体
ExcelWriterSheetBuilder builder = EasyExcel.write(pathName)
.autoCloseStream(true)
.sheet("sheet1");
doWriteWithDynamicColumns(builder, entityClazz, data);
} catch (Exception e) {
log.error("写文件错误：{}", e.getMessage());
throw new PlatformException("Excel下载数据错误");
}
}
public static <T> void downloadExcelToResponse(HttpServletResponse response, String excelFileName, List<T> data, Class<T> entityClazz) {
if (ObjectsUtil.isNull(data)) {
log.error("写文件错误：{}", "暂无可下载的数据");
writeErrMsg(response, "暂无可下载的数据");
return;
}
try {
// 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setCharacterEncoding("utf-8");
if (excelFileName.endsWith(".xlsx") || excelFileName.endsWith(".xls") ||
excelFileName.endsWith(".XLSX") || excelFileName.endsWith(".XLS")) {
excelFileName = excelFileName.substring(0, excelFileName.lastIndexOf("."));
}
// 这里URLEncoder.encode可以防止中文乱码 当然和easy excel没有关系
String urlFileName = URLEncoder.encode(excelFileName, "UTF-8").replaceAll("\\+", "%20");
response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + urlFileName + ".xlsx");
response.setHeader("excel-file-name", urlFileName + ".xlsx");
// 构建Excel表头及数据体
ExcelWriterSheetBuilder builder = EasyExcel.write(response.getOutputStream())
.excelType(ExcelTypeEnum.XLSX)
.autoCloseStream(true)
.sheet("sheet1");
doWriteWithDynamicColumns(builder, entityClazz, data);
} catch (Exception e) {
log.error("写文件错误：{}", e.getMessage());
writeErrMsg(response, e.getMessage());
}
}
public static <T> void doWriteWithDynamicColumns(ExcelWriterSheetBuilder builder, Class<T> entityClazz, List<T> data) {
List<HeadVO> customizeHeads = new ArrayList<>();
Field[] fieldArray = entityClazz.getDeclaredFields();
// 获取类的注解
for (Field field : fieldArray) {
// 忽略导出属性
if (field.isAnnotationPresent(ExcelIgnore.class)) {
continue;
}
if (field.isAnnotationPresent(ExcelProperty.class)) {
ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);
List<String> head = Arrays.asList(excelProperty.value());
int index = excelProperty.index();
int order = excelProperty.order();
HeadVO headVO = HeadVO.builder().headTitle(head).index(index).order(order).field(field.getName()).build();
customizeHeads.add(headVO);
}
}
// 表头排序
Collections.sort(customizeHeads);
// 处理表头
List<List<String>> heads = new ArrayList<>();
List<String> fields = new ArrayList<>();
for (int i = 0; i <= customizeHeads.size() - 1; i++) {
heads.add(customizeHeads.get(i).getHeadTitle());
fields.add(customizeHeads.get(i).getField());
}
// 处理数据
List<List<Object>> objs = new ArrayList<>();
List<Map<String, ?>> maps = BeanMapUtil.beansToMaps(data);
maps.forEach(map -> {
List<Object> obj = new ArrayList<>();
for (String field : fields) {
obj.add(map.get(field));
}
objs.add(obj);
});
builder.head(heads).doWrite(objs);
}
@SneakyThrows
private static void writeErrMsg(HttpServletResponse response, String errMsg) {
// 重置response
response.reset();
response.setContentType("application/json");
response.setCharacterEncoding("utf-8");
response.getWriter().println(JSON.toJSONString(ResultVO.failed(errMsg)));
}
}package xin.cosmos.basic.easyexcel.template;
import lombok.Builder;
import lombok.Data;
import java.util.List;
@Builder
@Data
public class HeadVO implements Comparable<HeadVO> {
private List<String> headTitle;
private String field;
private int index;
private int order;
@Override
public int compareTo(HeadVO o) {
if (this.index == o.getIndex()) {
return this.order - o.getOrder();
}
return this.index - o.getIndex();
}
}最后是一个基于Spring cglib的Map<==>Java Bean之间的转换工具
package xin.cosmos.basic.util;
import org.springframework.cglib.beans.BeanMap;
import xin.cosmos.basic.exception.PlatformException;
import java.util.*;
public class BeanMapUtil {
public static <T> Map<String, ?> beanToMap(T bean) {
BeanMap beanMap = BeanMap.create(bean);
Map<String, Object> map = new HashMap<>();
beanMap.forEach((key, value) -> map.put(String.valueOf(key), value));
return map;
}
public static <T> T mapToBean(Map<String, ?> map, Class<T> beanClazz) {
T bean;
try {
bean = beanClazz.newInstance();
} catch (InstantiationException | IllegalAccessException e) {
e.printStackTrace();
throw new PlatformException("Map集合转换到Bean失败");
}
BeanMap beanMap = BeanMap.create(bean);
beanMap.putAll(map);
return bean;
}
public static <T> List<Map<String, ?>> beansToMaps(List<T> dataList) {
List<Map<String, ?>> list = new ArrayList<>();
if (ObjectsUtil.isNull(dataList)) {
return Collections.emptyList();
}
Map<String, ?> map;
T bean;
for (T t : dataList) {
bean = t;
map = beanToMap(bean);
list.add(map);
}
return list;
}
public static <T> List<T> mapsToBeans(List<Map<String, ?>> dataMaps, Class<T> beanClazz) {
List<T> list = new ArrayList<>();
if (ObjectsUtil.isNull(dataMaps)) {
return Collections.emptyList();
}
Map<String, ?> map;
for (Map<String, ?> dataMap : dataMaps) {
map = dataMap;
T bean = mapToBean(map, beanClazz);
list.add(bean);
}
return list;
}
}
总结
到此这篇关于EasyExcel工具读取Excel空数据行问题解决的文章就介绍到这了,更多相关EasyExcel读取Excel空数据行内容请搜索编程网以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程网！
