Free Support Forum - aspose.com

Use regex style search in data validation using Aspose.Cells for .NET

I want to put some validation controls in dynamically generated excel file. The scenario is: I generate the excel file and hand it to the user for download.Once they download, they fill out the excel file. Suppose C Column is a field which can take either date of birth or simply date in a specific format (mm/dd, mm/dd/yyyy etc). The problem is this can be done truly in Aspose.web.cells with validation using regex. I could not find the same feature in Aspose.cells. I want to make sure if regex or similar dynamic pattern match is possible in Aspose.cells. I thought I can use ValidationType.Date but this does not help when I am expecting date of birth which is (mm/dd), excel puts year at the end automatically.

I hope I explained everything here. I hope this can be done easily by using validationtype.custom or validationtype.anyvalue.If yes, could you also provide a sample code snippet.

Thank you,
-Soven

Hi Soven,

Thanks for considering Aspose.

May the following sample code help you implement your requirement, kindly consult it:

Sample code:

Workbook workbook = new Workbook();
Cells cells = workbook.Worksheets[0].Cells;
Validations validations = workbook.Worksheets[0].Validations;
Validation validation = validations[validations.Add()];
//Set the data validation type
validation.Type = Aspose.Cells.ValidationType.Date;
//Set the operator for the data validation
validation.Operator = OperatorType.Between;
//the value or expression associated with the second part of the data validation
validation.Formula1 = "1/1/2008";
//Set the value or expression associated with the data validation
validation.Formula2 = "8/15/2008";
validation.ShowError = true;
//Set the validation alert style
validation.AlertStyle = ValidationAlertType.Stop;
//Set the title of the data-validation error dialog box
validation.ErrorTitle = "Date Error";
//Set the data validation error message
validation.ErrorMessage = " Enter a Valid Date";
//Set the data validation input message
validation.InputMessage = "Date Validation Type";
validation.IgnoreBlank = true;
validation.ShowInput = true;
validation.ShowError = true;
//Set a collection of CellArea (C1:C100) which contains the data validation settings
CellArea cellArea;
cellArea.StartRow = 0;
cellArea.EndRow = 99;
cellArea.StartColumn = 2;
cellArea.EndColumn = 2;
validation.AreaList.Add(cellArea);

Range range = cells.CreateRange("C1", "C100");
Style style = workbook.Styles[workbook.Styles.Add()];
style.Custom = "mm/dd";
StyleFlag flag = new StyleFlag();
flag.NumberFormat = true;
range.ApplyStyle(style,flag);

workbook.Save("f:\\test\\outcustomdatevalidation1.xls", FileFormatType.Excel2003);

Thank you.