使用MFC和Visual C++自动化Excel操作指南

How to automate Excel from MFC and Visual C++ 2005 or Visual C++ .NET to fill or obtain data in a range using arrays 

Article ID: 308407 - View products that this article applies to.
This article was previously published under Q308407
For a Microsoft C# .NET version of this article, see 302096
For a Microsoft Visual Basic .NET version of this article, see 302094

Note Microsoft Visual C++ 2005, Microsoft Visual C++ .NET 2003, and Microsoft Visual C++ .NET 2002 support both the managed code model that is provided by the Microsoft .NET Framework and the unmanaged native Microsoft Windows code model. The information in this article applies only to unmanaged Visual C++ code.

SUMMARY

This step-by-step article demonstrates how to automate Microsoft Excel from Visual C++ 2005 or Visual C++ .NET to fill and retrieve values in a multi-cell range by using arrays.
Create an Automation Client for Excel
To fill a multi-cell range without populating cells one at a time, you can set the Value property of a Range object to a two-dimensional array. Likewise, you can retrieve a two-dimensional array of values for multiple cells at once by using the Value property. The following steps demonstrate this process for both setting and retrieving data using two-dimensional arrays. 1.Follow the steps in the "Create an Automation Client" section of the following Microsoft Knowledge Base article to create a basic Automation client: 
307473

 How To Use a Type Library for Office Automation from Visual C++ .NET In step 3, add a second button and a check box to the form. Change the ID of the button to IDC_GETVALUES and the caption to Get Values. Change the ID of the check box to IDC_CHECK and the caption to Fill with Strings.

 In step 4 of the article, select "Microsoft Excel 10.0 Object Library" if you are automating Excel 2002 from Office XP. The default location for Excel 2002 is C:\Program Files\Microsoft Office\Office10\Excel.exe. Or, select "Microsoft Excel 11.0 Object Library" if you are automating Microsoft Office Excel 2003. The default location for Excel 2003 is C:\Program Files\Microsoft Office\Office11\Excel.exe. Select the following Microsoft Excel interfaces: 

?_Application
?_Workbook
?_Worksheet
?Range
?Workbooks
?Worksheets

In step 6, add the following #include statements directly after the #pragma once directive in Autoprojectdlg.h:

#include "CApplication.h"

#include "CRange.h"
#include "CWorkbook.h"
#include "CWorkbooks.h"
#include "CWorksheet.h"
#include "CWorksheets.h"


2. Add the following two public member variables to the CAutoProjectDlg class:

CApplication oExcel;
CWorkbook oBook;


3.On your dialog box, right-click IDC_CHECK and select Add Variable. Name the variable m_bFillWithStrings and click Finish.
4.On your dialog box, double-click Run and replace the following code

void CAutoProjectDlg::OnBnClickedRun()
{
// TODO: Add your control notification handler code here
}

with: 

void CAutoProjectDlg::OnBnClickedRun()
{
CWorkbooks oBooks;
CWorksheets oSheets;
CWorksheet oSheet;
CRange oRange;
COleVariant covOptional(DISP_E_PARAMNOTFOUND,VT_ERROR);