I added cell validation to my workbook, and it corrupts the file. What is causing this? Is there a workaround?
Hi,
Thanks for considering Aspose.
How do you implement validation and which version of Aspose.Cells you are using and are you using Aspose.Cells for .Net or Java?
Aspose.Cells supports validation of every type.
I use the following sample code using Aspose.Cells for .Net version 4.1.1. and the results are perfect and the out file it fine.
Workbook workbook = new Workbook();
Cells cells = workbook.Worksheets[0].Cells;
cells["A1"].PutValue("Please enter a number other than 0 to 10 in B1 to activate data validation:");
cells["A1"].Style.IsTextWrapped = true;
cells.SetRowHeight(0, 31);
cells.SetColumnWidth(0, 35);
cells["B1"].PutValue(5);
Validations validations = workbook.Worksheets[0].Validations;
Validation validation = validations[validations.Add()];
//Set the data validation type
validation.Type = 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 = "10";
validation.ShowError = true;
//Set the validation alert style
validation.AlertStyle = ValidationAlertType.Information;
//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 10";
//Set the data validation input message
validation.InputMessage = "Data Validation using Condition for Numbers";
validation.IgnoreBlank = true;
validation.ShowInput = true;
validation.ShowError = true;
//Set a collection of CellArea for B1 which contains the data validation settings
CellArea cellArea;
cellArea.StartRow = 0;
cellArea.EndRow = 0;
cellArea.StartColumn = 1;
cellArea.EndColumn = 1;
validation.AreaList.Add(cellArea);
workbook.Save("d:\\testvalidation.xls");
Could you send us here your sample code with the generated output excel file, so that we may resolve the issue soon,
Thank you.
Here is the code.
// Add validation for this sheet.
Validations validations = productSheet.Validations;
sizeCellValidation = validations[validations.Add()];
sizeCellValidation.Type = ValidationType.WholeNumber;
sizeCellValidation.Formula1 = “=0”;
sizeCellValidation.AlertStyle = ValidationAlertType.Stop;
sizeCellValidation.Operator = OperatorType.GreaterOrEqual;
sizeCellValidation.ErrorMessage = “You can only enter whole numbers!”;
// Add this cell to the size validation.
CellArea cellArea = new CellArea();
cellArea.StartColumn = Convert.ToByte( column );
cellArea.StartRow = rowNum - 1;
cellArea.EndColumn = Convert.ToByte( column );
cellArea.EndRow = rowNum - 1;
sizeCellValidation.AreaList.Add( cellArea );
The document doesn’t get corrupted until there is a certain amount of validations added. I don’t know the exact number, but I think it’s somewhere around 300-400 cells.
Thanks.
Please post your corrupt file here. Then I can figure out what caused this problem. Thank you.
Hi,
Did you implement your code with the latest version (4.1.1)?
For you, I test the following code with 400 validations and the output file is fine and validations are ok.
Workbook workbook = new Workbook();
Cells cells = workbook.Worksheets[0].Cells;
cells["A1"].PutValue("Please enter a number other than 0 to 10 in into (C1..C400) to activate data validation:");
cells["A1"].Style.IsTextWrapped = true;
cells.SetRowHeight(0, 35);
cells.SetColumnWidth(0, 45);
Validations validations = workbook.Worksheets[0].Validations;
Validation validation;
for (int i = 0;i<400;i++)
{
validation = validations[validations.Add()];
//Set the data validation type
validation.Type = 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 = "10";
validation.ShowError = true;
//Set the validation alert style
validation.AlertStyle = ValidationAlertType.Information;
//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 10";
//Set the data validation input message
validation.InputMessage = "Data Validation using Condition for Numbers";
validation.IgnoreBlank = true;
validation.ShowInput = true;
validation.ShowError = true;
//Set a collection of CellArea for B1 which contains the data validation settings
CellArea cellArea;
cellArea.StartRow = i;
cellArea.EndRow = i;
cellArea.StartColumn = 1;
cellArea.EndColumn = 1;
validation.AreaList.Add(cellArea);
}
workbook.Save("d:\\testvalidation.xls");
We will be really grateful if you could create a sample console application that generates the corrupted output file and post us here. So, that we may figure out and resolve your issue very soon.
Thanks for your workaround and information.
I'm using 4.1.1.0. I also tried 4.1.1.7, and that didn't work either.
I attached the corrupt file.
I will see if I can create a sample application to upload here sometime today.
I'm not able to make this post private, can you do that for me?
I’m trying to reproduce the error in a console app, but not having any luck. I’m using quite a bit of styles with the workbook also, so maybe that has something to do with hit.
When I open the file, excel does a repair on it and all the styles are missing, the validation is missing, and an image on the sheet is missing.
Hi,
Thanks for the file and information.
We will figure out the issue and catch up you soon.
For private messaging you may send your posts or projects @ nanjing@aspose.com
Our customers fully rely on us as we deal our customers with higher priority and keep their posts and confidential data with great care.
Thanks for your considerations
Hi,
For your information, When you post any attachment file here, only Aspose developers and you can download the attachement. Other users cannot download your attachment. So you may also post your attachments here without any difficulty what so ever.
Thank you.
Amjad Sahi:
Hi,
For your information, When you post any attachment file here, only Aspose developers and you can download the attachement. Other users cannot download your attachment. So you may also post your attachments here without any difficulty what so ever.
Thank you.
That's good to know. Thanks.
How long do you think it will be to create a fix for this?
I’d like to be able to push this live this weekend if possible.
Thanks.
Hi,
We have worked on your problem. I think your corrupted file is not very complex. So, we are facing difficulties to reproduce your problem. We will be really grateful if you could create a sample console application that generates the corrupted output file and post us here. That will help us to resolve your issue very soon.
Thank you.
I was able to reproduce the error. The solution is attached.
Hi,
Thanks for the project, We will check and resolve it soon.
Regards,
Hi,
The problem is caused by that you have added too many areas to a validation.
int column = 10;
for( int i = 0; i < 10; i++ )
{
// Add this cell to the size validation.
CellArea cellArea = new CellArea();
cellArea.StartColumn = Convert.ToByte( column );
cellArea.StartRow = rowNum - 1;
cellArea.EndColumn = Convert.ToByte( column );
cellArea.EndRow = rowNum - 1;
sizeCellValidation.AreaList.Add( cellArea );
column++;
}
Actually you can merge those areas to change your code to:
int column = 10;
CellArea cellArea = new CellArea();
cellArea.StartColumn = Convert.ToByte( column );
cellArea.StartRow = rowNum - 1;
cellArea.EndColumn = Convert.ToByte( column + 10);
cellArea.EndRow = rowNum - 1;
sizeCellValidation.AreaList.Add( cellArea );
And I think you can also optimize with adjacent rows. For example:
int column = 10;
CellArea cellArea = new CellArea();
cellArea.StartColumn = Convert.ToByte( column );
cellArea.StartRow = rowNum - 1;
cellArea.EndColumn = Convert.ToByte( column + 10);
cellArea.EndRow = rowNum + table.Rows.Count - 1;
sizeCellValidation.AreaList.Add( cellArea );
The last piece of code is just a hint/tip for optimization. So, you may enhance your code for your need for better performance.
Thank you.
The problem with merging is the rows are dynamic, but I should be able to do that.
Is this a bug then? Will there be a fix in a new release?
Thanks.