easypoi使用

1、pom文件

<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-spring-boot-starter</artifactId>
    <version>4.3.0</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-base</artifactId>
    <version>4.3.0</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-web</artifactId>
    <version>4.3.0</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-annotation</artifactId>
    <version>4.3.0</version>
</dependency>

<!-- Word 需要使用 -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>ooxml-schemas</artifactId>
    <version>1.3</version>
    <optional>true</optional>
</dependency>

<!-- 校验,下面两个实现 -->
<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-validator</artifactId>
    <version>5.1.3.Final</version>
    <optional>true</optional>
</dependency>

<dependency>
    <groupId>org.apache.bval</groupId>
    <artifactId>org.apache.bval.bundle</artifactId>
    <version>1.1.0</version>
</dependency>

<!-- PDF -->
<dependency>
    <groupId>com.itextpdf</groupId>
    <artifactId>itextpdf</artifactId>
    <version>5.5.6</version>
    <optional>true</optional>
</dependency>

<dependency>
    <groupId>com.itextpdf</groupId>
    <artifactId>itext-asian</artifactId>
    <version>5.2.0</version>
    <optional>true</optional>
</dependency>

2、实体类

package org.es.bean;

import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.Data;
import org.springframework.data.elasticsearch.annotations.Document;

@Data
@TableName("notify_message_setting")
@ExcelTarget("NotifyMessageSettingDO")
@Document(indexName = "notifymessagesetting")
public class NotifyMessageSettingDO {

    @TableId(type = IdType.AUTO)
    private Long id;
    @Excel(name = "机构编码")
    private String orgCode;//机构编码
    @Excel(name = "消息类型")
    private String messageType;
    @Excel(name = "模板id")
    private String userTemplateId;//支付宝模板消息id
    private String channel;//
    private String toPage;//小程序调转页面
    private String ext;//扩展信息
    @Excel(name = "支付宝小程序appid")
    private String thirdAppId;//微信或支付宝的appid
    private String status;
    @Excel(name = "支付宝应用私钥")
    private String privateKey;
    @Excel(name = "支付宝公钥")
    private String publicKey;
    private String appSecret;
}

3、导入

public static void importExcel(){
    ImportParams params = new ImportParams();
    params.setNeedSave(true);
    List<NotifyMessageSettingDO> result = ExcelImportUtil.importExcel(
            new File("C:\\Users\\niujinlong\\Desktop\\现有工作文档\\模板消息配置表格1.xls"),
            NotifyMessageSettingDO.class, params);
}

4、导出

package com.system;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.zero.bean.ChannelConfigDO;

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

public class EasyPoiTest {

    /**
     * easypoi导出测试
     */
    public static void export(){
        List<ChannelConfigDO> channelConfigDOList=new ArrayList<>();
        for(int i=0;i<100;i++){
            ChannelConfigDO channelConfigDO=new ChannelConfigDO();
            channelConfigDO.setCreateTime(new Date());
            channelConfigDO.setUpdateTime(new Date());
            channelConfigDO.setOrgCode(String.valueOf(i));
            channelConfigDO.setMchAppId("366655444");
            channelConfigDOList.add(channelConfigDO);
        }
        ExportParams params = new ExportParams();

        try(HSSFWorkbook workbook = (HSSFWorkbook) ExcelExportUtil.exportExcel(params,
                ChannelConfigDO.class, channelConfigDOList)){
            File file = new File("C:\\Users\\15236\\Desktop\\temp\\test.xls");
            workbook.write(file);//新建一个xlsx文件，并将workbook写入这个xlsx文件
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    public static void importExcel(){
        ImportParams params = new ImportParams();
        params.setNeedSave(true);
        List<ChannelConfigDO> result = ExcelImportUtil.importExcel(
                new File("C:\\Users\\15236\\Desktop\\temp\\test.xls"),
                ChannelConfigDO.class, params);
        System.out.println(result.size());
    }

    public static void main(String[] args){
        importExcel();
    }
}
 

5、实体类

import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;

import java.io.Serializable;
import java.util.Date;
@Data
@TableName("channel_config")
@ExcelTarget("channelConfigDO")
public class ChannelConfigDO implements Serializable {

    private static final long serialVersionUID = 7696421942432057679L;
    @TableId(type = IdType.AUTO)
    private Long id; //bigint 主键
    @Excel(name = "创建时间",format="yyyy-MM-dd HH:mm:ss",width = 25)
    @JsonFormat(pattern="yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
    private Date createTime; //datetime(0) 创建时间
    @Excel(name = "更新时间",format="yyyy-MM-dd HH:mm:ss",width = 25)
    @JsonFormat(pattern="yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
    private Date updateTime; //datetime(0) 更新时间
    private String configId; //varchar(32) 配置ID
    @Excel(name = "机构编码",width = 25)
    private String orgCode; //varchar(32) 机构编号

}