org.apache.poi.xssf.usermodel.XSSFDataValidation Java Examples

The following examples show how to use org.apache.poi.xssf.usermodel.XSSFDataValidation. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example #1
Source File: ExcelUtil.java    From supplierShop with MIT License 8 votes vote down vote up 
/**
 * 设置某些列的值只能输入预制的数据,显示下拉框.
 * 
 * @param sheet 要设置的sheet.
 * @param textlist 下拉框显示的内容
 * @param firstRow 开始行
 * @param endRow 结束行
 * @param firstCol 开始列
 * @param endCol 结束列
 * @return 设置好的sheet.
 */
public void setXSSFValidation(Sheet sheet, String[] textlist, int firstRow, int endRow, int firstCol, int endCol)
{
    DataValidationHelper helper = sheet.getDataValidationHelper();
    // 加载下拉列表内容
    DataValidationConstraint constraint = helper.createExplicitListConstraint(textlist);
    // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
    CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
    // 数据有效性对象
    DataValidation dataValidation = helper.createValidation(constraint, regions);
    // 处理Excel兼容性问题
    if (dataValidation instanceof XSSFDataValidation)
    {
        dataValidation.setSuppressDropDownArrow(true);
        dataValidation.setShowErrorBox(true);
    }
    else
    {
        dataValidation.setSuppressDropDownArrow(false);
    }

    sheet.addValidationData(dataValidation);
}
Example #2
Source File: ExcelUtil.java    From RuoYi-Vue with MIT License 7 votes vote down vote up 
/**
 * 设置某些列的值只能输入预制的数据,显示下拉框.
 * 
 * @param sheet 要设置的sheet.
 * @param textlist 下拉框显示的内容
 * @param firstRow 开始行
 * @param endRow 结束行
 * @param firstCol 开始列
 * @param endCol 结束列
 * @return 设置好的sheet.
 */
public void setXSSFValidation(Sheet sheet, String[] textlist, int firstRow, int endRow, int firstCol, int endCol)
{
    DataValidationHelper helper = sheet.getDataValidationHelper();
    // 加载下拉列表内容
    DataValidationConstraint constraint = helper.createExplicitListConstraint(textlist);
    // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
    CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
    // 数据有效性对象
    DataValidation dataValidation = helper.createValidation(constraint, regions);
    // 处理Excel兼容性问题
    if (dataValidation instanceof XSSFDataValidation)
    {
        dataValidation.setSuppressDropDownArrow(true);
        dataValidation.setShowErrorBox(true);
    }
    else
    {
        dataValidation.setSuppressDropDownArrow(false);
    }

    sheet.addValidationData(dataValidation);
}
Example #3
Source File: ExcelUtil.java    From v-mock with MIT License 7 votes vote down vote up 
/**
 * 设置某些列的值只能输入预制的数据,显示下拉框.
 *
 * @param sheet    要设置的sheet.
 * @param textlist 下拉框显示的内容
 * @param firstRow 开始行
 * @param endRow   结束行
 * @param firstCol 开始列
 * @param endCol   结束列
 * @return 设置好的sheet.
 */
public void setXssValidation(Sheet sheet, String[] textlist, int firstRow, int endRow, int firstCol, int endCol) {
    DataValidationHelper helper = sheet.getDataValidationHelper();
    // 加载下拉列表内容
    DataValidationConstraint constraint = helper.createExplicitListConstraint(textlist);
    // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
    CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
    // 数据有效性对象
    DataValidation dataValidation = helper.createValidation(constraint, regions);
    // 处理Excel兼容性问题
    if (dataValidation instanceof XSSFDataValidation) {
        dataValidation.setSuppressDropDownArrow(true);
        dataValidation.setShowErrorBox(true);
    } else {
        dataValidation.setSuppressDropDownArrow(false);
    }

    sheet.addValidationData(dataValidation);
}
Example #4
Source File: CommonsUtils.java    From czy-nexus-commons-utils with Apache License 2.0 7 votes vote down vote up 
/**
 * 功能描述:下拉列表
 *
 * @param xssfWsheet
 * @param list
 * @param firstRow
 * @param lastRow
 * @param firstCol
 * @param lastCol
 */
public static void setDataValidation(SXSSFSheet xssfWsheet, String[] list, Integer firstRow, Integer lastRow, Integer firstCol, Integer lastCol) {
    DataValidationHelper helper = xssfWsheet.getDataValidationHelper();
    CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
    DataValidationConstraint constraint = helper.createExplicitListConstraint(list);
    DataValidation dataValidation = helper.createValidation(constraint, addressList);
    dataValidation.createErrorBox(DataValidationError1, DataValidationError2);
    //  处理Excel兼容性问题
    if (dataValidation instanceof XSSFDataValidation) {
        dataValidation.setSuppressDropDownArrow(true);
        dataValidation.setShowErrorBox(true);
    } else {
        dataValidation.setSuppressDropDownArrow(false);
    }
    xssfWsheet.addValidationData(dataValidation);
}
Example #5
Source File: ExcelUtil.java    From RuoYi with Apache License 2.0 7 votes vote down vote up 
/**
 * 设置某些列的值只能输入预制的数据,显示下拉框.
 * @param sheet    要设置的sheet.
 * @param textlist 下拉框显示的内容
 * @param firstCol 开始列
 * @param endCol   结束列
 */
private static void setXSSFValidation(Sheet sheet, String[] textlist, int firstCol, int endCol) {
    DataValidationHelper helper = sheet.getDataValidationHelper();
    // 加载下拉列表内容
    DataValidationConstraint constraint = helper.createExplicitListConstraint(textlist);
    // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
    CellRangeAddressList regions = new CellRangeAddressList(1, 100, firstCol, endCol);
    // 数据有效性对象
    DataValidation dataValidation = helper.createValidation(constraint, regions);
    // 处理Excel兼容性问题
    if (dataValidation instanceof XSSFDataValidation){
        dataValidation.setSuppressDropDownArrow(true);
        dataValidation.setShowErrorBox(true);
    }else{
        dataValidation.setSuppressDropDownArrow(false);
    }
    sheet.addValidationData(dataValidation);
}
Example #6
Source File: ExcelUtil.java    From LuckyFrameWeb with GNU Affero General Public License v3.0 7 votes vote down vote up 
/**
 * 设置某些列的值只能输入预制的数据,显示下拉框.
 * 
 * @param sheet 要设置的sheet.
 * @param textlist 下拉框显示的内容
 * @param firstRow 开始行
 * @param endRow 结束行
 * @param firstCol 开始列
 * @param endCol 结束列
 * @return 设置好的sheet.
 */
public static Sheet setHSSFValidation(Sheet sheet, String[] textlist, int firstRow, int endRow, int firstCol,
        int endCol)
{
    DataValidationHelper helper = sheet.getDataValidationHelper();
    // 加载下拉列表内容
    DataValidationConstraint constraint = helper.createExplicitListConstraint(textlist);
    // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
    CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
    // 数据有效性对象
    DataValidation dataValidation = helper.createValidation(constraint, regions);
    // 处理Excel兼容性问题
    if (dataValidation instanceof XSSFDataValidation)
    {
        dataValidation.setSuppressDropDownArrow(true);
        dataValidation.setShowErrorBox(true);
    }
    else
    {
        dataValidation.setSuppressDropDownArrow(false);
    }

    sheet.addValidationData(dataValidation);
    return sheet;
}
Example #7
Source File: GenerateDoc.java    From danyuan-application with Apache License 2.0 7 votes vote down vote up 
private static void setDataValidationList(int j, int k, int l, int m, String data, XSSFSheet sheet) {
	// 设置下拉列表的内容
	String[] textlist = data.split(",");
	// 加载下拉列表内容
	// 设置数据有效性加载在哪个单元格上。
	XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
	XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(textlist);
	// 四个参数分别是：起始行、终止行、起始列、终止列
	CellRangeAddressList regions = new CellRangeAddressList(j, k, l, m);
	/*   CellRangeAddressList regions = new CellRangeAddressList(
	      6,5, 6,5);*/
	// 数据有效性对象
	XSSFDataValidation data_validation_list = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, regions);
	// data_validation_list.setSuppressDropDownArrow(false);
	
	sheet.addValidationData(data_validation_list);
}
Example #8
Source File: ExcelUtil.java    From albedo with GNU Lesser General Public License v3.0 7 votes vote down vote up 
/**
 * 设置某些列的值只能输入预制的数据,显示下拉框.
 *
 * @param sheet    要设置的sheet.
 * @param textlist 下拉框显示的内容
 * @param firstRow 开始行
 * @param endRow   结束行
 * @param firstCol 开始列
 * @param endCol   结束列
 * @return 设置好的sheet.
 */
public void setXssfValidation(Sheet sheet, String[] textlist, int firstRow, int endRow, int firstCol, int endCol) {
	DataValidationHelper helper = sheet.getDataValidationHelper();
	// 加载下拉列表内容
	DataValidationConstraint constraint = helper.createExplicitListConstraint(textlist);
	// 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
	CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
	// 数据有效性对象
	DataValidation dataValidation = helper.createValidation(constraint, regions);
	// 处理Excel兼容性问题
	if (dataValidation instanceof XSSFDataValidation) {
		dataValidation.setSuppressDropDownArrow(true);
		dataValidation.setShowErrorBox(true);
	} else {
		dataValidation.setSuppressDropDownArrow(false);
	}

	sheet.addValidationData(dataValidation);
}
Example #9
Source File: AbstractExcelFactory.java    From myexcel with Apache License 2.0 7 votes vote down vote up 
private String setDropDownList(Td td, Sheet sheet, String content) {
    if (content.length() > 250) {
        throw new IllegalArgumentException("The total number of words in the drop-down list should not exceed 250.");
    }
    CellRangeAddressList addressList = new CellRangeAddressList(
            td.getRow(), td.getRowBound(), td.getCol(), td.getColBound());
    DataValidationHelper dvHelper = sheet.getDataValidationHelper();
    String[] list = content.split(",");
    DataValidationConstraint dvConstraint = dvHelper.createExplicitListConstraint(list);
    DataValidation validation = dvHelper.createValidation(
            dvConstraint, addressList);
    if (validation instanceof XSSFDataValidation) {
        validation.setSuppressDropDownArrow(true);
        validation.setShowErrorBox(true);
    } else {
        validation.setSuppressDropDownArrow(false);
    }
    sheet.addValidationData(validation);
    if (list.length > 0) {
        return list[0];
    }
    return null;
}