【在线教育】EasyExcel入门（二）

packagecom.czxy.zx.demo02;
​importcom.alibaba.excel.annotation.ExcelProperty;
importcom.alibaba.excel.annotation.format.DateTimeFormat;
importcom.alibaba.excel.annotation.write.style.ColumnWidth;
importcom.alibaba.excel.annotation.write.style.ContentRowHeight;
importcom.alibaba.excel.annotation.write.style.HeadRowHeight;
importlombok.Data;
​importjava.util.Date;
​/*** @author 桐叔* @email liangtong@itcast.cn*/@Data@ContentRowHeight(20)
@HeadRowHeight(20)  //行高@ColumnWidth(25)    //列宽publicclassStudent2 {
@ExcelProperty("编号")
privateStringid;
​@ExcelProperty({"基本信息","姓名"})   //复制表头privateStringname;
​@ExcelProperty({"基本信息","年龄"})
privateIntegerage;
​@ExcelProperty("电话")
privateStringtelephone;
​@ExcelProperty("邮箱")
privateStringemail;
​@ExcelProperty("生日")
@DateTimeFormat("yyyy年MM月dd日")
privateDatebrithday;
}

packagecom.czxy.zx.demo02;
​importcom.alibaba.excel.EasyExcel;
importcom.alibaba.excel.ExcelWriter;
importcom.alibaba.excel.write.metadata.WriteSheet;
importcom.czxy.zx.demo01.Student;
importorg.junit.Test;
​importjava.util.ArrayList;
importjava.util.Date;
importjava.util.List;
​/*** @author 桐叔* @email liangtong@itcast.cn*/publicclassTestExcel2 {
/*** 获得根路径* @return*/publicStringgetPath() {
returnthis.getClass().getResource("/").getPath();
    }
​/*** 准备数据* @return*/privateList<Student2>getData(Integerflag){
List<Student2>list=newArrayList<Student2>();
for(intm=0 ; m<10 ; m++){
Stringi=""+flag+m ;
Student2student=newStudent2();
student.setId("stu"+i);
student.setName("wang"+i);
student.setAge( 18 );
student.setTelephone("1361234"+i);
student.setEmail("wang"+i+"@czxy.com");
student.setBrithday(newDate());
list.add(student);
        }
returnlist;
    }
​@TestpublicvoidtestMoreSheetWrite(){
Stringfile=getPath() +"student_demo2.xls";
​ExcelWriterexcelWriter=EasyExcel.write(file).build();
​for (inti=0; i<5; i++) {
WriteSheetwriteSheet=EasyExcel.writerSheet(i, "模板"+i).head(Student2.class).build();
// 分页去数据库查询数据 这里可以去数据库查询每一页的数据List<Student2>data=getData(i);
excelWriter.write(data, writeSheet);
        }
​excelWriter.finish();
    }
​​}
​

packagecom.czxy.zx.demo02;
​importcom.alibaba.excel.context.AnalysisContext;
importcom.alibaba.excel.event.AnalysisEventListener;
​importjava.util.ArrayList;
importjava.util.List;
​/*** Created by liangtong.*/publicclassStudent2ListenerextendsAnalysisEventListener<Student2> {
​// 批量操作数privatestaticfinalintBATCH_COUNT=10;
// 用于缓存信息privateList<Student2>cache=newArrayList<Student2>();
​publicvoidinvoke(Student2student, AnalysisContextanalysisContext) {
//保存学生信息cache.add(student);
if(cache.size() >=BATCH_COUNT){
// 保存数据saveData();
        }
    }
​publicvoiddoAfterAllAnalysed(AnalysisContextanalysisContext) {
//最后的不够 BATCH_COUNT 倍数saveData();
    }
​privatevoidsaveData() {
// 集合不为空if(!cache.isEmpty()) {
// 处理缓存数据System.out.println(cache);
// 清空缓存cache.clear();
        }
    }
}
​读操作@TestpublicvoidtestMoreRead(){
Stringfile=getPath() +"student_demo2.xls";
//EasyExcel.read(文件, 封装对象, 处理类).sheet("表").doRead();​ExcelReaderexcelReader=EasyExcel.read(file, Student2.class, newStudent2Listener()).build();
// 确定需要解析的sheetfor (inti=0; i<5; i++) {
ReadSheetreadSheet=EasyExcel.readSheet("模板"+i).build();
excelReader.read(readSheet);
        }
​excelReader.finish();
    }

Student@Data@NoArgsConstructor@AllArgsConstructorpublicclassStudent {
@ExcelProperty("姓名")
privateStringname;
@ExcelProperty("年龄")
privateIntegerage;
}
​Book@Data@NoArgsConstructor@AllArgsConstructor@HeadRowHeight(50)
@HeadFontStyle(fontName="黑体",italic=BooleanEnum.TRUE, color=Font.COLOR_RED, underline=2)
publicclassBook {
@ExcelProperty("编号")
privateStringid;
​@ExcelProperty({"作者信息","姓名"})
privateStringauthorName;
​@ExcelProperty({"作者信息","年龄"})
privateIntegerauthorAge;
​@ExcelProperty({"书籍基本信息","标题"})
privateStringtitle;
​@ContentFontStyle(fontName="楷书",italic=BooleanEnum.TRUE, color=Font.COLOR_RED, underline=-1)
@ExcelProperty({"书籍基本信息","价格"})
privateDoubleprice;
@ExcelProperty({"书籍基本信息","出版日期"})
@DateTimeFormat("yyyy年MM月dd日")
privateDatepublishDate;
}
实现packagecom.czxy.zx.demo03;
​importcom.alibaba.excel.EasyExcel;
importcom.alibaba.excel.ExcelWriter;
importcom.alibaba.excel.write.metadata.WriteSheet;
importcom.czxy.zx.demo01.Student;
importcom.czxy.zx.demo02.Book;
importorg.junit.Test;
​importjava.util.*;
​/*** @author 桐叔* @email liangtong@itcast.cn*/publicclassTestManyObject {
​// 获得当前项目的运行时的根目录publicStringgetPath() {
returnthis.getClass().getResource("/").getPath();
    }
​// 模拟数据publicList<Student>getStudentData() {
List<Student>list=newArrayList<>();
for (inti=0; i<20; i++) {
list.add(newStudent("张三"+i, 18+i));
        }
returnlist;
    }
​publicList<Book>getBookData() {
List<Book>list=newArrayList<>();
for (inti=0; i<20; i++) {
list.add(newBook(i+"" , "张三"+i , 18+i, "坏蛋是怎么"+i, 998d+i, newDate()));
        }
returnlist;
    }
​// 遍历map即可privateMap<Class<?>, List<?>>getData() {
Map<Class<?>, List<?>>map=newHashMap<>();
map.put(Student.class, getStudentData());
map.put(Book.class, getBookData());
returnmap;
    }
​@TestpublicvoidtestManyObject() {
Stringfile=getPath() +"many_object.xlsx";
//1 开始写ExcelWriterexcelWriter=EasyExcel.write(file).build();
//2 依次写每一个对象for(Map.Entry<Class<?>, List<?>>entry : getData().entrySet()) {
Class<?>clazz=entry.getKey();            //类型List<?>data=entry.getValue();            //数据WriteSheetwriteSheet=EasyExcel.writerSheet(clazz.getSimpleName()).head(clazz).build();
excelWriter.write(data, writeSheet);
        }
​//3 写完成excelWriter.finish();
    }
}
​