SpringBoot 整合eazyPoi 4.3.0 数据导入导出

• 依赖
• 注解用法说明
• • @ExcelTarget 绑定Id
  • @Excel 用在字段上面
  • @ExcelCollection
  • 实体代码
• Excel导出
• • 正常数量导出(1-2W条)
  • 大数据量导出
  • 多sheet导出
  • 单元格超链接
• Excel导入
• • importParams参数
  • 正常导入 importExcel
  • 大数据量 importExcelBySax
  • 异步
  • 读取合并的表头
  • 验证导入(筛出符合与不符合的数据)
  • 表头的groupName
  • 导入图片
  • CSV导入
  • excel转换csv
• Excel转html

    cn.afterturn
    easypoi-spring-boot-starter
    4.3.0

• @Excel 用在字段上面
• @ExcelCollection 表示根对象中的list
• @ExcelEntity 表示根对象中的实体
• @ExcelIgnore 忽略导出
• @ExcelTarget 绑定Id

解决了相同的实体去映射Excel 但是表头名字不太相同 实现根据班级不同映射不同名字

这里的@ExcelTarget 表示使用X这个对象是可以针对不同字段做不同处理
同样的ExcelEntity 和ExcelCollection 都支持这种方式

看下如何使用 在下面有完全代码可以copy

@Data
@ExcelTarget("user1")
@Accessors(fluent = true)
public class TestBean implements Serializable {
    @Excel(name = "A班级编号_user1,B班级编号_user2" ,//表头名称
            width = 15.0, //宽度
            orderNum = "0", //排序 这个排序在@ExcelEntity中也可以连贯使用  ExcelCollection内的Excel不可以连贯使用
            needMerge = true)
    private Integer id;
    @Excel(name = "姓名",width = 15.0,orderNum = "1",needMerge = true)
    private String name;
    @Excel(name = "性別",
            replace = {"男_1","女_2"}, //字符替换  源实1代表男  2代表女
            suffix = "生",//添加后缀
            orderNum = "2",needMerge = true)
    private int sex;
    @Excel(name = "生日", databaseFormat = "yyyyMMddHHmmss",//如果是字符串的话>>同步数据库格式
            format = "yyyy-MM-dd HH:mm:ss",//设置导入导出格式
            orderNum = "3",
            needMerge = true)
    private Date bir;
    @ExcelIgnore //不生成数据
    @Excel(name = "生日2", databaseFormat = "yyyy-MM-dd HH:mm:ss",format = "yyyy-MM-dd HH:mm:ss",orderNum = "4")
    private Date bir2;
    @Excel(name = "照片",type = 2,//设置数据类型 2是图片
            imageType = 1,//图片使用方式
            orderNum = "8",width = 30,height = 30)
    private String img;
    @Excel(name = "照片2",type = 2,imageType = 2,orderNum = "8",width = 30,height = 30)
    private byte[] img1;

    @ExcelEntity  //表示这是一个实体  其实相当于在根实体中添加其他字段的感觉
    private Card card;

    @ExcelCollection(name = "物品",orderNum = "7")//这个集合会在这个对象内循环遍历..之前的字段数据只在第一行显示
    private List orders;

3个实体

@Data
@ExcelTarget("user1")
@Accessors(fluent = true)
public class TestBean implements Serializable {
    public static String imgPath="imgs/company/baidu.png";
    @Excel(name = "A班级编号_user1,B班级编号_user2" ,//表头名称
            width = 15.0, //宽度
            orderNum = "0", //排序 这个排序在@ExcelEntity中也可以连贯使用  ExcelCollection内的Excel不可以连贯使用
            needMerge = true)
    private Integer id;
    @Excel(name = "姓名",width = 15.0,orderNum = "1",needMerge = true)
    private String name;
    @Excel(name = "性別",
            replace = {"男_1","女_2"}, //字符替换  源实1代表男  2代表女
            suffix = "生",//添加后缀
            orderNum = "2",needMerge = true)
    private int sex;
    @Excel(name = "生日", databaseFormat = "yyyyMMddHHmmss",//如果是字符串的话>>同步数据库格式
            format = "yyyy-MM-dd HH:mm:ss",//设置导入导出格式
            orderNum = "3",
            needMerge = true)
    private Date bir;
    @ExcelIgnore //不生成数据
    @Excel(name = "生日2", databaseFormat = "yyyy-MM-dd HH:mm:ss",format = "yyyy-MM-dd HH:mm:ss",orderNum = "4")
    private Date bir2;
    @Excel(name = "照片",type = 2,//设置数据类型 2是图片
            imageType = 1,//图片使用方式
            orderNum = "8",width = 30,height = 30)
    private String img;
    @Excel(name = "照片2",type = 2,imageType = 2,orderNum = "8",width = 30,height = 30)
    private byte[] img1;

    @ExcelEntity  //表示这是一个实体  其实相当于在根实体中添加其他字段的感觉
    private Card card;

    @ExcelCollection(name = "物品",orderNum = "7")//这个集合会在这个对象内循环遍历..之前的字段数据只在第一行显示
    private List orders;

    //获取10条 完整数据
    public static List getListUser(){
        List list=new ArrayList<>();
        for (int i = 1; i <= 10; i++) {
            list.add(new TestBean().id(i).name("姓名"+i).bir(new Date()).sex(i%2==0?1:2).card(new Card())
            .orders(getOrder())
                    .img(imgPath));
        }
        return list;
    }
    //只获取根对象数据  没有图片,list,实体
    public static List getBigListFor10000(int page){
        List list=new ArrayList<>();
        for (int i = page; i <= page*10000; i++) {
            list.add(new TestBean().id(i).name("姓名"+i).bir(new Date()).sex(i%2==0?1:2));
        }
        return list;
    }
    public static List getOrder(){
        List list=new ArrayList<>();
        for (int i = 1; i <=3 ; i++) {
            list.add(new Order().id(i).name("物品"+i));
        }
        return list;
    }



    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getSex() {
        return sex;
    }

    public void setSex(int sex) {
        this.sex = sex;
    }

    public Date getBir() {
        return bir;
    }

    public void setBir(Date bir) {
        this.bir = bir;
    }

    public Date getBir2() {
        return bir2;
    }

    public void setBir2(Date bir2) {
        this.bir2 = bir2;
    }

    public String getImg() {
        return img;
    }

    public void setImg(String img) {
        this.img = img;
    }

    public byte[] getImg1() {
        return img1;
    }

    public void setImg1(byte[] img1) {
        this.img1 = img1;
    }

    public Card getCard() {
        return card;
    }

    public void setCard(Card card) {
        this.card = card;
    }

    public List getOrders() {
        return orders;
    }

    public void setOrders(List orders) {
        this.orders = orders;
    }
    //校验文件目录
    public static String existsDir(){
        String dir="D:/eazyPoi/";
        File savefile = new File(dir);
        if (!savefile.exists()) {
            savefile.mkdirs();
        }
        return dir;
    }


}

@Accessors(fluent = true)
@ExcelTarget("card1")
@Data
public class Card{
    @Excel(name = "身份证",orderNum = "5")
    private String id ;
    @Excel(name = "地址",orderNum = "6")
    private String addr;
    public Card() {
        this.id = "123212321";this.addr = "北京市朝阳区";
    }

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getAddr() {
        return addr;
    }

    public void setAddr(String addr) {
        this.addr = addr;
    }
}

@Accessors(fluent = true)
@ExcelTarget("order1")
@Data
public class Order{
    @Excel(name = "序号",orderNum = "1")
    public int id ;
    @Excel(name = "物品",orderNum = "2")
    private String name;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

package com.ruoyi.web.eazyPoi;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.handler.inter.IExcelExportServer;
import cn.afterturn.easypoi.handler.inter.IWriter;
import org.apache.poi.hssf.record.DVALRecord;
import org.apache.poi.ss.usermodel.Workbook;


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

public class Test {


    public static void main(String[] args) throws Exception {
        Test.exportExcel();
    }

    //1万条以下的方法
    public static void exportExcel()throws Exception{
        ExportParams exportParams=new ExportParams("用戶信息表", "用戶信息");
        List listUser = TestBean.getListUser();
        Workbook sheets = ExcelExportUtil.exportExcel(exportParams, TestBean.class, listUser);
        sheets.write(new FileOutputStream(TestBean.existsDir()+System.currentTimeMillis() +".xls"));
        sheets.close();
    }

}

这里面的这个@Override是一个循环函数,obj是外层传的queryParams(参4)但是page会一直循环到>obj 比比如我们分10页的数据 page会循环超过10

• sql条件分页到最后一页后查询为

public class Test {


    public static void main(String[] args) throws Exception {
        Test.exportExcel();
    }

    public static void exportBigExcel()throws Exception{
        Workbook     workbook = null;
        Date         start    = new Date();
        ExportParams exportParams=new ExportParams("用戶信息表", "用戶信息");
        workbook = ExcelExportUtil.exportBigExcel(exportParams, TestBean.class, new IExcelExportServer() {
            
            @Override
            public List