sheet.InsertRow degrading with each additional insert

[Todd Davis]

Here is our situation. We have an xls template file that we use to generate a new book/sheet. We iterate on the sheet''s names, and if it matches the format "Table.abcd" we do an ImportDataTable on table abcd from a supplied DataSet.

The problem arises if we attempt to do an InsertRow for each incoming row in the data table before we do the import. This is desirable because if there is "footer" information below the table placeholder, we would like the import to happen within an area that won''t overwrite other cells in our template. It works fine if the importing table has just a few rows, but if we attempt to import a table with as little as 255 rows, asp.net hits hundreds of megs of usage, CPU pegs, and the entire request times out. Removing these lines solved the problem:

for (int i = 0; i < table.Rows.Count - 1; i++)
{
templateSheet.InsertRow(startingRow.Row + 1);
}

In a future release, will inserting a block of rows at once be supported or will the degenerative nature of calling InsertRow more than once or twice be addressed? Is there a work around that will solve out issue that we could use but are not?

Thanks,
Todd

[Administrator]

Hi Todd, Sorry for the delay in getting back to you. I am able to see the issue with memory usage when rows are inserted. We will look into this issue. 1) As a temporary workaround you could use IRange.MoveTo to accompolish what you require. See attached sample. InsertRows 2) We will also look into having a method for inserting several rows at once. Thanks, Stephen. >Here is our situation. We have an xls template file that we use to generate a new book/sheet. We iterate on the sheet''s names, and if it matches the format "Table.abcd" we do an ImportDataTable on table abcd from a supplied DataSet. > >The problem arises if we attempt to do an InsertRow for each incoming row in the data table before we do the import. This is desirable because if there is "footer" information below the table placeholder, we would like the import to happen within an area that won''t overwrite other cells in our template. It works fine if the importing table has just a few rows, but if we attempt to import a table with as little as 255 rows, asp.net hits hundreds of megs of usage, CPU pegs, and the entire request times out. Removing these lines solved the problem: > >for (int i = 0; i < table.Rows.Count - 1; i++) >{ > templateSheet.InsertRow(startingRow.Row + 1); >} > >In a future release, will inserting a block of rows at once be supported or will the degenerative nature of calling InsertRow more than once or twice be addressed? Is there a work around that will solve out issue that we could use but are not? > >Thanks, >Todd

[Administrator]

Hi Todd,

You can track the live status of this issue here

http://www.syncfusion.com/support/issues/excelrw/Default.aspx?ToDo=view&questId=236

Thanks,

Stephen.

>Here is our situation. We have an xls template file that we use to generate a new book/sheet. We iterate on the sheet''s names, and if it matches the format "Table.abcd" we do an ImportDataTable on table abcd from a supplied DataSet.
>
>The problem arises if we attempt to do an InsertRow for each incoming row in the data table before we do the import. This is desirable because if there is "footer" information below the table placeholder, we would like the import to happen within an area that won''t overwrite other cells in our template. It works fine if the importing table has just a few rows, but if we attempt to import a table with as little as 255 rows, asp.net hits hundreds of megs of usage, CPU pegs, and the entire request times out. Removing these lines solved the problem:
>
>for (int i = 0; i < table.Rows.Count - 1; i++)
>{
> templateSheet.InsertRow(startingRow.Row + 1);
>}
>
>In a future release, will inserting a block of rows at once be supported or will the degenerative nature of calling InsertRow more than once or twice be addressed? Is there a work around that will solve out issue that we could use but are not?
>
>Thanks,
>Todd