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

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

EasyExcel是一個基于Java的、快速、簡潔、解決大文件內(nèi)存溢出的Excel處理工具,這篇文章主要介紹了SpringBoot整合POI實現(xiàn)Excel文件讀寫操作,首先準(zhǔn)備環(huán)境進(jìn)行一系列操作,本文給大家介紹的非常詳細(xì),需要的朋友參考下吧

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 '手機號',
    `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', '武漢', '運維工程師', 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 # 類型別名（實體類所在包）
  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ù)量較小；Excel2007 的核心結(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列
特點	存儲容量有限	基于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ù)備份等工作。

官網(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ù)是不能訪問的，因為它們已經(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的官方文檔非常全面，我就不一一贅述了。

到此這篇關(guān)于SpringBoot整合POI實現(xiàn)Excel文件讀寫操作的文章就介紹到這了,更多相關(guān)SpringBoot整合POI實現(xiàn)Excel文件讀寫內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: