1 导入依赖
< dependency>
< groupId> com.alibaba</ groupId>
< artifactId> easyexcel</ artifactId>
< version> 2.1.6</ version>
</ dependency>
< dependency>
< groupId> org.projectlombok</ groupId>
< artifactId> lombok</ artifactId>
< version> 1.18.10</ version>
</ dependency>
2 创建实体类
package com. zhiyou100. test ;
import com. alibaba. excel. annotation. ExcelIgnore ;
import com. alibaba. excel. annotation. ExcelProperty ;
import com. alibaba. excel. annotation. write. style. ColumnWidth ;
import lombok. AllArgsConstructor ;
import lombok. Builder ;
import lombok. Data ;
import lombok. NoArgsConstructor ;
import lombok. experimental. Accessors ;
import java. util. Date ;
@Data
@AllArgsConstructor
@NoArgsConstructor
@ColumnWidth ( 20 )
public class Student {
private String id;
private String name;
private String gender;
@ColumnWidth ( 20 )
private Date birthday;
}
3 创建excel
private static void WriteExcel ( ) {
ArrayList < Student > students = new ArrayList < > ( ) ;
for ( int i = 0 ; i < 10 ; i++ ) {
Student data = new Student ( ) ;
data. setId ( "id_" + i) ;
data. setName ( "学号0" + i) ;
data. setBirthday ( new Date ( ) ) ;
data. setGender ( "男" ) ;
students. add ( data) ;
}
EasyExcel . write ( "d:\\学员表.xlsx" , Student . class ) . sheet ( "学生信息表" ) . doWrite ( students) ;
}
4 读取excel
创建一个excel:学生信息.xls
读取1:创建监听器
package com. zhiyou100. test ;
import com. alibaba. excel. context. AnalysisContext ;
import com. alibaba. excel. event. AnalysisEventListener ;
public class StudentReadListener extends AnalysisEventListener < Student > {
@Override
public void invoke ( Student data, AnalysisContext context) {
System . out. println ( "data = " + data) ;
}
@Override
public void doAfterAllAnalysed ( AnalysisContext context) {
}
}
读取1:创建读取程序
ExcelReaderBuilder workBook = EasyExcel . read
( "d:\\学员信息.xls" , Student . class , new StudentReadListener ( ) ) ;
ExcelReaderSheetBuilder sheet1 = workBook. sheet ( ) ;
sheet1. doRead ( ) ;
读取2:不创建监听器类 直接使用匿名内部类
EasyExcel . read ( "d:\\学员信息.xls" , Student . class , new AnalysisEventListener < Student > ( ) {
@Override
public void invoke ( Student student, AnalysisContext analysisContext) {
System . out. println ( "读取行数!" + analysisContext. readRowHolder ( ) . getRowIndex ( ) ) ;
System . out. println ( "student=" + student) ;
}
@Override
public void doAfterAllAnalysed ( AnalysisContext analysisContext) {
System . out. println ( "读取完毕!" + analysisContext. readRowHolder ( ) . getRowIndex ( ) ) ;
}
} ) . sheet ( ) . doRead ( ) ;
5 读取时使用日志系统
< dependency>
< groupId> org.slf4j</ groupId>
< artifactId> slf4j-log4j12</ artifactId>
< version> 1.7.5</ version>
</ dependency>
位置:src/main/resources下
名字:log4j.properties
log4j.rootLogger=DEBUG,console,file
log4j.appender.console = org.apache.log4j.ConsoleAppender
log4j.appender.console.Target = System.out
log4j.appender.console.Threshold=DEBUG
log4j.appender.console.layout = org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern=[%c]-%M-%t-%r-%d{HH:mm:ss}-%m%n
log4j.appender.file = org.apache.log4j.RollingFileAppender
log4j.appender.file.File=d:\\log\\log4j.log
log4j.appender.file.MaxFileSize=10mb
log4j.appender.file.Threshold=DEBUG
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=[%p][%d{yy-MM-dd}][%c]%m%n
log4j.logger.java.sql=DEBUG
log4j.logger.com.zhiyou100=DEBUG
log4j.logger.org.springframework=ERROR
log4j.logger.com.alibaba=DEBUG
@Slf4j
public class Test05Log {
public static void main ( String [ ] args) {
System . out. println ( "日志输出11" ) ;
log. info ( "info信息1" ) ;
log. debug ( "debug信息1" ) ;
log. warn ( "warn信息1" ) ;
System . out. println ( "日志输出12" ) ;
log. info ( "info信息2" ) ;
log. debug ( "debug信息2" ) ;
log. warn ( "warn信息2" ) ;
}
}
设置日志级别:在log4j.properties文件夹中::log4j.logger.com.zhiyou100=DEBUG
设置日志级别:在log4j.properties文件夹中::log4j.logger.com.zhiyou100=DEBUG
6 读取时 跨过表头
创建监听器时 重写读取表头的方法
package com. zhiyou100. test ;
import com. alibaba. excel. context. AnalysisContext ;
import com. alibaba. excel. event. AnalysisEventListener ;
import lombok. extern. slf4j. Slf4j ;
import java. util. Map ;
@Slf4j
public class StudentReadListener2 extends AnalysisEventListener < Student > {
@Override
public void invoke ( Student data, AnalysisContext context) {
System . out. println ( "第" + context. readRowHolder ( ) . getRowIndex ( ) + "行数据:" + data) ;
}
@Override
public void doAfterAllAnalysed ( AnalysisContext context) {
log. info ( "读取数据完毕!" ) ;
}
public void invokeHeadMap ( Map < Integer , String > headMap, AnalysisContext context) {
log. info ( "表头数据::headMap.size()=" + headMap. size ( ) + ",headMap=" + headMap) ;
}
}
创建sheet时 指定表头的行号
EasyExcel . read ( "d:\\学员表.xlsx" , Student . class , new StudentReadListener2 ( ) ) . sheet ( ) . headRowNumber ( 1 ) . doRead ( ) ;
测试
[com.alibaba.excel.context.AnalysisContextImpl]-currentSheet-main-870-18:01:33-Began to read:ReadSheetHolder{sheetNo=0, sheetName='学生信息表'} com.alibaba.excel.read.metadata.holder.ReadSheetHolder@5606c0b
[com.zhiyou100.test.StudentReadListener2]-invokeHeadMap-main-940-18:01:33-表头数据::headMap.size()=4,headMap={0=id, 1=name, 2=gender, 3=birthday}
第1行数据:Student(id=id_0, name=学号00, gender=男, birthday=Fri Jul 01 16:44:11 CST 2022)
第2行数据:Student(id=id_1, name=学号01, gender=男, birthday=Fri Jul 01 16:44:11 CST 2022)
第3行数据:Student(id=id_2, name=学号02, gender=男, birthday=Fri Jul 01 16:44:11 CST 2022)
第4行数据:Student(id=id_3, name=学号03, gender=男, birthday=Fri Jul 01 16:44:11 CST 2022)
第5行数据:Student(id=id_4, name=学号04, gender=男, birthday=Fri Jul 01 16:44:11 CST 2022)
第6行数据:Student(id=id_5, name=学号05, gender=男, birthday=Fri Jul 01 16:44:11 CST 2022)
第7行数据:Student(id=id_6, name=学号06, gender=男, birthday=Fri Jul 01 16:44:11 CST 2022)
第8行数据:Student(id=id_7, name=学号07, gender=男, birthday=Fri Jul 01 16:44:11 CST 2022)
第9行数据:Student(id=id_8, name=学号08, gender=男, birthday=Fri Jul 01 16:44:11 CST 2022)
第10行数据:Student(id=id_9, name=学号09, gender=男, birthday=Fri Jul 01 16:44:11 CST 2022)
[com.zhiyou100.test.StudentReadListener2]-doAfterAllAnalysed-main-1102-18:01:34-读取数据完毕!