MFC를 이용하지 않고 Win32로 엑셀 (Excel) 자동화(OLE Automation)하기

출처 : http://alones.byus.net/moniwiki/wiki.php/c_cpp_windows_tip_excel?action=show

목차

1 Excel Automation without MFC

2 CAloExcelAuto.h

3 CAloExcelAuto.cpp

4 Example code

5 Binary

5.1 source code

5.2 chm file

6 References

 

[edit]

1 Excel Automation without MFC #

MFC에서 MS Office의 excel을 이용해서 자동 생성된 코드로 엑셀 (excel)을 자동화 (ole autoamtion) 하는 방법을 정리 하고 이 것을 class로 만들어 보았습니다.
즉, Win32로 엑셀을 자동화하는 클래스입니다.
실제 자료를 찾아 보면 excel의 몇 몇 기초적인 기능들을 사용하기 위한 예제 코드를 찾기가 쉽지 않기도 해서 정리해보았습니다.
우선 MFC를 이용하지 않고 Win32를 이용해서 엑셀을 자동화하면 다음과 같은 이점들이 있을 것입니다.

• 원하는 것을 정확히 수행할 수 있습니다.
• 코드가 MFC를 이용한 것 보다 작고 빠릅니다.
• 디버깅 하기 쉽습니다.
• 가장 큰 이유는 오피스 버전에 종속되지 않아도 되는 것입니다.
  • 실제로 이게 가장 큰 이유인 것 같습니다. Office 2003에서 MFC를 이용해서 자동화 코드를 만들었다면 하위 버전에서 동작하지 않아서 힘든 경우가 많았습니다.
    
    

※ VB 등으로 쉽게 엑셀을 핸들링 할 수도 있겠지만 -_-;;

[edit]

2 CAloExcelAuto.h #

/**
 * @file        AloExcelAuto.h
 * @brief   CAloExcelAuto class 
 * @author      alones
 * @date initial version: 2007.07.22 
 */

#include "ole2.h"               // OLE2 Definitions

/**
 * @class CAloExcelAuto
 * @brief excel automation class
 */
class CAloExcelAuto
{
private:
        bool            m_bCOMInited;   /**< check if com ini*/
        bool            m_bInit ;               /**< check if excel init */

        struct PROPERTIES
        {
                IDispatch *pXlApp;              /**< Excel.exe */
                IDispatch *pXlBooks;    /**< Ptr To MainFrame -> Can open new Workbooks */
                IDispatch *pXlBook;     /**< A Workbook. Has more Sheets (Z-Layers) */
                IDispatch *pXlSheet;    /**< What U see. Has X and Y*/
        } m_instance;

public:
        /**
         * @brief make excel app visible or not
         * @param[in]   bVisible(bool) if true, make excel app visibile
         * @return      int     if success 1, otherwise negative number
        */
        int SetVisible(bool bVisible);

        /**
         * @brief open excel file 
         * @return              int     if success 1, otherwise negative number
         */
        int Open(const char* file);

        /**
         * @brief set active sheet to read and to write
         */
        int SetActiveSheet(int nSheet);

        /**
         * @brief               get cell data on current active sheet
         * @param[in]   pPos (char*)    cell position e.g. B4
         * @param[out]  pData (char*)   cell data. string
         */
        bool GetData(char* pPos, char* pData);

        /**
         * @brief               set cell data on current active sheet
         * @param[in]   pPos (char*)    cell position e.g. B4
         * @param[in]   pData (char*)   cell data. string
         */
        bool SetData(char* pPos, char* pData);

        /**
         * @brief save workbook
         */
        void Save();

        /**
         * @brief close workbook
         */
        void Close();

protected:
        /**
         * @brief Open a new Excel Window and create a new worksheet
         */
        int Init();

        /**
         * @brief       check if excel init
         * @return      if excel init, return true, otherwise false
         */
        bool CheckExcelInit(){return m_bInit;};

        // constructor & destructor
public:
        /**
         * @brief constructor
         */
        CAloExcelAuto();

        /**
         * @brief destructor
         */
        virtual ~CAloExcelAuto();

};

//

[edit]

3 CAloExcelAuto.cpp #

/**
 * @file        AloExcelAuto.cpp
 * @brief   CAloExcelAuto class impl.
 * @author      alones
 * @date initial version: 2007.07.22
 */

#include "AloExcelAuto.h"
#include "stdio.h"
#include <comdef.h>

/**
 * @brief Automation helper function
 */
HRESULT AutoWrap(int autoType, VARIANT *pvResult, IDispatch *pDisp, LPOLESTR ptName, int cArgs...)
{
    // Begin variable-argument list...
    va_list marker;
    va_start(marker, cArgs);

    if(!pDisp)
        {
        MessageBox(NULL, "NULL IDispatch passed to AutoWrap()", "Error", 0x10010);
        _exit(0);
    }

    // Variables used...
    DISPPARAMS dp = { NULL, NULL, 0, 0 };
    DISPID dispidNamed = DISPID_PROPERTYPUT;
    DISPID dispID;
    HRESULT hr;
    char buf[200];
    char szName[200];


    // Convert down to ANSI
    WideCharToMultiByte(CP_ACP, 0, ptName, -1, szName, 256, NULL, NULL);

    // Get DISPID for name passed...
    hr = pDisp->GetIDsOfNames(IID_NULL, &ptName, 1, LOCALE_USER_DEFAULT, &dispID);
    if(FAILED(hr))
        {
        sprintf(buf, "IDispatch::GetIDsOfNames(\"%s\") failed w/err 0x%08lx", szName, hr);
        MessageBox(NULL, buf, "AutoWrap()", 0x10010);
        _exit(0);
        return hr;
    }

    // Allocate memory for arguments...
    VARIANT *pArgs = new VARIANT[cArgs+1];
    // Extract arguments...
    for(int i=0; i<cArgs; i++)
        {
        pArgs[i] = va_arg(marker, VARIANT);
    }

    // Build DISPPARAMS
    dp.cArgs = cArgs;
    dp.rgvarg = pArgs;

    // Handle special-case for property-puts!
    if(autoType & DISPATCH_PROPERTYPUT)
        {
        dp.cNamedArgs = 1;
        dp.rgdispidNamedArgs = &dispidNamed;
    }

    // Make the call!
    hr = pDisp->Invoke(dispID, IID_NULL, LOCALE_SYSTEM_DEFAULT, autoType, &dp, pvResult, NULL, NULL);
    if(FAILED(hr))
        {
                sprintf(buf, "IDispatch::Invoke(\"%s\"=%08lx) failed w/err 0x%08lx",
                        szName, dispID, hr);
                MessageBox(NULL, buf, "AutoWrap()", 0x10010);
                switch(hr)
                {
                case DISP_E_BADPARAMCOUNT:
                        MessageBox(NULL, "DISP_E_BADPARAMCOUNT", "Error:", 0x10010);
                        break;
                case DISP_E_BADVARTYPE:
                        MessageBox(NULL, "DISP_E_BADVARTYPE", "Error:", 0x10010);
                        break;
                case DISP_E_EXCEPTION:
                        MessageBox(NULL, "DISP_E_EXCEPTION", "Error:", 0x10010);
                        break;
                case DISP_E_MEMBERNOTFOUND:
                        MessageBox(NULL, "DISP_E_MEMBERNOTFOUND", "Error:", 0x10010);
                        break;
                case DISP_E_NONAMEDARGS:
                        MessageBox(NULL, "DISP_E_NONAMEDARGS", "Error:", 0x10010);
                        break;
                case DISP_E_OVERFLOW:
                        MessageBox(NULL, "DISP_E_OVERFLOW", "Error:", 0x10010);
                        break;
                case DISP_E_PARAMNOTFOUND:
                        MessageBox(NULL, "DISP_E_PARAMNOTFOUND", "Error:", 0x10010);
                        break;
                case DISP_E_TYPEMISMATCH:
                        MessageBox(NULL, "DISP_E_TYPEMISMATCH", "Error:", 0x10010);
                        break;
                case DISP_E_UNKNOWNINTERFACE:
                        MessageBox(NULL, "DISP_E_UNKNOWNINTERFACE", "Error:", 0x10010);
                        break;
                case DISP_E_UNKNOWNLCID:
                        MessageBox(NULL, "DISP_E_UNKNOWNLCID", "Error:", 0x10010);
                        break;
                case DISP_E_PARAMNOTOPTIONAL:
                        MessageBox(NULL, "DISP_E_PARAMNOTOPTIONAL", "Error:", 0x10010);
                        break;
                }
                // _exit(0);
                return hr;
        }
    // End variable-argument section...
    va_end(marker);

    delete [] pArgs;

    return hr;
}


/**
 * @brief constructor
 */
CAloExcelAuto::CAloExcelAuto()
{
        // initialize COM for this thread...
        if(CoInitialize(NULL)!=S_OK)
                m_bCOMInited = false;
        else
                m_bCOMInited = true;
}

/**
 * @brief destructor
 */
CAloExcelAuto::~CAloExcelAuto()
{
        // Uninitialized COM for this thread...
        if (m_bCOMInited)
                CoUninitialize();
}

/**
 * @brief Open a new Excel Window and create a new worksheet
 */
int CAloExcelAuto::Init()
{
        // Get CLSID for our server...
   CLSID clsid;
   HRESULT hr = CLSIDFromProgID(L"Excel.Application", &clsid);

   if(FAILED(hr))
   {
      ::MessageBox(NULL, "CLSIDFromProgID() failed", "Error", 0x10010);
      return -1;
   }

   // Start server and get IDispatch...   
   hr = CoCreateInstance(clsid, NULL, CLSCTX_LOCAL_SERVER, IID_IDispatch, (void **)&m_instance.pXlApp);
   if(FAILED(hr))
   {
      ::MessageBox(NULL, "Excel not registered properly", "Error", 0x10010);
      return -2;
   }

   // Get Workbooks collection
   VARIANT result;
   VariantInit(&result);
   AutoWrap(DISPATCH_PROPERTYGET, &result, m_instance.pXlApp, L"Workbooks", 0);
   m_instance.pXlBooks = result.pdispVal;

   m_bInit = true;

   return 1;
}

/**
 * @brief make excel app visible or not
 */
int CAloExcelAuto::SetVisible(bool bVisible)
{
        if( !CheckExcelInit() )
        {
                ::MessageBox(NULL, "Excel is not initialzed", "Error", 0x10010);
                return -1;
        }

        // Make it visible (i.e. app.visible = 1)       
        VARIANT x;
        x.vt = VT_I4;
        x.lVal = bVisible ? 1 : 0;
        HRESULT hr = AutoWrap(DISPATCH_PROPERTYPUT, NULL, m_instance.pXlApp, L"Visible", 1, x);
        if( FAILED(hr))
        {
                return -1;
        }

        return 1;
}

/**
 * @brief opne excel file 
 * @param[in]   file (const char*) the full path of the excel file to open 
 * @return              int     if success 1, otherwise negative number  
 */
int CAloExcelAuto::Open(const char* file)
{
        if( Init() < 0)
        {
                ::MessageBox(NULL, "Fail to init excel", "Error", 0x10010);
                return -1;
        }

        if( !CheckExcelInit() )
        {
                ::MessageBox(NULL, "Excel is not initialzed", "Error", 0x10010);
                return -1;
        }

        if( file == NULL)
        {
                ::MessageBox(NULL, "file name is null", "Error", 0x10010);
                return -1;
        }

        VARIANT result;
        VariantInit(&result);

        _bstr_t str = file;

        VARIANT vaPath;
        vaPath.vt = VT_BSTR;
        vaPath.bstrVal = ::SysAllocString(str);

        AutoWrap(DISPATCH_METHOD, &result, m_instance.pXlBooks, L"Open", 1, vaPath);
        m_instance.pXlBook = result.pdispVal;
        SysFreeString(vaPath.bstrVal);

        return 1;
}

/**
 * @brief set active sheet
 */
int CAloExcelAuto::SetActiveSheet(int nSheet)
{
        // Get ActiveSheet object


        VARIANT result;
        VariantInit(&result);

        VARIANT vaSheet;
        vaSheet.vt = VT_I4;
        vaSheet.lVal = nSheet;

        AutoWrap(DISPATCH_PROPERTYGET, &result, m_instance.pXlApp, L"Worksheets", 1, vaSheet);
        m_instance.pXlSheet = result.pdispVal;

        return 1;

}

/**
 * @brief save workbook
 */
void CAloExcelAuto::Save()
{
        // Set .Saved property of workbook to 'Saved'
        //VARIANT x;
        //x.vt = VT_I4;
        //x.lVal = bAsk ? 1 : 0;
        //AutoWrap(DISPATCH_PROPERTYPUT, NULL, m_instance.pXlBook, L"Saved", 1, x);     

        AutoWrap(DISPATCH_METHOD, NULL, m_instance.pXlApp, L"Save", 0);

}

/**
 * @brief close workbook
 */
void CAloExcelAuto::Close()
{
        //Save();       
        // Tell Excel to quit (i.e. App.Quit)
        AutoWrap(DISPATCH_METHOD, NULL, m_instance.pXlApp, L"Quit", 0);
        m_instance.pXlSheet->Release();
        m_instance.pXlBook->Release();
        m_instance.pXlBooks->Release();
        m_instance.pXlApp->Release();

        // Tell Excel to quit (i.e. App.Quit)
        //AutoWrap(DISPATCH_METHOD, NULL, m_instance.pXlApp, L"Close", 0);      
}

/**
 * @brief               get cell data on current active sheet
 * @param[in]   pPos (char*)    cell position e.g. B4
 * @param[out]  pData (char*)   cell data. string
 */
bool CAloExcelAuto::GetData(char* pPos, char* pData)
{
        if( pPos == NULL || pData == NULL)
                return false;

        // Get Range object for the Range A1:O15...
        _bstr_t str;
        str=pPos; // BSTR
        str+=":";
        str+=pPos;

        IDispatch *pXlRange;
        {
                VARIANT parm;
                parm.vt = VT_BSTR;
                parm.bstrVal = ::SysAllocString(str);
                VARIANT result;
                VariantInit(&result);
                AutoWrap(DISPATCH_PROPERTYGET, &result, m_instance.pXlSheet, L"Range", 1, parm);
                VariantClear(&parm);
                pXlRange = result.pdispVal;
        }

        VARIANT tmp;
        tmp.vt=VT_BSTR;
        AutoWrap(DISPATCH_PROPERTYGET, &tmp, pXlRange, L"Value", 0, 0);

        if (tmp.vt==VT_EMPTY)
        {
                pData[0]='\0';
        }
        else
        {
                VariantChangeType(&tmp, &tmp, VARIANT_NOUSEROVERRIDE, VT_BSTR);
                str=tmp.bstrVal;
                strcpy(pData, str);
        }
        // Release references...
        pXlRange->Release();
        return true;
}

/**
 * @brief               set cell data on current active sheet
 * @param[in]   pPos (char*)    cell position e.g. B4
 * @param[in]   pData (char*)   cell data. string
 */
bool CAloExcelAuto::SetData(char* pPos, char* pData)
{
        if( pPos == NULL || pData == NULL)
                return false;

        _bstr_t str=pData;

        VARIANT tmp;
        tmp.vt=VT_BSTR;
        tmp.bstrVal=::SysAllocString(str);

        // Get Range object for the Range       
        str=pPos;
        str+=":";
        str+=pPos;

        IDispatch *pXlRange;
        {
                VARIANT parm;
                parm.vt = VT_BSTR;
                parm.bstrVal = ::SysAllocString(str);
                VARIANT result;
                VariantInit(&result);
                AutoWrap(DISPATCH_PROPERTYGET, &result, m_instance.pXlSheet, L"Range", 1, parm);
                VariantClear(&parm);

                pXlRange = result.pdispVal;
        }

        // Set range with our safearray...
        AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXlRange, L"Value", 1, tmp);//     

        // Release references...
        pXlRange->Release();


        return true;
}

//

[edit]

4 Example code #

#include "windows.h"
#include "AloExcelAuto.h"
#include <iostream>

void main()
{
        CAloExcelAuto excel;


        // 1. open an excel file
        excel.Open("C:\\source\\Test\\ExcelAutomation\\test.xls");

        // 2. set visible 굳이 필요하진 않습니다. 쇼용?
        excel.SetVisible(true);

        // 3. set active sheet. 2번 째 시트의 값을 읽고 쓰기 위해서
        excel.SetActiveSheet(2);

        // 4. get data C4의 값을 가져와보기
        char pData[256];
        excel.GetData("C4", pData);
        std::cout<<"data: "<<pData<<std::endl;

        // set data. C5에 쓰기
        memset(pData, 0x00, 256);
        strcpy( pData, "테스트");
        excel.SetData("C5", pData);

        // save
        excel.Save();

        // close
        excel.Close();
}

//

[edit]

5 Binary #

[edit]

5.1 source code #

AloExcelAuto.zip

[edit]

5.2 chm file #

AloExcelAuto_2.zip

[edit]

6 References #

• C++에서 MFC나 #import를 사용하지 않고 Excel을 자동화하는 방법
  • MSDN으로 class로 정리는 안한 샘플 코드이지만 대부분의 내용들이 들어있다.
• comp.os.ms-windows.programmer.win32
  • 위 MSDN에 나와있지 않은 몇 가지 기능에 대해서 나와있다.
• How To Use Visual C++ to Access DocumentProperties with Automation
  • Word를 win32로 automation한 것인데 "Save" 등은 여기서 참고 했다.