SpringBoot 整合eazyPoi 4.3.0 Excel数据导入导出(持续更新功能)

最新推荐文章于 2024-05-20 12:59:12 发布

矮小的

最新推荐文章于 2024-05-20 12:59:12 发布

阅读量1w

点赞数 3

本文链接：https://blog.csdn.net/crazyo2jam/article/details/120225038

版权

java 同时被 2 个专栏收录

41 篇文章 0 订阅

订阅专栏

Spring

22 篇文章 0 订阅

订阅专栏

依赖

文档

<!-- https://mvnrepository.com/artifact/cn.afterturn/easypoi-spring-boot-starter -->
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-spring-boot-starter</artifactId>
    <version>4.3.0</version>
</dependency>

注解用法说明

在这里插入图片描述

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

@ExcelTarget 绑定Id

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

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

@Excel 用在字段上面

属性	类型	默认值	功能
name	String	null	列名,支持ExcelTarget
needMerge	boolean	fasle	纵向合并单元格(用于含有list中,单个的单元格,合并list创建的多个row)
orderNum	String	“0”	列的排序,支持ExcelTarget
replace	String[]	{}	字符替换导出是{“替换值_源”,“替换值_源”}
savePath	String	“upload”	导入文件保存路径,如果是图片可以填写,默认是upload/className/ IconEntity这个类对应的就是upload/Icon/
type	int	1	导出类型 1 是文本 2 是图片,3 是函数,10 是数字默认是文本
width	double	10	列宽
height	double	10	列高,后期打算统一使用@ExcelTarget的height,这个会被废弃,注意
isStatistics	boolean	fasle	自动统计数据,在追加一行统计,把所有数据都和输出[这个处理会吞没异常,请注意这一点]
isHyperlink	boolean	FALSE	超链接,如果是需要实现接口返回对象
isImportField	boolean	TRUE	校验字段,看看这个字段是不是导入的Excel中有,如果没有说明是错误的Excel,读取失败,支持ExcelTarget
exportFormat	String	“”	导出的时间格式,以这个是否为空来判断是否需要格式化日期
importFormat	String	“”	导入的时间格式,以这个是否为空来判断是否需要格式化日期
format	String	“”	时间格式,相当于同时设置了exportFormat 和 importFormat
databaseFormat	String	“yyyyMMddHHmmss”	导出时间设置,如果字段是Date类型则不需要设置数据库如果是string 类型,这个需要设置这个数据库格式,用以转换时间格式输出
numFormat	String	“”	数字格式化,参数是Pattern,使用的对象是DecimalFormat
imageType	int	1	导出类型 1 从file读取 2 是从数据库中读取默认是文件同样导入也是一样的
suffix	String	“”	文字后缀,如值实90 suffix="%" 变成90%
isWrap	boolean	TRUE	是否换行即支持\n
mergeRely	int[]	{}	合并单元格依赖关系,比如第二列合并是基于第一列则{0}就可以了
mergeVertical	boolean	fasle	纵向合并内容相同的单元格
fixedIndex	int	-1	对应excel的列,忽略名字
isColumnHidden	boolean	FALSE	导出隐藏列

看下如何使用 在下面有完全代码可以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<Order> orders;

@ExcelCollection

属性	类型	默认值	功能
id	String	null	定义ID
name	String	null	定义集合列名,支持ExcelTarget
orderNum	int	0	排序,支持ExcelTarget
type	Class<?>	ArrayList.class	导入时创建对象使用

实体代码

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<Order> orders;

    //获取10条 完整数据
    public static List<TestBean> getListUser(){
        List<TestBean> 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<TestBean> getBigListFor10000(int page){
        List<TestBean> 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<Order> getOrder(){
        List<Order> 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<Order> getOrders() {
        return orders;
    }

    public void setOrders(List<Order> 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;
    }
}

Excel导出

正常数量导出(1-2W条)

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<TestBean> 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() {
            /**
             * @param obj   总页数
             * @param page  当前页数
             * @return
             */
            @Override
            public List<Object> selectListForExcelExport(Object obj, int page) {
                //page是从1开始递增**(应该做成外面传几就截止到几但并不是回直加)所以手动限制下
                if((int)obj==page){return null;};
                //最后返回的表格对象
                List<Object> listUser = new ArrayList<>();
                //模拟业务分页查询
                listUser.addAll(TestBean.getBigListFor10000(page));
                return listUser;
            }
        },10);
        workbook.write(new FileOutputStream(TestBean.existsDir()+System.currentTimeMillis() +".xlsx"));
        workbook.close();
    }
    
}

多sheet导出

    public void moveSheet(){
        //sheet集合信息
        List<Map<String,Object>> sheetList=new ArrayList<Map<String,Object>>();
        //循环方式创建2个sheet
        for (int i = 0; i <2 ; i++) {
            //获取数据
            List<TestBean> listUser = TestBean.getListUser();
            //封装sheet信息
            Map<String,Object> mapData=new HashMap<String, Object>();
            ExportParams params = new ExportParams();
            //sheet名称
            params.setSheetName("学生信息表"+i);
            mapData.put("title",params);//表格信息
            mapData.put("entity",TestBean.class);//实体类
            mapData.put("data",listUser);//列表集
            sheetList.add(mapData);//sheet集合
        }
        //导出
        Workbook workbook = ExcelExportUtil.exportExcel(sheetList, ExcelType.XSSF);
        FileOutputStream fos = null;
        try {
            fos = new FileOutputStream("D:/home/excel/测试多Sheet.xls");
            workbook.write(fos);
            fos.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

单元格超链接

@Data
public class HyperLinkEntity {
    @Excel(name = "名称", isHyperlink = true)
    private String name;
    @Excel(name = "URL")
    private String url;
    @Excel(name="备注")
    private String comment;
}

    @Test
    public void test() throws Exception {
        //数据集合
        List<HyperLinkEntity> list = new ArrayList<HyperLinkEntity>();
        HyperLinkEntity client = new HyperLinkEntity();
        client.setName("百度");
        client.setUrl("https://www.baidu.com/");
        client.setComment("XXXXXXXXXX");
        list.add(client);
        client = new HyperLinkEntity();
        client.setName("新浪");
        client.setUrl("http://www.sina.com.cn/");
        client.setComment("XXXXXXXXXX");
        list.add(client);

        //表格信息
        ExportParams params = new ExportParams("超链接测试", "超链接测试", ExcelType.XSSF);
        //设置拦截
        params.setDataHandler(new ExcelDataHandlerDefaultImpl() {
            /**
             * 获取这个字段的 Hyperlink ,07版本需要,03版本不需要
             * @param obj 设置的注解实体
             * @param name 注释name属性
             * @param value 字段值
            */
            @Override
            public Hyperlink getHyperlink(CreationHelper creationHelper, Object obj, String name, Object value) {
                Hyperlink link = creationHelper.createHyperlink(HyperlinkType.URL);
                System.out.println(name);
                System.out.println(value);
                HyperLinkEntity e = (HyperLinkEntity) obj;
                link.setAddress(e.getUrl());//超链接地址
                link.setLabel(e.getName());//返回此超链接的文本标签
                HyperlinkType type = link.getType();//获取超链接类型HyperlinkType枚举
                return link;
            }
        });
        Workbook workbook = ExcelExportUtil.exportExcel(params, HyperLinkEntity.class, list);
        FileOutputStream fos = new FileOutputStream("D:/home/excel/ExcelExportForLink.xlsx");
        workbook.write(fos);
        fos.close();
    }

非注解导出(ExcelExportEntity+Map)

        //表头信息List 相当于@Excel
        List<ExcelExportEntity> entity = new ArrayList<ExcelExportEntity>();
        //设置字段信息
        for (int i = 1; i <=5 ; i++) {
            ExcelExportEntity exportEntity=new ExcelExportEntity("姓名","name"+i);
            exportEntity.setWidth(8.0);//表格宽度
            exportEntity.setHeight(4.0);//表格高度
            entity.add(exportEntity);
        }
        //数据列表
        List<Map<String,Object>> list=new ArrayList<>();
        for (int i = 0; i <100 ; i++) {
            Map<String,Object> map=new HashMap<>();
            map.put("name1",i);
            map.put("name2",i);
            map.put("name3",i);
            map.put("name4",i);
            map.put("name5",i);
            list.add(map);
        }
        Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("测试", "测试"), entity, list);
        FileOutputStream fos = new FileOutputStream("D:/home/excel/ExcelExportForMap"+System.currentTimeMillis()+".xls");
        workbook.write(fos);
        fos.close();

统计(数值)功能

在这里插入图片描述

设置字典dict&下拉选项addressList

@Accessors(fluent = true)
@ExcelTarget("card1")
@Data
@AllArgsConstructor
public class Card{
    @Excel(name = "name")
    private String name ;
    @Excel(name = "性别",orderNum = "2",dict = "sexType" ,addressList = true)
    private Integer sex;
    @Excel(name = "证件类型",orderNum = "1",dict = "cardType")
    private String type;
    @Excel(name = "证件编号")
    private String id ;
}

实现IExcelDictHandler

  List<Card> list=new ArrayList<>();
        list.add(new Card("姓名1",1,"sfz","112233"));
        list.add(new Card("姓名2",2,"hz","112233"));
        ExportParams exportParams = new ExportParams("title", "sheet");
        exportParams.setDictHandler(new IExcelDictHandler(){
            //注解使用 addressList属性
            @Override
            public List<Map> getList(String dict) {
                List<Map>           list    = new ArrayList<>();
                Map<String, String> dictMap = new HashMap<>();
                if(dict.equals("sexType")){
                    dictMap = new HashMap<>();
                    dictMap.put("dictKey", "1");
                    dictMap.put("dictValue", "男");
                    list.add(dictMap);
                    dictMap = new HashMap<>();
                    dictMap.put("dictKey", "2");
                    dictMap.put("dictValue", "女");
                    list.add(dictMap);
                }
                return list;
            }

            /** 注解dict属性
             * @param dict  dict名称
             * @param obj   注解对象
             * @param name  注解name属性
             * @param value 值
             * @return
             */
            @Override
            public String toName(String dict, Object obj, String name, Object value) {
                if("cardType".equals(dict)){
                    if ("sfz".equals(value)) {
                        return "身份证";
                    } else if ("hz".equals(value)) {
                        return "护照";
                    }
                }
                if("sexType".equals(dict)){
                    if ("1".equals(value.toString())) {
                        return "男";
                    } else if ("2".equals(value.toString())) {
                        return "女";
                    }
                }
                return null;
            }
            @Override
            public String toValue(String s, Object o, String s1, Object o1) {
                return null;
            }
        });
        Workbook sheets = ExcelExportUtil.exportExcel(exportParams, Card.class, list);
        FileOutputStream fos = null;

            fos = new FileOutputStream("D:/home/excel/ExcelExportForMap"+System.currentTimeMillis()+".xls");
            sheets.write(fos);
            fos.close();

在这里插入图片描述

Excel导入

ImportParams参数

值	类型	默认值	功能
titleRows	int	0	表格标题行数,默认0
headRows	int	1	表头行数,默认1
startRows	int	0	设置开始读取的位置,也就是从标题下0为全部 1读取第一行以后的数据
keyIndex	int	0	主键设置,如何这个cell没有值,就跳过或者认为这个是list的下面的值,这一列必须有值,不然认为这列为无效数据
startSheetIndex	int	0	与sheetNum两个配合使用在多个sheet时有10个sheet我们想读取1-4个sheet
sheetNum	int	1	上传表格需要读取的sheet 数量,默认为1
needSave	boolean	FALSE	是否需要保存上传的Excel
needVerfiy	boolean	FALSE	是否需要校验上传的Excel
saveUrl	String	“upload/excelUpload”	保存上传的Excel目录,默认是如 TestEntity这个类保存路径就是upload/excelUpload/Test/yyyyMMddHHmss_ 保存名称上传时间_五位随机数
verifyHanlder	IExcelVerifyHandler	null	校验处理接口,自定义校验
lastOfInvalidRow	int	0	最后的无效行数,不读的行数
readRows	int	0	手动控制读取的行数实测设置head后 2=第一行数据
importFields	String[]	null	excel文件中必须要有的字段,必须要有这些数据
keyMark	String	“:”	Key-Value 读取标记,以这个为Key,后面一个Cell 为Value,多个改为ArrayList
readSingleCell	boolean	FALSE	按照Key-Value 规则读取全局扫描Excel,但是跳过List读取范围提升性能,仅仅支持titleRows + headRows + startRows 以及 lastOfInvalidRow
dataHanlder	IExcelDataHandler	null	数据处理接口,以此为主,replace,format都在这后面

正常导入 importExcel

需要把实体里面图片的字段的Excel注释掉

    //普通正常表格
    public void importExcel1() {
        ImportParams params = new ImportParams();
        params.setTitleRows(1);
        params.setHeadRows(1);
        params.setStartRows(0);//设置开始读取的位置,也就是从标题下0为全部  1读取第一行以后的数据
//        params.setKeyIndex(1);//
//        params.setStartSheetIndex(0); params.setSheetNum(1);//两个配合使用  在多个sheet时   有10个sheet我们想读取1-4个sheet
//        params.setReadRows(3);//实测设置head后 2=第一行数据
        params.setImportFields(new String[]{"姓名","性別"});//excel文件中必须要有的字段,必须要有这些数据
//        params.setReadRows(4);
        File file = new File(getWebRootPath("import/B.xlsx"));
        List<TestBean> list = ExcelImportUtil.importExcel(file,TestBean.class, params);
        list.stream().forEach(i-> System.out.println(i.toString()));
    }

大数据量 importExcelBySax

    //导入大量数据
    public void importExcelBySax() throws FileNotFoundException {
        /**
         需要添加依赖  实现xml解析的jar包
         <dependency>
         <groupId>xerces</groupId>
         <artifactId>xercesImpl</artifactId>
         <version>2.9.1</version>
         </dependency>
         */
        ImportParams params = new ImportParams();
        params.setTitleRows(1);
        List<TestBean> list=new ArrayList<>();
        ExcelImportUtil.importExcelBySax(
                new FileInputStream(
                        new File(getWebRootPath("import/ExcelExportMsgClient.xlsx"))),
                TestBean.class, params, new IReadHandler<TestBean>() {
                    @Override//读取的每一行
                    public void handler(TestBean o) {
                        list.add(o);
                        System.out.println(ReflectionToStringBuilder.toString(o));
                    }
                    @Override
                    public void doAfterAll() {
                        System.out.println("数据执行完毕的处理");
                    }
                });
    }

异步

从官方的demo中看得出没快哪里去

    public void asyncTask(){
        ImportParams params =new ImportParams();
        params.setHeadRows(1);
        params.setTitleRows(1);
        params.setConcurrentTask(true);//开启异步
        params.setCritical(500);
        List<TestBean> objects = ExcelImportUtil.importExcel(
                new File(getWebRootPath("import/B.xlsx")),
                TestBean.class,
                params
        );
        objects.stream().forEach(i-> System.out.println(i.toString()));
    }

读取合并的表头

在这里插入图片描述

    //普通正常表格
    public void importExcel1() {
        ImportParams params = new ImportParams();
        params.setTitleRows(1);//标题1行
        params.setHeadRows(2);//表头一共是2行
        File file = new File(getWebRootPath("import/B.xlsx"));
        List<TestBean> list = ExcelImportUtil.importExcel(file,TestBean.class, params);
        list.stream().forEach(i-> System.out.println(i.toString()));
    }

在这里插入图片描述

验证导入(筛出符合与不符合的数据)

官方给出挺多的demo,有点繁琐,觉得这个是比较实用的
在实体类中添加hibernate的字段验证注解
这个不需要额外引入依赖
主要验证这3个位置

在这里插入图片描述

    public void validFieldImport() throws IOException {
        ImportParams params=new ImportParams();
        params.setTitleRows(1);
        params.setHeadRows(2);
        params.setNeedVerify(true);//设置需要验证
        params.setVerifyGroup(new Class[]{ValidGroup.class});//设置验证分组
        ExcelImportResult<TestBean> importResult = ExcelImportUtil.importExcelMore(new File(getWebRootPath("import/B.xlsx")), TestBean.class, params);
        //符合验证
        List<TestBean> list = importResult.getList();
        //不符合验证
        List<TestBean> failList = importResult.getFailList();
        //获取map
        Map<String, Object> map = importResult.getMap();
        //导出验证的数据
        importResult.getWorkbook().write(new FileOutputStream("D:/eazyPoi/validExcel.xlsx"));
        //不符合的数据
        importResult.getFailWorkbook().write(new FileOutputStream("D:/eazyPoi/failValidExcel.xlsx"));
        System.out.println("是否验证失败"+importResult.isVerifyFail());
    }

bir 字段没有加验证分组所以不参与验证

失败导出的excel会有提示

表头的groupName

在这里插入图片描述

导入图片

    @Excel(name = "公司LOGO", type = 2 ,width = 40 , height = 30,imageType = 1,
            savePath="D:\\javaProject\\")
    private String companyLogo;

在这里插入图片描述

CSV导入

新建C.csv文件

id,name,sex,bir
1,小明,1,1991-08-08
2,小张,1,1991-09-08
3,小红,2,1991-09-08

public  void importCSV()throws Exception{
       Date start = new Date();
       CsvImportParams params = new CsvImportParams(CsvImportParams.UTF8);
       CsvImportUtil.importCsv(new FileInputStream(
                       new File(getWebRootPath("import/C.csv"))),
               Map.class, params, new IReadHandler<Map>() {
                   @Override
                   public void handler(Map o) {
                       System.out.println(JSON.toJSONString(o));
                   }

                   @Override
                   public void doAfterAll() {

                   }
               });
}

在这里插入图片描述

excel转换csv

Excel

    public void excel2Csv()throws Exception{
        //输出的CSV
        FileOutputStream fos    = new FileOutputStream("D:\\eazyPoi\\aa.csv");
        ImportParams     params = new ImportParams();
        params.setTitleRows(1);
        params.setHeadRows(1);
        CsvExportParams csvExportParams = new CsvExportParams();
        csvExportParams.setEncoding(CsvExportParams.GBK);//可使用UTF8
        IWriter ce = CsvExportUtil.exportCsv(csvExportParams, TestBean.class, fos);
        ExcelImportUtil.importExcelBySax(
                new FileInputStream(
                        new File(getWebRootPath("import/B.xlsx"))),
                TestBean.class, params, new IReadHandler<TestBean>() {
                    private List<TestBean> list = new ArrayList<>();
                    @Override
                    public void handler(TestBean o) {
                        //读取后添加到list
                        list.add(o);
                    }
                    @Override
                    public void doAfterAll() {
                        //读取结束后转换输出
                        ce.write(list);
                        list.clear();
                        System.out.println("succcess--------------------------------");
                    }
                });
    }

CSV 红框为实体类注解字段

Excel转html

在这里插入图片描述

    public void testToAllHtmlWorkbookAndImage()throws Exception{
        Workbook wb = new XSSFWorkbook(new FileInputStream(new File(getWebRootPath("import/B.xlsx"))));
        String     html = ExcelXorHtmlUtil.excelToHtml(new ExcelToHtmlParams(wb, true, "D:\\eazyPoi\\"));
        FileWriter fw   = new FileWriter("D:/eazyPoi/B.html");
        fw.write(html);
        fw.close();
    }

在这里插入图片描述

ExportParams 表格参数配置

构造方法

ExportParams() 
ExportParams(String 标题, String sheet名字) 
ExportParams(String 标题, String sheet名字, ExcelType ecxel类型枚举) 
ExportParams(String 标题, String 第二表头名字, String sheet名字)

setFreezeCol 横向滑动左侧固定列

Excel 模板功能

基本功能

public void three()throws Exception{
        TemplateExportParams params = new TemplateExportParams(
                "doc/test.xlsx", 0);
        params.setColForEach(true);
        Map<String, Object> map = new HashMap<>();
        map.put("int", 10);
        map.put("double", 3.1415926);
        map.put("strNum", "10.2525");
        map.put("str", "这是一段文字!");
        map.put("date", new Date());
        map.put("bigNum",88888888);
        map.put("list",getList());
        map.put("user",getUser());
        ImageEntity image = new ImageEntity();
        image.setHeight(200);
        image.setWidth(500);
        image.setUrl("imgs/company/baidu.png");
        map.put("img",image);

        Workbook book = ExcelExportUtil.exportExcel(params, map);
        FileOutputStream fos = new FileOutputStream("D:/home/excel/test"+System.currentTimeMillis()+".xlsx");
        book.write(fos);
        fos.close();
    }

    public  List<Map<String,Object>> getList(){
        List<Map<String,Object>> list=new ArrayList<>();
            Map<String,Object> maps=new LinkedHashMap<>();
            maps.put("name","李明");
            maps.put("sex","男");
            maps.put("title1","姓名1");
            maps.put("title2","性别1");
            maps.put("field1","t.name");
            maps.put("field2","t.sex");
            list.add(maps);
            maps=new LinkedHashMap<>();
            maps.put("name","小红");
            maps.put("sex","女");
            maps.put("title1","姓名2");
            maps.put("title2","性别2");
            maps.put("field1","t.name");
            maps.put("field2","t.sex");
            list.add(maps);
            maps=new LinkedHashMap<>();
            maps.put("name","小朵");
            maps.put("sex","女");
            maps.put("title1","姓名3");
            maps.put("title2","性别3");
            maps.put("field1","t.name");
            maps.put("field2","t.sex");
            list.add(maps);
        return list;
    }

{{}} 输出

变量输出使用{{变量名称}} 是不是很像Vue
在这里插入图片描述

{{n:}} 数值类型

数值类型的String使用{{n:objName}}
在这里插入图片描述

时间格式化 & str长度 & 三目运算 & 保留小数

在这里插入图片描述

遍历的3种区别 {{fe:}}{{!fe:}}{{$fe:}}

便利格式
在这里插入图片描述

{{!fe:}} 不创建行,直接向下遍历
{{fe:}} 不创建行,除第一行外,覆盖所有数据行
{{$fe:}} 创插入新建行,除第一行外

{{#fe:}}{{v_fe:}} 横向遍历

    public  List<Map<String,Object>> getList(){
        List<Map<String,Object>> list=new ArrayList<>();
            Map<String,Object> maps=new LinkedHashMap<>();
            maps.put("name","李明");
            maps.put("sex","男");
            maps.put("title1","姓名1");
            maps.put("title2","性别1");
            maps.put("field1","t.name");
            maps.put("field2","t.sex");
            list.add(maps);
            maps=new LinkedHashMap<>();
            maps.put("name","小红");
            maps.put("sex","女");
            maps.put("title1","姓名2");
            maps.put("title2","性别2");
            maps.put("field1","t.name");
            maps.put("field2","t.sex");
            list.add(maps);
            maps=new LinkedHashMap<>();
            maps.put("name","小朵");
            maps.put("sex","女");
            maps.put("title1","姓名3");
            maps.put("title2","性别3");
            maps.put("field1","t.name");
            maps.put("field2","t.sex");
            list.add(maps);
        return list;
    }

在这里插入图片描述

word模板

在这里插入图片描述

    public void testDoc() throws IOException {
        Map<String,Object> map=new HashMap<>();
        map.put("title","请假单");
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String curTime = format.format(new Date());
        map.put("now",curTime);
        map.put("name","小明");
        map.put("day",2);
        List<Map<String,Object>> list=new ArrayList<>();
        for (int i = 0; i <2 ; i++) {
            Map<String,Object> map1=new HashMap<>();
            map1.put("date","2020-01-01");
            map1.put("type","全天");
            //使用base64图片方式
            String img="iVBORw0KGgoAAAANSUhEUgAAAkwAAAFLCAIAAACr60SxAAAACXBIWXMAABJ0AAASdAHeZh94AAAAEXRFWHRTb2Z0d2FyZQBTbmlwYXN0ZV0Xzt0AACAASURBVHic7d15cFRV9sDxyxYgIQlJOgTCKpiwCkGHVZBNkZ1REYFBkEG0LMbSUqFGcMZhnAUVGJfRGkGUGkdFKBxQlCiCAgOoCAKy7yAQCFnIAiRhye+PY53q3+sQCOlOv775fv6w2k7SvLzuvPPuueeeW6W4uNgAAGCjqsE+AAAAAoUgBwCwFkEOAGAtghwAwFoEOQCAtQhyAABrEeQAANYiyAEArEWQAwBYiyAHALAWQQ4AYC2CHADAWgQ5AIC1CHIAAGsR5AAA1iLIAQCsRZADAFiLIAcAsBZBDgBgLYIcAMBaBDkAgLUIcgAAaxHkAADWIsgBAKxFkAMAWIsgBwCwFkEOAGAtghwAwFoEOQCAtQhyAABrEeQAANYiyAEArEWQAwBYiyAHALAWQQ4AYC2CHADAWgQ5AIC1CHIAAGsR5AAA1iLIAQCsRZADAFiLIAcAsBZBDgBgLYIcAMBaBDkAgLUIcgAAaxHkAADWIsgBAKxFkAMAWIsgBwCwFkEOAGAtghwAwFoEOQCAtQhyAABrEeQAANYiyAEArEWQAwBYiyAHALAWQQ4AYC2CHADAWgQ5AIC1CHIAAGsR5AAA1iLIAQCsRZADAFiLIAcAsBZBDgBgLYIcAMBaBDkAgLUIcgAAaxHkAADWIsgBAKxFkAMAWIsgBwCwFkEOAGAtghwAwFoEOQCAtQhyAABrEeQAANYiyAEArEWQAwBYq3qwDwBusWbNmqKiomt+W+/evWvUqFEBxwMA5UeQwy9OnTpVWFh4zW+7cuVKBRwMAPgF6UoAgLWqFBcXB/sYEHD/+te/Lly4YIzZvHnzpUuXjNeATD8Ap0+fvp5RWnJycrVq1Ywxc+bMiYyMDNQRA4A/kK6sFPbu3Zufn2+MWbdunUy8Xb58Wb6kQa5KlSq+P+j71czMzKpVqxpjJFgCgJuRrgQAWIuRXEjScdi2bdtkRPXGG28UFBRc7fv37NkjP1JYWOhIUGup5G9/+9vatWvL486dO1ev/v8+Gw8//HBeXp4xJjc3V0Z1VKAAcD+CXEjSQJWRkXHx4kVjzLp16yQhWVaSezTGtGrVKiIiQh736tUrLCzM+9s0Fso/BwAhgXQlAMBajOTcJTMzU0Zp+/fv15zksmXLpFrkzJkzkiTUkZx+vxRPOrRr104qIW+//XYZinXq1MmRh9SRnJZNGmMc3wMAIYprmbsUFhZKGDt9+rSWL37//fcSw44dO1ammbCYmBiJba1bt65Zs6Yxpnfv3o48JABYjHQlAMBaBDkAgLVIVwZEfn6+TJW9+eabWtnvPc12NfrNOTk5OvH2888/yw/qM5GRkVLHP2bMmFq1ahljbrvtNt+JtNjYWPm2hIQEmXtjsg1ApcIlLyAuXrwoAWnbtm3nzp2TJzdt2uSvLiFhYWESvdq0aRMeHm6M6dOnD5NtAOBAuhIAYC1GcgHx1FNPSX+QXbt26ejtmrlKY4z2HHnllVd0abb2jdQHOmhLSkqq4DxkmzZtZKUBmU8A7sd1KiDWr19/9uzZG/jBatWqSSS744476tatK09qbNM1bUGkyxJKbOgMAK4S/IsmAAABwkguIObOnXtjPR41BxgZGemGcdsHH3wgBZ+6aXjPnj2l1MUNdS7btm3T85ySkkIGFYADF4WA6N27d7APwT/2798v1aE6s5iYmFinTh3jjtxpRkaGNDwz7IoAoCTBv04BABAgjORQmrNnz0qZqK5D79ixo1TEBDE3+O6770ozzxUrVmgetVu3bm7IoAJwFYIcSlNYWKhRRMTHx8fGxgbreMTu3bsl9G7dulUPz18L7QHYhHQlAMBajORQmu3bt2dnZxuXjZPat28v6crU1FQdyb3++utS9vn444/LAwAgyKE0GRkZWVlZwT4KJ4/HI7HNu8Jz27ZtMid3Y4s3AFiJdCUAwFqM5BB6br31VlkVJw3GxMmTJ6WpZiAWzGm29oknnsjJyZHHo0ePll6jPXr0oLATcCeCHEJPvXr15IF3ujI/P1/+V1c7+FFxcbG87Ndff52RkSFPduvWLTIy0lxf620AQUG6EgBgLYIcAMBapCtRgo0bN0qNolYqTpo0SeafatWqFcwju7qA9q7Mzc2V1/fOhcbExERFRRl3tPEEUCKCHEqQlpYmNfoaOdq3by99mV3b6V+nzQKhqKjId+KtZs2aEvLZWg9wLe5AAQDWculdOYJr8eLFubm5xmsbuVtuuSXofZlLl5+fLyOqQOQtn3/+ednqXXpmilatWsXFxRkXnxMA/HGiBHv37pVuXpqj83g80pfZtfNPAW10sn79+jNnzjiejI2N9Xg8xsXnBAB/nAAAazGSQwkKCwsLCgqMVzFhtWrVpJ9IJedd28I5AdyPIIcSXL582VFMWKVKFWoIHTgngPuRrgQAWIuRHHBV8+fPP3/+vDFG/muMGTp0qC6Hr1mzZtCODMD1IcgBV7V9+3ZZSqG7ENx8880RERHymJUDgPuRrgQAWItbUfzi3LlzWjqoD0aPHi3ZucqZmtu+fXtmZqbxWoR31113xcTEyOPKeU6A0EKQwy+Kiop8ez+2adNGsnOVMzWXnp7uWAPerFmz+Pj4YB0PgLIiXQkAsFZlvD1HiZYsWXLhwgV5rC0r69SpI5tfu39BWM2aNeUgy3+on332mZwBPQ/NmjWTdd+s/gZCC0EOv9izZ09+fr481mLCsLCwsLCw4B1UGVSvXt1fkfjAgQPnzp0zXuchOjq6Ro0ahjaVQKjhLxYAYC2CHADAWqQr8YvNmzfLlmnGK03n/m3kVFhYmL9yiStXrpSdhnRO7sknn4yOjjbG6Epw2CEvL083INywYYO+4+Luu++uXbt2MI4LfuP2KxcqTFpaWlZWluPJevXqyTZy7letWjUJcuWfmTt8+LBj5UDnzp1ZOWClwsJCDXIHDx6UuVjVr1+/YBwU/Il0JQDAWozkgF8cPXpUNhjSbYZ69+4txaUuKTGdNWuWVsCKp59+WtZ4oBRz5syRHqRpaWmOPaQKCwu1B8Lx48c1US8mTJjA6Q11BDngFzk5OdK+S/NXzZo1kykZlyyP27hxo7QZU48//niwDiaEbNy4UfLP+/fv1w5t16NM3wx3Il0JALAWIzmEnuzsbBlseTfbDAsLK+d469VXX5X6Ut09btiwYdKOWfeQC5DMzEz5jZYuXSp9Z3bt2iWpM+86mt27dxcVFXn/oA46K6eCggL5DHzwwQdaM7Jnzx7HCGzXrl0FBQXGq2y4Vq1acmIjIiK0KHfq1KlSQ6tCpeoKpSDIIfRcuHDBN8hVq1ZNgtwNV1d+++23jqLKli1bVkxR5fnz52Wu6IcffpBZtzVr1kg5e+m/TiUPcpcuXZLPwJYtW2TVhzFm3bp1jpUA+jnRk1m9enWJbd5B7u67705ISKiYI0eFIV0JALAWI7nKbvXq1ZIB895qx+XtmL/77ju5VffOSk2bNi0qKsqUfb32888/n5eXZ4yR/xpjunbtKp0q5b9+sWbNGs00/vDDD3LkJ0+elKGYbuanqbZLly759pseNWpUeHi4MebWW2+VY3Ok10LdiRMntPpx//79jlpH/erWrVv1LMl52759u55e/ank5GQ5S2PHjpVPhaad9Z0NCwvTM2zZyYQgyFV2hw4dkkkgR2m1mx0/flymzbyPuVevXh6P5wZebdWqVVKyqFfJxo0by9XQj0WVR44c0am+tWvXyhTR3r175Yp8nbcX7du3lwvxkCFDAj1NGBS5ubl643Lo0CFH1lHvAFJTU3XHjFJ4PB7Z2LZ///4ytxoREeHyGzj4HelKAIC1GMlZIicnR0YDCxYs0PtfHZqcPHnyagO1Q4cOyZeKiopC5SZ37dq1UgbpKDW8frm5uVqykZ+fL7UeOpwaMWKE3PhfZ9/CFStWyMjs4MGDcjKPHj3qSLUdOXJEn9G3Q9+UyMhIOfkTJ06UhGTjxo19x5Ht27f3ex614k2dOlW7pDrk5ubqu5CZmen40GqfSX3ftWbkd7/7na7abtiwobRajYuLk3MYFxcny/lD5RMOPyLIWUJb8G3YsEGbYmiB9Z49exzXi1CZfivR4cOHJcF4wynWgoICDXKFhYWOtFi7du3KVFS5b98+OeebNm2S6++2bdvKtI5Yu0t3795dOmK3bds2pCNZKVJTU9PS0ow/Pnv6Ct27d9dkdevWrW09dbgBpCsBANZiJBcEmlqcN2+epLm0yu6GaQs+XURsvAY6vi+u+bGoqCh54H0Ms2fPrlOnjjFG/us2RUVFMvbyXid3PfQXfOGFF6SZoTFGB74vvvii1GcWFxdLPu3jjz+WN2jXrl2ljMz27dsn51zXdPsOMZOSknS7okmTJjlKQLXGr02bNjIKcUkjsUDo3r277Hfx008/Oc5qo0aN9BcfP368nKVSdlDSkVzbtm2lxsRYfepwAwhyQaCpxbVr18oVdvfu3XJZrLDkoebHoqKi5IFkkMTgwYPd3Ovh0qVLjhmv66RBcfXq1Y5138aYgQMHSpYyMzNTYtumTZvkDfr6669veP5PeDwe7fI8ZMgQyUlWTk2bNpUpz4MHD8p5VvXq1dNM4+DBg+Xb/LVNIConPj0AAGsR5AAA1iJdGQQzZsyQZrIHDhzQtcA3lqhs27atzEBER0fLK9x33306OVEKnQSqWbOmPPCu3nbnVNwN06mymTNnSvpRJ+S8/f73v5dTp8WWP/30k7xBJWZH4+LiJJPWsmVLmW8bPnz41VYdeDweTbvJIoFKa/jw4TIV16tXL8fkZXR0tJ6lOnXqhGLpL9yGIBcEK1euvNo6obJKSEiQOYyEhAS5OgwfPrySX0N9afvjzz77TEoeSvT555+X6WUjIiLkDqNZs2Yy3zZs2DD22LymNm3ayIMOHToE90hQGZCuBABYi5FcEDzyyCPSeU/zXfqgrHQAp/tjaQmfNTSJ+vbbb0v7xxL7Fk6dOvVqedrz58/Li+hqgdJp2xFtddiwYUPfwvTw8HA5+R6PR75qZT9JIKQR5IKga9euMifRpEkTuUo2bdqUOulr+vbbb3NycsxVunktX77cX/9Qly5dpA9yTEyMvC9t2rTRVW4AQggXVgCAtbg5DYLk5GTJnoWHh/vuGWYZ3abuyJEjUv0xf/7869knxdePP/4oY7jSW1b6nkzfxigzZ86U5iYluu222yTrq7lfmmgAIYogFwT16tUL9iFUHN3WMj09XQrxly1bVmIFv99ptPMNckOHDi1TC2YAIYp0JQDAWozkXGHGjBmSwcvLy5Nhx8svv+zo4RsS8vPz5fj//Oc/y4J3XUadlZUlXyo9V6n1HR06dHDUemiR5J49e+Rlp0yZoosC4+PjpUhEl36//PLLcgxKs5SsZgMqiSpl7eOOQOjevbvUDeoFeu/evdKdNrTo8ffo0SM7O/sGXkFbsQwYMMCxJODs2bPa2Fom5z7//HM9S82aNZOZs2PHjsmk3YABAxzHsHPnTrKUQKVCuhIAYC3SlcGUnZ0tI+nc3Ny8vDzjlawLUeHh4fIbadFH/fr1JYt49913S7Fi6WvOqlatKj+bmJjoWDv41FNPyVnSFKjH44mLi9Nv0CylFLZorvKJJ56Q3G8oZoABlEdoX1JD3YULF+S6XFBQIBtr1a5dO6SXE+hmYPpbREVFSRaxS5cuMn/Wt2/fG2vLkpWVlZGR4f1MnTp1dHbt8uXLEl9XrVrl+LbevXvL/m32tYMBUDrSlQAAazGSC6a//e1vklLTgsMXXnhBtrkJ0cSaLpqeOXOmbFgTFRUlo7rWrVtLltKPKdni4mKtnJo+fbokM+W/xpjx48fLxjc33XSTDCJZ0w1UNgS5YPryyy+l/E+Te0OHDg3Fokqlv8jw4cMr+J/+5JNPHFnKLl26yIIBj8dDohKonEhXAgCsRZADAFiLdGUQnDt3TmaSiouLQ7qWMlhiY2NldcFzzz2n9Zw6FffrX/9a9nVLSUmRqbhQX5gB4Ibxxx8EhYWFNJopj4iICCkh+eSTT3x3JEhJSZHinUaNGrGLKVDJka4EAFiLkVwQ/P3vfz9//rzxWjnw7LPPSmJNSt7hbcOGDbKRum5Nl5WVJWleWUovxowZI2fvjjvukAeayQRQaRHkgiA1NfXs2bPGGOkybIwZOHBgdHS0oSVHSQ4dOiR3A5qZdOwtIDp37iwLBpKSkhydnQFUWqQrAQDWYiQXBJcuXdIWwyI6OlqaK7q/2PKPf/yjDKQuXrwoyUPv0eeMGTOk6MOPli9fLgPfwsJCOT9RUVFSXTl16lRtDdOlSxfJT5KlBKDYTy4IkpOT5aqt9u3bJ0HO/Tp06JCVlWWMKSgokA9PrVq1NDZv3brVe1sAv2jTpo2jlUlCQoIEudWrV8fGxsqT7r8/AFDxSFcCAKxFurLi5OfnO8bNAwcOdE+GzbtScenSpdJeefPmzY7M6tmzZ+UZHTldunSpgkdRubm58i9Onz5da0zGjRsnRZVNmzaVVXQ1a9ZkeAdvOTk58jlPT0+XOqaGDRtqr4Dw8HA+MPYhyFUcze9pqLvlllvkuuyG7vjeQW7z5s0y8bZkyRKJdiXyreOvGLr04uOPP9Yne/XqJdWV9evXl5sGaizhcP78eYltaWlpcq8WGxurk8qhvpsjSkS6EgBgLUZyFWf27NmONeA9e/aUwUfFpytPnTolI7A33njDsQrNGLNlyxa5z9Vcpd7hPvbYYzL6jI6OlidnzpypC9fmz58vq9onTpxYwQvb//3vf8stucfjkQPTs6qFKk2aNJFB87Bhw+j4Zas1a9YUFBQYr+Tkjz/+KAmJ8+fPSx4lLy9PvhQTE6N5lL/+9a8hvdEVSkSQqzgrVqyQokrp32GMadmyZbCKKnNyciSqLVmyJCcnx/z/IOdLg1y/fv1k3Xr9+vUlcvzjH//QILdmzRoJLTpDVmHWr1/vOFSVlJQkF7KOHTvK4Q0cOJAgZ6u9e/dKt+60tDT5VP/3v/+V+0udKSgxLTl9+nSCnH1IVwIArMVIruIUFRVJH68grk184YUX5JY2PT1d0jXnzp3zrRzRXpoRERGOe96kpCTJCmrS1fvXuXDhgoxTA/E76thx8uTJcnjedAMj7/SpOH36tPzg999/Lw+effZZTWbqoSYkJMiAb/LkybrGHO6Rmpoqecht27bJx+zChQu+n949e/boV+XN1f55+mGeMmWKzBR4r/IMlbWqKBOCXMXxbXRS8ZYuXZqdnW28QkKJhg4dKnmb2NjYq9Wb/fzzz74ZzqKiInkyEEEuOjpaqr3vv/9+3+tRdna2XO+806ciNzdXHuga/P379+tX9SqZnJwsrz9p0iSCnAtt375d3srly5fLPZbOupXVoEGD6tevb4yJjIyU+x7DvoOWIl0JALAWQQ4AYC2G54H13nvvySyCMUZXVQ8aNEimtQKxcuDw4cOawHnnnXd05kxkZ2fLYURGRkoecsqUKb7zW/Xq1bvmpj8bNmyQX00nPIwxkydPjoyMNMb4vmZZ7dixw7GMwePxyBkrMYMaHh4uOdIXXnjhagvY9+zZI6+2bNkyfV80c5ueni6Zq+eee86xkDwsLEz+0UceeUQzmR6PRzNduKbdu3fLVNmiRYtkYristm/fLh+2s2fPOrLidevW1fciPj5e5lZHjx59tbRzYmKiVP+SorQeb3BgrVu3Lj8/Xx7rxbpjx46B+wM7c+aM/kMfffSRTkc5xMbGykVh5MiRNzbfvm/fPpn68p6Z69+/v78aNB8/flxilb5+nTp1JPaUGFo0LN1///1Xe83Vq1fLVXLVqlV6fdTiBT1XixcvdvygNny655579PT6vRW13U6cOCE3FosXL5aJYT+KiIjQ5W6JiYk6d6v9u1FpcR8KALAWI7nA2rdvnyy1Nl4jkm7duklOr/wjubVr10oK6KuvvpIxypkzZzRdqblK7fShjUi0Td8NL9n+/vvvHWvb/evQoUOS1NLzNnLkSFmHfsPruFu1aiWvNm3aND1szW2eOnVKvrp+/Xo5mT/99JOM2zQl+89//lMTuVFRUXIOu3TpIk8OGDCANeYO+vYtXrxYNmm6zlxlnz595GRWr15dznNcXJwM4pOSkhyp/vDwcB3f6/tCiSwMQS7QTp486dg6zhiTnJzsrxU5u3fvlhTQBx984Jh+8xYfHy8BddSoUbI8qPwOHDiQmZnpl5cq0enTpyXTqzG7W7duHo+nPK+ZmJgoDxo3buz7VZ2xS09Plwvxrl27HPOCqampvj9YXFws9wp33nlneQ7PSsXFxZIZ3rhx46lTp67/B9u2bSv3grqbRLNmzeRj3LNnT24mcJ1IVwIArMVILiB27twp9/7eRRnl395s1qxZMlzLzc2Vu+Pdu3fLP6TJNy2bNMY8+eSTUuWovUJC6P538+bNUp4QoHSor/r168uo8f7775ez2rNnT3kH8/Ly5ITPnj3bsdLcGPPdd9/JCGP69OmONFq7du3kmXvvvbeCm3m6hGYRn3nmGTl117ltW9u2bSUJXLVqVfl+/Wy7Yf9FhAqCXEAcO3ZMLs3e7Rhq1KhRziD36aefSvLz9OnTV2v0ULt2bb2saGcQrYAPIYcOHcrIyKjIf1FzyN26dXN8SWc633jjDd8gp/1Tdu/e7fiSdoIeOnRoJQ9yw4YNk3PoXe4PBBofNQCAtRjJBcSiRYukaMJ7VfK0adMkeVjKQulLly5JWuwvf/mLbxFaWlqao8WztlscM2aMLBTz3t04KipKvurHYdzmzZtlkKq1GI899pj+RrYOVurUqSMP/vSnP+kqcqVvh47zsrKyZNSyc+dOqWv9wx/+4Lu+ftKkSXLqGjRo4Ibd4QNKPxshl1RASCPIBcT69et9iypHjBhxzaLKy5cvyxVz4cKF17NgtmHDhjI/MWbMGL0QB9SBAwckcmuQGzBggP5ejkYh1tAL9AMPPOD7VU0dL168WCpO9b07duyYPDh8+LDvDw4aNEhWK0uzYLvZ+tmAy5GuBABYi5GcP2myUTVu3Fjn2EuZbJ85c6aUTebn58srlLhgtn///jJuu/322+VBYmKivGyF3SYvX75c2l8VFhbKodarV0+bJ5W/oCDouxHdAM2/TZ48WYa5Wo35v//9TzLMq1ev9u2o+fbbb8sbFxMTI6euR48e8kyfPn0Y+gDlR5DzJ002qri4OG1rUspUxJIlS6QxSk5OTik7saWkpEjebOzYscFaDLBp0ybpW+HdG1c2n/OLK1euBHFT2Ruj7+ygQYMcXyoqKpL7lXXr1vkGuZUrVzqeCQsLkz4dPXv2JMgB5Ue6EgBgLUZy/qQlkdpha+zYsdpAT2/M58yZ41jTnZWV5SibfOqpp7TYQXvxpaSkyLiw4hfDvvXWW1JVqLWFPXv2DMSGQQcPHpT115q3fPLJJ+Ucln/7norXo0cPKUb1eDy6ql0/Hq+//rpW4cpbv379enmLz5w5Iyd25MiR+otrMS2A68QfjD999NFHjqLKPn36SE9h4xUMli1bJsnJU6dOXW1N9/Dhw/UHtV9JEH3xxRdyzNqquG3btoHYMOj06dPyT+iZGTZsmMz5hVC7FtW6dWt50L59e31Sk9ILFiyQX1Yzmbt27ZIH27dvlwe33nqrFq+2aNGCIAeUCelKAIC1uCsMrKysLE27zZo1S27YT506Jffv1atXlzt6TU7WqlVLMpP169fXtcNuWDyblpYmg1TtxqmD1GvuIV4mCxculOpNzYvWrFkzFMdwpahdu7a871OnTpVf8+LFi/LM/PnzJeOdlpYmp3rx4sV6hr/66itZM66dscaOHSufHH2mqKhIXio3N1dHw/Hx8UFPBgBBQZALrPz8fA1R77//fl5envdXNaSNGDFCAkZ0dLQbQpqv7OxsKapUHTp0CMTW2GvXrpX11Do9WaNGDcn0uvPM3AANWvfdd5/jS6mpqbKQXHelWbdune8rNGrUSKLdgAEDpLRVk9saL3NycvSORHdiAyobPvcAAGsR5AAA1iJd6Qc7duyQvJD37nFi7ty5mpvSCrrBgwfLk126dJFiudjYWHnGbRm5N998U+rdtep96NChshYicEuVHYvBdU7ObScnECZOnCizdNriedWqVfrJ+fbbb2U29+zZs3I2Xn/9dXkjIiIi5BmdAz5//ryeyRdffFHzmUClQpDzg6NHjzqq3tWKFSt8v79z586y8mnUqFEu72qxfPlyx8qBrl27yqq1AK3V8213onNylUH//v0dz2RlZclaOmPMDz/8IG+EPvPJJ5/IAz1vJd4KzJgxgyCHyol0JQDAWozk/GDbtm2SzfNtLty0aVOtahs3bpyM27p27SpZStcu7D1w4ICkXtPT06WgX1tKpqSkyJjAtQdvmX79+mmrlObNm8vjjIwMSRvMmzfPd6dyce+992rTHFv3+QOuieuUHxw+fFjSR77pynr16mkwePDBB2XLN/cXc584cUIuptnZ2bLsQbNhLVq00D0HUAE6duyojzt37iwPDh48KHdU77333tWCXI8ePTRF6d+1jEAIcfvVFgCAG8ZIzg9Gjx4t45677rrLMZhLSEjQcZsu/Xa/hQsXauNgeWbatGkV1iVZRo2tW7d2eVI3iDwej3zSpk+fLtWYvgU7nTp10gFc5ancCS3eXWnEhx9+KH96OTk5V2ts623q1Kna2hQlqhJye3ehArRq1crR32THjh2SpQx0yGnVqpV0POnfv79MYb7yyitRUVEB/UeBoNDmbeqRRx45c+aMMebEiRPXs4Hwli1bGjVqFKjjswLpSgCAtUgE4Rfr16/XKj590KJFC+mRWKNGjYDWyxw6dEhuaa9cuSJJ3ZtuuklSo6QrUSa6Cj47O1syfidPnvRt1FAm586dk5c6fPiwDLDS09OvJ514zZd15NKOHTsmpdr64iUm21JSUiTPQUnRNXH5wC927NihbU30ipCYmCjTOdWrVw9okDt5HwEKdAAAC6hJREFU8qREVv3bbtCgQagUo8JVCgsL5VOUkZEhAWn37t3azeDGZGRkyB/Fhg0bZKJ63759ei9Y8Vq0aCF/Hcy2XhOXDwCAtRjJ4RfffPONrPs2Xk28Ro0aJTeMgW4/tnDhQlmQp9vIdezYUTaRIV1ZOS1atEi21rtw4YKk7E6dOnU9WcfCwkL5/ry8PBnSZWZmljO1qMegpSI3nP/0Tj/q35eKioqSdH3dunVlpqDEdOXNN98sY7gKqHYOdVw+8Isff/zRUVFpjOnXr1/FLP1etWqVFFWq5ORkj8dTAf803Gn16tXygdRi+l27dpUpQ1h6P0836Nu3b3x8vPczDRo0kNjWqFEjbu/8gnQlAMBa3ClUdq+99pomhfTJHj16SDKEaW34S0FBgQyt3n33XVnvnJ+fX0oWcfv27ZK71o4EvhnCmjVryijNe6xWu3Zt+d927drJB7hbt27u2e7DO/3Yvn17R1tR3TKJeit/IchVdosWLcrOzjZelxJjTIcOHeRvj4QJ/KWoqEhC2pIlSzIyMowx6enp5azs15Ut3iEhKipK/rdjx44yZTVhwgTH1BcqD24WAADWIsgBAKxFMqqS0g4jubm5MkFSpUoVKesyxvTs2VPaRVb8nJxrC+H86Pjx43LyMzMzHfm6lJSU0J0H9e61+J///Ef2ADpz5oz8jkVFRVqF72jqER4eLu97ixYtNEN+55131qpVyxhTvXr1q30qatSo4TsnpxN1zZs3l5Ppngk5VDyCXCWlO8adO3dOrjje19aUlBQ2jQuc9PR0Ofm6LZwWI2itRCi6fPmy/iLLly+XNSHX0xmkVq1aMouWlJSkAenBBx+UO60Q2r4DLkS6EgBgLUZylYuWcS9atMixY9yUKVO0e0IQ2yg0b95csqaaOw0hBw8elNTcl19+KSc2NzfXt2PF6dOnJVPnu2fY8OHDHTXl7peamiq1/lu3btVB28GDB+VJzcdqKrJfv36Sh9QySB2rNWrUSN/3yMhIGdQyjEN5EOQqF+149OWXX8rKATVu3Dg3pCgbNmwol7ZQDHLHjx+XjmgfffSRdCkrvROV7+W7nH2Eg2LDhg05OTnGmKVLl+pqS99uI40bN5ZU5MiRIyUPqd09gMAhXQkAsBYjucrltddek5o3veP+zW9+I7mjiq9A++abb3TgoqV3t99+uyRL3Vl/4V1AOGfOHDmZSssmz5w5I79acXGxDGW8hywxMTGSppswYYIjMxyKa5Z/9atfycdJ85bGmKeffjoyMtJ4jeS0GWNCQoLsgkZTD1QAglzl8v777zu6MA8ePFhyRxUf5DZt2iQdxYzXNq0pKSnR0dHGrUHOu4BwwYIF0rnjelStWlUv97GxsRLzJkyY4IYUcTm1bdtWYr8W9Btjxo8fX79+/aAeF2AM6UoAgMUYyVUKL730koyZdOQ0ZMgQSRk1b95cMmYVnzvKysqSCk/jldZr0KCBbCMXxFzWsWPHJOu4dOlSKZLUgh3vkZwjV2m8VrkNGDDAMTL2HslpVaEdm4HFxcVJjegzzzyjaXDJVQJBR5CrFN555x1HlvKOO+6Q6Z+GDRsGqx9ETk6OlCAar2K8+Pj4uLi4oByPOnnypKRP33vvPdlINi8vr8S9Kx2SkpJkgnPixImSBK4M6tatKw8eeuihoB4IUALSlQAAazGSs9nLL7/syFIOHz5cxm0dO3ZkMx3x6aefSk3gvn37pIAiLS1N0pU5OTneOxAZY9q3b69nrEuXLpKcjI6O1q5U8lUZzwEIusp+gbPb/PnzHVnKfv36SZaydevWMieHr7/+WnKSX3zxhSOk+WrZsqUGsFGjRsmkmnefDgCuQroSAGAtRnK2eemll7TCTbOUSUlJMtRo3bq1jOTcMPI4ffq0tIMyXruuBMKOHTskD5mamuo7VtuyZYss3NaF3k2bNpXzM2LECBm3aWFky5YtNV1Zr149SVeyqBlwLYKcbebPn+9oSmmMadKkiS4YcE9td2Zmph5qQIPc/v37ZdZt3rx5Ws9ZisTERDldEydOlJXphDEgRPGnCwCwFkEOAGAt0pWWmDVrlkzF6YScMaZNmzYyt3TffffJgoFgrfsuUfXq1f24gOHAgQNS979s2TLHxJuuDdDn4+LiNAM5atQox8lp2LChnLfatWuzmRkQ0ghylpg3b57vVNzNN98sF+4BAwa4sL19tWrVtP6l/LHk6NGjUj8yd+5cWRJQirp162p8HT9+vPTsiIyMJKQBliFdCQCwFiO50DZ79mxHljIhIUGHI4MHD46IiDAuy1KquLg4HU4dP378ms0hdYvtlStXyqBt165dWvd/5MgR+Qbd0kz1799fzkDbtm3lX9Qd3YwxdevWdef5AVB+BLnQNnfuXEeW0uPxaA6wb9++UgHvTnXr1i1TuvLKlSsSCNesWSPbF+guAaXr1auXZGvvueceGm4BlQrpSgCAtRjJhRJNzb366qu+tZRi7NixkqI0rm8T3LFjRz3+devWye42c+bMkVpHpWlMXTC+adMm+WZNYBpjwsPDZTg4adIkeQUdJnbq1EkWd9ONGqhsqlzPLllwCd26s0OHDr61lGLv3r2y6aj7rV+/XmKVMebhhx++WkmkfkRLT2nqqoBVq1bFxsYaY2rUqEG1JFDJka4EAFiL7E0IeOedd6RiMCMjQ4Y1muW78847pUdwhw4ddP1y8I60bJo2baoZyJtuuklGcseOHfNOQhqvAVzjxo21b7JvPaQu3I6MjJRvYxgHgCAXAhYuXCjd+o8ePepoZNyzZ0/Z0mzUqFEhVwffqFEjfdykSRMJcsePH3cEOZWYmCgR/dFHH3Vz1SgA9yBdCQCwFiM5d0lLS5Ox2sKFC3UFWFpamjzWEoxx48bJuK1Tp07yINTrBu+9915Z333HHXdcbSTXsGFDKS1xedUoAPegutJdtm3bJusEHnjggVIaMH766adRUVHGmOTkZDdsfwoA7kS6EgBgrdDOcVnjlVdekYLJU6dOSbrSuwHjkCFDZC1zUlKSjNsaNWokWUp2rAaAUpCudIVWrVqdPXvWeLXw8C5/f+2116T1Yv/+/SXaAQCuB+MAAIC1CHIAAGsxJxcEOgOnLly4IHnjmJgYSVQ+9NBDWijfvn17WQRNISUAlAlzckGQnJwsM3C+mjZtKpHsiy++oKkHAJQT6UoAgLVIVwbWhx9+qI1L0tPTfZcHiEcffVSSkwkJCTT1AAB/IV0ZWPfcc09eXp483rlz59UaVn333XeSnNQ5OQBA+ZGuBABYi3SlPy1YsEBSkbm5uTJEPnbsmPQdNl7tlcePHy/ZyOjoaBm3xcTEhNxGOQDgfqQr/alv3766KZpj4zdvX331lbRXbtKkCX25ACBwuMICAKxFurLM8vLyZPj77rvvauWkSE9P12ckDzl06FDtNtmsWTMZtyUkJEhykhoTAAgo0pVldvLkSUlF9u7dOycnx5QUq/SZt956S3orG2N69eoljUsAABWDdCUAwFqkK8tswYIF0nnSkas0xowdO1aSk7GxsTKYa9WqlaYr6TwJABWMdGWZldJ5csWKFVI22bx5c0IaAAQd6UoAgLVIV5bZ5MmTZcX3+fPn5Znw8HBJTjZo0EDKJln9BgBuQLqyzLT/ZGZmppy9+Ph4ohoAuBCXZgCAtQhyAABrMSdXZlWrVpUsZUREhDygcQkAuBNzcgAAa5GuBABYiyAHALAWQQ4AYC2CHADAWgQ5AIC1CHIAAGsR5AAA1iLIAQCsRZADAFiLIAcAsBZBDgBgLYIcAMBaBDkAgLUIcgAAaxHkAADWIsgBAKxFkAMAWIsgBwCwFkEOAGAtghwAwFoEOQCAtQhyAABrEeQAANYiyAEArEWQAwBYiyAHALAWQQ4AYC2CHADAWgQ5AIC1CHIAAGsR5AAA1iLIAQCsRZADAFiLIAcAsBZBDgBgLYIcAMBaBDkAgLUIcgAAaxHkAADWIsgBAKxFkAMAWOv/AGKRq2V+RClSAAAAAElFTkSuQmCC";
            ImageEntity imageEntity=new ImageEntity();
            imageEntity.setData(new BASE64Decoder().decodeBuffer(img));
            imageEntity.setHeight(50);
            imageEntity.setWidth(50);
            imageEntity.setType(ImageEntity.Data);
            map1.put("img1",imageEntity);
            //图片路径方式
            String path="D:\\javaProject\\easypoi-test\\src\\test\\resources\\imgs\\company\\one.png" ;
            imageEntity=new ImageEntity();
            imageEntity.setUrl(path);
            imageEntity.setHeight(50);
            imageEntity.setWidth(50);
            imageEntity.setType(ImageEntity.URL);
            map1.put("img2",imageEntity);
            list.add(map1);
        }
        map.put("list",list);
        try {
            XWPFDocument doc = WordExportUtil
                    .exportWord07("word/qjd.docx", map);
            FileOutputStream fos = new FileOutputStream("D:/home/excel/请假单.docx");
            doc.write(fos);
            fos.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

图片设置
image.setLocationType(ImageEntity.EMBED);图片嵌入类型
public static int EMBED = 0; 嵌入
public static int ABOVE = 1; 上面(顶层)
public static int BEHIND = 2; 后面(底层)

矮小的

关注

3
点赞
踩
25

收藏

觉得还不错? 一键收藏
0
评论
SpringBoot 整合eazyPoi 4.3.0 Excel数据导入导出(持续更新功能)

目录依赖注解用法说明@ExcelTarget 绑定Id@Excel 用在字段上面@ExcelCollectionExcel导入&导出实体代码正常数量导出(1-2W条)依赖<dependency> <groupId>cn.afterturn</groupId> &lt
复制链接

扫一扫