Excel Interop - 효율성 및 성능

Excel Interop - 효율성 및 성능

엑셀 자동화의 성능을 향상시키기 위해 무엇을 할 수 있는지 궁금했습니다. 워크시트에서 많은 작업이 진행되면 속도가 느려질 수 있기 때문입니다.

여기 제가 찾은 몇 가지가 있습니다.

• ExcelApp.ScreenUpdating = false 다시

• ExcelApp.Calculation = Excel.XlCalculation.xlCalculationManual셀 값이 변경될 때 Excel이 자동으로 재계산되지 않도록 계산 엔진 끄기(완료 후 다시 켜짐)

• 를 통화축으로 줄입니다.Worksheet.Cells.Item(row, col)그리고.Worksheet.Range저는 제가 필요한 세포를 찾기 위해 수백 개의 세포를 폴링해야 했습니다.셀 위치의 일부 캐싱을 구현하여 실행 시간을 ~40초에서 ~5초로 단축했습니다.

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

C# 또는 VB를 사용하는 경우.범위를 가져오거나 설정하기 위한 네트, 범위의 총 크기를 파악한 다음 큰 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이 자동으로 이 작업을 수행하지 않으므로 Excel이 저장하고 있는 데이터 유형(텍스트 또는 숫자)을 아는 것이 중요합니다.데이터 유형을 미리 확인할 수 없는 경우 데이터의 유효성을 확인하기 위해 필요한 경우 테스트를 추가합니다.

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

이전 Pentium 4 3GHz 박스에 155개의 열과 4200개의 레코드로 Excel 시트를 채우려고 시도할 때 가장 느린 순서에서 가장 빠른 순서로 10초를 초과하지 않는 데이터 검색 시간을 포함하는 몇 가지 성능 수치는 다음과 같습니다.

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

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

3. 한 번에 열 하나 - 4분

4. SQL 2005에서 더 이상 사용되지 않는 sp_makeweb 태스크 절차를 사용하여 HTML 파일 생성 - 9초 + HTML 파일을 excel에 로드한 후 XLS/XLSX로 저장 - 약 2분

5. 변환합니다.데이터 세트를 ADO RecordSet에 순으로 설정하고 워크시트를 사용합니다.범위[].CopyFromRecordset 함수를 Excel에 입력 - 45초!

저는 결국 옵션 5를 사용하게 되었습니다.이게 도움이 되길 바랍니다.

많은 셀의 값을 폴링하는 경우 한 번에 변형 배열에 저장된 범위의 모든 셀 값을 얻을 수 있습니다.

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

여기서 여러분이 얻을 수 있는 범위의 크기에 대한 트레이드오프가 있습니다.천 개 이상의 셀 값이 필요하다면 다른 셀을 순환하여 값을 폴링하는 것보다 더 빠를 것입니다.

가능할 때마다 다음과 같은 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 부목을정려면하렬을사 Excel ▁the를 합니다.sort " 함", VBA 파일에서 다음과 같이 수행합니다.

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

익명 유형에서 알 수 있듯이, 넓은 범위의 블록을 읽고 쓰는 것은 성능에 매우 중요합니다.

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

XLL 인터페이스는 주로 C++ 사용자를 위한 것이지만 XL DNA와 Addin Express는 모두 제공합니다.COM-Interop보다 훨씬 빠른 NET-XLL 브리지 기능.

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

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

만약에.NET 또한 Microsoft에서 제공하는 최적화된 기본 상호 운용 어셈블리를 사용해야 하며 사용자 지정 상호 운용 어셈블리를 사용해서는 안 됩니다.

VBA에서 할 수 있는 또 다른 중요한 일은 옵션 명시적을 사용하고 가능한 한 변형을 방지하는 것입니다.변형은 VBA에서 100% 피할 수 없지만, 인터프리터가 런타임에 더 많은 작업을 수행하고 메모리를 낭비하도록 만듭니다.

제가 엑셀에서 VBA를 시작할 때 이 기사가 많은 도움이 되었습니다.
http://www.ozgrid.com/VBA/.htmhttp ://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 공식 표기법을 사용하고 전체 열을 공식 문자열과 동일하게 설정하여 한 번의 호출로 전체를 설정할 수 있습니다.

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

또한 Excel을 사용하여 XLL 추가 기능 만들기DNA & .NET(또는 C의 하드웨이)는 또한 UDF를 여러 스레드에서 실행할 수 있는 유일한 방법입니다.(Excel DNA의 ExcelFunction 속성의 IsThreadSafe 속성 참조).

Excel DNA로 완전히 전환하기 전에 에서 COM 가시 라이브러리를 만드는 실험도 했습니다.VBA 프로젝트에서 참조할 NET.랩핑을 사용하는 것처럼 무거운 텍스트 처리는 VBA보다 약간 빠릅니다.VBA의 Collection 대신 NET List 클래스가 있지만 Excel DNA가 더 좋습니다.

언급URL : https://stackoverflow.com/questions/356371/excel-interop-efficiency-and-performance