Fill

This section explains how to use Fesod to fill data into files.

Placeholder Syntax

A template marks the cells to fill with {} placeholders. What sits inside the braces decides how the cell is filled:

Placeholder	Meaning	Filled by
{name}	a single variable	doFill(object), doFill(map)
{.name}	the name property of every item of a list	doFill(list), fill(list, ...)
{data1.name}	the same, for the list named data1	fill(new FillWrapper("data1", list), ...)
\{name\}	escaped with \, never parsed	nothing

The leading . is what makes a cell repeat once per item - downwards by default, or across the columns with FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL). The text before the . names which list the items come from, so one template can hold several lists side by side.

A cell may mix several placeholders with ordinary text, as in {name} is {number} years old this year. A placeholder the fill does not supply is cleared rather than left in the sheet: filling a list against {name}, or an object against {.name}, empties the cell and keeps only the text around it.

Escaping stops the braces from being parsed, but the \ characters are only removed when the same cell also holds a real placeholder. In a cell that contains nothing else, \{name\} is written out exactly as typed, backslashes included.

Simple Fill

Overview

Fill data into spreadsheet based on a template file using objects or Map.

POJO Class

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

Data List

private List<FillData> data() {
    List<FillData> list = ListUtils.newArrayList();
    for (int i = 0; i < 10; i++) {
        FillData fillData = new FillData();
        fillData.setName("John Doe" + i);
        fillData.setNumber(5.2);
        fillData.setDate(new Date());
        list.add(fillData);
    }
    return list;
}

Code Example

@Test
public void simpleFill() {
    String templateFileName = "path/to/simple.xlsx";

    // Approach 1: Fill based on object
    FillData fillData = new FillData();
    fillData.setName("John Doe");
    fillData.setNumber(5.2);
    FesodSheet.write("simpleFill.xlsx")
            .withTemplate(templateFileName)
            .sheet()
            .doFill(fillData);

    // Approach 2: Fill based on Map
    Map<String, Object> map = new HashMap<>();
    map.put("name", "John Doe");
    map.put("number", 5.2);
    FesodSheet.write("simpleFillMap.xlsx")
            .withTemplate(templateFileName)
            .sheet()
            .doFill(map);
}

Template

	A	B	C	D	E
1	Name	Number	Complex	Ignored	Empty
2	{name}	{number}	{name} is {number} years old	\{name\} ignored, {name}	Empty{.empty}

Result

	A	B	C	D	E
1	Name	Number	Complex	Ignored	Empty
2	John Doe	5.2	John Doe is 5.2 years old	{name} ignored, John Doe	Empty

---

Fill List

Overview

Fill multiple data items into a template list, supporting in-memory batch operations and file cache batch filling.

Code Example

@Test
public void listFill() {
    String templateFileName = "path/to/list.xlsx";

    // Approach 1: Fill all data at once
    FesodSheet.write("listFill.xlsx")
            .withTemplate(templateFileName)
            .sheet()
            .doFill(data());

    // Approach 2: Batch filling
    try (ExcelWriter writer = FesodSheet.write("listFillBatch.xlsx").withTemplate(templateFileName).build()) {
        WriteSheet writeSheet = FesodSheet.writerSheet().build();
        writer.fill(data(), writeSheet);
        writer.fill(data(), writeSheet);
    }
}

Template

	A	B	C
1	Name	Number	Date
2	{.name}	{.number}	{.date}

Result

Approach 1:

	A	B	C
1	Name	Number	Date
2	John Doe0	5.2	2026-07-31 19:55:44
3	John Doe1	5.2	2026-07-31 19:55:44
4	John Doe2	5.2	2026-07-31 19:55:44
⋮	…	…	…
11	John Doe9	5.2	2026-07-31 19:55:44

Approach 2:

	A	B	C
1	Name	Number	Date
2	John Doe0	5.2	2026-07-31 19:55:44
⋮	…	…	…
11	John Doe9	5.2	2026-07-31 19:55:44
12	John Doe0	5.2	2026-07-31 19:55:44
⋮	…	…	…
21	John Doe9	5.2	2026-07-31 19:55:44

---

Complex Fill

Overview

Fill various data types in a template, including lists and regular variables.

Code Example

@Test
public void complexFill() {
    String templateFileName = "path/to/complex.xlsx";

    try (ExcelWriter writer = FesodSheet.write("complexFill.xlsx").withTemplate(templateFileName).build()) {
        WriteSheet writeSheet = FesodSheet.writerSheet().build();

        // Fill list data, with forceNewRow enabled
        FillConfig config = FillConfig.builder().forceNewRow(true).build();
        writer.fill(data(), config, writeSheet);

        // Fill regular variables
        Map<String, Object> map = new HashMap<>();
        map.put("date", "November 20, 2024");
        map.put("total", 1000);
        writer.fill(map, writeSheet);
    }
}

Template

	A	B	C	D
1			Statistics
2			Time: {date}
3	Name	Number	Name	Number
4	{.name}	{.number}	{.name}	{.number}
5				Total:{total}

Result

	A	B	C	D
1			Statistics
2			Time: November 20, 2024
3	Name	Number	Name	Number
4	John Doe0	5.2	John Doe0	5.2
5	John Doe1	5.2	John Doe1	5.2
6	John Doe2	5.2	John Doe2	5.2
⋮	…	…	…	…
13	John Doe9	5.2	John Doe9	5.2
14				Total:1000

---

Complex Fill with Large Data

Overview

Optimize performance for filling large data, ensuring the template list is at the last row, and subsequent data is filled using WriteTable.

Code Example

@Test
public void complexFillWithTable() {
    String templateFileName = "path/to/complexFillWithTable.xlsx";

    try (ExcelWriter writer = FesodSheet.write("complexFillWithTable.xlsx").withTemplate(templateFileName).build()) {
        WriteSheet writeSheet = FesodSheet.writerSheet().build();

        // Fill list data
        writer.fill(data(), writeSheet);

        // Fill list data
        Map<String, Object> map = new HashMap<>();
        map.put("date", "November 20, 2024");
        writer.fill(map, writeSheet);

        // Fill statistical information
        List<List<String>> totalList = new ArrayList<>();
        totalList.add(Arrays.asList(null, null, null, "Total: 1000"));
        writer.write(totalList, writeSheet);
    }
}

Template

	A	B	C	D
1			Statistics
2			Time: {date}
3	Name	Number	Name	Number
4	{.name}	{.number}	{.name}	{.number}

Result

The file comes out the same as Complex Fill above. What changes is how it gets there. The template stops at the list row instead of reserving a row for {total}, and the total is appended afterwards with writer.write(...), so the list can grow to any length without rows below it to push down.

	A	B	C	D
1			Statistics
2			Time: November 20, 2024
3	Name	Number	Name	Number
4	John Doe0	5.2	John Doe0	5.2
5	John Doe1	5.2	John Doe1	5.2
6	John Doe2	5.2	John Doe2	5.2
⋮	…	…	…	…
13	John Doe9	5.2	John Doe9	5.2
14				Total: 1000

---

Horizontal Fill

Overview

Fill list data horizontally, suitable for scenarios with dynamic column numbers.

Code Example

@Test
public void horizontalFill() {
    String templateFileName = "path/to/horizontal.xlsx";

    try (ExcelWriter writer = FesodSheet.write("horizontalFill.xlsx").withTemplate(templateFileName).build()) {
        WriteSheet writeSheet = FesodSheet.writerSheet().build();

        FillConfig config = FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).build();
        writer.fill(data(), config, writeSheet);

        Map<String, Object> map = new HashMap<>();
        map.put("date", "November 20, 2024");
        writer.fill(map, writeSheet);
    }
}

Template

	A	B	C
1	Statistics	Name	{.name}
2		Number	{.number}
3		Name	{.name}
4		Number	{.number}
5	Time: {date}

Result

	A	B	C	D	E	⋯	L
1	Statistics	Name	John Doe0	John Doe1	John Doe2	…	John Doe9
2		Number	5.2	5.2	5.2	…	5.2
3		Name	John Doe0	John Doe1	John Doe2	…	John Doe9
4		Number	5.2	5.2	5.2	…	5.2
5	Time: November 20, 2024

---

Fill Multiple Lists Together

Overview

Support filling multiple lists simultaneously, with prefixes to differentiate between lists.

Code Example

@Test
public void compositeFill() {
    String templateFileName = "path/to/composite.xlsx";

    try (ExcelWriter writer = FesodSheet.write("compositeFill.xlsx").withTemplate(templateFileName).build()) {
        WriteSheet writeSheet = FesodSheet.writerSheet().build();

        // Use FillWrapper for filling multiple lists
        // data1 is laid out across the columns, so it is filled horizontally
        FillConfig fillConfig = FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).build();
        writer.fill(new FillWrapper("data1", data()), fillConfig, writeSheet);
        writer.fill(new FillWrapper("data2", data()), writeSheet);
        writer.fill(new FillWrapper("data3", data()), writeSheet);

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

Template

	A	B	C	D	E
1	Statistics	Name	{data1.name}
2		Number	{data1.number}
3		Name	{data1.name}
4		Number	{data1.number}
5		Time: {date}
6
7
8	Name	Number
9	{data2.name}	{data2.number}
10				Name	Number
11				{data3.name}	{data3.number}

Result

• data1 is filled horizontally, so its ten items run across the columns from C to L on each of the four template rows.
• data2 and data3 are filled downwards instead, occupying A/B in rows 9 to 18 and D/E in rows 11 to 20.
• Calling fill again with the same list name appends to it, as in Fill List.

	A	B	C	D	E	⋯	L
1	Statistics	Name	John Doe0	John Doe1	John Doe2	…	John Doe9
2		Number	5.2	5.2	5.2	…	5.2
3		Name	John Doe0	John Doe1	John Doe2	…	John Doe9
4		Number	5.2	5.2	5.2	…	5.2
5		Time: 2026-07-31 20:04:59
6
7
8	Name	Number
9	John Doe0	5.2
10	John Doe1	5.2		Name	Number
11	John Doe2	5.2		John Doe0	5.2
12	John Doe3	5.2		John Doe1	5.2
⋮	…	…		…	…
18	John Doe9	5.2		John Doe7	5.2
19				John Doe8	5.2
20				John Doe9	5.2