자동화 - MFC

* 엑셀
// to automate Microsoft Excel 97, Excel 2000, or Excel 2002
1. 새로운 MFC 어플리케이션 프로젝트를 생성한다.

2. 자동화 서버의 타입 라이브러리로 부터 래퍼 클래스(IDispatch)를 생성한다.

Microsoft Excel 97

    Microsoft Excel 8.0 Object Library // Excel8.olb, excel8.h

Microsoft Excel 2000
    Microsoft Excel 9.0 Type Library
        C:\Program Files\Microsoft Office\Office\Excel9.olb // excel9.h
Microsoft Excel 2002 // OfficeXP
    Microsoft Excel 10.0 Type Library
        C:\program Files\Microsoft Office\Office10\Excel.exe // excel.h
Microsoft Excel 2003 // Office 2003
    Microsoft Excel 11.0 Object Library

        C:\program Files\Microsoft Office\Office11\Excel.exe

_Application, _Workbook, _Worksheet, Workbooks, Worksheets, Range

COleDispatchDriver

.AttachDispatch()
.ReleaseDispatch()

_Application -> COleDispatchDriver

.CreateDispatch()
.Quit()
.SetVisible()
.GetWorkbooks()

Workbooks -> COleDispatchDriver

.Open() // _Workbook

_Workbook -> COleDispatchDriver

.GetSheets() // Worksheets

.GetWorksheets() // Worksheets
.SetSaved()
.Close()

Worksheets -> COleDispatchDriver

.GetCount()

.GetItem() // _Worksheet

_Worksheet -> COleDispatchDriver

.Activate()

.GetRange() // Range

Range -> COleDispatchDriver

.Clear()
.SetValue2()

.GetValue2()

BOOL CAutoProjectApp::InitInstance()
{
// {
     if(!AfxOleInit())  // Your addition starts here
     {
        AfxMessageBox("Could not initialize COM dll");
        return FALSE;
     }                 // End of your addition
// }
    AfxEnableControlContainer();
...
}

#include "excel.h"

void CAutoProjectDlg::OnBnClickedRun()
{
    // TODO: Add your control notification handler code here
      // This example walks through three worksheets and places

      // literal strings in cells A1 and B2 on each sheet.

      try
       {
       _Application app;  // app is an _Application object.
       _Workbook book;
       _Worksheet sheet;
       Workbooks books;
       Worksheets sheets;
       Range range;
       char buf[1024];
       LPDISPATCH lpDisp; // IDispatch *; pointer reused many times.
       long count; // Count of the number of worksheets.
       int i;

       // Common OLE variants. These are easy variants to use for
       // calling arguments.
       COleVariant
                  covTrue((short)TRUE),
                  covFalse((short)FALSE),
                  covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);

       // Start Excel and get Application object.
       if(!app.CreateDispatch("Excel.Application"))
       {
        AfxMessageBox("Couldn't CreateDispatch on Excel");
        return;
       }

       // Set visible.
       app.SetVisible(TRUE);

       // Get Workbooks collection.
       lpDisp = app.GetWorkbooks();  // Get an IDispatch pointer

       ASSERT(lpDisp);               // or fail.
       books.AttachDispatch( lpDisp );  // Attach the IDispatch pointer
                                        // to the books object.

          // Open a workbook.
          lpDisp = books.Open("D:\\work\\Data\\Excel\\Test.xls",
//          lpDisp = books.Open("C:\\Test.xls",
                         covOptional, covOptional, covOptional, covOptional,
                         covOptional, covOptional, covOptional, covOptional,
                         covOptional, covOptional, covOptional, covOptional,
                         covOptional, covOptional); // Excel 2000 has 13 parameters
//                         covOptional, covOptional, covOptional); // Excel 2000 has 13 parameters
          ASSERT(lpDisp);  // It worked!



       // Attach to a Workbook object.
       book.AttachDispatch( lpDisp );  // Attach the IDispatch pointer
                                       // to the book object.

       // Get sheets.
       lpDisp = book.GetSheets();
       ASSERT(lpDisp);
       sheets.AttachDispatch(lpDisp);

       // Get the number of worksheets in this book.
       count = sheets.GetCount();

       // Enumerate through worksheets in book and activate in
       // succession.
       for(i=0; i<count; i++)
       {
        // Get the sheet. Note that 1 is added to the index to make sure
        // it is 1-based, not zero-based. Otherwise, you will get odd
        // exceptions.
        lpDisp = sheets.GetItem( COleVariant((short)(i+1)) ); // 'Item' in
                              // the Worksheets collection = worksheet #.
        ASSERT(lpDisp);
        sheet.AttachDispatch(lpDisp);
        // Activate and sleep for two seconds so you can see it happen.
        sheet.Activate();

        lpDisp = sheet.GetRange(COleVariant("A1"), COleVariant("W40"));
                                                   // From cell# To cell#.
        ASSERT(lpDisp);
        range.AttachDispatch(lpDisp);  // Attach the IDispatch pointer
                                       // to the range.
        range.Clear();  // Could be ClearContents().

        lpDisp = sheet.GetRange(COleVariant("A1"), COleVariant("A1"));
                                                       // From and To.
        ASSERT(lpDisp);
        range.AttachDispatch(lpDisp);// Attach the IDispatch pointer
                                     // to the range.
        range.SetValue2(COleVariant("A1A1")); // Excel 2002. A1A1 is data string
//        range.SetValue(COleVariant("A1A1")); // Excel 97 and Excel 2000.

        // Release dispatch pointer.
        sheet.ReleaseDispatch();
        } // End of For loop. You walked through all three sheets of the
          // workbook, and stuffed data into cells A1 and B2.

       // Set the workbook back to sheet 1 so that it starts there next
       // time it is opened.
       lpDisp = sheets.GetItem( COleVariant((short)(1)) );

       ASSERT(lpDisp);
       sheet.AttachDispatch(lpDisp);
       sheet.Activate();

//       book.SetSaved(TRUE); // Forestall the 'Save ?' dialog box.

       app.Quit();  //Excel departs.

       }  // End of Processing logic.
      catch(COleException *e)
      {
       char buf[1024];
       sprintf(buf, "COleException. SCODE: %08lx.", (long)e->
m_sc);
       ::MessageBox(NULL, buf, "COleException", MB_SETFOREGROUND | MB_OK);
      }
      catch(COleDispatchException *e)
      {
       char buf[1024];
       sprintf(buf,
          "COleDispatchException. SCODE: %08lx,Description: \"%s\".",
          (long)e->m_wCode, (LPSTR)e->m_strDescription.GetBuffer(1024));
       ::MessageBox(NULL, buf, "COleDispatchException",
                    MB_SETFOREGROUND | MB_OK);
      }
      catch(...)
      {
       ::MessageBox(NULL, "General Exception caught.", "Catch-All",
                    MB_SETFOREGROUND | MB_OK);
      }
/*
      // OLE Variant for Optional.
      COleVariant VOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);

      _Application objApp;
      _Workbook objBook;
      Workbooks objBooks;
      Worksheets objSheets;
      _Worksheet objSheet;
      Range objRange;
      VARIANT ret;

      // Instantiate Excel and open an existing workbook.
      objApp.CreateDispatch("Excel.Application");
      objBooks = objApp.GetWorkbooks();
      objBook = objBooks.Open("C:\\Test.xls",
                VOptional, VOptional, VOptional, VOptional,
                VOptional, VOptional, VOptional, VOptional,
                VOptional, VOptional, VOptional, VOptional, VOptional, VOptional);
      objSheets = objBook.GetWorksheets();
      objSheet = objSheets.GetItem(COleVariant((short)1));

      //Get the range object for which you wish to retrieve the
      //data and then retrieve the data (as a variant array, ret).
      objRange = objSheet.GetRange(COleVariant("A1"), COleVariant("C8"));
      ret = objRange.GetValue2();
//      ret = objRange.GetValue();

      //Create the SAFEARRAY from the VARIANT ret.
      COleSafeArray sa(ret);

      //Determine the array's dimensions.
      long lNumRows;
      long lNumCols;
      sa.GetUBound(1, &lNumRows);
      sa.GetUBound(2, &lNumCols);

      //Display the elements in the SAFEARRAY.
      long index[2];
      VARIANT val;
      int r, c;
      TRACE("Contents of SafeArray\n");
      TRACE("=====================\n\t");
      for(c=1;c<=lNumCols;c++)
      {
         TRACE("\t\tCol %d", c);
      }
      TRACE("\n");
      for(r=1;r<=lNumRows;r++)
      {
         TRACE("Row %d", r);
         for(c=1;c<=lNumCols;c++)
         {
            index[0]=r;
            index[1]=c;
            sa.GetElement(index, &val);
            switch(val.vt)
            {
            case VT_R8:
               {
                  TRACE("\t\t%1.2f", val.dblVal);
                  break;
               }
            case VT_BSTR:
               {
                  TRACE("\t\t%s",(CString)val.bstrVal);
                  break;
               }

            case VT_EMPTY:
               {
                  TRACE("\t\t<empty>");
                  break;
               }
            }
         }
         TRACE("\n");
      }

      //Close the workbook without saving changes
      //and quit Microsoft Excel.
      objBook.Close(COleVariant((short)FALSE), VOptional, VOptional);
      objApp.Quit(); */
}

참조 사이트:

http://www.devpia.com/MAEUL/Contents/Detail.aspx?BoardID=51&MAEULNO=20&no=8151&page=8
http://support.microsoft.com/kb/178749/
http://support.microsoft.com/kb/178782/en-us/
http://support.microsoft.com/kb/307473/
http://support.microsoft.com/kb/186122/en-us/
http://support.microsoft.com/kb/186120/EN-US/
http://support.microsoft.com/kb/308407/
http://www.codeproject.com/KB/COM/ComExcelImages.aspx

http://support.microsoft.com/kb/178781/en-us/

CXLAutomation

CXLEzAutomation

http://whiteat.com/zbxe/17858
http://www.codeguru.com/cpp/data/mfc_database/microsoftexcel/print.php/c11745/
http://support.microsoft.com/kb/141759/en-us/