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

最新推荐文章于 2018-08-12 15:24:37 发布

Augusdi

最新推荐文章于 2018-08-12 15:24:37 发布

阅读量2.1k

点赞数

分类专栏： Office编程文章标签： excel microsoft mfc 工作 application null

Office编程专栏收录该内容

136 篇文章 7 订阅

订阅专栏

下面是创建 MFC 应用程序的步骤：

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

将生成下面几个类：

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
在视图菜单上，单击类向导。单击自动化选项卡，单击添加类，并选择从类型库。找到 Microsoft Excel 类型库，然后将类型库中的所有类都添加到您的项目中。对于 Excel 97，类型库位于 Excel8.olb。对于 Excel 2000，类型库位于 Excel9.olb；对于 Excel 2002，类型库位于 Excel.exe。
将下面一行代码添加到 CntrItem.h：
LPDISPATCH GetIDispatch();

然后将 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;
      }

将下面一行代码添加到 Embed_ExcelView.h：
void EmbedAutomateExcel();

然后将 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();
        }

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