Easy Excel动态表头的实现

步骤：

1.查找官方API文档理解实现

2.实现融入到代码里面

一：Easy Excel动态头实时生成头写入

动态头实时生成头写入

二：实现

目的：实现表头为，第一列是固定列，第二列为动态生成的时间段的每一天的日期

步骤：

1.查询数据

2.创建表头

3.填充数据

例子：

package com.test.cn.sales.service.impl;

import com.alibaba.excel.EasyExcel;

import java.io.File;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

public class DynamicExcelExport {
    public static void main(String[] args) {
        //1.模拟从数据库中获取数据
        List<String> keywords = getKeywordsFromDatabase();
        List<SalesData> salesDataList = getSalesDataFromDatabase();

        //2.创建表头
        List<String> dates = generateDateRange("2023-12-30", "2024-02-02");// 根据开始和结束时间动态生成日期
        List<List<String>> head = new ArrayList<>();
        head.add(new ArrayList<>(Arrays.asList("关键字"))); // 第一列固定为关键字
        dates.forEach(o -> head.add(new ArrayList<>(Arrays.asList(o))));

        //3.填充数据
        List<List<Object>> data = fillData(keywords, salesDataList, dates);

        String outputPath = "E:\\output.xlsx"; // 输出路径修改为 E 盘
        EasyExcel.write(new File(outputPath)).head(head).sheet("Sheet1")
                .doWrite(data);
        System.out.println("Excel 文件导出成功！输出路径：" + outputPath);
    }

    private static List<String> getKeywordsFromDatabase() {
        // 模拟从数据库中获取关键字
        return new ArrayList<>(Arrays.asList("关键字1", "关键字2", "关键字3"));
    }

    private static List<SalesData> getSalesDataFromDatabase() {
        // 模拟从数据库中获取销售数据
        List<SalesData> salesDataList = new ArrayList<>();
        salesDataList.add(new SalesData("关键字1", 100.00, "2023-12-30"));
        salesDataList.add(new SalesData("关键字2", 200.00, "2024-02-02"));
        salesDataList.add(new SalesData("关键字3", 300.00, "2024-02-01"));
        return salesDataList;
    }


    private static List<List<Object>> fillData(List<String> keywords, List<SalesData> salesDataList, List<String> dates) {
        // 填充数据
        List<List<Object>> data = new ArrayList<>();
        for (String keyword : keywords) {
            List<Object> rowData = new ArrayList<>();
            rowData.add(keyword); // 添加关键字
            for (String date : dates) {
                Double sales = getSalesForKeywordAndDate(keyword, date, salesDataList);
                rowData.add(sales != null ? sales : 0); // 添加销量数据
            }
            data.add(rowData);
        }
        return data;
    }

    private static Double getSalesForKeywordAndDate(String keyword, String date, List<SalesData> salesDataList) {
        // 根据关键字和日期从销售数据列表中获取销量数据
        for (SalesData salesData : salesDataList) {
            if (salesData.getKeyword().equals(keyword) && salesData.getDate().toString().equals(date)) {
                return salesData.getSales();
            }
        }
        return null;
    }

    /**
     * 时间段生成
     *
     * @param startDateStr 开始时间
     * @param endDateStr   结束时间
     * @return list
     */
    public static List<String> generateDateRange(String startDateStr, String endDateStr) {
        List<String> dateRange = new ArrayList<>();
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
        LocalDate startDate = LocalDate.parse(startDateStr, formatter);
        LocalDate endDate = LocalDate.parse(endDateStr, formatter);

        while (!startDate.isAfter(endDate)) {
            dateRange.add(startDate.format(formatter));
            startDate = startDate.plusDays(1);
        }
        return dateRange;
    }
}

class SalesData {
    private String keyword;

    private Double sales;

    private String date;

    public SalesData(String keyword, Double sales, String date) {
        this.keyword = keyword;
        this.sales = sales;
        this.date = date;
    }

    public String getKeyword() {
        return keyword;
    }

    public void setKeyword(String keyword) {
        this.keyword = keyword;
    }

    public Double getSales() {
        return sales;
    }

    public void setSales(Double sales) {
        this.sales = sales;
    }

    public String getDate() {
        return date;
    }

    public void setDate(String date) {
        this.date = date;
    }
}

导出效果：