【在线教育】EasyExcel入门

迈不过友情╰ 2024-04-07 12:27 119阅读 0赞

## 文章目录 ##

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（文档已经迁移） · 语雀][EasyExcel_ _]

github地址：[GitHub - alibaba/easyexcel: 快速、简洁、解决大文件内存溢出的java处理Excel工具][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![c18b60c0f741444c81a6fb968530e3c5.png][]
 *  修改pom文件
    
           <dependencies>
                
                <dependency>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-starter-test</artifactId>
                </dependency>
            </dependencies>

#### 1.3.2 测试excel项目 ####

*  项目名：zx-test-excel

![2465008be00f48ad9cdd665017cfb039.png][]

*  修改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 基本操作 ###

![2a6de6c4662644b69b437eccfccf5a2a.png][]

#### 1.4.1 测试JavaBean ####

![2c1a1d6ffa714e4f9f0b5c8d2bfdc1eb.png][]

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备份数据库 ###

![bfb69e1aec264b24b453e85038cb187a.png][]

*  步骤
    
     *  步骤1：添加坐标
     *  步骤2：编写封装类
     *  步骤3：编写核心类

![31ff3aa3b31b4944b63d2d2d74284cdd.png][]

*  步骤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();
            }
        
        }

[EasyExcel_ _]: https://www.yuque.com/easyexcel/doc/easyexcel
[GitHub - alibaba_easyexcel_ _java_Excel]: https://github.com/alibaba/easyexcel
[c18b60c0f741444c81a6fb968530e3c5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/dfee87636b4146a8933ce227d2652360.png
[2465008be00f48ad9cdd665017cfb039.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/41117be0118042e2bb5f33207f9286ee.png
[2a6de6c4662644b69b437eccfccf5a2a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/93a6b1ffc1824710ae87fcbbddb8acc4.png
[2c1a1d6ffa714e4f9f0b5c8d2bfdc1eb.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/6e26e2bc84394dbc8559ac1ddbb5caf8.png
[bfb69e1aec264b24b453e85038cb187a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/92507b4a201e46d2bf881ec68f25f363.png
[31ff3aa3b31b4944b63d2d2d74284cdd.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/f1e4cf60da7f406080010b072eb6417a.png