easyExcel的学习：简单的使用

简介

简单的读，写Excel，以及大数据量下的读和写

基本概念

easyExcel：基于java的读写Excel的开源项目。省内存的条件下支持读写百M的Excel。适用于项目中涉及到Excel文件，cvs文件的读写操作。
官方文档

导入：把数据从Excel存到数据库中的过程
导出：把数据从数据库中存到Excel中的过程

easyExcel写的简单例子

适用于数据量不大的时候（5000条数据以内）

创建maven项目

创建普通的maven项目，结构如下：

pom.xml文件

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>org.example</groupId>
  <artifactId>easyExcelTest</artifactId>
  <version>1.0-SNAPSHOT</version>

  <name>easyExcelTest</name>
  <!-- FIXME change it to the project's website -->
  <url>http://www.example.com</url>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <maven.compiler.source>1.7</maven.compiler.source>
    <maven.compiler.target>1.7</maven.compiler.target>
  </properties>

  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>org.projectlombok</groupId>
      <artifactId>lombok</artifactId>
      <version>1.18.24</version>
    </dependency>
    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>easyexcel</artifactId>
      <version>3.1.2</version>
    </dependency>
  </dependencies>
</project>

pojo的student类

package org.christ.pojo;

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@NoArgsConstructor
@AllArgsConstructor
public class student {
    @ExcelProperty("学号")
    private Integer id;
    @ExcelProperty("名字")
    private String name;
    @ExcelProperty("地址")
    private String address;
}

writeTest类

package org.christ.write;

import com.alibaba.excel.EasyExcel;
import org.christ.pojo.student;

import java.util.ArrayList;
import java.util.List;

public class WriteTest {
    public static List<student> getStudents(){
    List<student> students = new ArrayList<>();
    students.add(new student(1,"christ","china"));
        students.add(new student(2,"mike","japan"));
        students.add(new student(3,"jhon","usa"));
return students;
    }

    public static void main(String[] args) {
        String filename = "C:\\Users\\86183\\Desktop\\seata\\test.xlxs";
        List<student> students = getStudents();
    	EasyExcel.write(filename,student.class).sheet("testtest").doWrite(students);
    }
}

easyExcel读的简单例子

ReadTest类

package org.christ.read;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.read.listener.PageReadListener;
import org.christ.pojo.student;

public class ReadTest {
    public static void main(String[] args) {
        String filename = "C:\\Users\\86183\\Desktop\\seata\\test.xlxs";
        EasyExcel.read(filename, student.class,new PageReadListener<student>(dataList ->{
            for (student s: dataList) {
                System.out.println(s);
                
            }
        })).sheet().doRead();
    }
}

批量写数据

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import org.christ.pojo.student;

import java.util.List;

public class batchWrite {
    public static void main(String[] args) {
        String filename = "C:\\Users\\86183\\Desktop\\seata\\batchtest.xlxs";
        try(ExcelWriter excelWriter = EasyExcel.write(filename, student.class).build()){
             WriteSheet writeSheet = EasyExcel.writerSheet("多条写入").build();
            for (int i = 0; i < 10; i++) {
                List<student> students = WriteTest.getStudents();
                excelWriter.write(students,writeSheet);
            }
        }
    }
}

解析：和少数据量对比，批量写入是先创建excelWriter ，然后再循环中分批次写入。writeSheet是sheet，如果要每个批次都写入不同的sheet中，那可以把这个writeSheet放入到循环里。

使用模板

可以给Excel表格模板，添加样式，让写入的数据都有样式。

创建模版

创建一个模版.xlxs文件，设置好样式{.属性名}表示接受的是集合

编写代码

package org.christ.write;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import org.christ.pojo.student;

import java.util.List;

public class fillWrite {
    public static void main(String[] args) {
        String filename = "C:\\Users\\86183\\Desktop\\seata\\filetest.xlxs";
        String templateFileName = "C:\\Users\\86183\\Desktop\\模版.xlsx";
        try(ExcelWriter excelWriter = EasyExcel.write(filename).withTemplate(templateFileName).build()){
            WriteSheet writeSheet = EasyExcel.writerSheet().build();
            for (int i = 0; i < 10; i++) {
                List<student> students = WriteTest.getStudents();
                excelWriter.fill(students,writeSheet);
            }
        }
    }
}

解析：templateFileName 就是模版.xlxs的位置。
在使用模板的时候， WriteSheet writeSheet = EasyExcel.writerSheet().build();的writerSheet()这个方法是不可以带参数的，它是使用模板的sheet。

自定义监听器读数据

编写studentDao

模拟存入数据库

package org.christ.dao;

import org.christ.pojo.student;

import java.util.ArrayList;

public class studentDao {
    public static void save(ArrayList<student> students){
        System.out.println("模拟存入数据库");
    }
}

编写监听器studentListener

package org.christ.listener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import org.christ.dao.studentDao;
import org.christ.pojo.student;

import java.util.ArrayList;

public class studentListener implements ReadListener<student> {
    private Integer count = 10;
    private ArrayList<student> list = new ArrayList<>();
    private org.christ.dao.studentDao studentDao;
    public studentListener(studentDao studentDao){
        this.studentDao = studentDao;
    }
    @Override
    public void invoke(student student, AnalysisContext analysisContext) {
	list.add(student);
	if(list.size()>=10){
   		studentDao.save(list);
    	list = new ArrayList<>(count);
}
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        if(list.size()>=0){
            studentDao.save(list);
            list = new ArrayList<>(count);
            System.out.println("end");
        }
    }
}

解析：
构造方法要传入一个dao层对象。
invoke方法是每读一条数据就会执行一次，doAfterAllAnalysed则是读完全部的时候执行。提高效率，让其在内存中存够10条再写入，使用list。
doAfterAllAnalysed方法中的判断则是如果还有剩余小于10条的数据的时候，也存入数据库。

使用easyExcel

package org.christ.read;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.read.metadata.ReadSheet;
import org.christ.dao.studentDao;
import org.christ.listener.studentListener;
import org.christ.pojo.student;

public class listenerRead {
    public static void main(String[] args) {
        String filename = "C:\\Users\\86183\\Desktop\\seata\\batchtest.xlxs";
         ExcelReader reader = EasyExcel.read(filename, student.class, new studentListener(new studentDao())).build();
         ReadSheet readSheet = EasyExcel.readSheet().build();
         reader.read(readSheet);
    }
}