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
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
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.
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.
Could you please tell me the time to call Save method and the time to call InsertRows in your machine? Thank you.
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.