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

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

Вот несколько я нашел себя:

  • ExcelApp.ScreenUpdating = false - отключить перерисовку экрана

  • ExcelApp.Calculation = Excel.XlCalculation.xlCalculationManual - отключение механизма вычислений, чтобы Excel не пересчитывал автоматически при изменении значения ячейки (включите его снова после завершения)

  • Сократите количество звонков Worksheet.Cells.Item(row, col)и Worksheet.Range- мне пришлось опросить сотни ячеек, чтобы найти нужную мне ячейку. Реализация некоторого кэширования местоположений ячеек позволила сократить время выполнения с ~ 40 до ~ 5 секунд.

Какие вызовы взаимодействия сильно сказываются на производительности и их следует избегать? Что еще вы можете сделать, чтобы избежать ненужной обработки?

10.12.2008 15:03:46
+1 У меня также есть проблемы с производительностью с помощью Excel Interop, и я узнал что - то новое: ExcelApp.Calculation = Excel.XlCalculation.xlCalculationManual. Спасибо! =)
Will Marcouiller 4.03.2011 18:33:14
Спасибо, что поделились своими текущими находками в вопросе, очень полезно.
Trebblez 17.02.2016 15:04:26
некоторые другие возможные варианты здесь: stackoverflow.com/documentation/excel-vba/1107/…
Slai 12.02.2017 01:51:11
7 ОТВЕТОВ

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

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

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

2
10.12.2008 15:11:19
все верно. но если вы последуете совету не делать много маленьких вызовов объекта Range для установки свойства Value2 и просто передать массив Object или получить его, вам на самом деле не нужно будет использовать VBA.
Anonymous Type 19.02.2010 04:28:54

По возможности используйте превосходную встроенную функциональность, например: вместо поиска целого столбца для заданной строки используйте 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
10.12.2008 15:12:32
Это VBA, а не взаимодействие.
CompanyDroneFromSector7G 28.04.2015 12:10:36
@bukko идея такая же и очень хорошая. это так же, как при использовании базы данных SQL, вы должны использовать Query для выполнения вашей задачи (или SP) вместо выполнения нескольких запросов.
Tomer W 21.07.2016 20:55:46

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

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

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

6
15.12.2008 18:31:10
-1, без обид Джона, но как за это так сильно проголосовали? Речь идет об Excel Interop, а не VBA / VB6. почти уверен, что Variant даже не существует как тип из COM-взаимодействия (использует объект).
Anonymous Type 6.03.2011 21:12:47
Range.Value работает и для COM. и, следовательно, смысл, если его пример для VBA / .NET здесь неуместен и не меняет ответ. Я согласен, что было бы лучше, если бы Джон сменил образец.
Tomer W 21.07.2016 20:59:20
Не говоря уже о том, что этот ответ был здесь за год до принятого ответа, и этот ответ только немного лучше, поскольку он имеет обратную операцию.
Chris 24.07.2018 18:27:15

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

//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 хранит (текст или числа), поскольку он не будет делать это автоматически для вас, когда вы конвертируете тип обратно из массива объектов. При необходимости добавьте тесты для проверки данных, если заранее не уверены в типе данных.

48
19.02.2010 04:34:06
+1 За использование двухмерных массивов объектов. Также есть функция, shtName.UsedRange.get_Value(XlRangeValueDataType.XlRangeValueDefault)которая может быть приведена к типу двухмерного массива объектов и будет извлекать все значения ячеек одновременно.
Will Marcouiller 4.03.2011 18:36:58
@Will Marcouiller: Да, но проблема с использованием свойства UsedRange заключается в том, что оно отрицательно влияет на производительность. Я не уверен, что это даже намного быстрее, чем использование ячеек / методов смещения.
Anonymous Type 6.03.2011 21:11:05
Это здорово, но как добавить изображения в xls, используя эту оптимизированную технику? В моем приложении вставка изображений является узким местом. Каждый сгенерированный файл XLSX содержит не менее 300-400 изображений. Текущее решение вызывает worksheet.Shapes.AddPicture()метод столько раз, сколько количество картинок. Это действительно медленно.
mr_esp 13.10.2013 19:02:41
Если вам нужна дополнительная функциональность (например, форматирование), которая ужасно работает с COM-объектом, вы можете попробовать EPPlus из этого ответа - stackoverflow.com/questions/6583136/…
Jeff Fischer 3.02.2015 03:27:29
У @JeffFischer OP "много чего происходит в рабочей таблице". Рекомендация EPPlus не соответствует тому, о чем мы говорим.
Jeremy Thompson 3.02.2015 10:45:00

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

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

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

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

  3. Одна колонка за раз - 4 минуты

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

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

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

13
15.01.2015 16:02:26
хммм хороший гибридный подход в (5). Однако вы тестировали шестой подход, используя OLEDbConnection к книге и заполняя лист в виде таблицы? ограничения этого подхода включают необходимость заранее знать схему каждого столбца (чтобы предотвратить неправильное преобразование типов в Excel).
Anonymous Type 18.01.2012 22:54:40
@AnonymousType - я должен признать, что я не пытался заполнить лист в виде таблицы. Я все еще хочу, чтобы Microsoft предоставила функцию «CopyFromDataSet» для разработчиков .Net :-).
Ritesh 12.06.2012 14:01:37
это была бы очень приятная особенность VSTO.
Anonymous Type 13.06.2012 00:34:30

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

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

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

1
8.02.2015 12:40:29

Еще одна важная вещь, которую вы можете сделать в 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

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

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

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

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

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

0
16.07.2015 23:36:37