如何使用 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_ExcelCntrItem
3. 视图菜单上,单击类向导。单击自动化选项卡,单击添加类,并选择从类型库。找到 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 标准版

 

 

 

posted on 2008-03-07 11:11  施昌权-博客  阅读(5262)  评论(3编辑  收藏  举报

导航