Excel Data Validation

Following sample demonstrates how to set Data Validation for certain cells.

GemBox.Spreadsheet supports reading and writing all types of Data Validations for XLSX file format.

Screenshot

Data Validation Screenshot

See the full code below, use RUN EXAMPLE to execute.


1using System;
2using System.Collections.Generic;
3using System.Text;
4using GemBox.Spreadsheet;
5using GemBox.Spreadsheet.ConditionalFormatting;
6using GemBox.Spreadsheet.PivotTables;
7
8class Sample
9{
10    [STAThread]
11    static void Main(string[] args)
12    {
13        // If using Professional version, put your serial key below.
14        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
15
16        ExcelFile ef = new ExcelFile();
17        ExcelWorksheet ws = ef.Worksheets.Add("Data Validation");
18
19        ws.Cells[0, 0].Value = "Data validation examples:";
20
21        ws.Cells[2, 1].Value = "Decimal greater than 3.14 (on entire row 4):";
22        ws.DataValidations.Add(new DataValidation(ws.Rows[3].Cells)
23        {
24            Type = DataValidationType.Decimal,
25            Operator = DataValidationOperator.GreaterThan,
26            Formula1 = 3.14,
27            InputMessageTitle = "Enter a decimal",
28            InputMessage = "Decimal should be greater than 3.14.",
29            ErrorTitle = "Invalid decimal",
30            ErrorMessage = "Value should be a decimal greater than 3.14."
31        });
32        ws.Cells.GetSubrange("A4", "J4").Value = 3.15;
33
34        ws.Cells[7, 1].Value = "List from B9 to B12 (on cell C8):";
35        ws.Cells[8, 1].Value = "John";
36        ws.Cells[9, 1].Value = "Fred";
37        ws.Cells[10, 1].Value = "Hans";
38        ws.Cells[11, 1].Value = "Ivan";
39        ws.DataValidations.Add(new DataValidation(ws, "C8")
40        {
41            Type = DataValidationType.List,
42            Formula1 = "=B9:B12",
43            InputMessageTitle = "Enter a name",
44            InputMessage = "Name should be from the list: John, Fred, Hans, Ivan.",
45            ErrorStyle = DataValidationErrorStyle.Warning,
46            ErrorTitle = "Invalid name",
47            ErrorMessage = "Value should be a name from the list: John, Fred, Hans, Ivan."
48        });
49        ws.Cells["C8"].Value = "John";
50
51        ws.Cells[13, 1].Value = "Date between 2011-01-01 and 2011-12-31 (on cell range C14:E15):";
52        ws.DataValidations.Add(new DataValidation(ws.Cells.GetSubrange("C14", "E15"))
53        {
54            Type = DataValidationType.Date,
55            Operator = DataValidationOperator.Between,
56            Formula1 = new DateTime(2011, 1, 1),
57            Formula2 = new DateTime(2011, 12, 31),
58            InputMessageTitle = "Enter a date",
59            InputMessage = "Date should be between 2011-01-01 and 2011-12-31.",
60            ErrorStyle = DataValidationErrorStyle.Information,
61            ErrorTitle = "Invalid date",
62            ErrorMessage = "Value should be a date between 2011-01-01 and 2011-12-31."
63        });
64        ws.Cells.GetSubrange("C14", "E15").Value = new DateTime(2011, 1, 1);
65
66        // Column width of 8, 55 and 15 characters.
67        ws.Columns[0].Width = 8 * 256;
68        ws.Columns[1].Width = 55 * 256;
69        ws.Columns[2].Width = 15 * 256;
70        ws.Columns[3].Width = 15 * 256;
71        ws.Columns[4].Width = 15 * 256;
72
73        ef.Save("Data Validation.xlsx");
74    }
75}
1Imports System
2Imports System.Collections.Generic
3Imports System.Text
4Imports GemBox.Spreadsheet
5Imports GemBox.Spreadsheet.ConditionalFormatting
6Imports GemBox.Spreadsheet.PivotTables
7
8Module Samples
9
10    Sub Main()
11
12        ' If using Professional version, put your serial key below.
13        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")
14
15        Dim ef As ExcelFile = New ExcelFile
16        Dim ws As ExcelWorksheet = ef.Worksheets.Add("Data Validation")
17
18        ws.Cells(0, 0).Value = "Data validation examples:"
19
20        ws.Cells(2, 1).Value = "Decimal greater than 3.14 (on entire row 4):"
21        ws.DataValidations.Add(New DataValidation(ws.Rows(3).Cells) With {
22         .Type = DataValidationType.Decimal,
23         .Operator = DataValidationOperator.GreaterThan,
24         .Formula1 = 3.14, .InputMessageTitle = "Enter a decimal",
25         .InputMessage = "Decimal should be greater than 3.14.",
26         .ErrorTitle = "Invalid decimal",
27         .ErrorMessage = "Value should be a decimal greater than 3.14."
28        })
29        ws.Cells.GetSubrange("A4", "J4").Value = 3.15
30
31        ws.Cells(7, 1).Value = "List from B9 to B12 (on cell C8):"
32        ws.Cells(8, 1).Value = "John"
33        ws.Cells(9, 1).Value = "Fred"
34        ws.Cells(10, 1).Value = "Hans"
35        ws.Cells(11, 1).Value = "Ivan"
36        ws.DataValidations.Add(New DataValidation(ws, "C8") With {
37         .Type = DataValidationType.List,
38         .Formula1 = "=B9:B12",
39         .InputMessageTitle = "Enter a name",
40         .InputMessage = "Name should be from the list: John, Fred, Hans, Ivan.",
41         .ErrorStyle = DataValidationErrorStyle.Warning,
42         .ErrorTitle = "Invalid name",
43         .ErrorMessage = "Value should be a name from the list: John, Fred, Hans, Ivan."
44        })
45        ws.Cells("C8").Value = "John"
46
47        ws.Cells(13, 1).Value = "Date between 2011-01-01 and 2011-12-31 (on cell range C14:E15):"
48        ws.DataValidations.Add(New DataValidation(ws.Cells.GetSubrange("C14", "E15")) With {
49         .Type = DataValidationType.Date,
50         .Operator = DataValidationOperator.Between,
51         .Formula1 = New DateTime(2011, 1, 1),
52         .Formula2 = New DateTime(2011, 12, 31),
53         .InputMessageTitle = "Enter a date",
54         .InputMessage = "Date should be between 2011-01-01 and 2011-12-31.",
55         .ErrorStyle = DataValidationErrorStyle.Information,
56         .ErrorTitle = "Invalid date",
57         .ErrorMessage = "Value should be a date between 2011-01-01 and 2011-12-31."
58        })
59        ws.Cells.GetSubrange("C14", "E15").Value = New DateTime(2011, 1, 1)
60
61        ' Column width of 8, 55 and 15 characters.
62        ws.Columns(0).Width = 8 * 256
63        ws.Columns(1).Width = 55 * 256
64        ws.Columns(2).Width = 15 * 256
65        ws.Columns(3).Width = 15 * 256
66        ws.Columns(4).Width = 15 * 256
67
68        ef.Save("Data Validation.xlsx")
69
70    End Sub
71
72End Module

Check next sample or find out more about GemBox.Spreadsheet and GemBox Software.