Excel Interop - эффективность и производительность

Ответ 1

При использовании С# или VB.Net для получения или установки диапазона выясните, что такое общий размер диапазона, а затем получите один большой массив 2-мерных объектов...

//get values
object[,] objectArray = shtName.get_Range("A1:Z100").Value2;
iFace = Convert.ToInt32(objectArray[1,1]);

//set values
object[,] objectArray = new object[3,1] {{"A"}{"B"}{"C"}};
rngName.Value2 = objectArray;

Обратите внимание, что важно знать, какой тип данных Excel хранится (текст или цифры), поскольку он не будет автоматически делать это для вас, когда вы преобразовываете тип обратно из массива объектов. Добавьте тесты, если необходимо, чтобы проверить данные, если вы не можете быть уверены в том, что тип данных.

Ответ 2

Это для тех, кто задается вопросом, что лучший способ - заполнить лист excel из набора результатов db. Это не значит, что это полный список, но он перечисляет несколько вариантов.

Некоторые показатели производительности при попытке заполнить листы Excel с 155 столбцами и 4200 записей на старом полете Pentium 4 3GHz, включая время поиска данных, которое не превышало 10 секунд, в порядке наименьшей скорости, выглядит следующим образом:

• Одна ячейка за раз - чуть менее 11 минут

• Заполнение набора данных путем преобразования в html + Сохранение html на диск + Загрузка html в excel и сохранение листа в виде xls/xlsx - 5 минут

• Один столбец за раз - 4 минуты

• Использование устаревшей процедуры sp_makewebtask в SQL 2005 для создания файла HTML - 9 секунд + после чего загружается файл html в excel и сохраняется как XLS/XLSX - около 2 минут.

• Преобразуйте набор данных .Net в ADO RecordSet и используйте функцию WorkSheet.Range []. CopyFromRecordset для заполнения excel - 45 секунд!

В итоге я использовал вариант 5. Надеюсь, это поможет.

Ответ 3

Если вы проверяете значения многих ячеек, вы можете получить все значения ячеек в диапазоне, хранящемся в одном из вариантов, одним махом:

Dim CellVals() as Variant
CellVals = Range("A1:B1000").Value

Здесь есть компромисс с точки зрения размера диапазона, для которого вы получаете значения. Я бы предположил, что если вам понадобится тысяча или более значений ячеек, это, вероятно, быстрее, чем просто цикл через разные ячейки и опрос значений.

Ответ 4

Используйте, если это возможно, встроенные функции excels, например: Вместо поиска целого столбца для данной строки используйте команду find, доступную в графическом интерфейсе, с помощью Ctrl-F:

Set Found = Cells.Find(What:=SearchString, LookIn:=xlValues, _
    SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)

If Not Found Is Nothing Then
    Found.Activate
    (...)
EndIf

Если вы хотите отсортировать некоторые списки, используйте команду excel sort, не делайте это вручную в VBA:

Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal

Ответ 5

Производительность также сильно зависит от того, как вы автоматизируете Excel. VBA быстрее, чем автоматизация COM быстрее, чем автоматизация .NET. И обычно раннее (время компиляции) выполняется быстрее, чем поздняя привязка.

Если у вас серьезные проблемы с производительностью, вы можете подумать о перемещении критических частей кода в модуль VBA и вызывать этот код из кода автоматизации COM/.NET.

Если вы используете .NET, вы также должны использовать оптимизированные первичные сборки interop, доступные от Microsoft, а не использовать сборки межсоединений по индивидуальному заказу.

Ответ 6

Как анонимный тип говорит: чтение/запись блоков большого диапазона очень важно для производительности.

В случаях, когда служебные данные COM-Interop все еще слишком велики, вы можете переключиться на использование интерфейса XLL, который является самым быстрым интерфейсом Excel.

Хотя интерфейс XLL предназначен в первую очередь для пользователей С++, как XL DNA, так и Addin Express предоставляют возможности .NET для XLL-моста, что значительно быстрее, чем COM-Interop.

Ответ 7

Еще одна важная вещь, которую вы можете сделать в VBA: использовать Option Explicit и избегать вариантов, где это возможно. В VBA варианты не устраняются на 100%, но они заставляют интерпретатора больше работать во время работы и в памяти.

Я нашел эту статью очень полезной, когда я начинал с VBA в Excel.
http://www.ozgrid.com/VBA/SpeedingUpVBACode.htm

И эта книга

http://www.amazon.com/VB-VBA-Nutshell-Language-OReilly/dp/1565923588

Аналогично

 app.ScreenUpdates = false //and
 app.Calculation = xlCalculationManual

вы также можете установить

 app.EnableEvents = false //Prevent Excel events
 app.Interactive = false  //Prevent user clicks and keystrokes

хотя они не кажутся столь же большими, как первые два.

Аналогично установке значений диапазона в массивы, если вы работаете с данными, которые в основном являются таблицами с одинаковой формулой в каждой строке столбца, вы можете использовать формулу формулы R1C1 для своей формулы и установить полный столбец, равный формуле string, чтобы установить все это за один вызов.

app.ReferenceStyle = xlR1C1
app.ActiveSheet.Columns(2) = "=SUBSTITUTE(C[-1],"foo","bar")"

Кроме того, создание надстроек XLL с использованием ExcelDNA и .NET(или жесткий путь на C) также является единственным способом, с помощью которого UDF могут запускаться на нескольких потоках. (См. Свойство атрибута Excel DNA ExcelFunction для свойства IsThreadSafe.)

Прежде чем я полностью перешел на ДНК Excel, я также экспериментировал с созданием COM видимых библиотек в .NET для ссылки в проектах VBA. Тяжелая обработка текста немного быстрее, чем VBA, так как используются обернутые классы .NET List вместо VBA Collection, но Excel лучше.