ExpertXls Excel Library Blazor demo · .NET 10 and .NET 11

← All demos

C# code of the DataValidation demo

Features/DataValidationDemo.cs
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.