利用poi-ooxml实现 Excel表格导入数据库与从数据库导出Excel表格

本文主要是介绍利用poi-ooxml实现 Excel表格导入数据库与从数据库导出Excel表格，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

一、介绍

POI OOXML是Apache POI项目中的一个子项目，它旨在提供对Microsoft Office Open XML（OOXML）格式（例如docx，xlsx和pptx）的读写支持。它允许Java开发人员在他们的应用程序中读取和写入Microsoft Office格式的文件，同时仍然保持格式的完整性。通过使用POI OOXML，开发人员可以创建和修改Microsoft Office文件，从而为他们的用户提供更好的体验和更多的功能。

本文前面会按分类一块一块讲解，最后会附上完整代码！

废话不多说，我们直接开始

二、准备工作

本次案例使用 Springboot + Mybatis + HTML

Excel表格的后缀为 .xlsx

2.1依赖注入

poi-ooxml依赖注入:

<dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>5.2.3</version>
</dependency>

完整的pom.xml:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd"><modelVersion>4.0.0</modelVersion><parent><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-parent</artifactId><version>2.7.16</version><relativePath/> <!-- lookup parent from repository --></parent><groupId>org.progingo</groupId><artifactId>ImportToSQL</artifactId><version>0.9-TEST</version><name>ImportToSQL</name><description>ImportToSQL</description><properties><java.version>1.8</java.version></properties><dependencies><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.3.0</version></dependency><dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-test</artifactId><scope>test</scope></dependency><dependency><groupId>com.mysql</groupId><artifactId>mysql-connector-j</artifactId><version>8.0.33</version><scope>runtime</scope></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>5.2.3</version></dependency></dependencies><build><plugins><plugin><groupId>org.springframework.boot</groupId><artifactId>spring-boot-maven-plugin</artifactId></plugin></plugins></build></projecXM

2.2自定义实体类

package org.progingo.importtosql.domain;public class Student {private int uid;private String name;public int getUid() {return uid;}public void setUid(int uid) {this.uid = uid;}public String getName() {return name;}public void setName(String name) {this.name = name;}@Overridepublic String toString() {return "Student{" +"uid=" + uid +", name='" + name + '\'' +'}';}
}

三、从Excel表格导入到数据库

需求：在网页上点击按钮上传.xlsx文件后，能将excel表格中的内容导入到数据库中

3.1前端页面

我们简单带过一下前端页面

(确实有亿点简陋...)

代码：

<!DOCTYPE html>
<html lang="en">
<head><meta charset="UTF-8"><title>Title</title>
</head>
<body>
<form method="post" enctype="multipart/form-data" id="form"><div><input type="file" name="uploadfile" id="wj"></div><div class="sc"><input id="sc-btn" type="button" value="上传" class="btn_sc"></div>
</form><div><p>批量导出：</p><button id="ge" onclick="location.href='/test/getExcel'">获取</button></div></body>
<script src="./js/jquery-1.9.1.min.js"></script>
<script type="text/javascript">$("#sc-btn").click(function () {$.ajax({url:"/test/import",type:"POST",data:new FormData($("form")[0]),processData:false,contentType:false,dataType:"JSON",success:function () {alert("成功");}})})</script></html>

3.2后端部分

接下来我们看一下最核心的后端部分

控制层Controller

控制层只需要接收前端传递过来的文件，将MulitipartFile文件传递给业务层

@RequestMapping("/import")
@ResponseBody
public void demo(MultipartFile uploadfile) throws IOException {importService.importFile(uploadfile);
}

业务层Service

首先我们要先了解这要用到的工具类：

XSSFWorkbook：它的一个实例化对象相当于一个excel文件，构造方法为 XSSFWorkbook(InputStream is)public XSSFSheet getSheet(String name)；获取excel文件中的页对象

XSSFSheet：它的一个实例化对象相当于excel文件中的一页表，默认使用的都是第一页表，而名称默认为Sheet1

        public XSSFRow getRow(int rownum)；获取页中的行对象

        public int getLastRowNum()；返回一页中最后一行的行号(第一行从0开始算)

XSSFRow：它的一个实例化对象相当于一页表中的一行。

        public XSSFCell getCell(int cellnum)；获取到具体的单元格对象

XSSFCell：它的实例化对象相当于一个具体的单元格

        public double getNumericCellValue()；获取单元格中的数据（适用于浮点数、整数类型）

        public String getStringCellValue()；获取单元格的数据（适用于字符串类型）以及本文暂时还用不到的获取布尔值类型和获取时间类型的方法：

        public boolean getBooleanCellValue()和public Date getDateCellValue()

好，简单地了解了下工具类和一些简单的方法后就足够我们完成读取表格内容的工作了：

我们获取到文件，进而获取到表格文件、表的实例对象，然后遍历每一行数据，然后将数据写进对应的实体类，并将实体类的实例放进List集合中，方便导入MySQL数据库。

    public void importFile(MultipartFile file) throws IOException {List<Student> datas = new ArrayList<>();//存放表格中导出的数据的实体InputStream is;try {is = file.getInputStream();//获取文件的流} catch (IOException e) {throw new RuntimeException(e);}//XSSFWorkbook的一个实例化对象相当于一个excel文件XSSFWorkbook sheets = new XSSFWorkbook(is);is.close();//在这里关闭流，如果放在后面关闭或者不关闭会报错：java.io.UncheckedIOException: Cannot delete XXX(一个缓存文件的路径)//XSSFSheet的一个实例化对象相当于excel中的一张表XSSFSheet sheet = sheets.getSheet("Sheet1");//或者sheets.getSheet(0)，参数传入0默认是第一张表//sheet.getLastRowNum();获取到最后一行的索引，第一行为0.从0开始，表中有三行数据时返回2for (int i = 0;i <= sheet.getLastRowNum();++i){//遍历表中的每一行//XSSFRow的一个实例化对象相当于一张表中的一行XSSFRow row = sheet.getRow(i);//这里是获取表中的第i行/*            double id = row.getCell(0).getNumericCellValue();String s = row.getCell(1).getStringCellValue();*///XSSFCell 的一个实例化相当于一行数据中的某一列，也就是一个表格。XSSFCell cell1 = row.getCell(0);//这里获取的就是第i行第0列数据XSSFCell cell2 = row.getCell(1);//这里获取的就是第i行第1列数据//从表格对象中获取到表格中的数据内容//不同的数据类型有不同的方法来获取double id = cell1.getNumericCellValue();String s = cell2.getStringCellValue();Student student = new Student();student.setUid((int) id);student.setName(s);datas.add(student);}System.out.println(datas);importMapper.importE(datas);//将集合传递进mapper层}

注意：在我写的案例中，应该在前面用完InputStream流后应该及时关闭，不然会报错。报错内容如下：

java.io.UncheckedIOException: Cannot delete XXX。这个XXX是个.tmp文件路径，应该是不能删除缓存文件导致的。

持久层Mapper

拿到装满实体类的List集合后，我们终于来到Mapper层

(呼呼~)

在这里我们已经完成了从表格中读取数据并保存到集合中的操作

这是mapper接口：

void importE(List<Student> datas);

下面是SQL语句的xml文件

    <insert id="importE" parameterType="java.util.List">INSERT INTO student (`uid`, `name`)VALUES<foreach collection="datas" item="student" separator=",">(#{student.uid},#{student.name})</foreach></insert>

我们用<foreach>遍历list中的对象，插入数据。

批量插入数据可以参考这篇博客：

Mybatis批量插入的四种方式https://blog.csdn.net/m0_56287495/article/details/131194750

3.3效果以及可能出现的问题

到这，我们将Excel中的数据导入到MySQL就完成啦。下面展示下效果

在使用中遇到过两个问题：

1、上面说的IO流问题

2、表页名错误的问题，我们知道在电脑上新建Excel表格打开后第一页表的默认名为“Sheet1”，但是在下面我们从数据库导入到excel表的时候，新建的表页如果不指定参数，默认的名为“Sheet0”，这时候再用这个表导入到SQL中要注意参数改成对应的表页名，不然可能会出现空指针异常。

四、将数据库中的数据导出为Excel表格

4.1前端页面

3.1展示的前端页面里就包括了这个，这里CtrlCV一下：

获取功能的代码如下：

<div><p>批量导出：</p><button id="ge" onclick="location.href='/test/getExcel'">获取</button>
</div>

4.2后端部分

这里我们反过来写应该会好看一点，所以接下来的顺序是：持久层--业务层--控制层

持久层Mapper

在持久层我们直接写上一句最简单的查询语句，返回的是一个List集合，泛型为自定义的Student类。

业务层Service

在这里我们创建表格实例化对象，然后将从SQL中获取到的数据添加进去，返回一个创建好的表格实例化对象就好。

public XSSFWorkbook outExcl(){List<Student> datas = importMapper.findAll();//从mapper层取到数据集合//创建一个表格文件对象XSSFWorkbook sheets = new XSSFWorkbook();//创建一个表页对象XSSFSheet sheet = sheets.createSheet("Sheet1");//没有形参创建的表名为Sheet0，所以推荐还是加上参数"Sheet1"//通过遍历将数据取出来并存放到表中for (int i = 0; i < datas.size(); ++i) {XSSFRow row = sheet.createRow(i);row.createCell(0).setCellValue(datas.get(i).getUid());row.createCell(1).setCellValue(datas.get(i).getName());}//返回一个表格对象return sheets;
}

控制层Controller

在这里，我们要获取到HttpsServletResponse，通过它设置响应信息以及将表格通过流添加进去，从而让前端接收。

@RequestMapping("/getExcel")
@ResponseBody
public void demo_2(HttpServletResponse response) throws Exception {XSSFWorkbook sheets = importService.outExcl();//设置响应信息response.setContentType("application/vnd.ms-excel;charset=utf-8");response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("表格3.xlsx","UTF-8"));ServletOutputStream ots = response.getOutputStream();//获取到响应文件流sheets.write(ots);//将表格写入流中ots.flush();//刷新流ots.close();//记得关闭！
}

在我的示范代码中，文件名固定为"表格3.xlsx"，在实际中推荐用动态的文件名，比如时间、时间戳、生成UUID等。

4.3效果

五、最后的最后

本文一直围绕的是.xlsx后缀的表格展开的，对于.xls后缀的表格文件我们只需将创建表格实例化对象的 XSSFWorkbook 改为 HSSFWorkbook即可。

我们知道，在excel表格中，我们可以设置单元格样式，可以设置字体颜色等等，大家可以参考下面这篇文章设置单元格和字体：

JAVA导入/出EXCEL表格 POI_poi-ooxml-CSDN博客POI简介：目前最新版本是Apache POI 4.1.0（Apache POI团队于2019年4月9日宣布发布），个人觉得旧版本中的3.6版比较稳定。自版本POI 4.0.1起，POI需要Java 8或更高版本。POI 3.11及更高版本3.x版本需要JDK 1.6或更高版本。POI 3.5到3.10需要JDK 1.5或更高版本。3.5之前的版本需要JDK 1.4+。Apac..._poi-ooxmlhttps://blog.csdn.net/u012431703/article/details/94716983