HOWTO: Embed and Automate a Microsoft Excel Worksheet with MFC

• Microsoft Visual C++, 32-bit Professional Edition, versions 5.0, 5.0sp3, 6.0
• The Microsoft Foundation Classes (MFC)
• Microsoft Office 2000 Developer
• Microsoft Excel 2000
• Microsoft Excel 97 for Windows

SUMMARY

This article describes how to embed a Microsoft Excel Worksheet into a View object in an SDI MFC application.

This article includes step-by-step instructions for embedding the worksheet and adding some text to cell A1, as well as comments explaining each step.

Although the sample code in this article can be taken and put into your application, the real benefit comes from reading and understanding the sample.

MORE INFORMATION

Here are the steps for creating the MFC application:

1. Use the AppWizard to create a new MFC AppWizard (EXE) project named "Embed_Excel."



2. Select Single Document as the type of application to create, and select Container as the type of compound document support to include. Accept all other default settings.
   
   The following classes are generated:
   
   Application: CEmbed_ExcelApp in Embed_Excel.h and Embed_Excel.cpp
   
   Frame: CMainFrame in MainFrm.h and MainFrm.cpp
   
   Document: CEmbed_ExcelDoc in Embed_ExcelDoc.h and Embed_ExcelDoc.cpp
   
   View: CEmbed_ExcelView in Embed_ExcelView.h and Embed_ExcelView.cpp
   
   Container Item: CEmbed_ExcelCntrItem in CntrItem.h and CntrItem.cpp



3. On the View menu, click ClassWizard. Click the Automation tab, click Add Class, and choose From a Type Library. Locate the Microsoft Excel 97/2000 type library, Excel8.olb or Excel9.olb respectively, and add all the classes in the type library to your project.



4. Add the following line to CntrItem.h:




LPDISPATCH GetIDispatch(); 

5. Then add the GetIDispatch method to 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. Add the following line to Embed_ExcelView.h:




      void EmbedAutomateExcel(); 

7. Then add the EmbedAutomateExcel method to 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!"));
         }

           //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. Add the following line to Embed_ExcelView.h:

   
         #include "excel8.h" 

   NOTE: If you are automating Excel 2000, the header file is "excel9.h."



9. Look at the OnInsertObject() method of the View class. It is interesting to note that this method, and the method we've just written, are strikingly similar. In fact, the code we've written is merely a special case of OnInsertObject(), which allows the user to select from a list of available OLE objects to insert into the application. Because we only want to automate the Excel Worksheet, we override this behavior. For our application, remove all the code from the interior of InsertObject() and replace it with a call to EmbedAutomateExcel().



10. Compile and run the application.



11. On the Edit menu, click Insert New Object.


RESULTS: A Microsoft Excel worksheet is embedded into the View; additionally, through automation, the contents of cell A1 is populated with "Hello, World!"

Additional query words:

Keywords : kbcode kbinterop kbole kbAutomation kbMFC kbVC500 kbVC600 kbExcel97
Version : WINDOWS:2000,97; winnt:5.0,5.0sp3,6.0; :
Platform : WINDOWS winnt
Issue type : kbhowto