Free Support Forum - aspose.com

Error accessing Cells collection when importing a large number of worksheets

Thanks in advance for your help.

I'm getting the following error:

Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index
This occurs on this line after importing 52 + dataview tables in a loop.
agridCells = agridResults.WebWorksheets[m_iSheet].Cells;
Each dataview table is only 7 or 8 columns by 100 to 200 rows. I'm just trying to access the Cells collection so I can format the sheet.
What happens is it imports some dataview tables but after a certain number (varies by query) all variables reset. I'm watching the debugger in VS2005 and the counter incrementing of the number of webworksheets resets back to zero (as wells as all other locals I'm viewing).
Is there a memory limit or limit to the number of webworksheets on which I can access the Cells collection? If I do not access the Cells collection then I can import hundreds of tables with no problem.
Here is the code. If you want I can provide a sample workbook of data for testing.

private void PopulateAsposeGrid()

{

ArrayList al = this._alUserChoiceList;

ArrayList al2 = null;

DataView dv = null;

SC.Item oItem = null;

WebCells agridCells = null;

int m_iSheet = 0;

if ((al != null) && (al.Count > 0))

{

agridResults.WebWorksheets.Clear();

// for each segment selected go the the db and fetch

for (int i = 0; i < al.Count; i++)

{

al2 = new ArrayList();

oItem = new SC.Item();

oItem.Text = ((SC.Item)al[i]).Text;

oItem.ID = ((SC.Item)al[i]).ID;

al2.Add(oItem);

using (SD.Opportunity o = new SD.Opportunity())

{

o.GetOpportunityScores(_SubStudyID, _StudyTypeID, al2,

_FilterFlag, _FilterValue, _FilterValue2, _FilterValueString,

_ImportanceFilterClause, _SatisfactionFilterClause,

_OpportunityFilterClause, _RankFilterClause, _IncludeOpportunity,

_IncludeProcessStep, _IncludeThemes, _IncludeRank,

_SubStudyName, _Sort);

if (o.DataTable.Rows.Count > 0)

{

dv = new DataView(o.DataTable);

this.agridResults.WebWorksheets.ImportDataView(dv, null, null, oItem.Text, 0, 0);

agridCells = agridResults.WebWorksheets[m_iSheet].Cells;

//format cells, set row height, etc.

m_iSheet++;

}

}

}

}

}

Hi,

Do you import the dataview tables into a single worksheet (horizontally / vertically) or in multiple worksheets. And No, there is no restriction adding worksheets into a workbook, it depends on your system resources (memory etc.) too.

Could you create a sample test project to show the issue and post all the .cs files, .aspx, .xls and other files etc. here. I think you can zip the project to attach it here. We will check it soon.

And which version you are using, did you try 1.9.4.5.

Thank you.

Multiple worksheets. I’ll see if I can put a test project together.

I just upgraded to version 1.9.4.0 and am still getting the same error. I’ll work on a sample test.

Hi,

This line has a problem:

this.agridResults.WebWorksheets.ImportDataView(dv, null, null, oItem.Text, 0, 0);

ImportDataView method has two overload:

1. void ImportDataView(DataView, TableItemStyle, TableItemStyle);

When using this formation, the control always create a new sheet and the sheet's name is the same as the DataView's table name.

2. void ImportDataView(DataView, TableItemStyle, TableItemStyle, string sheetName, int row, int column);

When using this formation, if there is already a sheet named with the given sheetName, the sheet will be reused and no new sheet will be created. So the m_iSheet will be larger than the sheet's count.

So please check the oItem.Text and ensure they are unique for each DataView. Or you may use the formation 1.

Good catch! I hadn't realized that there might be duplicate item names, thus duplicate sheets names which would throw off the m_iSheet incrementor.

I went back and checked the dataset in the sample data I was working with and sure enough, there were duplicate names in the item list. When I used the #1 overload method above all the dataview tables imported without a problem.

Thanks for your insight. You guys do a great job.

Rob