excel

来源：互联网发布：ovid数据库入口编辑：程序博客网时间：2024/05/29 04:33

可以将本文中的代码复制到消息处理函数，MFC.cpp 文件中定义的事件。但是，代码的目的是为了说明使用 IDispatch 接口和 Excel 类型库中定义的成员函数的过程。主要优点来自于阅读和理解代码，以便您可以修改该的示例或自动执行 Microsoft Excel 97、 Excel 2000 或 Excel 2002 从头开始编写代码。

自动完成 Microsoft Excel 2000 及更高版本的注释：

某些方法和属性已更改 Microsoft Excel 2000 及更高版本。有关使用 Microsoft Excel 2000 和更高版本的类型库与本文所述将示例代码的其他信息，请参阅 Microsoft 知识库中下面的文章：

224925 信息: 为 Office 类型库可能会更改与新版本

回到顶端

创建项目的步骤

在 Microsoft Excel 中创建名为 Test.xls 一个新的工作簿，并将其保存在根目录下的驱动器 c。
请按照步骤 1 至 12 中，以下 Microsoft 知识库文章创建示例项目使用 IDispatch 接口和 Excel 类型库中定义的成员函数的步骤操作：
178749 如何创建自动化项目使用 MFC 和类型库

在 AutoProjectDlg.cpp 顶部添加以下行：

      #include "excel8.h" // excel9.h for Excel 2000, excel.h for Excel 2002

将下面的代码添加到 CAutoProjectDlg::OnRun() AutoProjectDLG.cpp 文件中。

回到顶端

示例代码

      try      {       _Application app;  // app is the Excel _Application object.       _Workbook book;       _Worksheet sheet;       _Chart chart;       Workbooks books;       Worksheets sheets;       Range range;       ChartObjects chartobjects;       Charts charts;       LPDISPATCH lpDisp;       // Common OLE variants. These are easy variants to use for       // calling arguments.       COleVariant                  covTrue((short)TRUE),                  covFalse((short)FALSE),                  covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);       // Start Excel and get the Application object.       if(!app.CreateDispatch("Excel.Application"))       {        AfxMessageBox(             "Couldn't start Excel and get an application 0bject");        return;       }       // Set visible.       app.SetVisible(TRUE);       // Get Workbooks collection.       lpDisp = app.GetWorkbooks();  // Get an IDispatch pointer.       ASSERT(lpDisp);       books.AttachDispatch( lpDisp );  // Attach the IDispatch pointer                                        // to the books object.          // Open a workbook.          lpDisp = books.Open("C:\\Test",                      covOptional, covOptional, covOptional, covOptional,                      covOptional, covOptional, covOptional, covOptional,                      covOptional, covOptional, covOptional, covOptional,                      covOptional, covOptional, covOptional); // Excel 2000 requires only 13 arguments          ASSERT(lpDisp);  // It should have worked.       // Attach to a Workbook object.       book.AttachDispatch( lpDisp );  // Attach the IDispatch pointer                                       // to the Workbook object.       // Get sheets.       lpDisp = book.GetSheets();       ASSERT(lpDisp);       sheets.AttachDispatch(lpDisp);       lpDisp = sheets.GetItem( COleVariant((short)(1)) );       ASSERT(lpDisp);       // Attach the lpDisp pointer to a Worksheet object.       sheet.AttachDispatch(lpDisp);       lpDisp = sheet.GetRange(COleVariant("A1"), COleVariant("W40"));                               // The range is from A1 to W40.       ASSERT(lpDisp);       range.AttachDispatch(lpDisp);  // Attach the IDispatch pointer                                      // to the range object.       range.Clear();  // Could be ClearContents().       ::Sleep(500); // So you can see it happen.       lpDisp = sheet.GetRange(COleVariant("A3"), COleVariant("A3"));                                                 // From A3 to A3.       ASSERT(lpDisp);       range.AttachDispatch(lpDisp);  // Attach the IDispatch pointer                                      // the range object.       range.SetValue(COleVariant("March")); // Excel 97 & Excel 2000.range.SetValue2(COleVariant("March")); // Insert March into range.       // Following is a series of repetitive steps to populate the       // worksheet's cells with a series of Months and values to be       // used in the Chart object, which is yet to be constructed.       lpDisp = sheet.GetRange(COleVariant("B3"), COleVariant("B3"));       ASSERT(lpDisp);       range.AttachDispatch(lpDisp);       range.SetValue(COleVariant("12")); // 97 & 2000range.SetValue2(COleVariant("12")); //  Value for March.       lpDisp = sheet.GetRange(COleVariant("A4"), COleVariant("A4"));            // Months will be in column A, values in column B.       ASSERT(lpDisp);       range.AttachDispatch(lpDisp);       range.SetValue(COleVariant("April"));// Excel 97 & Excel 2000range.SetValue2(COleVariant("April")); // Excel 2002       lpDisp = sheet.GetRange(COleVariant("B4"), COleVariant("B4"));       ASSERT(lpDisp);       range.AttachDispatch(lpDisp);       range.SetValue(COleVariant("8")); // Excel 97 & Excel 2000range.SetValue2(COleVariant("8")); // Excel 2002       lpDisp = sheet.GetRange(COleVariant("A5"), COleVariant("A5"));       ASSERT(lpDisp);       range.AttachDispatch(lpDisp);       range.SetValue(COleVariant("May"));range.SetValue2(COleVariant("May"));       lpDisp = sheet.GetRange(COleVariant("B5"), COleVariant("B5"));       ASSERT(lpDisp);       range.AttachDispatch(lpDisp);       range.SetValue(COleVariant("2"));range.SetValue2(COleVariant("2"));       lpDisp = sheet.GetRange(COleVariant("A6"), COleVariant("A6"));       ASSERT(lpDisp);       range.AttachDispatch(lpDisp);       range.SetValue(COleVariant("June"));range.SetValue2(COleVariant("June"));       lpDisp = sheet.GetRange(COleVariant("B6"), COleVariant("B6"));       ASSERT(lpDisp);       range.AttachDispatch(lpDisp);       range.SetValue(COleVariant("11"));range.SetValue2(COleVariant("11"));       lpDisp = sheet.GetRange(COleVariant("A7"), COleVariant("A7"));       ASSERT(lpDisp);       range.AttachDispatch(lpDisp);       range.SetValue(COleVariant("July"));range.SetValue2(COleVariant("July"));       lpDisp = sheet.GetRange(COleVariant("B7"), COleVariant("B7"));       ASSERT(lpDisp);       range.AttachDispatch(lpDisp);       range.SetValue(COleVariant("16"));range.SetValue2(COleVariant("16"));       // The cells are populated. To start the chart,       // declare some long variables and site the chart.       long left, top, width, height;       left = 100;       top = 10;       width = 350;       height = 250;       lpDisp = sheet.ChartObjects(covOptional);       ASSERT(lpDisp);       chartobjects.AttachDispatch(lpDisp); // Attach the lpDisp pointer                                  // for ChartObjects to the chartobjects                                  // object.       ChartObject chartobject = chartobjects.Add(left, top, width, height);                                 //defines the rectangle,                                 // adds a new chart at that rectangle and                                 // assigns its object reference to a                                 // ChartObject variable named chartobject       chart.AttachDispatch(chartobject.GetChart()); // GetChart() returns                                         // LPDISPATCH, and this attaches                                          // it to your chart object.       lpDisp = sheet.GetRange(COleVariant("A3"), COleVariant("B7"));                         // The range containing the data to be charted.       ASSERT(lpDisp);       range.AttachDispatch(lpDisp);       VARIANT var; // ChartWizard needs a Variant for the Source range.       var.vt = VT_DISPATCH; // .vt is the usable member of the tagVARIANT                             // Struct. Its value is a union of options.       var.pdispVal = lpDisp; // Assign IDispatch pointer                              // of the Source range to var.       chart.ChartWizard(var,                    // Source.                        COleVariant((short)11),  // Gallery: 3d Column.                        covOptional,             // Format, use default.                        COleVariant((short)1),   // PlotBy: xlRows.                        COleVariant((short)0),   // CategoryLabels.                        COleVariant((short)1),   // SeriesLabels.                        COleVariant((short)TRUE),  // HasLegend.                        COleVariant("Use by Month"),  // Title.                        COleVariant("Month"),    // CategoryTitle.                        COleVariant("Usage in Thousands"),  // ValueTitles.                        covOptional              // ExtraTitle.                        );       // The return is void.       ::Sleep(3000);       chartobject.Delete();  // Removes the first chartobject, sets the       // ChartObjects.Item() count to 0. The next chart will restore the       // item count to 1.       ::Sleep(3000);  // Set the selected range to be erased.       range.Clear();  // Erase the usage data.       // Beginning of chart 2.       lpDisp = sheet.GetRange(COleVariant("B3"), COleVariant("B3"));                                      // From B3 to B3.       ASSERT(lpDisp);       range.AttachDispatch(lpDisp);  // Attach the IDispatch pointer                                      // to the range object.       range.SetValue(COleVariant("Chocolate")); // Insert Chocolate into                                                 // the range object.range.SetValue2(COleVariant("Chocolate")); // Insert Chocolate       // Following is a series of repetitive steps to populate the       // worksheet's cells with a series of Flavors and values to be       // used in the chart object, your second chart.       lpDisp = sheet.GetRange(COleVariant("B4"), COleVariant("B4"));       ASSERT(lpDisp);       range.AttachDispatch(lpDisp);       range.SetValue(COleVariant("12")); // Value for Chocolate.range.SetValue2(COleVariant("12")); // Value for Chocolate.       lpDisp = sheet.GetRange(COleVariant("C3"), COleVariant("C3"));            // Flavors will be in row 3, values in row 4.       ASSERT(lpDisp);       range.AttachDispatch(lpDisp);       range.SetValue(COleVariant("Vanilla"));range.SetValue2(COleVariant("Vanilla"));       lpDisp = sheet.GetRange(COleVariant("C4"), COleVariant("C4"));       ASSERT(lpDisp);       range.AttachDispatch(lpDisp);       range.SetValue(COleVariant("8"));range.SetValue2(COleVariant("8"));       lpDisp = sheet.GetRange(COleVariant("D3"), COleVariant("D3"));       ASSERT(lpDisp);       range.AttachDispatch(lpDisp);       range.SetValue(COleVariant("Orange"));range.SetValue2(COleVariant("Orange"));       lpDisp = sheet.GetRange(COleVariant("D4"), COleVariant("D4"));       ASSERT(lpDisp);       range.AttachDispatch(lpDisp);       range.SetValue(COleVariant("6"));range.SetValue2(COleVariant("6"));       // The cells are populated. To start the chart,       // define the bounds, and site the chart.       left = 250;       top = 40;       width = 300;       height = 300;       lpDisp = sheet.ChartObjects(covOptional);       ASSERT(lpDisp);       chartobjects.AttachDispatch(lpDisp); // Attach the lpDisp pointer                                   // for ChartObjects to the chartobjects                                   // object.       chartobjects.Add(left, top, width, height); // Adds 1 to item count.       //**************************************       lpDisp = chartobjects.Item( COleVariant((short)(1)) );  // It was           // zero, but just added one at a new location,           // with new left, top, width, and height.       ASSERT(lpDisp);       chartobject.AttachDispatch(lpDisp); // Use definition of new chart                                           // site.       chart.AttachDispatch(chartobject.GetChart());       //**************************************       lpDisp = sheet.GetRange(COleVariant("B3"), COleVariant("D4"));                         // Range containing the data to be charted.       ASSERT(lpDisp);       range.AttachDispatch(lpDisp);       var.pdispVal = lpDisp; // Contains IDispatch pointer                              // to the Source range.       chart.ChartWizard(var,                    // Source.                        COleVariant((short)11),  // Gallery = 3D Column.                        covOptional,             // Format, use default.                        COleVariant((short)2),   // PlotBy xlColumns.                        COleVariant((short)0),   // CategoryLabels.                        COleVariant((short)1),   // SeriesLabels.                        COleVariant((short)TRUE),  // HasLegend.                        COleVariant("Use by Flavor"),  // Title.                        COleVariant("Flavor"),    // CategoryTitle.                        COleVariant("Usage in Barrells"),  // ValueTitles.                        covOptional              // ExtraTitle.                        );       // The return is void.       ::Sleep(3000);       //Show the chart in Print Preview.       chart.PrintOut(COleVariant((short)1),     // From (page #).                      COleVariant((short)1),     // To (page #).                      COleVariant((short)1),     // Copies.                      COleVariant((short)TRUE),  // Preview.                      covOptional,               // ActivePrinter.                      covFalse,                  // PrintToFile.                      covFalse                   // Collate.                      covOptional                // PrToFileName // 2002 only                      );       book.SetSaved(TRUE); // Avoids the 'Save changes?' dialog box.       app.Quit(); // Excel departs.       // By default, the pointer references for the objects       // range, book, chart, chartobjects, sheet, and app       // are automatically released when they go out of scope.       // ReleaseDispatch()s are unnecessary.       ::Sleep(1000);       AfxMessageBox("Just executed App.Quit()");      }  // End of processing logic.      catch(COleException *e)      {        char buf[1024];        sprintf(buf, "COleException. SCODE: %08lx.", (long)e->m_sc);        ::MessageBox(NULL, buf, "COleException", MB_SETFOREGROUND | MB_OK);      }      catch(COleDispatchException *e)      {       char buf[1024];       sprintf(buf,               "COleDispatchException. SCODE: %08lx, Description: \"%s\".",               (long)e->m_wCode,               (LPSTR)e->m_strDescription.GetBuffer(1024));       ::MessageBox(NULL, buf, "COleDispatchException",                    MB_SETFOREGROUND | MB_OK);      }      catch(...)      {       ::MessageBox(NULL, "General Exception caught.", "Catch-All",                    MB_SETFOREGROUND | MB_OK);      }

您可能需要修改 CAutoProjectDlg::OnRun() 以指示您的工作簿 Test.xls 为正确的路径中的代码。在下面的行中引用该工作簿：
```
      lpDisp = books.open("C:\\Test", . . .);
```

回到顶端

参考

有关自动化的 Office 应用程序的其他信息，请单击下面文章编号，以查看 Microsoft 知识库中相应的文章：

222101 如何查找和使用 Office 对象模型文档