添加依赖
<!-- fastjson -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.79</version>
</dependency>
<!--easyexcel-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.0.5</version>
</dependency>
<!-- gson -->
<dependency>
<groupId>com.google.code.gson</groupId>
<artifactId>gson</artifactId>
<version>2.8.6</version>
</dependency>
EasyExcel封装工具类
监听器
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.CellExtra;
import com.alibaba.fastjson.JSON;
import lombok.extern.slf4j.Slf4j;
import java.util.ArrayList;
import java.util.List;
/**
* Excel模板的读取监听类
* @param <T>
*/
@Slf4j
public class ImportExcelListener<T> extends AnalysisEventListener<T> {
/**
* 解析的数据
*/
private List<T> list = new ArrayList<>();
/**
* 正文起始行
*/
private Integer headRowNumber;
/**
* 合并单元格
*/
private List<CellExtra> extraMergeInfoList = new ArrayList<>();
public ImportExcelListener(Integer headRowNumber) {
this.headRowNumber = headRowNumber;
}
/**
* 这个每一条数据解析都会来调用
*
* @param data one row value. Is is same as {@link AnalysisContext#readRowHolder()}
* @param context context
*/
@Override
public void invoke(T data, AnalysisContext context) {
log.info("解析到一条数据: {}", JSON.toJSONString(data));
list.add(data);
}
/**
* 所有数据解析完成了 都会来调用
*
* @param context context
*/
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
log.info("所有数据解析完成！");
}
/**
* 返回解析出来的List
*/
public List<T> getData() {
return list;
}
/**
* 读取额外信息：合并单元格
*/
@Override
public void extra(CellExtra extra, AnalysisContext context) {
log.info("读取到了一条额外信息:{}", JSON.toJSONString(extra));
switch (extra.getType()) {
case MERGE: {
log.info(
"额外信息是合并单元格,而且覆盖了一个区间,在firstRowIndex:{},firstColumnIndex;{},lastRowIndex:{},lastColumnIndex:{}",
extra.getFirstRowIndex(), extra.getFirstColumnIndex(), extra.getLastRowIndex(),
extra.getLastColumnIndex());
if (extra.getRowIndex() >= headRowNumber) {
extraMergeInfoList.add(extra);
}
break;
}
default:
}
}
/**
* 返回解析出来的合并单元格List
*/
public List<CellExtra> getExtraMergeInfoList() {
return extraMergeInfoList;
}
}
读取工具类
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.enums.CellExtraTypeEnum;
import com.alibaba.excel.metadata.CellExtra;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.multipart.MultipartFile;
import java.lang.reflect.Field;
import java.util.Collection;
import java.util.List;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.atomic.AtomicReference;
/**
* 导入工具类
*
* @param <T>
*/
@Slf4j
public class ImportExcelHelper<T> {
/**
* 返回解析后的List
*
* @return java.util.List<T> 解析后的List
* @param: fileName 文件名
* @param: clazz Excel对应属性名
* @param: sheetNo 要解析的sheet
* @param: headRowNumber 正文起始行
*/
public List<T> getList(MultipartFile file, Class<T> clazz, Integer sheetNo, Integer headRowNumber) {
ImportExcelListener<T> listener = new ImportExcelListener<>(headRowNumber);
CountDownLatch latch = new CountDownLatch(1);
new Thread(() -> {
try {
EasyExcel.read(file.getInputStream(), clazz, listener).extraRead(CellExtraTypeEnum.MERGE).sheet(sheetNo).headRowNumber(headRowNumber).doRead();
} catch (Exception e) {
log.error(e.getMessage());
}
latch.countDown();
}).start();
try {
latch.await();
} catch (InterruptedException e) {
}
List<CellExtra> extraMergeInfoList = listener.getExtraMergeInfoList();
//没有合并单元格情况，直接返回即可
if (isEmpty(extraMergeInfoList)) {
return listener.getData();
}
CountDownLatch computerLatch = new CountDownLatch(1);
AtomicReference<List<T>> data = new AtomicReference<>();
new Thread(() -> {
//存在有合并单元格时，自动获取值，并校对
data.set(explainMergeData(listener.getData(), extraMergeInfoList, headRowNumber));
computerLatch.countDown();
}).start();
try {
computerLatch.await();
} catch (InterruptedException e) {
}
return data.get();
}
/**
* 处理合并单元格
*
* @param data 解析数据
* @param extraMergeInfoList 合并单元格信息
* @param headRowNumber 起始行
* @return 填充好的解析数据
*/
private List<T> explainMergeData(List<T> data, List<CellExtra> extraMergeInfoList, Integer headRowNumber) {
//循环所有合并单元格信息
extraMergeInfoList.forEach(cellExtra -> {
int firstRowIndex = cellExtra.getFirstRowIndex() - headRowNumber;
int lastRowIndex = cellExtra.getLastRowIndex() - headRowNumber;
int firstColumnIndex = cellExtra.getFirstColumnIndex();
int lastColumnIndex = cellExtra.getLastColumnIndex();
//获取初始值
Object initValue = getInitValueFromList(firstRowIndex, firstColumnIndex, data);
//设置值
for (int i = firstRowIndex; i <= lastRowIndex; i++) {
for (int j = firstColumnIndex; j <= lastColumnIndex; j++) {
setInitValueToList(initValue, i, j, data);
}
}
});
return data;
}
/**
* 设置合并单元格的值
*
* @param filedValue 值
* @param rowIndex 行
* @param columnIndex 列
* @param data 解析数据
*/
private void setInitValueToList(Object filedValue, Integer rowIndex, Integer columnIndex, List<T> data) {
T object = data.get(rowIndex);
for (Field field : object.getClass().getDeclaredFields()) {
//提升反射性能，关闭安全检查
field.setAccessible(true);
ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);
if (annotation != null) {
if (annotation.index() == columnIndex) {
try {
field.set(object, filedValue);
break;
} catch (IllegalAccessException e) {
log.error("设置合并单元格的值异常：{}", e.getMessage());
}
}
}
}
}
/**
* 获取合并单元格的初始值
* rowIndex对应list的索引
* columnIndex对应实体内的字段
*
* @param firstRowIndex 起始行
* @param firstColumnIndex 起始列
* @param data 列数据
* @return 初始值
*/
private Object getInitValueFromList(Integer firstRowIndex, Integer firstColumnIndex, List<T> data) {
Object filedValue = null;
T object = data.get(firstRowIndex);
for (Field field : object.getClass().getDeclaredFields()) {
//提升反射性能，关闭安全检查
field.setAccessible(true);
ExcelProperty annotation = field.getAnnotation(ExcelProperty.class);
if (annotation != null) {
if (annotation.index() == firstColumnIndex) {
try {
filedValue = field.get(object);
break;
} catch (IllegalAccessException e) {
log.error("设置合并单元格的初始值异常：{}", e.getMessage());
}
}
}
}
return filedValue;
}
/**
* 判断集合是否为空
*
* @param collection
* @return
*/
private boolean isEmpty(Collection<?> collection) {
return collection == null || collection.isEmpty();
}
}
实体类
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
/**
* @Description:
* @Author：chenyanbin
* @CreateTime: 2022-02-16 13:50
* @Version：1.0.0
*/
@Data
public class TestExcel {
@ExcelProperty(value="姓名",index = 0)
private String name;
@ExcelProperty(value="年龄",index = 1)
private String age;
@ExcelProperty(value="合并测试",index = 2)
private String test;
}
控制层
@PostMapping(value = "importDynamic")
@ApiOperation("动态获取Excel列名")
public void importDynamic(
@ApiParam(value = "文件上传", required = true) @RequestPart("file") MultipartFile file
) throws IOException {
ImportExcelHelper<TestExcel> helper = new ImportExcelHelper<>();
List<TestExcel> list = helper.getList(file, TestExcel.class, 0, 1);
System.out.println(JSON.toJSONString(list));
}
Excel数据
测试
浙公网安备 33010602011771号