SpringBoot中Excel處理完全指南分享

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

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

SpringBoot中Excel處理指南

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

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

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

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

1.2 Java中的Excel處理庫介紹

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

1.2.1 Apache POI

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

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

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

缺點(diǎn)：

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

1.2.2 EasyExcel

EasyExcel是阿里巴巴開源的Excel處理庫，基于POI，但做了大量優(yōu)化。

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

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

缺點(diǎn)：

功能不如POI全面
靈活性相對較低

1.2.3 JExcel

JExcel是另一個處理Excel的Java庫。

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

API較簡單
速度較快

缺點(diǎn)：

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

1.2.4 Apache POI SXSSF

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

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

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

缺點(diǎn)：

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

1.3 Spring Boot中集成Excel處理

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

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

2. 在Spring Boot中集成Excel處理庫

2.1 集成Apache POI

2.1.1 添加依賴

在pom.xml文件中添加以下依賴：

<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)建基本配置類

創(chuàng)建一個配置類來處理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 添加依賴

在pom.xml文件中添加以下依賴：

<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)建配置類

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)建一個模型類來映射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)建一個服務(wù)類來處理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 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);
            
            // 獲取第一個工作表
            Sheet sheet = workbook.getSheetAt(0);
            
            // 跳過標(biāo)題行
            Iterator<Row> rowIterator = sheet.rowIterator();
            if (rowIterator.hasNext()) {
                rowIterator.next(); // 跳過標(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)建一個Controller來處理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上傳頁面

在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>部門</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ù)雜，如多個工作表、合并單元格、公式等。以下是處理這些情況的示例：

public List<Department> readComplexExcel(MultipartFile file) throws IOException {
    List<Department> departments = new ArrayList<>();
    
    try (InputStream inputStream = file.getInputStream()) {
        Workbook workbook = WorkbookFactory.create(inputStream);
        
        // 讀取部門信息（第一個工作表）
        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);
        }
        
        // 讀取員工信息（第二個工作表）
        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());
            
            // 獲取部門ID并關(guān)聯(lián)到相應(yīng)部門
            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文件

以下是一個創(chuàng)建簡單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("用戶數(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("部門");
            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);
            }
            
            // 自動調(diào)整列寬
            for (int i = 0; i < 5; i++) {
                sheet.autoSizeColumn(i);
            }
            
            // 寫入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));
    }
    
    // 生成測試用戶數(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("市場部");
        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("財務(wù)部");
        users.add(user4);
        
        User user5 = new User();
        user5.setId(5L);
        user5.setName("錢七");
        user5.setAge(29);
        user5.setEmail("qianqi@example.com");
        user5.setDepartment("人力資源部");
        users.add(user5);
        
        return users;
    }
}

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

在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)出用戶數(shù)據(jù)到Excel文件：</p>
                <a href="/api/excel/export" rel="external nofollow"  class="btn btn-primary">導(dǎo)出用戶數(shù)據(jù)</a>
            </div>
        </div>
    </div>
</body>
</html>

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

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

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("部門匯總");
        
        // 創(chuàng)建標(biāo)題行
        Row titleRow = summarySheet.createRow(0);
        Cell titleCell = titleRow.createCell(0);
        titleCell.setCellValue("公司部門人員統(tǒng)計");
        titleCell.setCellStyle(titleStyle);
        
        // 合并標(biāo)題單元格
        summarySheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));
        
        // 創(chuàng)建表頭
        Row headerRow = summarySheet.createRow(1);
        String[] headers = {"部門ID", "部門名稱", "部門經(jīng)理", "員工數(shù)量"};
        
        for (int i = 0; i < headers.length; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(headers[i]);
            cell.setCellStyle(headerStyle);
        }
        
        // 填充部門數(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();
            
            // 為每個部門創(chuàng)建單獨(dú)的工作表
            Sheet deptSheet = workbook.createSheet(dept.getName());
            
            // 創(chuàng)建部門表頭
            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());
                
                // 使用公式計算入職年限（假設(shè)年齡減去25）
                Cell tenureCell = empRow.createCell(4);
                tenureCell.setCellFormula("C" + empRowIdx + "-25");
            }
            
            // 自動調(diào)整列寬
            for (int i = 0; i < 5; i++) {
                deptSheet.autoSizeColumn(i);
            }
        }
        
        // 創(chuàng)建總計行
        Row totalRow = summarySheet.createRow(rowIdx);
        Cell totalLabelCell = totalRow.createCell(0);
        totalLabelCell.setCellValue("總計");
        totalLabelCell.setCellStyle(headerStyle);
        
        // 合并總計標(biāo)簽單元格
        summarySheet.addMergedRegion(new CellRangeAddress(rowIdx, rowIdx, 0, 2));
        
        Cell totalValueCell = totalRow.createCell(3);
        totalValueCell.setCellValue(totalEmployees);
        totalValueCell.setCellStyle(headerStyle);
        
        // 自動調(diào)整列寬
        for (int i = 0; i < 4; i++) {
            summarySheet.autoSizeColumn(i);
        }
        
        // 添加圖表
        XSSFSheet chartSheet = (XSSFSheet) workbook.createSheet("部門統(tǒng)計圖");
        
        // 復(fù)制部門數(shù)據(jù)到圖表數(shù)據(jù)表
        Row chartHeaderRow = chartSheet.createRow(0);
        chartHeaderRow.createCell(0).setCellValue("部門");
        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("部門人員分布");
        chart.setTitleOverlay(false);
        
        XDDFChartLegend legend = chart.getOrAddLegend();
        legend.setPosition(LegendPosition.RIGHT);
        
        // X軸和Y軸
        XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
        bottomAxis.setTitle("部門");
        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);
        
        // 寫入ByteArrayOutputStream
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        workbook.write(outputStream);
        
        return new ByteArrayInputStream(outputStream.toByteArray());
    }
}

注意：上面的圖表代碼需要添加以下依賴：

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

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

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

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);
        
        // 從第二行開始填充數(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è)在A1位置）
        Row headerRow = sheet.getRow(0);
        if (headerRow.getCell(6) != null) {
            Cell dateCell = headerRow.getCell(6);
            dateCell.setCellValue(new Date());
        }
        
        // 自動調(diào)整列寬
        for (int i = 0; i < 5; i++) {
            sheet.autoSizeColumn(i);
        }
        
        // 寫入ByteArrayOutputStream
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        workbook.write(outputStream);
        
        return new ByteArrayInputStream(outputStream.toByteArray());
    }
}

5. 使用EasyExcel處理Excel文件

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

5.1 使用EasyExcel讀取Excel

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

使用EasyExcel時，通常使用注解來映射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("部門")
    private String department;
    
    @ExcelProperty("入職日期")
    @DateTimeFormat("yyyy-MM-dd")
    private Date hireDate;
    
    @ExcelProperty("薪資")
    private Double salary;
}

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

EasyExcel采用事件模式讀取Excel，需要創(chuàng)建一個監(jiān)聽器來處理讀取的數(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ù)就會調(diào)用一次invoke方法
     */
    @Override
    public void invoke(Employee employee, AnalysisContext context) {
        log.info("讀取到一條數(shù)據(jù): {}", employee);
        employeeList.add(employee);
        
        // 達(dá)到BATCH_COUNT時，需要存儲一次數(shù)據(jù)庫，防止數(shù)據(jù)幾萬條數(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ù)庫
     */
    private void saveData() {
        log.info("{}條數(shù)據(jù)，開始保存數(shù)據(jù)庫！", employeeList.size());
        // 這里可以調(diào)用持久層完成數(shù)據(jù)入庫
        log.info("存儲數(shù)據(jù)庫成功！");
    }
    
    /**
     * 獲取讀取到的數(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 簡單導(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())  // 自動調(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)出多個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()) {
            
            // 同一個對象多個Sheet寫入
            for (int i = 0; i < departmentEmployees.size(); i++) {
                // 獲取Sheet名稱
                String sheetName = i < sheetNames.size() ? sheetNames.get(i) : "Sheet" + (i + 1);
                
                // 創(chuàng)建新的Sheet
                var writeSheet = EasyExcel.writerSheet(i, sheetName).build();
                
                // 寫入數(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");
        
        // 獲取測試數(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("部門員工數(shù)據(jù)", StandardCharsets.UTF_8).replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        
        // 獲取測試數(shù)據(jù) - 三個部門的員工
        List<List<Employee>> departmentEmployees = new ArrayList<>();
        departmentEmployees.add(getEmployeesByDepartment("研發(fā)部"));
        departmentEmployees.add(getEmployeesByDepartment("市場部"));
        departmentEmployees.add(getEmployeesByDepartment("行政部"));
        
        // Sheet名稱
        List<String> sheetNames = Arrays.asList("研發(fā)部員工", "市場部員工", "行政部員工");
        
        // 導(dǎo)出Excel
        exportService.exportMultipleSheets(departmentEmployees, sheetNames, response.getOutputStream());
    }
    
    /**
     * 生成測試員工數(shù)據(jù)
     */
    private List<Employee> getTestEmployees() {
        List<Employee> employees = new ArrayList<>();
        
        // 添加測試數(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 ? "市場部" : "行政部"));
            employee.setHireDate(new Date());
            employee.setSalary(5000.0 + i * 1000);
            
            employees.add(employee);
        }
        
        return employees;
    }
    
    /**
     * 根據(jù)部門獲取員工
     */
    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è)置自定義攔截器來處理樣式
        EasyExcel.write(outputStream, Employee.class)
                // 自動調(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);
        
        // 第五列 部門
        List<String> head5 = new ArrayList<>();
        head5.add("工作信息");
        head5.add("部門");
        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)為List<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);
        }
        
        // 寫入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提供的流式寫入方式，可以大大減少內(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行清理一次臨時文件
            if (i % 10000 == 0) {
                ((SXSSFSheet)sheet).flushRows();
            }
        }
        
        // 寫入文件
        try (FileOutputStream outputStream = new FileOutputStream(fileName)) {
            workbook.write(outputStream);
        }
        
        // 清理臨時文件
        workbook.dispose();
    }
}

注意事項：

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

6.2 使用EasyExcel處理大文件

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

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

// 寫入大文件
public void writeLargeExcel(String fileName, int batchSize) {
    // 分批獲取數(shù)據(jù)
    try (ExcelWriter excelWriter = EasyExcel.write(fileName, Employee.class)
            .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
            .build()) {
        
        // 獲取WriteSheet對象
        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));
            // 寫入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 ? "市場部" : "行政部"));
        employee.setHireDate(new Date());
        employee.setSalary(5000.0 + (i % 10) * 1000);
        
        list.add(employee);
    }
    return list;
}

6.3 使用CSV代替Excel

對于極大的數(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", "姓名", "年齡", "郵箱", "部門", "入職日期", "薪資"))) {
                 
        for (Employee employee : employees) {
            csvPrinter.printRecord(
                employee.getId(),
                employee.getName(),
                employee.getAge(),
                employee.getEmail(),
                employee.getDepartment(),
                employee.getHireDate(),
                employee.getSalary()
            );
        }
        
        csvPrinter.flush();
    }
}

注意：使用CSV需要添加依賴：

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

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

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

@GetMapping("/export/paged")
public ResponseEntity<String> exportPaged() {
    // 生成唯一任務(wù)ID
    String taskId = UUID.randomUUID().toString();
    
    // 啟動異步任務(wù)
    CompletableFuture.runAsync(() -> {
        try {
            // 導(dǎo)出文件路徑
            String filePath = "/temp/" + taskId + ".xlsx";
            
            // 分頁查詢數(shù)據(jù)并寫入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();
                
                // 分頁導(dǎo)出
                for (int pageNum = 0; pageNum < totalPages; pageNum++) {
                    // 從數(shù)據(jù)庫分頁查詢數(shù)據(jù)
                    List<Employee> pageData = getPageData(pageNum, pageSize);
                    // 寫入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)用場景和最佳實(shí)踐

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

在某些業(yè)務(wù)場景中，需要根據(jù)用戶選擇動態(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", "部門");
    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;
                }
            }
        }
        
        // 自動調(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();
        
        // 刪除臨時文件
        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開始，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 {
            // 記錄錯誤
            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)證部門
        if (employee.getDepartment() == null || employee.getDepartment().trim().isEmpty()) {
            errors.add("部門不能為空");
        }
        
        // 驗(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("文件讀寫異常", e);
        
        Map<String, String> response = new HashMap<>();
        response.put("error", "文件讀寫異常");
        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)化和注意事項

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

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

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

避免一次性加載整個文件：

讀取時使用流式解析
寫入時使用分批寫入

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

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

減少樣式對象：

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

使用異步處理：

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

8.2 注意事項

內(nèi)存管理：

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

安全考慮：

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

編碼問題：

處理國際字符時，確保使用正確的字符編碼
文件名包含中文時，確保正確編碼

并發(fā)控制：

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

臨時文件清理：

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

總結(jié)

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

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

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

您可能感興趣的文章:

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

SpringBoot中Excel處理完全指南分享

目錄

SpringBoot中Excel處理指南

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

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

1.2 Java中的Excel處理庫介紹

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處理庫

2.1 集成Apache POI

2.1.1 添加依賴

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

2.2 集成EasyExcel

2.2.1 添加依賴

2.2.2 創(chuàng)建配置類

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上傳頁面

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)出頁面

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)聽器

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

5.1.4 創(chuàng)建Controller

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

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

5.2.2 創(chuàng)建Controller

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

6.1 使用Apache POI SXSSF模式

6.2 使用EasyExcel處理大文件

6.3 使用CSV代替Excel

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

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

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

7.2 Excel模板填充

7.3 Excel文件校驗(yàn)

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

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

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

8.2 注意事項

總結(jié)

相關(guān)文章

最新評論

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

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

常用在線小工具

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