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.
- Create a new workbook and initialize worksheet.
- Write cue words in cells and format these cells.
- Set validation, including number validation and date validation.
- Save and launch file.
static void Main(string args)
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets;
//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
Click Here to LEARN MORE about Spire.XLS.
Click Here to DOWNLOAD Spire.XLS.