跳到主要内容

Excel 处理

Excel Spring Boot Starter 是一个基于 Apache POI 的 Excel 处理工具库，提供了简洁易用的 Builder API 和强大的注解支持，帮助开发者快速实现 Excel 文件的读写功能。

主要特性：

  • 🚀 流式处理：支持大文件的流式读取，内存占用低
  • 🎯 注解驱动：通过注解定义 Excel 与 Java 对象的映射关系
  • 🔧 链式 API：流畅的 Builder 模式，代码简洁优雅
  • 🎨 样式支持：丰富的样式设置选项，支持注解和编程式配置
  • 📊 CellSet 架构：灵活强大的单元格处理机制
  • ⚡ 性能优化：针对大数据集进行了优化

依赖配置​

在 pom.xml 中添加依赖：

<dependency>
    <groupId>com.jeeapp</groupId>
    <artifactId>excel-spring-boot-starter</artifactId>
    <version>0.2.2</version>
</dependency>

快速开始​

定义实体类​

使用 @FieldColumn 注解定义 Excel 列映射：

import com.jeeapp.excel.annotation.FieldColumn;
import lombok.Data;

@Data
public class User {

    @FieldColumn(name = "姓名", order = 1)
    private String name;

    @FieldColumn(name = "年龄", order = 2)
    private Integer age;

    @FieldColumn(name = "邮箱", order = 3)
    private String email;

    @FieldColumn(name = "部门", order = 4)
    private String department;
}

读取 Excel​

基础读取​

import com.jeeapp.excel.RowReader;
import com.jeeapp.excel.mapping.AnnotationBasedRowMapper;
import org.apache.poi.ss.usermodel.Row;
import org.springframework.stereotype.Service;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

@Service
public class ExcelService {

    public List<User> readUsers(InputStream inputStream) throws Exception {
        List<User> users = new ArrayList<>();

        try (RowReader reader = RowReader.open(inputStream)) {
            AnnotationBasedRowMapper<User> mapper = 
                new AnnotationBasedRowMapper<>(User.class);

            for (Row row : reader) {
                if (row.getRowNum() == 0) {
                    continue; // 跳过标题行
                }
                User user = mapper.mapRow(row);
                if (user != null) {
                    users.add(user);
                }
            }
        }

        return users;
    }
}

写入 Excel​

手动创建方式​

import com.jeeapp.excel.WorkbookBuilder;
import com.jeeapp.excel.SheetBuilder;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import java.io.OutputStream;
import java.util.List;

@Service
public class ExcelService {

    public void writeUsers(List<User> users, OutputStream outputStream) 
            throws Exception {
        Workbook workbook = new XSSFWorkbook();

        WorkbookBuilder workbookBuilder = WorkbookBuilder.builder(workbook);
        SheetBuilder sheetBuilder = workbookBuilder.createSheet("用户列表");

        // 创建标题行
        sheetBuilder.createRow()
            .createCell("姓名")
            .createCell("年龄")
            .createCell("邮箱")
            .createCell("部门");

        // 创建数据行
        for (User user : users) {
            sheetBuilder.createRow()
                .createCell(user.getName())
                .createCell(user.getAge())
                .createCell(user.getEmail())
                .createCell(user.getDepartment());
        }

        workbook.write(outputStream);
        workbook.close();
    }
}

使用 populate() 方法（推荐）​

使用 populate() 方法可以自动根据注解生成 Excel，代码更简洁：

import com.jeeapp.excel.WorkbookBuilder;
import com.jeeapp.excel.cellset.FieldCellSetProvider;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.OutputStream;
import java.util.List;

@Service
public class ExcelService {

    public void writeUsersWithPopulate(List<User> users, OutputStream outputStream) 
            throws Exception {
        XSSFWorkbook workbook = new XSSFWorkbook();

        WorkbookBuilder.builder(workbook)
            .createSheet("用户列表")
                // 使用 populate 自动填充数据
                .populate(FieldCellSetProvider.create(User.class, users))
            .build()
            .write(outputStream);

        workbook.close();
    }
}

CellSet 架构​

Excel Starter 的核心架构基于 CellSet（单元格集合）的概念，提供了灵活而强大的单元格处理机制。

核心概念​

CellSet（单元格集合）​

CellSet 表示一个或多个单元格的集合，继承自 CellRangeAddress，可以表示单个单元格或合并单元格区域。

public class CellSet extends CellRangeAddress {
    private Object value;  // 单元格的值

    public CellSet(int firstRow, int lastRow, int firstCol, int lastCol) {
        super(firstRow, lastRow, firstCol, lastCol);
    }
}

CellSetProvider（单元格集合提供者）​

CellSetProvider 负责生成 CellSet 集合，并定义如何处理这些单元格。

public interface CellSetProvider<T extends CellSet> {
    // 获取单元格集合
    CellSets<T> getCellSets(SheetContext context);

    // 后处理单元格集合
    void postProcess(CellSetHolder<T> holder);
}

CellSetHolder（单元格集合持有者）​

CellSetHolder 是 CellSet 的包装器，提供便捷的操作方法。

public class CellSetHolder<T extends CellSet> {
    // 合并单元格
    public CellSetHolder<T> mergeRegion() { ... }

    // 设置单元格值
    public void setCellValue(Object value) { ... }

    // 设置样式
    public CellSetHolder<T> setBold(boolean bold) { ... }
    public CellSetHolder<T> setBackgroundColor(short color) { ... }
    public CellSetHolder<T> setDataFormat(String format) { ... }

    // 创建注释
    public CellSetHolder<T> createCellComment(String text, String author, 
                                              int width, int height) { ... }

    // 创建超链接
    public CellSetHolder<T> createHyperlink(HyperlinkType type, 
                                           String address, String label) { ... }
}

CellSetProcessor（单元格集合处理器）​

CellSetProcessor 负责处理单元格集合，可以自定义单元格的显示逻辑。

@FunctionalInterface
public interface CellSetProcessor<T extends CellSet> {
    // 处理单元格集合，返回 false 表示停止后续处理
    boolean process(CellSetHolder<T> holder);

    // 默认处理器：合并单元格并设置值
    static <T extends CellSet> CellSetProcessor<T> defaults() {
        return holder -> {
            holder.mergeRegion().setCellValue(holder.getCellSet().getValue());
            return true;
        };
    }
}

FieldCellSetProvider​

FieldCellSetProvider 是基于 Java 对象字段的提供者实现，支持通过注解定义表格结构。

基础用法​

import com.jeeapp.excel.WorkbookBuilder;
import com.jeeapp.excel.cellset.FieldCellSetProvider;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.util.List;

// 定义数据模型
@Data
public class Employee {
    @FieldColumn(name = "员工姓名", order = 1, width = 15)
    private String name;

    @FieldColumn(name = "部门", order = 2)
    private String department;

    @FieldColumn(name = "薪资", order = 3)
    private BigDecimal salary;
}

// 使用 FieldCellSetProvider
@Service
public class ExcelService {

    public void exportEmployees(List<Employee> employees, OutputStream outputStream) 
            throws Exception {
        XSSFWorkbook workbook = new XSSFWorkbook();

        WorkbookBuilder.builder(workbook)
            .createSheet("员工列表")
                .populate(FieldCellSetProvider.create(Employee.class, employees))
            .build()
            .write(outputStream);

        workbook.close();
    }
}

使用 Builder 配置​

// 使用 Builder 自定义配置
FieldCellSetProvider<Employee> provider = FieldCellSetProvider
    .builder(Employee.class, employees)
    .needHeaders(true)                    // 是否需要表头
    .ignoreNullValues(true)               // 忽略 null 值
    .includes("name", "department")       // 只包含指定字段
    .excludes("internalId")               // 排除指定字段
    .startRow(5)                          // 起始行号（从 0 开始）
    .startColumn(2)                       // 起始列号（从 0 开始）
    .headerNames(Map.of(                  // 自定义表头名称
        "name", "员工姓名",
        "department", "所属部门"
    ))
    .processor(holder -> {                // 自定义处理器
        // 自定义处理逻辑
        holder.mergeRegion().setCellValue(holder.getCellSet().getValue());
        return true;
    })
    .build();

// 应用到 Sheet
sheetBuilder.populate(provider);

支持嵌套对象​

@Data
public class Employee {
    @FieldColumn(name = "姓名", order = 1)
    private String name;

    // 嵌套对象会自动展开
    private Address address;

    private List<Skill> skills;  // 支持集合
}

@Data
public class Address {
    @FieldColumn(name = "省份", order = 2)
    private String province;

    @FieldColumn(name = "城市", order = 3)
    private String city;
}

@Data
public class Skill {
    @FieldColumn(name = "技能名称")
    private String name;

    @FieldColumn(name = "熟练度")
    private String level;
}

// 使用时会自动生成层级表头和多行数据
// 例如：
// | 姓名 | 省份 | 城市 | 技能名称 | 熟练度 |
// | 张三 | 浙江 | 杭州 | Java     | 精通   |
// |      |      |      | Python   | 熟悉   |

字段路径过滤​

// 使用路径表达式精确控制字段
FieldCellSetProvider
    .builder(Employee.class, employees)
    .includes(
        "name",                  // 包含 name 字段
        "address.city",          // 只包含 address 的 city 字段
        "skills.name"            // 只包含 skills 的 name 字段
    )
    .build();

JsonCellSetProvider​

JsonCellSetProvider 支持直接从 JSON 数据生成 Excel。

import com.jeeapp.excel.cellset.JsonCellSetProvider;

String json = """
    [
        {
            "name": "张三",
            "age": 25,
            "address": {
                "city": "杭州",
                "province": "浙江"
            }
        },
        {
            "name": "李四",
            "age": 30,
            "address": {
                "city": "上海",
                "province": "上海"
            }
        }
    ]
    """;

// 创建 JsonCellSetProvider
JsonCellSetProvider provider = new JsonCellSetProvider(json);
provider.setNeedHeaders(true);
provider.setIncludes(List.of("name", "age", "address.city"));

// 应用到 Sheet
sheetBuilder.populate(provider);

自定义 CellSetProvider​

创建自定义的 CellSetProvider 来实现特定的业务需求。

import com.jeeapp.excel.cellset.*;
import com.jeeapp.excel.SheetContext;
import org.apache.poi.ss.usermodel.IndexedColors;

/**
 * 自定义 CellSetProvider：生成九九乘法表
 */
public class MultiplicationTableProvider implements CellSetProvider<CellSet> {

    private final int size;

    public MultiplicationTableProvider(int size) {
        this.size = size;
    }

    @Override
    public CellSets<CellSet> getCellSets(SheetContext context) {
        CellSets<CellSet> cellSets = CellSets.of();

        int startRow = context.getLastRowNum() + 1;
        int startCol = 0;

        // 生成乘法表数据
        for (int i = 1; i <= size; i++) {
            for (int j = 1; j <= i; j++) {
                int row = startRow + i - 1;
                int col = startCol + j - 1;

                CellSet cellSet = new CellSet(row, row, col, col);
                cellSet.setValue(j + "×" + i + "=" + (i * j));
                cellSets.add(cellSet);
            }
        }

        return cellSets;
    }

    @Override
    public void postProcess(CellSetHolder<CellSet> holder) {
        CellSet cellSet = holder.getCellSet();
        String value = String.valueOf(cellSet.getValue());

        // 对角线单元格使用特殊样式
        if (cellSet.getFirstRow() == cellSet.getFirstColumn()) {
            holder.setBackgroundColor(IndexedColors.YELLOW.getIndex())
                  .setBold(true);
        }

        // 设置单元格值和样式
        holder.setBorder(true)
              .setAlignment(org.apache.poi.ss.usermodel.HorizontalAlignment.CENTER)
              .mergeRegion()
              .setCellValue(value);
    }
}

// 使用自定义 Provider
sheetBuilder.populate(new MultiplicationTableProvider(9));

自定义 CellSetProcessor​

CellSetProcessor 可以在 @FieldColumn 注解中使用，也可以独立使用。

在注解中使用​

@Data
public class Product {
    @FieldColumn(name = "产品名称", order = 1)
    private String name;

    @FieldColumn(name = "价格", order = 2, processor = PriceProcessor.class)
    private BigDecimal price;

    @FieldColumn(name = "状态", order = 3, processor = StatusProcessor.class)
    private Integer status;

    /**
     * 价格处理器：添加货币符号和颜色
     */
    public static class PriceProcessor implements CellSetProcessor<FieldCellSet> {
        @Override
        public boolean process(CellSetHolder<FieldCellSet> holder) {
            Object value = holder.getCellSet().getValue();
            if (value instanceof BigDecimal) {
                BigDecimal price = (BigDecimal) value;

                // 格式化价格
                String formattedPrice = "¥" + price.setScale(2, RoundingMode.HALF_UP);
                holder.getCellSet().setValue(formattedPrice);

                // 根据价格设置颜色
                if (price.compareTo(new BigDecimal("100")) > 0) {
                    holder.setFontColor(IndexedColors.RED.getIndex());
                } else {
                    holder.setFontColor(IndexedColors.GREEN.getIndex());
                }
            }

            holder.mergeRegion().setCellValue(holder.getCellSet().getValue());
            return true;
        }
    }

    /**
     * 状态处理器：显示中文状态并设置背景色
     */
    public static class StatusProcessor implements CellSetProcessor<FieldCellSet> {
        @Override
        public boolean process(CellSetHolder<FieldCellSet> holder) {
            Object value = holder.getCellSet().getValue();
            if (value instanceof Integer) {
                int status = (Integer) value;

                switch (status) {
                    case 0:
                        holder.getCellSet().setValue("已下架");
                        holder.setBackgroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
                        break;
                    case 1:
                        holder.getCellSet().setValue("在售");
                        holder.setBackgroundColor(IndexedColors.LIGHT_GREEN.getIndex());
                        break;
                    case 2:
                        holder.getCellSet().setValue("预售");
                        holder.setBackgroundColor(IndexedColors.LIGHT_YELLOW.getIndex());
                        break;
                    default:
                        holder.getCellSet().setValue("未知");
                }
            }

            holder.mergeRegion().setCellValue(holder.getCellSet().getValue());
            return true;
        }
    }
}

在 Provider 中使用​

// 创建自定义处理器
CellSetProcessor<FieldCellSet> customProcessor = holder -> {
    FieldCellSet cellSet = holder.getCellSet();

    // 只处理数据行（非表头）
    if (!cellSet.isHeader()) {
        Object value = cellSet.getValue();

        // 处理空值
        if (value == null) {
            cellSet.setValue("N/A");
            holder.setFontColor(IndexedColors.GREY_50_PERCENT.getIndex());
        }

        // 处理数字：添加千分位分隔符
        if (value instanceof Number) {
            DecimalFormat df = new DecimalFormat("#,###.##");
            cellSet.setValue(df.format(value));
        }

        // 处理日期：格式化
        if (value instanceof Date) {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            cellSet.setValue(sdf.format(value));
        }
    }

    // 应用默认处理（合并单元格并设置值）
    holder.mergeRegion().setCellValue(cellSet.getValue());
    return true;
};

// 应用到 Provider
FieldCellSetProvider<Product> provider = FieldCellSetProvider
    .builder(Product.class, products)
    .processor(customProcessor)
    .build();

sheetBuilder.populate(provider);

组合多个处理器​

// 定义多个处理器
CellSetProcessor<FieldCellSet> validator = holder -> {
    Object value = holder.getCellSet().getValue();
    if (value == null) {
        holder.setBackgroundColor(IndexedColors.RED.getIndex());
    }
    return true;  // 继续执行下一个处理器
};

CellSetProcessor<FieldCellSet> formatter = holder -> {
    Object value = holder.getCellSet().getValue();
    if (value instanceof String) {
        holder.getCellSet().setValue(((String) value).toUpperCase());
    }
    return true;
};

CellSetProcessor<FieldCellSet> defaultProcessor = CellSetProcessor.defaults();

// 组合处理器
CellSetProcessor<FieldCellSet> combined = CellSetProcessor.from(
    validator,
    formatter,
    defaultProcessor
);

// 使用组合处理器
FieldCellSetProvider<Product> provider = FieldCellSetProvider
    .builder(Product.class, products)
    .processor(combined)
    .build();

高级功能​

样式设置​

单元格样式​

import com.jeeapp.excel.CellBuilder;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.HorizontalAlignment;

@Service
public class ExcelStyleService {

    public void createStyledExcel(OutputStream outputStream) throws Exception {
        Workbook workbook = new XSSFWorkbook();

        WorkbookBuilder workbookBuilder = WorkbookBuilder.builder(workbook);
        SheetBuilder sheetBuilder = workbookBuilder.createSheet("样式示例");

        // 设置默认样式并创建标题行
        sheetBuilder
            .setBackgroundColor(IndexedColors.BLUE.getIndex())
            .setFontColor(IndexedColors.WHITE.getIndex())
            .setBold(true)
            .setBorder(true)
            .createRow()
                .createCell("姓名")
                .createCell("年龄");

        // 设置数据样式并创建数据行
        sheetBuilder
            .setBorder(true)
            .setAlignment(HorizontalAlignment.CENTER)
            .createRow()
                .createCell("张三")
                .createCell(25);

        workbook.write(outputStream);
        workbook.close();
    }
}

注解样式​

import com.jeeapp.excel.annotation.FieldColumn;
import com.jeeapp.excel.annotation.HeaderStyle;
import com.jeeapp.excel.annotation.RecordStyle;
import lombok.Data;

@Data
public class StyledUser {

    @FieldColumn(name = "姓名", order = 1)
    @HeaderStyle(backgroundColor = "BLUE", fontColor = "WHITE", bold = true)
    @RecordStyle(alignment = "CENTER")
    private String name;

    @FieldColumn(name = "年龄", order = 2)
    @HeaderStyle(backgroundColor = "GREEN", fontColor = "WHITE", bold = true)
    @RecordStyle(alignment = "CENTER")
    private Integer age;
}

公式支持​

import com.jeeapp.excel.CellBuilder;
import com.jeeapp.excel.SheetBuilder;

@Service
public class ExcelFormulaService {

    public void createExcelWithFormulas(OutputStream outputStream) throws Exception {
        Workbook workbook = new XSSFWorkbook();

        WorkbookBuilder workbookBuilder = WorkbookBuilder.builder(workbook);
        SheetBuilder sheetBuilder = workbookBuilder.createSheet("公式示例");

        // 创建数据行
        sheetBuilder.createRow()
            .createCell(10)
            .createCell(20)
            .matchingLastCell()
                .setCellFormula("A1+B1");

        sheetBuilder.createRow()
            .createCell(30)
            .createCell(40)
            .matchingLastCell()
                .setCellFormula("A2+B2");

        // 创建总计行
        sheetBuilder.createRow()
            .createCell("总计")
            .matchingCell(2, 1)
                .setCellFormula("SUM(A1:A2)")
            .matchingCell(2, 2)
                .setCellFormula("SUM(C1:C2)");

        workbook.write(outputStream);
        workbook.close();
    }
}

多工作表​

import com.jeeapp.excel.WorkbookBuilder;
import com.jeeapp.excel.SheetBuilder;

@Service
public class ExcelMultiSheetService {

    public void createMultiSheetExcel(OutputStream outputStream) throws Exception {
        Workbook workbook = new XSSFWorkbook();

        WorkbookBuilder workbookBuilder = WorkbookBuilder.builder(workbook);

        // 创建用户工作表
        workbookBuilder.createSheet("用户信息")
            .createRow()
                .createCell("姓名")
                .createCell("年龄");

        // 创建部门工作表
        workbookBuilder.createSheet("部门信息")
            .createRow()
                .createCell("部门名称")
                .createCell("部门人数");

        // 创建统计工作表
        workbookBuilder.createSheet("统计信息")
            .createRow()
                .createCell("统计项")
                .createCell("数值");

        workbook.write(outputStream);
        workbook.close();
    }
}

高级示例​

动态数据透视表​

/**
 * 动态数据透视表 Provider
 */
public class PivotTableProvider implements CellSetProvider<CellSet> {

    private final Map<String, Map<String, Object>> data;  // category -> month -> value
    private final List<String> categories;
    private final List<String> months;

    public PivotTableProvider(Map<String, Map<String, Object>> data, 
                             List<String> categories, 
                             List<String> months) {
        this.data = data;
        this.categories = categories;
        this.months = months;
    }

    @Override
    public CellSets<CellSet> getCellSets(SheetContext context) {
        CellSets<CellSet> cellSets = CellSets.of();
        int startRow = context.getLastRowNum() + 1;

        // 生成表头
        CellSet headerCell = new CellSet(startRow, startRow, 0, 0);
        headerCell.setValue("类别/月份");
        cellSets.add(headerCell);

        for (int i = 0; i < months.size(); i++) {
            CellSet monthCell = new CellSet(startRow, startRow, i + 1, i + 1);
            monthCell.setValue(months.get(i));
            cellSets.add(monthCell);
        }

        // 生成数据行
        for (int i = 0; i < categories.size(); i++) {
            String category = categories.get(i);
            int row = startRow + i + 1;

            // 类别列
            CellSet categoryCell = new CellSet(row, row, 0, 0);
            categoryCell.setValue(category);
            cellSets.add(categoryCell);

            // 数据列
            Map<String, Object> categoryData = data.get(category);
            for (int j = 0; j < months.size(); j++) {
                String month = months.get(j);
                CellSet dataCell = new CellSet(row, row, j + 1, j + 1);
                dataCell.setValue(categoryData.get(month));
                cellSets.add(dataCell);
            }
        }

        return cellSets;
    }

    @Override
    public void postProcess(CellSetHolder<CellSet> holder) {
        CellSet cellSet = holder.getCellSet();
        int row = cellSet.getFirstRow();
        int col = cellSet.getFirstColumn();

        // 表头样式
        if (row == holder.getSheetContext().getLastRowNum() + 1 || col == 0) {
            holder.setBold(true)
                  .setBackgroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        }

        // 数值单元格样式
        if (cellSet.getValue() instanceof Number) {
            holder.setDataFormat("#,##0.00")
                  .setAlignment(HorizontalAlignment.RIGHT);
        }

        holder.setBorder(true)
              .mergeRegion()
              .setCellValue(cellSet.getValue());
    }
}

// 使用
Map<String, Map<String, Object>> data = ...;  // 准备数据
List<String> categories = List.of("食品", "服装", "电子");
List<String> months = List.of("1月", "2月", "3月");

sheetBuilder.populate(new PivotTableProvider(data, categories, months));

复杂表头生成​

/**
 * 复杂表头 Provider（支持多级表头）
 */
public class ComplexHeaderProvider implements CellSetProvider<CellSet> {

    @Override
    public CellSets<CellSet> getCellSets(SheetContext context) {
        CellSets<CellSet> cellSets = CellSets.of();
        int startRow = context.getLastRowNum() + 1;

        // 第一级表头（跨列合并）
        CellSet header1 = new CellSet(startRow, startRow, 0, 2);
        header1.setValue("基本信息");
        cellSets.add(header1);

        CellSet header2 = new CellSet(startRow, startRow, 3, 5);
        header2.setValue("销售数据");
        cellSets.add(header2);

        // 第二级表头
        String[] subHeaders1 = {"姓名", "部门", "职位"};
        for (int i = 0; i < subHeaders1.length; i++) {
            CellSet cell = new CellSet(startRow + 1, startRow + 1, i, i);
            cell.setValue(subHeaders1[i]);
            cellSets.add(cell);
        }

        String[] subHeaders2 = {"Q1", "Q2", "Q3"};
        for (int i = 0; i < subHeaders2.length; i++) {
            CellSet cell = new CellSet(startRow + 1, startRow + 1, i + 3, i + 3);
            cell.setValue(subHeaders2[i]);
            cellSets.add(cell);
        }

        return cellSets;
    }

    @Override
    public void postProcess(CellSetHolder<CellSet> holder) {
        holder.setBold(true)
              .setBackgroundColor(IndexedColors.LIGHT_BLUE.getIndex())
              .setAlignment(HorizontalAlignment.CENTER)
              .mergeRegion()
              .setCellValue(holder.getCellSet().getValue());
    }
}

Web 集成​

文件上传​

import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import com.jeeapp.excel.RowReader;
import com.jeeapp.excel.mapping.AnnotationBasedRowMapper;
import org.apache.poi.ss.usermodel.Row;
import java.io.InputStream;
import java.util.List;
import java.util.ArrayList;

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

    @PostMapping("/upload")
    public List<User> uploadExcel(@RequestParam("file") MultipartFile file) 
            throws Exception {
        List<User> users = new ArrayList<>();

        try (InputStream inputStream = file.getInputStream();
             RowReader reader = RowReader.open(inputStream)) {

            AnnotationBasedRowMapper<User> mapper = 
                new AnnotationBasedRowMapper<>(User.class);

            for (Row row : reader) {
                if (row.getRowNum() == 0) {
                    continue; // 跳过标题行
                }
                User user = mapper.mapRow(row);
                if (user != null) {
                    users.add(user);
                }
            }
        }

        return users;
    }
}

文件下载​

import org.springframework.web.bind.annotation.*;
import org.springframework.http.HttpHeaders;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import com.jeeapp.excel.WorkbookBuilder;
import com.jeeapp.excel.cellset.FieldCellSetProvider;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.ByteArrayOutputStream;
import java.util.List;
import java.util.stream.Collectors;

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

    @GetMapping("/download")
    public ResponseEntity<byte[]> downloadExcel(@RequestParam List<String> names) 
            throws Exception {
        // 创建用户数据
        List<User> users = names.stream()
            .map(name -> {
                User user = new User();
                user.setName(name);
                user.setAge(25);
                user.setEmail(name + "@example.com");
                user.setDepartment("技术部");
                return user;
            })
            .collect(Collectors.toList());

        // 创建 Excel
        XSSFWorkbook workbook = new XSSFWorkbook();
        WorkbookBuilder.builder(workbook)
            .createSheet("用户列表")
                .populate(FieldCellSetProvider.create(User.class, users))
            .build();

        // 输出到字节数组
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        workbook.write(outputStream);
        workbook.close();

        byte[] excelBytes = outputStream.toByteArray();

        HttpHeaders headers = new HttpHeaders();
        headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
        headers.setContentDispositionFormData("attachment", "users.xlsx");

        return ResponseEntity.ok()
            .headers(headers)
            .body(excelBytes);
    }
}

API 参考​

主要类​

类名功能说明
WorkbookBuilder工作簿构建器用于创建和配置 Excel 工作簿
SheetBuilder工作表构建器用于创建和配置工作表
RowBuilder行构建器用于创建和配置行
CellBuilder单元格构建器用于创建和配置单元格
RowReader行读取器用于流式读取 Excel 文件
AnnotationBasedRowMapper注解映射器基于注解的对象映射
CellStyleBuilder样式构建器用于创建单元格样式
FieldCellSetProvider字段单元格集合提供者基于 Java 对象字段的 Provider
JsonCellSetProviderJSON 单元格集合提供者基于 JSON 数据的 Provider

注解​

注解名功能说明
@FieldColumn字段列映射指定字段对应的 Excel 列
@FieldIgnore忽略字段忽略不需要映射的字段
@HeaderStyle标题样式设置列标题的样式
@RecordStyle记录样式设置数据行的样式
@DefaultStyle默认样式设置默认样式
@HeaderComment标题注释设置列标题的注释
@HeaderFont标题字体设置列标题的字体
@RecordFont记录字体设置数据行的字体

完整示例​

完整应用示例​

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.http.ResponseEntity;
import org.springframework.http.HttpHeaders;
import org.springframework.http.MediaType;
import com.jeeapp.excel.RowReader;
import com.jeeapp.excel.WorkbookBuilder;
import com.jeeapp.excel.cellset.FieldCellSetProvider;
import com.jeeapp.excel.mapping.AnnotationBasedRowMapper;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.util.List;
import java.util.ArrayList;
import java.util.stream.Collectors;

@SpringBootApplication
public class ExcelDemoApplication {
    public static void main(String[] args) {
        SpringApplication.run(ExcelDemoApplication.class, args);
    }
}

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

    @PostMapping("/upload")
    public List<User> uploadExcel(@RequestParam("file") MultipartFile file) 
            throws Exception {
        List<User> users = new ArrayList<>();

        try (InputStream inputStream = file.getInputStream();
             RowReader reader = RowReader.open(inputStream)) {

            AnnotationBasedRowMapper<User> mapper = 
                new AnnotationBasedRowMapper<>(User.class);

            for (Row row : reader) {
                if (row.getRowNum() == 0) {
                    continue; // 跳过标题行
                }
                User user = mapper.mapRow(row);
                if (user != null) {
                    users.add(user);
                }
            }
        }

        return users;
    }

    @GetMapping("/download")
    public ResponseEntity<byte[]> downloadExcel(@RequestParam List<String> names) 
            throws Exception {
        // 创建用户数据
        List<User> users = names.stream()
            .map(name -> {
                User user = new User();
                user.setName(name);
                user.setAge(25);
                user.setEmail(name + "@example.com");
                user.setDepartment("技术部");
                return user;
            })
            .collect(Collectors.toList());

        // 创建 Excel
        XSSFWorkbook workbook = new XSSFWorkbook();
        WorkbookBuilder.builder(workbook)
            .createSheet("用户列表")
                .populate(FieldCellSetProvider.create(User.class, users))
            .build();

        // 输出到字节数组
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        workbook.write(outputStream);
        workbook.close();

        byte[] excelBytes = outputStream.toByteArray();

        HttpHeaders headers = new HttpHeaders();
        headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
        headers.setContentDispositionFormData("attachment", "users.xlsx");

        return ResponseEntity.ok()
            .headers(headers)
            .body(excelBytes);
    }
}

API 迁移指南​

从 createCells() 迁移到 populate()​

在 v0.2.2 版本中，我们引入了新的 populate() 方法，它比 createCells() 更语义化。createCells() 方法已被标记为 @Deprecated。

迁移步骤：

// 旧代码（仍然可用，但已弃用）
sheetBuilder.createCells(FieldCellSetProvider.create(Employee.class, employees));

// 新代码（推荐）
sheetBuilder.populate(FieldCellSetProvider.create(Employee.class, employees));

为什么改用 populate()？

  • ✅ 更清晰的语义：populate 准确表达了"用数据填充工作表"的意图
  • ✅ 更好的一致性：与 createCell、createRow 等低级 API 形成清晰的层次区分
  • ✅ 更符合 Java 社区习惯：populate 在 Java 社区中常用于数据填充场景

相关资源​