如何使用 MFC 嵌入并自动化 Microsoft Excel 工作表

概要

本文介绍如何将 Microsoft Excel 工作表嵌入 SDI MFC 应用程序中的 View 对象。

本文包含嵌入工作表和向单元格 A1 添加文本的分步骤说明，并包含解释每个步骤的注释。

尽管可以将本文中的示例代码直接拿到您的应用程序中，但只有阅读和理解了文中的示例才会使您真正获益。

更多信息

下面是创建 MFC 应用程序的步骤：1.使用应用程序向导新建一个名为“Embed_Excel”的 MFC 应用程序向导 (EXE) 项目。2.选择“单文档”作为要创建的应用程序类型，并选择“容器”作为要包括的复合文档支持类型。接受所有其他默认设置。

将生成下面几个类：

Application：Embed_Excel.h 和 Embed_Excel.cpp 中的 CEmbed_ExcelApp

Frame：MainFrm.h 和 MainFrm.cpp 中的 CMainFrame

Document：Embed_ExcelDoc.h 和 Embed_ExcelDoc.cpp 中的 CEmbed_ExcelDoc

View：Embed_ExcelView.h 和 Embed_ExcelView.cpp 中的 CEmbed_ExcelView

Container Item：CntrItem.h 和 CntrItem.cpp 中的 CEmbed_ExcelCntrItem3.在视图菜单上，单击类向导。单击自动化选项卡，单击添加类，并选择从类型库。找到 Microsoft Excel 类型库，然后将类型库中的所有类都添加到您的项目中。对于 Excel 97，类型库位于 Excel8.olb。对于 Excel 2000，类型库位于 Excel9.olb；对于 Excel 2002，类型库位于 Excel.exe。4.将下面一行代码添加到 CntrItem.h：

LPDISPATCH GetIDispatch();            

5.然后将 GetIDispatch 方法添加到 CntrItem.cpp：

   Sample Code            -----------            /*******************************************************************            *   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.然后将 EmbedAutomateExcel 方法添加到 Embed_ExcelView.cpp：

Sample Code            -----------            /********************************************************************            *   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!"));            //NOTE: If you are automating Excel 2002, the Range.SetValue method has an            //additional optional parameter specifying the data type.  Because the            //parameter is optional, existing code will still work correctly, but new            //code should use the new convention.  The call for Excel2002 should look            //like the following:            //range.SetValue( ColeVariant( (long)DISP_E_PARAMNOTFOUND, VT_ERROR ),            //                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();            }            

8.将下面一行代码添加到 Embed_ExcelView.h：

      #include "excel8.h"            

注意：如果要自动化 Excel 2000，头文件应为“excel9.h”。如果要自动化 Excel 2002，头文件应为“excel.h”。9.看一下 View 类的 OnInsertObject() 方法。您会注意到一件非常有趣的事：这个方法与我们刚刚编写的方法惊人地相似。事实上，我们编写的代码只是 OnInsertObject() 的一个特例，它允许用户从可用的 OLE 对象列表中选择插入应用程序的对象。由于我们的目的只是自动化 Excel 工作表，因此覆盖了这一行为。在我们的应用程序中，从 InsertObject() 内部删除了所有代码，并代之以对 EmbedAutomateExcel() 的调用。10.编译并运行应用程序。11.在编辑菜单上，单击插入新对象。结果：Microsoft Excel 工作表被嵌入到 View 对象中；另外，通过自动化，在单元格 A1 的内容中填入了“Hello, World!”

---

这篇文章中的信息适用于:

•Microsoft Excel 2000 标准版•Microsoft Visual C++ 5.0 专业版•Microsoft Visual C++ 6.0 专业版•Microsoft Foundation Class Library 4.2•Microsoft Office XP Developer Edition•Microsoft Office 2000 Developer Edition•Microsoft Excel 2002 标准版•Microsoft Excel 97 标准版

 

 

更多技术文章请参看施昌权的个人网站： http://www.joyvc.cn