导出带下拉选项的Excel基于EasyExcel实现

EasyExcel导出表格固定列生成下拉框并且下拉框数据突破255限制，采用的方式是生成虚拟sheet页

1、实现 SheetWriteHandler方法，代码如下

该处理器仅支持固定列生成下拉框，可动态指定下拉框行数范围

package com.shareworx.handler;

import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * 导出excel固定列增加下拉框处理器(通用模板)
 *
 * @author yong.li_1
 * @version since Shareworx platform 4.0.6
 */
@Slf4j
@Data
@AllArgsConstructor
@NoArgsConstructor
public class BuildSpinnerWriteHandler implements SheetWriteHandler {

    private Map<Integer, List<String>> dropDownMap;  // 下拉框数据源的映射，key为列索引，value为下拉框数据源


    private int firstRow = 6; // 起始行

    private int lastRow = 45000;  // 结束行


    public BuildSpinnerWriteHandler(Map<Integer, List<String>> dropDownMap) {
        this.dropDownMap = dropDownMap;
    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

        Sheet sheet = writeSheetHolder.getSheet();
        List<String> sheetNames = new ArrayList<>();

        for (Map.Entry<Integer, List<String>> entry : dropDownMap.entrySet()) {
            int columnIndex = entry.getKey();
            List<String> dropDownValues = entry.getValue();
            if (CollectionUtils.isEmpty(dropDownValues)) {
                continue;
            }

            // 突破下拉框255的限制
            String sheetName = "_" + columnIndex + "sheet";
            if (!sheetNames.contains(sheetName)) {
                //获取一个workbook
                Workbook workbook = writeWorkbookHolder.getWorkbook();
                //定义sheet的名称
                //1.创建一个隐藏的sheet 名称为 proviceSheet
                Sheet proviceSheet = workbook.createSheet(sheetName);
                // 设置隐藏
                workbook.setSheetHidden(sheetNames.size() + 1, true);
                //2.循环赋值（为了防止下拉框的行数与隐藏域的行数相对应，将隐藏域加到结束行之后）
                for (int j = 0, length = dropDownValues.size(); j < length; j++) {
                    // i:表示你开始的行数  0表示你开始的列数
                    proviceSheet.createRow(j).createCell(0).setCellValue(dropDownValues.get(j));
                }
                Name category1Name = workbook.createName();
                category1Name.setNameName(sheetName);
                //4 $A$1:$A$N代表 以A列1行开始获取N行下拉数据
                category1Name.setRefersToFormula(sheetName + "!$A$1:$A$" + (dropDownValues.size()));
                sheetNames.add(sheetName);
            }
            //5 将刚才设置的sheet引用到你的下拉列表中
            CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, columnIndex, columnIndex);
            DataValidationConstraint constraint8 = sheet.getDataValidationHelper().createFormulaListConstraint(sheetName);
            DataValidation dataValidation3 = sheet.getDataValidationHelper().createValidation(constraint8, addressList);
            writeSheetHolder.getSheet().addValidationData(dataValidation3);

        }
    }
}

2、处理器如何使用代码如下

package com.shareworx.ho;

import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.util.ListUtils;
import com.shareworx.handler.BuildSpinnerWriteHandler;
import org.apache.commons.compress.utils.Lists;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author yong.li_1
 **/
public class ExcelExportTest {
    public static void main(String[] args) {
        String fileName =  "E:\\" + System.currentTimeMillis() + ".xlsx";

        // 下拉框数据
        Map<Integer, List<String>> dropDownMap = new HashMap<>();
        List<String> list1 = new ArrayList<>();
        for (int i = 0; i < 100; i++) {
            list1.add("字符串" + i);
        }
        List<String> list2 = new ArrayList<>();
        for (int i = 0; i < 100; i++) {
            list2.add("字符" + i);
        }
        // 第一列
        dropDownMap.put(0, list1);
        // 第二列
        dropDownMap.put(1, list2);

        EasyExcelFactory.write(fileName).registerWriteHandler(new BuildSpinnerWriteHandler(dropDownMap, 1, 255))
                // 这里放入动态头
                .head(head()).sheet("模板").doWrite(Lists.newArrayList());
    }


    private static List<List<String>> head() {
        List<List<String>> list = ListUtils.newArrayList();
        List<String> head0 = ListUtils.newArrayList();
        head0.add("字符串" + System.currentTimeMillis());
        List<String> head1 = ListUtils.newArrayList();
        head1.add("字符" + System.currentTimeMillis());
        list.add(head0);
        list.add(head1);
        return list;
    }
}

3、导出excel表格效果如下