In my previous article we discuss about how to create Excel file, how to open Excel file and how to write content to excel file. In this article we discuss about how to apply validations(date validation, number validations, decimal validation...etc) for excel file to restrict the user to enter specific content only.

First we discuss how to apply date validations for excel file. As shown below we are using XlDVType class to apply the validation for Microsoft Office Excel.

using System;

using System.Reflection;

using System.Windows.Forms;

using Excel = Microsoft.Office.Interop.Excel;

using System.Diagnostics;

namespace CSharpExcelValidations

{

    public partial class Form1 : Form

    {

        public Form1()

        {

            InitializeComponent();

        }

        private void Form1_Load(object sender, EventArgs e)

        {

        }

        private void button1_Click(object sender, EventArgs e)

        {

            Excel.Application oXL = new Excel.Application();

            Excel.Workbook oWB = oXL.Workbooks.Add(Missing.Value);

            Excel.Worksheet oWS = oWB.Worksheets[1] as Excel.Worksheet;

            //rename the Sheet name

            oWS.Name = "Validations";

            oWS.Cells[1, 1] = "Enter Date";

            Excel.Range range;

            range = oWS.UsedRange.Cells[2,1] as Excel.Range;

            range.EntireColumn.Validation.Add(Microsoft.Office.Interop.Excel.XlDVType.xlValidateDate, Excel.XlDVAlertStyle.xlValidAlertStop,

                                             Excel.XlFormatConditionOperator.xlBetween,"1/1/2010","1/1/2012");

            range.EntireColumn.Validation.ErrorMessage = "Please enter valid date between 1/1/2010 and 1/1/2012";

            range.EntireColumn.Validation.ErrorTitle = "Date validation";

            range = oWS.UsedRange.Cells[1, 1] as Excel.Range;

            range.Validation.Delete(); 

            oWB.SaveAs(Application.StartupPath + "\\Sample.xlsx", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

            oWB.Close(true, Missing.Value, Missing.Value);

            Process.Start(Application.StartupPath + "\\Sample.xlsx");

        }

    }

}

As highlighted we are applying the date validation, where user allowed to enter only date between 1/1/2010 and 1/1/2012 for the first column in Excel sheet "Validations".

We can apply number validation also as shown below.

             Excel.Range range; 

            range = oWS.UsedRange.Cells[2,1] as Excel.Range;

            range.EntireColumn.Validation.Add(Microsoft.Office.Interop.Excel.XlDVType.xlValidateWholeNumber, Excel.XlDVAlertStyle.xlValidAlertStop,

                                             Excel.XlFormatConditionOperator.xlGreater, "0", Missing.Value);

            range.EntireColumn.Validation.ErrorMessage = "Please enter number only";

            range.EntireColumn.Validation.ErrorTitle = "Number validation"; 

We can apply decimal validation as shown below. 

            Excel.Range range; 

            range = oWS.UsedRange.Cells[2,1] as Excel.Range;

            range.EntireColumn.Validation.Add(Microsoft.Office.Interop.Excel.XlDVType.xlValidateDecimal, Excel.XlDVAlertStyle.xlValidAlertStop,

                                             Excel.XlFormatConditionOperator.xlGreater, "0", Missing.Value);

            range.EntireColumn.Validation.ErrorMessage = "Please enter decimal only";

            range.EntireColumn.Validation.ErrorTitle = "Decimal validation";

Even we can restrict the user to enter specific number characters as shown below.

            Excel.Range range; 

            range = oWS.UsedRange.Cells[2,1] as Excel.Range;

            range.EntireColumn.Validation.Add(Microsoft.Office.Interop.Excel.XlDVType.xlValidateTextLength, Excel.XlDVAlertStyle.xlValidAlertStop,

                                             Excel.XlFormatConditionOperator.xlBetween, "2", "5");

            range.EntireColumn.Validation.ErrorMessage = "Please give input minimum of 2 characters and maximum of 5 characters";

            range.EntireColumn.Validation.ErrorTitle = "Length validation";

As shown above we are restricting user to enter input with minimum of 2 characters and maximum of 5 characters.

                                                                                                                CSharpExcelValidations.zip (1.06 mb)