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

最新推荐文章于 2021-08-31 15:22:32 发布

iteye_15675

最新推荐文章于 2021-08-31 15:22:32 发布

阅读量113

点赞数

文章标签：人工智能

下面是创建 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!”