The repro code listed below:
class Program
{
static void Main(string[] args)
{
Workbook wb = new Workbook();
Worksheet sheet1 = wb.Worksheets[0];
for (int i = 1; i <=500; ++i)
{
sheet1.Cells[“A” + i].PutValue(i * 100);
}
Range range = sheet1.Cells.CreateRange(0, 0, 500, 1);
range.Name = “Numbers”;
Worksheet sheet2 = wb.Worksheets[wb.Worksheets.Add()];
Validation validation = CreateValidation(sheet2, “Numbers”);
for (int i = 0; i < 1000; ++i)
{
for (int j = 0; j < 40; ++j)
{
CellArea listArea = new CellArea();
listArea.StartRow = i;
listArea.EndRow = i;
listArea.StartColumn = j;
listArea.EndColumn = j;
if (validation.AreaList.Count >= 50)
{
validation = CreateValidation(sheet2, “Numbers”);
}
validation.AreaList.Add(listArea);
}
}
wb.Save(“Repro.xls”);
}
private static Validation CreateValidation(Worksheet sheet, string rangeName)
{
Validation listVal = sheet.Validations[sheet.Validations.Add()];
listVal.Type = ValidationType.List;
listVal.Operator = OperatorType.None;
listVal.InCellDropDown = true;
listVal.ShowError = true;
listVal.AlertStyle = ValidationAlertType.Warning;
listVal.ErrorTitle = “Error”;
listVal.ErrorMessage = “The value is invalid. Please select from the list.”;
listVal.Formula1 = String.Format("={0}", rangeName);
return listVal;
}
}
If delete one row, the excel will be hang for a while and after that, some validation dropdownlist is disabled and doesn’t work properly.
For example, I delete line 990, see attached that lines above 990 do now work anymore
Hi,
Thanks for the sample code and the output file.
I can notice the issue as you have mentioned. After running your sample code, I generated the output file. I opened the file into MS Excel. When I delete line 990 in the second sheet, it hangs it a bit for a while and then the lines above 990 do now work anymore.
I have logged a ticket with an id “CELLSNET-41492” for your issue. We will look into soon.
Once we have any update on it, we will let you know here.
Thank you.
Hi,
Sorry for the delay and addressing your issue late.
This is to inform you that we have fixed your issue now. We will soon provide the fixed version after performing QA and including other enhancements and fixes.
Thank you.
Hi,
Well, there are two possible solutions:
a) Please apply validation to a whole continuous range, not huge continuous ranges.
See the following codes:
e.g
Sample code:
Validation validation = CreateValidation(sheet2, "Numbers");
CellArea listArea1 = new CellArea();
listArea1.StartRow = 0;
listArea1.EndRow = 1000;
listArea1.StartColumn = 0;
listArea1.EndColumn = 40;
validation.AddArea(listArea1);
b) Please try the latest version/ fix (Aspose.Cells for .NET v8.6.2.5) and set XlsSaveOptions.MergeAreas as true to merge the validations before saving the file.
See the following sample codes:
e.g
Sample code:
XlsSaveOptions saveOptions = new XlsSaveOptions();
saveOptions.MergeAreas = true;
wb.Save(path + "Repro.xls",saveOptions);
Let us know your feedback.
Thank you.
The issues you have found earlier (filed as CELLSNET-41492) have been fixed in this update.
This message was posted using Notification2Forum from Downloads module by Aspose Notifier.