Java EasyExcel 支持动态表头和单元格下拉导出

最新推荐文章于 2025-03-29 19:30:00 发布

原创最新推荐文章于 2025-03-29 19:30:00 发布 · 1k 阅读

4 ·

CC 4.0 BY-SA版权

文章标签：

#java #开发语言

easyexcel 同时被 2 个专栏收录

2 篇文章

订阅专栏

java

2 篇文章

订阅专栏

该博客介绍了如何使用EasyExcel库动态创建Excel模板并设置单元格的下拉选项。通过测试类`DynamicWriteTest`，展示了如何准备表头、下拉列表数据，并利用自定义拦截器`DynamicSheetWriteHandler`实现数据验证和下拉框功能。测试方法中创建了一个带有动态表头的Excel文件，并设置了特定列的下拉选项，实现了动态导出模板的功能。

需求背景：对于有些表头需要根据配置动态生成，做以下记录。

动态导出模板

测试工具类

package com.alibaba.easyexcel.test.demo.write;

import com.alibaba.easyexcel.test.util.TestFileUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.util.ListUtils;
import org.junit.Test;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.stream.Collectors;

/**
 * @description:
 * @author: root
 * @date: 2022-11-16
 */
public class DynamicWriteTest {

    /**
     * 测试方法
     * @param
     * @return
     * @author root
     * @date 2022-11-16
     */
    @Test
    public void writeDynamicHead() {
        // 表头
        List<List<String>> headers = prepareHeaders();
        // 单元格下拉
        HashMap<Integer, List<String>> dropCells = prepareDropCells();

        // 模板导出
        String fileName = TestFileUtil.getPath() + "customHandlerWrite" + System.currentTimeMillis() + ".xlsx";
        EasyExcel.write(fileName).head(headers).registerWriteHandler(new DynamicSheetWriteHandler(dropCells)).sheet("模板").doWrite(new ArrayList<>());
    }

    /**
     * 测试数据
     * @param
     * @return {@link List< DemoData>}
     * @author root
     * @date 2022-11-16
     */
    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;
    }

    private static final List<String> headers = Arrays.asList("动态表头1", "动态表头2", "动态表头3");
    private static final List<String> dropCells = Arrays.asList("测试1", "测试2");

    /**
     * 准备动态表头数据
     * @param
     * @return {@link List< List< String>>}
     * @author root
     * @date 2022-11-16
     */
    private List<List<String>> prepareHeaders() {
        List<List<String>> list = headers.stream().map(item -> Arrays.asList(item)).collect(Collectors.toList());
        return list;
    }

    /**
     * 准备单元格下拉数据
     * @param
     * @return {@link HashMap< Integer, List< String>>}
     * @author root
     * @date 2022-11-16
     */
    private HashMap<Integer, List<String>> prepareDropCells() {
        HashMap<Integer, List<String>> dropCellMap = new HashMap<>();

        for(int i = 0; i < headers.size(); i++) {
            if(headers.get(i).equals("动态表头1")) {
                dropCellMap.put(i, dropCells);
            }
        }
        return dropCellMap;
    }

}

自定义拦截器

package com.alibaba.easyexcel.test.demo.write;

import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.handler.context.SheetWriteHandlerContext;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.util.CellRangeAddressList;

import java.util.HashMap;
import java.util.List;

/**
 * 自定义拦截器. 新增单元格下拉框数据
 *
 * @author root
 */
@Slf4j
public class DynamicSheetWriteHandler implements SheetWriteHandler {

    private HashMap<Integer, List<String>> dropCellMap;

    public DynamicSheetWriteHandler(HashMap<Integer, List<String>> dropCellMap) {
        this.dropCellMap = dropCellMap;
    }

    @Override
    public void afterSheetCreate(SheetWriteHandlerContext context) {
        log.info("第{}个Sheet写入成功。", context.getWriteSheetHolder().getSheetNo());

        dropCellMap.forEach((k, v) -> {
            CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(1, 65535, k, k);
            DataValidationHelper helper = context.getWriteSheetHolder().getSheet().getDataValidationHelper();
            DataValidationConstraint constraint = helper.createExplicitListConstraint(v.toArray(new String[0]));
            DataValidation dataValidation = helper.createValidation(constraint, cellRangeAddressList);

            //设置约束
            if(dataValidation instanceof HSSFDataValidation) {
                dataValidation.setSuppressDropDownArrow(false);
            } else {
                dataValidation.setSuppressDropDownArrow(true);
                dataValidation.setShowErrorBox(true);
            }
            // 禁止输入非下拉框的内容
            dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
            dataValidation.createErrorBox("提示", "此值与单元格下拉不符");
            context.getWriteSheetHolder().getSheet().addValidationData(dataValidation);
        });

    }
}

结果展示：