一、官方文档地址
https://www.yuque.com/easyexcel/doc/easyexcel#63093516 maven坐标,版本同步https://github.com/alibaba/easyexcel
< dependency>
< groupId> com. alibaba< / groupId>
< artifactId> easyexcel< / artifactId>
< version> 2.1 .6 < / version>
< / dependency>
二、读取Excel文件
1、准备读入的Excel,如下图所示:
2、定义实体
ExcelProperty:定义需要处理的字段,未加ExcelProperty的话就默认处理,如果希望忽略的话,则在类上面加注解@ExcelIgnoreUnannotated ExcelIgnore 默认所有字段都会和excel去匹配,加了这个注解会忽略该字段
@Data
public class DemoEntity {
@ExcelProperty ( value = "ID" )
private Long id;
@ExcelProperty ( value = "姓名" )
private String name;
@ExcelProperty ( value = "年龄" )
private Long age;
}
3、重写读取的监听器
invokeHeadMap:可以针对Excel文件的头信息进行处理,例如比对Excel字段是否有缺失之类
@Slf4j
public class DemoListener extends AnalysisEventListener < DemoEntity> {
@Getter
private List< DemoEntity> demoEntityList = Lists. newArrayList ( ) ;
@Override
public void invokeHeadMap ( Map< Integer, String> headMap, AnalysisContext context) {
super . invokeHeadMap ( headMap, context) ;
}
@Override
public void invoke ( DemoEntity data, AnalysisContext context) {
demoEntityList. add ( data) ;
}
@Override
public void doAfterAllAnalysed ( AnalysisContext context) {
}
}
处理头文件:示例中是用的简单的list对比进行,可以使用和实体的映射来进行处理
public class DemoListener extends AnalysisEventListener < DemoEntity> {
@Getter
private List< DemoEntity> demoEntityList = Lists. newArrayList ( ) ;
private List< String> standardHeadMap = Lists. newArrayList ( "ID" , "姓名" , "年龄" ) ;
@Override
public void invokeHeadMap ( Map< Integer, String> headMap, AnalysisContext context) {
super . invokeHeadMap ( headMap, context) ;
if ( ! MapUtils. isEmpty ( headMap) ) {
Collection< String> headVal = headMap. values ( ) ;
StringBuilder diffBuilder = new StringBuilder ( ) . append ( "字段缺失" ) ;
int diffSize = 0 ;
for ( String index : standardHeadMap) {
if ( ! headVal. contains ( index) ) {
diffBuilder. append ( " " ) . append ( index) ;
diffSize++ ;
}
}
if ( diffSize > 0 ) {
log. error ( diffBuilder. toString ( ) ) ;
throw new ExcelAnalysisException ( diffBuilder. toString ( ) ) ;
}
}
}
@Override
public void invoke ( DemoEntity data, AnalysisContext context) {
demoEntityList. add ( data) ;
}
@Override
public void doAfterAllAnalysed ( AnalysisContext context) {
}
}
4、读取文件工具类
public static void readExcel ( InputStream fis, DemoListener listener) {
ExcelReader excelReader = EasyExcel. read ( fis, DemoEntity. class , listener) . build ( ) ;
ReadSheet readSheet = EasyExcel. readSheet ( 0 ) . build ( ) ;
excelReader. read ( readSheet) ;
excelReader. finish ( ) ;
}
5、测试及执行结果
public static void main ( String[ ] args) throws Exception {
String path = "/Users/allen/Documents/book/col/demo.xlsx" ;
File file = new File ( path) ;
InputStream inputStream = new FileInputStream ( file) ;
DemoListener listener = new DemoListener ( ) ;
readExcel ( inputStream, listener) ;
List< DemoEntity> list = listener. getDemoEntityList ( ) ;
System. out. println ( JSON. toJSONString ( list) ) ;
}
处理结果
sheetNo:代表读取第几个sheet sheetName:代表读取的sheet的名称 list:解析成功的结果集