如何自动化 Excel 使用 MFC 和工作表函数

步骤来创建项目

1.	请按步骤 1 通过以下 Microsoft 知识库文章文章要创建示例项目使用 IDispatch 接口和 Excel8.olb, Excel 9 .olb 或 Excel.exe 对于 Excel 2002 和 Excel 2003 类型库中定义成员函数中 12： 178749 (http://support.microsoft.com/kb/178749/EN-US/) 如何创建自动化项目使用 MFC 和类型库
2.	在添加到 AutoProjectDlg.cpp, 顶部添加以下行： #include "excel8.h" 如果您是自动化 Excel 2000, 包括 excel9.h。 如果您是自动化 Excel 2002 或 Excel 2003, 包括 excel.h
3.	将以下代码添加到 AutoProjectDLG.cpp 文件中 CAutoProjectDlg::OnRun()： 示例代码： 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 must provide 16 arguments. // However in Excel 2003 you must provide 15 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 2000 range.SetValue2(COleVariant("1/25/98")); // Excel 2002 and Excel 2003 // 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 2000 range.SetValue2(COleVariant("11/15/99")); // Excel 2002 and Excel 2003 // 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 2000 range.SetValue2(COleVariant("2")); // Excel 2002 and Excel 2003 // 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 2000 range.SetValue2(COleVariant("1")); // Excel 2002 and Excel 2003 // 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); }