如何使用 MFC 创建 Excel 图表

文章翻译 文章翻译
文章编号: 178783 - 查看本文应用于的产品
展开全部 | 关闭全部

本文内容

概要

本文讨论了如何使用 4.2 版安装与 Microsoft Visual c + + 版本 5.0 和 6.0 Microsoft 基础类 (MFC) 库的自动完成 Microsoft Excel,以便它将用数据填充工作表,并创建图表。

更多信息

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

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

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

创建项目的步骤

  1. 在 Microsoft Excel 中创建名为 Test.xls 一个新的工作簿,并将其保存在根目录下的驱动器 c。
  2. 请按照步骤 1 至 12 中,以下 Microsoft 知识库文章创建示例项目使用 IDispatch 接口和 Excel 类型库中定义的成员函数的步骤操作:
    178749如何创建自动化项目使用 MFC 和类型库
  3. 在 AutoProjectDlg.cpp 顶部添加以下行:
          #include "excel8.h" // excel9.h for Excel 2000, excel.h for Excel 2002
    					
  4. 将下面的代码添加到 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 & 2000
    
    range.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 2000
    
    range.SetValue2(COleVariant("April")); // Excel 2002
    
    
           lpDisp = sheet.GetRange(COleVariant("B4"), COleVariant("B4"));
           ASSERT(lpDisp);
           range.AttachDispatch(lpDisp);
           range.SetValue(COleVariant("8")); // Excel 97 & Excel 2000
    
    range.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);
          }
    					
  5. 您可能需要修改 CAutoProjectDlg::OnRun() 以指示您的工作簿 Test.xls 为正确的路径中的代码。在下面的行中引用该工作簿:
          lpDisp = books.open("C:\\Test", . . .);
    					

参考

有关自动化的 Office 应用程序的其他信息,请单击下面文章编号,以查看 Microsoft 知识库中相应的文章:
222101如何查找和使用 Office 对象模型文档

属性

文章编号: 178783 - 最后修改: 2007年1月22日 - 修订: 4.1
这篇文章中的信息适用于:
  • Microsoft Foundation Class Library 4.2?当用于
    • Microsoft Visual C++ 5.0 标准版
    • Microsoft Visual C++ 6.0 Service Pack 5
    • Microsoft Excel 97 标准版
  • Microsoft Office XP Developer Edition
  • Microsoft Office 2000 Developer Edition
  • Microsoft Excel 2002 标准版
关键字:?
kbmt kbautomation kbhowto kbinterop KB178783 KbMtzh
机器翻译
注意:这篇文章是由无人工介入的微软自动的机器翻译软件翻译完成。微软很高兴能同时提供给您由人工翻译的和由机器翻译的文章, 以使您能使用您的语言访问所有的知识库文章。然而由机器翻译的文章并不总是完美的。它可能存在词汇,语法或文法的问题,就像是一个外国人在说中文时总是可能犯这样的错误。虽然我们经常升级机器翻译软件以提高翻译质量,但是我们不保证机器翻译的正确度,也不对由于内容的误译或者客户对它的错误使用所引起的任何直接的, 或间接的可能的问题负责。
点击这里察看该文章的英文版: 178783
Microsoft和/或其各供应商对于为任何目的而在本服务器上发布的文件及有关图形所含信息的适用性,不作任何声明。 所有该等文件及有关图形均"依样"提供,而不带任何性质的保证。Microsoft和/或其各供应商特此声明,对所有与该等信息有关的保证和条件不负任何责任,该等保证和条件包括关于适销性、符合特定用途、所有权和非侵权的所有默示保证和条件。在任何情况下,在由于使用或运行本服务器上的信息所引起的或与该等使用或运行有关的诉讼中,Microsoft和/或其各供应商就因丧失使用、数据或利润所导致的任何特别的、间接的、衍生性的损害或任何因使用而丧失所导致的之损害、数据或利润不负任何责任。

提供反馈

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com