SpringBoot中Excel处理完全指南分享_Java

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处理库。本指南将主要介绍：

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

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 创建基本配置类

创建一个配置类来处理excel相关的配置：

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); // 设置上传文件最大为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 创建配置类

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); // 设置上传文件最大为10mb
        return resolver;
    }
}

3. 使用apache poi读取excel文件

3.1 创建数据模型

首先，创建一个模型类来映射excel中的数据：

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 创建excel读取服务

创建一个服务类来处理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);
            
            // 跳过标题行
            iterator<row> rowiterator = sheet.rowiterator();
            if (rowiterator.hasnext()) {
                rowiterator.next(); // 跳过标题行
            }
            
            // 遍历数据行
            while (rowiterator.hasnext()) {
                row row = rowiterator.next();
                user user = new user();
                
                // 读取单元格数据
                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 创建controller处理excel上传

创建一个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 创建html上传页面

在src/main/resources/templates目录下创建upload.html：

<!doctype html>
<html xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="utf-8">
    <title>excel上传</title>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css" 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>上传结果:</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 处理更复杂的excel结构

在实际应用中，excel结构可能更复杂，如多个工作表、合并单元格、公式等。以下是处理这些情况的示例：

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并关联到相应部门
            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创建和导出excel文件

4.1 创建基本excel文件

以下是一个创建简单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()) {
            // 创建工作表
            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());
        }
    }
}

4.2 创建导出控制器

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 {
        // 生成示例数据
        list<user> 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<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("研发部");
        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;
    }
}

4.3 创建导出页面

在src/main/resources/templates目录下创建export.html：

<!doctype html>
<html xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="utf-8">
    <title>excel导出</title>
    <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css" 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">
                <p>点击下面的按钮导出用户数据到excel文件：</p>
                <a href="/api/excel/export" rel="external nofollow"  class="btn btn-primary">导出用户数据</a>
            </div>
        </div>
    </div>
</body>
</html>

4.4 创建复杂的excel文件

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

public bytearrayinputstream exportcomplexexcel(list<department> 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<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));
        
        // 创建柱状图
        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());
    }
}

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

<dependency>
    <groupid>org.apache.poi</groupid>
    <artifactid>poi-ooxml-full</artifactid>
    <version>5.2.3</version>
</dependency>

4.5 使用模板导出excel

在某些场景下，我们需要基于预定义的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);
        
        // 从第二行开始填充数据（第一行是表头）
        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<employee> {

    /**
     * 用于暂存读取的数据
     */
    private list<employee> 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<employee> 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<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 创建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导出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<employee> employees, outputstream outputstream) {
        easyexcel.write(outputstream, employee.class)
                .registerwritehandler(new longestmatchcolumnwidthstylestrategy())  // 自动调整列宽
                .sheet("员工数据")
                .dowrite(employees);
    }
    
    /**
     * 导出员工数据到指定文件
     */
    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("员工数据")
                .dowrite(employees);
    }
    
    /**
     * 导出多个sheet的excel
     */
    public void exportmultiplesheets(list<list<employee>> departmentemployees, 
                                    list<string> 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<employee> 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<list<employee>> departmentemployees = new arraylist<>();
        departmentemployees.add(getemployeesbydepartment("研发部"));
        departmentemployees.add(getemployeesbydepartment("市场部"));
        departmentemployees.add(getemployeesbydepartment("行政部"));
        
        // sheet名称
        list<string> sheetnames = arrays.aslist("研发部员工", "市场部员工", "行政部员工");
        
        // 导出excel
        exportservice.exportmultiplesheets(departmentemployees, sheetnames, response.getoutputstream());
    }
    
    /**
     * 生成测试员工数据
     */
    private list<employee> gettestemployees() {
        list<employee> 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<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 使用自定义样式和复杂表头

    /**
     * 导出自定义样式的excel
     */
    public void exportwithcustomstyle(list<employee> 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<celldata> 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<employee> employees, outputstream outputstream) {
        // 构建复杂表头
        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("联系方式");
        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);
        
        // 将数据转为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("员工数据")
                .dowrite(datalist);
    }
}

6. 处理大型excel文件的策略

6.1 使用apache poi sxssf模式

sxssf（streaming xlsx writer）是poi提供的流式写入方式，可以大大减少内存使用：

public void exportlargeexcel(string filename, int rowcount) throws ioexception {
    try (sxssfworkbook workbook = new sxssfworkbook(100)) { // 100表示内存中保留的行数
        sheet sheet = workbook.createsheet("大数据");
        
        // 创建表头
        row headerrow = sheet.createrow(0);
        for (int i = 0; i < 10; i++) {
            headerrow.createcell(i).setcellvalue("列 " + (i + 1));
        }
        
        // 创建数据行
        for (int i = 0; i < rowcount; i++) {
            row row = sheet.createrow(i + 1);
            for (int j = 0; j < 10; j++) {
                row.createcell(j).setcellvalue("数据 " + (i + 1) + "-" + (j + 1));
            }
            
            // 每生成10000行清理一次临时文件
            if (i % 10000 == 0) {
                ((sxssfsheet)sheet).flushrows();
            }
        }
        
        // 写入文件
        try (fileoutputstream outputstream = new fileoutputstream(filename)) {
            workbook.write(outputstream);
        }
        
        // 清理临时文件
        workbook.dispose();
    }
}

注意事项：

使用完毕后一定要调用dispose()方法清理临时文件
sxssf仅支持写入操作，不支持读取
不支持某些高级特性（如合并单元格等）

6.2 使用easyexcel处理大文件

easyexcel在设计上就考虑了大文件处理，采用sax方式逐行读取，内存占用小：

// 读取大文件
public void readlargeexcel(string filename) {
    // 使用sax方式读取
    easyexcel.read(filename, employee.class, new employeereadlistener())
            .sheet()
            .doread();
}

// 写入大文件
public void writelargeexcel(string filename, int batchsize) {
    // 分批获取数据
    try (excelwriter excelwriter = easyexcel.write(filename, employee.class)
            .registerwritehandler(new longestmatchcolumnwidthstylestrategy())
            .build()) {
        
        // 获取writesheet对象
        writesheet writesheet = easyexcel.writersheet("员工数据").build();
        
        // 模拟分批获取数据
        int totalcount = 100000; // 总数据量
        for (int i = 0; i < totalcount; i += batchsize) {
            // 获取当前批次数据
            list<employee> data = getbatchdata(i, math.min(i + batchsize, totalcount));
            // 写入excel
            excelwriter.write(data, writesheet);
        }
    }
}

// 模拟分批获取数据
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 ? "研发部" : (i % 3 == 1 ? "市场部" : "行政部"));
        employee.sethiredate(new date());
        employee.setsalary(5000.0 + (i % 10) * 1000);
        
        list.add(employee);
    }
    return list;
}

6.3 使用csv代替excel

对于极大的数据集，考虑使用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 分页导出大型数据集

对于web应用中需要导出的大型数据集，可以考虑分页导出：

@getmapping("/export/paged")
public responseentity<string> exportpaged() {
    // 生成唯一任务id
    string taskid = uuid.randomuuid().tostring();
    
    // 启动异步任务
    completablefuture.runasync(() -> {
        try {
            // 导出文件路径
            string filepath = "/temp/" + taskid + ".xlsx";
            
            // 分页查询数据并写入excel
            int pagesize = 1000;
            int totalpages = gettotalpages(pagesize);
            
            try (excelwriter excelwriter = easyexcel.write(filepath, employee.class)
                    .registerwritehandler(new longestmatchcolumnwidthstylestrategy())
                    .build()) {
                
                writesheet writesheet = easyexcel.writersheet("员工数据").build();
                
                // 分页导出
                for (int pagenum = 0; pagenum < totalpages; pagenum++) {
                    // 从数据库分页查询数据
                    list<employee> pagedata = getpagedata(pagenum, pagesize);
                    // 写入excel
                    excelwriter.write(pagedata, writesheet);
                    // 更新进度
                    updateexportprogress(taskid, (pagenum + 1) * 100 / totalpages);
                }
            }
            
            // 更新导出状态为完成
            updateexportstatus(taskid, "completed", filepath);
            
        } catch (exception e) {
            // 更新导出状态为失败
            updateexportstatus(taskid, "failed", null);
        }
    });
    
    // 返回任务id
    return responseentity.ok(taskid);
}

@getmapping("/export/status/{taskid}")
public responseentity<map<string, object>> getexportstatus(@pathvariable string taskid) {
    // 获取任务状态
    map<string, object> status = gettaskstatus(taskid);
    return responseentity.ok(status);
}

@getmapping("/export/download/{taskid}")
public responseentity<resource> downloadexportedfile(@pathvariable string taskid) {
    // 获取导出文件路径
    string filepath = getexportedfilepath(taskid);
    
    if (filepath == null) {
        return responseentity.notfound().build();
    }
    
    // 创建文件资源
    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. 实际应用场景和最佳实践

7.1 动态列导出

在某些业务场景中，需要根据用户选择动态确定导出的列：

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("员工数据");
        
        // 创建表头行
        row headerrow = sheet.createrow(0);
        
        // 设置表头样式
        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);
            }
        }
        
        // 填充数据
        int rowidx = 1;
        for (employee employee : employees) {
            row row = sheet.createrow(rowidx++);
            
            colidx = 0;
            for (string column : selectedcolumns) {
                cell cell = row.createcell(colidx++);
                
                // 根据列名设置单元格值
                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());
                            
                            // 设置日期格式
                            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;
                }
            }
        }
        
        // 自动调整列宽
        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文件校验

在导入excel文件前进行数据校验：

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++;
        
        // 验证数据
        list<string> errors = validateemployee(employee);
        
        if (errors.isempty()) {
            // 数据有效
            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) {
        // 处理完成
    }
    
    // 验证员工数据
    private list<string> validateemployee(employee employee) {
        list<string> errors = new arraylist<>();
        
        // 验证姓名
        if (employee.getname() == null || employee.getname().trim().isempty()) {
            errors.add("姓名不能为空");
        }
        
        // 验证年龄
        if (employee.getage() == null) {
            errors.add("年龄不能为空");
        } else if (employee.getage() < 18 || employee.getage() > 65) {
            errors.add("年龄必须在18-65岁之间");
        }
        
        // 验证邮箱
        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("邮箱格式不正确");
            }
        }
        
        // 验证部门
        if (employee.getdepartment() == null || employee.getdepartment().trim().isempty()) {
            errors.add("部门不能为空");
        }
        
        // 验证薪资
        if (employee.getsalary() != null && employee.getsalary() < 0) {
            errors.add("薪资不能为负数");
        }
        
        return errors;
    }
    
    public list<employee> getvalidemployees() {
        return validemployees;
    }
    
    public list<map<string, object>> geterrorrecords() {
        return errorrecords;
    }
    
    public boolean haserrors() {
        return !errorrecords.isempty();
    }
}

7.4 统一异常处理

为excel处理添加统一的异常处理：

@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("参数异常", e);
        
        map<string, string> response = new hashmap<>();
        response.put("error", "参数异常");
        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. 性能优化和注意事项

8.1 性能优化建议

使用适当的excel库：

小文件可使用apache poi
大文件请使用easyexcel或poi的sxssf模式
极大文件考虑使用csv格式

避免一次性加载整个文件：

读取时使用流式解析
写入时使用分批写入

合理设置缓冲区大小：

在sxssfworkbook中设置合理的内存行数
在批处理中选择合适的批次大小

减少样式对象：

样式对象重用，而不是为每个单元格创建新样式
限制使用的颜色、字体和边框样式数量

使用异步处理：

将大文件处理放在后台线程中执行
提供进度反馈机制

8.2 注意事项

内存管理：

注意监控jvm内存使用情况
对于大文件处理，考虑增加jvm堆内存（-xmx参数）
使用完毕后及时关闭资源和清理临时文件

安全考虑：

限制上传文件大小
验证文件类型和内容
防止恶意excel文件（包含宏或公式）

编码问题：

处理国际字符时，确保使用正确的字符编码
文件名包含中文时，确保正确编码

并发控制：

大文件处理时注意服务器负载
限制并发处理任务数量

临时文件清理：

使用sxssf时，必须调用dispose()方法清理临时文件
定期清理服务器上的临时文件

总结

spring boot提供了强大而灵活的excel处理能力，通过结合apache poi和easyexcel等工具，可以轻松实现excel文件的读取、创建和导出功能。在实际应用中，应根据具体需求和数据量选择合适的处理策略，既要保证功能完整，又要注重性能和资源使用。

无论是简单的数据导出，还是复杂的报表生成，或是大数据量的文件处理，都可以通过本文介绍的方法灵活实现。重点是要根据实际业务场景，选择合适的技术方案，并注意性能优化和异常处理。

以上为个人经验，希望能给大家一个参考，也希望大家多多支持代码网。


验证码：

SpringBoot中Excel处理完全指南分享

2025年04月21日 • Java •我要评论