The Problem Many apps offer an export function. So wouldn′t it be nice to be able to easily save that result as an Excel sheet? ODBC does make this possible, but there′s one little drawback: Using ODBC the usual way there has to be a registered datasource (DSN) in the ODBC manager. This is not very useful because you′d have to install that DSN locally on every machine that should support your export function. The Solution Omiting the DSN tag in the connect string of CDatabase::OpenEx() gives us the opportunity to refer the ODBC-Driver directly using its name so we don′t have to have a DSN registered. This, of course, implies that the name of the ODBC-Driver is exactly known. If you just want to test if a certain driver is present (to show the supported extensions in the CFileOpenDlg for example) just try to CDatabase::OpenEx() it. If it isn′t installed an exception gets thrown. To create and write to that Excel sheet you simply use SQL as shown in the code sample below. What is Needed In order to get the code below going you have to have included have an installed ODBC-driver called "MICROSOFT EXCEL DRIVER (*.XLS)" The Source code // this example creates the Excel file C:\DEMO.XLS, puts in a worksheet with two // columns (one text the other numeric) an appends three no-sense records. // void MyDemo::Put2Excel() { CDatabase database; CString sDriver = "MICROSOFT EXCEL DRIVER (*.XLS)"; // exactly the same name as in the ODBC-Manager CString sExcelFile = "c:\\demo.xls"; // Filename and path for the file to be created CString sSql;
TRY { // Build the creation string for access without DSN
HOWTO: Use MFC to Automate Excel and Create/Format a New Workbook -------------------------------------------------------------------------------- The information in this article applies to:Microsoft Visual C++, 32-bit Editions, versions 5.0, 6.0 Microsoft Excel 2000 The Microsoft Foundation Classes (MFC) Microsoft Office 2000 Developer Microsoft Excel 97 for Windows-------------------------------------------------------------------------------- SUMMARY This article shows how to automate Microsoft Excel 97 or Excel 2000 using the Microsoft Foundation Class (MFC) library, version 4.2 (installed with Microsoft Visual C++ versions 5.0 and 6.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.Notes for Automating Microsoft Excel 2000 Some methods and properties have changed for Microsoft Excel 2000. For additional information about using the sample code described in this article with the Microsoft Excel 2000 type library,, please click the article number below to view it in the Microsoft Knowledge Base: Q224925 INFO: Type Libraries for Office 2000 Have Changed 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: Q178749 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: Sample Code // 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(): Sample Code 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.Additional query words: Excel 8 8.0 XL97 XL8
"xls",NULL,OFN_HIDEREADONLY | OFN_OVERWRITEPROMPT,"Microsoft Excel 97 (*.xls)",this);
if(FileDialog.DoModal()!=IDOK)return;
CString strFile=FileDialog.GetPathName();
if(::PathFileExists(strFile)){
::DeleteFile(strFile);
// if(!::g_FileOperation(FO_DELETE,strFile,NULL,bAborted,this))return;
} CString strSql,strTable;
//Create a Recordset object for the worksheet Supplier.xls
strTable=m_pSet->GetTableName();
strSql.Format("SELECT * INTO %s in '' 'Excel 5.0;database=%s;' FROM (%s)",
m_pSet->GetTableName(),strFile,strTable);
if(!m_strFilter.IsEmpty())
strSql+=(" WHERE "+m_strFilter);
if(!m_strSort.IsEmpty())
strSql+=(" ORDER BY "+m_strSort);
m_pSet->m_pDatabase->Execute(strSql);
用classwizard導入Excel8.olb後,怎麼操作都可以。路徑是:C:\Program Files\Microsoft Office\Office\excel8.olb.然後#include"excel8.h".有問題可以和我聯系:[email protected].
Many apps offer an export function. So wouldn′t it be nice to be able to easily save that result as an Excel sheet? ODBC does make this possible, but there′s one little drawback: Using ODBC the usual way there has to be a registered datasource (DSN) in the ODBC manager. This is not very useful because you′d have to install that DSN locally on every machine that should support your export function. The Solution
Omiting the DSN tag in the connect string of CDatabase::OpenEx() gives us the opportunity to refer the ODBC-Driver directly using its name so we don′t have to have a DSN registered. This, of course, implies that the name of the ODBC-Driver is exactly known. If you just want to test if a certain driver is present (to show the supported extensions in the CFileOpenDlg for example) just try to CDatabase::OpenEx() it. If it isn′t installed an exception gets thrown. To create and write to that Excel sheet you simply use SQL as shown in the code sample below. What is Needed
In order to get the code below going you have to have included
have an installed ODBC-driver called "MICROSOFT EXCEL DRIVER (*.XLS)"
The Source code
// this example creates the Excel file C:\DEMO.XLS, puts in a worksheet with two
// columns (one text the other numeric) an appends three no-sense records.
//
void MyDemo::Put2Excel()
{
CDatabase database;
CString sDriver = "MICROSOFT EXCEL DRIVER (*.XLS)"; // exactly the same name as in the ODBC-Manager
CString sExcelFile = "c:\\demo.xls"; // Filename and path for the file to be created
CString sSql;
TRY
{
// Build the creation string for access without DSN
sSql.Format("DRIVER={%s};DSN='';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=\"%s\";DBQ=%s",
sDriver, sExcelFile, sExcelFile); // Create the database (i.e. Excel sheet)
if( database.OpenEx(sSql,CDatabase::noOdbcDialog) )
{
// Create table structure
sSql = "CREATE TABLE demo (Name TEXT,Age NUMBER)";
database.ExecuteSQL(sSql); // Insert data
sSql = "INSERT INTO demo (Name,Age) VALUES ('Bruno Brutalinsky',45)";
database.ExecuteSQL(sSql); sSql = "INSERT INTO demo (Name,Age) VALUES ('Fritz Pappenheimer',30)";
database.ExecuteSQL(sSql); sSql = "INSERT INTO demo (Name,Age) VALUES ('Hella Wahnsinn',28)";
database.ExecuteSQL(sSql);
} // Close database
database.Close();
}
CATCH_ALL(e)
{
TRACE1("Driver not installed: %s",sDriver);
}
END_CATCH_ALL;
}
The information in this article applies to:Microsoft Visual C++, 32-bit Editions, versions 5.0, 6.0
Microsoft Excel 2000
The Microsoft Foundation Classes (MFC)
Microsoft Office 2000 Developer
Microsoft Excel 97 for Windows--------------------------------------------------------------------------------
SUMMARY
This article shows how to automate Microsoft Excel 97 or Excel 2000 using the Microsoft Foundation Class (MFC) library, version 4.2 (installed with Microsoft Visual C++ versions 5.0 and 6.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.Notes for Automating Microsoft Excel 2000
Some methods and properties have changed for Microsoft Excel 2000. For additional information about using the sample code described in this article with the Microsoft Excel 2000 type library,, please click the article number below to view it in the Microsoft Knowledge Base:
Q224925 INFO: Type Libraries for Office 2000 Have Changed 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:
Q178749 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:
Sample Code
// 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():
Sample Code
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.Additional query words: Excel 8 8.0 XL97 XL8