POI导出设置下拉框不报错却失效

设置下拉框的代码，网上都有，我看了下，有三种

1.针对HSSFWorkbook

参考自：https://www.cnblogs.com/zhutouying/p/3447309.html

/** 
     * 设置某些列的值只能输入预制的数据,显示下拉框. 
     * @param HSSFSheet sheet  模板sheet页（需要设置下拉框的sheet）  
     * @param String[] textlist 下拉框显示的内容 
     * @param int firstRow  添加下拉框对应开始行 
     * @param int endRow    添加下拉框对应结束行 
     * @param int firstCol  添加下拉框对应开始列 
     * @param int endCol    添加下拉框对应结束列 
     * @return HSSFSheet 设置好的sheet. 
     */  
//HSSFWorkbook 
    public static HSSFSheet setHSSFValidation(HSSFSheet sheet, String[] textlist, int firstRow, int endRow, int firstCol, int endCol){  
        // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列  
        CellRangeAddressList regions = new CellRangeAddressList(firstRow,endRow, firstCol, endCol);  
        // 加载下拉列表内容  
        DVConstraint constraint = DVConstraint.createExplicitListConstraint(textlist);  
        // 数据有效性对象  
        HSSFDataValidation data_validation_list = new HSSFDataValidation(regions, constraint);  
        sheet.addValidationData(data_validation_list);  
        return sheet;  
    }  

2.针对XSSFWorkbook 

参考自：https://blog.csdn.net/sdaujsj1/article/details/81004209

//XSSFWorkbook 
public static void dropDownList42007(String filePath)
        throws Exception {
    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet("下拉列表测试");
    String[] datas = new String[] {"维持","恢复","调整"};
    XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
    XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper
            .createExplicitListConstraint(datas);
    CellRangeAddressList addressList = null;
    XSSFDataValidation validation = null;
    for (int i = 0; i < 100; i++) {
        addressList = new CellRangeAddressList(i, i, 0, 0);
        validation = (XSSFDataValidation) dvHelper.createValidation(
                dvConstraint, addressList);
        // 07默认setSuppressDropDownArrow(true);
        // validation.setSuppressDropDownArrow(true);
        // validation.setShowErrorBox(true);
        sheet.addValidationData(validation);
    }
    FileOutputStream stream = new FileOutputStream(filePath);
    workbook.write(stream);
    stream.close();
    addressList = null;
    validation = null;
}

 

3.两种都兼容的

/**
	 * <p>
	 * Description: 设置下拉框到excel
	 * </p>
	 * @date 2020年1月6日 下午3:13:50
	 * @author ***
	 * @param sheet
	 * @param strs 下拉框中的值
	 * @param firstRow 下标
	 * @param lastRow
	 * @param firstCol
	 * @param lastCol
	 */
	private void setDropdownToExcel(Sheet sheet,String[] strs, int firstRow, int lastRow, int firstCol, int lastCol ){
		CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol)  ;
		DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
		DataValidationConstraint createExplicitListConstraint = dataValidationHelper.createExplicitListConstraint(strs);
		DataValidation createValidation = dataValidationHelper.createValidation(createExplicitListConstraint, regions);
	     //处理Excel兼容性问题
        if (createValidation instanceof XSSFDataValidation) {
            createValidation.setSuppressDropDownArrow(true);
            createValidation.setShowErrorBox(true);
        } else {
            createValidation.setSuppressDropDownArrow(false);
        }
        sheet.addValidationData(createValidation);	
	}

 

 

我用的是第三种

情况1：先往excel中写入数据，再设置下拉框，没有报错，下拉框也没有生成，如图

 

 

 情况2：不向excel中写入数据，单独设置下拉框，设置成功，如图

 情况3：先设置下拉框，再写入数据，设置成功，如图