SpringBoot中Excel处理完全指南分享

springboot中excel处理指南

1. excel处理基础知识

1.1 为什么需要在应用中处理excel文件？

在企业应用开发中，excel文件处理是一个非常常见的需求，主要用于以下场景：

• 数据导入：允许用户通过excel上传批量数据到系统
• 数据导出：将系统数据导出为excel供用户下载分析
• 报表生成：生成复杂的报表并格式化为excel
• 数据交换：作为不同系统间交换数据的媒介
• 批量数据处理：处理大量结构化数据

1.2 java中的excel处理库介绍

java中处理excel文件的主要库有以下几种：

1.2.1 apache poi

apache poi是java中使用最广泛的excel处理库，提供了全面的api来创建、读取和修改office文档。

优点：

• 功能全面，支持excel所有功能
• 支持.xls (hssf - excel 97-2003)和.xlsx (xssf - excel 2007+)格式
• 社区活跃，文档丰富
• 支持公式计算、图表、合并单元格等高级功能

缺点：

• api相对复杂
• 处理大文件时内存消耗大（尤其是xssf）

1.2.2 easyexcel

easyexcel是阿里巴巴开源的excel处理库，基于poi，但做了大量优化。

优点：

• 内存占用低，使用sax模式读取，避免oom
• api简单易用，注解驱动
• 读写速度快
• 适合处理大型excel文件

缺点：

• 功能不如poi全面
• 灵活性相对较低

1.2.3 jexcel

jexcel是另一个处理excel的java库。

优点：

• api较简单
• 速度较快

缺点：

• 仅支持旧版excel (.xls)格式
• 不再积极维护
• 功能有限

1.2.4 apache poi sxssf

sxssf是poi提供的一种流式处理模式，专为处理大型excel文件设计。

优点：

• 大大降低内存占用
• 适合生成大型excel文件

缺点：

• 仅支持写入操作，不支持读取
• 功能比xssf受限

1.3 spring boot中集成excel处理

spring boot本身不提供excel处理功能，但可以轻松集成上述各种excel处理库。本指南将主要介绍：

1. 如何在spring boot项目中集成apache poi和easyexcel
2. 如何实现excel导入导出的常见功能
3. 如何处理常见问题和优化性能

2. 在spring boot中集成excel处理库

2.1 集成apache poi

2.1.1 添加依赖

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

    org.apache.poi
    


    

        

            

                
上传excel文件
            
            

                

                    

                        选择excel文件:
                        
                    
                    上传
                
                
                

                    
上传结果:
                    

                
            
        
    
    
    
    

public list readcomplexexcel(multipartfile file) throws ioexception {
    list 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并关联到相应部门
            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 users) throws ioexception {
        try (workbook workbook = new xssfworkbook()) {
            // 创建工作表
            sheet sheet = workbook.createsheet("用户数据");
            
            // 创建表头样式
            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);
            
            // 创建表头行
            row headerrow = sheet.createrow(0);
            
            // 创建表头单元格
            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);
            
            // 设置数据单元格样式
            cellstyle datacellstyle = workbook.createcellstyle();
            datacellstyle.setborderbottom(borderstyle.thin);
            datacellstyle.setbordertop(borderstyle.thin);
            datacellstyle.setborderright(borderstyle.thin);
            datacellstyle.setborderleft(borderstyle.thin);
            
            // 创建数据行
            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);
            }
            
            // 自动调整列宽
            for (int i = 0; i < 5; i++) {
                sheet.autosizecolumn(i);
            }
            
            // 写入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 exportusers() throws ioexception {
        // 生成示例数据
        list users = gettestusers();
        
        // 生成excel文件
        bytearrayinputstream in = excelexportservice.exportuserstoexcel(users);
        
        // 设置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));
    }
    
    // 生成测试用户数据
    private list gettestusers() {
        list users = new arraylist<>();
        
        user user1 = new user();
        user1.setid(1l);
        user1.setname("张三");
        user1.setage(28);
        user1.setemail("zhangsan@example.com");
        user1.setdepartment("研发部");
        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("财务部");
        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;
    }
}

    
    
    


    

        

            

                
导出excel文件
            
            

                
点击下面的按钮导出用户数据到excel文件：
                导出用户数据
            
        
    

4.4 创建复杂的excel文件

以下是一个创建更复杂excel文件的示例，包含多个工作表、合并单元格、公式等：

public bytearrayinputstream exportcomplexexcel(list departments) throws ioexception {
    try (workbook workbook = new xssfworkbook()) {
        // 创建字体和样式
        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);
        
        // 创建汇总表
        sheet summarysheet = workbook.createsheet("部门汇总");
        
        // 创建标题行
        row titlerow = summarysheet.createrow(0);
        cell titlecell = titlerow.createcell(0);
        titlecell.setcellvalue("公司部门人员统计");
        titlecell.setcellstyle(titlestyle);
        
        // 合并标题单元格
        summarysheet.addmergedregion(new cellrangeaddress(0, 0, 0, 3));
        
        // 创建表头
        row headerrow = summarysheet.createrow(1);
        string[] headers = {"部门id", "部门名称", "部门经理", "员工数量"};
        
        for (int i = 0; i < headers.length; i++) {
            cell cell = headerrow.createcell(i);
            cell.setcellvalue(headers[i]);
            cell.setcellstyle(headerstyle);
        }
        
        // 填充部门数据
        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();
            
            // 为每个部门创建单独的工作表
            sheet deptsheet = workbook.createsheet(dept.getname());
            
            // 创建部门表头
            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);
            }
            
            // 填充员工数据
            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());
                
                // 使用公式计算入职年限（假设年龄减去25）
                cell tenurecell = emprow.createcell(4);
                tenurecell.setcellformula("c" + emprowidx + "-25");
            }
            
            // 自动调整列宽
            for (int i = 0; i < 5; i++) {
                deptsheet.autosizecolumn(i);
            }
        }
        
        // 创建总计行
        row totalrow = summarysheet.createrow(rowidx);
        cell totallabelcell = totalrow.createcell(0);
        totallabelcell.setcellvalue("总计");
        totallabelcell.setcellstyle(headerstyle);
        
        // 合并总计标签单元格
        summarysheet.addmergedregion(new cellrangeaddress(rowidx, rowidx, 0, 2));
        
        cell totalvaluecell = totalrow.createcell(3);
        totalvaluecell.setcellvalue(totalemployees);
        totalvaluecell.setcellstyle(headerstyle);
        
        // 自动调整列宽
        for (int i = 0; i < 4; i++) {
            summarysheet.autosizecolumn(i);
        }
        
        // 添加图表
        xssfsheet chartsheet = (xssfsheet) workbook.createsheet("部门统计图");
        
        // 复制部门数据到图表数据表
        row chartheaderrow = chartsheet.createrow(0);
        chartheaderrow.createcell(0).setcellvalue("部门");
        chartheaderrow.createcell(1).setcellvalue("员工数");
        
        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());
        }
        
        // 创建图表和数据序列
        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("员工数");
        
        // 创建数据源
        xddfdatasource departments = xddfdatasourcesfactory.fromstringcellrange(
                chartsheet, new cellrangeaddress(1, chartrowidx - 1, 0, 0));
        
        xddfnumericaldatasource values = xddfdatasourcesfactory.fromnumericcellrange(
                chartsheet, new cellrangeaddress(1, chartrowidx - 1, 1, 1));
        
        // 创建柱状图
        xddfbarchartdata barchart = (xddfbarchartdata) chart.createdata(
                charttypes.bar, bottomaxis, leftaxis);
        barchart.setvarycolors(true);
        
        xddfbarchartdata.series series = (xddfbarchartdata.series) barchart.addseries(departments, values);
        series.settitle("员工数", null);
        
        chart.plot(barchart);
        
        // 写入bytearrayoutputstream
        bytearrayoutputstream outputstream = new bytearrayoutputstream();
        workbook.write(outputstream);
        
        return new bytearrayinputstream(outputstream.tobytearray());
    }
}

注意：上面的图表代码需要添加以下依赖：

    org.apache.poi
    poi-ooxml-full
    5.2.3

4.5 使用模板导出excel

在某些场景下，我们需要基于预定义的excel模板生成文件，以下是一个示例：

public bytearrayinputstream exportfromtemplate(list users) throws ioexception {
    // 加载模板文件
    try (inputstream templatestream = getclass().getresourceasstream("/templates/user_template.xlsx");
         workbook workbook = workbookfactory.create(templatestream)) {
        
        sheet sheet = workbook.getsheetat(0);
        
        // 从第二行开始填充数据（第一行是表头）
        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());
        }
        
        // 更新模板中的日期单元格（假设在a1位置）
        row headerrow = sheet.getrow(0);
        if (headerrow.getcell(6) != null) {
            cell datecell = headerrow.getcell(6);
            datecell.setcellvalue(new date());
        }
        
        // 自动调整列宽
        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，并且在处理大文件时有明显的性能优势。

5.1 使用easyexcel读取excel

5.1.1 创建数据模型

使用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 创建读取监听器

easyexcel采用事件模式读取excel，需要创建一个监听器来处理读取的数据：

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 {

    /**
     * 用于暂存读取的数据
     */
    private list employeelist = new arraylist<>();
    
    /**
     * 每读取一行数据就会调用一次invoke方法
     */
    @override
    public void invoke(employee employee, analysiscontext context) {
        log.info("读取到一条数据: {}", employee);
        employeelist.add(employee);
        
        // 达到batch_count时，需要存储一次数据库，防止数据几万条数据在内存，容易oom
        if (employeelist.size() >= 5000) {
            savedata();
            // 清理内存
            employeelist.clear();
        }
    }
    
    /**
     * 所有数据解析完成后调用此方法
     */
    @override
    public void doafterallanalysed(analysiscontext context) {
        // 确保最后一批数据被保存
        savedata();
        log.info("所有数据解析完成！");
    }
    
    /**
     * 保存数据，这里只是打印，实际应用中可以将数据存入数据库
     */
    private void savedata() {
        log.info("{}条数据，开始保存数据库！", employeelist.size());
        // 这里可以调用持久层完成数据入库
        log.info("存储数据库成功！");
    }
    
    /**
     * 获取读取到的数据
     */
    public list getemployeelist() {
        return employeelist;
    }
}

5.1.3 创建excel读取服务

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 reademployeedata(multipartfile file) throws ioexception {
        employeereadlistener listener = new employeereadlistener();
        
        easyexcel.read(file.getinputstream(), employee.class, listener).sheet().doread();
        
        return listener.getemployeelist();
    }
}

5.1.4 创建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> uploadexcel(@requestparam("file") multipartfile file) {
        try {
            list employees = easyexcelservice.reademployeedata(file);
            return responseentity.ok(employees);
        } catch (ioexception e) {
            log.error("excel读取失败", e);
            return responseentity.badrequest().build();
        }
    }
}

5.2 使用easyexcel导出excel

5.2.1 简单导出示例

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 {

    /**
     * 导出员工数据到excel文件
     */
    public void exportemployees(list employees, outputstream outputstream) {
        easyexcel.write(outputstream, employee.class)
                .registerwritehandler(new longestmatchcolumnwidthstylestrategy())  // 自动调整列宽
                .sheet("员工数据")
                .dowrite(employees);
    }
    
    /**
     * 导出员工数据到指定文件
     */
    public void exportemployeestofile(list 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("员工数据")
                .dowrite(employees);
    }
    
    /**
     * 导出多个sheet的excel
     */
    public void exportmultiplesheets(list> departmentemployees, 
                                    list sheetnames, 
                                    outputstream outputstream) {
        // 创建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);
                
                // 创建新的sheet
                var writesheet = easyexcel.writersheet(i, sheetname).build();
                
                // 写入数据
                excelwriter.write(departmentemployees.get(i), writesheet);
            }
        }
    }
}

5.2.2 创建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 {
        // 设置响应内容
        response.setcontenttype("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setcharacterencoding("utf-8");
        
        // 设置文件名
        string filename = urlencoder.encode("员工数据", standardcharsets.utf_8).replaceall("\\+", "%20");
        response.setheader("content-disposition", "attachment;filename*=utf-8''" + filename + ".xlsx");
        
        // 获取测试数据
        list employees = gettestemployees();
        
        // 导出excel
        exportservice.exportemployees(employees, response.getoutputstream());
    }
    
    @getmapping("/export-multiple-sheets")
    public void exportmultiplesheets(httpservletresponse response) throws ioexception {
        // 设置响应内容
        response.setcontenttype("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setcharacterencoding("utf-8");
        
        // 设置文件名
        string filename = urlencoder.encode("部门员工数据", standardcharsets.utf_8).replaceall("\\+", "%20");
        response.setheader("content-disposition", "attachment;filename*=utf-8''" + filename + ".xlsx");
        
        // 获取测试数据 - 三个部门的员工
        list> departmentemployees = new arraylist<>();
        departmentemployees.add(getemployeesbydepartment("研发部"));
        departmentemployees.add(getemployeesbydepartment("市场部"));
        departmentemployees.add(getemployeesbydepartment("行政部"));
        
        // sheet名称
        list sheetnames = arrays.aslist("研发部员工", "市场部员工", "行政部员工");
        
        // 导出excel
        exportservice.exportmultiplesheets(departmentemployees, sheetnames, response.getoutputstream());
    }
    
    /**
     * 生成测试员工数据
     */
    private list gettestemployees() {
        list employees = new arraylist<>();
        
        // 添加测试数据
        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 ? "研发部" : (i % 3 == 1 ? "市场部" : "行政部"));
            employee.sethiredate(new date());
            employee.setsalary(5000.0 + i * 1000);
            
            employees.add(employee);
        }
        
        return employees;
    }
    
    /**
     * 根据部门获取员工
     */
    private list getemployeesbydepartment(string department) {
        list allemployees = gettestemployees();
        list departmentemployees = new arraylist<>();
        
        for (employee employee : allemployees) {
            if (department.equals(employee.getdepartment())) {
                departmentemployees.add(employee);
            }
        }
        
        return departmentemployees;
    }

    // ... 5.2.3 使用自定义样式和复杂表头

    /**
     * 导出自定义样式的excel
     */
    public void exportwithcustomstyle(list employees, outputstream outputstream) {
        // 设置自定义拦截器来处理样式
        easyexcel.write(outputstream, employee.class)
                // 自动调整列宽
                .registerwritehandler(new longestmatchcolumnwidthstylestrategy())
                // 设置表头样式
                .registerwritehandler(new abstractrowheightstylestrategy() {
                    @override
                    protected void setheadcolumnheight(row row, int relativerowindex) {
                        // 设置表头行高
                        row.setheight((short) 500);
                    }

                    @override
                    protected void setcontentcolumnheight(row row, int relativerowindex) {
                        // 设置内容行高
                        row.setheight((short) 400);
                    }
                })
                // 设置单元格样式
                .registerwritehandler(new cellwritehandler() {
                    @override
                    public void aftercellcreate(writesheetholder writesheetholder, writetableholder writetableholder, 
                                              cell cell, head head, integer relativerowindex, boolean ishead) {
                        // 设置表头样式
                        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) {
                        // 在这里可以根据数据内容设置样式
                    }

                    @override
                    public void aftercelldispose(writesheetholder writesheetholder, writetableholder writetableholder, 
                                               list celldatalist, cell cell, head head, integer relativerowindex, boolean ishead) {
                        // 内容行的样式
                        if (!ishead) {
                            // 偶数行设置背景色
                            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("员工数据")
                .dowrite(employees);
    }

    /**
     * 导出复杂表头的excel
     */
    public void exportwithcomplexhead(list employees, outputstream outputstream) {
        // 构建复杂表头
        list> head = new arraylist<>();
        
        // 第一列 id
        list head1 = new arraylist<>();
        head1.add("基本信息");
        head1.add("员工id");
        head.add(head1);
        
        // 第二列 姓名
        list head2 = new arraylist<>();
        head2.add("基本信息");
        head2.add("姓名");
        head.add(head2);
        
        // 第三列 年龄
        list head3 = new arraylist<>();
        head3.add("基本信息");
        head3.add("年龄");
        head.add(head3);
        
        // 第四列 邮箱
        list head4 = new arraylist<>();
        head4.add("联系方式");
        head4.add("邮箱");
        head.add(head4);
        
        // 第五列 部门
        list head5 = new arraylist<>();
        head5.add("工作信息");
        head5.add("部门");
        head.add(head5);
        
        // 第六列 入职日期
        list head6 = new arraylist<>();
        head6.add("工作信息");
        head6.add("入职日期");
        head.add(head6);
        
        // 第七列 薪资
        list head7 = new arraylist<>();
        head7.add("薪资信息");
        head7.add("月薪(元)");
        head.add(head7);
        
        // 将数据转为list>格式
        list> datalist = new arraylist<>();
        for (employee employee : employees) {
            list