void CExcelsaveDlg::OnOK()
{
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.
COleVariant
covTrue((short)TRUE),
covFalse((short)FALSE),
covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
if(!app.CreateDispatch("Excel.Application"))
{
AfxMessageBox("Couldn't CreateDispatch() for Excel");
return;
}
app.SetVisible(TRUE);
lpDisp = app.GetWorkbooks(); // Get an IDispatch pointer.
ASSERT(lpDisp);
books.AttachDispatch(lpDisp); // Attach the IDispatch pointer
// to the books object.
lpDisp = books.Open("C:\\temp\\book1.xls", // Test.xls is a workbook.
covOptional, covOptional, covOptional, covOptional, covOptional,
covOptional, covOptional, covOptional, covOptional, covOptional,
covOptional, covOptional , covOptional, covOptional); // Return Workbook's IDispatch
// pointer.
book.AttachDispatch( lpDisp );
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)) );
ASSERT(lpDisp);
sheet.AttachDispatch(lpDisp);
lpDisp = sheet.GetRange(COleVariant("B2"), COleVariant("b6"));
range.AttachDispatch(lpDisp);
range.SetNumberFormat(COleVariant("@"));
range.SetItem(COleVariant((long)(1)),COleVariant((long)(1)),COleVariant(LPCTSTR("000666")));
//void _Worksheet::SaveAs(LPCTSTR Filename, const VARIANT& FileFormat,
// const VARIANT& Password, const VARIANT& WriteResPassword,
// const VARIANT& ReadOnlyRecommended, const VARIANT& CreateBackup,
// const VARIANT& AddToMru, const VARIANT& TextCodepage,
// const VARIANT& TextVisualLayout, const VARIANT& Local)
sheet.SaveAs(_T("c:\\temp\\book2.xls"),covOptional,
covOptional, covOptional, covOptional, covOptional,
covOptional, covOptional, covOptional, covOptional);
// 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);
}
CDialog::OnOK();
}
{
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.
COleVariant
covTrue((short)TRUE),
covFalse((short)FALSE),
covOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR);
if(!app.CreateDispatch("Excel.Application"))
{
AfxMessageBox("Couldn't CreateDispatch() for Excel");
return;
}
app.SetVisible(TRUE);
lpDisp = app.GetWorkbooks(); // Get an IDispatch pointer.
ASSERT(lpDisp);
books.AttachDispatch(lpDisp); // Attach the IDispatch pointer
// to the books object.
lpDisp = books.Open("C:\\temp\\book1.xls", // Test.xls is a workbook.
covOptional, covOptional, covOptional, covOptional, covOptional,
covOptional, covOptional, covOptional, covOptional, covOptional,
covOptional, covOptional , covOptional, covOptional); // Return Workbook's IDispatch
// pointer.
book.AttachDispatch( lpDisp );
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)) );
ASSERT(lpDisp);
sheet.AttachDispatch(lpDisp);
lpDisp = sheet.GetRange(COleVariant("B2"), COleVariant("b6"));
range.AttachDispatch(lpDisp);
range.SetNumberFormat(COleVariant("@"));
range.SetItem(COleVariant((long)(1)),COleVariant((long)(1)),COleVariant(LPCTSTR("000666")));
//void _Worksheet::SaveAs(LPCTSTR Filename, const VARIANT& FileFormat,
// const VARIANT& Password, const VARIANT& WriteResPassword,
// const VARIANT& ReadOnlyRecommended, const VARIANT& CreateBackup,
// const VARIANT& AddToMru, const VARIANT& TextCodepage,
// const VARIANT& TextVisualLayout, const VARIANT& Local)
sheet.SaveAs(_T("c:\\temp\\book2.xls"),covOptional,
covOptional, covOptional, covOptional, covOptional,
covOptional, covOptional, covOptional, covOptional);
// 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);
}
CDialog::OnOK();
}
1. HOWTO: Create an Automation Project Using MFC and a Type Library
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q178749
2. HOWTO: Use MFC to Automate Excel and Fill a Range with an Array (Q186120)
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q186120注意里面还有好多例子和要注意的:
REFERENCES
For more information about automating Microsoft Excel using MFC, please see the following articles in the Microsoft Knowledge Base: Q186122 HOWTO: Use MFC to Automate Excel and Obtain an Array from a Range Q184663 HOWTO: Embed and Automate a Microsoft Excel Worksheet with MFC Q179706 Use MFC to Automate Excel and Create/Format a New Workbook Q178781 HOWTO: Automate Excel Using MFC and Worksheet Functions Q178783 HOWTO: Use MFC to Create a Microsoft Excel Chart
The information in this article applies to:
Microsoft Visual C++, 32-bit Enterprise Edition, versions 5.0 , 6.0
Microsoft Visual C++, 32-bit Professional Edition, versions 5.0 , 6.0
Microsoft Visual C++, 32-bit Learning Edition, version 6.0
Microsoft Excel 2002
Microsoft Excel 2000
Microsoft Excel 97 for Windows
--------------------------------------------------------------------------------
SUMMARY
This article demonstrates how to automate Microsoft Excel and fill a multi-cell range with an array of values. MORE INFORMATION
To fill a multi-cell range without populating the cells one-by-one, you must create a two-dimensional variant SAFEARRAY which you pass to Excel by calling the SetValue function for the Range object. The following steps illustrate this process. Notes for Automating Microsoft Excel 2000 and 2002
The sample code in this article uses class wrappers generated from the Excel 97 object library (Excel 8.olb). With slight modification, this code can be applied to an Automation client that uses class wrappers for Excel 2000 (Excel9.olb) or Excel 2002 (Excel.olb). For additional information about using the sample code described in this article with the Microsoft Excel 2000 or 2002 type library, please click the article number below to view it in the Microsoft Knowledge Base:
Q224925 INFO: Type Libraries for Office May Change With New Release
Steps to Create Project
Follow steps 1 through 12 in the following article in the Microsoft Knowledge Base to create a sample project that uses the IDispatch interfaces and member functions defined in the Excel8.olb type library:
Q178749 HOWTO: Create an Automation Project Using MFC and a Type Library
To the dialog box created in steps 4 and 5 of the parent article Q178749 , add the following controls with properties as specified. Also add the corresponding member variables:
Member Member
Control Name Variable Type Variable Name
-----------------------------------------------------------------
Edit IDC_STARTINGCELL m_sStartingCell CString
Edit IDC_NUMROWS m_iNumRows short
Edit IDC_NUMCOLS m_iNumCols short
CheckBox IDC_STRING m_bFillWithStrings BOOL
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.
Sample Code
// OLE Variant for Optional.
COleVariant VOptional((long)DISP_E_PARAMNOTFOUND, VT_ERROR); _Application objApp; _Workbook objBook;
Workbooks objBooks;
Worksheets objSheets;
_Worksheet objSheet;
Range range; if(!UpdateData(TRUE))
{
return;
} // Instantiate Excel and start a new workbook.
objApp.CreateDispatch("Excel.Application");
objBooks = objApp.GetWorkbooks();
objBook = objBooks.Add(VOptional);
objSheets = objBook.GetWorksheets();
objSheet = objSheets.GetItem(COleVariant((short)1)); //Get the range where the starting cell has the address
//m_sStartingCell and it's dimensions are m_iNumRows x m_iNumCols.
range = objSheet.GetRange(COleVariant(m_sStartingCell),
COleVariant(m_sStartingCell));
range = range.GetResize(COleVariant(m_iNumRows),
COleVariant(m_iNumCols)); //*** Fill the range with an array of values. //Create the SAFEARRAY.
COleSafeArray saRet;
DWORD numElements[2];
numElements[0]= m_iNumRows; //Number of rows in the range.
numElements[1]= m_iNumCols; //Number of columns in the range. if(m_bFillWithStrings)
{
saRet.Create(VT_BSTR, 2, numElements);
}
else
{
saRet.Create(VT_R8, 2, numElements);
} //Fill the SAFEARRAY.
long index[2];
long iRow;
long iCol; for(iRow=0;iRow<=m_iNumRows-1;iRow++)
{
for(iCol=0;iCol<=m_iNumCols-1;iCol++)
{
index[0] = iRow;
index[1] = iCol;
if(m_bFillWithStrings) //Fill with Strings.
{
VARIANT v;
CString s;
VariantInit(&v);
v.vt = VT_BSTR;
s.Format("r%dc%d", iRow, iCol);
v.bstrVal = s.AllocSysString();
saRet.PutElement(index, v.bstrVal);
SysFreeString(v.bstrVal);
VariantClear(&v);
}
else //Fill with Numbers.
{
double d;
d = (iRow*1000) + iCol;
saRet.PutElement(index, &d);
}
}
} //Set the range value to the SAFEARRAY.
range.SetValue(COleVariant(saRet));
saRet.Detach(); //Return control of Excel to the user.
objApp.SetVisible(TRUE);
objApp.SetUserControl(TRUE);
Compile and Run the project.
Specify the following values for the controls on the dialog box:
Control Contents
------------------------------
IDC_STARTINGCELL A1
IDC_NUMROWS 10
IDC_NUMCOLS 5
IDC_STRING True
Click OK. Results: A new workbook is generated and cells A1:E10 of the first worksheet are populated with string values.
Specify the following values for the controls on the dialog box:
Control Contents
------------------------------
IDC_STARTINGCELL C3
IDC_NUMROWS 2
IDC_NUMCOLS 9
IDC_STRING False
Click OK. Results: A new workbook is generated and cells C3:K4 of the first worksheet are populated with numeric values. REFERENCES
For more information about automating Microsoft Excel using MFC, please see the following articles in the Microsoft Knowledge Base: Q186122 HOWTO: Use MFC to Automate Excel and Obtain an Array from a Range Q184663 HOWTO: Embed and Automate a Microsoft Excel Worksheet with MFC Q179706 Use MFC to Automate Excel and Create/Format a New Workbook Q178781 HOWTO: Automate Excel Using MFC and Worksheet Functions Q178783 HOWTO: Use MFC to Create a Microsoft Excel Chart
--------------------------------------------------------------------------------
Published May 26 1998 11:16AM Issue Type kbhowto
Last Modifed May 8 2001 3:48PM Additional Query Words
Keywords kbcode kbinterop kbole kbAutomation kbExcel kbMFC kbVC500 kbVC600 kbGrpDSO kbDSupport --------------------------------------------------------------------------------