C＃Excel - 向列表对象添加大量行时的性能 [英] C# Excel - Performance when adding a lot of rows to list object

问题描述

我需要在现有列表对象中添加大量新行，比如数千。我注意到使用代码执行操作时性能非常差，但是通过右键单击上下文菜单执行操作时速度非常快。

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我选择4000行，然后右键单击列表对象>插入>表行上面，立即添加了4000行！所以我记录了这个动作的宏来看看幕后叫什么，并注意到ListObject.ListRow.Add
被调用4000次，如下所示：

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)

但是现在，当我执行我记录的这个宏时，向列表对象添加4000行大约需要10秒！

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

为什么通过右键单击和通过代码添加ListRows之间有区别？

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

即使在我的C＃代码中，我已禁用ScreenUpdating，禁用EnabledEvents，并将CalculationMode设置为Manual，但它仍然很慢。

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

有人可能会问，为什么我不能只使用Range.Insert添加4000行。这是因为我的用户可能在列表对象的两侧有一些其他数据，并添加新的行到整个工作表可能会破坏数据。我只希望我的操作只影响
列表对象。

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.

谢谢！

推荐答案

Hello deejay220989，

Hello deejay220989,

>> 当我选择4000行时，然后右键单击列表对象>插入>上面的表行， 

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

你想做什么？所选行中是否有数据？在我的测试中，一旦我右键单击列表对象，我的选择将改变到我右键单击的范围。那么选择4000行的功能是什么
或者我误解了什么？

根据您录制的宏，您似乎从列表对象的第二个列行添加了4000个新行。如果是这样，您可以尝试参考下面的代码进行测试。

            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);
        

最好的问候，

Terry

这篇关于C＃Excel - 向列表对象添加大量行时的性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！