【在线教育】EasyExcel入门

文章目录

​​1. EasyExcel 入门​​

​​1.1 EasyExcel概述​​

​​1.2 EasyExcel 特点​​

​​1.3 环境搭建​​

​​1.3.1 测试父项目​​

​​1.3.2 测试excel项目​​

​​1.4 基本操作​​

​​1.4.1 测试JavaBean​​

​​1.4.2 测试文件路径​​

​​1.4.3 写操作​​

​​1.4.3 读操作​​

​​1.5 复杂操作​​

​​1.5.1 复合表头​​

​​1.5.2 写操作：多表​​

​​1.5.3 读操作：多表​​

​​1.6.4 写操作：多对象​​

​​1.6 扩展：excel备份数据库​​

1. EasyExcel 入门

1.1 EasyExcel概述

EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel工具。

官网：​​EasyExcel（文档已经迁移） · 语雀​​

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 [email protected] */ 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 [email protected] */ 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 [email protected] */ @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 [email protected] */ 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 [email protected] */ 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 [email protected] */ 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();   } }