EasyExcel——读Excel官方文档链接
maven依赖:
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.6</version>
</dependency>
监听器完整代码:
/**
* 多sheet、有合并单元格的excel导入
*/
public class ImportExcelListener<T> extends AnalysisEventListener<T> {
private static final Logger LOGGER = LoggerFactory.getLogger(ImportExcelListener.class);
/**
* 最终返回的解析数据list
*/
private final List<T> data = new ArrayList<>();
/**
* 解析数据
* key是sheetName,value是相应sheet的解析数据
*/
private final Map<String, List<T>> dataMap = new HashMap<>();
/**
* 合并单元格
* key键是sheetName,value是相应sheet的合并单元格数据
*/
private final Map<String, List<CellExtra>> mergeMap = new HashMap<>();
/**
* 正文起始行
*/
private final Integer headRowNumber;
public ImportExcelListener(Integer headRowNumber) {
this.headRowNumber = headRowNumber;
}
@Override
public void invoke(T data, AnalysisContext context) {
String sheetName = context.readSheetHolder().getSheetName();
dataMap.computeIfAbsent(sheetName, k -> new ArrayList<>());
dataMap.get(sheetName).add(data);
}
@Override
public void extra(CellExtra extra, AnalysisContext context) {
String sheetName = context.readSheetHolder().getSheetName();
switch (extra.getType()) {
case MERGE: // 额外信息是合并单元格
if (extra.getRowIndex() >= headRowNumber) {
mergeMap.computeIfAbsent(sheetName, k -> new ArrayList<>());
mergeMap.get(sheetName).add(extra);
}
break;
case COMMENT:
case HYPERLINK:
default:
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
LOGGER.info("Excel解析完成");
}
/**
* 获取解析数据
*/
public List<T> getData(InputStream in, Class<T> clazz) {
try {
EasyExcel.read(in, clazz, this)
.extraRead(CellExtraTypeEnum.MERGE)
.headRowNumber(headRowNumber)
.doReadAll();
} catch (Exception e) {
LOGGER.error("Excel读取异常:" + e);
}
convertDataMapToData();
return data;
}
/**
* 将具有多个sheet数据的dataMap转变成一个data
*/
private void convertDataMapToData() {
Iterator<Map.Entry<String, List<T>>> iterator = dataMap.entrySet().iterator();
while (iterator.hasNext()) {
Map.Entry<String, List<T>> next = iterator.next();
String sheetName = next.getKey();
List<T> list = next.getValue();
List<CellExtra> mergeList = mergeMap.get(sheetName);
if (CollectionUtils.isNotEmpty(mergeList)) {
list = explainMergeData(list, mergeList);
}
data.addAll(list);
}
}
/**
* 处理有合并单元格的数据
* @param list 解析数据
* @param mergeList 合并单元格信息
* @return 填充好的解析数据
*/
private List<T> explainMergeData(List<T> list, List<CellExtra> mergeList) {
// 循环所有合并单元格信息
mergeList.forEach(item -> {
Integer firstRowIndex = item.getFirstRowIndex() - headRowNumber;
Integer lastRowIndex = item.getLastRowIndex() - headRowNumber;
Integer firstColumnIndex = item.getFirstColumnIndex();
Integer lastColumnIndex = item.getLastColumnIndex();
// 获取初始值
Object initValue = getInitValueFromList(firstRowIndex, firstColumnIndex, list);
// 设置值
for (int i = firstRowIndex; i <= lastRowIndex; i++) {
for (int j = firstColumnIndex; j<= lastColumnIndex; j++) {
setInitValueToList(initValue, i, j, list);
}
}
});
return list;
}
/**
* 获取合并单元格的初始值
* rowIndex对应list的索引
* columnIndex对应实体内的字段
* @param firstRowIndex 起始行
* @param firstColumnIndex 起始列
* @param list 列数据
* @return 初始值
*/
private Object getInitValueFromList(Integer firstRowIndex, Integer firstColumnIndex, List<T> list) {
Object filedValue = null;
T object = list.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) {
LOGGER.error("获取合并单元格的初始值异常:"+e.getMessage());
}
}
}
}
return filedValue;
}
/**
* 设置合并单元格的值
* @param filedValue 值
* @param rowIndex 行
* @param columnIndex 列
* @param list 解析数据
*/
public void setInitValueToList(Object filedValue, Integer rowIndex, Integer columnIndex, List<T> list) {
T object = list.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) {
LOGGER.error("设置合并单元格的值异常:"+e.getMessage());
}
}
}
}
}
}
导入对象:
@Data
public class WbsExcelField {
@ExcelProperty(value = "单位工程", index = 0)
private String unitWork;
@ExcelProperty(value = "单位工程编码", index = 1)
private String unitWorkCode;
@ExcelProperty(value = "子单位工程", index = 2)
private String subUnitWork;
@ExcelProperty(value = "子单位工程编码", index = 3)
private String subUnitWorkCode;
@ExcelProperty(value = "分部工程", index = 4)
private String divisionalWork;
@ExcelProperty(value = "分部工程编码", index = 5)
private String divisionalWorkCode;
@ExcelProperty(value = "子分部工程", index = 6)
private String subDivisionalWork;
@ExcelProperty(value = "子分部工程编码", index = 7)
private String subDivisionalWorkCode;
@ExcelProperty(value = "分项工程", index = 8)
private String itemWork;
@ExcelProperty(value = "分项工程编码", index = 9)
private String itemWorkCode;
@ExcelProperty(value = "子分项工程", index = 10)
private String subItemWork;
@ExcelProperty(value = "子分项工程编码", index = 11)
private String subItemWorkCode;
}
导入文档截图(桥梁、隧道、道路三个sheet表头一致):
导入测试类:
public class ImportTest {
public static void main(String[] args) throws Exception {
String path = "......"; // 文件目录
String fileName = path + "导入模板TEST.xlsx";
File file = new File(fileName);
ImportExcelListener<WbsExcelField> listener = new ImportExcelListener<>(2);
List<WbsExcelField> data = listener.getData(new FileInputStream(file), WbsExcelField.class);
for (WbsExcelField wbsExcelField : data) {
System.out.println(wbsExcelField.toString());
}
}
}
输出结果:
对以上代码的注意点说明:
1.因为多个sheet的内容格式一致,所有有一个convertDataMapToData方法进行数据转换。
2.listener中没有对数据进行分批处理,而是直接将数据全部解析到data中。
3.EasyExcel......doReadAll()方法会读取全部sheet,doAfterAllAnalysed 会在每个sheet读取完毕后调用一次。