Skip to content

Latest commit

 

History

History
184 lines (155 loc) · 10.1 KB

excelengine-datavalidation.adoc

File metadata and controls

184 lines (155 loc) · 10.1 KB

Data Validation

This topic outlines the data validation support in the Infragistics Excel Engine and provides a code example demonstrating usage.

The topic is organized as follows:

Introduction

Data validation is the process of verifying user-entered data based on specific requirements before committing the value. The Infragistics Excel Engine allows you to set validation rules on worksheets, individual cells, or even multiple cells. Using data validation is useful when you want users to enter data that needs to stay within specified criteria.

Note
Note

The Excel Engine can create Microsoft Excel files without having Microsoft Excel installed, but to open the file, you will need to have an application compatible with the Microsoft Excel format.

The table below lists the supported validation rule types and provides a description.

Rule Type Description

pick:[win-forms=" AnyValueDataValidationRule"] pick:[asp-net=" AnyValueDataValidationRule"] pick:[win-forms-old=" AnyValueDataValidationRule"] pick:[aspnet-old=" AnyValueDataValidationRule"]

Allows any value to be set on the target cells.

pick:[win-forms=" ListDataValidationRule"] pick:[asp-net=" ListDataValidationRule"] pick:[win-forms-old=" ListDataValidationRule"] pick:[aspnet-old=" ListDataValidationRule"]

Allows only a specific set of values to be set on the target cells.

pick:[win-forms=" CustomDataValidationRule"] pick:[asp-net=" CustomDataValidationRule"] pick:[win-forms-old=" CustomDataValidationRule"] pick:[aspnet-old=" CustomDataValidationRule"]

Uses a custom formula to validate the value set on the target cells

pick:[win-forms=" OneConstraintDataValidationRule"] pick:[asp-net=" OneConstraintDataValidationRule"] pick:[win-forms-old=" OneConstraintDataValidationRule"] pick:[aspnet-old=" OneConstraintDataValidationRule"]

Allows any value to be set on the target cells if it is of a certain type and passes a conditional test relative to one other value. The value can be enforced to be one of the following types: date, time, decimal, whole number, or text. When the type is text, the length of the text is used when testing the conditional constraint.

pick:[win-forms=" TwoConstraintDataValidationRule"] pick:[asp-net=" TwoConstraintDataValidationRule"] pick:[win-forms-old=" TwoConstraintDataValidationRule"] pick:[aspnet-old=" TwoConstraintDataValidationRule"]

Allows any value to be set on the target cells if it is of a certain type and passes a conditional test relative to two other values. The value can be enforced to be one of the following types: date, time, decimal, whole number, or text. When the type is text, the length of the text is used when testing the conditional constraints.

Data Validation

Overview

The code snippet below creates a workbook with the following properties:

  • Workbook Named: output.xls

  • Worksheet Named: Validation

  • Rule Type: ListDataValidationRule

  • Valid Values: cat, dog, bird, goat, sheep, turtle, horse, chicken

  • Cell Validated: A1

Code Example

In C#:

// Create the workbook with one worksheet called Validation
Infragistics.Documents.Excel.Workbook workbook = new Infragistics.Documents.Excel.Workbook();
Infragistics.Documents.Excel.Worksheet worksheet = workbook.Worksheets.Add("Validation");
// Create a variable for the base data validation rule
DataValidationRule dataRule = null;
// Create a variable for the worksheet reference collection
WorksheetReferenceCollection cellCollection = null;
// Create a variable for a worksheet cell
WorksheetCell wsc = null;
// Create a new list data validation rule
ListDataValidationRule ld = new ListDataValidationRule();
// Copy the validation rule reference to the base data rule variable
dataRule = ld;
// Allow the worksheet cell to contain null values
ld.AllowNull = true;
// Allow the showing of the drop down for valid cell values
ld.ShowDropdown = true;
// Set the A1 cell's valid values
ld.SetValuesFormula("=\"cat, dog, bird, goat, sheep, turtle, horse, chicken\"", "A1");
// Reference the worksheet cell in the collection
cellCollection = new WorksheetReferenceCollection(worksheet, "A1");
// Add the error message information
dataRule.ErrorMessageDescription = "Invalid value entered.";
dataRule.ErrorMessageTitle = "Validation Error";
dataRule.ErrorStyle = DataValidationErrorStyle.Stop;
// Add the input message information
dataRule.InputMessageDescription = "Type or select a value from the list.";
dataRule.InputMessageTitle = "Value Selection";
// Set whether or not to display the error message after an invalid value was entered
dataRule.ShowErrorMessageForInvalidValue = true;
// Set whether or not to display the input message
dataRule.ShowInputMessage = true;
// Add the data validation to the worksheet
worksheet.DataValidationRules.Add(dataRule, cellCollection);
try
{
    // Save the created workbook
    workbook.Save("output.xls");
    // Open the workbook to display the results
    System.Diagnostics.Process.Start("output.xls");
}
catch
{
    MessageBox.Show("Please close any open instances of Excel.",
        "Error Opening Workbook",
        MessageBoxButtons.OK, MessageBoxIcon.Stop);
}

In Visual Basic:

' Create the workbook with one worksheet called Validation
Dim workbook As New Infragistics.Documents.Excel.Workbook()
Dim worksheet As Infragistics.Documents.Excel.Worksheet = workbook.Worksheets.Add("Validation")
' Create a variable for the base data validation rule
Dim dataRule As DataValidationRule = Nothing
' Create a variable for the worksheet reference collection
Dim cellCollection As WorksheetReferenceCollection = Nothing
' Create a variable for a worksheet cell
Dim wsc As WorksheetCell = Nothing
' Create a new list data validation rule
Dim ld As New ListDataValidationRule()
' Copy the validation rule reference to the base data rule variable
dataRule = ld
' Allow the worksheet cell to contain null values
ld.AllowNull = True
' Allow the showing of the drop down for valid cell values
ld.ShowDropdown = True
' Set the A1 cell's valid values
ld.SetValuesFormula("=""cat, dog, bird, goat, sheep, turtle, horse, chicken""", "A1")
' Reference the worksheet cell in the collection
cellCollection = New WorksheetReferenceCollection(worksheet, "A1")
' Add the error message information
dataRule.ErrorMessageDescription = "Invalid value entered."
dataRule.ErrorMessageTitle = "Validation Error"
dataRule.ErrorStyle = DataValidationErrorStyle.[Stop]
' Add the input message information
dataRule.InputMessageDescription = "Type or select a value from the list."
dataRule.InputMessageTitle = "Value Selection"
' Set whether or not to display the error message after an invalid value was entered
dataRule.ShowErrorMessageForInvalidValue = True
' Set whether or not to display the input message
dataRule.ShowInputMessage = True
' Add the data validation to the worksheet
worksheet.DataValidationRules.Add(dataRule, cellCollection)
Try
    ' Save the created workbook
    workbook.Save("output.xls")
    ' Open the workbook to display the results
    System.Diagnostics.Process.Start("output.xls")
Catch
    MessageBox.Show("Please close any open instances of Excel.", "Error Opening Workbook", MessageBoxButtons.OK, MessageBoxIcon.[Stop])
End Try