导入与导出Excel

1.导入相关依赖【我们这里没有用EasyExcel】

1.1）：pom.xml

<!--excel导入数据-->
<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-all</artifactId>
    <version>5.8.18</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.2.3</version>
</dependency>

1.2）：实体对象

package com.example.tests.vo;

import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@NoArgsConstructor
public class ExcelPoJo {
    private String id;
    private String ids;
    private String name;
    private String salary;
    private String dates;
}

1.3）：mapper层代码

package com.example.tests.mapper;

import com.example.tests.vo.ExcelPoJo;
import org.apache.ibatis.annotations.Mapper;

import java.util.ArrayList;
import java.util.List;

@Mapper
public interface ExcelMapper {
    /**
     * 数据查询导出
     * @return
     */
    ArrayList<ExcelPoJo> selectAll();

    /**
     * 数据导入
     * @param list
     */
    void batchInsert(List<ExcelPoJo> list);
}

1.4）：Mapper层对应的动态SQL【Oracle写法，如果你们用的是Oracle那就用这个就行】

<?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">
<mapper namespace="com.example.tests.mapper.ExcelMapper"> <!--映射地址-->
    <!--动态SQL   select的id为Mapper层的方法名称-->
    <!--我是查询导出用的-->
    <select id="selectAll" resultType="com.example.tests.vo.ExcelPoJo">
        select ID, IDS, NAME, SALARY, DATES from TPRGEASY
    </select>
    <!--我是数据导入用的-->
    <insert id="batchInsert" parameterType="java.util.List">
        insert into tprgeasy  ( id, ids, name, salary, dates)
        select javaid.nextval, A.* from(
        <foreach collection="list" item="item" separator="union all">
            select
                #{item.id} ids,
                #{item.name} name,
                #{item.salary} salary,
                #{item.dates} dates
            from dual
        </foreach>
        )A
    </insert>
</mapper>

1.5）：Mapper层对应的动态SQL【mysql写法，如果你们用的是MySql那就还成这种即可】

<insert id="batchInsert" parameterType="java.util.List">
    insert into tprgeasy  (ids, name, salary, dates)
    <foreach collection="list" item="item"  separator=",">
        (#{item.id}, #{item.name}, #{item.salary}, #{item.dates})
    </foreach>
</insert>

1.数据导入

2.数据导出