Apache Fesod

https://fesod.apache.org/zh-cn/docs
Admin
Apache Fesod is a high-performance, memory-efficient Java library for reading and writing...
Tokens:21,069
Snippets:226
Trust Score:10
Update:5 months ago
Show doc for...
Tokens:
Raw
### Import GPG KEYS File

Source: https://fesod.apache.org/zh-cn/docs/download

Import the KEYS file into your GPG keyring to verify Apache release signatures. Ensure you have GPG installed.

```bash
gpg --import KEYS
```

--------------------------------

### Writing Data with Annotation Styles

Source: https://fesod.apache.org/zh-cn/docs/sheet/write/style

This example demonstrates how to write data to an Excel file using the POJO class with defined annotation styles.

```java
@Test
public void annotationStyleWrite() {
    String fileName = "annotationStyleWrite" + System.currentTimeMillis() + ".xlsx";

    FesodSheet.write(fileName, DemoStyleData.class)
            .sheet()
            .doWrite(data());
}
```

--------------------------------

### Generate List of Demo Data

Source: https://fesod.apache.org/zh-cn/docs/sheet/write/simple

Create a method to generate a list of DemoData objects for writing to the spreadsheet. This example generates 10 entries.

```java
private List<DemoData> data() {
    List<DemoData> list = ListUtils.newArrayList();
    for (int i = 0; i < 10; i++) {
        DemoData data = new DemoData();
        data.setString("String" + i);
        data.setDate(new Date());
        data.setDoubleData(0.56);
        list.add(data);
    }
    return list;
}
```

--------------------------------

### Writing Data with Custom Cell Style Handler

Source: https://fesod.apache.org/zh-cn/docs/sheet/write/style

This example shows how to register and use a custom `CellWriteHandler` to apply unique styles to cells during the writing process.

```java
@Test
public void customCellStyleWrite() {
    String fileName = "customCellStyleWrite" + System.currentTimeMillis() + ".xlsx";

    FesodSheet.write(fileName, DemoData.class)
            .registerWriteHandler(new CustomCellStyleWriteHandler())
            .sheet("自定义样式")
            .doWrite(data());
}
```

--------------------------------

### Writing Excel with Image Data

Source: https://fesod.apache.org/zh-cn/docs/sheet/write/image

Demonstrates writing an Excel file with image data. This example shows how to populate a list of ImageDemoData objects with image files, byte arrays, and URLs, then export them.

```java
@Test
public void imageWrite() throws Exception {
    String fileName = "imageWrite" + System.currentTimeMillis() + ".xlsx";
    String imagePath = "path/to/image.jpg";

    List<ImageDemoData> list = new ArrayList<>();
    ImageDemoData data = new ImageDemoData();
    data.setFile(new File(imagePath));
    data.setByteArray(Files.readAllBytes(Paths.get(imagePath)));
    data.setUrl(new URL("https://example.com/image.jpg"));
    list.add(data);

    FesodSheet.write(fileName, ImageDemoData.class)
        .sheet()
        .doWrite(list);
}

```

--------------------------------

### Write Data to Excel File - Java

Source: https://fesod.apache.org/zh-cn/docs/quickstart/simple-example

Create an Excel file and write data to it using a defined data class. This example demonstrates how to populate a sheet with structured data.

```java
// 示例数据类  
public class DemoData {  
    @ExcelProperty("字符串标题")  
    private String string;  
    @ExcelProperty("日期标题")  
    private Date date;  
    @ExcelProperty("数字标题")  
    private Double doubleData;  
    @ExcelIgnore  
    private String ignore;  
}  
  
// 填充要写入的数据  
private static List<DemoData> data() {  
    List<DemoData> list = new ArrayList<>();  
    for (int i = 0; i < 10; i++) {  
        DemoData data = new DemoData();  
        data.setString("字符串" + i);  
        data.setDate(new Date());  
        data.setDoubleData(0.56);  
        list.add(data);  
    }  
    return list;  
}  
  
public static void main(String[] args) {  
    String fileName = "demo.xlsx";  
    // 创建一个名为“模板”的 sheet 页，并写入数据  
    FesodSheet.write(fileName, DemoData.class).sheet("模板").doWrite(data());  
}  


```

--------------------------------

### Custom String Converter Implementation

Source: https://fesod.apache.org/zh-cn/docs/sheet/write/converter

Implement the Converter interface to define custom logic for converting String data types between Java and Excel. This example shows how to prepend '自定义：' to string values during both reading and writing.

```java
public class CustomStringStringConverter implements Converter<String> {
    @Override
    public Class<?> supportJavaTypeKey() {
        return String.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    @Override
    public String convertToJavaData(ReadConverterContext<?> context) {
        return "自定义：" + context.getReadCellData().getStringValue();
    }

    @Override
    public WriteCellData<?> convertToExcelData(WriteConverterContext<String> context) {
        return new WriteCellData<>("自定义：" + context.getValue());
    }
}
```

--------------------------------

### Implement Custom String Converter

Source: https://fesod.apache.org/zh-cn/docs/sheet/advanced/custom-converter

Implement the `Converter` interface to define custom logic for converting between Java `String` and Excel cell data. This example handles both reading and writing string values with a custom prefix.

```java
public class CustomStringStringConverter implements Converter<String> {
    @Override
    public Class<?> supportJavaTypeKey() {
        return String.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    @Override
    public String convertToJavaData(ReadConverterContext<?> context) {
        return "自定义：" + context.getReadCellData().getStringValue();
    }

    @Override
    public WriteCellData<?> convertToExcelData(WriteConverterContext<String> context) {
        return new WriteCellData<>("自定义：" + context.getValue());
    }
}

```

--------------------------------

### Custom String Converter Implementation

Source: https://fesod.apache.org/zh-cn/docs/sheet/read/converter

Implement the Converter interface to define custom logic for converting between Java data types and Excel cell data. This example shows a custom converter for String types.

```java
public class CustomStringStringConverter implements Converter<String> {
    @Override
    public Class<?> supportJavaTypeKey() {
        return String.class;
    }

    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }

    @Override
    public String convertToJavaData(ReadConverterContext<?> context) {
        return "自定义：" + context.getReadCellData().getStringValue();
    }

    @Override
    public WriteCellData<?> convertToExcelData(WriteConverterContext<String> context) {
        return new WriteCellData<>(context.getValue());
    }
}

```

--------------------------------

### POJO Class with Field-Level Converter

Source: https://fesod.apache.org/zh-cn/docs/sheet/write/converter

Annotate a POJO field with @ExcelProperty and specify a custom converter class to apply specific conversion logic to that field during Excel writing. This example applies CustomStringStringConverter to the 'string' field.

```java
@Getter
@Setter
@EqualsAndHashCode
public class ConverterData {
    @ExcelProperty(value = "字符串标题", converter = CustomStringStringConverter.class)
    private String string;

    @DateTimeFormat("yyyy年MM月dd日HH时mm分ss秒")
    @ExcelProperty("日期标题")
    private Date date;

    @NumberFormat("#.#%\%")
    @ExcelProperty("数字标题")
    private Double doubleData;
}
```

--------------------------------

### Registering and Using Custom Converter for Reading

Source: https://fesod.apache.org/zh-cn/docs/sheet/read/converter

Demonstrates how to register a custom converter and perform data reading using Fesod. Ensure the custom converter is registered before initiating the read operation.

```java
@Test
public void converterRead() {
    String fileName = "path/to/demo.xlsx";

    FesodSheet.read(fileName, ConverterData.class, new DemoDataListener())
            .registerConverter(new CustomStringStringConverter()) // 注册自定义转换器
            .sheet()
            .doRead();
}

```

--------------------------------

### Maven 依赖配置

Source: https://fesod.apache.org/zh-cn/docs/sheet/read/spring

在 pom.xml 文件中包含必要的 Fesod 和 Spring Boot Web 依赖项。

```xml
<dependency>
    <groupId>org.apache.fesod</groupId>
    <artifactId>fesod</artifactId>
    <version>版本号</version>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter</artifactId>
</dependency>
```

--------------------------------

### Spring 控制器生成电子表格下载

Source: https://fesod.apache.org/zh-cn/docs/sheet/write/spring

在 Spring Boot 项目中，此控制器方法通过 HTTP 接口生成电子表格文件并提供下载。需要设置 ContentType、字符编码和 Content-disposition 响应头。

```java
@GetMapping("download")  
public void download(HttpServletResponse response) throws IOException {  
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");  
    response.setCharacterEncoding("utf-8");  
    String fileName = URLEncoder.encode("demo", "UTF-8").replaceAll("\\+", "%20");  
    response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");  

    FesodSheet.write(response.getOutputStream(), DemoData.class)  
            .sheet("Sheet1")  
            .doWrite(data());  
}  

```

--------------------------------

### 复杂数据类型填充

Source: https://fesod.apache.org/zh-cn/docs/sheet/fill

在模板中填充多种数据类型，包括列表和普通变量。可配置 `forceNewRow` 选项以确保列表数据在新行开始。

```java
@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();

        // 填充列表数据，开启 forceNewRow
        FillConfig config = FillConfig.builder().forceNewRow(true).build();
        writer.fill(data(), config, writeSheet);

        // 填充普通变量
        Map<String, Object> map = new HashMap<>();
        map.put("date", "2024年11月20日");
        map.put("total", 1000);
        writer.fill(map, writeSheet);
    }
}
```

--------------------------------

### Spring 控制器处理文件上传

Source: https://fesod.apache.org/zh-cn/docs/sheet/read/spring

创建一个 REST 控制器，使用 POST 请求接收上传的 Excel 文件，并使用 Fesod 进行解析。

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

    @PostMapping("/upload")
    public ResponseEntity<String> upload(@RequestParam("file") MultipartFile file) {
        if (file.isEmpty()) {
            return ResponseEntity.badRequest().body("请选择一个文件上传！");
        }

        try {
            FesodSheet.read(file.getInputStream(), UploadData.class, new UploadDataListener())
                    .sheet()
                    .doRead();
            return ResponseEntity.ok("文件上传并处理成功！");
        } catch (IOException e) {
            log.error("文件处理失败", e);
            return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR).body("文件处理失败！");
        }
    }
}
```

--------------------------------

### Writing Complex Headers

Source: https://fesod.apache.org/zh-cn/docs/sheet/write/head

Demonstrates writing a spreadsheet with complex, multi-level headers defined via a POJO class.

```java
@Test
public void complexHeadWrite() {
    String fileName = "complexHeadWrite" + System.currentTimeMillis() + ".xlsx";
    FesodSheet.write(fileName, ComplexHeadData.class)
            .sheet()
            .doWrite(data());
}
```

--------------------------------

### Writing Data with Custom Formatting

Source: https://fesod.apache.org/zh-cn/docs/sheet/write/format

Demonstrates how to write data to an Excel file using a POJO class that has custom formatting defined. The FesodSheet.write method is used to initiate the writing process.

```java
@Test  
public void converterWrite() {  
    String fileName = "converterWrite" + System.currentTimeMillis() + ".xlsx";  
    FesodSheet.write(fileName, ConverterData.class)  
        .sheet()  
        .doWrite(data());  
}

```

--------------------------------

### 读取 Excel 时处理异常

Source: https://fesod.apache.org/zh-cn/docs/sheet/read/exception

使用自定义的异常监听器来读取 Excel 文件，并在发生异常时进行处理。

```java
@Test
public void exceptionRead() {
    String fileName = "path/to/demo.xlsx";

    FesodSheet.read(fileName, ExceptionDemoData.class, new DemoExceptionListener())
            .sheet()
            .doRead();
}
```

--------------------------------

### Verify SHA-512 Checksum

Source: https://fesod.apache.org/zh-cn/docs/download

Verify the integrity of the downloaded source release by checking its SHA-512 checksum against the provided .sha512 file. This ensures the file was not corrupted during download.

```bash
shasum -a 512 -c apache-fesod-2.0.1-incubating-src.tar.gz.sha512
```

--------------------------------

### 数据监听器实现

Source: https://fesod.apache.org/zh-cn/docs/sheet/read/spring

创建一个监听器来处理每一行解析后的数据，并在所有数据读取完成后执行额外操作。

```java
@Slf4j
public class UploadDataListener extends AnalysisEventListener<UploadData> {
    private final List<UploadData> list = new ArrayList<>();

    @Override
    public void invoke(UploadData data, AnalysisContext context) {
        log.info("读取到一条数据: {}", JSON.toJSONString(data));
        list.add(data);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        log.info("所有数据读取完成！");
        // 在此处可以进行数据的存储操作，如保存到数据库
    }
}
```

--------------------------------

### Write Data Using a Template

Source: https://fesod.apache.org/zh-cn/docs/sheet/write/extra

Populate data into an existing Excel template file. This is useful for standardized output formats.

```java
@Test  
public void templateWrite() {  
    String templateFileName = "path/to/template.xlsx";  
    String fileName = "templateWrite" + System.currentTimeMillis() + ".xlsx";  
  
    FesodSheet.write(fileName, DemoData.class)  
        .withTemplate(templateFileName)  
        .sheet()  
        .doWrite(data());  
}  

```

--------------------------------

### Include Specific Columns for Writing

Source: https://fesod.apache.org/zh-cn/docs/sheet/write/pojo

Use `includeColumnFiledNames` to dynamically select columns to export, allowing you to only export specific columns.

```java
@Test
public void excludeOrIncludeWrite() {
    String fileName = "includeColumnFiledWrite" + System.currentTimeMillis() + ".xlsx";

    Set<String> includeColumns = Set.of("date");
    FesodSheet.write(fileName, DemoData.class)
        .includeColumnFiledNames(includeColumns)
        .sheet()
        .doWrite(data());
}

```

--------------------------------

### Write Spreadsheet Data with Fesod

Source: https://fesod.apache.org/zh-cn/docs

This snippet demonstrates writing data to an Excel file using a template sheet. Provide the file path, data class, sheet name, and the data to be written.

```java
FesodSheet.write("demo.xlsx", DemoData.class).sheet("模板").doWrite(data());  

```

--------------------------------

### Verify GPG Signature

Source: https://fesod.apache.org/zh-cn/docs/download

Verify the integrity and authenticity of the downloaded source release using its associated .asc signature file. This step is crucial before using any Apache release.

```bash
gpg --verify apache-fesod-2.0.1-incubating-src.tar.gz.asc apache-fesod-2.0.1-incubating-src.tar.gz
```

--------------------------------

### Batch Writing Large Excel Files with Compression

Source: https://fesod.apache.org/zh-cn/docs/sheet/advanced/large-file

Demonstrates writing a large Excel file in batches using `ExcelWriter` and enabling compression for temporary files. Use this for exporting datasets exceeding available memory. Ensure `SXSSFWorkbook` is used for compression to be effective.

```java
@Test
public void largeFileWrite() {
    String fileName = "largeFile" + System.currentTimeMillis() + ".xlsx";

    try (ExcelWriter excelWriter = FesodSheet.write(fileName, DemoData.class)
            .registerWriteHandler(new WorkbookWriteHandler() {
                @Override
                public void afterWorkbookCreate(WorkbookWriteHandlerContext context) {
                    Workbook workbook = context.getWriteWorkbookHolder().getWorkbook();
                    if (workbook instanceof SXSSFWorkbook) {
                        ((SXSSFWorkbook) workbook).setCompressTempFiles(true);
                    }
                }
            })
            .build()) {
        WriteSheet writeSheet = FesodSheet.writerSheet("模板").build();
        // 分批写入数据 — 每次 data() 调用返回一个批次
        for (int i = 0; i < 1000; i++) {
            excelWriter.write(data(), writeSheet);
        }
    }
}

```

--------------------------------

### Verify SHA-512 Checksum (Linux)

Source: https://fesod.apache.org/zh-cn/docs/download

An alternative command for verifying SHA-512 checksums on Linux systems. Use this if the `shasum` command is not available or preferred.

```bash
sha512sum -c apache-fesod-2.0.1-incubating-src.tar.gz.sha512
```

--------------------------------

### Configure CSV Format Directly

Source: https://fesod.apache.org/zh-cn/docs/sheet/write/csv

Allows direct construction of a CSVFormat object for advanced customization. This method is supported but not the most recommended approach.

```java
@Test
public void csvFormatDemo() {
    CSVFormat csvFormat = CSVFormat.DEFAULT.builder().setDelimiter(CsvConstant.AT).build();
    String csvFile = "path/to/your.csv";

    try (ExcelWriter excelWriter = FesodSheet.write(csvFile, DemoData.class).excelType(ExcelTypeEnum.CSV).build()) {
        WriteWorkbookHolder writeWorkbookHolder = excelWriter.writeContext().writeWorkbookHolder();
        Workbook workbook = writeWorkbookHolder.getWorkbook();
        // 判断是否为CsvWorkbook实例
        if (workbook instanceof CsvWorkbook) {
            CsvWorkbook csvWorkbook = (CsvWorkbook) workbook;
            csvWorkbook.setCsvFormat(csvFormat);
            writeWorkbookHolder.setWorkbook(csvWorkbook);
        }
        WriteSheet writeSheet = FesodSheet.writerSheet(0).build();
        excelWriter.write(data(), writeSheet);
    }
}
```

--------------------------------

### Dynamic Header Writing

Source: https://fesod.apache.org/zh-cn/docs/sheet/write/head

Enables real-time generation of dynamic table headers, suitable for scenarios where header content changes dynamically. Requires specifying the header structure as a list of lists.

```java
@Test
public void dynamicHeadWrite() {
    String fileName = "dynamicHeadWrite" + System.currentTimeMillis() + ".xlsx";

    List<List<String>> head = Arrays.asList(
            Collections.singletonList("动态字符串标题"),
            Collections.singletonList("动态数字标题"),
            Collections.singletonList("动态日期标题"));

    FesodSheet.write(fileName)
            .head(head)
            .sheet()
            .doWrite(data());
}
```

--------------------------------

### Configure CSVFormat Directly

Source: https://fesod.apache.org/zh-cn/docs/sheet/read/csv

Allows direct construction and application of a CSVFormat object for advanced customization. This method is supported but not the most recommended approach.

```Java
  
@Test  
public void csvFormatDemo() {  
  
    CSVFormat csvFormat = CSVFormat.DEFAULT.builder().setDelimiter(CsvConstant.AT).build();  
    String csvFile = "path/to/your.csv";  
  
    try (ExcelReader excelReader = FesodSheet.read(csvFile, DemoData.class, new DemoDataListener()).build()) {  
        ReadWorkbookHolder readWorkbookHolder = excelReader.analysisContext().readWorkbookHolder();  
        // 判断是否为CsvReadWorkbookHolder实例  
        if (readWorkbookHolder instanceof CsvReadWorkbookHolder) {  
            CsvReadWorkbookHolder csvReadWorkbookHolder = (CsvReadWorkbookHolder) readWorkbookHolder;  
            csvReadWorkbookHolder.setCsvFormat(csvFormat);  
        }  
  
        ReadSheet readSheet = FesodSheet.readSheet(0).build();  
        excelReader.read(readSheet);  
    }  
}  


```

--------------------------------

### Write Data with Merged Cells

Source: https://fesod.apache.org/zh-cn/docs/sheet/write/extra

Demonstrates writing data using both annotation-based and custom strategy-based cell merging.

```java
@Test  
public void mergeWrite() {  
    String fileName = "mergeWrite" + System.currentTimeMillis() + ".xlsx";  
  
    // 注解方式  
    FesodSheet.write(fileName, DemoMergeData.class)  
        .sheet("合并示例")  
        .doWrite(data());  
  
    // 自定义合并策略  
    FesodSheet.write(fileName, DemoData.class)  
        .registerWriteHandler(new CustomMergeStrategy())  
        .sheet("自定义合并")  
        .doWrite(data());  
}  

```

--------------------------------

### Simple Write using Lambda Expression

Source: https://fesod.apache.org/zh-cn/docs/sheet/write/simple

Perform a simple write operation using a lambda expression to provide the data. The filename includes a timestamp to ensure uniqueness.

```java
@Test
public void simpleWrite() {
    String fileName = "simpleWrite" + System.currentTimeMillis() + ".xlsx";

    FesodSheet.write(fileName, DemoData.class)
            .sheet("Sheet1")
            .doWrite(() -> data());
}
```

--------------------------------

### 列表填充 Excel

Source: https://fesod.apache.org/zh-cn/docs/sheet/fill

将列表数据填充到 Excel 模板中。支持一次性填充所有数据或分批进行填充以优化性能。

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

    // 方案1：一次性填充所有数据
    FesodSheet.write("listFill.xlsx")
            .withTemplate(templateFileName)
            .sheet()
            .doFill(data());

    // 方案2：分批填充
    try (ExcelWriter writer = FesodSheet.write("listFillBatch.xlsx").withTemplate(templateFileName).build()) {
        WriteSheet writeSheet = FesodSheet.writerSheet().build();
        writer.fill(data(), writeSheet);
        writer.fill(data(), writeSheet);
    }
}
```

--------------------------------

### Writing Data with Field-Level Converter

Source: https://fesod.apache.org/zh-cn/docs/sheet/write/converter

Demonstrates writing data to an Excel file using a POJO that has a field-level custom converter. The converter will be automatically applied to the specified field during the write operation.

```java
@Test
public void converterWrite() {
    String fileName = "converterWrite" + System.currentTimeMillis() + ".xlsx";
    FesodSheet.write(fileName, ConverterData.class)
        .sheet()
        .doWrite(data());
}
```

--------------------------------

### 大数据量填充与表格写入

Source: https://fesod.apache.org/zh-cn/docs/sheet/fill

优化大数据量填充性能，通过 `WriteTable` 填充统计信息。确保模板列表在最后一行，后续数据通过 `WriteTable` 填充。

```java
@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();

        // 填充列表数据
        writer.fill(data(), writeSheet);

        // 填充其他变量
        Map<String, Object> map = new HashMap<>();
        map.put("date", "2024年11月20日");
        writer.fill(map, writeSheet);

        // 填充统计信息
        List<List<String>> totalList = new ArrayList<>();
        totalList.add(Arrays.asList(null, null, null, "统计: 1000"));
        writer.write(totalList, writeSheet);
    }
}
```

--------------------------------

### Write Excel using POJO with Width and Height Annotations

Source: https://fesod.apache.org/zh-cn/docs/sheet/write/style

Writes an Excel file using a POJO class that has annotations for column width and row height. The Fesod library automatically applies these styles during the write operation.

```java
@Test
public void widthAndHeightWrite() {
    String fileName = "widthAndHeightWrite" + System.currentTimeMillis() + ".xlsx";

    FesodSheet.write(fileName, WidthAndHeightData.class)
            .sheet()
            .doWrite(data());
}

```

--------------------------------

### POJO Class for Demo Data

Source: https://fesod.apache.org/zh-cn/docs/sheet/write/simple

Define a POJO class that maps to the spreadsheet structure. Use annotations like @ExcelProperty for column mapping and @ExcelIgnore to exclude fields.

```java
@Getter
@Setter
@EqualsAndHashCode
public class DemoData {
    @ExcelProperty("字符串标题")
    private String string;
    @ExcelProperty("日期标题")
    private Date date;
    @ExcelProperty("数字标题")
    private Double doubleData;
    @ExcelIgnore
    private String ignore; // 忽略的字段
}
```

--------------------------------

### 简单填充 Excel

Source: https://fesod.apache.org/zh-cn/docs/sheet/fill

使用对象或 Map 将数据填充到 Excel 模板中。适用于填充单个数据记录。

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

    // 方案1：基于对象填充
    FillData fillData = new FillData();
    fillData.setName("张三");
    fillData.setNumber(5.2);
    FesodSheet.write("simpleFill.xlsx")
            .withTemplate(templateFileName)
            .sheet()
            .doFill(fillData);

    // 方案2：基于 Map 填充
    Map<String, Object> map = new HashMap<>();
    map.put("name", "张三");
    map.put("number", 5.2);
    FesodSheet.write("simpleFillMap.xlsx")
            .withTemplate(templateFileName)
            .sheet()
            .doFill(map);
}
```

--------------------------------

### Define Headers with POJO

Source: https://fesod.apache.org/zh-cn/docs/sheet/read/head

Use the `head()` method to specify a POJO class for header mapping. This simplifies header handling when the structure is known and consistent.

```java
@Test
public void headerPojoRead() {
    String fileName = "path/to/demo.xlsx";

    FesodSheet.read(fileName, new DemoDataListener())
            .head(DemoData.class)
            .sheet()
            .doRead();
}
```

--------------------------------

### Simple Write using Data List

Source: https://fesod.apache.org/zh-cn/docs/sheet/write/simple

Perform a simple write operation by directly passing a list of data objects. The filename includes a timestamp for uniqueness.

```java
@Test
public void simpleWrite() {
    String fileName = "simpleWrite" + System.currentTimeMillis() + ".xlsx";

    FesodSheet.write(fileName, DemoData.class)
            .sheet("Sheet1")
            .doWrite(data());
}
```

--------------------------------

### Add Gradle Dependency for Apache Fesod

Source: https://fesod.apache.org/zh-cn/docs/quickstart/guide

Add this line to your build.gradle file to include Apache Fesod as an implementation dependency in your Gradle project. Verify JDK compatibility.

```gradle
dependencies {
    implementation 'org.apache.fesod:fesod-sheet:2.0.1-incubating'
}
```

--------------------------------

### Define POJO for Column Width and Row Height

Source: https://fesod.apache.org/zh-cn/docs/sheet/write/style

Defines a POJO class with annotations to control default and specific column widths, as well as default row heights for the header and content rows.

```java
@Getter
@Setter
@EqualsAndHashCode
@ContentRowHeight(20)
@HeadRowHeight(30)
@ColumnWidth(25) // 默认列宽
public class WidthAndHeightData {
    @ExcelProperty("字符串标题")
    private String string;

    @ExcelProperty("日期标题")
    private Date date;

    @ColumnWidth(50) // 单独设置列宽
    @ExcelProperty("数字标题")
    private Double doubleData;
}

```

--------------------------------

### Write Using Tables - Java

Source: https://fesod.apache.org/zh-cn/docs/sheet/write/sheet

Supports writing data in chunks to multiple tables within a single sheet. This allows for structured data organization.

```java
@Test
public void tableWrite() {
    String fileName = "tableWrite" + System.currentTimeMillis() + ".xlsx";

    try (ExcelWriter excelWriter = FesodSheet.write(fileName, DemoData.class).build()) {
        WriteSheet writeSheet = FesodSheet.writerSheet("Table示例").build();
        WriteTable table1 = FesodSheet.writerTable(0).build();
        WriteTable table2 = FesodSheet.writerTable(1).build();

        excelWriter.write(data(), writeSheet, table1);
        excelWriter.write(data(), writeSheet, table2);
    }
}

```

--------------------------------

### 多列表组合填充

Source: https://fesod.apache.org/zh-cn/docs/sheet/fill

支持多个列表同时填充到 Excel 中，列表之间通过前缀区分。使用 `FillWrapper` 指定列表名称。

```java
@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();

        // 使用 FillWrapper 进行多列表填充
        writer.fill(new FillWrapper("data1", data()), 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);
    }
}
```

--------------------------------

### 自定义异常监听器

Source: https://fesod.apache.org/zh-cn/docs/sheet/read/exception

重写 onException 方法以处理 Excel 读取过程中发生的异常，包括数据转换错误。

```java
@Slf4j
public class DemoExceptionListener extends AnalysisEventListener<ExceptionDemoData> {
    @Override
    public void onException(Exception exception, AnalysisContext context) {
        log.error("失败: {}", exception.getMessage());
        if (exception instanceof ExcelDataConvertException) {
            ExcelDataConvertException ex = (ExcelDataConvertException) exception;
            log.error("第 {} 行, 第 {} 列异常, 数据: {}", ex.getRowIndex(), ex.getColumnIndex(), ex.getCellData());
        }
    }

    @Override
    public void invoke(ExceptionDemoData data, AnalysisContext context) {
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
    }
}
```

--------------------------------

### 横向填充列表数据

Source: https://fesod.apache.org/zh-cn/docs/sheet/fill

将列表数据横向填充到 Excel 中，适用于动态列数场景。可配置 `WriteDirectionEnum.HORIZONTAL`。

```java
@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", "2024年11月20日");
        writer.fill(map, writeSheet);
    }
}
```

--------------------------------

### Set Dropdown List in Spreadsheet

Source: https://fesod.apache.org/zh-cn/docs/sheet/write/extra

Implement the SheetWriteHandler to add a dropdown list to a specified cell range after a sheet is created. Requires importing necessary classes for data validation.

```java
public class DropdownWriteHandler implements SheetWriteHandler {
    @Override
    public void afterSheetCreate(SheetWriteHandlerContext context) {
        DataValidationHelper helper = context.getWriteSheetHolder().getSheet().getDataValidationHelper();
        CellRangeAddressList range = new CellRangeAddressList(1, 10, 0, 0); // 下拉框区域
        DataValidationConstraint constraint = helper.createExplicitListConstraint(new String[] {"选项1", "选项2"});
        DataValidation validation = helper.createValidation(constraint, range);
        context.getWriteSheetHolder().getSheet().addValidationData(validation);
    }
}

```

--------------------------------

### Read Spreadsheet Data Using POJO by Column Name or Index

Source: https://fesod.apache.org/zh-cn/docs/sheet/read/pojo

Use FesodSheet.read() with a POJO class and a listener to read data from a spreadsheet file. Ensure the file path and POJO class are correctly specified.

```java
@Test
public void indexOrNameRead() {
    String fileName = "path/to/demo.xlsx";

    FesodSheet.read(fileName, IndexOrNameData.class, new DemoDataListener())
            .sheet()
            .doRead();
}
```

--------------------------------

### Synchronous Read to POJO List

Source: https://fesod.apache.org/zh-cn/docs/sheet/read/simple

Read spreadsheet data directly into a `List<DemoData>` using `doReadSync()`. This method is suitable for smaller datasets that can fit into memory.

```java
@Test
public void synchronousReadToObjectList() {
    String fileName = "path/to/demo.xlsx";

    // POJO 列表
    List<DemoData> list = FesodSheet.read(fileName)
            .head(DemoData.class)
            .sheet()
            .doReadSync();

    for (DemoData data : list) {
        log.info("读取到的数据: {}", JSON.toJSONString(data));
    }
}

```

--------------------------------

### Register Global Converter for Reading

Source: https://fesod.apache.org/zh-cn/docs/sheet/advanced/custom-converter

Register a custom converter globally using the builder pattern before performing a read operation. This ensures that the custom conversion logic is applied when reading data from Excel.

```java
@Test
public void customConverterRead() {
    String fileName = "path/to/demo.xlsx";

    FesodSheet.read(fileName, DemoData.class, new DemoDataListener())
        .registerConverter(new CustomStringStringConverter())
        .sheet()
        .doRead();
}

```

--------------------------------

### POJO Class for Data Conversion

Source: https://fesod.apache.org/zh-cn/docs/sheet/read/converter

Define a POJO class with annotations for custom data conversion. Use @ExcelProperty with a custom converter, @DateTimeFormat for date parsing, and @NumberFormat for number formatting.

```java
@Getter
@Setter
@EqualsAndHashCode
public class ConverterData {
    @ExcelProperty(converter = CustomStringStringConverter.class)
    private String string;

    @DateTimeFormat("yyyy年MM月dd日HH时mm分ss秒")
    private String date;

    @NumberFormat("#.##%")
    private String doubleData;
}

```

--------------------------------

### Register Global Converter for Writing

Source: https://fesod.apache.org/zh-cn/docs/sheet/advanced/custom-converter

Register a custom converter globally using the builder pattern before performing a write operation. This allows the custom conversion logic to be applied to all matching fields.

```java
@Test
public void customConverterWrite() {
    String fileName = "customConverterWrite" + System.currentTimeMillis() + ".xlsx";

    FesodSheet.write(fileName, DemoData.class)
        .registerConverter(new CustomStringStringConverter())
        .sheet()
        .doWrite(data());
}

```

--------------------------------

### Write Password-Protected Excel File

Source: https://fesod.apache.org/zh-cn/docs/sheet/advanced/password

Encrypt an output Excel file with a password during the write operation. Ensure the password is provided correctly to secure the file.

```java
@Test
public void passwordWrite() {
    String fileName = "passwordWrite" + System.currentTimeMillis() + ".xlsx";

    FesodSheet.write(fileName)
        .password("your_password")
        .head(DemoData.class)
        .sheet("PasswordSheet")
        .doWrite(data());
}

```

--------------------------------

### POJO 类定义

Source: https://fesod.apache.org/zh-cn/docs/sheet/read/spring

定义一个 POJO 类来映射电子表格中的数据字段，包括字符串、日期和双精度浮点数。

```java
@Getter
@Setter
@ToString
public class UploadData {
    private String string;
    private Date date;
    private Double doubleData;
}
```

--------------------------------

### POJO Class with Custom Converters

Source: https://fesod.apache.org/zh-cn/docs/sheet/write/format

Define a POJO class with annotations to specify custom converters for different data types like strings, dates, and numbers. Use @ExcelProperty, @DateTimeFormat, and @NumberFormat for formatting.

```java
@Getter  
@Setter  
@EqualsAndHashCode  
public class ConverterData {  
    @ExcelProperty(value = "字符串标题", converter = CustomStringStringConverter.class)  
    private String string;  
  
    @DateTimeFormat("yyyy年MM月dd日HH时mm分ss秒")  
    @ExcelProperty("日期标题")  
    private Date date;  
  
    @NumberFormat("#.#%\"%")  
    @ExcelProperty("数字标题")  
    private Double doubleData;  
}

```

--------------------------------

### Write Data Without Creating Objects

Source: https://fesod.apache.org/zh-cn/docs/sheet/write/pojo

Write data directly using `List<List<String>>` for headers and data, eliminating the need to create entity classes. This method is useful for simple, dynamic data structures.

```java
@Test
public void noModelWrite() {
    String fileName = "noModelWrite" + System.currentTimeMillis() + ".xlsx";

    FesodSheet.write(fileName)
        .head(head()) // 动态头
        .sheet("无对象写入")
        .doWrite(dataList());
}

private List<List<String>> head() {
    return Arrays.asList(
        Collections.singletonList("字符串标题"),
        Collections.singletonList("数字标题"),
        Collections.singletonList("日期标题"));
}

private List<List<Object>> dataList() {
    List<List<Object>> list = new ArrayList<>();
    for (int i = 0; i < 10; i++) {
        list.add(Arrays.asList("字符串" + i, 0.56, new Date()));
    }
    return list;
}

```

--------------------------------

### Simple Write using ExcelWriter Object

Source: https://fesod.apache.org/zh-cn/docs/sheet/write/simple

Perform a simple write operation using an ExcelWriter object for more control. Ensure the writer is closed properly using a try-with-resources statement.

```java
@Test
public void simpleWrite() {
    String fileName = "simpleWrite" + System.currentTimeMillis() + ".xlsx";

    try (ExcelWriter excelWriter = FesodSheet.write(fileName, DemoData.class).build()) {
        WriteSheet writeSheet = FesodSheet.writerSheet("Sheet1").build();
        excelWriter.write(data(), writeSheet);
    }
}
```

--------------------------------

### Exclude Specific Columns for Writing

Source: https://fesod.apache.org/zh-cn/docs/sheet/write/pojo

Use `excludeColumnFieldNames` to dynamically select columns to export, allowing you to ignore specific columns.

```java
@Test
public void excludeOrIncludeWrite() {
    String fileName = "excludeColumnFieldWrite" + System.currentTimeMillis() + ".xlsx";

    Set<String> excludeColumns = Set.of("date");
    FesodSheet.write(fileName, DemoData.class)
            .excludeColumnFieldNames(excludeColumns)
            .sheet()
            .doWrite(data());
}

```

--------------------------------

### POJO 类定义

Source: https://fesod.apache.org/zh-cn/docs/sheet/fill

定义用于填充数据的 POJO 类。确保类具有相应的 getter 和 setter 方法。

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

--------------------------------

### Custom DemoDataListener Implementation

Source: https://fesod.apache.org/zh-cn/docs/sheet/read/simple

Implement a custom `ReadListener` for `DemoData` to define how each row is processed and what actions to take after all rows are analyzed. This listener is instantiated for each read operation.

```java
@Slf4j
public class DemoDataListener implements ReadListener<DemoData> {

    @Override
    public void invoke(DemoData data, AnalysisContext context) {
        log.info("读取到一条数据: {}", JSON.toJSONString(data));
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        log.info("所有数据读取完成！");
    }
}

```

--------------------------------

### POJO Class Annotations for Styling

Source: https://fesod.apache.org/zh-cn/docs/sheet/write/style

Use annotations directly on POJO class fields to define cell styles for headers and content. This is useful for setting default styles based on data types or specific column requirements.

```java
@Getter
@Setter
@EqualsAndHashCode
// 设置头部背景为红色
@HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 10)
// 设置头部字体大小为20
@HeadFontStyle(fontHeightInPoints = 20)
// 设置内容背景为绿色
@ContentStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 17)
// 设置内容字体大小为20
@ContentFontStyle(fontHeightInPoints = 20)
public class DemoStyleData {
    // 单独设置某列的头部和内容样式
    @HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 14)
    @HeadFontStyle(fontHeightInPoints = 30)
    @ContentStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 40)
    @ContentFontStyle(fontHeightInPoints = 30)
    @ExcelProperty("字符串标题")
    private String string;

    @ExcelProperty("日期标题")
    private Date date;

    @ExcelProperty("数字标题")
    private Double doubleData;
}
```

--------------------------------

### Write to the Same Sheet - Java

Source: https://fesod.apache.org/zh-cn/docs/sheet/write/sheet

Write data in batches to the same sheet. Ensure sheet names do not exceed 31 characters to avoid truncation.

```java
@Test
public void writeSingleSheet() {
    String fileName = "repeatedWrite" + System.currentTimeMillis() + ".xlsx";

    try (ExcelWriter excelWriter = FesodSheet.write(fileName, DemoData.class).build()) {
        WriteSheet writeSheet = FesodSheet.writerSheet("Sheet1").build();
        for (int i = 0; i < 5; i++) {
            excelWriter.write(data(), writeSheet);
        }
    }
}

```

--------------------------------

### Define Column Order with POJO Annotations

Source: https://fesod.apache.org/zh-cn/docs/sheet/write/pojo

Specify the column order in the Excel file by using the `index` attribute of the `@ExcelProperty` annotation within your POJO class.

```java
@Getter
@Setter
@EqualsAndHashCode
public class IndexData {
    @ExcelProperty(value = "字符串标题", index = 0)
    private String string;
    @ExcelProperty(value = "日期标题", index = 1)
    private Date date;
    @ExcelProperty(value = "数字标题", index = 3)
    private Double doubleData;
}

```

```java
@Test
public void indexWrite() {
    String fileName = "indexWrite" + System.currentTimeMillis() + ".xlsx";
    FesodSheet.write(fileName, IndexData.class)
        .sheet()
        .doWrite(data());
}

```

--------------------------------

### Read Spreadsheet with Custom Listener Class

Source: https://fesod.apache.org/zh-cn/docs/sheet/read/simple

Employ a custom listener class, such as `DemoDataListener`, for handling spreadsheet data. This promotes code organization and reusability for complex processing logic.

```java
@Test
public void simpleRead() {
    String fileName = "path/to/demo.xlsx";

    FesodSheet.read(fileName, DemoData.class, new DemoDataListener())
            .sheet()
            .doRead();
}

```

--------------------------------

### Add Maven Dependency for Apache Fesod

Source: https://fesod.apache.org/zh-cn/docs/quickstart/guide

Include this configuration in your pom.xml file to add Apache Fesod as a Maven dependency. Ensure your project uses a compatible JDK version.

```xml
<dependency>
    <groupId>org.apache.fesod</groupId>
    <artifactId>fesod-sheet</artifactId>
    <version>2.0.1-incubating</version>
</dependency>
```

--------------------------------

### Read Spreadsheet with Lambda Listener

Source: https://fesod.apache.org/zh-cn/docs/sheet/read/simple

Use a lambda expression for a concise data processing listener when reading spreadsheets. Ensure the listener is instantiated for each read operation.

```java
@Test
public void simpleRead() {
    String fileName = "path/to/demo.xlsx";

    FesodSheet.read(fileName, DemoData.class, new PageReadListener<>(dataList -> {
        for (DemoData demoData : dataList) {
            log.info("读取到一条数据: {}", JSON.toJSONString(demoData));
        }
    })).sheet().doRead();
}

```

--------------------------------

### POJO Class for Custom Formatting

Source: https://fesod.apache.org/zh-cn/docs/sheet/read/format

Define a POJO class with annotations to specify custom converters and formats for Excel cell values. Fields are String type, and Fesod applies conversions before setting values.

```java
@Getter
@Setter
@EqualsAndHashCode
public class ConverterData {
    @ExcelProperty(value = "字符串标题", converter = CustomStringStringConverter.class)
    private String string;

    @DateTimeFormat("yyyy-MM-dd HH:mm:ss")
    @ExcelProperty("日期标题")
    private String date;

    @NumberFormat("#.##%")
    @ExcelProperty("数字标题")
    private String doubleData;
}
```

--------------------------------

### Global Converter Registration for Writing

Source: https://fesod.apache.org/zh-cn/docs/sheet/write/converter

Register a custom converter at the builder level to apply it globally to all fields of a matching type during the Excel writing process. This is useful for consistent transformations across multiple fields without individual annotations.

```java
@Test
public void globalConverterWrite() {
    String fileName = "globalConverterWrite" + System.currentTimeMillis() + ".xlsx";
    FesodSheet.write(fileName, DemoData.class)
        .registerConverter(new CustomStringStringConverter())
        .sheet()
        .doWrite(data());
}
```

--------------------------------

### POJO Class for Reading by Column Name or Index

Source: https://fesod.apache.org/zh-cn/docs/sheet/read/pojo

Define a POJO class with @ExcelProperty annotations to map spreadsheet columns by name or index. This allows flexible interaction with dynamically generated files.

```java
@Getter
@Setter
@EqualsAndHashCode
public class IndexOrNameData {
    @ExcelProperty(index = 2)
    private Double doubleData;

    @ExcelProperty("字符串标题")
    private String string;

    @ExcelProperty("日期标题")
    private Date date;
}
```

--------------------------------

### 基于注解的单元格合并POJO类

Source: https://fesod.apache.org/zh-cn/docs/sheet/write/merge

使用 `@ContentLoopMerge(eachRow = N)` 注解字段，自动合并该列中每 N 行的单元格。此方法适用于固定模式的简单合并，并内置于数据类中。

```java
@Getter  
@Setter  
@EqualsAndHashCode  
public class DemoMergeData {
    @ContentLoopMerge(eachRow = 2)
    @ExcelProperty("字符串标题")
    private String string;

    @ExcelProperty("日期标题")
    private Date date;

    @ExcelProperty("数字标题")
    private Double doubleData;
}

```

--------------------------------

### Write Excel with Custom Cell Styles

Source: https://fesod.apache.org/zh-cn/docs/sheet/write/style

Applies a solid orange background to cells using a CellWriteHandler. This is useful for highlighting specific data or indicating status.

```java
@Test
public void poiStyleWrite() {
    String fileName = "poiStyleWrite" + System.currentTimeMillis() + ".xlsx";

    FesodSheet.write(fileName, DemoData.class)
            .registerWriteHandler(new CellWriteHandler() {
                @Override
                public void afterCellDispose(CellWriteHandlerContext context) {
                    if (BooleanUtils.isNotTrue(context.getHead())) {
                        Cell cell = context.getCell();
                        Workbook workbook = context.getWriteWorkbookHolder().getWorkbook();

                        // 创建并设置样式
                        CellStyle cellStyle = workbook.createCellStyle();
                        cellStyle.setFillForegroundColor(IndexedColors.LIGHT_ORANGE.getIndex());
                        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                        cell.setCellStyle(cellStyle);
                    }
                }
            })
            .sheet("POI样式")
            .doWrite(data());
}

```

--------------------------------

### Write to Multiple Sheets - Java

Source: https://fesod.apache.org/zh-cn/docs/sheet/write/sheet

Write data in batches to multiple sheets, suitable for large datasets requiring pagination. Each sheet can be named individually.

```java
@Test
public void writeMultiSheet() {
    String fileName = "repeatedWrite" + System.currentTimeMillis() + ".xlsx";

    try (ExcelWriter excelWriter = FesodSheet.write(fileName, DemoData.class).build()) {
        for (int i = 0; i < 5; i++) {
            WriteSheet writeSheet = FesodSheet.writerSheet(i, "Sheet" + i).build();
            excelWriter.write(data(), writeSheet);
        }
    }
}

```

--------------------------------

### Read Spreadsheet Data with Fesod

Source: https://fesod.apache.org/zh-cn/docs

Use this snippet to read data from an Excel file into a specified Java class using a listener. Ensure the file path and data class are correctly defined.

```java
FesodSheet.read("demo.xlsx", DemoData.class, new DemoDataListener()).sheet().doRead();  

```

--------------------------------

### Synchronous Read to Map List

Source: https://fesod.apache.org/zh-cn/docs/sheet/read/simple

Read spreadsheet data into a `List<Map<Integer, String>>` using `doReadSync()` when a POJO class is not used. Each map represents a row, with keys as column indices.

```java
@Test
public void synchronousReadToMapList() {
    String fileName = "path/to/demo.xlsx";

    // Map 列表
    List<Map<Integer, String>> list = FesodSheet.read(fileName)
            .sheet()
            .doReadSync();

    for (Map<Integer, String> data : list) {
        log.info("读取到的数据: {}", JSON.toJSONString(data));
    }
}

```
Tokens:
Raw