使用MFC插入Excel工作表并实现自动化

 摘要
这篇文章讲述了如何使用MFC将Excel工作表插入到SDI视图中。
文章包括插入工作表并将文字添加到A1单元格的详细步骤，每一步都有详细说明。 虽然你可以直接将代码插入到你的程序中，
但理解这些例子你才会真正受益。

更多信息

以下是创建这个MFC应用程序的步骤：
1.使用AppWizard创建一个新的MFC AppWizard(EXE)工程，命名为"Embed_Excel"
2.选择单文档视图(SDI)结构，在第3步中需要选中Container,以提供容器支持。
其它都为默认。

产生以下类：

应用类: CEmbed_ExcelApp in Embed_Excel.h and Embed_Excel.cpp
框架类: CMainFrame in MainFrm.h and MainFrm.cpp
文档类: CEmbed_ExcelDoc in Embed_ExcelDoc.h and Embed_ExcelDoc.cpp
视图类: CEmbed_ExcelView in Embed_ExcelView.h and Embed_ExcelView.cpp
容器类: CEmbed_ExcelCntrItem in CntrItem.h and CntrItem.cpp

3.在VIEW菜单中，选ClassWizard,选Automation选项卡,选Add Class,选择From a TypeLibrary, 选中Microsoft Excel 97/2000 类型库,Excel8.olb或Excel9.olb会将类型库中的所有类添加到你的工程中。

4.在CntrItem.h中添加如下行：

LPDISPATCH GetIDispatch(); 

5.然后在CntrItem.cpp中添加GetIDispatch方法         
          

   示例代码   -----------      /*******************************************************************      *   This method returns the IDispatch* for the application linked to      *   this container.      ********************************************************************/       LPDISPATCH CEmbed_ExcelCntrItem::GetIDispatch()      {         //The this and m_lpObject pointers must be valid for this function         //to work correctly. The m_lpObject is the IUnknown pointer to         // this object.         ASSERT_VALID(this);         ASSERT(m_lpObject != NULL);         LPUNKNOWN lpUnk = m_lpObject;         //The embedded application must be running in order for the rest         //of the function to work.         Run();         //QI for the IOleLink interface of m_lpObject.         LPOLELINK lpOleLink = NULL;         if (m_lpObject->QueryInterface(IID_IOleLink,            (LPVOID FAR*)&lpOleLink) == NOERROR)         {            ASSERT(lpOleLink != NULL);            lpUnk = NULL;            //Retrieve the IUnknown interface to the linked application.            if (lpOleLink->GetBoundSource(&lpUnk) != NOERROR)            {               TRACE0("Warning: Link is not connected!/n");               lpOleLink->Release();               return NULL;            }            ASSERT(lpUnk != NULL);         }         //QI for the IDispatch interface of the linked application.         LPDISPATCH lpDispatch = NULL;         if (lpUnk->QueryInterface(IID_IDispatch, (LPVOID FAR*)&lpDispatch)            !=NOERROR)         {            TRACE0("Warning: does not support IDispatch!/n");            return NULL;         }         //After assuring ourselves it is valid, return the IDispatch         //interface to the caller.         ASSERT(lpDispatch != NULL);         return lpDispatch;      } 

6.在Embed_ExcelView.h中添加如下行:

      void EmbedAutomateExcel(); 

7.然后在Embed_ExcelView.cpp中添加EmbedAutomateExcel方法:

示例代码   -----------      /********************************************************************      *   This method encapsulates the process of embedding an Excel      *   Worksheet in a View object and automating that worksheet to add      *   some text to cell A1.      ********************************************************************/       void CEmbed_ExcelView::EmbedAutomateExcel()      {         //Change the cursor so the user knows something exciting is going         //on.         BeginWaitCursor();         CEmbed_ExcelCntrItem* pItem = NULL;         TRY         {            //Get the document associated with this view, and be sure it's            //valid.            CEmbed_ExcelDoc* pDoc = GetDocument();            ASSERT_VALID(pDoc);            //Create a new item associated with this document, and be sure            //it's valid.            pItem = new CEmbed_ExcelCntrItem(pDoc);            ASSERT_VALID(pItem);            // Get Class ID for Excel sheet.            // This is used in creation.            CLSID clsid;            if(FAILED(::CLSIDFromProgID(L"Excel.sheet",&clsid)))               //Any exception will do. We just need to break out of the               //TRY statement.               AfxThrowMemoryException();            // Create the Excel embedded item.            if(!pItem->CreateNewItem(clsid))               //Any exception will do. We just need to break out of the               //TRY statement.               AfxThrowMemoryException();            //Make sure the new CContainerItem is valid.            ASSERT_VALID(pItem);            // Launch the server to edit the item.            pItem->DoVerb(OLEIVERB_SHOW, this);            // As an arbitrary user interface design, this sets the            // selection to the last item inserted.            m_pSelection = pItem;   // set selection to last inserted item            pDoc->UpdateAllViews(NULL);            //Query for the dispatch pointer for the embedded object. In            //this case, this is the Excel worksheet.            LPDISPATCH lpDisp;            lpDisp = pItem->GetIDispatch();            //Add text in cell A1 of the embedded Excel sheet            _Workbook wb;            Worksheets wsSet;            _Worksheet ws;            Range range;            _Application app;            //set _Workbook wb to use lpDisp, the IDispatch* of the            //actual workbook.            wb.AttachDispatch(lpDisp);            //Then get the worksheet's application.            app = wb.GetApplication();            //Then get the first worksheet in the workbook            wsSet = wb.GetWorksheets();            ws = wsSet.GetItem(COleVariant((short)1));            //From there, get a Range object corresponding to cell A1.            range = ws.GetRange(COleVariant("A1"), COleVariant("A1"));            //Fill A1 with the string "Hello, World!"            range.SetValue(COleVariant("Hello, World!"));         }           //Here, we need to do clean up if something went wrong.           CATCH(CException, e)           {              if (pItem != NULL)              {                 ASSERT_VALID(pItem);                 pItem->Delete();              }              AfxMessageBox(IDP_FAILED_TO_CREATE);           }           END_CATCH           //Set the cursor back to normal so the user knows exciting stuff           //is no longer happening.           EndWaitCursor();        } 

将下面一行添加到 Embed_ExcelView.h:

      #include "excel8.h" 

注意:如果使用Excel 2000, 头文件是 "excel9.h."

看一下View类中的 OnInsertObject() 方法，对其中的注释引起了我们的兴趣，因为它和我们刚写的方法有惊人的相似。事实上，我们刚才写的是OnInsertObject()的一个特例：允许用户从可用的OLE对象列表中选择其一插入到应用程序中。因为我们只想对Excel工作表进行自动化，所以派生这一行为。在我们的程序中，我们移去了InsertObject()内部的所有代码然后把它称作EmbedAutomateExcel()。

编译并运行我们的程序。
在Edit 菜单中选择 Insert New Object.

运行结果：一张Microsoft Excel 工作表插入到视图中；并且通过自动化，A1单元格被填上"Hello, World!" 字符串。