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.

STEPS

  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.

CODING

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

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

            //Initialize Worksheet
            workbook.CreateEmptySheets(1);
            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;
            sheet.AutoFitColumn(2);

            //Save and Launch File
            workbook.SaveToFile(“ExcelValidation.xlsx”, ExcelVersion.Version2010);
            System.Diagnostics.Process.Start(workbook.FileName);
        }
    }
}

 RESULT

 

_______________________________________________________________________________

Click Here to LEARN MORE about Spire.XLS.

Click Here to DOWNLOAD Spire.XLS.

Advertisements

Author: janewdaisy

.NET Program Beginner. Share methods about how to use C#/VB.NET to export data, operate Word, Excel, PDF and other useful skills.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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