My spreadsheet generation has worked fine until I upped the amount of data I am testing with. I have previously hit the 65000 row limit and that gives a different error but I've changed the code since then to reduce the output and it now all works fine until I call Save().
It's a bit tricky to quote my code as it is heavily integrated with business logic components but it's basically just lots of fairly short rows, most with validations applied.
A search brought up this error and similar issues but given that I bought very recently, I assume I have the latest version and so this is not affected by any fixes to those problems. Version of the Aspose.Excel assembly is showing as 3.0.4.0.
Urgent help appreciated. Thanks.
Stack trace doesn't look very helpful but here it is:
[IndexOutOfRangeException: Index was outside the bounds of the array.]
System.String.get_Chars(Int32 index) +20
?.?.?(String ?)
?.?.?(Validation ?, Int32 ?)
?.?.?(Validation ?)
Aspose.Excel.Worksheet.?(? ?)
Aspose.Excel.Worksheets.?(? ?)
Aspose.Excel.Worksheets.?(String ?, FileFormatType ?, MemoryStream& ?)
[Exception: Errors in Excel Save method:0]
Aspose.Excel.Worksheets.?(UInt32 ?, Exception ?)
Aspose.Excel.Worksheets.?(String ?, FileFormatType ?, MemoryStream& ?)
Aspose.Excel.Worksheets.?(String ?, SaveType ?, FileFormatType ?, HttpResponse ?)
[Exception: Errors in Excel Save method:0]
Aspose.Excel.Worksheets.?(UInt32 ?, Exception ?)
Aspose.Excel.Worksheets.?(String ?, SaveType ?, FileFormatType ?, HttpResponse ?)
Aspose.Excel.Excel.Save(String fileName, FileFormatType fileFormatType)
Entropy.Envoy.Web.Management.Site.Audit.ExportChecklist.Page_Load(Object sender, EventArgs e) in c:\sourcesafe\entropy system\envoy enterprise\entropysystemsolution\entropy.envoy.web\management\site\audit\exportchecklist.aspx.cs:83
System.EventHandler.Invoke(Object sender, EventArgs e) +0
System.Web.UI.Control.OnLoad(EventArgs e)
System.Web.UI.Control.LoadRecursive()
System.Web.UI.Page.ProcessRequestMain()
Hmmm. Taking out the validations makes it work, is there some kind of limit on validations, should I be applying identical validations to ranges of cells (I currently apply each individually). Is this some known problem? I am fairly sure my validations are valid (!) because they work fine with lesser amounts of data.
Could you please post your template and sample code here? I will check this issue. And you can try this attached fix. We did fix a bug related to data validations.
Hi Laurence.
The fix does not seem to have affected the problem.
I am not using a template approach - I am generating the whole thing from scratch so I’m not sure I can post anything useful. I can give you a bit more info about the amounts of validations:
Originally my validations collection contained 19404 separate validations. I have tried to reduce this by having 7938 separate “List” validations and just one TextLength validation with 11466 AreaList entries so I now just have 7939 validations in total but I still get the same error.
I take it 7939 is greater than a size limit on some internal array which manipulates the validations at some point?
Do you add 11466 AreaList to just one TextLength validation? I think that caused the problem.
Why so many areas? Could you combine them into fewer areas? Each area represents a rectangle range. So you may combine some of them.
The TextLength validations are on individual cells throughout the sheet.
With a few experiments I think it is the total number of validations that is the problem. I am narrowing it down but even 500 separate validations added to the collection causes an error.
There must be a limit somewhere in the assembly code - do you know what it is? If it can be increased (massively!) that would be fantastic. If not, I am going to have to drop some but I suggest that the collection should throw an exception when too many are added rather than the whole class fall over when Save() is called.
I don’t think the number of validations caused the problem but the TextLength validations with 11466 AreaList. You can try to remove all other validations but leave this one in your program. And you can still meet this problem.
I will check and fix this issue. And your test result will help me. Thank you.
I only tried using more entries in the AreaList as a possible solution. Going back to my original code I still have the problem.
So, the thing that definitely causes the problem is too many entries in the Worksheet.Validations collection. Each one only has a single entry in the AreaList collection which is how I started.
What’s the minimal entries of validations caused your problem? Could you please just post your code for setting these data validations? Thank you very much.
It’s when I add more than 330 validations. As soon as there are 331 or more, Save() falls over. My function which adds them to the sheet looks like this:
void AddCellValidation(Aspose.Excel.ValidationType type, Int32 row, byte col, System.Object extraInfo1, System.Object extraInfo2)
{
Validation val = excel.Worksheets[“Audit Export”].Validations[excel.Worksheets[“Audit Export”].Validations.Add()];
val.Type = type;
switch(type)
{
case Aspose.Excel.ValidationType.TextLength:
{
// Validation on text length. ExtraInfo params not used.
val.Operator = OperatorType.LessOrEqual;
val.Formula1 = “4000”;
val.ErrorTitle = “Error”;
val.InputMessage = “Text must not exceed 4000 characters”;
break;
}
case Aspose.Excel.ValidationType.Decimal:
{
// Validation of numeric value. ExtraInfo params define upper and lower allowed values.
val.Operator = OperatorType.Between;
val.Formula1 = extraInfo1.ToString();
val.Formula2 = extraInfo2.ToString();
val.ErrorTitle = “Number out of range”;
val.InputMessage = "Enter a number between " + extraInfo1.ToString() + " and " + extraInfo2.ToString();
val.ErrorMessage = "Enter a number between " + extraInfo1.ToString() + " and " + extraInfo2.ToString();
break;
}
case Aspose.Excel.ValidationType.List:
{
// Validation of cell against list of values. ExtraInfo1 provides comma-sep list of values.
val.Formula1 = extraInfo1.ToString();
val.ErrorTitle = “Invalid entry”;
val.InputMessage = “Please select an answer”;
val.ErrorMessage = “Please select an answer”;
break;
}
}
// Apply this validation to the given cell
CellArea area = new CellArea();
area.StartRow = row;
area.EndRow = row;
area.StartColumn = col;
area.EndColumn = col;
val.AreaList.Add(area);
}
The following is my test code but don't find the problem:
Excel excel = new Excel();
Worksheet sheet = excel.Worksheets[0];
Validations validations = sheet.Validations;
for(int i = 0; i < 1331; i ++)
{
Validation val = validations[validations.Add()];
val.Type = ValidationType.List;
val.Formula1 = "1,2,3,4,5";
val.ErrorTitle = "Invalid entry";
val.InputMessage = "Please select an answer";
val.ErrorMessage = "Please select an answer";
// Apply this validation to the given cell
CellArea area = new CellArea();
area.StartRow = i * 2;
area.EndRow = i * 2;
area.StartColumn = 0;
area.EndColumn = 0;
val.AreaList.Add(area);
}
for(int i = 0; i < 1331; i ++)
{
Validation val = validations[validations.Add()];
val.Type = ValidationType.TextLength;
val.Operator = OperatorType.LessOrEqual;
val.Formula1 = "4000";
val.ErrorTitle = "Error";
val.InputMessage = "Text must not exceed 4000 characters";
// Apply this validation to the given cell
CellArea area = new CellArea();
area.StartRow = i * 2 + 3000;
area.EndRow = i * 2 + 3000;
area.StartColumn = 0;
area.EndColumn = 0;
val.AreaList.Add(area);
}
for(int i = 0; i < 1331; i ++)
{
Validation val = validations[validations.Add()];
val.Operator = OperatorType.Between;
val.Formula1 = i.ToString();
val.Formula2 = (i + 100).ToString();
val.ErrorTitle = "Number out of range";
val.InputMessage = "Enter a number between " + i.ToString() + " and " + (i + 100).ToString();
val.ErrorMessage = "Enter a number between " + i.ToString() + " and " + (i + 100).ToString();
// Apply this validation to the given cell
CellArea area = new CellArea();
area.StartRow = i * 2 + 6000;
area.EndRow = i * 2 + 6000;
area.StartColumn = 0;
area.EndColumn = 0;
val.AreaList.Add(area);
}
Could you please check what's the difference? Thank you very much.
Thanks Laurence.
That code works for me too. This is odd.
Is there anything in the stacktrace that helps? A lot of it is garbled for me but is that last line (where it seems to be indexing a string?) the problem - the “index” the error is talking about?
I know you would ideally be able to reproduce the error first but is there anything you can look at in that area whilst I try to re-produce it outside my application?
You said that “As soon as there are 331 or more, Save() falls over”. Could you please post the file with just 330 validations created with your code? Thank you.
I checked your file but don’t find the clue.
But I don’t think this problem is caused by number of validations. Instead I think maybe it’s caused by the setting by one of you validations. Could you please print out all the settings of that extra validation which make it crash?
Thank you.
Excellent! Good thinking Laurence!
Turns out that the 331st validation, a List, is not being supplied data for its items and this makes it fall over. The error must be coming from where your code tries to chop up the comma-separated string of list items.
Got there in the end, thanks very much for your help.
R
Also thanks for your report. I will enhance the exception error message to give you clearer clues for these kinds of errors.