easyexcel-fill

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

@Service
public class SalaryFillService {

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

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

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

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

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