Thanks for the template files.
I have written a sample code to accomplish your task for the first column (A2:A5) cells only in your user file. The code first evaluates the data validation applied in the original template file’s first worksheet’s first column cell (A2), it retrieves the data validation type (e.g Whole Number) attributes applied to that cell. Now it loads the user’s file and then iterates through the cells in the first column (A2:A5) of the first worksheet, it obtains the value from each cell in the area (A2:A5) and if it is a numeric value, it then re-inserts that value to original template file’s A2 cell(each time) and checks if the value satisfies the data validation rule or not. If the cell value is not valid, it prints some messages on console output and also eliminates the value and makes the cell empty etc. Please refer to the code segment and you may write your own code to evaluate the data validation rules applied on other cells accordingly.
//Instantiate the workbook from template Excel file
Workbook workbook = new Workbook(“e:\test2\Template-Contacts.xlsx”);
//Access its first worksheet
Worksheet worksheet = workbook.Worksheets[0];
//Cell A2 has the Whole number Validation applied on it.
//It can take only the values Between greater than 1
Cell cell = worksheet.Cells[“A2”];
//Access the valditation applied on this cell
Validation validation = cell.GetValidation();
//Read various properties of the validation
Console.WriteLine(“Reading Properties of Validation”);
Console.WriteLine("--------------------------------");
Console.WriteLine("Type: " + validation.Type);
Console.WriteLine("Operator: " + validation.Operator);
Console.WriteLine("Formula1: " + validation.Formula1);
Console.WriteLine("Formula2: " + validation.Formula2);
Console.WriteLine("Ignore blank: " + validation.IgnoreBlank);
//Instantiate user’s workbook sample Excel file
Workbook workbook1 = new Workbook(“e:\test2\My-Contacts.xlsx”);
//Access the first worksheet
Worksheet worksheet1 = workbook1.Worksheets[0];
//Iternate the first column cells (starting from A2 to A5 etc.) to match with the validation applied in the template file
for (int i = 1; i <= worksheet1.Cells.MaxDataRow; i++)
{
//Get the cell value
Cell cell1 = worksheet1.Cells[“A” + (i+1).ToString()];
if (cell1.Type == CellValueType.IsNumeric)
{
//Now input the value to the original template file’s sheet cell for evaluation:
cell.PutValue(cell1.IntValue);
//Check if number satisfies the Data Validation rule applied on the cell in the original template file.
Console.WriteLine("Cell " + cell1.Name + ": " + "Is this a Valid Value for this Cell: " + cell.GetValidationValue());
if (cell.GetValidationValue() == false)
{
Console.WriteLine("Cell " + cell1.Name + ": " + “This is not a valid valid, so it should be eliminated from the cell”);
cell1.PutValue(null);
}
}
else
{
Console.WriteLine("Cell " + cell1.Name + ": " + “This is rather a string value, so it should be eliminated from the cell”);
cell1.PutValue(null);
}
}
workbook1.Save(“e:\test2\out1.xlsx”);
Hope, this helps a bit.
Thank you.