InsertRows / Save very costly

Hi,

in my current project I need to insert a couple of rows (~500) in a sheet that already consists of about 7000 rows. The rows are being inserted in chunks of 50 - 150 rows at once. This is being repeated on 5 sheets of a workbook consisting of 12 sheets. Rows being shifted down by the insert operation are formatted using colors etc. When running through the profiler I see that the most time is spent in InsertRows call which is called just 36 times. The data being inserted is gathererd through SQL statements and even querying the database isn’t slower than inserting the new rows Smile

The second most costly method call seems to be the Save call on the Excel object. The resulting Excel workbook is about 2.32MB in size so I don’t think this should take too long.

Unfortunately I may not send you the designer Excel workbook as it contains intellectual property of the company I’m working for.

Any chances/ideas on how to improve performance in the aforementioned areas (the whole report generation process runs for about 5 minutes - a bit too much for just the bit of data being dealt with)?

Thanks in advance

Regards

Kai

Hi Kai,

Which version are you using? The latest v2.9.7 double speeds up the Save call.

About the InsertRow method, if you want to insert many rows (such as 50 -150), please don’t repeatedly call Cells.InsertRow method. Please call Cells.InsertRows method instead.

Oh, you did call InsertRows method. I will check this issue.

Laurence,

I have just checked to make sure I’m using 2.9.7. and I am. Please note that the Workbook has lots of formatting applied to the various cells.

Kai

Could you please tell me the time to call Save method and the time to call InsertRows in your machine? Thank you.

Laurence,

never mind, it was my fault. I had the profiler not measure the time spent in other threads / applications, i.e. ADO.NET / SQL Server Access. Now everything’s clear - I do spend too much time selecting the data (93% of the whole application).

Sorry for all the hassle.

Regards

Kai

@Kai_Iske,
Aspose.Excel is discarded and is replaced by an advanced product Aspose.Cells that supports all the latest features in different versions of MS Excel. You can insert/delete rows and columns easily using Aspose.Cells too as demonstrated in the following sample code:

// Creating a file stream containing the Excel file to be opened
FileStream fstream = new FileStream(dataDir + "book1.xls", FileMode.Open);

// Instantiating a Workbook object
// Opening the Excel file through the file stream
Workbook workbook = new Workbook(fstream);

// Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.Worksheets[0];

// Inserting 10 rows into the worksheet starting from 3rd row
worksheet.Cells.InsertRows(2, 10);


// Saving the modified Excel file
workbook.Save(dataDir + "output.out.xls");

// Closing the file stream to free all resources
fstream.Close();

Have a look at the following article for more details about inserting/deleting rows and columns:
Inserting and Deleting Rows and Columns

To run this sample code, get the latest free trial version here:
Aspose.Cells for .NET (Latest version

To test the features of this new product download a complete runnable solution here.