Java Code Examples for org.apache.poi.ss.usermodel.Sheet#getDataValidationHelper()

The following examples show how to use org.apache.poi.ss.usermodel.Sheet#getDataValidationHelper() . 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

7 votes

/**
 * 设置某些列的值只能输入预制的数据,显示下拉框.
 * 
 * @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

6 votes

/**
 * 设置某些列的值只能输入预制的数据,显示下拉框.
 * 
 * @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 LuckyFrameWeb with GNU Affero General Public License v3.0

6 votes

/**
 * 设置某些列的值只能输入预制的数据,显示下拉框.
 * 
 * @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 4

Source File: AbstractExcelFactory.java From myexcel with Apache License 2.0

6 votes

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;
}

Example 5

Source File: POIUtils.java From xlsmapper with Apache License 2.0

6 votes

/**
 * 指定した範囲のセルに制約を追加する。
 * <p>POI-3.7以上が必要。
 * @param sheet シート
 * @param constraint 制約
 * @param startPosition 設定するセルの開始位置
 * @param endPosition 設定するセルの終了位置
 */
public static void setupConstaint(final Sheet sheet, final DataValidationConstraint constraint,
        final Point startPosition, final Point endPosition) {

    ArgUtils.notNull(sheet, "sheet");
    ArgUtils.notNull(constraint, "constraint");
    ArgUtils.notNull(startPosition, "startPosition");
    ArgUtils.notNull(endPosition, "endPosition");

    final DataValidationHelper helper = sheet.getDataValidationHelper();

    final CellRangeAddressList region = new CellRangeAddressList(
            startPosition.y, endPosition.y,
            startPosition.x, endPosition.x
            );
    final DataValidation dataValidation = helper.createValidation(constraint, region);
    sheet.addValidationData(dataValidation);
}

Example 6

Source File: ExcelUtil.java From supplierShop with MIT License

5 votes

/**
 * 设置 POI XSSFSheet 单元格提示
 * 
 * @param sheet 表单
 * @param promptTitle 提示标题
 * @param promptContent 提示内容
 * @param firstRow 开始行
 * @param endRow 结束行
 * @param firstCol 开始列
 * @param endCol 结束列
 */
public void setXSSFPrompt(Sheet sheet, String promptTitle, String promptContent, int firstRow, int endRow,
        int firstCol, int endCol)
{
    DataValidationHelper helper = sheet.getDataValidationHelper();
    DataValidationConstraint constraint = helper.createCustomConstraint("DD1");
    CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
    DataValidation dataValidation = helper.createValidation(constraint, regions);
    dataValidation.createPromptBox(promptTitle, promptContent);
    dataValidation.setShowPromptBox(true);
    sheet.addValidationData(dataValidation);
}

Example 7

Source File: ExcelUtil.java From RuoYi-Vue with MIT License

5 votes

/**
 * 设置 POI XSSFSheet 单元格提示
 * 
 * @param sheet 表单
 * @param promptTitle 提示标题
 * @param promptContent 提示内容
 * @param firstRow 开始行
 * @param endRow 结束行
 * @param firstCol 开始列
 * @param endCol 结束列
 */
public void setXSSFPrompt(Sheet sheet, String promptTitle, String promptContent, int firstRow, int endRow,
        int firstCol, int endCol)
{
    DataValidationHelper helper = sheet.getDataValidationHelper();
    DataValidationConstraint constraint = helper.createCustomConstraint("DD1");
    CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
    DataValidation dataValidation = helper.createValidation(constraint, regions);
    dataValidation.createPromptBox(promptTitle, promptContent);
    dataValidation.setShowPromptBox(true);
    sheet.addValidationData(dataValidation);
}

Example 8

Source File: POIUtils.java From xlsmapper with Apache License 2.0

5 votes

/**
 * テンプレートの入力規則の制約「リスト」を追加する。
 * <p>POI-3.7以上が必要。
 * @param sheet シート
 * @param constraints 制約とするリストの中身
 * @param startPosition 開始位置
 * @param endPosition 終了位置
 */
public static void setupExplicitListConstaint(final Sheet sheet, final String[] constraints,
        final Point startPosition, final Point endPosition) {

    ArgUtils.notNull(sheet, "sheet");
    ArgUtils.notEmpty(constraints, "constraints");
    ArgUtils.notNull(startPosition, "startPosition");
    ArgUtils.notNull(endPosition, "endPosition");

    final DataValidationHelper helper = sheet.getDataValidationHelper();
    final DataValidationConstraint constraint = helper.createExplicitListConstraint(constraints);
    setupConstaint(sheet, constraint, startPosition, endPosition);

}

Example 9

Source File: POIUtils.java From xlsmapper with Apache License 2.0

5 votes

/**
 * テンプレートの入力規則の制約「リスト」を式形式で追加する。
 * <p>POI-3.7以上が必要。
 * @param sheet シート
 * @param listFormula 入力規則の式('='は含まない)
 * @param startPosition 設定するセルの開始位置
 * @param endPosition 設定するセルの終了位置
 */
public static void setupFormulaListConstaint(final Sheet sheet, final String listFormula,
        final Point startPosition, final Point endPosition) {

    ArgUtils.notNull(sheet, "sheet");
    ArgUtils.notEmpty(listFormula, "listFormula");
    ArgUtils.notNull(startPosition, "startPosition");
    ArgUtils.notNull(endPosition, "endPosition");

    final DataValidationHelper helper = sheet.getDataValidationHelper();
    final DataValidationConstraint constraint = helper.createFormulaListConstraint("=" + listFormula);
    setupConstaint(sheet, constraint, startPosition, endPosition);
}