EasyExcel

EasyExcel的优点

Java 中操作 Excel 的框架 POI、JXT，但是他们都存在一个问题：非常的消耗内存。这时候阿里的大佬在 POI 的基础上进一步重写，封装得到了 EasyExcel，它最大的好处就是解决了过于消耗内存问题，也让使用者使用更加便利。

EasyExcel操作Excel

引入依赖

<dependency>
        <groupId>cn.afterturn</groupId>
        <artifactId>easypoi-base</artifactId>
        <version>${easypoi.version}</version>
    </dependency>

    <dependency>
        <groupId>cn.afterturn</groupId>
        <artifactId>easypoi-web</artifactId>
        <version>${easypoi.version}</version>
    </dependency>

    <dependency>
        <groupId>cn.afterturn</groupId>
        <artifactId>easypoi-annotation</artifactId>
        <version>${easypoi.version}</version>
    </dependency>

导出Excel

1.导出基本数据

注意:导出Excel的对象必须实现对象序列化接口

定义对象

package com.easy.entity;

import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import lombok.Data;

import java.io.Serializable;
import java.util.Date;

/**
 * @author jingLv
 * @date 2021/01/19
 */
@Data
@ExcelTarget("users")
public class User implements Serializable {
    private static final long serialVersionUID = -3764618099931012959L;

    @Excel(name = "编号", orderNum = "1")
    private String id;
    @Excel(name = "姓名", orderNum = "2")
    private String name;
    @Excel(name = "年龄", orderNum = "4", suffix = " $")
    private Integer age;
    @Excel(name = "生日", orderNum = "3", width = 20.0, exportFormat = "yyyy年MM月dd日")
    private Date bir;
    @Excel(name = "状态", orderNum = "5", replace = {"激活_1", "锁定_0"})
    private String status;
}

定义测试数据及导出Excel

package com.easy.demo;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import com.easy.entity.User;
import org.apache.poi.ss.usermodel.Workbook;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 导出excel
 *
 * @author jingLv
 * @date 2021/01/19
 */
public class ExportExcel {

    public static void main(String[] args) throws IOException {
        //获取数据
        List<User> users = getUsers();
        // 导出excel
        // 参数：exportParams 导出配置对象， 参数2：导出的类型， 参数3：导出数据集合
        Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("用户信息列表", "用户信息"), User.class, users);
        // 将excel写入指定位置
        FileOutputStream outputStream = new FileOutputStream("/Users/apple/JavaProject/operation-excel/ExportExcel.xls");
        workbook.write(outputStream);
        // 关闭流
        outputStream.close();
        workbook.close();
    }

    /**
     * 定义导出数据
     *
     * @return
     */
    public static List<User> getUsers() {
        List<User> users = new ArrayList<>();
        for (int i = 0; i < 5; i++) {
            User user = new User();
            user.setId(String.valueOf(i));
            user.setName("阿花number" + i);
            user.setAge(10 + i);
            user.setBir(new Date());
          	if (i % 2 == 0) {
                user.setStatus("0");
            } else {
                user.setStatus("1");
            }
            users.add(user);
        }
        return users;
    }
}

执行结果及查看Excel

2.导出指定字段

将age的属性不导出excel中

package com.easy.entity;

import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelIgnore;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import lombok.Data;

import java.io.Serializable;
import java.util.Date;

/**
 * @author jingLv
 * @date 2021/01/19
 */
@Data
@ExcelTarget("users")
public class User implements Serializable {
    private static final long serialVersionUID = -3764618099931012959L;

    @Excel(name = "编号", orderNum = "1", replace = {"xxx_1", "nnn_2"})
    private String id;
    @Excel(name = "姓名", orderNum = "2")
    private String name;
    // @Excel(name = "年龄", orderNum = "4", suffix = " $")
    @ExcelIgnore
    private Integer age;
    @Excel(name = "生日", orderNum = "3", width = 20.0, exportFormat = "yyyy年MM月dd日")
    private Date bir;
}

执行完成查看excel

3.导出list集合

定义实体加入集合对象

package com.easy.entity;

import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelIgnore;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import lombok.Data;

import java.io.Serializable;
import java.util.Date;
import java.util.List;

/**
 * @author jingLv
 * @date 2021/01/19
 */
@Data
@ExcelTarget("users")
public class User implements Serializable {
    private static final long serialVersionUID = -3764618099931012959L;

    @Excel(name = "编号", orderNum = "1")
    private String id;
    @Excel(name = "姓名", orderNum = "2")
    private String name;
    // @Excel(name = "年龄", orderNum = "4", suffix = " $")
    @ExcelIgnore
    private Integer age;
    @Excel(name = "生日", orderNum = "3", width = 20.0, exportFormat = "yyyy年MM月dd日")
    private Date bir;
    @Excel(name = "状态", orderNum = "5", replace = {"激活_1", "锁定_0"})
    private String status;
    @Excel(name = "爱好", orderNum = "6", width = 20.0)
    private List<String> habits;
}

修改测试数据及导出Excel

package com.easy.demo;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import com.easy.entity.User;
import org.apache.poi.ss.usermodel.Workbook;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

/**
 * 导出excel
 *
 * @author jingLv
 * @date 2021/01/19
 */
public class ExportExcel {

    public static void main(String[] args) throws IOException {
        //获取数据
        List<User> users = getUsers();
        // 导出excel
        // 参数：exportParams 导出配置对象， 参数2：导出的类型， 参数3：导出数据集合
        Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("用户信息列表", "用户信息"), User.class, users);
        // 将excel写入指定位置
        FileOutputStream outputStream = new FileOutputStream("/Users/apple/JavaProject/operation-excel/ExportExcel.xls");
        workbook.write(outputStream);
        // 关闭流
        outputStream.close();
        workbook.close();
    }

    /**
     * 定义导出数据
     *
     * @return
     */
    public static List<User> getUsers() {
        List<User> users = new ArrayList<>();
        for (int i = 0; i < 5; i++) {
            User user = new User();
            user.setId(String.valueOf(i));
            user.setName("阿花number" + i);
            user.setAge(10 + i);
            user.setBir(new Date());
            if (i % 2 == 0) {
                user.setStatus("0");
                user.setHabits(Arrays.asList("打篮球", "看书", "看片"));
            } else {
                user.setStatus("1");
                user.setHabits(Arrays.asList("喝酒", "抽烟", "烫头"));
            }
            users.add(user);
        }
        return users;
    }
}

执行完成查看excel

从上面的结果可以看出，导出格式是默认图片中数组的格式，如果要自定义导出格式怎么办？

自定义导出格式可以在对应的get方法中进行处理即可，如下代码：

package com.easy.entity;

import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelIgnore;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import lombok.Data;

import java.io.Serializable;
import java.util.Date;
import java.util.List;

/**
 * @author jingLv
 * @date 2021/01/19
 */
@Data
@ExcelTarget("users")
public class User implements Serializable {
    private static final long serialVersionUID = -3764618099931012959L;

    @Excel(name = "编号", orderNum = "1")
    private String id;
    @Excel(name = "姓名", orderNum = "2")
    private String name;
    // @Excel(name = "年龄", orderNum = "4", suffix = " $")
    @ExcelIgnore
    private Integer age;
    @Excel(name = "生日", orderNum = "3", width = 20.0, exportFormat = "yyyy年MM月dd日")
    private Date bir;
    @Excel(name = "状态", orderNum = "5", replace = {"激活_1", "锁定_0"})
    private String status;
    // @Excel(name = "爱好", orderNum = "6", width = 20.0)
    // 1. 忽略该字段
    @ExcelIgnore
    private List<String> habits;
    // 2. 定义爱好字符串代替habits
    @Excel(name = "爱好", orderNum = "6", width = 20.0)
    private String habitsStr;
    
    // 3. 修改get方法
    public String getHabitsStr() {
        StringBuilder sb = new StringBuilder();
        this.habits.forEach(s -> sb.append(s).append("、"));
        return sb.toString();
    }
}

执行完成查看excel

从上图看到，爱好一列已经是我们定义的格式了

4.导出对象中含有对象

导出对象中含有其他对象的Excel

新建新对象，Card.java

package com.easy.entity;

import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import lombok.Data;

import java.io.Serializable;

/**
 * @author jingLv
 * @date 2021/01/20
 */
@Data
@ExcelTarget("card")
public class Card implements Serializable {

    private static final long serialVersionUID = 9083730912519223193L;

    @Excel(name = "身份证号", orderNum = "7")
    private String id;
    @Excel(name = "家庭住址", orderNum = "8")
    private String address;
}

在User对象中定义Card对象

package com.easy.entity;

import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelEntity;
import cn.afterturn.easypoi.excel.annotation.ExcelIgnore;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import lombok.Data;

import java.io.Serializable;
import java.util.Date;
import java.util.List;

/**
 * @author jingLv
 * @date 2021/01/19
 */
@Data
@ExcelTarget("users")
public class User implements Serializable {
    private static final long serialVersionUID = -3764618099931012959L;

    @Excel(name = "编号", orderNum = "1")
    private String id;
    @Excel(name = "姓名", orderNum = "2")
    private String name;
    // @Excel(name = "年龄", orderNum = "4", suffix = " $")
    @ExcelIgnore
    private Integer age;
    @Excel(name = "生日", orderNum = "3", width = 20.0, exportFormat = "yyyy年MM月dd日")
    private Date bir;
    @Excel(name = "状态", orderNum = "5", replace = {"激活_1", "锁定_0"})
    private String status;
    // @Excel(name = "爱好", orderNum = "6", width = 20.0)
    // 1. 忽略该字段
    @ExcelIgnore
    private List<String> habits;
    // 2. 定义爱好字符串代替habits
    @Excel(name = "爱好", orderNum = "6", width = 20.0)
    private String habitsStr;

    // 3. 修改get方法
    public String getHabitsStr() {
        StringBuilder sb = new StringBuilder();
        this.habits.forEach(s -> sb.append(s).append("、"));
        return sb.toString();
    }

    // 标识一对一的关系
    @ExcelEntity(name = "card")
    private Card card;
}

测试数据中定义Card数据及导出Excel

package com.easy.demo;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import com.easy.entity.Card;
import com.easy.entity.User;
import org.apache.poi.ss.usermodel.Workbook;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

/**
 * 导出excel
 *
 * @author jingLv
 * @date 2021/01/19
 */
public class ExportExcel {

    public static void main(String[] args) throws IOException {
        //获取数据
        List<User> users = getUsers();
        // 导出excel
        // 参数：exportParams 导出配置对象， 参数2：导出的类型， 参数3：导出数据集合
        Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("用户信息列表", "用户信息"), User.class, users);
        // 将excel写入指定位置
        FileOutputStream outputStream = new FileOutputStream("/Users/apple/JavaProject/operation-excel/ExportExcel.xls");
        workbook.write(outputStream);
        // 关闭流
        outputStream.close();
        workbook.close();
    }

    /**
     * 定义导出数据
     *
     * @return
     */
    public static List<User> getUsers() {
        List<User> users = new ArrayList<>();
        for (int i = 0; i < 5; i++) {
            User user = new User();
            user.setId(String.valueOf(i));
            user.setName("阿花number" + i);
            user.setAge(10 + i);
            user.setBir(new Date());
            if (i % 2 == 0) {
                user.setStatus("0");
                user.setHabits(Arrays.asList("打篮球", "看书", "看片"));
            } else {
                user.setStatus("1");
                user.setHabits(Arrays.asList("喝酒", "抽烟", "烫头"));
            }
            // Card赋值
            Card card = new Card();
            card.setIdCard("123456789");
            card.setAddress("北京朝阳区");
            user.setCard(card);
            users.add(user);

        }
        return users;
    }
}

执行完成查看excel

5.导出一对多关系

往往在业务比较复杂时,我们需要导出一对多关联关系处理,如导出学生课程信息,导出用户的所有订单信息等等….

新增一个订单对象，Order.java

package com.easy.entity;

import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.io.Serializable;

/**
 * @author jingLv
 * @date 2021/01/20
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
@ExcelTarget("orders")
public class Order implements Serializable {

    private static final long serialVersionUID = -8486811488058323527L;

    @Excel(name = "订单编号", orderNum = "9")
    private String id;
    @Excel(name = "订单名称", orderNum = "10")
    private String name;
}

在User对象中定义Order对象集合

package com.easy.entity;

import cn.afterturn.easypoi.excel.annotation.*;
import lombok.Data;

import java.io.Serializable;
import java.util.Date;
import java.util.List;

/**
 * @author jingLv
 * @date 2021/01/19
 */
@Data
@ExcelTarget("users")
public class User implements Serializable {
    private static final long serialVersionUID = -3764618099931012959L;

    @Excel(name = "编号", orderNum = "1")
    private String id;
    @Excel(name = "姓名", orderNum = "2")
    private String name;
    // @Excel(name = "年龄", orderNum = "4", suffix = " $")
    @ExcelIgnore
    private Integer age;
    @Excel(name = "生日", orderNum = "3", width = 20.0, exportFormat = "yyyy年MM月dd日")
    private Date bir;
    @Excel(name = "状态", orderNum = "5", replace = {"激活_1", "锁定_0"})
    private String status;
    // @Excel(name = "爱好", orderNum = "6", width = 20.0)
    // 1. 忽略该字段
    @ExcelIgnore
    private List<String> habits;
    // 2. 定义爱好字符串代替habits
    @Excel(name = "爱好", orderNum = "6", width = 20.0)
    private String habitsStr;

    // 3. 修改get方法
    public String getHabitsStr() {
        StringBuilder sb = new StringBuilder();
        this.habits.forEach(s -> sb.append(s).append("、"));
        return sb.toString();
    }

    // 标识一对一的关系
    @ExcelEntity(name = "card")
    private Card card;

    @ExcelCollection(name = "订单信息", orderNum = "8")
    private List<Order> orders;
}

测试数据中定义Order集合数据及导出Excel

package com.easy.demo;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import com.easy.entity.Card;
import com.easy.entity.Order;
import com.easy.entity.User;
import org.apache.poi.ss.usermodel.Workbook;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

/**
 * 导出excel
 *
 * @author jingLv
 * @date 2021/01/19
 */
public class ExportExcel {

    public static void main(String[] args) throws IOException {
        //获取数据
        List<User> users = getUsers();
        // 导出excel
        // 参数：exportParams 导出配置对象， 参数2：导出的类型， 参数3：导出数据集合
        Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("用户信息列表", "用户信息"), User.class, users);
        // 将excel写入指定位置
        FileOutputStream outputStream = new FileOutputStream("/Users/apple/JavaProject/operation-excel/ExportExcel.xls");
        workbook.write(outputStream);
        // 关闭流
        outputStream.close();
        workbook.close();
    }

    /**
     * 定义导出数据
     *
     * @return
     */
    public static List<User> getUsers() {
        List<User> users = new ArrayList<>();
        for (int i = 0; i < 5; i++) {
            User user = new User();
            user.setId(String.valueOf(i));
            user.setName("阿花number" + i);
            user.setAge(10 + i);
            user.setBir(new Date());
            if (i % 2 == 0) {
                user.setStatus("0");
                user.setHabits(Arrays.asList("打篮球", "看书", "看片"));
            } else {
                user.setStatus("1");
                user.setHabits(Arrays.asList("喝酒", "抽烟", "烫头"));
            }
            // Card赋值
            Card card = new Card();
            card.setIdCard("123456789");
            card.setAddress("北京朝阳区");
            user.setCard(card);
            // 订单赋值
            List<Order> orders = new ArrayList<>();
            orders.add(new Order("12", "超短裙"));
            orders.add(new Order("13", "连衣裙"));
            orders.add(new Order("14", "短裤"));
            user.setOrders(orders);
            users.add(user);
        }
        return users;
    }
}

执行完成查看excel

6.导出图片

往往随着业务不断变化,可能需要在导出excel时将图片信息也一并导出,如商品图标,用户头像信息等数据,这个时候easypoi该如何处理呢?

在实体类中定义头像信息属性，直接写指定图片路径

@ExcelTarget("users")
public class User implements Serializable {
		//.....
    /**
     * 定义头像 直接写指定图片路径
     * type的值一定要指定为2
     */
    @Excel(name = "头像信息", type = 2, orderNum = "0", width = 12, height = 12)
    private String photo;
}

type=2该字段表示类型为图片，imageType=1（默认可以不填），表示从file读取是个字符串类型，可以用相对路径也可以用绝对路径优先依次获取。
type=2该字段表示类型为图片，imageType=2，表示从数据库或者已经读取完毕，字段类型是个字节数组，直接使用，同时，image类型的cell最好设置好宽和高，会百分百缩放cell那么大，不是原尺寸，这里需注意下。

准备图片放入指定路径中,并在测试数据中进行赋值

package com.easy.demo;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import com.easy.entity.Card;
import com.easy.entity.Order;
import com.easy.entity.User;
import org.apache.poi.ss.usermodel.Workbook;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

/**
 * 导出excel
 *
 * @author jingLv
 * @date 2021/01/19
 */
public class ExportExcel {

    public static void main(String[] args) throws IOException {
        //获取数据
        List<User> users = getUsers();
        // 导出excel
        // 参数：exportParams 导出配置对象， 参数2：导出的类型， 参数3：导出数据集合
        Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("用户信息列表", "用户信息"), User.class, users);
        // 将excel写入指定位置
        FileOutputStream outputStream = new FileOutputStream("/Users/apple/JavaProject/operation-excel/ExportExcel.xls");
        workbook.write(outputStream);
        // 关闭流
        outputStream.close();
        workbook.close();
    }

    /**
     * 定义导出数据
     *
     * @return
     */
    public static List<User> getUsers() {
        List<User> users = new ArrayList<>();
        for (int i = 0; i < 5; i++) {
            User user = new User();
            user.setId(String.valueOf(i));
            user.setName("阿花number" + i);
            user.setAge(10 + i);
            user.setBir(new Date());
            if (i % 2 == 0) {
                user.setStatus("0");
                user.setHabits(Arrays.asList("打篮球", "看书", "看片"));
            } else {
                user.setStatus("1");
                user.setHabits(Arrays.asList("喝酒", "抽烟", "烫头"));
            }
            // Card赋值
            Card card = new Card();
            card.setIdCard("123456789");
            card.setAddress("北京朝阳区");
            user.setCard(card);
            // 订单赋值
            List<Order> orders = new ArrayList<>();
            orders.add(new Order("12", "超短裙"));
            orders.add(new Order("13", "连衣裙"));
            orders.add(new Order("14", "短裤"));
            user.setOrders(orders);
            // 图片复制绝对路径
            user.setPhoto("/Users/apple/Documents/关键字01.png");
            users.add(user);
        }
        return users;
    }
}

执行完成，查看excel

7.大数据量导出

大数据导出是当我们的导出数量在几万,到上百万的数据时,一次从数据库查询这么多数据加载到内存然后写入会对我们的内存和CPU都产生压力,这个时候需要我们像分页一样处理导出分段写入Excel缓解Excel的压力

exportBigExcel提供的方法：

/**
  * 大数据量导出
  *
  * @param entity    表格标题属性
  * @param pojoClass Excel对象Class
  */
 public static IWriter<Workbook> exportBigExcel(ExportParams entity, Class<?> pojoClass) {
     ExcelBatchExportService batchServer = new ExcelBatchExportService();
     batchServer.init(entity, pojoClass);
     return batchServer;
 }

 /**
  * 大数据量导出
  *
  * @param entity
  * @param excelParams
  * @return
  */
 public static IWriter<Workbook> exportBigExcel(ExportParams entity, List<ExcelExportEntity> excelParams) {
     ExcelBatchExportService batchServer = new ExcelBatchExportService();
     batchServer.init(entity, excelParams);
     return batchServer;
 }

 /**
  * 大数据量导出
  *
  * @param entity      表格标题属性
  * @param pojoClass   Excel对象Class
  * @param server      查询数据的接口
  * @param queryParams 查询数据的参数
  */
 public static Workbook exportBigExcel(ExportParams entity, Class<?> pojoClass,
                                       IExcelExportServer server, Object queryParams) {
     ExcelBatchExportService batchServer = new ExcelBatchExportService();
     batchServer.init(entity, pojoClass);
     return batchServer.exportBigExcel(server, queryParams);
 }

 /**
  * 大数据量导出
  *
  * @param entity
  * @param excelParams
  * @param server      查询数据的接口
  * @param queryParams 查询数据的参数
  * @return
  */
 public static Workbook exportBigExcel(ExportParams entity, List<ExcelExportEntity> excelParams,
                                       IExcelExportServer server, Object queryParams) {
     ExcelBatchExportService batchServer = new ExcelBatchExportService();
     batchServer.init(entity, excelParams);
     return batchServer.exportBigExcel(server, queryParams);
 }

注意:最好大量数据进行分页处理,每次导出数据最好不要超过1W条记录

导入Excel

1. 导入基本数据

准备导入的目标Excel

注意：日期的格式是2021/1/2 10:47:30 AM

定义导出数据基本对象

package com.easy.entity;

import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import lombok.Data;

import java.io.Serializable;
import java.util.Date;

/**
 * 注意：对象名必须要与excel的列名对应上
 *
 * @author jingLv
 * @date 2021/01/25
 */
@Data
@ExcelTarget("students")
public class Student implements Serializable {

    private static final long serialVersionUID = -4127812440236301876L;

    @Excel(name = "编号")
    private String id;
    @Excel(name = "姓名")
    private String name;
    @Excel(name = "年龄")
    private Integer age;
    @Excel(name = "生日", format = "yyyy年MM月dd日")
    private Date bir;
    @Excel(name = "状态", replace = {"激活_1", "锁定_0"})
    private String status;
}

导入excel中的数据

package com.easy.demo;

import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import com.easy.entity.Student;

import java.io.File;
import java.util.List;

/**
 * @author jingLv
 * @date 2021/01/25
 */
public class ImportExcel {
    public static void main(String[] args) {
        //定义导入参数
        ImportParams importParams = new ImportParams();
        //导出数据 餐数1:当如excel文件  参数2:导入对象的类型 参数3:导入参数配置
        List<Student> students = ExcelImportUtil.importExcel(new File("/Users/apple/JavaProject/operation-excel/ExportExcel.xls"), Student.class, importParams);
        students.forEach(System.out::println);
    }
}

执行并查看结果

2. 导入小技巧

读取指定的sheet
比如要读取上传得第二个sheet 那么需要把startSheetIndex = 1 就可以了
读取几个sheet
比如读取前2个sheet,那么 sheetNum=2 就可以了
读取第二个到第五个sheet
设置 startSheetIndex = 1 然后sheetNum = 4
读取全部的sheet
sheetNum 设置大点就可以了
判断一个Excel是不是合法的Excel
importFields 设置下值,就是表示表头必须至少包含的字段,如果缺一个就是不合法的excel,不导入

3. 带图片导入

有图片的导出就有图片的导入,导入的配置和导出是一样的,但是需要设置保存路径

准备数据

定义导出数据基本对象，图片的列需要设置保存路径

package com.easy.entity;

import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import lombok.Data;

import java.io.Serializable;
import java.util.Date;

/**
 * 注意：对象名必须要与excel的列名对应上
 *
 * @author jingLv
 * @date 2021/01/25
 */
@Data
@ExcelTarget("students")
public class Student implements Serializable {

    private static final long serialVersionUID = -4127812440236301876L;

    @Excel(name = "编号")
    private String id;
    @Excel(name = "姓名")
    private String name;
    @Excel(name = "年龄")
    private Integer age;
    @Excel(name = "生日", format = "yyyy年MM月dd日")
    private Date bir;
    @Excel(name = "状态", replace = {"激活_1", "锁定_0"})
    private String status;
    @Excel(name = "头像", type = 2, savePath = "/Users/apple/JavaProject/operation-excel/easy-excel/image")
    private String photo;
}

type：类型为2是为图片类型
savePath：设置保存图片的位置

导入excel

package com.easy.demo;

import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import com.easy.entity.Student;

import java.io.File;
import java.util.List;

/**
 * @author jingLv
 * @date 2021/01/25
 */
public class ImportExcel {
    public static void main(String[] args) {
        //定义导入参数
        ImportParams importParams = new ImportParams();
        // 导入时校验数据模板,是不是正确的Excel
        importParams.setImportFields(new String[]{"生日"});
        //是否保存上传的excel
        importParams.setNeedSave(false);
        importParams.setSaveUrl("/Users/apple/JavaProject/operation-excel/easy-excel/image");
        //导出数据 餐数1:当如excel文件  参数2:导入对象的类型 参数3:导入参数配置
        List<Student> students = ExcelImportUtil.importExcel(new File("/Users/apple/JavaProject/operation-excel/ExportExcel.xls"), Student.class, importParams);
        students.forEach(System.out::println);
    }
}

执行测试及结果

easyexcel操作Excle