EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel工具。
github地址:GitHub - alibaba/easyexcel: 快速、简洁、解决大文件内存溢出的java处理Excel工具
修改pom文件
? ?<dependencies> ? ?
?<!-- 测试 --> ? ?
? ?<dependency> ? ? ? ? ? ?
<groupId>org.springframework.boot</groupId> ? ? ? ?
? ?<artifactId>spring-boot-starter-test</artifactId> ? ? ?
?</dependency> ?
?</dependencies>
修改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>
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;
}
?
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);
? }
?
?
}
IV65536
,256列(IV)
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());
? }
?
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();
}
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;
}
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();
? }
?
?
}
?
具有缓存处理类
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();
}
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:添加坐标
<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();
}
?
}