C++ 예제 (Excel Automation)

엑셀 오토메이션 (자동화)

개인적으로 사용해 본  소프트웨어 중 가장 잘 만들었다고 생각되는것을 하나만 꼽으라면 단연 엑셀 입니다.

문서의 작성이나, 자료 처리, 통계, 수식 계산 등 다양한 분야에 활용되고 있으며, 오피스 제품군 중 독보적 존재감이라 생각합니다.

이번 시간에는 C++ 과 MFC를 이용해 엑셀파일을 제어하는 방법에 대해 살펴보겠습니다.

파이썬에서는 openpyxl 등 모듈을 통해 쉽게 핸들링 가능하지만, C++은 코드로 구현하기가 보다 원초적이며, 어렵습니다.

약 15년 쯤 전에 C++, MFC를 이용해 탈질설비의 주요 요소(Factors, 약 30가지)를 입력받아 엑셀로 설계 출력값 (장비 치수, 허용치, 소요 자재량 등)을 일괄 계산해 주는 소프트웨어를 만든 기억이 납니다.

바로 살펴보도록 하겠습니다.

아래 코드는 Windows 10, Visual Studio 2017, Office 2016 환경에서 제작되었습니다.

프로젝트 생성

1. VS 실행, 프로젝트 생성, 대화상자 프로젝트 생성

2. 고급기능 탭, 자동화 체크, 마침

3. 프로젝트 생성 후 솔루션 탐색기, 프로젝트 우클릭, 추가, 새항목

4. MFC 탭, TypeLib MFC 클래스 선택

5. TypeLib 추가, 파일 선택, 위치 열기(아이콘)

예전에 작업할때는 레지스트리에 등록된 'Microsoft Excel xx.0 Object Library' 가 보였는데, 오피스 버전(2016) 때문인지 VS 2017 때문인지 이젠 보이지 않습니다.

파일로 설정하고 오피스가 설치된 경로로 가서 직접 Excel.exe를 선택해 필요한 클래스 인터페이스를 추가해 보겠습니다.

6. Excel 설치 경로로 이동, Excel.exe 선택

7. 인터페이스 선택 창에서 필요한 것 선택

예제에 사용된 인터페이스 목록

• _Application
• _Workbook
• _Worksheet
• Border, Borders
• Font
• Range
• Style, Styles
• Workbooks, Worksheets

8. 해당 인터페이스 클래스 생성 및 헤더(*.h) 추가 확인

9. 빌드 후 다수 에러 발생

10. 추가한 모든 excel 관련 .h 파일의 #import 구문 주석처리

각 엑셀관련 추가 헤더파일을 모두 열어 3번라인에 주석 처리합니다. 이미 엑셀 TypeLib를 추가하며 import 되어있습니다.

11. 빌드 후 CRange.h 에서 에러 발생

12. 해당 함수 앞 _붙임 or 해당 함수 전체 주석

음. 에러 원인은 잘 모르겠습니다.

이제 Excel TypeLib 코드 추가 완료되었으며, 빌드 오류가 없어야 합니다.

이제 C++, MFC 기반 Excel 개발 환경이 준비된 상태 입니다.

소스코드 작성

1. 대화상자 프로젝트생성, 리소스를 아래와 같이 추가.(버튼 4개)

차후 해당 버튼 이벤트를 감지해 엑셀관련 코드를 실행할 용도입니다.

2. Dlg.h 파일에 아래 멤버 함수 추가

class CexcelDlg : public CDialogEx
{
    생략....

protected:
    void OnClickedButton(unsigned int id);
    void ExcelTest1();
    void ExcelTest2();
    void ExcelTest3();
    void ExcelTest4();
};

OnClickedButton() 함수는 앞서 만든 버튼과 연결할 이벤트 처리 함수입니다.

ExcelTest1~4() 함수는 버튼이 눌러지면 호출되는 함수이며, 자세한 쓰임은 뒤에 설명드리겠습니다.

3. Dlg.cpp 파일에 Excel header 파일 추가

// excel headers
#include "CApplication.h"
#include "CCellFormat.h"
#include "CRange.h"
#include "CStyle.h"
#include "CStyles.h"
#include "CWorkbook.h"
#include "CWorkbooks.h"
#include "CWorksheet.h"
#include "CWorksheets.h"
#include "CFont0.h"
#include "CBorder.h"
#include "CBorders.h"

 
4. Dlg.cpp 파일 BEGIN_MESSAGE_MAP() 처리

BEGIN_MESSAGE_MAP(CexcelDlg, CDialogEx)
 ON_WM_CLOSE()
 ON_WM_PAINT()
 ON_WM_QUERYDRAGICON()
 ON_CONTROL_RANGE(BN_CLICKED, IDC_BUTTON1, IDC_BUTTON4, &CexcelDlg::OnClickedButton)
END_MESSAGE_MAP()

5번 라인의 ON_CONTROL_RANGE() 에 처리할 통보 메시지 코드와 시작컨트롤 아이디, 끝 컨트롤 아이디, 호출될 함수를 입력합니다.

4개의 버튼 마다 이벤트처리함수를 따라 추가하면 번거로우므로, 한꺼번에 처리하고 OnClickedButton(unsigned int id) 함수의 전달인자인 id 로 버튼 컨트롤 아이디를 받아 처리합니다.

5. Dlg.cpp 파일에 OnClickedButton() 함수 정의

void CexcelDlg::OnClickedButton(unsigned int id)
{
    switch (id)
    {
    case IDC_BUTTON1:
        ExcelTest1();
        break;
    case IDC_BUTTON2:
        ExcelTest2();
        break;
    case IDC_BUTTON3:
        ExcelTest3();
        break;
    case IDC_BUTTON4:
        ExcelTest4();
        break;
    }
}

이제 버튼을 클릭하면 이 함수가 호출되며, 각 버튼의 ID에 맞게 ExcelTest1~4() 함수가 호출됩니다.

6. ExcelTest1() 함수 (엑셀 파일 열기, 값 쓰기)

void CexcelDlg::ExcelTest1()
{
    CApplication app;    

    // 엑셀 시작
    if (!app.CreateDispatch(_T("Excel.Application")))
    {
        AfxMessageBox(_T("Couldn't start Excel."));
    }
    else
    {
        //열리는 과정이 보이게
        app.put_Visible(true);        
    }

    // workbook 생성
    app.put_SheetsInNewWorkbook(1);        

    CWorkbooks wbs;
    CWorkbook wb;    

    COleVariant covTrue((short)TRUE);
    COleVariant    covFalse((short)FALSE);
    COleVariant covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
    
    // books 연결
    wbs.AttachDispatch(app.get_Workbooks());
    // book 연결
    wb.AttachDispatch(wbs.Add(covOptional));    

    // sheets 생성, 연결
    CWorksheets wss;
    wss = wb.get_Sheets();

    // sheet 생성, 연결 (1번 시트)
    CWorksheet ws;
    ws = wss.get_Item(COleVariant((short)1));

    // sheet 이름 변경
    ws.put_Name(_T("test sheet 1"));

    // range 생성, 연결
    CRange range;
    range.AttachDispatch(ws.get_Cells(), true);    

    // range 값 쓰기
    for (int i = 1; i <= 10; i++)
    {
        CString strRow;
        strRow.Format(_T("ROW%d"), i);
        range.put_Item(COleVariant((long)1), COleVariant(long(i)), COleVariant(strRow));
    }

    // 연결 끊기
    range.ReleaseDispatch();
    ws.ReleaseDispatch();
    wss.ReleaseDispatch();
    wb.ReleaseDispatch();
    wbs.ReleaseDispatch();
    app.ReleaseDispatch();
}

코드가 복잡하므로 먼저 분석하기 보다는, 일단 맨 아래 첨부된 소스코드를 다운받아 실행 후 엑셀 파일이 자동으로 생성되어 값이 써지는 모습을 관찰 한 후 코드를 분석해보기 바랍니다.

코드의 동작방식은 Excel App을 생성, 워크북, 워크시트 생성, 워크시트 셀 연결, 값쓰기로 이어지는 흐름입니다.

클래스를 유심히 살펴보면 CWorksheets, CWorksheet 등 복수, 단수형 이름 클래스가 존재하는데 이는 엑셀파일에서 여러개의 워크시트들이 존재하고 하나의 활성 워크시트에서 작업이 이루어지기 때문입니다.

51번 라인의 CRange 객체는 put_Item(행번호, 열번호, 값) 함수로 워크시트에 값을 써주는 역할입니다. 주의해야할 점은 행번호, 열번호는 1번부터 시작합니다.

또한 엑셀 관련 클래스가 대부분 VARIANT 구조체로 값을 주고 받게 되어있으며, 모든 타입(정수, 실수, 문자열 등)에 대응해야 하므로 구조체가 복잡합니다.

따라서 코드에서는 VARIANT 구조체의 Wrapper class인 MFC COleVariant class를 활용해 구현하였습니다.

해당 함수의 수행 결과는 아래와 같이 엑셀이 자동 실행되어 값이 써집니다.

7. ExcelTest2() 함수 (셀 병합, 폰트변경, 정렬)

ExcelTest1() 에서 이미 엑셀 파일 열기, 연결은 살펴보았으므로 생략합니다.

void CexcelDlg::ExcelTest2()
{
    생략...

    // range 생성
    CRange range;    

    // range 값 쓰기 (merge)
    range = ws.get_Range(COleVariant(_T("A1")), COleVariant(_T("E2")));
    range.Merge(COleVariant(0L));
    range.put_Value2(COleVariant(_T("Merge Cell")));
    
    // Font
    CFont0 font = range.get_Font();
    font.put_Bold(covTrue);
    font.put_Size(COleVariant(16L));
    font.put_Color(COleVariant((double)RGB(0, 0, 255)));
    
    // Alignment
    // Top:        -4160
    // Bottom:    -4107
    // Left:    -4131
    // Center:    -4108
    // Right:    -4152
    range.put_HorizontalAlignment(COleVariant(short(-4108)));

    // 연결 끊기
    font.ReleaseDispatch();
    range.ReleaseDispatch();
    ws.ReleaseDispatch();
    wss.ReleaseDispatch();
    wb.ReleaseDispatch();
    wbs.ReleaseDispatch();
    app.ReleaseDispatch();
}

CFont 클래스는 특이하게 이름이 뒤에 '0' 이 붙는데 아마도, MFC의 CFont class와 이름충돌을 피하기 위함인 것 같습니다.

여기서 사용되는 CFont0 class는 엑셀의 폰트 class입니다.

25번 라인의 put_HorizontalAlignment() 함수를 통해 셀 범위의 정렬을 수행하는데, 인자값은 주석으로 명시해 두었습니다.

아마도 이런 정수계열 상수값이 정의된 헤더파일이나 클래스가 존재할거 같은데 문서화가 부실해 찾기가 힘드네요.

결과는 아래와 같습니다.

8. ExcelTest3() 함수 (테두리, 선 그리기)

void CexcelDlg::ExcelTest3()
{
    생략...

    // range 생성
    CRange range;
    range.AttachDispatch(ws.get_Cells(), true);

    // 범위 지정
    range = ws.get_Range(COleVariant(_T("B2")), COleVariant(_T("D3")));

    // borders (전체 테두리)
    CBorders borders;    
    borders = range.get_Borders();
    borders.put_LineStyle(COleVariant((short)1));


    // 범위 지정
    range = ws.get_Range(COleVariant(_T("B5")), COleVariant(_T("D6")));

    // border (부분 테두리, 상하좌우)
    CBorder bl, bt, bd, br;
    borders = range.get_Borders();

    // BORDERLEFT:    7
    // BORDERTOP:    8
    // BORDERBOTTOM:9
    // BORDERRIGHT:    10
    // BORDERINNERL:11
    bl = borders.get_Item(7L);
    bt = borders.get_Item(8L);
    bd = borders.get_Item(9L);
    br = borders.get_Item(10L);

    // line style with thickness
    // Continuous     1
    // xlDash - 4115
    // xlDashDot     4
    // xlDashDotDot    5
    // xlDot - 4118
    // xlDouble - 4119
    // xlLineStyleNone - 4142
    // xlSlantDashDot     13
        
    signed short thick = 3;
    bl.put_LineStyle(COleVariant((short)1));
    bl.put_Weight(COleVariant(thick));

    bt.put_LineStyle(COleVariant((short)2));
    bt.put_Weight(COleVariant(thick));

    bd.put_LineStyle(COleVariant((short)4));
    bd.put_Weight(COleVariant(thick));

    br.put_LineStyle(COleVariant((short)-4119));
    br.put_Weight(COleVariant((short)4));    

    // 연결 끊기    
    borders.ReleaseDispatch();
    range.ReleaseDispatch();
    ws.ReleaseDispatch();
    wss.ReleaseDispatch();
    wb.ReleaseDispatch();
    wbs.ReleaseDispatch();
    app.ReleaseDispatch();
}

CBorder 클래스를 이용해 해당 범위 셀의 상, 하, 좌, 우 테두리를 각자 다른 선으로 그려주는 예제입니다.

CBorder class 메소드인 put_LineStyle() 함수로 선의 종류를, put_Weight() 함수로 선의 두께를 결정합니다.

55번 라인 put_LineStyle() 함수의 4번 선종류가 이중선인데 특이하게 선 두께가 4 만 동작하네요.

결과는 아래와 같습니다.

9. ExcelTest4() 함수 (엑셀 수식, Formula 적용하기)

void CexcelDlg::ExcelTest4()
{
    생략...

    // range 생성, 연결
    CRange range;
    range.AttachDispatch(ws.get_Cells(), true);

    // range 값 쓰기
    for (long i = 1; i <= 10; i++)
    {        
        range.put_Item(COleVariant((long)i), COleVariant(long(1)), COleVariant(i));
    }

    // 함수 적용
    range = ws.get_Range(COleVariant(_T("A1")), COleVariant(_T("A10")));

    CRange sum = ws.get_Range(COleVariant(_T("A11")), COleVariant(_T("A11")));
    sum.put_Formula(COleVariant(_T("=SUM(A1:A10)")));
    CBorders bd = sum.get_Borders();
    bd.put_LineStyle(COleVariant(short(1)));

    // 연결 끊기
    range.ReleaseDispatch();
    ws.ReleaseDispatch();
    wss.ReleaseDispatch();
    wb.ReleaseDispatch();
    wbs.ReleaseDispatch();
    app.ReleaseDispatch();
}

A1 ~ A10번 셀에 숫자를 1부터 10까지 기록하고 A11번 셀에 put_Formula() 함수를 이용해 엑셀의 "=SUM(A1:A10)" 함계 수식을 적용해 보았습니다.

CRange class의 메소드인 put_Formula() 함수 전달인자로 수식 문자열을 적용합니다.

결과는 아래와 같습니다.

이상으로 모든 코드 분석을 마칩니다.

감사합니다.

소스코드 링크 : 엑셀 예제