用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.ltesting.net