【在线教育】EasyExcel入门

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

}