SpringBoot导入与导出Excel

一、导入相关依赖【我们这里没有用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）：【Oracle写法】Mapper层对应的动态SQL【如果你们用的是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）：【Mysql写法】Mapper层对应的动态SQL【如果你们用的是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.6）：项目目录

二、数据导入/导出代码【controllers层】

package com.example.tests.controllers;

import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.example.tests.mapper.ExcelMapper;
import com.example.tests.vo.ExcelPoJo;
import com.example.tests.vo.Result;
import java.net.URLEncoder;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import javax.annotation.Resource;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * excel数据导入数据库
 * 注意：用了@Resource 这个注解 咱们就不要用@Controller 这个注解 要不然返回值会报错 实际运行是成功的
 * 解决如下：将@Controller 变成 @RestController 就可以解决
 */
@RestController
@RequestMapping("/UserExcle")
public class ExcelController {
    @Resource // @Resource和@Autowired注解都是用来实现依赖注入的。只是@AutoWried按bytype自动注入，而@Resource默认按byName自动注入。
    private ExcelMapper excelMapper;

    /**
     * 数据导入
     * @param file
     * @return
     * @throws Exception
     */
    @PostMapping("/Excel")
    public Result imp(@RequestBody MultipartFile file) throws Exception{
        //1. 使用InputStream流读取文件
        InputStream inputStream = file.getInputStream();
        //2. 读取流中的数据
        ExcelReader reader = ExcelUtil.getReader(inputStream);
        //3. 将excel表头的名字替换为咱们需要的名字即可，切记替换的名字一定要和实体对应起来
        reader.addHeaderAlias("编号", "id");
        reader.addHeaderAlias("名字", "name");
        reader.addHeaderAlias("工资", "salary");
        reader.addHeaderAlias("日期", "dates");
        //3. 将读取到的数据填充至List<ExcelPoJo>中
        List<ExcelPoJo> excelPoJos = reader.readAll(ExcelPoJo.class);
        System.out.println(excelPoJos);
        //4. 将list导入数据库
        excelMapper.batchInsert(excelPoJos);
        return new Result<>(0, "操作成功", "");
    }

    /**
     * 数据导出
     * @param response
     * @throws Exception
     */
    @GetMapping("/import")
    public void excelopen(HttpServletResponse response) throws Exception{
        //1. 查询导入表中所有数据
        ArrayList<ExcelPoJo> excelPoJos = excelMapper.selectAll();
        System.out.println(excelPoJos);
        //2. 通过工具类创建Writer写出到浏览器中
        ExcelWriter writer = ExcelUtil.getWriter(true);
        writer.addHeaderAlias("id","序列编号");
        writer.addHeaderAlias("ids","导入编号");
        writer.addHeaderAlias("name","人员姓名");
        writer.addHeaderAlias("salary","人员工资");
        writer.addHeaderAlias("dates","人员日期");
        //3. 依次写出到excel中，使用默认方式强制输出标题
        writer.write(excelPoJos,true);
        //4. 查询时间戳，防止多次导出  出现相同名称覆盖问题
        long times = System.currentTimeMillis();
        //4. 设置浏览器响应格式
        String  fileName = URLEncoder.encode(times+"人员信息.xlsx", "UTF-8");
        response.setHeader("Content-disposition", "attachment;filename="+fileName+";"+"filename*=utf-8''"+fileName);
        response.setContentType("application/octet-stream; charset=UTF-8");
        //5. 将Writer中的数据刷新到流中
        ServletOutputStream outputStream = response.getOutputStream();
        writer.flush(outputStream,true);
        //6. 关闭流
        outputStream.close();
        //7. 关闭writer
        writer.close();
    }
}

三、关于ByBatis的Oracle与MySql写法说明

3.1）：MyBatis的foreach相关材料

foreach的主要用在构建in条件中，它可以在SQL语句中进行迭代一个集合。

foreach元素的属性主要有 item，index，collection，open，separator，close。

item表示集合中每一个元素进行迭代时的别名，index指定一个名字，用于表示在迭代过程中，每次迭代到的位置，open表示该语句以什么开始，separator表示在每次进行迭代之间以什么符号作为分隔符，close表示以什么结束，在使用foreach的时候最关键的也是最容易出错的就是collection属性，该属性是必须指定的，但是在不同情况 下，该属性的值是不一样的，主要有一下3种情况：

1. 如果传入的是单参数且参数类型是一个List的时候，collection属性值为list

2. 如果传入的是单参数且参数类型是一个array数组的时候，collection的属性值为array

3. 如果传入的参数是多个的时候，我们就需要把它们封装成一个Map了，当然单参数也可以封装成map

3.2）：MyBatis操作Oracle的相关资料

1.SQL中没有VALUES；

2.<foreach>标签中的(selece ..... from dual)；

3.<foreach>标签中的separator的属性为"UNION ALL"，将查询合并结果集。

四、注意

我用的是oracle的数据库，所以我的写法是1.4里面的写法。

其次，我给表里面增加了一个自增序列编号(javaid.nextval)，你们没有用到可以直接删掉即可。

五、PostMan测是一下看看【上传导入 / 下载数据】

5.1）：本地准备好测试上传Excel的文件

5.2）：Oracle创建好对应的数据表

5.3）：PostMan测试

5.4）：导入结果截图

Oracle查询截图

5.5）：数据导出为excel文件