C# code of the DataValidation demo
using System;
using System.Drawing;
using System.IO;
using ExpertXls.ExcelLib;
namespace ExcelLibraryDemos
{
/// <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 static class DataValidationDemo
{
/// <summary>The name of the generated file, without extension.</summary>
public const string OutputName = "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 static void CreateWorkbook(ExcelWorkbookFormat format, string inputFolder, Stream output)
{
ExcelWorkbook workbook = new ExcelWorkbook(format);
// workbook.LicenseKey = "your license key";
workbook.DocumentProperties.Subject = "Data validation";
workbook.DocumentProperties.Comments = "Created with ExpertXls Excel Library for .NET";
Image logo = Image.FromFile(Path.Combine(inputFolder, "Images", "logo.jpg"));
try
{
ExcelCellStyle titleStyle = DemoLayout.AddTitleStyle(workbook);
ExcelCellStyle sectionStyle = DemoLayout.AddSectionStyle(workbook);
// the style of the validated cells, so that the user sees where to type
ExcelCellStyle inputStyle = 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;
ExcelWorksheet worksheet = 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.");
ExcelDataValidator listValidator = 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;
ExcelDataValidator numberValidator = 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);
ExcelDataValidator dateValidator = 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;
ExcelDataValidator textValidator = 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";
ExcelDataValidator priceValidator = 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();
}
}
}
}
Create, edit and read Excel workbooks in a Blazor application: the workbooks are created on the server and downloaded by the browser. The code of each workbook is in the Features folder.