### Java: Write Excel with Dynamically Generated Headers using EasyExcel

Source: https://context7.com/alibaba/easyexcel/llms.txt

Shows how to generate Excel headers dynamically at runtime based on business logic. This example includes methods to construct a list of header rows and a list of data rows, then writes them to an Excel file using EasyExcel. It requires EasyExcel utility classes.

```java
import com.alibaba.excel.util.ListUtils;
import java.util.List;
import java.util.Date;

public class DynamicHeadDemo {
    // 1. 构造动态表头
    private List<List<String>> dynamicHead() {
        List<List<String>> headList = ListUtils.newArrayList();

        // 第一列表头
        List<String> head0 = ListUtils.newArrayList();
        head0.add("字符串" + System.currentTimeMillis());
        headList.add(head0);

        // 第二列表头
        List<String> head1 = ListUtils.newArrayList();
        head1.add("数字" + System.currentTimeMillis());
        headList.add(head1);

        // 第三列表头
        List<String> head2 = ListUtils.newArrayList();
        head2.add("日期" + System.currentTimeMillis());
        headList.add(head2);

        return headList;
    }

    // 2. 构造动态数据
    private List<List<Object>> dynamicData() {
        List<List<Object>> dataList = ListUtils.newArrayList();
        for (int i = 0; i < 10; i++) {
            List<Object> row = ListUtils.newArrayList();
            row.add("字符串" + i);
            row.add(0.56);
            row.add(new Date());
            dataList.add(row);
        }
        return dataList;
    }

    // 3. 写入动态表头
    public void dynamicHeadWrite() {
        String fileName = "/path/to/output" + System.currentTimeMillis() + ".xlsx";

        EasyExcel.write(fileName)
            .head(dynamicHead())
            .sheet("模板")
            .doWrite(dynamicData());
    }
}
```

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

### Specify Columns for Reading with EasyExcel Annotations

Source: https://context7.com/alibaba/easyexcel/llms.txt

Shows how to use `@ExcelProperty` annotations to specify which columns to read from an Excel file. You can map columns by their index (starting from 0) or by their header name. This simplifies data mapping, especially when the Excel structure is known or when you only need a subset of the data.

```java
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

// 1. 使用 @ExcelProperty 注解指定列
@Data
public class IndexOrNameData {
    // 按索引指定(从0开始)
    @ExcelProperty(index = 0)
    private String string;

    // 按列名指定
    @ExcelProperty("日期")
    private Date date;

    // 按索引指定
    @ExcelProperty(index = 2)
    private Double doubleData;
}

// 2. 读取时会自动匹配
public class IndexReadDemo {
    public void indexOrNameRead() {
        String fileName = "/path/to/demo.xlsx";

        EasyExcel.read(fileName, IndexOrNameData.class, new ReadListener<IndexOrNameData>() {
            @Override
            public void invoke(IndexOrNameData data, AnalysisContext context) {
                log.info("读取到数据: {}", data);
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext context) {
                log.info("所有数据解析完成!");
            }
        }).sheet().doRead();
    }
}
```

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

### Configure File Storage Threshold for Shared Strings (Java)

Source: https://github.com/alibaba/easyexcel/blob/master/docs/LARGEREAD.md

This configuration allows fine-grained control over when EasyExcel switches from in-memory storage to file-based storage for shared strings. It's particularly useful for high-concurrency scenarios involving very large files where managing memory is critical. The `SimpleReadCacheSelector` takes two parameters: the first defines the shared string size threshold (in MB) for switching to file storage, and the second defines the amount of memory (in MB) to allocate for caching shared strings when file storage is used. This helps balance memory usage and read efficiency.

```java
EasyExcel.read().readCacheSelector(new SimpleReadCacheSelector(5, 20));
```

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

### Force In-Memory Storage for Large Files (Java)

Source: https://github.com/alibaba/easyexcel/blob/master/docs/LARGEREAD.md

This configuration forces EasyExcel to use in-memory storage for all data, including shared strings. This approach can offer higher efficiency compared to file-based storage for specific scenarios, such as when dealing with moderately sized files and low concurrency, but may lead to higher peak memory usage. It is achieved by providing a `MapCache` to the `readCache` method.

```java
EasyExcel.read().readCache(new MapCache());
```

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

### Java EasyExcel Web File Download

Source: https://github.com/alibaba/easyexcel/blob/master/README.md

Demonstrates downloading an Excel file using EasyExcel. It sets the response content type and headers to facilitate a file download. The `EasyExcel.write` method is used to write data to the response's output stream, creating an Excel file.

```java
    /**
    * 文件下载（失败了会返回一个有部分数据的Excel）
    * <p>
    * 1. 创建excel对应的实体对象 参照{@link DownloadData}
    * <p>
    * 2. 设置返回的 参数
    * <p>
    * 3. 直接写，这里注意，finish的时候会自动关闭OutputStream,当然你外面再关闭流问题不大
    */
    @GetMapping("download")
    public void download(HttpServletResponse response) throws IOException {
        // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName=URLEncoder.encode("测试","UTF-8").replaceAll("\\+","%20");
        response.setHeader("Content-disposition","attachment;filename*=utf-8''"+fileName+".xlsx");
        EasyExcel.write(response.getOutputStream(),DownloadData.class).sheet("模板").doWrite(data());
    }
```

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

### Simple Excel Writing with EasyExcel

Source: https://context7.com/alibaba/easyexcel/llms.txt

Illustrates how to write Java object lists to an Excel file using EasyExcel. It covers the creation of a data model (DemoData) and demonstrates multiple writing strategies, including a straightforward write operation, using `ExcelWriter` for better control (especially for large data), and batch writing to the same sheet.

```java
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.util.ListUtils;
import lombok.Data;

import java.util.Date;
import java.util.List;

// 1. 定义实体类
@Data
public class DemoData {
    private String string;
    private Date date;
    private Double doubleData;
}

// 2. 准备数据
public class WriteDemo {
    private List<DemoData> data() {
        List<DemoData> list = ListUtils.newArrayList();
        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 void simpleWrite() {
        String fileName = "/path/to/output" + System.currentTimeMillis() + ".xlsx";

        // 方式1: 最简单的写入
        EasyExcel.write(fileName, DemoData.class)
            .sheet("模板")
            .doWrite(data());

        // 方式2: 使用 ExcelWriter (推荐大数据量场景)
        try (ExcelWriter excelWriter = EasyExcel.write(fileName, DemoData.class).build()) {
            WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
            excelWriter.write(data(), writeSheet);
        }

        // 方式3: 分批多次写入同一个 sheet
        try (ExcelWriter excelWriter = EasyExcel.write(fileName, DemoData.class).build()) {
            WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
            for (int i = 0; i < 5; i++) {
                // 分页查询数据库,每次写入一批
                List<DemoData> dataList = data();
                excelWriter.write(dataList, writeSheet);
            }
        }
    }
}

```

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

### Maven Dependency for EasyExcel

Source: https://github.com/alibaba/easyexcel/blob/master/README.md

This snippet shows how to add the EasyExcel library to your Maven project. Ensure you use the latest version available for the most up-to-date features and bug fixes.

```xml
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>4.0.3</version>
</dependency>
```

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

### EasyExcel 简单填充 (Java)

Source: https://context7.com/alibaba/easyexcel/llms.txt

使用 Java 对象或 Map 对 Excel 模板进行简单数据填充。需要指定模板文件路径和输出文件路径，并传入填充数据。支持使用 {key} 占位符进行填充。

```java
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.util.MapUtils;
import lombok.Data;
import java.util.Date;
import java.util.Map;

@Data
class FillData {
    private String name;
    private Double number;
    private Date date;
}

public class FillDemo {
    // 1. 简单填充
    public void simpleFill() {
        String templateFileName = "/path/to/template.xlsx";
        String fileName = "/path/to/output" + System.currentTimeMillis() + ".xlsx";

        // 方式1: 使用对象填充
        FillData fillData = new FillData();
        fillData.setName("张三");
        fillData.setNumber(5.2);
        EasyExcel.write(fileName)
            .withTemplate(templateFileName)
            .sheet()
            .doFill(fillData);

        // 方式2: 使用 Map 填充 (模板中使用 {name} {number} 占位符)
        Map<String, Object> map = MapUtils.newHashMap();
        map.put("name", "张三");
        map.put("number", 5.2);
        EasyExcel.write(fileName)
            .withTemplate(templateFileName)
            .sheet()
            .doFill(map);
    }
}
```

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

### Simple Excel Reading with EasyExcel

Source: https://context7.com/alibaba/easyexcel/llms.txt

Demonstrates basic Excel file reading using EasyExcel. It involves defining a data model (DemoData), implementing a listener (DemoDataListener) to process rows, and then executing the read operation. Supports different reading strategies including `PageReadListener` for large files and a synchronous method (not recommended for large data).

```java
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.PageReadListener;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.util.ListUtils;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;

import java.util.Date;
import java.util.List;

// 1. 定义实体类
@Data
public class DemoData {
    private String string;
    private Date date;
    private Double doubleData;
}

// 2. 实现监听器处理数据
@Slf4j
public class DemoDataListener implements ReadListener<DemoData> {
    private static final int BATCH_COUNT = 100;
    private List<DemoData> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);

    @Override
    public void invoke(DemoData data, AnalysisContext context) {
        log.info("解析到一条数据:{}", data);
        cachedDataList.add(data);
        if (cachedDataList.size() >= BATCH_COUNT) {
            saveData();
            cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        saveData();
        log.info("所有数据解析完成!");
    }

    private void saveData() {
        log.info("{}条数据,开始存储数据库!", cachedDataList.size());
        // 实际业务中这里应该调用 DAO 保存到数据库
        log.info("存储数据库成功!");
    }
}

// 3. 执行读取
public class ReadDemo {
    public void simpleRead() {
        String fileName = "/path/to/demo.xlsx";

        // 方式1: 使用 PageReadListener (JDK8+)
        EasyExcel.read(fileName, DemoData.class, new PageReadListener<DemoData>(dataList -> {
            for (DemoData demoData : dataList) {
                log.info("读取到数据: {}", demoData);
            }
        })).sheet().doRead();

        // 方式2: 使用自定义监听器
        EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();

        // 方式3: 同步读取(不推荐,数据量大会OOM)
        List<DemoData> list = EasyExcel.read(fileName)
            .head(DemoData.class)
            .sheet()
            .doReadSync();
        for (DemoData data : list) {
            log.info("读取到数据: {}", data);
        }
    }
}

```

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

### EasyExcel 复杂填充 (Java)

Source: https://context7.com/alibaba/easyexcel/llms.txt

结合列表和单个变量进行 Excel 模板填充。可以通过 `FillConfig` 设置 `forceNewRow` 为 `true` 来控制列表写入时是否强制换行。适用于需要填充混合类型数据的场景。

```java
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.enums.WriteDirectionEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.excel.util.MapUtils;
import lombok.Data;
import java.util.Date;
import java.util.List;
import java.util.Map;

@Data
class FillData {
    private String name;
    private Double number;
    private Date date;
}

public class FillDemo {
    // 3. 复杂填充(列表+单个变量)
    public void complexFill() {
        String templateFileName = "/path/to/template.xlsx";
        String fileName = "/path/to/output" + 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(prepareData(), fillConfig, writeSheet);
            excelWriter.fill(prepareData(), fillConfig, writeSheet);

            // 填充单个变量
            Map<String, Object> map = MapUtils.newHashMap();
            map.put("date", "2024年1月1日");
            map.put("total", 1000);
            excelWriter.fill(map, writeSheet);
        }
    }

    private List<FillData> prepareData() {
        List<FillData> list = ListUtils.newArrayList();
        for (int i = 0; i < 10; i++) {
            FillData fillData = new FillData();
            fillData.setName("姓名" + i);
            fillData.setNumber(Double.valueOf(i * 10.0));
            fillData.setDate(new Date());
            list.add(fillData);
        }
        return list;
    }
}
```

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

### Java Web Excel 文件下载

Source: https://context7.com/alibaba/easyexcel/llms.txt

实现 Excel 文件下载功能。通过设置 HttpServletResponse 的响应头，将 Excel 数据流写入响应。支持中文文件名编码，并使用 EasyExcel 写入数据。

```java
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.util.ListUtils;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.Date;
import java.util.List;

@Data
class DownloadData {
    private String string;
    private Date date;
    private Double doubleData;
}

@Controller
public class WebExcelController {

    /**
     * 文件下载
     * 访问: http://localhost:8080/download
     */
    @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("测试", "UTF-8").replaceAll("\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");

        // 准备数据
        List<DownloadData> dataList = ListUtils.newArrayList();
        for (int i = 0; i < 10; i++) {
            DownloadData data = new DownloadData();
            data.setString("字符串" + i);
            data.setDate(new Date());
            data.setDoubleData(0.56);
            dataList.add(data);
        }

        // 写入响应流 (自动关闭流)
        EasyExcel.write(response.getOutputStream(), DownloadData.class)
            .sheet("模板")
            .doWrite(dataList);
    }

    private List<DownloadData> prepareData() {
        List<DownloadData> list = ListUtils.newArrayList();
        for (int i = 0; i < 100; i++) {
            DownloadData data = new DownloadData();
            data.setString("字符串" + i);
            data.setDate(new Date());
            data.setDoubleData(0.56);
            list.add(data);
        }
        return list;
    }
}
```

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

### Java Web Excel 文件上传

Source: https://context7.com/alibaba/easyexcel/llms.txt

实现 Excel 文件上传功能。接收 MultipartFile，使用 EasyExcel 读取文件内容，并交给自定义的监听器处理。监听器负责将解析出的数据保存。

```java
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.util.ListUtils;
import lombok.Data;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.util.Date;
import java.util.List;

@Data
class UploadData {
    private String string;
    private Date date;
    private Double doubleData;
}

// 上传监听器
class UploadDataListener implements ReadListener<UploadData> {
    private static final int BATCH_COUNT = 100;
    private List<UploadData> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);

    @Override
    public void invoke(UploadData data, AnalysisContext context) {
        cachedDataList.add(data);
        if (cachedDataList.size() >= BATCH_COUNT) {
            saveData();
            cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        saveData();
    }

    private void saveData() {
        // 保存到数据库
        System.out.println("保存 " + cachedDataList.size() + " 条数据到数据库");
    }
}

@Controller
public class WebExcelController {

    /**
     * 文件上传
     * 访问: POST http://localhost:8080/upload
     */
    @PostMapping("upload")
    @ResponseBody
    public String upload(MultipartFile file) throws IOException {
        // 读取上传的文件
        EasyExcel.read(file.getInputStream(), UploadData.class, new UploadDataListener())
            .sheet()
            .doRead();

        return "success";
    }
}
```

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

### Set Cell Styles with Annotations and Handlers in Java

Source: https://context7.com/alibaba/easyexcel/llms.txt

Demonstrates how to apply custom styles to Excel cells using EasyExcel in Java. It covers both annotation-based styling for data classes and programmatic styling via cell write handlers. Dependencies include EasyExcel and Apache POI.

```java
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.*;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.handler.context.CellWriteHandlerContext;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import lombok.Data;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;

// 1. 使用注解设置样式
@Data
@HeadRowHeight(20)
@ContentRowHeight(15)
@ColumnWidth(25)
@HeadStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 10)
@HeadFontStyle(fontHeightInPoints = 14, bold = true)
@ContentStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 17)
@ContentFontStyle(fontHeightInPoints = 11)
public class DemoStyleData {
    @ExcelProperty("字符串标题")
    @ColumnWidth(50)
    private String string;

    @ExcelProperty("日期标题")
    @DateTimeFormat("yyyy年MM月dd日")
    private Date date;

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

// 2. 使用拦截器设置样式
public class StyleDemo {
    public void annotationStyleWrite() {
        String fileName = "/path/to/output" + System.currentTimeMillis() + ".xlsx";

        // 注解方式直接写入
        EasyExcel.write(fileName, DemoStyleData.class)
            .sheet("模板")
            .doWrite(data());
    }

    public void handlerStyleWrite() {
        String fileName = "/path/to/output" + System.currentTimeMillis() + ".xlsx";

        // 表头样式
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        headWriteCellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 20);
        headWriteCellStyle.setWriteFont(headWriteFont);

        // 内容样式
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
        WriteFont contentWriteFont = new WriteFont();
        contentWriteFont.setFontHeightInPoints((short) 20);
        contentWriteCellStyle.setWriteFont(contentWriteFont);

        // 应用样式策略
        HorizontalCellStyleStrategy horizontalCellStyleStrategy =
            new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);

        EasyExcel.write(fileName, DemoData.class)
            .registerWriteHandler(horizontalCellStyleStrategy)
            .sheet("模板")
            .doWrite(data());
    }
}
```

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

### EasyExcel 多列表组合填充 (Java)

Source: https://context7.com/alibaba/easyexcel/llms.txt

支持使用 `FillWrapper` 将多个列表组合填充到 Excel 模板中，通过指定别名（如 `data1`, `data2`）区分不同列表。模板中可以使用 `{alias.fieldName}` 的方式引用数据。

```java
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.enums.WriteDirectionEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.alibaba.excel.write.metadata.fill.FillWrapper;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.excel.util.MapUtils;
import lombok.Data;
import java.util.Date;
import java.util.List;
import java.util.Map;

@Data
class FillData {
    private String name;
    private Double number;
    private Date date;
}

public class FillDemo {
    // 5. 多列表组合填充
    public void compositeFill() {
        String templateFileName = "/path/to/template.xlsx";
        String fileName = "/path/to/output" + System.currentTimeMillis() + ".xlsx";

        // 模板中使用 {data1.name} {data2.name} {data3.name} 区分不同列表
        try (ExcelWriter excelWriter = EasyExcel.write(fileName)
                .withTemplate(templateFileName).build()) {
            WriteSheet writeSheet = EasyExcel.writerSheet().build();
            FillConfig fillConfig = FillConfig.builder()
                .direction(WriteDirectionEnum.HORIZONTAL)
                .build();

            // 使用 FillWrapper 包裹不同的列表
            excelWriter.fill(new FillWrapper("data1", prepareData()), fillConfig, writeSheet);
            excelWriter.fill(new FillWrapper("data2", prepareData()), writeSheet);
            excelWriter.fill(new FillWrapper("data3", prepareData()), writeSheet);

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

    private List<FillData> prepareData() {
        List<FillData> list = ListUtils.newArrayList();
        for (int i = 0; i < 10; i++) {
            FillData fillData = new FillData();
            fillData.setName("姓名" + i);
            fillData.setNumber(Double.valueOf(i * 10.0));
            fillData.setDate(new Date());
            list.add(fillData);
        }
        return list;
    }
}
```

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

### EasyExcel 列表填充 (Java)

Source: https://context7.com/alibaba/easyexcel/llms.txt

使用 Java 列表对 Excel 模板进行数据填充，支持一次性填充和分批填充以节省内存。列表填充的占位符通常使用 `{.fieldName}` 格式。

```java
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.util.ListUtils;
import lombok.Data;
import java.util.Date;
import java.util.List;

@Data
class FillData {
    private String name;
    private Double number;
    private Date date;
}

public class FillDemo {
    // 2. 列表填充
    public void listFill() {
        String templateFileName = "/path/to/template.xlsx";
        String fileName = "/path/to/output" + System.currentTimeMillis() + ".xlsx";

        List<FillData> data = prepareData();

        // 一次性填充 (模板中使用 {.name} {.number} 表示列表)
        EasyExcel.write(fileName)
            .withTemplate(templateFileName)
            .sheet()
            .doFill(data);

        // 分批填充(节省内存)
        try (ExcelWriter excelWriter = EasyExcel.write(fileName)
                .withTemplate(templateFileName).build()) {
            WriteSheet writeSheet = EasyExcel.writerSheet().build();
            excelWriter.fill(data, writeSheet);
            excelWriter.fill(data, writeSheet);
        }
    }

    private List<FillData> prepareData() {
        List<FillData> list = ListUtils.newArrayList();
        for (int i = 0; i < 10; i++) {
            FillData fillData = new FillData();
            fillData.setName("姓名" + i);
            fillData.setNumber(Double.valueOf(i * 10.0));
            fillData.setDate(new Date());
            list.add(fillData);
        }
        return list;
    }
}
```

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

### Format Data Conversion with EasyExcel Annotations and Converters

Source: https://context7.com/alibaba/easyexcel/llms.txt

Illustrates how to automatically convert data types like dates and numbers during Excel reading and writing using annotations such as `@DateTimeFormat` and `@NumberFormat`. It also shows how to implement and register custom `Converter` classes for more complex transformations, ensuring data integrity and simplifying processing.

```java
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.format.NumberFormat;
import com.alibaba.excel.converters.Converter;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.GlobalConfiguration;
import com.alibaba.excel.metadata.data.ReadCellData;
import com.alibaba.excel.metadata.property.ExcelContentProperty;
import lombok.Data;

// 1. 定义带格式转换的实体类
@Data
public class ConverterData {
    // 自定义字符串转换器
    @ExcelProperty(converter = CustomStringConverter.class)
    private String string;

    // 日期格式转换
    @DateTimeFormat("yyyy年MM月dd日HH时mm分ss秒")
    private Date date;

    // 数字格式转换
    @NumberFormat("#.##%")
    private Double doubleData;
}

// 2. 自定义转换器示例
public class CustomStringConverter implements Converter<String> {
    @Override
    public Class<?> supportJavaTypeKey() {
        return String.class;
    }

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

    @Override
    public String convertToJavaData(ReadCellData<?> cellData,
                                     ExcelContentProperty contentProperty,
                                     GlobalConfiguration globalConfiguration) {
        // 读取时的转换逻辑
        return "自定义:" + cellData.getStringValue();
    }

    @Override
    public WriteCellData<?> convertToExcelData(String value,
                                                ExcelContentProperty contentProperty,
                                                GlobalConfiguration globalConfiguration) {
        // 写入时的转换逻辑
        return new WriteCellData<>(value);
    }
}

// 3. 使用
public class ConverterDemo {
    public void converterRead() {
        String fileName = "/path/to/demo.xlsx";

        EasyExcel.read(fileName, ConverterData.class, new PageReadListener<ConverterData>(dataList -> {
            for (ConverterData data : dataList) {
                log.info("读取到数据: {}", data);
            }
        }))
        // 也可以注册全局转换器
        .registerConverter(new CustomStringConverter())
        .sheet()
        .doRead();
    }
}
```

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

### Java Web Excel 文件下载（失败时返回 JSON）

Source: https://context7.com/alibaba/easyexcel/llms.txt

处理 Excel 文件下载，并在下载失败时返回 JSON 格式的错误信息。通过 try-catch 块捕获异常，重置响应内容类型为 JSON。

```java
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.util.ListUtils;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.Date;
import java.util.List;

@Data
class DownloadData {
    private String string;
    private Date date;
    private Double doubleData;
}

@Controller
public class WebExcelController {

    /**
     * 文件下载(失败时返回JSON)
     */
    @GetMapping("downloadFailedUsingJson")
    public void downloadFailedUsingJson(HttpServletResponse response) throws IOException {
        try {
            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");

            // 设置不自动关闭流,以便出错时可以重置响应
            EasyExcel.write(response.getOutputStream(), DownloadData.class)
                .autoCloseStream(Boolean.FALSE)
                .sheet("模板")
                .doWrite(prepareData());
        } catch (Exception e) {
            // 重置响应
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            response.getWriter().println("{\"status\":\"failure\",\"message\":\"下载文件失败\"}");
        }
    }

    private List<DownloadData> prepareData() {
        List<DownloadData> list = ListUtils.newArrayList();
        for (int i = 0; i < 100; i++) {
            DownloadData data = new DownloadData();
            data.setString("字符串" + i);
            data.setDate(new Date());
            data.setDoubleData(0.56);
            list.add(data);
        }
        return list;
    }
}
```

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

### Java EasyExcel Web File Upload

Source: https://github.com/alibaba/easyexcel/blob/master/README.md

Demonstrates uploading an Excel file using EasyExcel. It reads data from a `MultipartFile` input stream using `EasyExcel.read` and processes it with a custom listener (`UploadDataListener`). This is suitable for handling file uploads in web applications.

```java
    /**
     * 文件上传
     * <p>1. 创建excel对应的实体对象 参照{@link UploadData}
     * <p>2. 由于默认一行行的读取excel，所以需要创建excel一行一行的回调监听器，参照{@link UploadDataListener}
     * <p>3. 直接读即可
     */
    @PostMapping("upload")
    @ResponseBody
    public String upload(MultipartFile file)throws IOException{
        EasyExcel.read(file.getInputStream(),UploadData.class,new UploadDataListener(uploadDAO)).sheet().doRead();
        return"success";
    }
```

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

### Simple Excel Reading in Java

Source: https://github.com/alibaba/easyexcel/blob/master/README.md

Demonstrates the basic usage of EasyExcel for reading data from an Excel file. It requires an Excel file, a corresponding data entity class (DemoData), and a listener class (DemoDataListener) to process each row. The method reads the first sheet by default.

```java
    /**
    * 最简单的读
    * <p>1. 创建excel对应的实体对象 参照{@link DemoData}
    * <p>2. 由于默认一行行的读取excel，所以需要创建excel一行行的回调监听器，参照{@link DemoDataListener}
    * <p>3. 直接读即可
    */
    @Test
    public void simpleRead() {
        String fileName = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
        // 这里 需要指定读用哪个class去读，然后读取第一个sheet 文件流会自动关闭
        EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();
    }
```

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

### Java: Read and Write Complex Headers with EasyExcel

Source: https://context7.com/alibaba/easyexcel/llms.txt

Demonstrates defining and using multi-row headers in Excel files. It includes a data class with annotations to map nested headers and methods for reading and writing Excel files with these complex structures. Dependencies include EasyExcel and Lombok.

```java
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
import java.util.Date;

// 1. 使用数组定义复杂表头
@Data
public class ComplexHeadData {
    @ExcelProperty({"主标题", "字符串标题"})
    private String string;

    @ExcelProperty({"主标题", "日期标题"})
    private Date date;

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

// 2. 读取复杂表头
public class ComplexHeadDemo {
    public void complexHeaderRead() {
        String fileName = "/path/to/demo.xlsx";

        EasyExcel.read(fileName, DemoData.class, new DemoDataListener())
            .sheet()
            // 指定表头行数(如果不指定,会根据 @ExcelProperty 注解自动计算)
            .headRowNumber(2)
            .doRead();
    }

    public void complexHeadWrite() {
        String fileName = "/path/to/output" + System.currentTimeMillis() + ".xlsx";

        // 写入时会自动生成复杂表头
        EasyExcel.write(fileName, ComplexHeadData.class)
            .sheet("模板")
            .doWrite(data());
    }
}
```

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

### Simple Excel Writing in Java

Source: https://github.com/alibaba/easyexcel/blob/master/README.md

Illustrates the straightforward way to write data to an Excel file using EasyExcel. This involves defining a data entity class (DemoData) and providing the data to be written. The method writes to the first sheet with a specified name and automatically closes the file stream.

```java
    /**
     * 最简单的写
     * <p>1. 创建excel对应的实体对象 参照{@link com.alibaba.easyexcel.test.demo.write.DemoData}
     * <p>2. 直接写即可
     */
    @Test
    public void simpleWrite() {
        String fileName=TestFileUtil.getPath()+"write"+System.currentTimeMillis()+".xlsx";
        // 这里 需要指定写用哪个class去读，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        // 如果这里想使用03 则 传入excelType参数即可
        EasyExcel.write(fileName,DemoData.class).sheet("模板").doWrite(data());
    }
```

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

### Export Specific Columns with EasyExcel in Java

Source: https://context7.com/alibaba/easyexcel/llms.txt

Demonstrates how to export an Excel file using EasyExcel in Java, either by excluding specific columns or including only a defined set of columns. This is useful for tailoring reports to specific business needs. It requires the EasyExcel library and a data class (DemoData).

```java
import java.util.HashSet;
import java.util.Set;

public class IncludeExcludeDemo {
    public void excludeOrIncludeWrite() {
        String fileName = "/path/to/output" + System.currentTimeMillis() + ".xlsx";

        // 排除指定字段(假设排除 date 字段)
        Set<String> excludeColumnFieldNames = new HashSet<>();
        excludeColumnFieldNames.add("date");

        EasyExcel.write(fileName, DemoData.class)
            .excludeColumnFieldNames(excludeColumnFieldNames)
            .sheet("模板")
            .doWrite(data());

        // 只导出指定字段(假设只导出 date 字段)
        Set<String> includeColumnFieldNames = new HashSet<>();
        includeColumnFieldNames.add("date");

        EasyExcel.write(fileName, DemoData.class)
            .includeColumnFieldNames(includeColumnFieldNames)
            .sheet("模板")
            .doWrite(data());
    }
}
```

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

### EasyExcel 横向填充 (Java)

Source: https://context7.com/alibaba/easyexcel/llms.txt

配置 `FillConfig` 设置 `direction` 为 `WriteDirectionEnum.HORIZONTAL`，实现 Excel 模板的横向数据填充。适用于需要将列表数据按行填充到模板中的场景。

```java
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.enums.WriteDirectionEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.alibaba.excel.util.ListUtils;
import lombok.Data;
import java.util.Date;
import java.util.List;

@Data
class FillData {
    private String name;
    private Double number;
    private Date date;
}

public class FillDemo {
    // 4. 横向填充
    public void horizontalFill() {
        String templateFileName = "/path/to/template.xlsx";
        String fileName = "/path/to/output" + 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(prepareData(), fillConfig, writeSheet);
        }
    }

    private List<FillData> prepareData() {
        List<FillData> list = ListUtils.newArrayList();
        for (int i = 0; i < 10; i++) {
            FillData fillData = new FillData();
            fillData.setName("姓名" + i);
            fillData.setNumber(Double.valueOf(i * 10.0));
            fillData.setDate(new Date());
            list.add(fillData);
        }
        return list;
    }
}
```

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

### Merge Cells with Annotations and Strategies in Java

Source: https://context7.com/alibaba/easyexcel/llms.txt

Explains how to merge cells in EasyExcel using Java. It covers merging cells automatically using the `@ContentLoopMerge` annotation and programmatically using the `LoopMergeStrategy`. Dependencies include EasyExcel and Lombok.

```java
import com.alibaba.excel.annotation.write.style.ContentLoopMerge;
import com.alibaba.excel.write.merge.LoopMergeStrategy;
import lombok.Data;

// 1. 使用注解合并
@Data
public class DemoMergeData {
    // 每隔2行合并一次,合并当前行和后面1行(共2行)
    @ContentLoopMerge(eachRow = 2)
    @ExcelProperty("字符串标题")
    private String string;

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

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

// 2. 使用策略合并
public class MergeDemo {
    public void annotationMergeWrite() {
        String fileName = "/path/to/output" + System.currentTimeMillis() + ".xlsx";

        // 注解方式会自动合并
        EasyExcel.write(fileName, DemoMergeData.class)
            .sheet("模板")
            .doWrite(data());
    }

    public void strategyMergeWrite() {
        String fileName = "/path/to/output" + System.currentTimeMillis() + ".xlsx";

        // 每隔2行合并,从第0列开始(只合并第一列)
        LoopMergeStrategy loopMergeStrategy = new LoopMergeStrategy(2, 0);

        EasyExcel.write(fileName, DemoData.class)
            .registerWriteHandler(loopMergeStrategy)
            .sheet("模板")
            .doWrite(data());
    }
}
```

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

### Read Multiple Excel Sheets with EasyExcel

Source: https://context7.com/alibaba/easyexcel/llms.txt

Demonstrates how to read all sheets from an Excel file or read only specific sheets. It utilizes EasyExcel's `read` and `readSheet` methods, allowing for custom listeners per sheet. This is useful for processing files with varied data structures across different sheets.

```java
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.read.metadata.ReadSheet;

public class MultiSheetReadDemo {
    public void readAllSheets() {
        String fileName = "/path/to/demo.xlsx";

        // 读取所有 sheet (注意: DemoDataListener 会接收所有 sheet 的数据)
        EasyExcel.read(fileName, DemoData.class, new DemoDataListener())
            .doReadAll();
    }

    public void readSpecificSheets() {
        String fileName = "/path/to/demo.xlsx";

        // 读取指定的多个 sheet
        try (ExcelReader excelReader = EasyExcel.read(fileName).build()) {
            // 为每个 sheet 指定不同的监听器
            ReadSheet readSheet1 = EasyExcel.readSheet(0)
                .head(DemoData.class)
                .registerReadListener(new DemoDataListener())
                .build();

            ReadSheet readSheet2 = EasyExcel.readSheet(1)
                .head(DemoData.class)
                .registerReadListener(new DemoDataListener())
                .build();

            // 一次性读取多个 sheet (推荐,避免重复读取文件)
            excelReader.read(readSheet1, readSheet2);
        }
    }
}
```

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

### Read Extra Information (Comments, Hyperlinks, Merged Cells) with EasyExcel in Java

Source: https://context7.com/alibaba/easyexcel/llms.txt

Shows how to read extra information such as comments, hyperlinks, and merged cells from an Excel file using EasyExcel in Java. This feature is valuable for extracting rich metadata from spreadsheets. It requires the EasyExcel library, a data class (DemoData), and specifies which extra types to read.

```java
import com.alibaba.excel.enums.CellExtraTypeEnum;
import com.alibaba.excel.metadata.CellExtra;
import com.alibaba.excel.read.listener.ReadListener;

public class ExtraInfoDemo {
    public void extraRead() {
        String fileName = "/path/to/demo.xlsx";

        EasyExcel.read(fileName, DemoData.class, new ReadListener<DemoData>() {
            @Override
            public void invoke(DemoData data, AnalysisContext context) {
                log.info("读取到数据: {}", data);
            }

            @Override
            public void extra(CellExtra extra, AnalysisContext context) {
                switch (extra.getType()) {
                    case COMMENT:
                        log.info("批注: {}", extra.getText());
                        break;
                    case HYPERLINK:
                        log.info("超链接: {}", extra.getText());
                        break;
                    case MERGE:
                        log.info("合并单元格: 起始行{} 起始列{} 结束行{} 结束列 strok",
                            extra.getFirstRowIndex(), extra.getFirstColumnIndex(),
                            extra.getLastRowIndex(), extra.getLastColumnIndex());
                        break;
                    default:
                        break;
                }
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext context) {
                log.info("所有数据解析完成!");
            }
        })
        .extraRead(CellExtraTypeEnum.COMMENT)      // 读取批注
        .extraRead(CellExtraTypeEnum.HYPERLINK)    // 读取超链接
        .extraRead(CellExtraTypeEnum.MERGE)        // 读取合并单元格
        .sheet()
        .doRead();
    }
}
```

=== COMPLETE CONTENT === This response contains all available snippets from this library. No additional content exists. Do not make further requests.