How to Write Validation in Excel with C#

Sometimes, although we have created a table template and set format well in Excel, we need others to provides data to fill cells, for example, an application form. When we write information in cells according to cue words, they need to pay attention that there may be some special requirements, for exmaple, date format must be mm-dd-yy. If we don’t follow requirements to fill cells, there will be a message to tell that you should input correct information. We can call this process Validation.

In this post, I will show the method how to write validation in Excel with C#. This method is based on a component: Spire.XLS. In project, I have added its dll file as reference.


  1. Create a new workbook and initialize worksheet.
  2. Write cue words in cells and format these cells.
  3. Set validation, including number validation and date validation.
  4. Save and launch file.


using System;
using System.Drawing;
using Spire.Xls;

namespace ExcelValidation
    class Validation
        static void Main(string[] args)
            Workbook workbook = new Workbook();

            //Initialize Worksheet
            Worksheet sheet = workbook.Worksheets[0];

            //Show Cue Words
            sheet.Range[“B2”].Text = “Input Number(5-9):”;
            sheet.Range[“B2”].Style.Font.FontName = “Calibri”;
            sheet.Range[“B2”].Style.Font.Size = 12;
            sheet.Range[“B2”].Style.Font.IsBold = true;
            sheet.Range[“B2”].Style.Color = Color.LightCyan;

            //Set Validation for Number
            CellRange rangeNumber = sheet.Range[“B3”];
            rangeNumber.DataValidation.CompareOperator = ValidationComparisonOperator.Between;
            rangeNumber.DataValidation.Formula1 = “5”;
            rangeNumber.DataValidation.Formula2 = “9”;
            rangeNumber.DataValidation.AllowType = CellDataType.Decimal;
            rangeNumber.DataValidation.ErrorMessage = “Please input correct number!”;
            rangeNumber.DataValidation.ShowError = true;
            rangeNumber.Style.KnownColor = ExcelColors.YellowCustom;

            //Show Cue Words
            sheet.Range[“B5”].Text = “Inputate:”;
            sheet.Range[“B5”].Style.Font.FontName = “Calibri”;
            sheet.Range[“B5”].Style.Font.Size = 12;
            sheet.Range[“B5”].Style.Font.IsBold = true;
            sheet.Range[“B5”].Style.Color = Color.LightCyan;

            //Set Validataion for Date
            CellRange rangeDate = sheet.Range[“B6”];
            rangeDate.DataValidation.AllowType = CellDataType.Date;
            rangeDate.DataValidation.ErrorMessage = “Please input correct date!”;
            rangeDate.DataValidation.ShowError = true;
            rangeDate.Style.KnownColor = ExcelColors.YellowCustom;

            //Save and Launch File
            workbook.SaveToFile(“ExcelValidation.xlsx”, ExcelVersion.Version2010);




Click Here to LEARN MORE about Spire.XLS.

Click Here to DOWNLOAD Spire.XLS.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s