This tutorial guides on how to add data validation in Excel using C#. It provides all the steps to be followed along with the running sample code to insert data validation in Excel using C#. You can perform this operation on an existing workbook or a new workbook by using reference data in some existing worksheet or by adding a new list of data and then saving the resultant workbook as XLS, XLSX, or in any other format supported by MS Excel.
Steps to Add Data Validation in Excel using C#
- Set the environment to use Aspose.Cells for .NET to add a validation
- Instantiate a workbook and create and access desired worksheets
- Create a named range and fill the reference data in it to be shown in the validation list
- Create a cell area and instantiate a new validation for the selected worksheet
- Set properties of the new validation
- Save the workbook having validation in it
These steps describe the process to create a data validation list in Excel using C# where first a workbook is created along with adding an additional worksheet for the reference data. A named range is created and filled with reference data in the next steps. In the final steps, a new validation is added to the validations collection of the selected worksheet, and its various properties are set before saving the workbook.
Code to Create Data Validation Rule in Excel using C#
| using Aspose.Cells; | |
| namespace AsposeProjects | |
| { | |
| class Program | |
| { | |
| static void Main(string[] args) // Main function to add table in a slide using C# | |
| { | |
| // Set license | |
| License lic = new License(); | |
| lic.SetLicense("Aspose.Total.lic"); | |
| // Instantiate a workbook | |
| Workbook workbook = new Workbook(); | |
| // Access the first sheet | |
| Worksheet worksheet1 = workbook.Worksheets[0]; | |
| // Create another sheet for reference data and get access to it | |
| int i = workbook.Worksheets.Add(); | |
| Worksheet worksheet2 = workbook.Worksheets[i]; | |
| // Create a range for the reference list | |
| Range referenceRange = worksheet2.Cells.CreateRange("E1", "E4"); | |
| // Set the name property of the above-created range | |
| referenceRange.Name = "ReferenceRange"; | |
| // Fill the reference list to be used for validation | |
| referenceRange[0, 0].PutValue("Tiny"); | |
| referenceRange[1, 0].PutValue("Small"); | |
| referenceRange[2, 0].PutValue("Medium"); | |
| referenceRange[3, 0].PutValue("Large"); | |
| // Get a reference to the validations collection on the first sheet | |
| ValidationCollection validations = worksheet1.Validations; | |
| // Create cell Area where validation is to be implemented | |
| CellArea area; | |
| area.StartRow = 0; | |
| area.EndRow = 4; | |
| area.StartColumn = 0; | |
| area.EndColumn = 0; | |
| // Create a new validation for the given cell area defined above | |
| Validation validation = validations[validations.Add(area)]; | |
| // Set type of validation | |
| validation.Type = Aspose.Cells.ValidationType.List; | |
| // Set the type of operator | |
| validation.Operator = OperatorType.None; | |
| // Set flag for in cell drop down | |
| validation.InCellDropDown = true; | |
| // Set the formula by providing reference data range name | |
| validation.Formula1 = "=ReferenceRange"; | |
| // Enable the flag to show an error | |
| validation.ShowError = true; | |
| // Set the type of alert on error | |
| validation.AlertStyle = ValidationAlertType.Stop; | |
| // Set the title of the error | |
| validation.ErrorTitle = "Error Title"; | |
| // Set the message to be shown when error is raised | |
| validation.ErrorMessage = "Please select an entry from the combobox"; | |
| // Save the output file | |
| workbook.Save("output.out.xls"); | |
| System.Console.WriteLine("Done"); | |
| } | |
| } | |
| } |
This code demonstrates the process of how to create data validation in Excel using C#. You need to create a range and set different reference values in it as this range will be set as Formula1 in the newly created validation. Similarly, the new validation requires a cell area where this validation is to be implemented and a number of properties are to be set like validation type, flag to create an in-cell dropdown, and properties for handling the error conditions.
This tutorial has guided us on how to make a drop down box in Excel using C#. If you want to learn the process to filter data in an Excel table, refer to the article on how to filter data in Excel table using C#.
