请各位老大给个例子,教我一下。1.通过COM接口实现。
2.数据库接口实现。
3.#import倒入excel类库实现。
4.智能指针实现。无论哪种方法都行,谢谢。
2.数据库接口实现。
3.#import倒入excel类库实现。
4.智能指针实现。无论哪种方法都行,谢谢。
解决方案 »
- VC中操作EXCEL的问题。
- 关于在对话框的绘图区域弹出工具提示(tooltip)问题
- 搜索了好久,未果,高分求助(菜鸟级),如何获得正确的返回值。
- 我在单文档应用程序中创建了一个弹出对话框,里面放了一些控件,我在对话框的OnCreate函数中使用GetDlgItem(IDC_PROXYIP)->EnableWindow(
- 将问题进行到底!!!
- NetMessageBufferSend() API 发送信息会乱码吗?
- 请问:加入一个工程以后,两个工程怎样连接,才能执行
- 那有好看的图标,图片?(自己画太麻烦)
- VC里有象位段里元素那样的操作吗
- MFC中利用ODBC保存数据为Excel的问题
- 设置ClistCtrl 单元格颜色
- 如何同时在一个进程中同时播放两个WAVE文件
This article shows how to automate Microsoft Excel 97, Microsoft Excel 2000, or Microsoft Excel 2002 using the Microsoft Foundation Class (MFC) library, version 4.2 (installed with Microsoft Visual C++ versions 5.0 and 6.0). Specifically, this article illustrates how to use worksheet functions provided by an add-in such as the Analysis ToolPak (ATP) and how to use the worksheet formula functions that are built-in to Microsoft Excel. More Information
You can copy the code in this article to the message handler function of an event defined in an MFC .cpp file. However, the purpose of the code is to illustrate the process of using the IDispatch interfaces and member functions defined in the Excel8.olb for Excel 97, in Excel9.olb for Excel 2000, and in Excel.exe for the Excel 2002 type library. The primary benefit comes from reading and understanding the code in the example, so that you can modify the example or write code from scratch to automate a worksheet function in Microsoft Excel using MFC. Steps to Create the Project
Follow steps 1 through 12 in the following Microsoft Knowledge Base article to create a sample project that uses the IDispatch interfaces and member functions defined in the Excel8.olb, or Excel9.olb, or Excel.exe for Excel 2002 type library:
Q178749 HOWTO: Create an Automation Project Using MFC and a Type Library
At the top of the AutoProjectDlg.cpp, add the following line:
#include "excel8.h"
If you are automating Excel 2000, include excel9.h. If you are automating Excel 2002, include excel.h
Add the following code to CAutoProjectDlg::OnRun() in the AutoProjectDLG.cpp file:
try
{
_Application app; // app is an _Application object.
_Workbook book; // More object declarations.
_Worksheet sheet;
Workbooks books;
Worksheets sheets; Range range; // Used for Microsoft Excel 97 components.
LPDISPATCH lpDisp; // Often reused variable. // Common OLE variants. Easy variants to use for calling arguments.
COleVariant
covTrue((short)TRUE),
covFalse((short)FALSE),
covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR); // Start Microsoft Excel, get _Application object,
// and attach to app object.
if(!app.CreateDispatch("Excel.Application"))
{
AfxMessageBox("Couldn't CreateDispatch() for Excel");
return;
}
// Set visible.
app.SetVisible(TRUE); // Register the Analysis ToolPak.
CString sAppPath; sAppPath.Format ("%s\\Analysis\\Analys32.xll", app.GetLibraryPath()); if(!app.RegisterXLL(sAppPath))
AfxMessageBox("Didn't register the Analys32.xll"); // Get the Workbooks collection.
lpDisp = app.GetWorkbooks(); // Get an IDispatch pointer.
ASSERT(lpDisp);
books.AttachDispatch(lpDisp); // Attach the IDispatch pointer
// to the books object. // Open a new workbook and attach that IDispatch pointer to the
// Workbook object.
lpDisp = books.Add( covOptional );
ASSERT(lpDisp);
book.AttachDispatch( lpDisp ); // To open an existing workbook, you need to provide all
// arguments for the Open member function. In the case of
// Excel 2002 you need to provide 16 arguments.
// The code below opens a workbook and adds it to the Workbook's
// Collection object. It shows 13 arguments, required for Excel
// 2000.
// You need to modify the path and file name for your own
// workbook. //
// lpDisp = books.Open("C:\\Test", // Test.xls is a workbook.
// covOptional, covOptional, covOptional, covOptional, covOptional,
// covOptional, covOptional, covOptional, covOptional, covOptional,
// covOptional, covOptional ); // Return Workbook's IDispatch
// pointer. // Get the Sheets collection and attach the IDispatch pointer to your
// sheets object.
lpDisp = book.GetSheets();
ASSERT(lpDisp);
sheets.AttachDispatch(lpDisp); // Get sheet #1 and attach the IDispatch pointer to your sheet
// object.
lpDisp = sheets.GetItem( COleVariant((short)(1)) );
//GetItem(const VARIANT &index)
ASSERT(lpDisp);
sheet.AttachDispatch(lpDisp); // Fill range A1 with "1/25/98", the settlement date.
lpDisp = sheet.GetRange(COleVariant("A1"), COleVariant("A1"));
ASSERT(lpDisp);
range.AttachDispatch(lpDisp);
range.SetValue(COleVariant("1/25/98")); // Excel 97 & Excel 2000range.SetValue2(COleVariant("1/25/98")); // Excel 2002
// Fill range A2 with "11/15/99", the maturity date.
lpDisp = sheet.GetRange(COleVariant("A2"), COleVariant("A2"));
ASSERT(lpDisp);
range.AttachDispatch(lpDisp);
range.SetValue(COleVariant("11/15/99")); // Excel 97 & Excel 2000range.SetValue2(COleVariant("11/15/99")); // Excel 2002
// Fill range A3 with "2", the frequency for semi-annual interest
// payments.
lpDisp = sheet.GetRange(COleVariant("A3"), COleVariant("A3"));
ASSERT(lpDisp);
range.AttachDispatch(lpDisp);
range.SetValue(COleVariant("2")); // Excel 97 & Excel 2000range.SetValue2(COleVariant("2")); // Excel 2002 // Fill range A4 with 1, the basis (actual/actual).
lpDisp = sheet.GetRange(COleVariant("A4"), COleVariant("A4"));
ASSERT(lpDisp);
range.AttachDispatch(lpDisp);
range.SetValue(COleVariant("1")); // Excel 97 & Excel 2000range.SetValue2(COleVariant("1")); // Excel 2002 // Fill range C1 with the formula "=COUPNCD(A1, A2, A3, A4)" and
// format the cell with a Date type of the Number format.
lpDisp = sheet.GetRange(COleVariant("C1"), COleVariant("C1"));
ASSERT(lpDisp);
range.AttachDispatch(lpDisp);
range.SetNumberFormat(COleVariant("mm/dd/yy"));
range.SetFormula(COleVariant("=COUPNCD(A1, A2, A3, A4)")); /* This is an alternative that works without placing variables on
// the worksheet.
// The values are arguments contained in the SetFormula() call.
// range.SetFormula(COleVariant(
"=COUPNCD(\"09/15/96\",\"11/15/99\",2,1)"));
*/ // *** The example in this block uses a built-in Microsoft Excel
// function. // You do not have to register any add-in to use the built-in // Microsoft Excel worksheet functions.
lpDisp = sheet.GetRange(COleVariant("C3"), COleVariant("C3"));
ASSERT(lpDisp);
range.AttachDispatch(lpDisp);
range.SetFormula(COleVariant("=SUM(A3, A4)"));
// or use:
// range.SetFormula(COleVariant("=SUM(2,1)")); // *** End of example for built-in function usage. // Release dispatch pointers.
range.ReleaseDispatch();
sheet.ReleaseDispatch();
// This is not really necessary because
// the default second parameter of AttachDispatch releases
// when the current scope is lost. } // End of processing. catch(COleException *e)
{
char buf[1024]; // For the Try...Catch error message.
sprintf(buf, "COleException. SCODE: %08lx.", (long)e->m_sc);
::MessageBox(NULL, buf, "COleException", MB_SETFOREGROUND | MB_OK);
} catch(COleDispatchException *e)
{
char buf[1024]; // For the Try...Catch error message.
sprintf(buf,
"COleDispatchException. SCODE: %08lx, Description: \"%s\".",
(long)e->m_wCode,(LPSTR)e->m_strDescription.GetBuffer(512));
::MessageBox(NULL, buf, "COleDispatchException",
MB_SETFOREGROUND | MB_OK);
} catch(...)
{
::MessageBox(NULL, "General Exception caught.", "Catch-All",
MB_SETFOREGROUND | MB_OK);
}
Open the generated XlCpp.cpp and add the following code before the main() function:#include <ole2.h> // OLE2 Definitions// AutoWrap() - Automation helper function...
HRESULT AutoWrap(int autoType, VARIANT *pvResult, IDispatch *pDisp, LPOLESTR ptName, int cArgs...) {
// Begin variable-argument list...
va_list er;
va_start(er, cArgs); if(!pDisp) {
MessageBox(NULL, "NULL IDispatch passed to AutoWrap()", "Error", 0x10010);
_exit(0);
} // Variables used...
DISPPARAMS dp = { NULL, NULL, 0, 0 };
DISPID dispidNamed = DISPID_PROPERTYPUT;
DISPID dispID;
HRESULT hr;
char buf[200];
char szName[200];
// Convert down to ANSI
WideCharToMultiByte(CP_ACP, 0, ptName, -1, szName, 256, NULL, NULL);
// Get DISPID for name passed...
hr = pDisp->GetIDsOfNames(IID_NULL, &ptName, 1, LOCALE_USER_DEFAULT, &dispID);
if(FAILED(hr)) {
sprintf(buf, "IDispatch::GetIDsOfNames(\"%s\") failed w/err 0x%08lx", szName, hr);
MessageBox(NULL, buf, "AutoWrap()", 0x10010);
_exit(0);
return hr;
}
// Allocate memory for arguments...
VARIANT *pArgs = new VARIANT[cArgs+1];
// Extract arguments...
for(int i=0; i<cArgs; i++) {
pArgs[i] = va_arg(er, VARIANT);
}
// Build DISPPARAMS
dp.cArgs = cArgs;
dp.rgvarg = pArgs;
// Handle special-case for property-puts!
if(autoType & DISPATCH_PROPERTYPUT) {
dp.cNamedArgs = 1;
dp.rgdispidNamedArgs = &dispidNamed;
}
// Make the call!
hr = pDisp->Invoke(dispID, IID_NULL, LOCALE_SYSTEM_DEFAULT, autoType, &dp, pvResult, NULL, NULL);
if(FAILED(hr)) {
sprintf(buf, "IDispatch::Invoke(\"%s\"=%08lx) failed w/err 0x%08lx", szName, dispID, hr);
MessageBox(NULL, buf, "AutoWrap()", 0x10010);
_exit(0);
return hr;
}
// End variable-argument section...
va_end(er);
delete [] pArgs;
return hr;
} Inside the main() function, replace the printf() line with the following code: // Initialize COM for this thread...
CoInitialize(NULL); // Get CLSID for our server...
CLSID clsid;
HRESULT hr = CLSIDFromProgID(L"Excel.Application", &clsid); if(FAILED(hr)) { ::MessageBox(NULL, "CLSIDFromProgID() failed", "Error", 0x10010);
return -1;
} // Start server and get IDispatch...
IDispatch *pXlApp;
hr = CoCreateInstance(clsid, NULL, CLSCTX_LOCAL_SERVER, IID_IDispatch, (void **)&pXlApp);
if(FAILED(hr)) {
::MessageBox(NULL, "Excel not registered properly", "Error", 0x10010);
return -2;
} // Make it visible (i.e. app.visible = 1)
{ VARIANT x;
x.vt = VT_I4;
x.lVal = 1;
AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXlApp, L"Visible", 1, x);
} // Get Workbooks collection
IDispatch *pXlBooks;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlApp, L"Workbooks", 0);
pXlBooks = result.pdispVal;
} // Call Workbooks.Add() to get a new workbook...
IDispatch *pXlBook;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlBooks, L"Add", 0);
pXlBook = result.pdispVal;
} // Create a 15x15 safearray of variants...
VARIANT arr;
arr.vt = VT_ARRAY | VT_VARIANT;
{
SAFEARRAYBOUND sab[2];
sab[0].lLbound = 1; sab[0].cElements = 15;
sab[1].lLbound = 1; sab[1].cElements = 15;
arr.parray = SafeArrayCreate(VT_VARIANT, 2, sab);
} // Fill safearray with some values...
for(int i=1; i<=15; i++) {
for(int j=1; j<=15; j++) {
// Create entry value for (i,j)
VARIANT tmp;
tmp.vt = VT_I4;
tmp.lVal = i*j;
// Add to safearray...
long indices[] = {i,j};
SafeArrayPutElement(arr.parray, indices, (void *)&tmp);
}
} // Get ActiveSheet object
IDispatch *pXlSheet;
{
VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlApp, L"ActiveSheet", 0);
pXlSheet = result.pdispVal;
} // Get Range object for the Range A1:O15...
IDispatch *pXlRange;
{
VARIANT parm;
parm.vt = VT_BSTR;
parm.bstrVal = ::SysAllocString(L"A1:O15"); VARIANT result;
VariantInit(&result);
AutoWrap(DISPATCH_PROPERTYGET, &result, pXlSheet, L"Range", 1, parm);
VariantClear(&parm); pXlRange = result.pdispVal;
} // Set range with our safearray...
AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXlRange, L"Value", 1, arr); // Wait for user...
::MessageBox(NULL, "All done.", "Notice", 0x10000); // Set .Saved property of workbook to TRUE so we aren't prompted
// to save when we tell Excel to quit...
{
VARIANT x;
x.vt = VT_I4;
x.lVal = 1;
AutoWrap(DISPATCH_PROPERTYPUT, NULL, pXlBook, L"Saved", 1, x);
} // Tell Excel to quit (i.e. App.Quit)
AutoWrap(DISPATCH_METHOD, NULL, pXlApp, L"Quit", 0); // Release references...
pXlRange->Release();
pXlSheet->Release();
pXlBook->Release();
pXlBooks->Release();
pXlApp->Release();
VariantClear(&arr); // Uninitialize COM for this thread...
CoUninitialize();
谢谢。
老大,这个为什么不能自动保存,点击消息框后,Excel被关闭了。