Excel Interop-효율성 및 성능

Excel Interop-효율성 및 성능

---

워크 시트에서 많은 작업을 수행하면 속도가 상당히 느려질 수 있으므로 Excel 자동화의 성능을 개선하기 위해 무엇을 할 수 있는지 궁금합니다.

내가 찾은 몇 가지는 다음과 같습니다.

• ExcelApp.ScreenUpdating = false -화면 다시 그리기 끄기

• ExcelApp.Calculation = Excel.XlCalculation.xlCalculationManual -계산 엔진을 꺼서 셀 값이 변경 될 때 Excel이 자동으로 다시 계산되지 않도록합니다 (완료 후 다시 켜십시오).

• 호출을 절감 Worksheet.Cells.Item(row, col)하고 Worksheet.Range- 내가 필요한 셀을 찾을 수 세포의 설문 조사 수백했다. 셀 위치의 일부 캐싱을 구현하여 실행 시간을 ~ 40 초에서 ~ 5 초로 단축했습니다.

어떤 종류의 interop 호출이 성능에 큰 영향을 미치며 피해야합니까? 불필요한 처리를 방지하기 위해 또 무엇을 할 수 있습니까?

---

C # 또는 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이 저장하는 데이터 형식 (텍스트 또는 숫자)을 아는 것이 중요합니다. 데이터 유형을 미리 알 수없는 경우 데이터 유효성을 검사하기 위해 필요한 경우 테스트를 추가합니다.

---

이것은 db 결과 세트에서 Excel 시트를 채우는 가장 좋은 방법이 무엇인지 궁금해하는 사람을위한 것입니다. 이것은 어떤 식 으로든 전체 목록을 의미하지는 않지만 몇 가지 옵션을 나열합니다.

가장 느린 것부터 가장 빠른 순서로 10 초를 넘지 않았던 데이터 검색 시간을 포함하여 오래된 펜티엄 4 3GHz 상자에 155 개의 열과 4200 개의 레코드로 엑셀 시트를 채우려 고 시도하는 동안 일부 성능 수치는 다음과 같습니다.

1. 한 번에 하나의 셀 -11 분 미만

2. html로 변환하여 데이터 세트 채우기 + html을 디스크에 저장 + html을 Excel로로드하고 워크 시트를 xls / xlsx로 저장 -5 분

3. 한 번에 한 열 -4 분

4. SQL 2005에서 더 이상 사용되지 않는 sp_makewebtask 프로 시저를 사용하여 HTML 파일 만들기-9 초 + Excel에서 html 파일을로드하고 XLS / XLSX로 저장- 약 2 분.

5. .Net 데이터 세트를 ADO RecordSet으로 변환하고 WorkSheet.Range []. CopyFromRecordset 함수를 사용하여 Excel을 채우십시오 -45 초!

옵션 5를 사용하게되었습니다. 도움이 되었기를 바랍니다.

---

가능하면 Excel 내장 기능을 사용하십시오. 예를 들면 다음과 같습니다. 주어진 문자열에 대해 전체 열을 검색하는 대신 findCtrl-F로 GUI에서 사용할 수 있는 명령을 사용하십시오 .

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

---

여러 셀의 값을 폴링하는 경우 한 번에 변형 배열에 저장된 범위의 모든 셀 값을 가져올 수 있습니다.

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

여기에는 값을 얻는 범위의 크기와 관련하여 절충안이 있습니다. 천 개 이상의 셀 값이 필요한 경우 다른 셀을 반복하고 값을 폴링하는 것보다 더 빠를 것입니다.

---

성능은 Excel 자동화 방법에 따라 크게 달라집니다. VBA는 COM 자동화가 .NET 자동화보다 빠릅니다. 그리고 일반적으로 초기 (컴파일 시간) 바인딩은 후기 바인딩보다 빠릅니다.

심각한 성능 문제가있는 경우 코드의 중요한 부분을 VBA 모듈로 이동하고 COM / .NET 자동화 코드에서 해당 코드를 호출 할 수 있습니다.

.NET을 사용하는 경우 Microsoft에서 제공하는 최적화 된 기본 interop 어셈블리도 사용해야하며 사용자 지정 빌드 interop 어셈블리를 사용하지 않아야합니다.

---

Anonymous Type이 말했듯이 : 넓은 범위의 블록을 읽고 쓰는 것은 성능에 매우 중요합니다.

COM-Interop 오버 헤드가 여전히 너무 큰 경우 가장 빠른 Excel 인터페이스 인 XLL 인터페이스를 사용하도록 전환 할 수 있습니다.

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

---

Another big thing you can do in VBA is to use Option Explicit and avoid Variants wherever possible. Variants are not 100% avoidable in VBA, but they make the interpreter do more work at runtime and waste memory.

I found this article very helpful when I was starting 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

Similar to

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

you can also set

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

although they don't seem to make as big a difference as the first two.

Similar to setting Range values to arrays, if you are working with data that is mostly tables with the same formula in every row of a column, you can use R1C1 formula notation for your formula and set an entire column equal to the formula string to set the whole thing in one call.

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

Also, creating XLL add-ins using ExcelDNA & .NET (or the hard way in C) is also the only way you can get UDFs to run on multiple threads. (See Excel DNA's ExcelFunction attribute's IsThreadSafe property.)

Before I transitioned to Excel DNA completely, I also experimented with creating COM visible libraries in .NET to reference in VBA projects. Heavy text processing is a bit faster than VBA that way, as are using wrapped .NET List classes instead of VBA's Collection, but Excel DNA is better.

참고URL : https://stackoverflow.com/questions/356371/excel-interop-efficiency-and-performance