Excel Interop - Efficiency and Performance

Question

I was wondering what I can do to improve Excel automation performance, as it can be pretty slow if you have a lot going on in a worksheet ...

Here are a few I found myself:

• ExcelApp.ScreenUpdating = false - disable screen redrawing

• ExcelApp.Calculation = Excel.XlCalculation.xlCalculationManual - ExcelApp.Calculation = Excel.XlCalculation.xlCalculationManual calculation mechanism, so Excel will not be automatically recounted when the cell value changes (return it after completion)

• Reduce the number of calls to Worksheet.Cells.Item(row, col) and Worksheet.Range - I had to poll hundreds of cells to find the cell I needed. By implementing some cell caching, the runtime was reduced from ~ 40 to ~ 5 seconds.

What challenges between calls have a big impact on performance and should be avoided? What else can you do to avoid unnecessary processing?

Answer 1

When using C # or VB.Net to get or set a range, find out what the total size of the range is, and then get one large array of 2-dimensional objects ...

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

Please note that it is important to know what type of Excel data is stored (text or numbers), because it will not automatically do this for you when you convert the type back from an array of objects. Add tests, if necessary, to verify the data, if you cannot be sure of the data type.

Answer 2

This is for those who wonder what the best way is to fill out an excel sheet from the db result set. This does not mean that it is a complete list, but it lists several options.

Some performance indicators when trying to fill out Excel sheets with 155 columns and 4200 records on an old Pentium 4 3GHz flight, including data search time that did not exceed 10 seconds, in order of lowest speed, looks like this:

• One cell at a time - just under 11 minutes

• Filling the data set by converting to html + Saving html to disk + Downloading html to excel and saving the sheet as xls / xlsx - 5 minutes

• One column at a time - 4 minutes

• Using the deprecated sp_makewebtask procedure in SQL 2005 to create an HTML file - 9 seconds + after which the html file is loaded into excel and saved as XLS / XLSX - about 2 minutes.

• Convert the .Net dataset to ADO RecordSet and use the WorkSheet.Range [] function. CopyFromRecordset to fill excel - 45 seconds!

In the end, I used option 5. Hope this helps.

Answer 3

If you check the values ​​of many cells, you can get all the values ​​of the cells in the range stored in one of the options, in one fell swoop:

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

There is a trade-off in terms of the size of the range for which you get the values. I would suggest that if you need a thousand or more cell values, this is probably faster than just looping through different cells and polling the values.

Answer 4

If possible, use the built-in excels functions, for example: Instead of searching for an entire column for a given row, use the find , available in the graphical interface, using 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 

If you want to sort some lists, use the excel sort command, do not do it manually in VBA:

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

Answer 5

Performance also depends heavily on how you automate Excel. VBA is faster than COM automation faster than .NET automation. And usually early (compile time) is faster than late binding.

If you have serious performance issues, you might consider moving critical parts of the code to a VBA module and invoking this code from COM / .NET automation code.

If you use .NET, you should also use optimized primary interop assemblies available from Microsoft, and not use custom interconnect assemblies.

Answer 6

As the anonymous type says: reading / writing large-range blocks is very important for performance.

In cases where the COM-Interop overhead is still too large, you can switch to using the XLL interface, which is the fastest Excel interface.

Although the XLL interface is primarily intended for C ++ users, both XL DNA and Addin Express provide .NET features for the XLL bridge, which is significantly faster than COM-Interop.

Answer 7

Another important thing you can do in VBA is to use Option Explicit and avoid options where possible. In VBA, options are not eliminated 100%, but they make the interpreter work more during runtime and in memory.

I found this article very useful when I started with VBA in Excel.
http://www.ozgrid.com/VBA/SpeedingUpVBACode.htm

And this book

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

Similarly

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

you can also install

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

although they do not seem as large as the first two.

Similar to setting range values ​​to arrays, if you work with data, which is basically tables with the same formula in each row of a column, you can use the formula formula R1C1 for your formula and set the full column equal to the string formula to set it all in one call.

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

In addition, creating XLL add-ins using ExcelDNA and .NET (or the hard path in C) is also the only way that UDFs can run on multiple threads. (See Excel DNA Attribute Property ExcelFunction for IsThreadSafe Property.)

Before I completely switched to Excel DNA, I also experimented with creating COM visible libraries in .NET for reference in VBA projects. Heavy text processing is slightly faster than VBA, since wrapped .NET List classes are used instead of VBA Collection, but Excel is better.