Setting List DataValidation Rule to Reference Cell Range in SpreadProcessing

Environment

Version	Product	Author
2025.3.806	RadSpreadProcessing	Yoan Karamanov

Description

This article describes how to set a List Data Validation rule in the SpreadProcessing library that uses a cell range as the validation source instead of a comma-delimited list of values. This avoids the 256-character limit imposed by Excel for comma-separated values.

This knowledge base article also answers the following questions:

• How to define validation rules with a cell range in SpreadProcessing?
• How to bypass the character limit in validation rules by using cell ranges?
• How to set up list validation using cell ranges in Telerik's SpreadProcessing?

Solution

To set a List Data Validation rule that references a cell range, use the ListDataValidationRule and specify the cell range as the Argument1. Follow the steps below:

1. Import the workbook using the XlsxFormatProvider.
2. Specify the cell where the validation rule will apply using CellIndex.
3. Define the validation parameters, including the cell range for allowed values.
4. Create and assign the ListDataValidationRule to the target cell.
5. Export the updated workbook using the XlsxFormatProvider.

Here is an example:

// Import the workbook
Workbook workbook;
IWorkbookFormatProvider xlsxFormatProvider = new XlsxFormatProvider();

using (Stream input = new FileStream("input.xlsx", FileMode.Open))
{
    workbook = xlsxFormatProvider.Import(input, TimeSpan.FromSeconds(10));
}

// Access the worksheet
var worksheet = workbook.Worksheets[0];

// Define the cell to apply validation
CellIndex dataValidationRuleCellIndex = new CellIndex(4, 4);

// Configure the validation rule context
ListDataValidationRuleContext context = new ListDataValidationRuleContext(worksheet, dataValidationRuleCellIndex);
context.InputMessageTitle = "Restricted input";
context.InputMessageContent = "The input is restricted to the week days.";
context.ErrorStyle = ErrorStyle.Stop;
context.ErrorAlertTitle = "Wrong value";
context.ErrorAlertContent = "The entered value is not valid. Allowed values are the week days!";
context.InCellDropdown = true;

// Set the cell range as the validation source
context.Argument1 = "=$A$1:$A$26";

// Create and apply the validation rule
ListDataValidationRule rule = new ListDataValidationRule(context);
worksheet.Cells[dataValidationRuleCellIndex].SetDataValidationRule(rule);

// Export the workbook
string xlsxOutputPath = "output.xlsx";
using (Stream output = new FileStream(xlsxOutputPath, FileMode.Create))
{
    xlsxFormatProvider.Export(workbook, output, TimeSpan.FromSeconds(10));
}

See Also

• Data Validation
• List Rule
• SpreadProcessing Overview