springBoot使用Easypoi实现excle的导入导出

1.首先创建测试表

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(25) DEFAULT NULL,
  `num` varchar(25) DEFAULT NULL,
  `birth` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=90 DEFAULT CHARSET=utf8;

2.创建pojo类

@Data
public class UserEntity implements java.io.Serializable {
    public UserEntity() {
    }

    public UserEntity(Integer id, String name,String num, Date birth) {
        this.id = id;
        this.name = name;
        this.birth = birth;
        this.num = num;
    }

    /** id */
    private Integer id;

    /** name */
    @Excel(name="姓名",width = 15)
    private String name;

    /** name */
    @Excel(name="学号",width = 15)
    private String num;

    public String getNum() {
        return num;
    }

    public void setNum(String num) {
        this.num = num;
    }

    /** birth */
    @JsonFormat(pattern="yyyy-MM-dd HH:mm:ss",timezone = "GMT+8")
    @DateTimeFormat(pattern="yyyy-MM-dd HH:mm:ss")
    @Excel(name="生日",width = 15,format = "yyyy-MM-dd HH:mm:ss")
    private Date birth;

3.创建dao

@Mapper
public interface UserDao {
    List findAll();

    int insertUser(UserEntity user);

    int insertUsers(List<UserEntity> users);
}

4.创建service

public interface UserService {
    List findAll();

    int insertUser(UserEntity user);

    int insertUsers(List<UserEntity> users);

}

@Service
public class UserServiceImpl implements UserService {

    @Autowired
    private UserDao userDao;

    @Override
    public List findAll() {
        return userDao.findAll();
    }

    @Override
    public int insertUser(UserEntity user) {
        return userDao.insertUser(user);
    }

    @Override
    public int insertUsers(List<UserEntity> users) {
        return userDao.insertUsers(users);
    }
}

5.创建mybatis映射文件

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<!-- USER -->
<mapper namespace="com.hydata.mybatisAndMVC.dao.UserDao">
    <!-- 结果映射 -->
    <resultMap id="userMap" type="com.hydata.mybatisAndMVC.pojo.UserEntity">
        <id column="ID" property="id" jdbcType="INTEGER"/>
        <result column="NAME" property="name" jdbcType="VARCHAR"/>
        <result column="NUM" property="num" jdbcType="VARCHAR"/>
        <result column="BIRTH" property="birth" jdbcType="VARCHAR"/>
    </resultMap>

    <!-- 表所有字段 -->
    <sql id="allColumns">
        ID, NAME,NUM,BIRTH
    </sql>

    <!-- 查询所有数据 -->
    <select id="findAll" resultMap="userMap">
        SELECT
        <include refid="allColumns"/>
        FROM USER
    </select>



    <!-- 插入数据 -->
    <insert id="insertUser" parameterType="map">
        INSERT INTO USER (<include refid="allColumns"/>)
        VALUES (
        #{id},
        #{name},
        #{num},
        #{birth}
        )
    </insert>

    <!-- 批量插入数据 -->
    <insert id="insertUsers" parameterType="list">
        INSERT INTO USER (<include refid="allColumns"/>)
        VALUES
        <foreach collection="list" index="index" item="item" separator=",">
            (
            #{item.id},
            #{item.name},
            #{item.num},
            #{item.birth}
            )
        </foreach>

    </insert>

6.导入相关依赖

因为easypoi是对poi的封装，所以poi以及easypoi都需要导入

		<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.9</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.9</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.9</version>
        </dependency>
		!-- EasyPoi 引用本地jar -->
        <dependency>
            <groupId>com.platformframework</groupId>
            <artifactId>easypoi</artifactId>
            <version>1.0.0</version>
            <scope>system</scope>
            <systemPath>${basedir}/lib/easypoi-1.0.0.jar</systemPath>
        </dependency>
        <dependency>
            <groupId>com.platformframework</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>1.0.0</version>
            <scope>system</scope>
            <systemPath>${basedir}/lib/easypoi-web-1.0.0.jar</systemPath>
        </dependency>
        <!--可能会存在报缺少commons-lang3，所以也进行引入-->
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.4</version>
        </dependency>

7.编写Controller

@Controller
public class ExportAndLoadUserExcle {


    @Autowired
    private UserService userService;


    @PostMapping("loadExcle")
    @ResponseBody
    public Map<String,Object> load(Part part){
        Map<String,Object> map=null;
        try {
            ImportParams importParams=new ImportParams();
            importParams.setHeadRows(1);
            InputStream inputStream = part.getInputStream();
            List<UserEntity> userEntityList = ExcelImportUtil.importExcel(inputStream, UserEntity.class, importParams);
            userService.insertUsers(userEntityList);
            map = makeMap(true, "导入成功");
        } catch (Exception e) {
            e.printStackTrace();
            map = makeMap(false, "导入失败");
        }

        return map;
    }


    @RequestMapping(value = "/exportXls")
    public ModelAndView exportXls(UserEntity sysUser, HttpServletRequest request) {
        //Step.1 AutoPoi 导出Excel
        ModelAndView mv = new ModelAndView(new PlatformEntityExcelView());
        List<UserEntity> pageList = userService.findAll();
        //导出文件名称
        mv.addObject(NormalExcelConstants.FILE_NAME, "用户列表");
        mv.addObject(NormalExcelConstants.CLASS, UserEntity.class);
        mv.addObject(NormalExcelConstants.PARAMS, new ExportParams("用户列表数据", "导出人:Du", "导出信息"));
        mv.addObject(NormalExcelConstants.DATA_LIST, pageList);
        return mv;
    }




    public Map<String,Object> makeMap(boolean b,String str){
        Map<String,Object> map=new HashMap();
        map.put("success",b);
        map.put("mes",str);
        return map;

    }


}

8.测试

1. 启动springBoot，使用PostMan测试上传文件
准备excle文件：

PostMan设置Headers与请求方式Post
在Body中选择上传测试文件（需要注意的是参数名称需要和控制层保持一致，或者在控制层加@RequestParam(“xxx”))

点击send，出现如下所示表示上传成功

查看数据库

1. 测试导出文件
   直接在浏览器地址栏中输入localhost:8080/exportXls即可，下载完成打开如下：
   

本文只是简单测试springBoot使用easypoi进行excle的导入导出，其框架针对excle的导入导出做了很多的封装，easypoi的更多参数设置可以查看其官网：http://easypoi.mydoc.io/