【在线教育】EasyExcel入门
文章目录
1. EasyExcel 入门
1.1 EasyExcel概述
EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel工具。
github地址：GitHub - alibaba/easyexcel: 快速、简洁、解决大文件内存溢出的java处理Excel工具
1.2 EasyExcel 特点
-
Java解析、生成Excel比较有名的框架有Apache poi、jxl，但他们都存在一个严重的问题就是非常的耗内存。
-
EasyExcel 重写了poi，使一个3M的excel只需要几M内存，并且再大的excel不会出现内存溢出。
-
64M内存1分钟内读取75M(46W行25列)的Excel。
-
1.3 环境搭建
1.3.1 测试父项目
-
项目名：zx-test-parent
-
修改pom文件
<dependencies> <!-- 测试 --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> </dependency> </dependencies>
1.3.2 测试excel项目
-
项目名：zx-test-excel
-
修改pom，添加依赖
<dependencies> <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>3.1.1</version> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> </dependency> </dependencies>
1.4 基本操作
1.4.1 测试JavaBean
package com.czxy.zx.demo01;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
import java.util.Date;
/**
* Created by liangtong.
*/
@Data
public class Student {
@ExcelProperty("编号")
private String id;
@ExcelProperty("姓名")
private String name;
@ExcelProperty("年龄")
private Integer age;
@ExcelProperty("电话")
private String telephone;
@ExcelProperty("邮箱")
private String email;
@ExcelProperty("生日")
private Date brithday;
}
1.4.2 测试文件路径
package com.czxy.zx.demo01;
import org.junit.jupiter.api.Test;
/**
* @author 桐叔
* @email liangtong@itcast.cn
*/
public class TestExcel {
/**
* 获得根路径
* @return
*/
public String getPath() {
return this.getClass().getResource("/").getPath();
}
@Test
public void testPath() {
// 测试文件路径
String path = getPath() + "student_demo.xls";
System.out.println(path);
}
}
1.4.3 写操作
-
excel 属于 office组件一个软件
-
存在若干版本，大体上划分2种情况，2007前的，2007年后的
-
2003版：扩展名 xls，内容比较少，最大单元格
IV65536，256列(IV) -
2007版：扩展名 xlsx，内容较多，最大单元格
XFD1048576，16384列(XFD)
-
/**
* 准备数据
* @return
*/
private List<Student> getData(){
List<Student> list = new ArrayList<Student>();
for(int i = 0 ; i < 10 ; i ++){
Student student = new Student();
student.setId("stu" + i);
student.setName("wang" + i);
student.setAge( 18 + i );
student.setTelephone("1361234" + i);
student.setEmail("wang" + i + "@czxy.com");
student.setBrithday(new Date());
list.add(student);
}
return list;
}
@Test
public void testWrite(){
String file = getPath() + "student_demo.xls";
//EasyExcel.write(位置,对象).sheet("表名").doWrite(数据);
EasyExcel.write(file,Student.class).sheet("班级").doWrite(getData());
}
1.4.3 读操作
-
处理类：
-
处理类需要实现
AnalysisEventListener接口
package com.czxy.zx.demo01; import com.alibaba.excel.context.AnalysisContext; import com.alibaba.excel.event.AnalysisEventListener; import com.czxy.zx.domain.Student; /** * @author 桐叔 * @email liangtong@itcast.cn */ public class StudentListener extends AnalysisEventListener<Student> { @Override public void invoke(Student student, AnalysisContext analysisContext) { System.out.println(student); } @Override public void doAfterAllAnalysed(AnalysisContext analysisContext) { System.out.println("解析完成"); } } -
-
测试
@Test public void testRead(){ String file = getPath() + "student_demo.xls"; //EasyExcel.read(文件, 封装对象, 处理类).sheet("表").doRead(); EasyExcel.read(file, Student.class, new StudentListener()).sheet("班级").doRead(); }
1.5 复杂操作
1.5.1 复合表头
package com.czxy.zx.demo02;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import lombok.Data;
import java.util.Date;
/**
* @author 桐叔
* @email liangtong@itcast.cn
*/
@Data
@ContentRowHeight(20)
@HeadRowHeight(20) //行高
@ColumnWidth(25) //列宽
public class Student2 {
@ExcelProperty("编号")
private String id;
@ExcelProperty({"基本信息","姓名"}) //复制表头
private String name;
@ExcelProperty({"基本信息","年龄"})
private Integer age;
@ExcelProperty("电话")
private String telephone;
@ExcelProperty("邮箱")
private String email;
@ExcelProperty("生日")
@DateTimeFormat("yyyy年MM月dd日")
private Date brithday;
}
1.5.2 写操作：多表
package com.czxy.zx.demo02;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.czxy.zx.demo01.Student;
import org.junit.Test;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
/**
* @author 桐叔
* @email liangtong@itcast.cn
*/
public class TestExcel2 {
/**
* 获得根路径
* @return
*/
public String getPath() {
return this.getClass().getResource("/").getPath();
}
/**
* 准备数据
* @return
*/
private List<Student2> getData(Integer flag){
List<Student2> list = new ArrayList<Student2>();
for(int m = 0 ; m < 10 ; m ++){
String i = "" + flag + m ;
Student2 student = new Student2();
student.setId("stu" + i);
student.setName("wang" + i);
student.setAge( 18 );
student.setTelephone("1361234" + i);
student.setEmail("wang" + i + "@czxy.com");
student.setBrithday(new Date());
list.add(student);
}
return list;
}
@Test
public void testMoreSheetWrite(){
String file = getPath() + "student_demo2.xls";
ExcelWriter excelWriter = EasyExcel.write(file).build();
for (int i = 0; i < 5; i++) {
WriteSheet writeSheet = EasyExcel.writerSheet(i, "模板" + i).head(Student2.class).build();
// 分页去数据库查询数据 这里可以去数据库查询每一页的数据
List<Student2> data = getData(i);
excelWriter.write(data, writeSheet);
}
excelWriter.finish();
}
}
1.5.3 读操作：多表
-
具有缓存处理类
package com.czxy.zx.demo02; import com.alibaba.excel.context.AnalysisContext; import com.alibaba.excel.event.AnalysisEventListener; import java.util.ArrayList; import java.util.List; /** * Created by liangtong. */ public class Student2Listener extends AnalysisEventListener<Student2> { // 批量操作数 private static final int BATCH_COUNT = 10; // 用于缓存信息 private List<Student2> cache = new ArrayList<Student2>(); public void invoke(Student2 student, AnalysisContext analysisContext) { //保存学生信息 cache.add(student); if(cache.size() >= BATCH_COUNT){ // 保存数据 saveData(); } } public void doAfterAllAnalysed(AnalysisContext analysisContext) { //最后的不够 BATCH_COUNT 倍数 saveData(); } private void saveData() { // 集合不为空 if(! cache.isEmpty()) { // 处理缓存数据 System.out.println(cache); // 清空缓存 cache.clear(); } } } -
读操作
@Test public void testMoreRead(){ String file = getPath() + "student_demo2.xls"; //EasyExcel.read(文件, 封装对象, 处理类).sheet("表").doRead(); ExcelReader excelReader = EasyExcel.read(file, Student2.class, new Student2Listener()).build(); // 确定需要解析的sheet for (int i = 0; i < 5; i++) { ReadSheet readSheet = EasyExcel.readSheet("模板" + i).build(); excelReader.read(readSheet); } excelReader.finish(); }
1.6.4 写操作：多对象
-
Student
@Data @NoArgsConstructor @AllArgsConstructor public class Student { @ExcelProperty("姓名") private String name; @ExcelProperty("年龄") private Integer age; } -
Book
@Data @NoArgsConstructor @AllArgsConstructor @HeadRowHeight(50) @HeadFontStyle(fontName = "黑体",italic = BooleanEnum.TRUE, color = Font.COLOR_RED, underline = 2) public class Book { @ExcelProperty("编号") private String id; @ExcelProperty({"作者信息","姓名"}) private String authorName; @ExcelProperty({"作者信息","年龄"}) private Integer authorAge; @ExcelProperty({"书籍基本信息","标题"}) private String title; @ContentFontStyle(fontName = "楷书",italic = BooleanEnum.TRUE, color = Font.COLOR_RED, underline = -1) @ExcelProperty({"书籍基本信息","价格"}) private Double price; @ExcelProperty({"书籍基本信息","出版日期"}) @DateTimeFormat("yyyy年MM月dd日") private Date publishDate; } -
实现
package com.czxy.zx.demo03; import com.alibaba.excel.EasyExcel; import com.alibaba.excel.ExcelWriter; import com.alibaba.excel.write.metadata.WriteSheet; import com.czxy.zx.demo01.Student; import com.czxy.zx.demo02.Book; import org.junit.Test; import java.util.*; /** * @author 桐叔 * @email liangtong@itcast.cn */ public class TestManyObject { // 获得当前项目的运行时的根目录 public String getPath() { return this.getClass().getResource("/").getPath(); } // 模拟数据 public List<Student> getStudentData() { List<Student> list = new ArrayList<>(); for (int i = 0; i < 20; i++) { list.add(new Student("张三" + i, 18 + i)); } return list; } public List<Book> getBookData() { List<Book> list = new ArrayList<>(); for (int i = 0; i < 20; i++) { list.add(new Book(i+"" , "张三" + i , 18 +i, "坏蛋是怎么"+i, 998d+i, new Date())); } return list; } // 遍历map即可 private Map<Class<?>, List<?>> getData() { Map<Class<?>, List<?>> map = new HashMap<>(); map.put(Student.class, getStudentData()); map.put(Book.class, getBookData()); return map; } @Test public void testManyObject() { String file = getPath() + "many_object.xlsx"; //1 开始写 ExcelWriter excelWriter = EasyExcel.write(file).build(); //2 依次写每一个对象 for(Map.Entry<Class<?>, List<?>> entry : getData().entrySet()) { Class<?> clazz = entry.getKey(); //类型 List<?> data = entry.getValue(); //数据 WriteSheet writeSheet = EasyExcel.writerSheet(clazz.getSimpleName()).head(clazz).build(); excelWriter.write(data, writeSheet); } //3 写完成 excelWriter.finish(); } }
1.6 扩展：excel备份数据库
-
步骤
-
步骤1：添加坐标
-
步骤2：编写封装类
-
步骤3：编写核心类
-
-
步骤1：添加坐标
<dependencies> <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>3.0.5</version> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> </dependency> <dependency> <groupId>commons-dbutils</groupId> <artifactId>commons-dbutils</artifactId> <version>1.7</version> </dependency> </dependencies>
-
步骤2：编写封装类
package com.czxy.zx.demo03; import com.alibaba.excel.annotation.ExcelProperty; import com.alibaba.excel.annotation.write.style.ColumnWidth; import com.alibaba.excel.annotation.write.style.ContentRowHeight; import com.alibaba.excel.annotation.write.style.HeadRowHeight; import lombok.Data; import java.util.Date; import java.util.List; /** * 课程 * */ @Data @ContentRowHeight(20) @HeadRowHeight(20) //行高 @ColumnWidth(25) //列宽 public class Chapter { @ExcelProperty("章节ID") private String id; @ExcelProperty("课程ID") private String courseId; @ExcelProperty("章节名称") private String title; @ExcelProperty("显示排序") private Integer sort; @ExcelProperty("创建时间") private Date gmtCreate; @ExcelProperty("更新时间") private Date gmtModified; } package com.czxy.zx.demo03; import com.alibaba.excel.annotation.ExcelProperty; import com.alibaba.excel.annotation.format.DateTimeFormat; import com.alibaba.excel.annotation.write.style.ColumnWidth; import com.alibaba.excel.annotation.write.style.ContentRowHeight; import com.alibaba.excel.annotation.write.style.HeadRowHeight; import lombok.Data; import java.util.Date; /** * 课程 * */ @Data @ContentRowHeight(20) @HeadRowHeight(20) //行高 @ColumnWidth(25) //列宽 public class Course { @ExcelProperty("课程ID") private String id; @ExcelProperty("课程讲师ID") private String teacherId; @ExcelProperty("课程专业ID二级分类ID") private String subjectId; @ExcelProperty("一级分类ID") private String subjectParentId; @ExcelProperty("课程标题") private String title; @ExcelProperty("课程销售价格，设置为0则可免费观看") private Double price; @ExcelProperty("总课时") private Integer lessonNum; @ExcelProperty("课程封面图片路径") private String cover; @ExcelProperty("销售数量") private Long buyCount; @ExcelProperty("浏览数量") private Long viewCount; @ExcelProperty("乐观锁") private Long version; @ExcelProperty("视频状态 Draft未发布 Normal已发布") private String status; @ExcelProperty("创建时间") @DateTimeFormat("yyyy年MM月dd日") private Date gmtCreate; @ExcelProperty("更新时间") @DateTimeFormat("yyyy年MM月dd日") private Date gmtModified; } -
步骤3：编写核心类
package com.czxy.zx.demo03; import com.alibaba.excel.EasyExcel; import com.alibaba.excel.ExcelWriter; import com.alibaba.excel.write.metadata.WriteSheet; import com.czxy.zx.demo02.Student2; import org.apache.commons.dbutils.BasicRowProcessor; import org.apache.commons.dbutils.DbUtils; import org.apache.commons.dbutils.GenerousBeanProcessor; import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.handlers.BeanListHandler; import org.junit.Test; import java.sql.*; import java.util.*; /** * @author 桐叔 * @email liangtong@itcast.cn */ public class TestBackdb { public Class getClassByTableName(String tableName) { Map<String,Class> map = new HashMap<>(); map.put("edu_chapter", Chapter.class); map.put("edu_course", Course.class); return map.get(tableName); } public String getPath() { return this.getClass().getResource("/").getPath(); } public Connection getConnection() { try { Class.forName("com.mysql.jdbc.Driver"); String url = "jdbc:mysql://127.0.0.1:3306/zx_edu_course?useUnicode=true&characterEncoding=utf8"; String username = "root"; String password = "1234"; Properties props =new Properties(); props.setProperty("user", username); props.setProperty("password", password); props.setProperty("remarks", "true"); //设置可以获取remarks信息 props.setProperty("useInformationSchema", "true"); //设置可以获取tables remarks信息 return DriverManager.getConnection(url, props); } catch (Exception e) { throw new RuntimeException(e); } } @Test public void testDB() throws Exception { String file = getPath() + "db.xls"; QueryRunner queryRunner = new QueryRunner(); ExcelWriter excelWriter = EasyExcel.write(file).build(); String dbName = "zx_edu_course"; //获得连接 Connection conn = getConnection(); //语句执行者 Statement st = conn.createStatement(); //数据库的元数据 DatabaseMetaData databaseMetaData = conn.getMetaData(); //获得所有的数据库 ResultSet catalogResultSet = databaseMetaData.getCatalogs(); //遍历所有的数据库 while(catalogResultSet.next()) { //获得数据库的名称 String databaseName = catalogResultSet.getString(1); if(dbName.equals(databaseName)) { //使用数据库 st.execute("use " + databaseName); ResultSet tableResultSet = databaseMetaData.getTables(databaseName, null, null, null); //遍历所有的表名 while(tableResultSet.next()) { //表名 String tableName = tableResultSet.getString(3); //TABLE_NAME String tableRemarks = tableResultSet.getString("REMARKS"); //获得表的备注 // 通过表名获得excel处理类 Class excelBeanClass = getClassByTableName(tableName); if(excelBeanClass != null) { //获得当前表的所有数据 String sql = "select * from " + tableName; // List data = (List) queryRunner.query(conn, sql, new BeanListHandler<>(excelBeanClass, new BasicRowProcessor(new GenerousBeanProcessor()) )); // 创建sheet WriteSheet writeSheet = EasyExcel.writerSheet(tableRemarks != null ? tableRemarks : tableName).head(excelBeanClass).build(); excelWriter.write(data, writeSheet); } } } } //写入完成 excelWriter.finish(); } }
