SpringBoot中Excel處理完全指南分享

更新時(shí)間：2025年04月21日 10:55:07 作者：全棧凱哥

這篇文章主要介紹了SpringBoot中Excel處理完全指南,具有很好的參考價(jià)值,希望對(duì)大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教

SpringBoot中Excel處理指南

1. Excel處理基礎(chǔ)知識(shí)

1.1 為什么需要在應(yīng)用中處理Excel文件？

在企業(yè)應(yīng)用開(kāi)發(fā)中，Excel文件處理是一個(gè)非常常見(jiàn)的需求，主要用于以下場(chǎng)景：

數(shù)據(jù)導(dǎo)入：允許用戶(hù)通過(guò)Excel上傳批量數(shù)據(jù)到系統(tǒng)
數(shù)據(jù)導(dǎo)出：將系統(tǒng)數(shù)據(jù)導(dǎo)出為Excel供用戶(hù)下載分析
報(bào)表生成：生成復(fù)雜的報(bào)表并格式化為Excel
數(shù)據(jù)交換：作為不同系統(tǒng)間交換數(shù)據(jù)的媒介
批量數(shù)據(jù)處理：處理大量結(jié)構(gòu)化數(shù)據(jù)

1.2 Java中的Excel處理庫(kù)介紹

Java中處理Excel文件的主要庫(kù)有以下幾種：

1.2.1 Apache POI

Apache POI是Java中使用最廣泛的Excel處理庫(kù)，提供了全面的API來(lái)創(chuàng)建、讀取和修改Office文檔。

優(yōu)點(diǎn)：

功能全面，支持Excel所有功能
支持.xls (HSSF - Excel 97-2003)和.xlsx (XSSF - Excel 2007+)格式
社區(qū)活躍，文檔豐富
支持公式計(jì)算、圖表、合并單元格等高級(jí)功能

缺點(diǎn)：

API相對(duì)復(fù)雜
處理大文件時(shí)內(nèi)存消耗大（尤其是XSSF）

1.2.2 EasyExcel

EasyExcel是阿里巴巴開(kāi)源的Excel處理庫(kù)，基于POI，但做了大量?jī)?yōu)化。

優(yōu)點(diǎn)：

內(nèi)存占用低，使用SAX模式讀取，避免OOM
API簡(jiǎn)單易用，注解驅(qū)動(dòng)
讀寫(xiě)速度快
適合處理大型Excel文件

缺點(diǎn)：

功能不如POI全面
靈活性相對(duì)較低

1.2.3 JExcel

JExcel是另一個(gè)處理Excel的Java庫(kù)。

優(yōu)點(diǎn)：

API較簡(jiǎn)單
速度較快

缺點(diǎn)：

僅支持舊版Excel (.xls)格式
不再積極維護(hù)
功能有限

1.2.4 Apache POI SXSSF

SXSSF是POI提供的一種流式處理模式，專(zhuān)為處理大型Excel文件設(shè)計(jì)。

優(yōu)點(diǎn)：

大大降低內(nèi)存占用
適合生成大型Excel文件

缺點(diǎn)：

僅支持寫(xiě)入操作，不支持讀取
功能比XSSF受限

1.3 Spring Boot中集成Excel處理

Spring Boot本身不提供Excel處理功能，但可以輕松集成上述各種Excel處理庫(kù)。本指南將主要介紹：

如何在Spring Boot項(xiàng)目中集成Apache POI和EasyExcel
如何實(shí)現(xiàn)Excel導(dǎo)入導(dǎo)出的常見(jiàn)功能
如何處理常見(jiàn)問(wèn)題和優(yōu)化性能

2. 在Spring Boot中集成Excel處理庫(kù)

2.1 集成Apache POI

2.1.1 添加依賴(lài)

在pom.xml文件中添加以下依賴(lài)：

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>5.2.3</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>5.2.3</version>
</dependency>

如果使用Gradle，在build.gradle中添加：

implementation 'org.apache.poi:poi:5.2.3'
implementation 'org.apache.poi:poi-ooxml:5.2.3'

2.1.2 創(chuàng)建基本配置類(lèi)

創(chuàng)建一個(gè)配置類(lèi)來(lái)處理Excel相關(guān)的配置：

package com.example.excel.config;

import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.web.multipart.MultipartResolver;
import org.springframework.web.multipart.commons.CommonsMultipartResolver;

@Configuration
public class ExcelConfig {
    
    @Bean
    public MultipartResolver multipartResolver() {
        CommonsMultipartResolver resolver = new CommonsMultipartResolver();
        resolver.setMaxUploadSize(10485760); // 設(shè)置上傳文件最大為10MB
        return resolver;
    }
}

2.2 集成EasyExcel

2.2.1 添加依賴(lài)

在pom.xml文件中添加以下依賴(lài)：

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.2.1</version>
</dependency>

如果使用Gradle，在build.gradle中添加：

implementation 'com.alibaba:easyexcel:3.2.1'

2.2.2 創(chuàng)建配置類(lèi)

package com.example.excel.config;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.web.multipart.MultipartResolver;
import org.springframework.web.multipart.commons.CommonsMultipartResolver;

@Configuration
public class EasyExcelConfig {
    
    @Bean
    public MultipartResolver multipartResolver() {
        CommonsMultipartResolver resolver = new CommonsMultipartResolver();
        resolver.setMaxUploadSize(10485760); // 設(shè)置上傳文件最大為10MB
        return resolver;
    }
}

3. 使用Apache POI讀取Excel文件

3.1 創(chuàng)建數(shù)據(jù)模型

首先，創(chuàng)建一個(gè)模型類(lèi)來(lái)映射Excel中的數(shù)據(jù)：

package com.example.excel.model;

import lombok.Data;

@Data
public class User {
    private Long id;
    private String name;
    private Integer age;
    private String email;
    private String department;
}

3.2 創(chuàng)建Excel讀取服務(wù)

創(chuàng)建一個(gè)服務(wù)類(lèi)來(lái)處理Excel文件讀?。?/p>

package com.example.excel.service;

import com.example.excel.model.User;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

@Service
public class ExcelService {

    public List<User> readUsersFromExcel(MultipartFile file) throws IOException {
        List<User> userList = new ArrayList<>();
        
        // 獲取工作簿
        try (InputStream inputStream = file.getInputStream()) {
            Workbook workbook = WorkbookFactory.create(inputStream);
            
            // 獲取第一個(gè)工作表
            Sheet sheet = workbook.getSheetAt(0);
            
            // 跳過(guò)標(biāo)題行
            Iterator<Row> rowIterator = sheet.rowIterator();
            if (rowIterator.hasNext()) {
                rowIterator.next(); // 跳過(guò)標(biāo)題行
            }
            
            // 遍歷數(shù)據(jù)行
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                User user = new User();
                
                // 讀取單元格數(shù)據(jù)
                user.setId((long) row.getCell(0, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getNumericCellValue());
                user.setName(getCellValueAsString(row.getCell(1)));
                user.setAge((int) row.getCell(2, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getNumericCellValue());
                user.setEmail(getCellValueAsString(row.getCell(3)));
                user.setDepartment(getCellValueAsString(row.getCell(4)));
                
                userList.add(user);
            }
            
            workbook.close();
        }
        
        return userList;
    }
    
    // 獲取單元格的字符串值
    private String getCellValueAsString(Cell cell) {
        if (cell == null) {
            return "";
        }
        
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    return String.valueOf((int) cell.getNumericCellValue());
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            default:
                return "";
        }
    }
}

3.3 創(chuàng)建Controller處理Excel上傳

創(chuàng)建一個(gè)Controller來(lái)處理Excel文件上傳：

package com.example.excel.controller;

import com.example.excel.model.User;
import com.example.excel.service.ExcelService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.util.List;

@RestController
@RequestMapping("/api/excel")
public class ExcelController {

    @Autowired
    private ExcelService excelService;

    @PostMapping("/upload")
    public ResponseEntity<List<User>> uploadExcel(@RequestParam("file") MultipartFile file) {
        try {
            List<User> users = excelService.readUsersFromExcel(file);
            return ResponseEntity.ok(users);
        } catch (IOException e) {
            e.printStackTrace();
            return ResponseEntity.badRequest().build();
        }
    }
}

3.4 創(chuàng)建HTML上傳頁(yè)面

在src/main/resources/templates目錄下創(chuàng)建upload.html：

<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title>Excel上傳</title>
    <link rel="stylesheet"  rel="external nofollow"  rel="external nofollow" >
</head>
<body>
    <div class="container mt-5">
        <div class="card">
            <div class="card-header">
                <h3>上傳Excel文件</h3>
            </div>
            <div class="card-body">
                <form id="uploadForm" enctype="multipart/form-data">
                    <div class="form-group">
                        <label for="file">選擇Excel文件:</label>
                        <input type="file" class="form-control-file" id="file" name="file" accept=".xls,.xlsx">
                    </div>
                    <button type="button" class="btn btn-primary" onclick="uploadExcel()">上傳</button>
                </form>
                
                <div class="mt-4">
                    <h4>上傳結(jié)果:</h4>
                    <div id="resultContainer"></div>
                </div>
            </div>
        </div>
    </div>
    
    <script src="https://code.jquery.com/jquery-3.5.1.min.js"></script>
    <script>
        function uploadExcel() {
            var formData = new FormData(document.getElementById('uploadForm'));
            
            $.ajax({
                url: '/api/excel/upload',
                type: 'POST',
                data: formData,
                processData: false,
                contentType: false,
                success: function(response) {
                    var resultHtml = '<table class="table table-striped">' +
                                    '<thead><tr><th>ID</th><th>姓名</th><th>年齡</th><th>郵箱</th><th>部門(mén)</th></tr></thead>' +
                                    '<tbody>';
                    
                    for (var i = 0; i < response.length; i++) {
                        var user = response[i];
                        resultHtml += '<tr>' +
                                     '<td>' + user.id + '</td>' +
                                     '<td>' + user.name + '</td>' +
                                     '<td>' + user.age + '</td>' +
                                     '<td>' + user.email + '</td>' +
                                     '<td>' + user.department + '</td>' +
                                     '</tr>';
                    }
                    
                    resultHtml += '</tbody></table>';
                    
                    $('#resultContainer').html(resultHtml);
                },
                error: function(error) {
                    $('#resultContainer').html('<div class="alert alert-danger">上傳失敗: ' + error.responseText + '</div>');
                }
            });
        }
    </script>
</body>
</html>

3.5 處理更復(fù)雜的Excel結(jié)構(gòu)

在實(shí)際應(yīng)用中，Excel結(jié)構(gòu)可能更復(fù)雜，如多個(gè)工作表、合并單元格、公式等。以下是處理這些情況的示例：

public List<Department> readComplexExcel(MultipartFile file) throws IOException {
    List<Department> departments = new ArrayList<>();
    
    try (InputStream inputStream = file.getInputStream()) {
        Workbook workbook = WorkbookFactory.create(inputStream);
        
        // 讀取部門(mén)信息（第一個(gè)工作表）
        Sheet departmentSheet = workbook.getSheetAt(0);
        for (int i = 1; i <= departmentSheet.getLastRowNum(); i++) {
            Row row = departmentSheet.getRow(i);
            if (row == null) continue;
            
            Department department = new Department();
            department.setId((long) row.getCell(0).getNumericCellValue());
            department.setName(row.getCell(1).getStringCellValue());
            department.setManager(row.getCell(2).getStringCellValue());
            department.setEmployees(new ArrayList<>());
            
            departments.add(department);
        }
        
        // 讀取員工信息（第二個(gè)工作表）
        Sheet employeeSheet = workbook.getSheetAt(1);
        for (int i = 1; i <= employeeSheet.getLastRowNum(); i++) {
            Row row = employeeSheet.getRow(i);
            if (row == null) continue;
            
            User employee = new User();
            employee.setId((long) row.getCell(0).getNumericCellValue());
            employee.setName(row.getCell(1).getStringCellValue());
            employee.setAge((int) row.getCell(2).getNumericCellValue());
            employee.setEmail(row.getCell(3).getStringCellValue());
            
            // 獲取部門(mén)ID并關(guān)聯(lián)到相應(yīng)部門(mén)
            long departmentId = (long) row.getCell(4).getNumericCellValue();
            for (Department dept : departments) {
                if (dept.getId() == departmentId) {
                    dept.getEmployees().add(employee);
                    break;
                }
            }
        }
        
        workbook.close();
    }
    
    return departments;
}

4. 使用Apache POI創(chuàng)建和導(dǎo)出Excel文件

4.1 創(chuàng)建基本Excel文件

以下是一個(gè)創(chuàng)建簡(jiǎn)單Excel文件的示例：

package com.example.excel.service;

import com.example.excel.model.User;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.List;

@Service
public class ExcelExportService {

    public ByteArrayInputStream exportUsersToExcel(List<User> users) throws IOException {
        try (Workbook workbook = new XSSFWorkbook()) {
            // 創(chuàng)建工作表
            Sheet sheet = workbook.createSheet("用戶(hù)數(shù)據(jù)");
            
            // 創(chuàng)建表頭樣式
            Font headerFont = workbook.createFont();
            headerFont.setBold(true);
            headerFont.setColor(IndexedColors.BLUE.getIndex());
            
            CellStyle headerCellStyle = workbook.createCellStyle();
            headerCellStyle.setFont(headerFont);
            headerCellStyle.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());
            headerCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            headerCellStyle.setBorderBottom(BorderStyle.THIN);
            headerCellStyle.setBorderTop(BorderStyle.THIN);
            headerCellStyle.setBorderRight(BorderStyle.THIN);
            headerCellStyle.setBorderLeft(BorderStyle.THIN);
            
            // 創(chuàng)建表頭行
            Row headerRow = sheet.createRow(0);
            
            // 創(chuàng)建表頭單元格
            Cell cell0 = headerRow.createCell(0);
            cell0.setCellValue("ID");
            cell0.setCellStyle(headerCellStyle);
            
            Cell cell1 = headerRow.createCell(1);
            cell1.setCellValue("姓名");
            cell1.setCellStyle(headerCellStyle);
            
            Cell cell2 = headerRow.createCell(2);
            cell2.setCellValue("年齡");
            cell2.setCellStyle(headerCellStyle);
            
            Cell cell3 = headerRow.createCell(3);
            cell3.setCellValue("郵箱");
            cell3.setCellStyle(headerCellStyle);
            
            Cell cell4 = headerRow.createCell(4);
            cell4.setCellValue("部門(mén)");
            cell4.setCellStyle(headerCellStyle);
            
            // 設(shè)置數(shù)據(jù)單元格樣式
            CellStyle dataCellStyle = workbook.createCellStyle();
            dataCellStyle.setBorderBottom(BorderStyle.THIN);
            dataCellStyle.setBorderTop(BorderStyle.THIN);
            dataCellStyle.setBorderRight(BorderStyle.THIN);
            dataCellStyle.setBorderLeft(BorderStyle.THIN);
            
            // 創(chuàng)建數(shù)據(jù)行
            int rowIdx = 1;
            for (User user : users) {
                Row row = sheet.createRow(rowIdx++);
                
                Cell idCell = row.createCell(0);
                idCell.setCellValue(user.getId());
                idCell.setCellStyle(dataCellStyle);
                
                Cell nameCell = row.createCell(1);
                nameCell.setCellValue(user.getName());
                nameCell.setCellStyle(dataCellStyle);
                
                Cell ageCell = row.createCell(2);
                ageCell.setCellValue(user.getAge());
                ageCell.setCellStyle(dataCellStyle);
                
                Cell emailCell = row.createCell(3);
                emailCell.setCellValue(user.getEmail());
                emailCell.setCellStyle(dataCellStyle);
                
                Cell deptCell = row.createCell(4);
                deptCell.setCellValue(user.getDepartment());
                deptCell.setCellStyle(dataCellStyle);
            }
            
            // 自動(dòng)調(diào)整列寬
            for (int i = 0; i < 5; i++) {
                sheet.autoSizeColumn(i);
            }
            
            // 寫(xiě)入ByteArrayOutputStream
            ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
            workbook.write(outputStream);
            
            return new ByteArrayInputStream(outputStream.toByteArray());
        }
    }
}

4.2 創(chuàng)建導(dǎo)出控制器

package com.example.excel.controller;

import com.example.excel.model.User;
import com.example.excel.service.ExcelExportService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.InputStreamResource;
import org.springframework.http.HttpHeaders;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

@RestController
@RequestMapping("/api/excel")
public class ExcelExportController {

    @Autowired
    private ExcelExportService excelExportService;

    @GetMapping("/export")
    public ResponseEntity<InputStreamResource> exportUsers() throws IOException {
        // 生成示例數(shù)據(jù)
        List<User> users = getTestUsers();
        
        // 生成Excel文件
        ByteArrayInputStream in = excelExportService.exportUsersToExcel(users);
        
        // 設(shè)置HTTP頭
        HttpHeaders headers = new HttpHeaders();
        headers.add("Content-Disposition", "attachment; filename=users.xlsx");
        
        // 返回Excel文件
        return ResponseEntity
                .ok()
                .headers(headers)
                .contentType(MediaType.parseMediaType("application/vnd.ms-excel"))
                .body(new InputStreamResource(in));
    }
    
    // 生成測(cè)試用戶(hù)數(shù)據(jù)
    private List<User> getTestUsers() {
        List<User> users = new ArrayList<>();
        
        User user1 = new User();
        user1.setId(1L);
        user1.setName("張三");
        user1.setAge(28);
        user1.setEmail("zhangsan@example.com");
        user1.setDepartment("研發(fā)部");
        users.add(user1);
        
        User user2 = new User();
        user2.setId(2L);
        user2.setName("李四");
        user2.setAge(32);
        user2.setEmail("lisi@example.com");
        user2.setDepartment("市場(chǎng)部");
        users.add(user2);
        
        User user3 = new User();
        user3.setId(3L);
        user3.setName("王五");
        user3.setAge(45);
        user3.setEmail("wangwu@example.com");
        user3.setDepartment("行政部");
        users.add(user3);
        
        User user4 = new User();
        user4.setId(4L);
        user4.setName("趙六");
        user4.setAge(36);
        user4.setEmail("zhaoliu@example.com");
        user4.setDepartment("財(cái)務(wù)部");
        users.add(user4);
        
        User user5 = new User();
        user5.setId(5L);
        user5.setName("錢(qián)七");
        user5.setAge(29);
        user5.setEmail("qianqi@example.com");
        user5.setDepartment("人力資源部");
        users.add(user5);
        
        return users;
    }
}

4.3 創(chuàng)建導(dǎo)出頁(yè)面

在src/main/resources/templates目錄下創(chuàng)建export.html：

<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title>Excel導(dǎo)出</title>
    <link rel="stylesheet"  rel="external nofollow"  rel="external nofollow" >
</head>
<body>
    <div class="container mt-5">
        <div class="card">
            <div class="card-header">
                <h3>導(dǎo)出Excel文件</h3>
            </div>
            <div class="card-body">
                <p>點(diǎn)擊下面的按鈕導(dǎo)出用戶(hù)數(shù)據(jù)到Excel文件：</p>
                <a href="/api/excel/export" rel="external nofollow"  class="btn btn-primary">導(dǎo)出用戶(hù)數(shù)據(jù)</a>
            </div>
        </div>
    </div>
</body>
</html>

4.4 創(chuàng)建復(fù)雜的Excel文件

以下是一個(gè)創(chuàng)建更復(fù)雜Excel文件的示例，包含多個(gè)工作表、合并單元格、公式等：

public ByteArrayInputStream exportComplexExcel(List<Department> departments) throws IOException {
    try (Workbook workbook = new XSSFWorkbook()) {
        // 創(chuàng)建字體和樣式
        Font headerFont = workbook.createFont();
        headerFont.setBold(true);
        headerFont.setFontHeightInPoints((short) 14);
        
        CellStyle headerStyle = workbook.createCellStyle();
        headerStyle.setFont(headerFont);
        headerStyle.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headerStyle.setAlignment(HorizontalAlignment.CENTER);
        
        CellStyle titleStyle = workbook.createCellStyle();
        Font titleFont = workbook.createFont();
        titleFont.setBold(true);
        titleFont.setFontHeightInPoints((short) 16);
        titleStyle.setFont(titleFont);
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        
        // 創(chuàng)建匯總表
        Sheet summarySheet = workbook.createSheet("部門(mén)匯總");
        
        // 創(chuàng)建標(biāo)題行
        Row titleRow = summarySheet.createRow(0);
        Cell titleCell = titleRow.createCell(0);
        titleCell.setCellValue("公司部門(mén)人員統(tǒng)計(jì)");
        titleCell.setCellStyle(titleStyle);
        
        // 合并標(biāo)題單元格
        summarySheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));
        
        // 創(chuàng)建表頭
        Row headerRow = summarySheet.createRow(1);
        String[] headers = {"部門(mén)ID", "部門(mén)名稱(chēng)", "部門(mén)經(jīng)理", "員工數(shù)量"};
        
        for (int i = 0; i < headers.length; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(headers[i]);
            cell.setCellStyle(headerStyle);
        }
        
        // 填充部門(mén)數(shù)據(jù)
        int rowIdx = 2;
        int totalEmployees = 0;
        
        for (Department dept : departments) {
            Row row = summarySheet.createRow(rowIdx++);
            
            row.createCell(0).setCellValue(dept.getId());
            row.createCell(1).setCellValue(dept.getName());
            row.createCell(2).setCellValue(dept.getManager());
            row.createCell(3).setCellValue(dept.getEmployees().size());
            
            totalEmployees += dept.getEmployees().size();
            
            // 為每個(gè)部門(mén)創(chuàng)建單獨(dú)的工作表
            Sheet deptSheet = workbook.createSheet(dept.getName());
            
            // 創(chuàng)建部門(mén)表頭
            Row deptHeaderRow = deptSheet.createRow(0);
            Cell deptTitleCell = deptHeaderRow.createCell(0);
            deptTitleCell.setCellValue(dept.getName() + " - 員工列表");
            deptTitleCell.setCellStyle(titleStyle);
            deptSheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4));
            
            // 員工表頭
            Row empHeaderRow = deptSheet.createRow(1);
            String[] empHeaders = {"員工ID", "姓名", "年齡", "郵箱", "入職年限"};
            
            for (int i = 0; i < empHeaders.length; i++) {
                Cell cell = empHeaderRow.createCell(i);
                cell.setCellValue(empHeaders[i]);
                cell.setCellStyle(headerStyle);
            }
            
            // 填充員工數(shù)據(jù)
            int empRowIdx = 2;
            for (User emp : dept.getEmployees()) {
                Row empRow = deptSheet.createRow(empRowIdx++);
                
                empRow.createCell(0).setCellValue(emp.getId());
                empRow.createCell(1).setCellValue(emp.getName());
                empRow.createCell(2).setCellValue(emp.getAge());
                empRow.createCell(3).setCellValue(emp.getEmail());
                
                // 使用公式計(jì)算入職年限（假設(shè)年齡減去25）
                Cell tenureCell = empRow.createCell(4);
                tenureCell.setCellFormula("C" + empRowIdx + "-25");
            }
            
            // 自動(dòng)調(diào)整列寬
            for (int i = 0; i < 5; i++) {
                deptSheet.autoSizeColumn(i);
            }
        }
        
        // 創(chuàng)建總計(jì)行
        Row totalRow = summarySheet.createRow(rowIdx);
        Cell totalLabelCell = totalRow.createCell(0);
        totalLabelCell.setCellValue("總計(jì)");
        totalLabelCell.setCellStyle(headerStyle);
        
        // 合并總計(jì)標(biāo)簽單元格
        summarySheet.addMergedRegion(new CellRangeAddress(rowIdx, rowIdx, 0, 2));
        
        Cell totalValueCell = totalRow.createCell(3);
        totalValueCell.setCellValue(totalEmployees);
        totalValueCell.setCellStyle(headerStyle);
        
        // 自動(dòng)調(diào)整列寬
        for (int i = 0; i < 4; i++) {
            summarySheet.autoSizeColumn(i);
        }
        
        // 添加圖表
        XSSFSheet chartSheet = (XSSFSheet) workbook.createSheet("部門(mén)統(tǒng)計(jì)圖");
        
        // 復(fù)制部門(mén)數(shù)據(jù)到圖表數(shù)據(jù)表
        Row chartHeaderRow = chartSheet.createRow(0);
        chartHeaderRow.createCell(0).setCellValue("部門(mén)");
        chartHeaderRow.createCell(1).setCellValue("員工數(shù)");
        
        int chartRowIdx = 1;
        for (Department dept : departments) {
            Row row = chartSheet.createRow(chartRowIdx++);
            row.createCell(0).setCellValue(dept.getName());
            row.createCell(1).setCellValue(dept.getEmployees().size());
        }
        
        // 創(chuàng)建圖表和數(shù)據(jù)序列
        XSSFDrawing drawing = chartSheet.createDrawingPatriarch();
        XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 4, 0, 15, 15);
        
        XSSFChart chart = drawing.createChart(anchor);
        chart.setTitleText("部門(mén)人員分布");
        chart.setTitleOverlay(false);
        
        XDDFChartLegend legend = chart.getOrAddLegend();
        legend.setPosition(LegendPosition.RIGHT);
        
        // X軸和Y軸
        XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
        bottomAxis.setTitle("部門(mén)");
        XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);
        leftAxis.setTitle("員工數(shù)");
        
        // 創(chuàng)建數(shù)據(jù)源
        XDDFDataSource<String> departments = XDDFDataSourcesFactory.fromStringCellRange(
                chartSheet, new CellRangeAddress(1, chartRowIdx - 1, 0, 0));
        
        XDDFNumericalDataSource<Double> values = XDDFDataSourcesFactory.fromNumericCellRange(
                chartSheet, new CellRangeAddress(1, chartRowIdx - 1, 1, 1));
        
        // 創(chuàng)建柱狀圖
        XDDFBarChartData barChart = (XDDFBarChartData) chart.createData(
                ChartTypes.BAR, bottomAxis, leftAxis);
        barChart.setVaryColors(true);
        
        XDDFBarChartData.Series series = (XDDFBarChartData.Series) barChart.addSeries(departments, values);
        series.setTitle("員工數(shù)", null);
        
        chart.plot(barChart);
        
        // 寫(xiě)入ByteArrayOutputStream
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        workbook.write(outputStream);
        
        return new ByteArrayInputStream(outputStream.toByteArray());
    }
}

注意：上面的圖表代碼需要添加以下依賴(lài)：

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-full</artifactId>
    <version>5.2.3</version>
</dependency>

4.5 使用模板導(dǎo)出Excel

在某些場(chǎng)景下，我們需要基于預(yù)定義的Excel模板生成文件，以下是一個(gè)示例：

public ByteArrayInputStream exportFromTemplate(List<User> users) throws IOException {
    // 加載模板文件
    try (InputStream templateStream = getClass().getResourceAsStream("/templates/user_template.xlsx");
         Workbook workbook = WorkbookFactory.create(templateStream)) {
        
        Sheet sheet = workbook.getSheetAt(0);
        
        // 從第二行開(kāi)始填充數(shù)據(jù)（第一行是表頭）
        int rowIdx = 1;
        for (User user : users) {
            Row row = sheet.createRow(rowIdx++);
            
            row.createCell(0).setCellValue(user.getId());
            row.createCell(1).setCellValue(user.getName());
            row.createCell(2).setCellValue(user.getAge());
            row.createCell(3).setCellValue(user.getEmail());
            row.createCell(4).setCellValue(user.getDepartment());
        }
        
        // 更新模板中的日期單元格（假設(shè)在A(yíng)1位置）
        Row headerRow = sheet.getRow(0);
        if (headerRow.getCell(6) != null) {
            Cell dateCell = headerRow.getCell(6);
            dateCell.setCellValue(new Date());
        }
        
        // 自動(dòng)調(diào)整列寬
        for (int i = 0; i < 5; i++) {
            sheet.autoSizeColumn(i);
        }
        
        // 寫(xiě)入ByteArrayOutputStream
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        workbook.write(outputStream);
        
        return new ByteArrayInputStream(outputStream.toByteArray());
    }
}

5. 使用EasyExcel處理Excel文件

EasyExcel是阿里巴巴開(kāi)源的基于POI的Excel處理工具，相比原生POI，它提供了更簡(jiǎn)潔的API，并且在處理大文件時(shí)有明顯的性能優(yōu)勢(shì)。

5.1 使用EasyExcel讀取Excel

5.1.1 創(chuàng)建數(shù)據(jù)模型

使用EasyExcel時(shí)，通常使用注解來(lái)映射Excel列：

package com.example.excel.model;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import lombok.Data;

import java.util.Date;

@Data
public class Employee {
    
    @ExcelProperty("員工ID")
    private Long id;
    
    @ExcelProperty("姓名")
    private String name;
    
    @ExcelProperty("年齡")
    private Integer age;
    
    @ExcelProperty("郵箱")
    private String email;
    
    @ExcelProperty("部門(mén)")
    private String department;
    
    @ExcelProperty("入職日期")
    @DateTimeFormat("yyyy-MM-dd")
    private Date hireDate;
    
    @ExcelProperty("薪資")
    private Double salary;
}

5.1.2 創(chuàng)建讀取監(jiān)聽(tīng)器

EasyExcel采用事件模式讀取Excel，需要?jiǎng)?chuàng)建一個(gè)監(jiān)聽(tīng)器來(lái)處理讀取的數(shù)據(jù)：

package com.example.excel.listener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.example.excel.model.Employee;
import lombok.extern.slf4j.Slf4j;

import java.util.ArrayList;
import java.util.List;

@Slf4j
public class EmployeeReadListener extends AnalysisEventListener<Employee> {

    /**
     * 用于暫存讀取的數(shù)據(jù)
     */
    private List<Employee> employeeList = new ArrayList<>();
    
    /**
     * 每讀取一行數(shù)據(jù)就會(huì)調(diào)用一次invoke方法
     */
    @Override
    public void invoke(Employee employee, AnalysisContext context) {
        log.info("讀取到一條數(shù)據(jù): {}", employee);
        employeeList.add(employee);
        
        // 達(dá)到BATCH_COUNT時(shí)，需要存儲(chǔ)一次數(shù)據(jù)庫(kù)，防止數(shù)據(jù)幾萬(wàn)條數(shù)據(jù)在內(nèi)存，容易OOM
        if (employeeList.size() >= 5000) {
            saveData();
            // 清理內(nèi)存
            employeeList.clear();
        }
    }
    
    /**
     * 所有數(shù)據(jù)解析完成后調(diào)用此方法
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 確保最后一批數(shù)據(jù)被保存
        saveData();
        log.info("所有數(shù)據(jù)解析完成！");
    }
    
    /**
     * 保存數(shù)據(jù)，這里只是打印，實(shí)際應(yīng)用中可以將數(shù)據(jù)存入數(shù)據(jù)庫(kù)
     */
    private void saveData() {
        log.info("{}條數(shù)據(jù)，開(kāi)始保存數(shù)據(jù)庫(kù)！", employeeList.size());
        // 這里可以調(diào)用持久層完成數(shù)據(jù)入庫(kù)
        log.info("存儲(chǔ)數(shù)據(jù)庫(kù)成功！");
    }
    
    /**
     * 獲取讀取到的數(shù)據(jù)
     */
    public List<Employee> getEmployeeList() {
        return employeeList;
    }
}

5.1.3 創(chuàng)建Excel讀取服務(wù)

package com.example.excel.service;

import com.alibaba.excel.EasyExcel;
import com.example.excel.listener.EmployeeReadListener;
import com.example.excel.model.Employee;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.util.List;

@Slf4j
@Service
public class EasyExcelService {

    public List<Employee> readEmployeeData(MultipartFile file) throws IOException {
        EmployeeReadListener listener = new EmployeeReadListener();
        
        EasyExcel.read(file.getInputStream(), Employee.class, listener).sheet().doRead();
        
        return listener.getEmployeeList();
    }
}

5.1.4 創(chuàng)建Controller

package com.example.excel.controller;

import com.example.excel.model.Employee;
import com.example.excel.service.EasyExcelService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.util.List;

@Slf4j
@RestController
@RequestMapping("/api/easyexcel")
public class EasyExcelController {

    @Autowired
    private EasyExcelService easyExcelService;
    
    @PostMapping("/upload")
    public ResponseEntity<List<Employee>> uploadExcel(@RequestParam("file") MultipartFile file) {
        try {
            List<Employee> employees = easyExcelService.readEmployeeData(file);
            return ResponseEntity.ok(employees);
        } catch (IOException e) {
            log.error("Excel讀取失敗", e);
            return ResponseEntity.badRequest().build();
        }
    }
}

5.2 使用EasyExcel導(dǎo)出Excel

5.2.1 簡(jiǎn)單導(dǎo)出示例

package com.example.excel.service;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.example.excel.model.Employee;
import org.springframework.stereotype.Service;

import java.io.File;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;

@Service
public class EasyExcelExportService {

    /**
     * 導(dǎo)出員工數(shù)據(jù)到Excel文件
     */
    public void exportEmployees(List<Employee> employees, OutputStream outputStream) {
        EasyExcel.write(outputStream, Employee.class)
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())  // 自動(dòng)調(diào)整列寬
                .sheet("員工數(shù)據(jù)")
                .doWrite(employees);
    }
    
    /**
     * 導(dǎo)出員工數(shù)據(jù)到指定文件
     */
    public void exportEmployeesToFile(List<Employee> employees, String fileName) throws IOException {
        // 確保目錄存在
        File file = new File(fileName);
        if (!file.getParentFile().exists()) {
            file.getParentFile().mkdirs();
        }
        
        EasyExcel.write(fileName, Employee.class)
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                .sheet("員工數(shù)據(jù)")
                .doWrite(employees);
    }
    
    /**
     * 導(dǎo)出多個(gè)Sheet的Excel
     */
    public void exportMultipleSheets(List<List<Employee>> departmentEmployees, 
                                    List<String> sheetNames, 
                                    OutputStream outputStream) {
        // 創(chuàng)建ExcelWriter
        try (var excelWriter = EasyExcel.write(outputStream, Employee.class)
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                .build()) {
            
            // 同一個(gè)對(duì)象多個(gè)Sheet寫(xiě)入
            for (int i = 0; i < departmentEmployees.size(); i++) {
                // 獲取Sheet名稱(chēng)
                String sheetName = i < sheetNames.size() ? sheetNames.get(i) : "Sheet" + (i + 1);
                
                // 創(chuàng)建新的Sheet
                var writeSheet = EasyExcel.writerSheet(i, sheetName).build();
                
                // 寫(xiě)入數(shù)據(jù)
                excelWriter.write(departmentEmployees.get(i), writeSheet);
            }
        }
    }
}

5.2.2 創(chuàng)建Controller

package com.example.excel.controller;

import com.example.excel.model.Employee;
import com.example.excel.service.EasyExcelExportService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

@RestController
@RequestMapping("/api/easyexcel")
public class EasyExcelExportController {

    @Autowired
    private EasyExcelExportService exportService;
    
    @GetMapping("/export")
    public void exportEmployees(HttpServletResponse response) throws IOException {
        // 設(shè)置響應(yīng)內(nèi)容
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        
        // 設(shè)置文件名
        String fileName = URLEncoder.encode("員工數(shù)據(jù)", StandardCharsets.UTF_8).replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        
        // 獲取測(cè)試數(shù)據(jù)
        List<Employee> employees = getTestEmployees();
        
        // 導(dǎo)出Excel
        exportService.exportEmployees(employees, response.getOutputStream());
    }
    
    @GetMapping("/export-multiple-sheets")
    public void exportMultipleSheets(HttpServletResponse response) throws IOException {
        // 設(shè)置響應(yīng)內(nèi)容
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        
        // 設(shè)置文件名
        String fileName = URLEncoder.encode("部門(mén)員工數(shù)據(jù)", StandardCharsets.UTF_8).replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        
        // 獲取測(cè)試數(shù)據(jù) - 三個(gè)部門(mén)的員工
        List<List<Employee>> departmentEmployees = new ArrayList<>();
        departmentEmployees.add(getEmployeesByDepartment("研發(fā)部"));
        departmentEmployees.add(getEmployeesByDepartment("市場(chǎng)部"));
        departmentEmployees.add(getEmployeesByDepartment("行政部"));
        
        // Sheet名稱(chēng)
        List<String> sheetNames = Arrays.asList("研發(fā)部員工", "市場(chǎng)部員工", "行政部員工");
        
        // 導(dǎo)出Excel
        exportService.exportMultipleSheets(departmentEmployees, sheetNames, response.getOutputStream());
    }
    
    /**
     * 生成測(cè)試員工數(shù)據(jù)
     */
    private List<Employee> getTestEmployees() {
        List<Employee> employees = new ArrayList<>();
        
        // 添加測(cè)試數(shù)據(jù)
        for (int i = 1; i <= 10; i++) {
            Employee employee = new Employee();
            employee.setId((long) i);
            employee.setName("員工" + i);
            employee.setAge(20 + i);
            employee.setEmail("employee" + i + "@example.com");
            employee.setDepartment(i % 3 == 0 ? "研發(fā)部" : (i % 3 == 1 ? "市場(chǎng)部" : "行政部"));
            employee.setHireDate(new Date());
            employee.setSalary(5000.0 + i * 1000);
            
            employees.add(employee);
        }
        
        return employees;
    }
    
    /**
     * 根據(jù)部門(mén)獲取員工
     */
    private List<Employee> getEmployeesByDepartment(String department) {
        List<Employee> allEmployees = getTestEmployees();
        List<Employee> departmentEmployees = new ArrayList<>();
        
        for (Employee employee : allEmployees) {
            if (department.equals(employee.getDepartment())) {
                departmentEmployees.add(employee);
            }
        }
        
        return departmentEmployees;
    }

    // ... 5.2.3 使用自定義樣式和復(fù)雜表頭

    /**
     * 導(dǎo)出自定義樣式的Excel
     */
    public void exportWithCustomStyle(List<Employee> employees, OutputStream outputStream) {
        // 設(shè)置自定義攔截器來(lái)處理樣式
        EasyExcel.write(outputStream, Employee.class)
                // 自動(dòng)調(diào)整列寬
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                // 設(shè)置表頭樣式
                .registerWriteHandler(new AbstractRowHeightStyleStrategy() {
                    @Override
                    protected void setHeadColumnHeight(Row row, int relativeRowIndex) {
                        // 設(shè)置表頭行高
                        row.setHeight((short) 500);
                    }

                    @Override
                    protected void setContentColumnHeight(Row row, int relativeRowIndex) {
                        // 設(shè)置內(nèi)容行高
                        row.setHeight((short) 400);
                    }
                })
                // 設(shè)置單元格樣式
                .registerWriteHandler(new CellWriteHandler() {
                    @Override
                    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, 
                                              Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
                        // 設(shè)置表頭樣式
                        if (isHead) {
                            Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
                            CellStyle style = workbook.createCellStyle();
                            Font font = workbook.createFont();
                            font.setBold(true);
                            font.setFontHeightInPoints((short) 12);
                            font.setColor(IndexedColors.WHITE.getIndex());
                            style.setFont(font);
                            style.setFillForegroundColor(IndexedColors.ROYAL_BLUE.getIndex());
                            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                            style.setAlignment(HorizontalAlignment.CENTER);
                            style.setVerticalAlignment(VerticalAlignment.CENTER);
                            cell.setCellStyle(style);
                        }
                    }

                    @Override
                    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, 
                                                     Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
                        // 在這里可以根據(jù)數(shù)據(jù)內(nèi)容設(shè)置樣式
                    }

                    @Override
                    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, 
                                               List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
                        // 內(nèi)容行的樣式
                        if (!isHead) {
                            // 偶數(shù)行設(shè)置背景色
                            if (relativeRowIndex % 2 == 0) {
                                Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
                                CellStyle style = workbook.createCellStyle();
                                style.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
                                style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                                style.setAlignment(HorizontalAlignment.CENTER);
                                style.setVerticalAlignment(VerticalAlignment.CENTER);
                                cell.setCellStyle(style);
                            }
                        }
                    }
                })
                .sheet("員工數(shù)據(jù)")
                .doWrite(employees);
    }

    /**
     * 導(dǎo)出復(fù)雜表頭的Excel
     */
    public void exportWithComplexHead(List<Employee> employees, OutputStream outputStream) {
        // 構(gòu)建復(fù)雜表頭
        List<List<String>> head = new ArrayList<>();
        
        // 第一列 ID
        List<String> head1 = new ArrayList<>();
        head1.add("基本信息");
        head1.add("員工ID");
        head.add(head1);
        
        // 第二列 姓名
        List<String> head2 = new ArrayList<>();
        head2.add("基本信息");
        head2.add("姓名");
        head.add(head2);
        
        // 第三列 年齡
        List<String> head3 = new ArrayList<>();
        head3.add("基本信息");
        head3.add("年齡");
        head.add(head3);
        
        // 第四列 郵箱
        List<String> head4 = new ArrayList<>();
        head4.add("聯(lián)系方式");
        head4.add("郵箱");
        head.add(head4);
        
        // 第五列 部門(mén)
        List<String> head5 = new ArrayList<>();
        head5.add("工作信息");
        head5.add("部門(mén)");
        head.add(head5);
        
        // 第六列 入職日期
        List<String> head6 = new ArrayList<>();
        head6.add("工作信息");
        head6.add("入職日期");
        head.add(head6);
        
        // 第七列 薪資
        List<String> head7 = new ArrayList<>();
        head7.add("薪資信息");
        head7.add("月薪(元)");
        head.add(head7);
        
        // 將數(shù)據(jù)轉(zhuǎn)為L(zhǎng)ist<List<Object>>格式
        List<List<Object>> dataList = new ArrayList<>();
        for (Employee employee : employees) {
            List<Object> data = new ArrayList<>();
            data.add(employee.getId());
            data.add(employee.getName());
            data.add(employee.getAge());
            data.add(employee.getEmail());
            data.add(employee.getDepartment());
            data.add(employee.getHireDate());
            data.add(employee.getSalary());
            dataList.add(data);
        }
        
        // 寫(xiě)入Excel
        EasyExcel.write(outputStream)
                .head(head)
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                .sheet("員工數(shù)據(jù)")
                .doWrite(dataList);
    }
}

6. 處理大型Excel文件的策略

6.1 使用Apache POI SXSSF模式

SXSSF（Streaming Xlsx Writer）是POI提供的流式寫(xiě)入方式，可以大大減少內(nèi)存使用：

public void exportLargeExcel(String fileName, int rowCount) throws IOException {
    try (SXSSFWorkbook workbook = new SXSSFWorkbook(100)) { // 100表示內(nèi)存中保留的行數(shù)
        Sheet sheet = workbook.createSheet("大數(shù)據(jù)");
        
        // 創(chuàng)建表頭
        Row headerRow = sheet.createRow(0);
        for (int i = 0; i < 10; i++) {
            headerRow.createCell(i).setCellValue("列 " + (i + 1));
        }
        
        // 創(chuàng)建數(shù)據(jù)行
        for (int i = 0; i < rowCount; i++) {
            Row row = sheet.createRow(i + 1);
            for (int j = 0; j < 10; j++) {
                row.createCell(j).setCellValue("數(shù)據(jù) " + (i + 1) + "-" + (j + 1));
            }
            
            // 每生成10000行清理一次臨時(shí)文件
            if (i % 10000 == 0) {
                ((SXSSFSheet)sheet).flushRows();
            }
        }
        
        // 寫(xiě)入文件
        try (FileOutputStream outputStream = new FileOutputStream(fileName)) {
            workbook.write(outputStream);
        }
        
        // 清理臨時(shí)文件
        workbook.dispose();
    }
}

注意事項(xiàng)：

使用完畢后一定要調(diào)用dispose()方法清理臨時(shí)文件
SXSSF僅支持寫(xiě)入操作，不支持讀取
不支持某些高級(jí)特性（如合并單元格等）

6.2 使用EasyExcel處理大文件

EasyExcel在設(shè)計(jì)上就考慮了大文件處理，采用SAX方式逐行讀取，內(nèi)存占用?。?/p>

// 讀取大文件
public void readLargeExcel(String fileName) {
    // 使用SAX方式讀取
    EasyExcel.read(fileName, Employee.class, new EmployeeReadListener())
            .sheet()
            .doRead();
}

// 寫(xiě)入大文件
public void writeLargeExcel(String fileName, int batchSize) {
    // 分批獲取數(shù)據(jù)
    try (ExcelWriter excelWriter = EasyExcel.write(fileName, Employee.class)
            .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
            .build()) {
        
        // 獲取WriteSheet對(duì)象
        WriteSheet writeSheet = EasyExcel.writerSheet("員工數(shù)據(jù)").build();
        
        // 模擬分批獲取數(shù)據(jù)
        int totalCount = 100000; // 總數(shù)據(jù)量
        for (int i = 0; i < totalCount; i += batchSize) {
            // 獲取當(dāng)前批次數(shù)據(jù)
            List<Employee> data = getBatchData(i, Math.min(i + batchSize, totalCount));
            // 寫(xiě)入Excel
            excelWriter.write(data, writeSheet);
        }
    }
}

// 模擬分批獲取數(shù)據(jù)
private List<Employee> getBatchData(int start, int end) {
    List<Employee> list = new ArrayList<>();
    for (int i = start; i < end; i++) {
        Employee employee = new Employee();
        employee.setId((long) i);
        employee.setName("員工" + i);
        employee.setAge(20 + (i % 20));
        employee.setEmail("employee" + i + "@example.com");
        employee.setDepartment(i % 3 == 0 ? "研發(fā)部" : (i % 3 == 1 ? "市場(chǎng)部" : "行政部"));
        employee.setHireDate(new Date());
        employee.setSalary(5000.0 + (i % 10) * 1000);
        
        list.add(employee);
    }
    return list;
}

6.3 使用CSV代替Excel

對(duì)于極大的數(shù)據(jù)集，考慮使用CSV格式替代Excel：

public void exportToCsv(List<Employee> employees, String fileName) throws IOException {
    try (FileWriter writer = new FileWriter(fileName);
         CSVPrinter csvPrinter = new CSVPrinter(writer, CSVFormat.DEFAULT
                 .withHeader("ID", "姓名", "年齡", "郵箱", "部門(mén)", "入職日期", "薪資"))) {
                 
        for (Employee employee : employees) {
            csvPrinter.printRecord(
                employee.getId(),
                employee.getName(),
                employee.getAge(),
                employee.getEmail(),
                employee.getDepartment(),
                employee.getHireDate(),
                employee.getSalary()
            );
        }
        
        csvPrinter.flush();
    }
}

注意：使用CSV需要添加依賴(lài)：

<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-csv</artifactId>
    <version>1.9.0</version>
</dependency>

6.4 分頁(yè)導(dǎo)出大型數(shù)據(jù)集

對(duì)于Web應(yīng)用中需要導(dǎo)出的大型數(shù)據(jù)集，可以考慮分頁(yè)導(dǎo)出：

@GetMapping("/export/paged")
public ResponseEntity<String> exportPaged() {
    // 生成唯一任務(wù)ID
    String taskId = UUID.randomUUID().toString();
    
    // 啟動(dòng)異步任務(wù)
    CompletableFuture.runAsync(() -> {
        try {
            // 導(dǎo)出文件路徑
            String filePath = "/temp/" + taskId + ".xlsx";
            
            // 分頁(yè)查詢(xún)數(shù)據(jù)并寫(xiě)入Excel
            int pageSize = 1000;
            int totalPages = getTotalPages(pageSize);
            
            try (ExcelWriter excelWriter = EasyExcel.write(filePath, Employee.class)
                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                    .build()) {
                
                WriteSheet writeSheet = EasyExcel.writerSheet("員工數(shù)據(jù)").build();
                
                // 分頁(yè)導(dǎo)出
                for (int pageNum = 0; pageNum < totalPages; pageNum++) {
                    // 從數(shù)據(jù)庫(kù)分頁(yè)查詢(xún)數(shù)據(jù)
                    List<Employee> pageData = getPageData(pageNum, pageSize);
                    // 寫(xiě)入Excel
                    excelWriter.write(pageData, writeSheet);
                    // 更新進(jìn)度
                    updateExportProgress(taskId, (pageNum + 1) * 100 / totalPages);
                }
            }
            
            // 更新導(dǎo)出狀態(tài)為完成
            updateExportStatus(taskId, "COMPLETED", filePath);
            
        } catch (Exception e) {
            // 更新導(dǎo)出狀態(tài)為失敗
            updateExportStatus(taskId, "FAILED", null);
        }
    });
    
    // 返回任務(wù)ID
    return ResponseEntity.ok(taskId);
}

@GetMapping("/export/status/{taskId}")
public ResponseEntity<Map<String, Object>> getExportStatus(@PathVariable String taskId) {
    // 獲取任務(wù)狀態(tài)
    Map<String, Object> status = getTaskStatus(taskId);
    return ResponseEntity.ok(status);
}

@GetMapping("/export/download/{taskId}")
public ResponseEntity<Resource> downloadExportedFile(@PathVariable String taskId) {
    // 獲取導(dǎo)出文件路徑
    String filePath = getExportedFilePath(taskId);
    
    if (filePath == null) {
        return ResponseEntity.notFound().build();
    }
    
    // 創(chuàng)建文件資源
    Resource resource = new FileSystemResource(filePath);
    
    return ResponseEntity.ok()
            .header(HttpHeaders.CONTENT_DISPOSITION, 
                    "attachment; filename=employee_data.xlsx")
            .contentType(MediaType.parseMediaType("application/vnd.ms-excel"))
            .body(resource);
}

7. 實(shí)際應(yīng)用場(chǎng)景和最佳實(shí)踐

7.1 動(dòng)態(tài)列導(dǎo)出

在某些業(yè)務(wù)場(chǎng)景中，需要根據(jù)用戶(hù)選擇動(dòng)態(tài)確定導(dǎo)出的列：

public ByteArrayInputStream exportDynamicColumns(List<Employee> employees, List<String> selectedColumns) throws IOException {
    // 定義所有可能的列
    Map<String, String> allColumns = new HashMap<>();
    allColumns.put("id", "員工ID");
    allColumns.put("name", "姓名");
    allColumns.put("age", "年齡");
    allColumns.put("email", "郵箱");
    allColumns.put("department", "部門(mén)");
    allColumns.put("hireDate", "入職日期");
    allColumns.put("salary", "薪資");
    
    try (Workbook workbook = new XSSFWorkbook()) {
        Sheet sheet = workbook.createSheet("員工數(shù)據(jù)");
        
        // 創(chuàng)建表頭行
        Row headerRow = sheet.createRow(0);
        
        // 設(shè)置表頭樣式
        CellStyle headerStyle = workbook.createCellStyle();
        Font headerFont = workbook.createFont();
        headerFont.setBold(true);
        headerStyle.setFont(headerFont);
        
        // 填充表頭
        int colIdx = 0;
        for (String column : selectedColumns) {
            if (allColumns.containsKey(column)) {
                Cell cell = headerRow.createCell(colIdx++);
                cell.setCellValue(allColumns.get(column));
                cell.setCellStyle(headerStyle);
            }
        }
        
        // 填充數(shù)據(jù)
        int rowIdx = 1;
        for (Employee employee : employees) {
            Row row = sheet.createRow(rowIdx++);
            
            colIdx = 0;
            for (String column : selectedColumns) {
                Cell cell = row.createCell(colIdx++);
                
                // 根據(jù)列名設(shè)置單元格值
                switch (column) {
                    case "id":
                        cell.setCellValue(employee.getId());
                        break;
                    case "name":
                        cell.setCellValue(employee.getName());
                        break;
                    case "age":
                        cell.setCellValue(employee.getAge());
                        break;
                    case "email":
                        cell.setCellValue(employee.getEmail());
                        break;
                    case "department":
                        cell.setCellValue(employee.getDepartment());
                        break;
                    case "hireDate":
                        if (employee.getHireDate() != null) {
                            cell.setCellValue(employee.getHireDate());
                            
                            // 設(shè)置日期格式
                            CellStyle dateStyle = workbook.createCellStyle();
                            CreationHelper createHelper = workbook.getCreationHelper();
                            dateStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-mm-dd"));
                            cell.setCellStyle(dateStyle);
                        }
                        break;
                    case "salary":
                        cell.setCellValue(employee.getSalary());
                        break;
                }
            }
        }
        
        // 自動(dòng)調(diào)整列寬
        for (int i = 0; i < selectedColumns.size(); i++) {
            sheet.autoSizeColumn(i);
        }
        
        // 輸出
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        workbook.write(outputStream);
        return new ByteArrayInputStream(outputStream.toByteArray());
    }
}

7.2 Excel模板填充

使用Freemarker或其他模板引擎生成Excel：

public ByteArrayInputStream fillTemplate(Map<String, Object> data) throws Exception {
    // 加載模板
    Configuration cfg = new Configuration(Configuration.VERSION_2_3_30);
    cfg.setClassLoaderForTemplateLoading(getClass().getClassLoader(), "templates");
    cfg.setDefaultEncoding("UTF-8");
    
    // 獲取模板
    Template template = cfg.getTemplate("excel_template.ftl");
    
    // 輸出目錄
    File tempDir = new File(System.getProperty("java.io.tmpdir"));
    File tempFile = new File(tempDir, "temp_" + System.currentTimeMillis() + ".xlsx");
    
    // 填充模板
    try (Writer out = new FileWriter(tempFile)) {
        template.process(data, out);
    }
    
    // 讀取填充后的文件
    try (FileInputStream fis = new FileInputStream(tempFile)) {
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        byte[] buffer = new byte[1024];
        int len;
        while ((len = fis.read(buffer)) > -1) {
            baos.write(buffer, 0, len);
        }
        baos.flush();
        
        // 刪除臨時(shí)文件
        tempFile.delete();
        
        return new ByteArrayInputStream(baos.toByteArray());
    }
}

7.3 Excel文件校驗(yàn)

在導(dǎo)入Excel文件前進(jìn)行數(shù)據(jù)校驗(yàn)：

public class ExcelValidationListener extends AnalysisEventListener<Employee> {
    
    private List<Employee> validEmployees = new ArrayList<>();
    private List<Map<String, Object>> errorRecords = new ArrayList<>();
    private int rowIndex = 1; // 從1開(kāi)始，0是表頭
    
    @Override
    public void invoke(Employee employee, AnalysisContext context) {
        rowIndex++;
        
        // 驗(yàn)證數(shù)據(jù)
        List<String> errors = validateEmployee(employee);
        
        if (errors.isEmpty()) {
            // 數(shù)據(jù)有效
            validEmployees.add(employee);
        } else {
            // 記錄錯(cuò)誤
            Map<String, Object> errorRecord = new HashMap<>();
            errorRecord.put("rowIndex", rowIndex);
            errorRecord.put("data", employee);
            errorRecord.put("errors", errors);
            errorRecords.add(errorRecord);
        }
    }
    
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 處理完成
    }
    
    // 驗(yàn)證員工數(shù)據(jù)
    private List<String> validateEmployee(Employee employee) {
        List<String> errors = new ArrayList<>();
        
        // 驗(yàn)證姓名
        if (employee.getName() == null || employee.getName().trim().isEmpty()) {
            errors.add("姓名不能為空");
        }
        
        // 驗(yàn)證年齡
        if (employee.getAge() == null) {
            errors.add("年齡不能為空");
        } else if (employee.getAge() < 18 || employee.getAge() > 65) {
            errors.add("年齡必須在18-65歲之間");
        }
        
        // 驗(yàn)證郵箱
        if (employee.getEmail() != null && !employee.getEmail().isEmpty()) {
            String emailRegex = "^[a-zA-Z0-9_+&*-]+(?:\\.[a-zA-Z0-9_+&*-]+)*@" +
                    "(?:[a-zA-Z0-9-]+\\.)+[a-zA-Z]{2,7}$";
            if (!employee.getEmail().matches(emailRegex)) {
                errors.add("郵箱格式不正確");
            }
        }
        
        // 驗(yàn)證部門(mén)
        if (employee.getDepartment() == null || employee.getDepartment().trim().isEmpty()) {
            errors.add("部門(mén)不能為空");
        }
        
        // 驗(yàn)證薪資
        if (employee.getSalary() != null && employee.getSalary() < 0) {
            errors.add("薪資不能為負(fù)數(shù)");
        }
        
        return errors;
    }
    
    public List<Employee> getValidEmployees() {
        return validEmployees;
    }
    
    public List<Map<String, Object>> getErrorRecords() {
        return errorRecords;
    }
    
    public boolean hasErrors() {
        return !errorRecords.isEmpty();
    }
}

7.4 統(tǒng)一異常處理

為Excel處理添加統(tǒng)一的異常處理：

@ControllerAdvice
public class ExcelExceptionHandler {

    private static final Logger logger = LoggerFactory.getLogger(ExcelExceptionHandler.class);
    
    @ExceptionHandler(IOException.class)
    public ResponseEntity<Map<String, String>> handleIOException(IOException e) {
        logger.error("文件讀寫(xiě)異常", e);
        
        Map<String, String> response = new HashMap<>();
        response.put("error", "文件讀寫(xiě)異常");
        response.put("message", e.getMessage());
        
        return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR).body(response);
    }
    
    @ExceptionHandler(IllegalArgumentException.class)
    public ResponseEntity<Map<String, String>> handleIllegalArgumentException(IllegalArgumentException e) {
        logger.error("參數(shù)異常", e);
        
        Map<String, String> response = new HashMap<>();
        response.put("error", "參數(shù)異常");
        response.put("message", e.getMessage());
        
        return ResponseEntity.status(HttpStatus.BAD_REQUEST).body(response);
    }
    
    @ExceptionHandler(Exception.class)
    public ResponseEntity<Map<String, String>> handleGenericException(Exception e) {
        logger.error("Excel處理異常", e);
        
        Map<String, String> response = new HashMap<>();
        response.put("error", "Excel處理異常");
        response.put("message", e.getMessage());
        
        return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR).body(response);
    }
}

8. 性能優(yōu)化和注意事項(xiàng)

8.1 性能優(yōu)化建議

使用適當(dāng)?shù)腅xcel庫(kù)：

小文件可使用Apache POI
大文件請(qǐng)使用EasyExcel或POI的SXSSF模式
極大文件考慮使用CSV格式

避免一次性加載整個(gè)文件：

讀取時(shí)使用流式解析
寫(xiě)入時(shí)使用分批寫(xiě)入

合理設(shè)置緩沖區(qū)大小：

在SXSSFWorkbook中設(shè)置合理的內(nèi)存行數(shù)
在批處理中選擇合適的批次大小

減少樣式對(duì)象：

樣式對(duì)象重用，而不是為每個(gè)單元格創(chuàng)建新樣式
限制使用的顏色、字體和邊框樣式數(shù)量

使用異步處理：

將大文件處理放在后臺(tái)線(xiàn)程中執(zhí)行
提供進(jìn)度反饋機(jī)制

8.2 注意事項(xiàng)

內(nèi)存管理：

注意監(jiān)控JVM內(nèi)存使用情況
對(duì)于大文件處理，考慮增加JVM堆內(nèi)存（-Xmx參數(shù)）
使用完畢后及時(shí)關(guān)閉資源和清理臨時(shí)文件

安全考慮：

限制上傳文件大小
驗(yàn)證文件類(lèi)型和內(nèi)容
防止惡意Excel文件（包含宏或公式）

編碼問(wèn)題：

處理國(guó)際字符時(shí)，確保使用正確的字符編碼
文件名包含中文時(shí)，確保正確編碼

并發(fā)控制：

大文件處理時(shí)注意服務(wù)器負(fù)載
限制并發(fā)處理任務(wù)數(shù)量

臨時(shí)文件清理：

使用SXSSF時(shí)，必須調(diào)用dispose()方法清理臨時(shí)文件
定期清理服務(wù)器上的臨時(shí)文件

總結(jié)

Spring Boot提供了強(qiáng)大而靈活的Excel處理能力，通過(guò)結(jié)合Apache POI和EasyExcel等工具，可以輕松實(shí)現(xiàn)Excel文件的讀取、創(chuàng)建和導(dǎo)出功能。在實(shí)際應(yīng)用中，應(yīng)根據(jù)具體需求和數(shù)據(jù)量選擇合適的處理策略，既要保證功能完整，又要注重性能和資源使用。

無(wú)論是簡(jiǎn)單的數(shù)據(jù)導(dǎo)出，還是復(fù)雜的報(bào)表生成，或是大數(shù)據(jù)量的文件處理，都可以通過(guò)本文介紹的方法靈活實(shí)現(xiàn)。重點(diǎn)是要根據(jù)實(shí)際業(yè)務(wù)場(chǎng)景，選擇合適的技術(shù)方案，并注意性能優(yōu)化和異常處理。

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

您可能感興趣的文章:

欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

SpringBoot中Excel處理完全指南分享

目錄

SpringBoot中Excel處理指南

1. Excel處理基礎(chǔ)知識(shí)

1.1 為什么需要在應(yīng)用中處理Excel文件？

1.2 Java中的Excel處理庫(kù)介紹

1.2.1 Apache POI

1.2.2 EasyExcel

1.2.3 JExcel

1.2.4 Apache POI SXSSF

1.3 Spring Boot中集成Excel處理

2. 在Spring Boot中集成Excel處理庫(kù)

2.1 集成Apache POI

2.1.1 添加依賴(lài)

2.1.2 創(chuàng)建基本配置類(lèi)

2.2 集成EasyExcel

2.2.1 添加依賴(lài)

2.2.2 創(chuàng)建配置類(lèi)

3. 使用Apache POI讀取Excel文件

3.1 創(chuàng)建數(shù)據(jù)模型

3.2 創(chuàng)建Excel讀取服務(wù)

3.3 創(chuàng)建Controller處理Excel上傳

3.4 創(chuàng)建HTML上傳頁(yè)面

3.5 處理更復(fù)雜的Excel結(jié)構(gòu)

4. 使用Apache POI創(chuàng)建和導(dǎo)出Excel文件

4.1 創(chuàng)建基本Excel文件

4.2 創(chuàng)建導(dǎo)出控制器

4.3 創(chuàng)建導(dǎo)出頁(yè)面

4.4 創(chuàng)建復(fù)雜的Excel文件

4.5 使用模板導(dǎo)出Excel

5. 使用EasyExcel處理Excel文件

5.1 使用EasyExcel讀取Excel

5.1.1 創(chuàng)建數(shù)據(jù)模型

5.1.2 創(chuàng)建讀取監(jiān)聽(tīng)器

5.1.3 創(chuàng)建Excel讀取服務(wù)

5.1.4 創(chuàng)建Controller

5.2 使用EasyExcel導(dǎo)出Excel

5.2.1 簡(jiǎn)單導(dǎo)出示例

5.2.2 創(chuàng)建Controller

6. 處理大型Excel文件的策略

6.1 使用Apache POI SXSSF模式

6.2 使用EasyExcel處理大文件

6.3 使用CSV代替Excel

6.4 分頁(yè)導(dǎo)出大型數(shù)據(jù)集

7. 實(shí)際應(yīng)用場(chǎng)景和最佳實(shí)踐

7.1 動(dòng)態(tài)列導(dǎo)出

7.2 Excel模板填充

7.3 Excel文件校驗(yàn)

7.4 統(tǒng)一異常處理

8. 性能優(yōu)化和注意事項(xiàng)

8.1 性能優(yōu)化建議

8.2 注意事項(xiàng)

總結(jié)

相關(guān)文章

最新評(píng)論

大家感興趣的內(nèi)容

最近更新的內(nèi)容

常用在線(xiàn)小工具