Apply Data Validation in Excel Using C#

data-validation-in-excel-using-csharp.md

Learn how to Apply Data Validation in Excel Using C#

data-validation-in-excel-using-csharp_date.cs

using Aspose.Cells;

// Create a new Excel workbook
var workbook = new Workbook();

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

// Define the cell area to apply validation — C3 (row 2, column 2)
var area = new CellArea
{
    StartRow = 2,
    EndRow = 2,
    StartColumn = 2,
    EndColumn = 2
};

// Access the validations collection of the worksheet
var validations = sheet.Validations;

// Add a new validation and get its index
int index = validations.Add(area);

// Retrieve the validation object
var validation = validations[index];

// Set the validation type to Date
validation.Type = ValidationType.Date;

// Set the operator to Between (start and end dates)
validation.Operator = OperatorType.Between;

// Specify the valid date range: Jan 1, 2024 to Dec 31, 2024
validation.Formula1 = "2024-01-01";
validation.Formula2 = "2024-12-31";

// Set the error message to display when the date is out of range
validation.ErrorMessage = "Date must be within the year 2024.";

// Enable showing the error alert
validation.ShowError = true;

// Re-apply the area to ensure validation is bound correctly
validation.AddArea(area);

// Save the workbook to the specified path
workbook.Save("date-validation.xlsx");

data-validation-in-excel-using-csharp_dropdown.cs

// Create a new Excel workbook
var workbook = new Workbook();

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

// Define the target cell area for validation (A1 in this case)
var area = new CellArea
{
    StartRow = 0,     // Row index starts from 0 (Row 1)
    EndRow = 0,       // Apply to a single row
    StartColumn = 0,  // Column index starts from 0 (Column A)
    EndColumn = 0     // Apply to a single column
};

// Access the validations collection of the worksheet
var validations = sheet.Validations;

// Add a new validation entry for the specified cell area
int index = validations.Add(area);
var validation = validations[index];

// Set the validation type to a dropdown list
validation.Type = ValidationType.List;

// Define the allowed list items (comma-separated values)
validation.Formula1 = "Red,Green,Blue";

// Set the input message shown when the cell is selected
validation.InputMessage = "Select a color from the list.";

// Set the error message shown if invalid data is entered
validation.ErrorMessage = "Only Red, Green, or Blue are allowed.";

// Enable the display of the error message
validation.ShowError = true;

// Apply the defined area to the validation
validation.AddArea(area);

// Save the workbook to the specified file path
workbook.Save("dropdown-validation.xlsx");

data-validation-in-excel-using-csharp_errors.cs

var workbook = new Workbook();
var sheet = workbook.Worksheets[0];

// Set up validation area — apply to cell C1
var area = new CellArea
{
    StartRow = 0,
    EndRow = 0,
    StartColumn = 2, // Column C = 2
    EndColumn = 2
};

// Add validation
int index = sheet.Validations.Add(area);
var validation = sheet.Validations[index];

validation.Type = ValidationType.Custom;

// This formula always evaluates to FALSE
validation.Formula1 = "=FALSE";

// Set up input and error messages
validation.InputTitle = "Input Restricted";
validation.InputMessage = "Try entering anything to see the validation.";
validation.ErrorTitle = "Invalid Input";
validation.ErrorMessage = "You triggered this validation error successfully!";
validation.AlertStyle = ValidationAlertType.Stop;
validation.ShowError = true;
validation.ShowInput = true;

// Apply validation to area
validation.AddArea(area);

// Save the validated workbook
workbook.Save("D:\\Files\\validated_with_alerts.xlsx");

data-validation-in-excel-using-csharp_formula.cs

using Aspose.Cells;

// Create a new Excel workbook
var workbook = new Workbook();

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

// Define the cell area for validation — B1 (row 0, column 1)
var area = new CellArea
{
    StartRow = 0,
    EndRow = 0,
    StartColumn = 1,
    EndColumn = 1
};

// Access the worksheets validations collection
var validations = sheet.Validations;

// Add a new validation to the collection and get its index
int index = validations.Add(area);

// Retrieve the validation object by index
var validation = validations[index];

// Set the validation type to Custom (used for formula-based rules)
validation.Type = ValidationType.Custom;

// Set the custom formula: B1 must be greater than A1
validation.Formula1 = "=B1>A1";

// Define the error message shown when validation fails
validation.ErrorMessage = "Value in B1 must be greater than A1.";

// Enable display of the error alert on invalid input
validation.ShowError = true;

// Add the area explicitly to ensure it is covered by validation
validation.AddArea(area);

// Save the workbook to the specified file path
workbook.Save("formula-validation.xlsx");

data-validation-in-excel-using-csharp_numbers.cs

using Aspose.Cells;

// Create a new Excel workbook
var workbook = new Workbook();

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

// Define the target cell area — B2 (row 1, column 1)
var area = new CellArea
{
    StartRow = 1,
    EndRow = 1,
    StartColumn = 1,
    EndColumn = 1
};

// Access the worksheet’s validations collection
var validations = sheet.Validations;

// Add a new validation and get its index
int index = validations.Add(area);

// Retrieve the validation object using the index
var validation = validations[index];

// Set validation type to WholeNumber (only integers allowed)
validation.Type = ValidationType.WholeNumber;

// Set the operator to Between
validation.Operator = OperatorType.Between;

// Define the valid range: 1 to 100
validation.Formula1 = "1";
validation.Formula2 = "100";

// Set the error message that appears when invalid data is entered
validation.ErrorMessage = "Enter a number between 1 and 100.";

// Enable showing the error alert when validation fails
validation.ShowError = true;

// (Optional if not using Add(area) earlier) Add the area to the validation explicitly
validation.AddArea(area);

// Save the workbook to a file
workbook.Save("numbers-validation.xlsx");