EasyExcel 实现自定义单选下拉框

目录

1 Maven依赖

2 SpinnerModel

3 CustomSpinnerHandler

4 调试代码

5 调试结果

注：

---

1 Maven依赖

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <!-- EasyExcel文档处理工具 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.8</version>
        </dependency>
        <!--    hutool工具包    -->
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.6.2</version>
        </dependency>

2 SpinnerModel

下拉框信息类。

package com.xudongbase.easyexcel.model;

import cn.hutool.core.util.StrUtil;
import com.xudongbase.common.poi.constant.POIExcelConstant;
import com.xudongbase.easyexcel.model.common.SheetRangeModel;
import lombok.Getter;

/**
 * 下拉框信息类
 *
 * @author xudongmaster
 */
@Getter
public class SpinnerModel extends SheetRangeModel {
    /**
     * 下拉框数据
     */
    private String[] spinnerData;

    private SpinnerModel() {
    }

    /**
     * 生成下拉框信息
     *
     * @param sheetName     sheet页名称
     * @param colIndex      列索引
     * @param startRowIndex 开始行索引
     * @param spinnerData   下拉框数据
     * @return
     */
    public static SpinnerModel createSpinnerModel(String sheetName, Integer colIndex, Integer startRowIndex, String[] spinnerData) {
        return createSpinnerModel(sheetName, colIndex, colIndex, startRowIndex, POIExcelConstant.ROW_INDEX_MAX, spinnerData);
    }

    /**
     * 生成下拉框信息
     *
     * @param sheetName     sheet页名称
     * @param startColumnIndex 开始列索引
     * @param endColumnIndex   结束列索引
     * @param startRowIndex 开始行索引
     * @param endRowIndex   结束行索引
     * @param spinnerData   下拉框数据
     */
    public static SpinnerModel createSpinnerModel(String sheetName, Integer startColumnIndex, Integer endColumnIndex, Integer startRowIndex, Integer endRowIndex, String[] spinnerData) {
        SpinnerModel spinnerModel = new SpinnerModel();
        //sheet页名称
        spinnerModel.sheetName = StrUtil.isNotBlank(sheetName) ? sheetName : POIExcelConstant.SHEET_NAME_DEFAULT;
        //开始列索引
        startColumnIndex = startColumnIndex != null ? startColumnIndex : POIExcelConstant.COL_INDEX_MIN;
        spinnerModel.startColumnIndex = startColumnIndex;
        //结束列索引
        endColumnIndex = endColumnIndex != null ? endColumnIndex : startColumnIndex;
        spinnerModel.endColumnIndex = endColumnIndex;
        //开始行索引
        startRowIndex = startRowIndex != null ? startRowIndex : POIExcelConstant.ROW_INDEX_MIN;
        spinnerModel.startRowIndex = startRowIndex;
        //结束行索引
        endRowIndex = endRowIndex != null ? endRowIndex : POIExcelConstant.ROW_INDEX_MAX;
        spinnerModel.endRowIndex = endRowIndex;
        //下拉框数据
        spinnerData = spinnerData != null ? spinnerData : new String[]{};
        spinnerModel.spinnerData = spinnerData;
        return spinnerModel;
    }


}

3 CustomSpinnerHandler

自定义下拉框处理器。

package com.xudongbase.easyexcel.handler;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import com.xudongbase.common.poi.constant.POIExcelConstant;
import com.xudongbase.common.poi.util.POIExcelUtil;
import com.xudongbase.easyexcel.model.SpinnerModel;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.Sheet;

import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;

/**
 * 自定义下拉框处理器
 *
 * @author xudongmaster
 */
public class CustomSpinnerHandler implements SheetWriteHandler {
    /**
     * 下拉框信息列表
     */
    List<SpinnerModel> spinnerList = new ArrayList<>();
    /**
     * sheet页名称列表
     */
    List<String> sheetNameList = new ArrayList<>();

    /**
     * 构造方法
     *
     * @param spinnerList 下拉框信息列表
     */
    public CustomSpinnerHandler(List<SpinnerModel> spinnerList) {
        this.spinnerList = CollectionUtil.isNotEmpty(spinnerList) ? spinnerList.stream().filter(x ->
                StrUtil.isNotEmpty(x.getSheetName()) && x.getSpinnerData() != null
                        && x.getSpinnerData().length > 0
        ).collect(Collectors.toList()) : new ArrayList<>();
        this.sheetNameList = CollectionUtil.isNotEmpty(this.spinnerList) ? this.spinnerList.stream().map(x ->
                x.getSheetName()).distinct().collect(Collectors.toList()) : new ArrayList<>();
    }

    /**
     * Sheet页创建之前
     *
     * @param writeWorkbookHolder
     * @param writeSheetHolder
     */
    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

    }

    /**
     * Sheet页创建之后
     *
     * @param writeWorkbookHolder
     * @param writeSheetHolder
     */
    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        Sheet sheet = writeSheetHolder.getSheet();
        //不需要绑定下拉框，或者当前sheet页不需要绑定下拉框
        if (CollUtil.isEmpty(spinnerList) || sheetNameList.contains(sheet.getSheetName()) == false) {
            return;
        }
        //当前Sheet页的下拉框信息
        List<SpinnerModel> sheetSpinnerList = spinnerList.stream().filter(x ->
                StrUtil.equals(x.getSheetName(), sheet.getSheetName())).collect(Collectors.toList());
        //当前sheet页不需要绑定下拉框
        if (CollUtil.isEmpty(sheetSpinnerList)) {
            return;
        }
        DataValidationHelper helper = sheet.getDataValidationHelper();
        //设置下拉框
        for (SpinnerModel spinnerModel : sheetSpinnerList) {
            //校验开始列索引大于结束列索引，或者开始行索引大于结束行索引
            if (spinnerModel.getStartColumnIndex() > spinnerModel.getEndColumnIndex() || spinnerModel.getStartRowIndex() > spinnerModel.getEndRowIndex()) {
                continue;
            }
            //校验开始列索引和结束列索引是否小于最小列索引
            if (spinnerModel.getStartColumnIndex() < POIExcelConstant.COL_INDEX_MIN || spinnerModel.getEndColumnIndex() < POIExcelConstant.COL_INDEX_MIN) {
                continue;
            }
            //校验开始行索引和结束行索引是否小于最小行索引，开始行索引和结束行索引是否大于最大行索引
            if (spinnerModel.getStartRowIndex() < POIExcelConstant.ROW_INDEX_MIN || spinnerModel.getEndRowIndex() < POIExcelConstant.ROW_INDEX_MIN
                    || spinnerModel.getStartRowIndex() > POIExcelConstant.ROW_INDEX_MAX || spinnerModel.getEndRowIndex() > POIExcelConstant.ROW_INDEX_MAX) {
                continue;
            }
            //绑定下拉框数据
            POIExcelUtil.setSpinnerData(writeWorkbookHolder.getWorkbook(), sheet, spinnerModel.getStartRowIndex(), spinnerModel.getEndRowIndex()
                    , spinnerModel.getStartRowIndex(), spinnerModel.getEndColumnIndex(), spinnerModel.getSpinnerData());
        }
    }
}

4 调试代码

    /**
     * 调试绑定下拉框方法
     *
     * @param response
     */
    @GetMapping("/testSpinner")
    public void testSpinner(HttpServletResponse response) throws IOException {
        //生成表格数据
        List<List<Object>> dataList = new ArrayList<>();
        dataList.add(new ArrayList<>(Arrays.asList(new Object[]{"表头11", "表头2", "表头3", "表头4"})));
        dataList.add(new ArrayList<>(Arrays.asList(new Object[]{"表头1", "表头2", "表头3", "表头4"})));
        dataList.add(new ArrayList<>(Arrays.asList(new Object[]{"表头31", "表头2", "表头3", "表头4"})));
        List<SpinnerModel> spinnerList = new ArrayList<>();
        //下拉框数据
        String[] spinnerData = new String[]{"是", "否"};
        spinnerList.add(SpinnerModel.createSpinnerModel("模板", 1, 1, spinnerData));
        spinnerList.add(SpinnerModel.createSpinnerModel("模板", 2, 2, spinnerData));
        response.addHeader("Content-Disposition", "attachment;filename=test.xlsx");
        //设置类型，扩展名为.xlsx
        response.setContentType("application/octet-stream");
        ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream())
                .registerWriteHandler(new CustomSpinnerHandler(spinnerList))
                .build();
        WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
        excelWriter.write(dataList, writeSheet);
        //千万别忘记finish 会帮忙关闭流
        excelWriter.finish();
    }  

5 调试结果

注：

觉得这篇博客写的不错的可以前往Gitee点个Star，源码请查看Gitee的xudongbase项目easyexcel分支。

xudongbase: 主要是项目中可以用到的共通方法 - Gitee.comhttps://gitee.com/xudong_master/xudongbase/tree/easyexcel/