The information in this article applies to:
- Microsoft Visual C++, 32-bit Editions, version 5.0
- Microsoft Excel 97 for Windows
- The Microsoft Foundation Classes (MFC)
SUMMARY
This article shows how to automate Microsoft Excel 97 using the Microsoft
Foundation Class (MFC) library, version 4.2 (installed with Microsoft
Visual C++ version 5.0).
The article describes a technique for using OLE automation to create/format
a Microsoft Excel workbook; it introduces several methods/properties (from
the Microsoft Excel type library) for adding data to the worksheet and
formatting the worksheet.
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 type library. The primary benefit of
this article, however, comes from reading and understanding the code in the
example so that you can modify the example or write your own code to
automate Microsoft Excel 97 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 type library:
ARTICLE-ID: Q178749
TITLE : HOWTO: Create an Automation Project Using MFC and a Type
Library
- At the top of the AutoProjectDlg.cpp file, add the following line:
#include "excel8.h"
- Add the following code to CAutoProjectDlg::OnRun() in the
AutoProjectDLG.cpp file.
// Commonly used OLE variants.
COleVariant
covTrue((short)TRUE),
covFalse((short)FALSE),
covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
_Application app;
Workbooks books;
_Workbook book;
Worksheets sheets;
_Worksheet sheet;
Range range;
Font font;
Range cols;
// Start Excel and get Application object.
if(!app.CreateDispatch("Excel.Application"))
{
AfxMessageBox("Couldn't start Excel and get Application object.");
return;
}
//Get a new workbook.
books = app.GetWorkbooks();
book = books.Add (covOptional);
//Get the first sheet.
sheets =book.GetSheets();
sheet = sheets.GetItem(COleVariant((short)1));
//Fill cells A1, B1, C1, and D1 one cell at a time with "headers".
range = sheet.GetRange(COleVariant("A1"),COleVariant("A1"));
range.SetValue(COleVariant("First Name"));
range = sheet.GetRange(COleVariant("B1"),COleVariant("B1"));
range.SetValue(COleVariant("Last Name"));
range = sheet.GetRange(COleVariant("C1"),COleVariant("C1"));
range.SetValue(COleVariant("Full Name"));
range = sheet.GetRange(COleVariant("D1"),COleVariant("D1"));
range.SetValue(COleVariant("Salary"));
//Format A1:D1 as bold, vertical alignment = center.
range = sheet.GetRange(COleVariant("A1"), COleVariant("D1"));
font = range.GetFont();
font.SetBold(covTrue);
range.SetVerticalAlignment(
COleVariant((short)-4108)); //xlVAlignCenter = -4108
//Fill A2:B6 with an array of values (First & Last Names).
{
COleSafeArray saRet;
DWORD numElements[]={5,2}; //5x2 element array
saRet.Create(VT_BSTR, 2, numElements);
//Fill the 5x2 SafeArray with the following data:
// John Smith
// Tom Brown
// Sue Thomas
// Jane Jones
// Adam Johnson
FillSafeArray(L"John", 0, 0, &saRet);
FillSafeArray(L"Smith", 0, 1, &saRet);
FillSafeArray(L"Tom", 1, 0, &saRet);
FillSafeArray(L"Brown", 1, 1, &saRet);
FillSafeArray(L"Sue", 2, 0, &saRet);
FillSafeArray(L"Thomas", 2, 1, &saRet);
FillSafeArray(L"Jane", 3, 0, &saRet);
FillSafeArray(L"Jones", 3, 1, &saRet);
FillSafeArray(L"Adam", 4, 0, &saRet);
FillSafeArray(L"Johnson", 4, 1, &saRet);
range = sheet.GetRange(COleVariant("A2"), COleVariant("B6"));
range.SetValue(COleVariant(saRet));
saRet.Detach();
}
//Fill C2:C6 with a relative formula (=A2 & " " & B2).
range = sheet.GetRange(COleVariant("C2"), COleVariant("C6"));
range.SetFormula(COleVariant("=A2 & \" \" & B2"));
//Fill D2:D6 with a formula(=RAND()*100000) and apply a number
//format.
range = sheet.GetRange(COleVariant("D2"), COleVariant("D6"));
range.SetFormula(COleVariant("=RAND()*100000"));
range.SetNumberFormat(COleVariant("$0.00"));
//AutoFit columns A:D.
range = sheet.GetRange(COleVariant("A1"), COleVariant("D1"));
cols = range.GetEntireColumn();
cols.AutoFit();
//Manipulate a variable number of columns for Quarterly Sales Data.
{
short NumQtrs;
CString msg;
Range resizedrange;
Interior interior;
Borders borders;
//Determine how many quarters to display data for.
for(NumQtrs=1;NumQtrs<=3;NumQtrs++)
{
msg.Format("Enter sales data for %d quarter(s)?", NumQtrs);
if(AfxMessageBox(msg,MB_YESNO)==IDYES)
{
break;
}
}
msg.Format("Displaying data for %d quarters.", NumQtrs);
AfxMessageBox(msg);
//Starting at E1, fill headers for the number of columns selected.
range = sheet.GetRange(COleVariant("E1"), COleVariant("E1"));
resizedrange = range.GetResize(covOptional, COleVariant(NumQtrs));
resizedrange.SetFormula(
COleVariant("=\"Q\" & COLUMN()-4 & CHAR(10) & \"Sales\""));
//Change the Orientation and WrapText properties for the headers.
resizedrange.SetOrientation(COleVariant((short)38));
resizedrange.SetWrapText(covTrue);
//Fill the interior color of the headers.
interior = resizedrange.GetInterior();
interior.SetColorIndex(COleVariant((short)36));
//Fill the columns with a formula and apply a number format.
range = sheet.GetRange(COleVariant("E2"), COleVariant("E6"));
resizedrange = range.GetResize(covOptional, COleVariant(NumQtrs));
resizedrange.SetFormula(COleVariant("=RAND()*100"));
resizedrange.SetNumberFormat(COleVariant("$0.00"));
//Apply borders to the Sales data and headers.
range = sheet.GetRange(COleVariant("E1"), COleVariant("E6"));
resizedrange= range.GetResize(covOptional, COleVariant(NumQtrs));
borders = resizedrange.GetBorders();
borders.SetWeight(COleVariant((short)2)); //xlThin = 2
//Add a Totals formula for the Quarterly sales data and apply a
//border.
range = sheet.GetRange(COleVariant("E8"), COleVariant("E8"));
resizedrange = range.GetResize(covOptional, COleVariant(NumQtrs));
resizedrange.SetFormula(COleVariant("=SUM(E2:E6)"));
borders = resizedrange.GetBorders();
{
Border bottomborder;
bottomborder = borders.GetItem((long)9);
bottomborder.SetLineStyle(
COleVariant((short)-4119)); //xlDouble = -4119
bottomborder.SetWeight(
COleVariant((short)4)); //xlThick = 4
}
}
//Make the application visible and give the user control of
//Microsoft Excel.
app.SetVisible(TRUE);
app.SetUserControl(TRUE);
- Add the following function to AutoProjectDLG.cpp, locating it somewhere
before CAutoProjectDlg::OnRun():
void FillSafeArray(OLECHAR FAR* sz, int iRow, int iCol,
COleSafeArray* sa)
{
VARIANT v;
long index[2];
index[0] = iRow;
index[1] = iCol;
VariantInit(&v);
v.vt = VT_BSTR;
v.bstrVal = SysAllocString(sz);
sa->PutElement(index, v.bstrVal);
SysFreeString(v.bstrVal);
VariantClear(&v);
}
- In Developer Studio, run the application. When the dialog box appears
(the example creates a dialog-based application), click the Run button.
|