SuperAPI AI Directory
Partner with us中文AI newsFavorites
Skillseasyexcel-fill

easyexcel-fill

Office & productivity

View repository

Updated 10-15 21:48

About this skill

EasyExcel Template Filling (Fill)

Official documentation:https://easyexcel.opensource.alibaba.com/docs/current/quickstart/fill Official examples:https://github.com/alibaba/easyexcel/blob/master/easyexcel-test/src/test/java/com/alibaba/easyexcel/test/demo/fill/FillTest.java Current version:com.alibaba:easyexcel:4.0.3(Apache-2.0, repository archived, bug fixes only)

This skill focuses on the "fill by pre-made template" scenario. When the user provides an .xlsx template with styles already drawn and asks to fill in data from a database/API (pay slips, contracts, invoices, reports, statistics sheets, etc.), this skill should be used.

Capability Boundaries

✅ Strengths

  1. Simple object filling (Map / single DTO)
  2. List filling (template {.} syntax, one-directional vertical)
  3. Complex filling (content after list → forceNewRow=true）
  4. Complex filling with large data volume (list must be placed in the last row, subsequent statistics rows appended with write )
  5. Horizontal multi-column combined filling (WriteDirectionEnum.HORIZONTAL + FillWrapper）
  6. Template escape character handling (\{ \}）
  7. Spring Boot integration (template files can come from resources/ or OSS)

⚠️ Limitations

  1. Only supports .xlsx(07 version), 03 version (.xls) does not support complex/large data volume filling
  2. forceNewRow=true is fully resident in memory, use with caution for large data volumes
  3. withTemplate templates are fully loaded into memory; excessively large template files (>tens of MB) will cause OOM
  4. When filling a list with a Map, all list keys must be included, otherwise an NPE is thrown

❌ Out of Scope (scenarios where this skill should not be used; please use other skills instead)

  1. Template-based programmatic Excel generation(no pre-made template) → not applicable tothis skill, use easyexcel-write
  2. Reading Excel data → not applicable tothis skill, use easyexcel-read
  3. CSV file processing → not applicable tothis skill, use commons-csv / OpenCSV on your own
  4. Word/PPT → not applicable tothis skill, use Apache POI / Apache POI-XML

Data Privacy

This skill does not collect, store, or transmit any user data. All code examples are for local development reference only.

When to use this skill

  • The user says "generate Excel by template", "fill Excel template", "export pay slips/contracts/invoices"
  • The user provides an .xlsx template file and asks to write data into variable placeholders
  • The report has a fixed format and needs to preserve styles/merged cells/functions/charts
  • There is a business requirement to "first fill the list, then append total rows/statistics rows"

Quick Start

Typical invocation methods (choose 1 of 4, in increasing order of file size)：

// 方案 A：最简单的对象填充（since 2.1.1）
String fileName = "output_simple.xlsx";
EasyExcel.write(fileName).withTemplate("templates/simple.xlsx")
    .sheet().doFill(fillData);

// 方案 B：列表填充（一次放入内存）
EasyExcel.write(fileName).withTemplate("templates/list.xlsx")
    .sheet().doFill(list);

// 方案 C：列表分次填充（文件缓存，省内存）
try (ExcelWriter excelWriter = EasyExcel.write(fileName)
        .withTemplate("templates/list.xlsx").build()) {
    WriteSheet writeSheet = EasyExcel.writerSheet().build();
    excelWriter.fill(list, writeSheet);  // 多次 fill，自动文件缓存
    excelWriter.fill(list, writeSheet);
}

// 方案 D：先 fill 列表 + 后 write 追加合计行（推荐：复杂报表 + 大数据量）
try (ExcelWriter excelWriter = EasyExcel.write(fileName)
        .withTemplate("templates/complex.xlsx").build()) {
    WriteSheet writeSheet = EasyExcel.writerSheet().build();
    excelWriter.fill(list, writeSheet);          // 1. 填列表
    excelWriter.fill(headerMap, writeSheet);     // 2. 填头部变量
    excelWriter.write(totalListList, writeSheet);// 3. 手动追加合计行
}

Workflow

Step 1. Confirm the scenario — Has the user provided an .xlsx template? Does the template contain {var} {.} {prefix.} placeholders? Step 2. Assess the data volume — < 10,000 rows: options A/B; > 10,000 rows: options C/D (list must be placed in the last row) Step 3. Choose the filling mode — single object / list / complex / horizontal multi-column Step 4. Construct the data — Map / DTO / List; when filling a list with a Map, all keys must be included Step 5. Execute fill — try-with-resources close ExcelWriter Step 6. Verify output — open the generated .xlsx Check whether placeholders were replaced and whether there is any residual {.} {}

Critical: Template syntax quick reference

Template placeholder Meaning Java data structure
{name} {number} {date} Single-value placeholder Map.put("name", value) or DTO field
{.} List placeholder (vertical by default) List<DTO> passed as the first argument of fill as a whole
{data1.} {data2.} Prefixed list (to distinguish multiple lists) new FillWrapper("data1", list)
\{ \} Escape: when the template really contains the { } character ——

Official text: "{} ordinary variable, {.} list variable. If there are already '{' and '}' special characters, replace them with '\{' and '\}'. The {. prefix can distinguish different lists."

Critical: Complete examples of the 4 fill scenarios

Scenario 1: Simple object fill

DTO：

@Getter
@Setter
@EqualsAndHashCode
public class FillData {
    private String name;
    private double number;
    private Date date;
}

Template（templates/simple.xlsx): the cell contains {name} {number} {date}

Code：

@Test
public void simpleFill() {
    String templateFileName = "templates/simple.xlsx";
    String fileName = "output_simpleFill_" + System.currentTimeMillis() + ".xlsx";

    // 方案 1：根据对象填充
    FillData fillData = new FillData();
    fillData.setName("张三");
    fillData.setNumber(5.2);
    EasyExcel.write(fileName).withTemplate(templateFileName).sheet().doFill(fillData);

    // 方案 2：根据 Map 填充
    Map<String, Object> map = new HashMap<>();
    map.put("name", "张三");
    map.put("number", 5.2);
    EasyExcel.write(fileName).withTemplate(templateFileName).sheet().doFill(map);
}

Scenario 2: List fill (template {.} placeholder occupies one row)

Template（templates/list.xlsx): a row of cells contains {.} indicates the start of the list

Code：

@Test
public void listFill() {
    String templateFileName = "templates/list.xlsx";
    String fileName = "output_listFill_" + System.currentTimeMillis() + ".xlsx";

    // 方案 1：一次性放入内存
    EasyExcel.write(fileName).withTemplate(templateFileName).sheet().doFill(data());

    // 方案 2：分次填充（文件缓存，省内存，推荐）
    try (ExcelWriter excelWriter = EasyExcel.write(fileName)
            .withTemplate(templateFileName).build()) {
        WriteSheet writeSheet = EasyExcel.writerSheet().build();
        excelWriter.fill(data(), writeSheet);
        excelWriter.fill(data(), writeSheet);
    }
}

Official note: "Filling in multiple batches will use file caching"

Scenario 3: Complex fill (there are also total/summary rows after the list)

Scenario characteristics: in the template, {.} there is also non-list content below the list rows (such as "Total: xxx").

@Test
public void complexFill() {
    String templateFileName = "templates/complex.xlsx";
    String fileName = "output_complexFill_" + System.currentTimeMillis() + ".xlsx";

    try (ExcelWriter excelWriter = EasyExcel.write(fileName)
            .withTemplate(templateFileName).build()) {
        WriteSheet writeSheet = EasyExcel.writerSheet().build();
        // ⚠️ 关键：forceNewRow=true 才能让 list 后面再追加内容
        FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();

        excelWriter.fill(data(), fillConfig, writeSheet);
        excelWriter.fill(data(), fillConfig, writeSheet);

        Map<String, Object> map = new HashMap<>();
        map.put("date", "2019年10月9日13:28:28");
        map.put("total", 1000);
        excelWriter.fill(map, writeSheet);
    }
}

Official text: "In version 02, the template list is not the last row, and there is still data below, so you need to set forceNewRow=true but there is a drawback, which is that it will put all data into memory, use with caution" Official text: "If the data volume is large and the list is not the last row, refer to the next one (complexFillWithTable)"

Scenario 4: Complex fill with a large amount of data (list placed in the last row + write append)

Best-practice template design: delete the "total row" placeholder rows after the list in the template, let the list occupy the last row; use write to manually append subsequent "statistics rows".

@Test
public void complexFillWithTable() {
    String templateFileName = "templates/complexFillWithTable.xlsx";
    String fileName = "output_complexFillWithTable_" + System.currentTimeMillis() + ".xlsx";

    try (ExcelWriter excelWriter = EasyExcel.write(fileName)
            .withTemplate(templateFileName).build()) {
        WriteSheet writeSheet = EasyExcel.writerSheet().build();

        // 1. 写入 list 数据
        excelWriter.fill(data(), writeSheet);
        excelWriter.fill(data(), writeSheet);

        // 2. 写入 list 之前的变量
        Map<String, Object> map = new HashMap<>();
        map.put("date", "2019年10月9日13:28:28");
        excelWriter.fill(map, writeSheet);

        // 3. list 后面手动 write 统计行
        List<List<String>> totalListList = new ArrayList<>();
        List<String> totalList = new ArrayList<>();
        totalListList.add(totalList);
        totalList.add(null);
        totalList.add(null);
        totalList.add(null);
        totalList.add("统计:1000");
        excelWriter.write(totalListList, writeSheet);
    }
}

Official text: "Here it is write, don't confuse it with fill"

Scenario 5: Horizontal fill (multiple columns side by side horizontally)

@Test
public void horizontalFill() {
    String templateFileName = "templates/horizontal.xlsx";
    String fileName = "output_horizontalFill_" + System.currentTimeMillis() + ".xlsx";

    try (ExcelWriter excelWriter = EasyExcel.write(fileName)
            .withTemplate(templateFileName).build()) {
        WriteSheet writeSheet = EasyExcel.writerSheet().build();
        // 横向：列方向展开
        FillConfig fillConfig = FillConfig.builder()
            .direction(WriteDirectionEnum.HORIZONTAL).build();
        excelWriter.fill(data(), fillConfig, writeSheet);
        excelWriter.fill(data(), fillConfig, writeSheet);

        Map<String, Object> map = new HashMap<>();
        map.put("date", "2019年10月9日13:28:28");
        excelWriter.fill(map, writeSheet);
    }
}

Scenario 6: Multi-list combined fill (fill multiple lists at the same time)

Template: contains {data1.} {data2.} {data3.} three prefixed lists

@Test
public void compositeFill() {
    String templateFileName = "templates/composite.xlsx";
    String fileName = "output_compositeFill_" + System.currentTimeMillis() + ".xlsx";

    try (ExcelWriter excelWriter = EasyExcel.write(fileName)
            .withTemplate(templateFileName).build()) {
        WriteSheet writeSheet = EasyExcel.writerSheet().build();
        FillConfig fillConfig = FillConfig.builder()
            .direction(WriteDirectionEnum.HORIZONTAL).build();

        // ⚠️ 关键：多个 list 必须用 FillWrapper 包裹，prefix 必须与模板占位符前缀一致
        excelWriter.fill(new FillWrapper("data1", data()), fillConfig, writeSheet);
        excelWriter.fill(new FillWrapper("data2", data()), writeSheet);  // data2 纵向
        excelWriter.fill(new FillWrapper("data3", data()), writeSheet);

        Map<String, Object> map = new HashMap<>();
        map.put("date", new Date());
        excelWriter.fill(map, writeSheet);
    }
}

Official text: "Multiple lists must be wrapped with FillWrapper ;{前缀.} prefixes can distinguish different lists; different lists can specify different directions (HORIZONTAL/VERTICAL)"

Critical: Spring Boot integration

1) Place the template in resources/templates/

@Service
public class SalaryFillService {

    public void fillSalary(String employeeId, OutputStream out) {
        InputStream templateIn = getClass()
            .getResourceAsStream("/templates/salary.xlsx");
        SalaryDTO data = salaryMapper.findByEmployee(employeeId);

        // 模板以流方式加载，避免临时文件
        try (ExcelWriter writer = EasyExcel.write(out)
                .withTemplate(templateIn).build()) {
            WriteSheet sheet = EasyExcel.writerSheet().build();
            writer.fill(data, sheet);
        }
    }
}

2) Controller returns

@GetMapping("/export/salary/{employeeId}")
public void exportSalary(@PathVariable String employeeId,
                         HttpServletResponse response) throws IOException {
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    response.setCharacterEncoding("utf-8");
    String fileName = URLEncoder.encode("工资条", "UTF-8").replaceAll("\\+", "%20");
    response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");

    salaryFillService.fillSalary(employeeId, response.getOutputStream());
}

3) Complex scenario: batch packaging of pay slips

public void batchExportSalary(List<String> employeeIds, OutputStream zipOut) {
    try (ZipOutputStream zos = new ZipOutputStream(zipOut);
         ExcelWriter writer = EasyExcel.write(/* unused */).build()) {  // 实际逐个写入临时文件
        for (String id : employeeIds) {
            ByteArrayOutputStream baos = new ByteArrayOutputStream();
            fillSalary(id, baos);
            ZipEntry entry = new ZipEntry("salary_" + id + ".xlsx");
            zos.putNextEntry(entry);
            zos.write(baos.toByteArray());
            zos.closeEntry();
        }
    }
}

Critical: Business template design specifications

Business scenario Recommended template design Key code
Pay slip Single object {name} {base} {bonus} {total} Simple fill solution 1
Employee contract Header {company} {name} {id} + list {items.} Simple + list
Statement Header {date} {account} + List {details.} + Footer "Total" complexFillWithTable (fill the list first, then write the total row)
Financial Report Horizontal multiple columns {months.} + Vertical {rows.} compositeFill (multiple FillWrapper)
Invoice Single object + functions =SUM() Simple fill (formulas preset by the template)
Product List List {products.} + Image List + Image (images need to be read as byte[])
Summary Table (by month) Header {year} + List {data1.}(horizontal multi-column expansion) horizontalFill

Gotchas (common mistakes)

# Mistake Correct approach
1 Map fill list misses some keys When filling a Map, all list keys must be put (value can be null)
2 Template has { } character not escaped Use \{ \} to escape
3 Complex fill forgets forceNewRow=true Must be set when there is content below the list
4 forceNewRow=true used on millions of records Switch to complexFillWithTable (put the list on the last row)
5 03 version (.xls) for complex fill Switch to 07 version (.xlsx）
6 Appending a total row after the list uses fill Use write("This is write, don't confuse it with fill")
7 Horizontal multiple columns forgot FillWrapper Multiple lists must use new FillWrapper("prefix", list)
8 Horizontal multiple columns direction not configured Must FillConfig.builder().direction(HORIZONTAL)
9 Template file withTemplate(InputStream) not closed after use Template streams are cached, close with try-with-resources
10 When closing ExcelWriter missing finish() Use try-with-resources so that ExcelWriter.close() triggers finish
11 Template file > tens of MB Switch to multiple small templates filled in batches
12 Template variable written incorrectly (case / missing { }） Template variable names must exactly match the Map key / DTO field

Key Classes / Methods

Type Name Purpose
Entry EasyExcel.write(path).withTemplate(template) Specify the template path or stream
Entry EasyExcel.write(OutputStream).withTemplate(stream) Web output stream scenario
Core ExcelWriter Container for multiple fills, requires try-with-resources
Core WriteSheet Single sheet configuration
Config FillConfig.builder().forceNewRow(true) Complex fill (content after the list)
Config FillConfig.builder().direction(HORIZONTAL) Horizontal fill
Wrapper FillWrapper("prefix", list) Multi-list grouping, prefix matches template {prefix.}
Utility EasyExcel.writerSheet().build() Create a default WriteSheet
Utility EasyExcel.writerSheet(0, "sheet名").build() Specify sheetNo and name

References