写在前面

一般情况下， 导出excel , 都会基于一个类， 然后在类的字段上， 加上各种注解。 但是有的时候， 数据是动态生成的， 也就是不能够基于一个类， 本文就以数据域数据格式为 List<Map<String,Object>> 为例， 使用 easypoi 导出excel.

测试步骤

引入maven依赖

<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-base</artifactId>
    <version>4.4.0</version>
</dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-web</artifactId>
    <version>4.4.0</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-annotation</artifactId>
    <version>4.4.0</version>
</dependency>

相关代码

easypoitest.zip

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DynamicExcelExportExample {
    public static void main(String[] args) {
        // 示例数据源
        List<Map<String, Object>> data = new ArrayList<>();

        Map<String, Object> row1 = new HashMap<>();
        row1.put("Name", "Jane Smith");
        row1.put("Age", 30);
        row1.put("Location", "New York");
        data.add(row1);

        Map<String, Object> row2 = new HashMap<>();
        row2.put("Name", "Jane Smith");
        row2.put("Age", 25);
        row2.put("Location", "Los Angeles");
        data.add(row2);

        Map<String, Object> row3 = new HashMap<>();
        row3.put("Name", "Mike Johnson");
        row3.put("Age", 35);
        row3.put("Location", "Los Angeles");
        data.add(row3);

        Map<String, Object> row4 = new HashMap<>();
        row4.put("Name", "Sarah Wilson");
        row4.put("Age", 40);
        row4.put("Location", "New York");
        data.add(row4);

        // 导出到 Excel 文件
        exportToExcel(data, "output1.xlsx");
    }

    public static void exportToExcel(List<Map<String, Object>> data, String fileName) {
        if (data == null || data.isEmpty()) {
            System.out.println("数据为空，无法导出");
            return;
        }

        // 创建导出参数，设置自定义样式
        ExportParams exportParams = new ExportParams();
        exportParams.setSheetName("Sheet1");
        exportParams.setStyle(CustomExcelHeadWriteHandler.class);

        // 获取表头
        Map<String, Object> firstRow = data.get(0);
        List<ExcelExportEntity> entityList = new ArrayList<>();
        for (String key : firstRow.keySet()) {
            ExcelExportEntity entity = new ExcelExportEntity(key, key);
            entityList.add(entity);
        }

        // 创建数据列表
        List<Map<String, Object>> exportData = new ArrayList<>();
        for (Map<String, Object> rowData : data) {
            Map<String, Object> map = new HashMap<>();
            for (String key : rowData.keySet()) {
                map.put(key, rowData.get(key));
            }
            exportData.add(map);
        }

        // 导出 Excel
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, entityList, exportData);
        Sheet sheet = workbook.getSheetAt(0);
        //        mergeCells(sheet, 1); // 1 是“Name”列的列索引,因为 head 头部是按照 map 的默认顺序排列的
        // 合并单元格逻辑
        mergeCells(sheet, 2); // 2 是“Location”列的列索引
        try (FileOutputStream fileOut = new FileOutputStream(fileName)) {
            workbook.write(fileOut);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private static void mergeCells(Sheet sheet, int columnIndex) {
        int startRow = -1;
        String lastValue = null;

        for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
            Row row = sheet.getRow(rowIndex);
            if (row == null) continue;

            Cell cell = row.getCell(columnIndex);
            if (cell == null) continue;

            String currentValue = cell.getStringCellValue();

            if (lastValue != null && lastValue.equals(currentValue)) {
                // Continue merging if the value is the same
                if (startRow == -1) {
                    startRow = rowIndex - 1; // Start merging from the previous row
                }
            } else {
                // Merge cells from startRow to the row before the current row
                if (lastValue != null && startRow != -1 && rowIndex > startRow) {
                    if (rowIndex - startRow > 1) { // Ensure there are at least 2 rows to merge
                        sheet.addMergedRegion(new CellRangeAddress(startRow, rowIndex - 1, columnIndex, columnIndex));
                    }
                    startRow = -1; // Reset startRow
                }
                lastValue = currentValue;
                startRow = rowIndex; // Update startRow for the new value
            }
        }

        // Handle the last region if needed
        if (lastValue != null && startRow != -1 && sheet.getLastRowNum() > startRow) {
            if (sheet.getLastRowNum() - startRow > 0) { // Ensure there are at least 2 rows to merge
                sheet.addMergedRegion(new CellRangeAddress(startRow, sheet.getLastRowNum(), columnIndex, columnIndex));
            }
        }
    }
}

import cn.afterturn.easypoi.excel.export.styler.AbstractExcelExportStyler;
import cn.afterturn.easypoi.excel.export.styler.IExcelExportStyler;
import org.apache.poi.ss.usermodel.*;
import org.springframework.stereotype.Component;

/**
 *

 * https://blog.csdn.net/weixin_44001799/article/details/124942148
 *
 */
@Component
public class CustomExcelHeadWriteHandler extends AbstractExcelExportStyler implements IExcelExportStyler {

    public CustomExcelHeadWriteHandler() {
    }

    public CustomExcelHeadWriteHandler(Workbook workbook) {
        super.createStyles(workbook);
    }

    @Override
    public CellStyle getTitleStyle(short i) {
        CellStyle titleStyle = workbook.createCellStyle();
        // 自定义字体
        Font font = workbook.createFont();
        font.setColor(IndexedColors.WHITE1.getIndex());
        font.setBold(true);
        font.setFontName("宋体");
        titleStyle.setFont(font);

        // 自定义背景色
        titleStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
        titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        titleStyle.setBorderBottom(BorderStyle.THIN);
        titleStyle.setBorderTop(BorderStyle.THIN);
        titleStyle.setBorderLeft(BorderStyle.THIN);
        titleStyle.setBorderRight(BorderStyle.THIN);

        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        titleStyle.setWrapText(true);
        return titleStyle;

    }

    @Override
    public CellStyle stringSeptailStyle(Workbook workbook, boolean isWarp) {
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setDataFormat(STRING_FORMAT);
        if (isWarp) {
            style.setWrapText(true);
        }
        return style;
    }

    @Override
    public CellStyle getHeaderStyle(short color) {
        CellStyle titleStyle = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) 12);
        titleStyle.setFont(font);
        titleStyle.setAlignment(HorizontalAlignment.CENTER);
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        return titleStyle;
    }

    @Override
    public CellStyle stringNoneStyle(Workbook workbook, boolean isWarp) {
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setDataFormat(STRING_FORMAT);
        if (isWarp) {
            style.setWrapText(true);
        }
        return style;
    }
}

导出excel效果

可以看出， 首先表头做了背景颜色处理。 相关处理代码就在
CustomExcelHeadWriteHandler 这个类中。

然后， 对于 Location 这一列，进行了合并单元格处理。 处理代码就是:

   private static void mergeCells(Sheet sheet, int columnIndex) {
        int startRow = -1;
        String lastValue = null;

        for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
            Row row = sheet.getRow(rowIndex);
            if (row == null) continue;

            Cell cell = row.getCell(columnIndex);
            if (cell == null) continue;

            String currentValue = cell.getStringCellValue();

            if (lastValue != null && lastValue.equals(currentValue)) {
                // Continue merging if the value is the same
                if (startRow == -1) {
                    startRow = rowIndex - 1; // Start merging from the previous row
                }
            } else {
                // Merge cells from startRow to the row before the current row
                if (lastValue != null && startRow != -1 && rowIndex > startRow) {
                    if (rowIndex - startRow > 1) { // Ensure there are at least 2 rows to merge
                        sheet.addMergedRegion(new CellRangeAddress(startRow, rowIndex - 1, columnIndex, columnIndex));
                    }
                    startRow = -1; // Reset startRow
                }
                lastValue = currentValue;
                startRow = rowIndex; // Update startRow for the new value
            }
        }

        // Handle the last region if needed
        if (lastValue != null && startRow != -1 && sheet.getLastRowNum() > startRow) {
            if (sheet.getLastRowNum() - startRow > 0) { // Ensure there are at least 2 rows to merge
                sheet.addMergedRegion(new CellRangeAddress(startRow, sheet.getLastRowNum(), columnIndex, columnIndex));
            }
        }
    }

最后，就是对表头数据处理:

// 获取表头
        Map<String, Object> firstRow = data.get(0);
        List<ExcelExportEntity> entityList = new ArrayList<>();
        for (String key : firstRow.keySet()) {
            ExcelExportEntity entity = new ExcelExportEntity(key, key);
            entityList.add(entity);
        }

这个测试例子中， 表头是直接通过 数据域List<Map<String,Object>> 中的第一项 Map<String,Object> 获取到它的key 作为表头的。 一般来讲， 在真实的开发中，表头是单独获取的。 其中， ExcelExportEntity entity = new ExcelExportEntity(key, key); 第一个 key 就是表头的别名。 比如： 你可以 根据实际业务需求， 自己获取一个 Map<String,Object> 的表头， 比如: 它的key 就是 原始的列名， value 就是 对应的中文名称。 那么上边的这句代码可以修改成 ExcelExportEntity entity = new ExcelExportEntity(map.get(key), key);

写在最后

正常情况下， 你导出的 excel 都会基于一个实体类。 那么，你直接在实体类中， 加入对应的注解，就可以完成相关的功能。 本文适用于你获取的数据是动态变化的， 不能基于一个实体类的情况， 数据源是List<Map<String,Object>> 的情况。 希望本文对你有所帮助。