用MFC插入Excel工作表實現自動化

這篇文章講述了如何使用MFC將Excel工作表插入到SDI視圖中。文章包括插入工作表并將文字添加到A1單元格的詳細步驟，每一步都有詳細說明。 雖然你可以直接將代碼插入到你的程序中，但理解這些例子你才會真正受益。


更多信息


以下是創建這個MFC應用程序的步驟：

1.使用AppWizard創建一個新的MFC AppWizard(EXE)工程，命名為"Embed_Excel"

2.選擇單文檔視圖(SDI)結構，在第3步中需要選中Container,以提供容器支持。

其它都為默認。


產生以下類：


應用類: CEmbed_ExcelApp in Embed_Excel.h and Embed_Excel.cpp

框架類: CMainFrame in MainFrm.h and MainFrm.cpp

文檔類: CEmbed_ExcelDoc in Embed_ExcelDoc.h and Embed_ExcelDoc.cpp

視圖類: CEmbed_ExcelView in Embed_ExcelView.h and Embed_ExcelView.cpp

容器類: CEmbed_ExcelCntrItem in CntrItem.h and CntrItem.cpp


3.在VIEW菜單中，選ClassWizard,選Automation選項卡,選Add Class,選擇From a TypeLibrary, 選中Microsoft Excel 97/2000 類型庫:Excel8.olb或Excel9.olb(在Microsoft Office\Office目錄下) 會將類型庫中的所有類添加到你的工程中。



4.在CntrItem.h中為CEmbed_ExcelCntrItem類添加如下函數定義：




LPDISPATCH GetIDispatch();

5.然后在CntrItem.cpp中添加GetIDispatch方法


示例代碼

-----------



/*******************************************************************

* 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中為CEmbed_ExcelView類添加如下函數定義:



void EmbedAutomateExcel();

7.然后在Embed_ExcelView.cpp中添加EmbedAutomateExcel方法:



示例代碼

-----------


/********************************************************************

* 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();

}

將下面一行添加到 Embed_ExcelView.h:

#include "excel8.h"

注意:如果使用Excel 2000, 頭文件是 "excel9.h."


看一下View類中的 OnInsertObject() 方法，對其中的注釋引起了我們的興趣，因為它和我們剛寫的方法有驚人的相似。事實上，我們剛才寫的是OnInsertObject()的一個特例：允許用戶從可用的OLE對象列表中選擇其一插入到應用程序中。因為我們只想對Excel工作表進行自動化，所以派生這一行為。在我們的程序中，我們移去了InsertObject()內部的所有代碼，用如上EmbedAutomateExcel()中的代碼代替，或者你可以在InsertObject()函數中直接調用EmbedAutomateExcel()。


編譯并運行我們的程序。

在編輯菜單中選擇 插入新對象.


運行結果：一張Microsoft Excel 工作表插入到視圖中；并且通過自動化，A1單元格被填上"Hello, World!" 字符串。

原文轉自：http://www.anti-gravitydesign.com