Easyexcel导出带下拉框选项excel模板

需求：导出的excel模板，表头字段动态生成、sheet页下拉框内容动态生成

解决思路：为了避免excel下拉框选项过多会导致内容不显示，将下拉框的内容都存储在另一个新建的固定的sheet页，再通过引用公式关联单元格的下拉框内容。

maven依赖

 
<dependency>
 
<groupId>com.alibaba</groupId>
 
<artifactId>easyexcel</artifactId>
 
<version>2.2.7</version>
 
</dependency>
 

 

导出代码

@RequestMapping("/download")
public void download(HttpServletResponse response) throws IOException {
String fileName = "test";
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf-8");
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
String fileNameEncode = URLEncoder.encode(fileName, "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileNameEncode + ExcelTypeEnum.XLS.getValue());
// 模拟下拉框内容
Map<Integer, List<String>> selectMap = new HashMap<>();
List<String> sexList = new ArrayList<>();
sexList.add("男");
sexList.add("女");
selectMap.put(1, sexList);
List<String> typeList = new ArrayList<>();
typeList.add("股票");
typeList.add("基金");
typeList.add("债券");
selectMap.put(2, typeList);
//模拟表头
List<List<String>> list = new ArrayList<>();
List<String> field1 = new ArrayList<>();
List<String> field2 = new ArrayList<>();
List<String> field3 = new ArrayList<>();
field1.add("编号");
field2.add("性别");
field3.add("类型");
list.add(field1);
list.add(field2);
list.add(field3);
EasyExcelFactory.write(response.getOutputStream())
.registerWriteHandler(new SelectSheetWriteHandler(selectMap))
.excelType(ExcelTypeEnum.XLS)
.head(list)
.sheet("测试sheet")
.doWrite(new ArrayList());
}

下拉框设置代码:

package com.test.excel;
 
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
 
import java.util.List;
import java.util.Map;
 
/**
 * @author 
 * @date 2021/12/20
 */
public class SelectSheetWriteHandler implements SheetWriteHandler {
    private Map<Integer, List<String>> selectMap;
 
    private char[] alphabet = new char[]{'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L',
            'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'};
 
    public SelectSheetWriteHandler(Map<Integer, List<String>> selectMap) {
        this.selectMap = selectMap;
    }
 
    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
    }
 
    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        if (selectMap == null || selectMap.size() == 0) {
            return;
        }
        // 需要设置下拉框的sheet页
        Sheet curSheet = writeSheetHolder.getSheet();
        DataValidationHelper helper = curSheet.getDataValidationHelper();
        String dictSheetName = "字典sheet";
        Workbook workbook = writeWorkbookHolder.getWorkbook();
        // 数据字典的sheet页
        Sheet dictSheet = workbook.createSheet(dictSheetName);
        for (Map.Entry<Integer, List<String>> entry : selectMap.entrySet()) {
            // 设置下拉单元格的首行、末行、首列、末列
            CellRangeAddressList rangeAddressList = new CellRangeAddressList(1, 65533, entry.getKey(), entry.getKey());
            int rowLen = entry.getValue().size();
            // 设置字典sheet页的值 每一列一个字典项
            for (int i = 0; i < rowLen; i++) {
                Row row = dictSheet.getRow(i);
                if (row == null) {
                    row = dictSheet.createRow(i);
                }
                row.createCell(entry.getKey()).setCellValue(entry.getValue().get(i));
            }
            String excelColumn = getExcelColumn(entry.getKey());
            // 下拉框数据来源 eg:字典sheet!$B1:$B2
            String refers = dictSheetName + "!$" + excelColumn + "$1:$" + excelColumn + "$" + rowLen;
            // 创建可被其他单元格引用的名称
            Name name = workbook.createName();
            // 设置名称的名字
            name.setNameName("dict" + entry.getKey());
            // 设置公式
            name.setRefersToFormula(refers);
            // 设置引用约束
            DataValidationConstraint constraint = helper.createFormulaListConstraint("dict" + entry.getKey());
            // 设置约束
            DataValidation validation = helper.createValidation(constraint, rangeAddressList);
            if (validation instanceof HSSFDataValidation) {
                validation.setSuppressDropDownArrow(false);
            } else {
                validation.setSuppressDropDownArrow(true);
                validation.setShowErrorBox(true);
            }
            // 阻止输入非下拉框的值
            validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
            validation.createErrorBox("提示", "此值与单元格定义格式不一致！");
            // 添加下拉框约束
            writeSheetHolder.getSheet().addValidationData(validation);
        }
    }
 
    /**
     * 将数字列转化成为字母列
     *
     * @param num
     * @return
     */
    private String getExcelColumn(int num) {
        String column = "";
        int len = alphabet.length - 1;
        int first = num / len;
        int second = num % len;
        if (num <= len) {
            column = alphabet[num] + "";
        } else {
            column = alphabet[first - 1] + "";
            if (second == 0) {
                column = column + alphabet[len] + "";
            } else {
                column = column + alphabet[second - 1] + "";
            }
        }
        return column;
    }
}

如图：