Springboot+POI做文件导出导入

最新推荐文章于 2024-07-22 19:11:06 发布

朝花不迟暮

最新推荐文章于 2024-07-22 19:11:06 发布

阅读量2k

点赞数 9

分类专栏： SpringBoot 文章标签： poi excel mybatis spring boot

本文链接：https://blog.csdn.net/Curtisjia/article/details/104238156

版权

SpringBoot 专栏收录该内容

73 篇文章 6 订阅

订阅专栏

前序

本文使用springboot+poi做Excel的（xls格式）文件导入导出数据库功能，萌新上路请多指教！谢谢！！

添加依赖

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.10</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.1</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.1.1</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.17</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

添加配置，防止编译时忽略放到类里面的xml文件，我本人喜欢把mapper类和xml配置文件放在一起。

        <resources>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.xml</include>
                </includes>
            </resource>
            <resource>
                <directory>src/main/resource</directory>
            </resource>
        </resources>

连接数据库

spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.url=jdbc:mysql://localhost:3306/leotemp\
  ?useUnicode=true&characterEncoding=utf-8&useSSL=false
logging.level.com.study.excel.mapper=debug

数据库脚本

CREATE TABLE `books` (
  `bookID` int(10) NOT NULL AUTO_INCREMENT COMMENT '书id',
  `bookName` varchar(100) NOT NULL COMMENT '书名',
  `bookCounts` int(11) NOT NULL COMMENT '数量',
  `detail` varchar(200) NOT NULL COMMENT '描述',
  `db_source` varchar(70) DEFAULT NULL COMMENT '数据来源',
  PRIMARY KEY (`bookID`),
  KEY `bookID` (`bookID`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

数据大家自己随便插一些就行了

实体类

package com.study.excel.model;

/**
 * @description:
 * @author: Leo
 * @createDate: 2020/2/9
 * @version: 1.0
 */
public class Book
{
    private Integer bookID;
    private String bookName;
    private Integer bookCounts;
    private String detail;
    private String db_source;

    @Override
    public String toString()
    {
        return "Book{" +
                "bookID=" + bookID +
                ", bookName='" + bookName + '\'' +
                ", bookCounts=" + bookCounts +
                ", detail='" + detail + '\'' +
                ", db_source='" + db_source + '\'' +
                '}';
    }

    public Integer getBookID()
    {
        return bookID;
    }

    public void setBookID(Integer bookID)
    {
        this.bookID = bookID;
    }

    public String getBookName()
    {
        return bookName;
    }

    public void setBookName(String bookName)
    {
        this.bookName = bookName;
    }

    public Integer getBookCounts()
    {
        return bookCounts;
    }

    public void setBookCounts(Integer bookCounts)
    {
        this.bookCounts = bookCounts;
    }

    public String getDetail()
    {
        return detail;
    }

    public void setDetail(String detail)
    {
        this.detail = detail;
    }

    public String getDb_source()
    {
        return db_source;
    }

    public void setDb_source(String db_source)
    {
        this.db_source = db_source;
    }
}

mapper层接口

package com.study.excel.mapper;

import com.study.excel.model.Book;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

@Mapper
public interface BookMapper
{
    List<Book> getAllBooks();

    boolean addBook(List<Book> list);
}

<?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.study.excel.mapper.BookMapper">
    <resultMap id="BaseResultMap" type="com.study.excel.model.Book">
        <id column="bookID" property="bookID" jdbcType="INTEGER"></id>
        <result column="bookName" property="bookName" jdbcType="VARCHAR"/>
        <result column="bookCounts" property="bookCounts" jdbcType="INTEGER"/>
        <result column="detail" property="detail" jdbcType="VARCHAR"/>
        <result column="db_source" property="db_source" jdbcType="VARCHAR"/>
    </resultMap>
    <select id="getAllBooks" resultMap="BaseResultMap">
        select * from books;
    </select>
    <insert id="addBook">
        insert into books(bookName,bookCounts,detail,db_source)
        values
        <foreach collection="list" separator="," item="bk">
            (
                #{bk.bookName,jdbcType=VARCHAR},
                #{bk.bookCounts,jdbcType=INTEGER},
                #{bk.detail,jdbcType=VARCHAR},
                #{bk.db_source,jdbcType=VARCHAR}
            )
        </foreach>
    </insert>
</mapper>

这里我被好几个地方坑到了，首先是表名写错了，wtf，其次是foreach标签下，定义每个字段的数据库类型，我自己一开始加了引号变成了jdbcType=“VARCHAR”，然后控制台一直给我报错Caused by: java.lang.IllegalArgumentException: No enum constant org.apache.ibatis.type.JdbcType.“VARCHAR”，其实是没有引号的，加了引号他的枚举类无法识别。

service服务层

package com.study.excel.service;

import com.study.excel.mapper.BookMapper;
import com.study.excel.model.Book;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * @description:
 * @author: Leo
 * @createDate: 2020/2/9
 * @version: 1.0
 */
@Service
public class ExcelService
{
    @Autowired
    BookMapper bookMapper;
    public List<Book> getAllBooks()
    {
        return bookMapper.getAllBooks();
    }

    public boolean addBook(List<Book> list)
    {
        return bookMapper.addBook(list);
    }
}

控制层

package com.study.excel.controller;

import com.study.excel.model.Book;
import com.study.excel.service.ExcelService;
import com.study.excel.utils.POIUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import java.util.List;

/**
 * @description:
 * @author: Leo
 * @createDate: 2020/2/9
 * @version: 1.0
 */
@RestController
@RequestMapping("/api/excel")
public class ToolExcelController
{
    @Autowired
    ExcelService excelService;
    @GetMapping("/")
    public List<Book> getAllBooks(){
        return excelService.getAllBooks();
    }
    @GetMapping("/export")
    public ResponseEntity<byte[]> excelExport(){
        List<Book> list=excelService.getAllBooks();
        return POIUtils.ExportExcel(list);
    }
    @PostMapping("/import")
    public String excelImport(MultipartFile file){
        List<Book> list=POIUtils.ImportExcel(file);
        if(excelService.addBook(list)){
            return "上传成功！";
        }
        return "上传失败！";
    }
}

控制跳转层

package com.study.excel.controller;

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.multipart.MultipartFile;

/**
 * @description:
 * @author: Leo
 * @createDate: 2020/2/9
 * @version: 1.0
 */
@Controller
@RequestMapping("/view/for")
public class FileController
{
    @GetMapping("/")
    public String fileUpLoad(){
        return "file";
    }
}

前端代码

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>文件上传</title>
</head>
<body>
    <form action="/api/excel/import" enctype="multipart/form-data" method="post">
        <input type="file" name="file">
        <br><br>
        <input type="submit" value="提交">
    </form>
</body>
</html>

好了，关键实现的代码马上就要贴出

我把实现方法封装成一个poi的工具类

package com.study.excel.utils;

import com.study.excel.model.Book;
import org.apache.poi.hpsf.DocumentSummaryInformation;
import org.apache.poi.hpsf.SummaryInformation;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Header;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.multipart.MultipartFile;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import java.util.List;

/**
 * @description:
 * @author: Leo
 * @createDate: 2020/2/9
 * @version: 1.0
 */
public class POIUtils
{

    public static ResponseEntity<byte[]> ExportExcel(List<Book> list)
    {
        //1.创建一个空的Excel表
        HSSFWorkbook workbook = new HSSFWorkbook();

        //3.开始创建sheet页
        HSSFSheet sheet = workbook.createSheet("书籍信息表1");

        //4.创建行
        HSSFRow row = sheet.createRow(0);

        //5.根据需要创建列
        HSSFCell c0 = row.createCell(0);
        c0.setCellValue("编号");
        HSSFCell c1 = row.createCell(1);
        c1.setCellValue("书籍名称");
        HSSFCell c2 = row.createCell(2);
        c2.setCellValue("现有数量");
        HSSFCell c3 = row.createCell(3);
        c3.setCellValue("书籍详情");
        HSSFCell c4 = row.createCell(4);
        c4.setCellValue("书籍来源");

        //6.循环遍历插入数据
        for (int i = 0; i < list.size(); i++)
        {
            Book book = list.get(i);//这里大家可以打断点调试，每个循环进来的数据都放到book对象里
            //因为我这里i=0,而第0行已经被标题给占了，所以我下面的遍历都是从1开始
            //我们可以看到，这里的逻辑可以说相当简单，我们循环创建一行，然后在那一行创建列，给每一列
            //设置对应的数据
            HSSFRow row1 = sheet.createRow(i + 1);
            row1.createCell(0).setCellValue(book.getBookID());
            row1.createCell(1).setCellValue(book.getBookName());
            row1.createCell(2).setCellValue(book.getBookCounts());
            row1.createCell(3).setCellValue(book.getDetail());
            row1.createCell(4).setCellValue(book.getDb_source());
        }

        //7.敲重点咯！这里可以说是最烦的地方。
        //首先，我们知道我们这里返回的数据类型是ResponseEntity<byte[]>，所以我们要在返回的时候实例化他
        //我们可以点击他的源码看，发现它里面有很多构造函数
        /**
         public ResponseEntity(@Nullable T body, @Nullable MultiValueMap<String, String> headers,
         HttpStatus status) {
         super(body, headers);
         Assert.notNull(status, "HttpStatus must not be null");
         this.status = status;
         }
         */
        //我们需要一个不为空的主体对象、一个头、一个状态位
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        HttpHeaders headers = new HttpHeaders();
        try
        {
            /**
             * 防止文件名乱码
             headers.setContentDispositionFormData("attachment",
             new String(exportFile.getName().getBytes("utf-8"), "ISO8859-1"));
             */
            headers.setContentDispositionFormData("attachment", new String("书籍表.xls"
                    .getBytes("UTF-8"), "ISO-8859-1"));
            //response.setContentType(MIME)的作用是使客户端浏览器，区分不同种类的数据，
            // 并根据不同的MIME调用浏览器内不同的程序嵌入模块来处理相应的数据。
            headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
            //将workbook转成byte数组
            workbook.write(baos);
        }
        catch (UnsupportedEncodingException e)
        {
            e.printStackTrace();
        }
        catch (IOException e)
        {
            e.printStackTrace();
        }

        return new ResponseEntity<byte[]>(baos.toByteArray(), headers, HttpStatus.OK);
    }

   public static List<Book> ImportExcel(MultipartFile file)
    {
        List<Book> list = new ArrayList<>();
        Book book = null;
        //创建表
        try
        {
            HSSFWorkbook workbook = new HSSFWorkbook(file.getInputStream());
            //获取sheets页
            int numberOfSheets = workbook.getNumberOfSheets();
            for (int i = 0; i < numberOfSheets; i++)
            {
                //循环获取每页的数据
                HSSFSheet sheetAt = workbook.getSheetAt(i);
                //获取行数
                int ofRows = sheetAt.getPhysicalNumberOfRows();
                for (int j = 0; j < ofRows; j++)
                {
                    if (j == 0)
                    {
                        //第一行是标题，想想不跳的话，你录到数据库里不就把标题也录进去了嘛
                        continue;
                    }
                    HSSFRow row = sheetAt.getRow(j);
                    if (row == null)
                    {
                        //跳空行
                        continue;
                    }
                    //获取表单列数据
                    int cells = row.getPhysicalNumberOfCells();
                    book = new Book();
                    for (int k = 0; k < cells; k++)
                    {
                        HSSFCell cell = row.getCell(k);
                        switch (k){
                            case 1:
                                book.setBookName(cell.getStringCellValue());
                                break;
                            case 2:
                                double numericCellValue = cell.getNumericCellValue();
                                book.setBookCounts((int) numericCellValue);
                                break;
                            case 3:
                                book.setDetail(cell.getStringCellValue());
                                break;
                            case 4:
                                book.setDb_source(cell.getStringCellValue());
                                break;
                        }

                    }
                    list.add(book);
                }
            }
        }
        catch (IOException e)
        {
            e.printStackTrace();
        }
        return list;
    }
}

关键地方的讲解，写在了注释里面。

导出实现

测试方法就是启动，在浏览器里输入http://localhost:8080/api/excel/export
能够在浏览器里下载出来，就OK！
在这里插入图片描述

在这里插入图片描述

数据库结果导出就这么实现了。

导入实现

首先在浏览器输入http://localhost:8080/view/for/，然后把你刚才导出的Excel再放回去。点提交
在这里插入图片描述
OK这样就成功了。再看数据库

总结

导出的思想总结

首先要拿到数据。

HSSFWorkbook workbook = new HSSFWorkbook();

第二步就是要创建Excel、创建表单

HSSFSheet sheet = workbook.createSheet("书籍信息表");

第三步就是创建标题行

HSSFRow row = sheet.createRow(0);
HSSFCell c0 = row.createCell(0);
c0.setCellValue("编号");
HSSFCell c1 = row.createCell(1);
c1.setCellValue("书名");
HSSFCell c2 = row.createCell(2);
c2.setCellValue("数量");
HSSFCell c3 = row.createCell(3);
c3.setCellValue("详细");
HSSFCell c4 = row.createCell(4);
c4.setCellValue("来源");

第四步是开始把数据循环插入

for (int i = 0; i < list.size(); i++)
        {
            Book book=list.get(i);
            HSSFRow row1 = sheet.createRow(i + 1);
            row1.createCell(0).setCellValue(book.getBookID());
            row1.createCell(1).setCellValue(book.getBookName());
            row1.createCell(2).setCellValue(book.getBookCounts());
            row1.createCell(3).setCellValue(book.getDetail());
            row1.createCell(4).setCellValue(book.getDb_source());
        }

第五步是构建输出流ByteArrayOutputStream，HttpHeaders还有HttpStatus的状态，最后返回给ResponseEntity

ByteArrayOutputStream baos = new ByteArrayOutputStream();
        HttpHeaders headers=new HttpHeaders();
        try
        {
            headers.setContentDispositionFormData("attachment",
                    new String("书籍.xls".getBytes("UTF-8"),"ISO-8859-1"));
            headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
            workbook.write(baos);
        }
        catch (UnsupportedEncodingException e)
        {
            e.printStackTrace();
        }
        catch (IOException e)
        {
            e.printStackTrace();
        }
        return new ResponseEntity<byte[]>(baos.toByteArray(),headers,HttpStatus.OK);

导入思想的总结

首先要把前端传来的Excel数据导入到我们创建的工作簿里

HSSFWorkbook workbook = new HSSFWorkbook(file.getInputStream());

第二步获取工作簿下的所有sheet页总数

 int numberOfSheets = workbook.getNumberOfSheets();

第三步遍历每个sheet页，先获取行数，通过行获取列的总数。进行list遍历，插入的book对象里。于是这里的话就在一开始定义一个List创建Book对象。
最后add进book对象里，返回；

OK，文件的导入和导出的基本实现就是这样，实际开发中，都是要结合一些前端框架来完成，但是都大同小异。

Author By 朝花不迟暮

在这里插入图片描述

朝花不迟暮

关注

9
点赞
踩
22

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录