Data import validation

In my web application, I am allowing the user to import excel files. Those files need to be in a specific format. Meaning the first column should contain a number and the second should either contain 0,1 or empty. The problem is that I can not check for the column type, because it can be string even if the value is 0 or 1 and unfortunately our users are not smart enough to always format the column to be numeric. I need to somehow make sure that the 2nd column does not contain anything besides 0,1 or empty. Is there a way to achieve that?


This message was posted using Email2Forum by DannyCooper.
Hi,

Thanks for considering Aspose.

As far as i can understand your need, I think you may scan through all the cells in the second column using a loop and utilize some properties like Cell.IntValue/Cell.StringValue and Cell.Value (for checking if the cell value is null) in if{}condition to check whether the values are 0,1or empty: You can use Cell.Type property as well. (Attached is the template file for the following code)

E.g.,

Workbook workbook = new Workbook();
workbook.Open("d:\\test\\checkcellvalue.xls");
Cells cells = workbook.Worksheets[0].Cells;
Cell cell;
//Scan the second column.
for (int i= 0;i<=cells.MaxDataRowInColumn(1);i++)
{ cell = cells[i,1];
if(cell.Value ==null)
{
continue;
}
if(cell.IntValue ==0 |cell.IntValue ==1)
{
//OK

}

else
{
//No valid
MessageBox.Show("Not Valid " + "Cell: " + cell.Name + " Value: " + cell.Value.ToString());
}
}

And if you want to force data validation that the user should not be able to insert values other than 0,1 or empty, please try to consult the following code:

E.g.,

Workbook workbook = new Workbook();
Cells cells = workbook.Worksheets[0].Cells;
Aspose.Cells.Validations validations = workbook.Worksheets[0].Validations;
Aspose.Cells.Validation validation = validations[validations.Add()];
//Set the data validation type
validation.Type = Aspose.Cells.ValidationType.WholeNumber;
//Set the operator for the data validation
validation.Operator = OperatorType.Between;
//Set the value or expression associated with the data validation
validation.Formula1 = "0";
//the value or expression associated with the second part of the data validation
validation.Formula2 = "1";
validation.ShowError = true;
//Set the validation alert style
validation.AlertStyle = ValidationAlertType.Stop;
//Set the title of the data-validation error dialog box
validation.ErrorTitle = "Error";
//Set the data validation error message
validation.ErrorMessage = " Enter value between 0 to 1";
//Set the data validation input message
validation.InputMessage = "Data Validation using Condition for Numbers";
validation.IgnoreBlank = true;
validation.ShowInput = true;
validation.ShowError = true;
//Also accept empty value
validation.IgnoreBlank = true;
//Set a collection of CellArea for B Column for first 1000 rows
CellArea cellArea;
cellArea.StartRow = 0;
cellArea.EndRow = 999;
cellArea.StartColumn = 1;
cellArea.EndColumn = 1;
validation.AreaList.Add(cellArea);
workbook.Save("data_validation.xls", FileFormatType.Default, SaveType.OpenInExcel, Response);

Thank you.