none
C# Excel - Performance when adding a lot of rows to list object RRS feed

  • Question

  • I have a need to add a lot of new rows to an existing list object, say thousands. I noticed that the performance is really bad when performing the operation using code, but it is very fast when doing the action via right-click context menu.

    When I select 4000 rows, then right-click on the list object > Insert > Table Rows Above, the 4000 rows were added instantly! So I recorded the macro of this action to see what's being called behind the scenes, and noticed that the ListObject.ListRow.Add is being called 4000 times, like this:

    Selection.ListObject.ListRows.Add (2)
    Selection.ListObject.ListRows.Add (3)
    Selection.ListObject.ListRows.Add (4)
    .
    .
    .
    Selection.ListObject.ListRows.Add (4001)

    But now, when I execute this macro which I have recorded, it takes about 10 seconds to add 4000 rows to the list object!

    Why is there a difference between adding ListRows via right-click and via code?

    Even in my C# code, I've disabled ScreenUpdating, disabled EnabledEvents, and set CalculationMode to Manual, but it is still slow.

    One might ask, why can't I just use Range.Insert to add the 4000 rows. It's because my user might have some other data at the sides of the list object, and adding new rows to the entire worksheet might corrupt the data. I just want my operations to impact only the list object.

    It would be great if someone can tell me what I'm missing to match the performance of adding new rows via right-click context menu.

    Thanks!

    Thursday, February 8, 2018 6:03 AM

All replies

  • Hello deejay220989,

    >>When I select 4000 rows, then right-click on the list object > Insert > Table Rows Above, 

    What do you want to do? Is there any data in the selected rows? In my test, once I right-click on the list object, my selection will change to the range where I right click. So what's the function of selecting 4000 rows or did I misunderstand any thing?

    According to your recorded macro, it seem that you are adding 4000 new rows from second list rows of the list object. If so, you could try to refer to below code for testing.

                Excel.ListObject listObject = worksheet.ListObjects[1];
                int rowIndex = listObject.ListRows[2].Range.Row;
                int insertCount = 4000;         
                worksheet.Rows[rowIndex+":"+(rowIndex+ insertCount-1)].Insert(Excel.XlInsertShiftDirection.xlShiftDown, Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
            

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, February 9, 2018 2:51 AM
  • Hi Terry,

    I believe that if I use your method, it is going to add 4000 new rows to the entire worksheet, and I do not want that. I only want to add 4000 new rows to the list object, because there might be some data outside of the list object and I do not want them involved.

    Here's the solution which works for me. Instead of using the worksheet rows as range, I get the range of 4000 rows with the number of columns I have in my list object, then perform the Insert operation on the range.

    var range = worksheet.Range[worksheet.Cells[startRow, firstColumn], worksheet.Cells[startRow + 4000 - 1, lastColumn]];
    range.Insert(Excel.XlInsertShiftDirection.xlShiftDown, Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);

    Tuesday, February 13, 2018 12:59 PM
  • Hello deejay220989,

    I'm glad to hear that you have solved the issue. I suggest you mark your solution as answer to close this thread. Thanks for understanding.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, February 23, 2018 7:57 AM