<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>

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

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;
    }
}

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

implementation 'com.alibaba:easyexcel:3.2.1'

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;
    }
}

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;
}

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 "";
        }
    }
}

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();
        }
    }
}

<!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>

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;
}

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("部門(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());
        }
    }
}

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è)試用戶數(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;
    }
}

<!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>

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());
    }
}

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

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è)在A1位置）
        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());
    }
}

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;
}

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;
    }
}

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();
    }
}

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();
        }
    }
}

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);
            }
        }
    }
}

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);
    }
}

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();
    }
}

// 讀取大文件
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;
}

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();
    }
}

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

@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è)查詢數(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è)查詢數(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);
}

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());
    }
}

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());
    }
}

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();
    }
}

@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);
    }
}