Free Support Forum - aspose.com

Validate an Excel file using another Excel file

Hi.


I am developing a windows service component which will validate a batch of excel file using another excel file. I have one excel file with set of defined rows and columns which will act a template, users will upload their data in excel file with the given format of template excel file. Sometimes they mistype some values or enter erroneous data.

I need to compare the user’s excel with my template excel file.

Please let me know how can we achieve this.

Thanks,
Sandeep

Hi,


Thanks for providing us some details about your requirements.

I am not entirely certain about your requirement. It looks your requirement is how to limit users’ input for cells’ values based on your predefined data from your other template file. For your information, Aspose.Cells is a library which does not provide any UI. The component provides almost all the features of MS Excel (97-2003, 2007/2010/2013 etc.) for the spreadsheet’s file formats (e.g XLS, XLSX, XLSM, XLSB, CSV, ODS, Tab Delimited etc.). I think there might be two possible solutions for your task (you may choose one for your needs):

1) Use data validations to be applied to the cells for the users’ individual files/workbooks. But, mind you, here the matching file’s data should be imported to the file first (you may hide the imported sheets if you want) and then apply data validations to the cells in the worksheet(s) accordingly. To import data from your other spreadsheet to a file, you may copy the worksheets from one workbook to other via Aspose.Cells APIs. Also you may export the data from your matching Workbook’s sheet to fill a DataTable or array and then import data from the data table or arrays to your underlying Excel file via Aspose.Cells APIs accordingly. Please see the document on how to apply different kinds of data validations (e.g List, DateTime, Decimal, etc.) to the worksheet cells:
http://www.aspose.com/docs/display/cellsnet/Data+Filtering+and+Validation#DataFilteringandValidation-validation
2) Use conditional formatting but again the other Excel file’s data should be imported to the underlying workbook first as discussed above. There is no other way to validate a value without loading or importing data from other file to your workbook. Please see the document for your reference here:
http://www.aspose.com/docs/display/cellsnet/Conditional+Formatting

It looks to me that your users would upload their individual files but with respect to your matching Excel file’s data and after filling data into the worksheet cells, and you need to restrict the users to input invalid data into the cells, so the above solutions might work for your needs, so you got to process their files as per above two approaches.

Furthermore, if a validation logic is somewhat custom oriented (that is not accomplished by Ms Excel’s predefined data validations), it is quite possible that you have to load (into the memory) both workbooks in parallel and check(compare) the cell values one by one by yourself.

Thank you.

Thanks for your response. We are not able to make it work. Please take a look at attached excel files; first one is the template “Template-Contacts.xlsx” and second one is the “My-Contacts.xlsx” the input file. All we need to do is validate the input using the template file.

Please help.

Hi,


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.
e.g
Sample code:

//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.