SpringBoot整合POI實現(xiàn)Excel文件讀寫操作
更新時間：2023年10月08日 09:57:54 作者：白豆五
EasyExcel是一個基于Java的、快速、簡潔、解決大文件內存溢出的Excel處理工具,這篇文章主要介紹了SpringBoot整合POI實現(xiàn)Excel文件讀寫操作,首先準備環(huán)境進行一系列操作,本文給大家介紹的非常詳細,需要的朋友參考下吧
1.環(huán)境準備
1、導入sql腳本：
create database if not exists springboot default charset utf8mb4;
use springboot;
create table if not exists `user`
(
`id` bigint(20) primary key auto_increment comment '主鍵id',
`username` varchar(255) not null comment '用戶名',
`sex` char(1) not null comment '性別',
`phone` varchar(22) not null comment '手機號',
`city` varchar(255) not null comment '所在城市',
`position` varchar(255) not null comment '職位',
`salary` decimal(18, 2) not null comment '工資:長度18位,保留2位小數(shù)'
) engine InnoDB comment '用戶表';
INSERT INTO `user` (`username`, `sex`, `phone`, `city`, `position`, `salary`) VALUES
('張三', '男', '13912345678', '北京', '軟件工程師', 10000.00),
('李四', '女', '13723456789', '上海', '數(shù)據(jù)分析師', 12000.00),
('王五', '男', '15034567890', '廣州', '產品經理', 15000.00),
('趙六', '女', '15145678901', '深圳', '前端工程師', 11000.00),
('劉七', '男', '15856789012', '成都', '測試工程師', 9000.00),
('陳八', '女', '13967890123', '重慶', 'UI設計師', 8000.00),
('朱九', '男', '13778901234', '武漢', '運維工程師', 10000.00),
('楊十', '女', '15089012345', '南京', '數(shù)據(jù)工程師', 13000.00),
('孫十一', '男', '15190123456', '杭州', '后端工程師', 12000.00),
('周十二', '女', '15801234567', '天津', '產品設計師', 11000.00);
2、創(chuàng)建springboot工程 (springboot版本為2.7.13)
3、引入依賴：
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-test</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.13</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.2</version>
</dependency>
</dependencies>4、修改yml配置：
server:
port: 8001
# 數(shù)據(jù)庫配置
spring:
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/springboot?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true&useSSL=false&serverTimezone=GMT%2b8&allowPublicKeyRetrieval=true
username: root
password: 123456
# mybatisplus配置
mybatis-plus:
mmapper-locations: classpath:mapper/*.xml #mapper文件存放路徑
type-aliases-package: cn.z3inc.exceldemo.entity # 類型別名（實體類所在包）
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl #配置標準sql輸出5、使用 MyBatisPlus 插件生成基礎代碼：
① 配置數(shù)據(jù)庫：
② 使用代碼生成器生成代碼：
2. POI
Excel報表的兩種方式：
在企業(yè)級應用開發(fā)中，Excel報表是一種常見的報表需求，Excel報表開發(fā)一般分為兩種形式：
- 把Excel中的數(shù)據(jù)導入到系統(tǒng)中；（上傳）
- 通過Java代碼生成Excel報表。（下載）
Excel版本：
目前世面上的Excel分為兩個大版本：Excel2003 和 Excel2007及以上版本；
Excel2003是一個特有的二進制格式，其核心結構是復合文檔類型的結構，存儲數(shù)據(jù)量較??；Excel2007 的核心結構是 XML 類型的結構，采用的是基于 XML 的壓縮方式，使其占用的空間更小，操作效率更高。
| Excel 2003 | Excel 2007 | |
|---|---|---|
| 后綴 | xls | xlsx |
| 結構 | 二進制格式，其核心結構是復合文檔類型的結構 | XML類型結構 |
| 單sheet數(shù)據(jù)量（sheet，工作表） | 表格共有65536行，256列 | 表格共有1048576行，16384列 |
| 特點 | 存儲容量有限 | 基于xml壓縮，占用空間小，操作效率高 |
Apache POI:
Apache POI（全稱：Poor Obfuscation Implementation），是Apache軟件基金會的一個開源項目，它提供了一組API，可以讓Java程序讀寫 Microsoft Office 格式的文件，包括 word、excel、ppt等。
Apache POI是目前最流行的操作Microsoft Office的API組件，借助POI可以為工作提高效率,如 數(shù)據(jù)報表生成，數(shù)據(jù)批量上傳，數(shù)據(jù)備份等工作。
官網地址：https://poi.apache.org/
POI針對Excel的API如下：
- Workbook：工作薄，Excel的文檔對象,針對不同的Excel類型分為：HSSFWorkbook（2003）和XSSFWorkbook（2007）；
- Sheet：Excel的工作單(表)；
- Row：Excel的行；
- Cell：Excel的格子，單元格。
Java中常用的excel報表工具有：POI、easyexcel、easypoi等。
POI快速入門：
引入POI依賴：
<!--excel POI依賴-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.0.1</version>
</dependency>示例1：批量寫操作(大數(shù)據(jù)量時會出現(xiàn)內存異常問題)
寫入excel文件步驟:
- 創(chuàng)建工作簿：workbook
- 創(chuàng)建工作表：sheet
- 創(chuàng)建行：row
- 創(chuàng)建列（單元格）：cell
- 具體數(shù)據(jù)寫入
package cn.z3inc.exceldemo.controller;
import cn.z3inc.exceldemo.entity.User;
import cn.z3inc.exceldemo.service.IUserService;
import lombok.RequiredArgsConstructor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.bind.annotation.CrossOrigin;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;
/**
* <p>
* 用戶表 前端控制器
* </p>
*
* @author 白豆五
* @since 2023-10-01
*/
@CrossOrigin
@RestController
@RequiredArgsConstructor
@RequestMapping("/user")
public class UserController {
private final IUserService userService;
/**
* 導出excel
*/
@RequestMapping("/export")
public void exportExcel(HttpServletResponse response) throws IOException {
// 1. 創(chuàng)建excel工作簿（workbook）:excel2003使用HSSF,excel2007使用XSSF,excel2010使用SXSSF(大數(shù)據(jù)量)
XSSFWorkbook workbook = new XSSFWorkbook();
// 2. 創(chuàng)建excel工作表（sheet）
Sheet sheet = workbook.createSheet("用戶表");
// 3. 在表中創(chuàng)建標題行（row）: 表頭
Row titleRow = sheet.createRow(0); // 通過索引表示行,0表示第一行
// 4. 在標題行中創(chuàng)建7個單元格 且 為每個單元格設置內容數(shù)據(jù)
String[] titleArr = {"用戶ID", "姓名", "性別", "電話", "所在城市", "職位", "薪資"};
for (int i = 0; i < titleArr.length; i++) {
Cell cell = titleRow.createCell(i); //設置單元格的位置,從0開始
cell.setCellValue(titleArr[i]); // 為單元格填充數(shù)據(jù)
}
// 5. 查詢所有用戶數(shù)據(jù)
List<User> userList = userService.list();
// 6. 遍歷用戶list,獲取每個用戶,并填充每一行單元格的數(shù)據(jù)
for (int i = 0; i < userList.size(); i++) {
User user = userList.get(i);
// 創(chuàng)建excel的行
Row row = sheet.createRow(i+1); // 從第二行開始,索引為1
// 為每個單元格填充數(shù)據(jù)
row.createCell(0).setCellValue(user.getId());
row.createCell(1).setCellValue(user.getUsername());
row.createCell(2).setCellValue(user.getSex());
row.createCell(3).setCellValue(user.getPhone());
row.createCell(4).setCellValue(user.getCity());
row.createCell(5).setCellValue(user.getPosition());
row.createCell(6).setCellValue(user.getSalary().doubleValue());
}
// 7. 輸出文件
// 7.1 把excel文件寫到磁盤上
FileOutputStream outputStream = new FileOutputStream("d:/1.xlsx");
workbook.write(outputStream); // 把excel寫到輸出流中
outputStream.close(); // 關閉流
// 7.2 把excel文件輸出到瀏覽器上
// 設置響應頭信息
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment; filename=1.xlsx");
ServletOutputStream servletOutputStream = response.getOutputStream();
workbook.write(servletOutputStream);
servletOutputStream.flush(); // 刷新緩沖區(qū)
servletOutputStream.close(); // 關閉流
workbook.close();
}
}
示例2：大數(shù)量寫操作
/**
* 大數(shù)據(jù)量批量導出excel:SXSSF(同樣兼容XSSF)
* 官方提供了SXSSF來解決大文件寫入問題,它可以寫入非常大量的數(shù)據(jù)，比如上百萬條數(shù)據(jù)，并且寫入速度更快，占用內存更少
* SXSSF在寫入數(shù)據(jù)時會將數(shù)據(jù)分批寫入硬盤（會產生臨時文件），而不是一次性將所有數(shù)據(jù)寫入硬盤。
* SXSSF通過滑動窗口限制內存讀取的行數(shù)(默認100行,超過100行就會寫入磁盤)，而XSSF將文檔中所有行加載到內存中。那些不在滑動窗口中的數(shù)據(jù)是不能訪問的，因為它們已經被寫到磁盤上了。這樣可以節(jié)省大量內存空間 。
*/
@RequestMapping("/export2")
public void exportExcel2(HttpServletResponse response) throws IOException {
long star = System.currentTimeMillis();
// 1. 創(chuàng)建excel工作簿（workbook）:SXSSFWorkbook
SXSSFWorkbook workbook = new SXSSFWorkbook();//默認窗口大小為100
// 2. 創(chuàng)建excel工作表（sheet）
Sheet sheet = workbook.createSheet("用戶表");
// 3. 在表中創(chuàng)建標題行（row）: 表頭
Row titleRow = sheet.createRow(0); // 通過索引表示行,0表示第一行
// 4. 在標題行中創(chuàng)建7個單元格 且 為每個單元格設置內容數(shù)據(jù)
String[] titleArr = {"用戶ID", "姓名", "性別", "電話", "所在城市", "職位", "薪資"};
for (int i = 0; i < titleArr.length; i++) {
Cell cell = titleRow.createCell(i); //設置單元格的位置,從0開始
cell.setCellValue(titleArr[i]); // 為單元格填充數(shù)據(jù)
}
// 5. 查詢所有用戶數(shù)據(jù)
List<User> userList = userService.list();
// 6. 遍歷用戶list,獲取每個用戶,并填充每一行單元格的數(shù)據(jù)
for (int i = 0; i < 65536; i++) {
User user;
if (i > userList.size() - 1) {
user = userList.get(userList.size() - 1);
} else {
user = userList.get(i);
}
// 創(chuàng)建excel的行
Row row = sheet.createRow(i + 1); // 從第二行開始,索引為1
// 為每個單元格填充數(shù)據(jù)
row.createCell(0).setCellValue(user.getId());
row.createCell(1).setCellValue(user.getUsername());
row.createCell(2).setCellValue(user.getSex());
row.createCell(3).setCellValue(user.getPhone());
row.createCell(4).setCellValue(user.getCity());
row.createCell(5).setCellValue(user.getPosition());
row.createCell(6).setCellValue(user.getPosition());
}
// 7. 輸出文件
// 7.1 把excel文件寫到磁盤上
FileOutputStream outputStream = new FileOutputStream("d:/2.xlsx");
workbook.write(outputStream); // 把excel寫到輸出流中
outputStream.close(); // 關閉流
workbook.close();
long end = System.currentTimeMillis();
log.info("大數(shù)據(jù)量批量數(shù)據(jù)寫入用時: {} ms", end - star);
}經測試XSSF大概十秒左右輸出excel文件，而SXSSF一秒左右輸出excel文件。
示例：讀取excel文件
讀取excel文件步驟:（通過文件流讀?。?/p>
- 獲取工作簿
- 獲取工作表（sheet）
- 獲取行（row）
- 獲取單元格（cell）
- 讀取數(shù)據(jù)
// 讀取excel文件
@RequestMapping("/upload")
public void readExcel(MultipartFile file) {
InputStream is = null;
XSSFWorkbook workbook = null;
try {
// 1. 創(chuàng)建excel工作簿（workbook）
is = file.getInputStream();
workbook = new XSSFWorkbook(is);
// 2. 獲取要解析的工作表（sheet）
Sheet sheet = workbook.getSheetAt(0); // 獲取第一個sheet
// 3. 獲取表格中的每一行，排除表頭，從第二行開始
User user;
List<User> list = new ArrayList<>();
for (int i = 1; i <= sheet.getLastRowNum(); i++) {
Row row = sheet.getRow(i); // 獲取第i行
// 4. 獲取每一行的每一列,并為user對象的屬性賦值，添加到list集合中
user = new User();
user.setUsername(row.getCell(1).getStringCellValue());
user.setSex(row.getCell(2).getStringCellValue());
user.setPhone(row.getCell(3).getStringCellValue());
user.setCity(row.getCell(4).getStringCellValue());
user.setPosition(row.getCell(5).getStringCellValue());
user.setSalary(new BigDecimal(row.getCell(6).getNumericCellValue()));
list.add(user);
}
// 5. 批量保存
userService.saveBatch(list);
} catch (IOException e) {
e.printStackTrace();
throw new RuntimeException("批量導入失敗");
} finally {
try {
if (is != null) {
is.close();
}
if (workbook != null) {
workbook.close();
}
} catch (IOException e) {
e.printStackTrace();
throw new RuntimeException("批量導入失敗");
}
}
}
3. EasyExcel
EasyExcel是一個基于Java的、快速、簡潔、解決大文件內存溢出的Excel處理工具。他能讓你在不用考慮性能、內存的等因素的情況下，快速完成Excel的讀、寫等功能。
官網地址：https://easyexcel.opensource.alibaba.com/
文檔地址：https://easyexcel.opensource.alibaba.com/docs/current/
示例代碼：https://github.com/alibaba/easyexcel/tree/master/easyexcel-test/src/test/java/com/alibaba/easyexcel/test/demo
pom依賴：
<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.2.1</version>
</dependency>最后，EasyExcel的官方文檔非常全面，我就不一一贅述了。
到此這篇關于SpringBoot整合POI實現(xiàn)Excel文件讀寫操作的文章就介紹到這了,更多相關SpringBoot整合POI實現(xiàn)Excel文件讀寫內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
springboot提供了一種快速使用spring的方式，基于coc的思想，即約定大于配置，通過本文學習快速掌握springboot入門知識，感興趣的朋友跟隨小編一起看看吧2021-07-07
你知道在Java中Integer和int的這些區(qū)別嗎?
最近面試,突然被問道,說一下Integer和int的區(qū)別.額…可能平時就知道寫一些業(yè)務代碼,包括面試的一些Spring源碼等,對于這種特別基礎的反而忽略了,導致面試的時候突然被問到反而不知道怎么回答了.哎,還是乖乖再看看底層基礎,順帶記錄一下把 ,需要的朋友可以參考下2021-06-06
SpringCloudAlibaba整合Feign實現(xiàn)遠程HTTP調用的簡單示例
這篇文章主要介紹了SpringCloudAlibaba 整合 Feign 實現(xiàn)遠程 HTTP 調用,文章中使用的是OpenFeign，是Spring社區(qū)開發(fā)的組件，需要的朋友可以參考下2021-09-09
詳解Spring bean的注解注入之@Autowired的原理及使用
之前講過bean注入是什么,也使用了xml的配置文件進行bean注入,這也是Spring的最原始的注入方式（xml注入）.本文主要講解的注解有以下幾個：@Autowired、 @Service、@Repository、@Controller 、@Component、@Bean、@Configuration、@Resource ,需要的朋友可以參考下2021-06-06
StringBuilder是一個可變的字符序列，這個類提供了一個與StringBuffer兼容的API。本文主要為大家介紹了StringBuilder類的常用用法，需要的可以參考一下2023-05-05
這篇文章主要介紹了Java中局部變量和成員變量的區(qū)別，本文將通過示例為大家詳細講講Java中成員變量與局部變量之間的區(qū)別，感興趣的同學可以了解一下2023-05-05
這篇文章主要介紹了Java發(fā)送郵件遇到的常見需求匯總的相關資料,非常不錯，具有參考借鑒價值，需要的朋友可以參考下2016-06-06
Eclipse運行android項目報錯Unable to build: the file dx.jar was not
今天小編就為大家分享一篇關于Eclipse運行android項目報錯Unable to build: the file dx.jar was not loaded from the SDK folder的解決辦法，小編覺得內容挺不錯的，現(xiàn)在分享給大家，具有很好的參考價值，需要的朋友一起跟隨小編來看看吧2018-12-12
這篇文章主要介紹了java簡單解析xls文件的方法,結合實例形式分析了java針對xls文件的讀取和寫入相關操作技巧與注意事項,需要的朋友可以參考下2017-06-06
這篇文章主要介紹了springboot中jsp配置tiles全過程，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2022-10-10