[ActiveX] Excel.Application 속도 개선

제가 입사하고 처음 마주한 개발 환경은 금융권 시스템 차세대 시절 SI 업체가 닦아놓은

(그리고 그것도 개발도구 제공업체의 기본 예제가 80%인) 생태계였습니다.
프레임워크 자체도 익숙하지 않았고, 레거시를 기반으로 한 응용프로그래밍 제작은 IT 현업에 대해 환상을 품고 있었던 대학생에게는 상당히 가혹한 일이었습니다. 😫😫

그래도 저는 이 환경 속에서 충분히 역량을 발휘하고픈 마음이 들었기에..

젊은 세대가 접하는 시장 교란적인 UX를 가진 서비스들(토X, 카XX 등)의 발끝이라도 따라잡고자 했고,
2년의 시간 동안 UX를 저해하는 요소들을 일부 색출해낼 수 있었습니다.

Excel.Application

그 중 하나로 ActiveX를 활용한 엑셀 작성기(Excel.Application)가 있었습니다.
당연히 해당 ActiveX 객체에 대한 Docs가 업체로부터 제공이 되었는데, 너무 제한된 함수로만 예제가 구성되어 있어서 아쉬운 점이 일부 있었습니다. (근데 사실 그것만으로 운영이 잘 되어 왔습니다. 더 바라는 제가 욕심쟁이 일 수도 있네요,,)
그래서 간간히 그것에 대해 아는 것이 있으면 알려달라고 제공사 홈페이지에 QnA가 올라 갔었던데,

MS에서 제공하는 거라 저희는 잘 모른다는 답변이 대부분이었습니다. 음,,
결국, 직접 extra API를 찾거나,
Super UX는 좀 더 IT 지향적인 회사들의 전유물로.. 

금융 레거시는.. 영업 쪽에서 경쟁력을 찾는 걸로.. 갈무리하는 방법이 있었습니다.

(그래.. 이대로도 괜찮잖아.. 열심히 했잖아.. 😔👍🏻)

 

그래도 메인 업무는 해야하니 당분간은 "보류" 해두는 것으로 결정 짓고 따로 윗선에 보고는 하지 않았습니다.

차근차근

어느날 Excel 작성에 10분이 넘어가는 걸 보면서, 문득 그런 생각이 들더라구요.
MS가 미쳤다고 이렇게 오래 걸리는 접근법을 대안의 솔루션 없이 냅두었을까..?

정말 그렇더라도 해외의 다른 개발자들은 아무 클레임없이.. 이대로 해왔다고?

 

정말 그럴 리는 없죠. 다시 문제의 한 번 원인부터 차근차근 밟아가기로 했습니다.

 

사실 엑셀 작성기가 불편을 주는 이유는 저희 회사 프로젝트가 single-thread로 동작하게 구현되어 있다는 점에 있습니다.

(당연히 개발 도구 자체가 multi-threading을 제공하지 않아 그 쪽으로의 개선 여지는 없습니다.)

로우레벨 단을 건드릴 수 없는 상태인 것이죠;;

그래서 프로젝트 위에서 엑셀 작성기가 동작(context switch)할 때는 프로젝트가 먹통(Starving)이 됩니다..

문제는 이 context switch 자체가 상당한 시간적 overhead를 가지고 있다는 점입니다..

사용자들이 엑셀 작성이 끝나는 수 분동안 저희 프로그램을 사용하지 못하게 되는 것이죠..

 

그래서 아래처럼 개선점이 어디에 어떻게 적용되어야 할지 정리해보았습니다.

- multi-threading 실패❌(애초에 불가능함)
- overhead 줄이기 어려움💦(Excel.Application 말고 다른 가용 가능한 도구를,, 아직 못 찾았기에..)
- overhead 횟수 줄이기 가능할 수도..?💡(오래 걸려도 overhead의 절대적인 수가 줄어든다면..?)

분석 및 정리 결과,

저 3번의 접근법에 해답이 있을 것을 믿고 속도 향상을 위한 여러 실험이 시작되었습니다.

유레카

목표 - 모든 엑셀 관련 API 호출을 최대한 줄여보자

N*M 영역 안에 작성이 필요한 셀이 있다면 API호출은 N*M이하로 일어날 것이고,

각 Overhead가 0.1초라 가정하면(실제로는 더 걸림)
N과 M이 커질 수록 시간이 엄청 뛰게 됩니다. 그리고 이것이야 말로 개선이 필요한 근본적인 지점이었죠.
단순히 N=10, M,8이라고만 해도 8초나 걸리게 됩니다..

[현상황]

var objExcelAppSheet = "Excel.Apllication 기반 객체 생성"; // 작성 대상 엑셀 시트 객체

var rows = 10;
var cols = 8;

// Microsoft.Office.Interop.Excel의 Range 객체를 가져온다
var objExcelAppRange = objExcelAppSheet.Cells.get_Range("A1","A1");

// Range 안의 각 셀에 하나하나 넣는다 index는 1부터 시작
for (var i = 1; i <= rows; i++) {
  for (var j = 1; j <= cols; j++) {
    objExcelAppRange.get_Item(i, j).Value = "어떤 값";
  }
}

 

이 구조를 정리하자마자 학부 4학년에 GPU 관련된 수업을 들었던 것이 생각나더군요..

'이런 단순 연산이라면 여러 개로 묶어서 한 번에 처리할 수 있겠다.'

그래서 배열로 접근하자는 생각이 들었습니다. 

내부 스크립트에서 단순히 삽입 지점만 찾아 일일이 API를 호출하는 것이 아닌,
전체 영역에 대한 2차원 배열을 미리 짜두고, 그것을 단 한번의 API 호출로 값을 입히는 것이었습니다.

[개선안]

var objExcelAppSheet = "Excel.Apllication 기반 객체 생성"; // 작성 대상 엑셀 시트 객체

var rows = 10;
var cols = 8;
var array = new Array(rows);; // 2D Array로 쓰일 배열 선언

// 2차원 배열 초기화
for (var i = 0; i < rows; i++) {
  array[i] = new Array(cols);
  for (var j = 0; j < cols; j++) {
    array[i][j] = "어떤 값";
  }
}

var startRange = "A1"; // 예시입니다.
var endRange = "H2"; // 예시입니다.

// Microsoft.Office.Interop.Excel의 Range 객체를 가져온다
var objExcelAppRange = objExcelAppSheet.Cells.get_Range(startRange,endRange);

// 아래는 부정확한 함수 활용이지만 이렇게 하면 될 것이라 생각했었음
objExcelAppRange.Value = array;

그렇게 스크립트에서 동적으로 배열을 짜고 데이터를 입력하는 API(위의 Value)를 호출했는데,,

 

아쉽게도 정상적으로 동작하지 않았습니다.. 🤯🤯 (한 번에 되는 게 이상하긴 해요 ㅋ)

 

뭔가 2차원 배열 자체를 해체하지 못하는 것 같아서 이런 저런 방법들(String, Object, Dict 형태로 전달한다든가)을

그런 거 하면서 오만 테스트를 거쳐봤는데.. 하나도 맞춰주지 않더군요..
그런 방법들은 MS API가 원하는 규격이 아니었습니다. (정확히는 Range 객체의 Value Property)

(근데 이상하게 StackOverFlow는 ~~하면 됩니다~ 하는 사람도 있더라구요? 아니 안되잖아;;)

 

Value라는 속성 호출은 2차원 배열 자체를 인식 못하는 것으로 보였고,

그렇다고 1차원으로 풀어서 주면(N*M개의 길이로) 딱 M(column)의 길이만 인식했습니다..

(M까지의 길이로 N개를 할당함, 즉 원래 의도한 array[0][0:M]으로 N개의 행이 채워짐)

 

이런 저런 질문글, 문서, GPT 등의 서치를 해보았으나 이틀 간은 수확이 없었습니다.
맞아요 이게 개발이지..

GPT가 답을 못주면 방법을 찾는데에만 서너일이 걸리는 게 맞지;;

근데 GPT MS 데이터 기반 아니에요? 네?????
그러다가 MS 공식 문서를 봐봐~ 하는 StackOverFlow 질문글을 찾아서 Official Docs를 찾게 되었습니다..
근데 좀 C# 베이스가 없으니 불편하긴 하더라구요..?

어찌저찌 기존에 쓰던 내용을 찾아 예습을 적당히 해보고,

거기서 확장해 나가는 중에 이윽고,, 2차원 배열을 인자로 받는 듯한 속성을 찾았습니다.

FormulaArray

너무 어이가 없을 정도로 단순하게 적용이 가능해서 문제가 될 수준이었습니다..
Value -> FormulaArray이기만 하면 됨;;
구글링을 해도 관련 질문이 없는 게 이쯤되면 이해가 되기도 하네요..
다들 자체 인수인계 문서에 잘 적어 놓았을 듯,,
결과적으로, 2차원 배열은 적용이 잘되었습니다. 그리고 매우 빠릅니다..
그토록 원하는 기능을 찾았는데.. 막 후련하지는 않더군요..

너무 쉽고 단순한 기능을 고생해서 찾아서 제가 이 정도밖에 안되나 싶고 😭😭

[최종안]

var objExcelAppSheet = "Excel.Apllication 기반 객체 생성"; // 작성 대상 엑셀 시트 객체

var rows = 10;
var cols = 8;
var array = new Array(rows);; // 2D Array로 쓰일 배열 선언

// 2차원 배열 초기화
for (var i = 0; i < rows; i++) {
  array[i] = new Array(cols);
  for (var j = 0; j < cols; j++) {
    array[i][j] = "어떤 값";
  }
}

var startRange = "A1"; // 예시입니다.
var endRange = "H2"; // 예시입니다.

// Microsoft.Office.Interop.Excel의 Range 객체를 가져온다
var objExcelAppRange = objExcelAppSheet.Cells.get_Range(startRange,endRange);

// 2차원 배열을 통해 단 한 번의 속성 호출로 할당한다.
objExcelAppRange.FormulaArray = array;

Docs 링크

https://learn.microsoft.com/en-us/previous-versions/office/developer/office-2010/ff837104(v=office.14)

Range.FormulaArray Property (Excel)

https://learn.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel.range.formulaarray?view=excel-pia

Range.FormulaArray Property (Microsoft.Office.Interop.Excel)

번외

Docs를 보시다 보면 아시겠지만, FormulaArray라는 속성의 호출을 통해 단순 값이 아닌 수식을 집어 넣을 수 있습니다.

=SUM(), =IF(), =C1+C2 같은 Excel Base 수식 적용을 할당할 수 있는데,

여기까지는 쉽죠..?

근데 Docs 잘 안보고 해버리면 수식이 이상하게 적용된다는 것을 확인하실 수 있습니다.

[처음에 시도한 방법]

var objExcelAppSheet = "Excel.Apllication 기반 객체 생성"; // 작성 대상 엑셀 시트 객체

// Microsoft.Office.Interop.Excel의 Range 객체를 가져온다
var objExcelAppRange = objExcelAppSheet.Cells.get_Range("A10","A10");

// 준비된 수식을 넣는다.
objExcelAppRange.FormulaArray = "=SUM(A1:A9)";

 

저는 당연히 이렇게 해야 적용이 될 줄 알았는데, 그건 아니더라구요 😅

Docs에 보시면 R#C# 방식으로 행과 열을 숫자로 지정해 주라고 되어 있습니다.

즉,

R12는 엑셀 상 12번째 줄

C4은 엑셀 상 D열이 되는 것이죠 🔮🔮

뭔가 개발 상의 편의를 따지면 그쪽이 더 좋은 것 같기도 하고.. 흠

[수정본]

var objExcelAppSheet = "Excel.Apllication 기반 객체 생성"; // 작성 대상 엑셀 시트 객체

// Microsoft.Office.Interop.Excel의 Range 객체를 가져온다
var objExcelAppRange = objExcelAppSheet.Cells.get_Range("A10","A10");

// 준비된 수식을 넣는다.
objExcelAppRange.FormulaArray = "=SUM(R1C1:R9C1)";

결론

Docs를 잘 읽읍시다..

모든 답은 거기에..