Easyexcel导出带下拉框选项excel模板（解决下拉框超50个的问题）

本文主要是介绍Easyexcel导出带下拉框选项excel模板（解决下拉框超50个的问题），对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

1、为了避免excel下拉框选项过多会导致内容不显示（或者生成的时候报错：String literals in formulas can't be bigger than 255 characters ASCII easyexcel），将下拉框的内容都存储在另一个新建的固定的sheet页，再通过引用公式关联单元格的下拉框内容，从而形成能够存储多数值的下拉框。

2、导出代码（这里演示的是一次性导出多个模板，采用压缩包的形式下载，并且表头是动态的）：

/**     * 导出模板     * @param response     * @param businessViewId     */    @Override    public void exportTemplate(HttpServletResponse response, String businessViewId) throws IOException {        String[] split = businessViewId.split(",");         String zipName = "模板.zip";        response.setCharacterEncoding("utf-8");        response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);        response.setHeader("Content-Disposition", "attachment;filename*=UTF-8''" + URLEncoder.encode(zipName, "UTF-8"));        ServletOutputStream out = response.getOutputStream();        ZipOutputStream zipOutputStream = new ZipOutputStream(out);         for (String viewid : split) {             List<BusinessField> field = baseMapper.findBusinessFieldByViewId(viewid);            //根据表id查询父表id            String parentId = this.findParentIdByViewId(viewid);            List<BusinessField> parentFieldList = baseMapper.findBusinessFieldByViewId(parentId);            //定义表头            List<List<String>> headList = new ArrayList<>();            //定义数据体            List<List<Object>> dataList = new ArrayList<>();            // 指定标红色的列            List<Integer> columns = Arrays.asList();            // 指定批注            HashMap<Integer, String> annotationsMap = new HashMap<>();            HashMap<Integer, List<String>> dropDownMap = new HashMap<>();            //主表字段            for (int i = 0;i<parentFieldList.size();i++){                BusinessField businessField = parentFieldList.get(i);                headList.add(Lists.newArrayList(businessField.getName()));                if (StringUtils.isNotBlank(businessField.getControlType())){                    if (businessField.getControlType().contains("select")){                        List<String> tDataDictionaries = tDataDictionaryTempMapper.getNameByPid(businessField.getDictionary());//                        存储需要下拉框的值，这里的key是需要设置为下拉框的列数，value是下拉框的值，是list                        if (tDataDictionaries != null && tDataDictionaries.size()>0) {                            dropDownMap.put(i,tDataDictionaries);                        }                    }                }            }            //子表字段            for (int i = 0;i<field.size();i++){                BusinessField businessField = field.get(i);                headList.add(Lists.newArrayList(businessField.getName()));                if (StringUtils.isNotBlank(businessField.getControlType())){                    if (businessField.getControlType().contains("select")){                        List<String> tDataDictionaries = tDataDictionaryTempMapper.getNameByPid(businessField.getDictionary());//                         存储需要下拉框的值，这里的key是需要设置为下拉框的列数，value是下拉框的值，是list                        if (tDataDictionaries != null && tDataDictionaries.size()>0){                            dropDownMap.put(i+parentFieldList.size(),tDataDictionaries);                        }                    }                }            }            ExcelWriter excelWriter = EasyExcel.write().excelType(ExcelTypeEnum.XLS).build();            //构建一个sheet页            WriteSheet writeSheet = EasyExcel.writerSheet("sheet1").build();//            TltleHandler titleHandler = new TltleHandler(columns, IndexedColors.RED.index,annotationsMap,dropDownMap);//            ExayExcelUtils.writeExcelWithModel(response.getOutputStream(), dataList, headList, "sheet1", (CellWriteHandler) titleHandler);// 头的策略            WriteCellStyle headWriteCellStyle = new WriteCellStyle();            // 单元格策略            WriteCellStyle contentWriteCellStyle = new WriteCellStyle();            // 初始化表格样式            HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);// SelectSheetWriteHandler(dropDownMap)  是设置下拉框的类            WriteTable writeTable = EasyExcel.writerTable(0).head(headList).registerWriteHandler(horizontalCellStyleStrategy).registerWriteHandler(new SelectSheetWriteHandler(dropDownMap)).needHead(Boolean.TRUE).build();            excelWriter.write(dataList, writeSheet, writeTable);            // 开始导出//            excelWriterSheetBuilder.doWrite(dataList);            Workbook workbook = excelWriter.writeContext().writeWorkbookHolder().getWorkbook();            //创建压缩文件            String nameByid = baseMapper.getNameByid(viewid);            ZipEntry zipEntry = new ZipEntry(nameByid+".xls");            zipOutputStream.putNextEntry(zipEntry);             //将excel对象以流的形式写入压缩流            workbook.write(zipOutputStream);         }         zipOutputStream.flush();        zipOutputStream.close();     }

3、设置下拉框的类：

package com.customization.BusinessFilIdExcel.utils; 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; public class SelectSheetWriteHandler implements SheetWriteHandler {     private Map<Integer, List<String>> selectMap;     private int index;     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;        this.index = 0;    }     @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);        // 从第二个工作簿开始隐藏，为了用户的友好性，将字典sheet隐藏掉        this.index++;        // 设置隐藏        workbook.setSheetHidden(this.index, true);        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;     } }

4、效果如下：

这篇关于Easyexcel导出带下拉框选项excel模板（解决下拉框超50个的问题）的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！