DataValidation.aspx.vb
Imports System.Data
Imports System.IO
Imports System.Web.UI
Imports ExpertXls.ExcelLib
''' <summary>
''' Data Validation: the workbook code is in App_Code\DataValidationDemo.vb.
''' </summary>
Partial Class DataValidationPage
Inherits Page
' AutoEventWireup="true" in the page directive connects Page_Load to the Load event
Protected Sub Page_Load(sender As Object, e As EventArgs)
End Sub
Protected Sub buttonCreate_Click(sender As Object, e As EventArgs)
Dim format As ExcelWorkbookFormat = If(radioXlsx.Checked, ExcelWorkbookFormat.Xlsx_2007, ExcelWorkbookFormat.Xls_2003)
Dim workbookBytes As Byte()
Try
' create the workbook in memory; the input files are in the folders of the web site
Using stream As New MemoryStream()
DataValidationDemo.CreateWorkbook(format, Server.MapPath("~"), stream)
workbookBytes = stream.ToArray()
End Using
Catch ex As Exception
labelError.Text = "The workbook could not be created: " & Server.HtmlEncode(ex.Message)
Return
End Try
WorkbookDownload.Send(Response, workbookBytes, DataValidationDemo.OutputName & DemoLayout.GetExtension(format), format)
End Sub
End Class
App_Code/DataValidationDemo.vb
Imports System.Drawing
Imports System.IO
Imports ExpertXls.ExcelLib
''' <summary>
''' Restricts what a user can type in cells: a drop-down list, whole numbers in an interval, dates in an interval
''' and a text length. Excel shows the input message when the cell is selected and the error alert when the
''' value is not valid.
''' </summary>
Public Module DataValidationDemo
''' <summary>The name of the generated file, without extension.</summary>
Public Const OutputName As String = "DataValidation"
''' <summary>
''' Creates the workbook in the given format and saves it to the output stream.
''' </summary>
''' <param name="format">Excel 97-2003 (.xls) or Excel 2007+ (.xlsx).</param>
''' <param name="inputFolder">The folder that contains the Images folder of the sample.</param>
''' <param name="output">The stream that receives the workbook.</param>
Public Sub CreateWorkbook(format As ExcelWorkbookFormat, inputFolder As String, output As Stream)
Dim workbook As New ExcelWorkbook(format)
' workbook.LicenseKey = "your license key"
workbook.DocumentProperties.Subject = "Data validation"
workbook.DocumentProperties.Comments = "Created with ExpertXls Excel Library for .NET"
Dim logo As Image = Image.FromFile(Path.Combine(inputFolder, "Images", "logo.jpg"))
Try
Dim titleStyle As ExcelCellStyle = DemoLayout.AddTitleStyle(workbook)
Dim sectionStyle As ExcelCellStyle = DemoLayout.AddSectionStyle(workbook)
' the style of the validated cells, so that the user sees where to type
Dim inputStyle As ExcelCellStyle = workbook.Styles.AddStyle("ValidatedCellStyle")
inputStyle.Font.Bold = True
inputStyle.Fill.FillType = ExcelCellFillType.SolidFill
inputStyle.Fill.SolidFillOptions.BackColor = Color.FromArgb(153, 204, 0)
inputStyle.Borders.LineStyle = ExcelCellLineStyle.Thin
Dim worksheet As ExcelWorksheet = workbook.Worksheets(0)
worksheet.Name = "Data Validation"
DemoLayout.SetupPage(worksheet, logo, ExcelPageOrientation.Portrait)
DemoLayout.WriteTitle(worksheet, "A2:G3", "Adding Data Validation to an Excel Worksheet", titleStyle)
worksheet("G1").ColumnWidthInChars = 25
' a drop-down list of allowed values
DemoLayout.WriteSection(worksheet, "A5:E5", "Select a value from the list:", sectionStyle)
worksheet("G5").Style = inputStyle
worksheet("G5").AddComment("Click this cell to select a value from the list.")
Dim listValidator As ExcelDataValidator = worksheet("G5").DataValidator
listValidator.AllowedDataType = ExcelDataValidatorDataType.List
listValidator.AllowedValues = New String() {"HTML to PDF Converter", "PDF Merge", "PDF Security", "Excel Library for .NET"}
listValidator.InCellDropDown = True
listValidator.InputMessageTitle = "Product"
listValidator.InputMessageText = "Select a product from the list"
listValidator.ShowInputMessage = True
' a whole number between two values, with a stop error alert
DemoLayout.WriteSection(worksheet, "A7:E7", "Enter a whole number between 0 and 10:", sectionStyle)
worksheet("G7").Style = inputStyle
Dim numberValidator As ExcelDataValidator = worksheet("G7").DataValidator
numberValidator.AllowedDataType = ExcelDataValidatorDataType.WholeNumber
numberValidator.Operator = ExcelDataValidatorOperator.Between
numberValidator.Value1 = 0
numberValidator.Value2 = 10
numberValidator.InputMessageText = "Enter a whole number between 0 and 10"
numberValidator.ShowInputMessage = True
numberValidator.ErrorAlertTitle = "Whole number expected"
numberValidator.ErrorAlertText = "A whole number between 0 and 10 is expected"
numberValidator.ErrorAlertStyle = ExcelDataValidatorErrorStyle.Stop
numberValidator.ShowErrorAlert = True
' a date between two dates; the cell has a default value and a date format
DemoLayout.WriteSection(worksheet, "A9:E9", "Enter a date in 2026:", sectionStyle)
worksheet("G9").Style = inputStyle
worksheet("G9").Style.Number.NumberFormatString = "m/d/yyyy"
worksheet("G9").Value = New DateTime(2026, 6, 15)
Dim dateValidator As ExcelDataValidator = worksheet("G9").DataValidator
dateValidator.AllowedDataType = ExcelDataValidatorDataType.Date
dateValidator.Operator = ExcelDataValidatorOperator.Between
dateValidator.Value1 = New DateTime(2026, 1, 1)
dateValidator.Value2 = New DateTime(2026, 12, 31)
dateValidator.InputMessageText = "Enter a date between 1/1/2026 and 12/31/2026"
dateValidator.ShowInputMessage = True
dateValidator.ErrorAlertTitle = "Date expected"
dateValidator.ErrorAlertText = "A date in 2026 is expected"
dateValidator.ShowErrorAlert = True
' a text length; a warning alert lets the user keep the value anyway
DemoLayout.WriteSection(worksheet, "A11:E11", "Enter a text of 2 to 5 characters:", sectionStyle)
worksheet("G11").Style = inputStyle
Dim textValidator As ExcelDataValidator = worksheet("G11").DataValidator
textValidator.AllowedDataType = ExcelDataValidatorDataType.TextLength
textValidator.Operator = ExcelDataValidatorOperator.Between
textValidator.Value1 = 2
textValidator.Value2 = 5
textValidator.InputMessageText = "Enter a text of 2 to 5 characters"
textValidator.ShowInputMessage = True
textValidator.ErrorAlertTitle = "Text length"
textValidator.ErrorAlertText = "The text should have 2 to 5 characters. Keep it anyway?"
textValidator.ErrorAlertStyle = ExcelDataValidatorErrorStyle.Warning
textValidator.ShowErrorAlert = True
' a decimal number greater than a value, on a whole range of cells
DemoLayout.WriteSection(worksheet, "A13:E13", "Enter prices greater than 0 in G13:G16:", sectionStyle)
worksheet("G13:G16").Style = inputStyle
worksheet("G13:G16").Style.Number.NumberFormatString = "#,##0.00"
Dim priceValidator As ExcelDataValidator = worksheet("G13:G16").DataValidator
priceValidator.AllowedDataType = ExcelDataValidatorDataType.Decimal
priceValidator.Operator = ExcelDataValidatorOperator.GreaterThan
priceValidator.Value1 = 0
priceValidator.ErrorAlertText = "A price greater than 0 is expected"
priceValidator.ShowErrorAlert = True
workbook.Save(output)
Finally
workbook.Close()
logo.Dispose()
End Try
End Sub
End Module