SpringBoot整合POI實現Excel文件讀寫操作

更新時間：2023年10月08日 09:57:54 作者：白豆五

EasyExcel是一個基于Java的、快速、簡潔、解決大文件內存溢出的Excel處理工具,這篇文章主要介紹了SpringBoot整合POI實現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位小數'
) engine InnoDB comment '用戶表';
INSERT INTO `user` (`username`, `sex`, `phone`, `city`, `position`, `salary`) VALUES
('張三', '男', '13912345678', '北京', '軟件工程師', 10000.00),
('李四', '女', '13723456789', '上海', '數據分析師', 12000.00),
('王五', '男', '15034567890', '廣州', '產品經理', 15000.00),
('趙六', '女', '15145678901', '深圳', '前端工程師', 11000.00),
('劉七', '男', '15856789012', '成都', '測試工程師', 9000.00),
('陳八', '女', '13967890123', '重慶', 'UI設計師', 8000.00),
('朱九', '男', '13778901234', '武漢', '運維工程師', 10000.00),
('楊十', '女', '15089012345', '南京', '數據工程師', 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
# 數據庫配置
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 插件生成基礎代碼：

① 配置數據庫：

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

2. POI

Excel報表的兩種方式：

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

把Excel中的數據導入到系統中；（上傳）
通過Java代碼生成Excel報表。（下載）

Excel版本：

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

Excel2003是一個特有的二進制格式，其核心結構是復合文檔類型的結構，存儲數據量較小；Excel2007 的核心結構是 XML 類型的結構，采用的是基于 XML 的壓縮方式，使其占用的空間更小，操作效率更高。

Excel 2003	Excel 2007
后綴	xls	xlsx
結構	二進制格式，其核心結構是復合文檔類型的結構	XML類型結構
單sheet數據量（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可以為工作提高效率,如數據報表生成，數據批量上傳，數據備份等工作。

官網地址：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：批量寫操作(大數據量時會出現內存異常問題)

寫入excel文件步驟:

創(chuàng)建工作簿：workbook
創(chuàng)建工作表：sheet
創(chuàng)建行：row
創(chuàng)建列（單元格）：cell
具體數據寫入

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(大數據量)
        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個單元格 且 為每個單元格設置內容數據
        String[] titleArr = {"用戶ID", "姓名", "性別", "電話", "所在城市", "職位", "薪資"};
        for (int i = 0; i < titleArr.length; i++) {
            Cell cell = titleRow.createCell(i); //設置單元格的位置,從0開始
            cell.setCellValue(titleArr[i]); // 為單元格填充數據
        }
        // 5. 查詢所有用戶數據
        List<User> userList = userService.list();
        // 6. 遍歷用戶list,獲取每個用戶,并填充每一行單元格的數據
        for (int i = 0; i < userList.size(); i++) {
            User user = userList.get(i);
            // 創(chuàng)建excel的行
            Row row = sheet.createRow(i+1); // 從第二行開始,索引為1
            // 為每個單元格填充數據
            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：大數量寫操作

/**
 * 大數據量批量導出excel:SXSSF(同樣兼容XSSF)
 * 官方提供了SXSSF來解決大文件寫入問題,它可以寫入非常大量的數據，比如上百萬條數據，并且寫入速度更快，占用內存更少
 * SXSSF在寫入數據時會將數據分批寫入硬盤（會產生臨時文件），而不是一次性將所有數據寫入硬盤。
 * SXSSF通過滑動窗口限制內存讀取的行數(默認100行,超過100行就會寫入磁盤)，而XSSF將文檔中所有行加載到內存中。那些不在滑動窗口中的數據是不能訪問的，因為它們已經被寫到磁盤上了。這樣可以節(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個單元格 且 為每個單元格設置內容數據
    String[] titleArr = {"用戶ID", "姓名", "性別", "電話", "所在城市", "職位", "薪資"};
    for (int i = 0; i < titleArr.length; i++) {
        Cell cell = titleRow.createCell(i); //設置單元格的位置,從0開始
        cell.setCellValue(titleArr[i]); // 為單元格填充數據
    }
    // 5. 查詢所有用戶數據
    List<User> userList = userService.list();
    // 6. 遍歷用戶list,獲取每個用戶,并填充每一行單元格的數據
    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
        // 為每個單元格填充數據
        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("大數據量批量數據寫入用時: {} ms", end - star);
}

經測試XSSF大概十秒左右輸出excel文件，而SXSSF一秒左右輸出excel文件。

示例：讀取excel文件

讀取excel文件步驟:（通過文件流讀?。?/p>

獲取工作簿
獲取工作表（sheet）
獲取行（row）
獲取單元格（cell）
讀取數據

// 讀取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實現Excel文件讀寫操作的文章就介紹到這了,更多相關SpringBoot整合POI實現Excel文件讀寫內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

已有的springcloud+mybatis項目升級為mybatis-plus的方法
這篇文章主要介紹了已有的springcloud+mybatis項目升級為mybatis-plus，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧
2021-03-03
JAVA系統中Spring?Boot應用程序的配置文件application.yml使用詳解
這篇文章主要介紹了JAVA系統中Spring?Boot應用程序的配置文件application.yml的相關資料,application.yml是Spring?Boot應用程序的配置文件,定義了服務器、Spring、日志、安全及其他配置屬性,確保應用程序正確啟動和運行,需要的朋友可以參考下
2025-01-01
談談Java中自定義注解及使用場景
這篇文章主要介紹了談談Java中自定義注解及使用場景，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧
2020-09-09
使用java項目搭建一個netty服務
這篇文章主要為大家詳細介紹了如何使用java項目搭建一個netty服務,文中的示例代碼講解詳細,感興趣的小伙伴可以跟隨小編一起學習一下
2024-10-10
Java定義形式及可變參數實例解析
這篇文章主要介紹了Java定義形式及可變參數實例解析,文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值,需要的朋友可以參考下
2019-12-12
分析HashMap 的 JDK 源碼
這篇文章主要分析了HashMap 的 JDK 源碼，幫助大家更好的理解和學習Java，感興趣的朋友可以了解下
2020-10-10
Java可變參數的應用小結
這篇文章主要介紹了Java可變參數的應用小結,實現同一個函數名,不同參數個數,實現的方法相同,本文通過實例代碼給大家介紹的非常詳細,對大家的學習或工作具有一定的參考借鑒價值,需要的朋友可以參考下
2023-10-10
Spring對象創(chuàng)建范式的五大適用場景詳解
本文深度解析了Spring中依賴注入與直接實例化的決策邊界,破除“萬物皆Bean”誤區(qū),文章系統梳理了五大適合直接new的場景,希望對大家有所幫助
2026-06-06
springboot使用小工具之Lombok、devtools、Spring Initailizr詳解
這篇文章主要介紹了springboot使用小工具之Lombok、devtools、Spring Initailizr詳解,Lombok可以代替手寫get、set、構造方法等，需要idea裝插件lombok，本文通過示例代碼給大家介紹的非常詳細，需要的朋友可以參考下
2022-10-10
spring?cloud?Gateway如何處理跨域問題
這篇文章主要介紹了spring?cloud?Gateway如何處理跨域問題,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教
2024-04-04