SpringBoot整合POI實(shí)現(xiàn)Excel文件讀寫操作

https://blog.csdn.net/qq_46921028/article/details/133631309?spm=1001.2014.3001.5501

1.環(huán)境準(zhǔn)備

1、導(dǎo)入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 '手機(jī)號',

? ? `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', '廣州', '產(chǎn)品經(jīng)理', 15000.00),

('趙六', '女', '15145678901', '深圳', '前端工程師', 11000.00),

('劉七', '男', '15856789012', '成都', '測試工程師', 9000.00),

('陳八', '女', '13967890123', '重慶', 'UI設(shè)計師', 8000.00),

('朱九', '男', '13778901234', '武漢', '運(yùn)維工程師', 10000.00),

('楊十', '女', '15089012345', '南京', '數(shù)據(jù)工程師', 13000.00),

('孫十一', '男', '15190123456', '杭州', '后端工程師', 12000.00),

('周十二', '女', '15801234567', '天津', '產(chǎn)品設(shè)計師', 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 # 類型別名（實(shí)體類所在包）

? configuration:

? ? log-impl: org.apache.ibatis.logging.stdout.StdOutImpl? #配置標(biāo)準(zhǔn)sql輸出

5、使用?MyBatisPlus?插件生成基礎(chǔ)代碼：

①?配置數(shù)據(jù)庫：

② 使用代碼生成器生成代碼：

2. POI

Excel報表的兩種方式：

在企業(yè)級應(yīng)用開發(fā)中，Excel報表是一種常見的報表需求，Excel報表開發(fā)一般分為兩種形式：

把Excel中的數(shù)據(jù)導(dǎo)入到系統(tǒng)中；（上傳）

通過Java代碼生成Excel報表。（下載）

Excel版本：

目前世面上的Excel分為兩個大版本：Excel2003 和 Excel2007及以上版本；

Excel2003是一個特有的二進(jìn)制格式，其核心結(jié)構(gòu)是復(fù)合文檔類型的結(jié)構(gòu)，存儲數(shù)據(jù)量較?。籈xcel2007 的核心結(jié)構(gòu)是 XML 類型的結(jié)構(gòu)，采用的是基于 XML 的壓縮方式，使其占用的空間更小，操作效率更高。

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? Excel 2003? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?Excel 2007

后綴? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? xls? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?xlsx

結(jié)構(gòu)? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 二進(jìn)制格式，其核心結(jié)構(gòu)是復(fù)合文檔類型的結(jié)構(gòu)? ? ? ? ?XML類型結(jié)構(gòu)

單sheet數(shù)據(jù)量（sheet，工作表）表格共有65536行，256列? ? ? ? ? ? ?表格共有1048576行，16384列

特點(diǎn)存儲容量有限? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?基于xml壓縮，占用空間小，操作效率高

Apache POI:

Apache POI（全稱：Poor Obfuscation Implementation），是Apache軟件基金會的一個開源項(xiàng)目，它提供了一組API，可以讓Java程序讀寫 Microsoft Office 格式的文件，包括 word、excel、ppt等。

Apache POI是目前最流行的操作Microsoft Office的API組件，借助POI可以為工作提高效率,如 數(shù)據(jù)報表生成，數(shù)據(jù)批量上傳，數(shù)據(jù)備份等工作。

官網(wǎng)地址：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)內(nèi)存異常問題)

寫入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;

/**

? ? * 導(dǎo)出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)建標(biāo)題行（row）: 表頭

?Row titleRow = sheet.createRow(0); // 通過索引表示行,0表示第一行

// 4. 在標(biāo)題行中創(chuàng)建7個單元格 且 為每個單元格設(shè)置內(nèi)容數(shù)據(jù)

? ? ? ? String[] titleArr = {"用戶ID", "姓名", "性別", "電話", "所在城市", "職位", "薪資"};

? ? ? ? for (int i = 0; i < titleArr.length; i++) {

? ? ? ? ? ? Cell cell = titleRow.createCell(i); //設(shè)置單元格的位置,從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(); // 關(guān)閉流

? ? ? ? // 7.2 把excel文件輸出到瀏覽器上

? ? ? ? // 設(shè)置響應(yīng)頭信息

? ? ? ? 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(); // 關(guān)閉流

? ? ? ? workbook.close();

? ? }

}

示例2：大數(shù)量寫操作

/**

* 大數(shù)據(jù)量批量導(dǎo)出excel:SXSSF(同樣兼容XSSF)

* 官方提供了SXSSF來解決大文件寫入問題,它可以寫入非常大量的數(shù)據(jù)，比如上百萬條數(shù)據(jù)，并且寫入速度更快，占用內(nèi)存更少

* SXSSF在寫入數(shù)據(jù)時會將數(shù)據(jù)分批寫入硬盤（會產(chǎn)生臨時文件），而不是一次性將所有數(shù)據(jù)寫入硬盤。

* SXSSF通過滑動窗口限制內(nèi)存讀取的行數(shù)(默認(rèn)100行,超過100行就會寫入磁盤)，而XSSF將文檔中所有行加載到內(nèi)存中。那些不在滑動窗口中的數(shù)據(jù)是不能訪問的，因?yàn)樗鼈円呀?jīng)被寫到磁盤上了。這樣可以節(jié)省大量內(nèi)存空間 。

*/

@RequestMapping("/export2")

public void exportExcel2(HttpServletResponse response) throws IOException {

long star = System.currentTimeMillis();

? ? // 1. 創(chuàng)建excel工作簿（workbook）:SXSSFWorkbook

? ? SXSSFWorkbook workbook = new SXSSFWorkbook();//默認(rèn)窗口大小為100

? ? // 2. 創(chuàng)建excel工作表（sheet）

? ? Sheet sheet = workbook.createSheet("用戶表");

// 3. 在表中創(chuàng)建標(biāo)題行（row）: 表頭

? ? Row titleRow = sheet.createRow(0); // 通過索引表示行,0表示第一行

? ? // 4. 在標(biāo)題行中創(chuàng)建7個單元格 且 為每個單元格設(shè)置內(nèi)容數(shù)據(jù)

? ? String[] titleArr = {"用戶ID", "姓名", "性別", "電話", "所在城市", "職位", "薪資"};

? ? for (int i = 0; i < titleArr.length; i++) {

? ? ? ? Cell cell = titleRow.createCell(i); //設(shè)置單元格的位置,從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(); // 關(guān)閉流

? ? workbook.close();

? ? long end = System.currentTimeMillis();

? ? log.info("大數(shù)據(jù)量批量數(shù)據(jù)寫入用時: {} ms", end - star);

}

經(jīng)測試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("批量導(dǎo)入失敗");

? ? } finally {

? ? ? ? try {

? ? ? ? ? ? if (is != null) {

? ? ? ? ? ? ? ? is.close();

? ? ? ? ? ? }

? ? ? ? ? ? if (workbook != null) {

? ? ? ? ? ? ? ? workbook.close();

? ? ? ? ? ? }

? ? ? ? } catch (IOException e) {

? ? ? ? ? ? e.printStackTrace();

? ? ? ? ? ? throw new RuntimeException("批量導(dǎo)入失敗");

? ? ? ? }

? ? }

}

3. EasyExcel

EasyExcel是一個基于Java的、快速、簡潔、解決大文件內(nèi)存溢出的Excel處理工具。他能讓你在不用考慮性能、內(nèi)存的等因素的情況下，快速完成Excel的讀、寫等功能。

官網(wǎng)地址：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的官方文檔非常全面，我就不一一贅述了。

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容