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

2023-12-23 01:30
文章标签 实现 excel 数据库 导出 表格 导入 poi ooxml

本文主要是介绍利用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批量插入的四种方式icon-default.png?t=N7T8https://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

这篇关于利用poi-ooxml实现 Excel表格导入数据库 与 从数据库导出Excel表格的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/526239。

相关文章

Spring Security基于数据库验证流程详解

Spring Security基于数据库验证流程详解

Spring Security 校验流程图 相关解释说明（认真看哦） AbstractAuthenticationProcessingFilter 抽象类 /*** 调用 #requiresAuthentication(HttpServletRequest, HttpServletResponse) 决定是否需要进行验证操作。* 如果需要验证，则会调用 #attemptAuthentica
阅读更多...
python: 多模块(.py)中全局变量的导入

python: 多模块(.py)中全局变量的导入

文章目录 global关键字可变类型和不可变类型数据的内存地址单模块(单个py文件)的全局变量示例总结 多模块(多个py文件)的全局变量from x import x导入全局变量示例 import x导入全局变量示例 总结 global关键字 global 的作用范围是模块(.py)级别： 当你在一个模块（文件）中使用 global 声明变量时，这个变量只在该模块的全局命名空
阅读更多...
hdu1043（八数码问题，广搜 + hash（实现状态压缩） ）

hdu1043（八数码问题，广搜 + hash（实现状态压缩） ）

利用康拓展开将一个排列映射成一个自然数，然后就变成了普通的广搜题。 #include<iostream>#include<algorithm>#include<string>#include<stack>#include<queue>#include<map>#include<stdio.h>#include<stdlib.h>#include<ctype.h>#inclu
阅读更多...
MySQL数据库宕机，启动不起来，教你一招搞定！

MySQL数据库宕机，启动不起来，教你一招搞定！

作者介绍：老苏，10余年DBA工作运维经验，擅长Oracle、MySQL、PG、Mongodb数据库运维（如安装迁移，性能优化、故障应急处理等）公众号：老苏畅谈运维欢迎关注本人公众号，更多精彩与您分享。 MySQL数据库宕机，数据页损坏问题，启动不起来，该如何排查和解决，本文将为你说明具体的排查过程。 查看MySQL error日志 查看 MySQL error日志，排查哪个表（表空间
阅读更多...
【C++】_list常用方法解析及模拟实现

【C++】_list常用方法解析及模拟实现

相信自己的力量，只要对自己始终保持信心，尽自己最大努力去完成任何事，就算事情最终结果是失败了，努力了也不留遗憾。💓💓💓 目录   ✨说在前面 🍋知识点一：什么是list？ •🌰1.list的定义 •🌰2.list的基本特性 •🌰3.常用接口介绍 🍋知识点二：list常用接口 •🌰1.默认成员函数 🔥构造函数（⭐） 🔥析构函数 •🌰2.list对象
阅读更多...
【Prometheus】PromQL向量匹配实现不同标签的向量数据进行运算

【Prometheus】PromQL向量匹配实现不同标签的向量数据进行运算

✨✨ 欢迎大家来到景天科技苑✨✨ 🎈🎈 养成好习惯，先赞后看哦~🎈🎈 🏆 作者简介：景天科技苑 🏆《头衔》：大厂架构师，华为云开发者社区专家博主，阿里云开发者社区专家博主，CSDN全栈领域优质创作者，掘金优秀博主，51CTO博客专家等。 🏆《博客》：Python全栈，前后端开发，小程序开发，人工智能，js逆向，App逆向，网络系统安全，数据分析，Django，fastapi
阅读更多...
让树莓派智能语音助手实现定时提醒功能

让树莓派智能语音助手实现定时提醒功能

最初的时候是想直接在rasa 的chatbot上实现，因为rasa本身是带有remindschedule模块的。不过经过一番折腾后，忽然发现，chatbot上实现的定时，语音助手不一定会有响应。因为，我目前语音助手的代码设置了长时间无应答会结束对话，这样一来，chatbot定时提醒的触发就不会被语音助手获悉。那怎么让语音助手也具有定时提醒功能呢？ 我最后选择的方法是用threading.Time
阅读更多...
Android实现任意版本设置默认的锁屏壁纸和桌面壁纸（两张壁纸可不一致）

Android实现任意版本设置默认的锁屏壁纸和桌面壁纸（两张壁纸可不一致）

客户有些需求需要设置默认壁纸和锁屏壁纸  在默认情况下 这两个壁纸是相同的  如果需要默认的锁屏壁纸和桌面壁纸不一样 需要额外修改 Android13实现 替换默认桌面壁纸： 将图片文件替换frameworks/base/core/res/res/drawable-nodpi/default_wallpaper.*  （注意不能是bmp格式） 替换默认锁屏壁纸： 将图片资源放入vendo
阅读更多...
C#实战|大乐透选号器[6]：实现实时显示已选择的红蓝球数量

C#实战|大乐透选号器[6]：实现实时显示已选择的红蓝球数量

哈喽，你好啊，我是雷工。 关于大乐透选号器在前面已经记录了5篇笔记，这是第6篇； 接下来实现实时显示当前选中红球数量，蓝球数量； 以下为练习笔记。 01 效果演示 当选择和取消选择红球或蓝球时，在对应的位置显示实时已选择的红球、蓝球的数量； 02 标签名称 分别设置Label标签名称为：lblRedCount、lblBlueCount
阅读更多...
Kubernetes PodSecurityPolicy：PSP能实现的5种主要安全策略

Kubernetes PodSecurityPolicy：PSP能实现的5种主要安全策略

Kubernetes PodSecurityPolicy：PSP能实现的5种主要安全策略 1. 特权模式限制2. 宿主机资源隔离3. 用户和组管理4. 权限提升控制5. SELinux配置 💖The Begin💖点点关注，收藏不迷路💖 Kubernetes的PodSecurityPolicy（PSP）是一个关键的安全特性，它在Pod创建之前实施安全策略，确保P
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2