5,139
社区成员
发帖
与我相关
我的任务
分享请发表友善的回复…
发表回复
mengxihe 2001-12-07
- 打赏
- 举报
Jneu(沧海桑田)高手，请问如何将Excel录制的宏翻译成VC++的代码？如：
Range("A25:C42").Select
Selection.sort Key1:=Range("B25"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _
:=xlPinYin
因为程序中Rang会随时改变！所以要翻译成VC，
或者用Range 的 Sort 函数又怎样传参数？
Range("A25:C42").Select
Selection.sort Key1:=Range("B25"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _
:=xlPinYin
因为程序中Rang会随时改变！所以要翻译成VC，
或者用Range 的 Sort 函数又怎样传参数？
Jneu 2001-11-08
- 打赏
- 举报
#include "excel8.h"
#include <afxdao.h>
NOTE: Add this code before the call to DoModal().
if(!AfxOleInit())
{
AfxMessageBox("Cannot initialize COM services.");
return FALSE;
}
void CAutoExcelDlg::OnRun()
{
//For optional arguments
COleVariant vOpt(DISP_E_PARAMNOTFOUND, VT_ERROR);
CDaoDatabase db;
CDaoRecordset rs;
CString sConn;
long lNumCols;
//Get a recordset that represents all the records in the Products
//table of the sample Northwind database
sConn =
"C:\\Program Files\\Microsoft Office\\Office\\Samples\\Northwind.mdb";
db.Open(sConn, FALSE, FALSE);
rs.m_pDatabase = &db;
rs.Open(AFX_DAO_USE_DEFAULT_TYPE, "Select * From Products", 0);
lNumCols = rs.GetFieldCount();
//Start a new workbook in Excel
_Application oApp;
oApp.CreateDispatch("Excel.Application");
if (!oApp)
{
AfxMessageBox("Cannot start Excel");
return;
}
Workbooks oBooks = oApp.GetWorkbooks();
_Workbook oBook = oBooks.Add(vOpt);
Worksheets oSheets = oBook.GetWorksheets();
_Worksheet oSheet = oSheets.GetItem(COleVariant((short)1));
Range oRange;
//Transfer the data in the recordset to the worksheet
COleDispatchDriver rs2;
rs2.AttachDispatch((LPDISPATCH) rs.m_pDAORecordset);
oRange = oSheet.GetRange(COleVariant("A2"), vOpt);
oRange.CopyFromRecordset((LPUNKNOWN) rs2.m_lpDispatch, vOpt, vOpt);
rs2.DetachDispatch();
rs2.ReleaseDispatch();
//Add the field names to row 1
CDaoFieldInfo FieldInfo;
for(long i=0; i<=lNumCols-1;i++)
{
oRange = oSheet.GetRange(COleVariant("A1"), vOpt);
oRange = oRange.GetOffset(vOpt, COleVariant(i));
rs.GetFieldInfo(i, FieldInfo, AFX_DAO_PRIMARY_INFO);
oRange.SetValue(COleVariant(FieldInfo.m_strName));
}
//Format the worksheet
oRange = oSheet.GetRange(COleVariant("A1"), vOpt);
oRange = oRange.GetResize(COleVariant((short)1),
COleVariant(lNumCols));
Font oFont = oRange.GetFont();
oFont.SetBold(COleVariant((short)TRUE));
oRange = oRange.GetEntireColumn();
oRange.AutoFit();
//Make Excel visible and give the user control
oApp.SetVisible(TRUE);
oApp.SetUserControl(TRUE);
}
Importing and Exporting Data Using The CDaoDatabase Class
The following code example demonstrates how to copy the Customers table in NorthWind (named C:\Nw97.mdb) to Sheet1 of an Excel workbook named C:\Customers.xls using the CDaoDatabase method:
#include <afxdao.h> // Needed for MFC DAO classes.
CDaoDatabase db;
CString SQL;
SQL = "SELECT * INTO "
"[Excel 8.0;HDR=Yes;DATABASE=c:\\customers.xls].[Sheet1] "
"FROM [Customers]";
try
{
// Open database and execute SQL statement to copy data.
db.Open( "c:\\nw97.mdb" );
db.Execute( SQL, dbFailOnError );
}
catch( CDaoException * pEX )
{
// Display errors.
AfxMessageBox( pEX->m_pErrorInfo->m_strDescription );
pEX->Delete();
}
The following example demonstrates how to copy the authors table from a SQL Server pubs database into NorthWind:
#include <afxdao.h> // Needed for MFC DAO classes.
CDaoDatabase db;
CString SQL;
// Change XXX to the name of your SQL Server.
SQL = "SELECT * INTO "
"[LocalAuthors] "
"FROM "
"[ODBC;Driver=SQL Server;SERVER=XXX;DATABASE=Pubs;UID=sa;PWD=;]."
"[authors]";
try
{
// Open database and execute SQL statement to copy data.
db.Open( "c:\\nw97.mdb" );
db.Execute( SQL, dbFailOnError );
}
catch( CDaoException * pEX )
{
// Display errors.
AfxMessageBox( pEX->m_pErrorInfo->m_strDescription );
pEX->Delete();
}
Importing and Exporting Data Using The CDatabase Class
The following code example demonstrates how to copy the Customers table in NorthWind (our copy named C:\Nw97.mdb) to Sheet1 of an Excel workbook named C:\Customers.xls using the CDaoDatabase class:
#include <afxdao.h> // Needed for MFC DAO classes.
CDaoDatabase db;
CString SQL;
SQL = "SELECT * INTO "
"[Excel 8.0;HDR=Yes;DATABASE=c:\\customers.xls].[Sheet1] "
"FROM [Customers]";
try
{
// Open database and execute SQL statement to copy data.
db.Open( "c:\\nw97.mdb" );
db.Execute( SQL, dbFailOnError );
}
catch( CDaoException * pEX )
{
// Display errors.
AfxMessageBox( pEX->m_pErrorInfo->m_strDescription );
pEX->Delete();
}
The following example demonstrates how to copy the Shippers table from NorthWind to a SQL Server table named RemoteShippers in pubs:
#include <afxdb.h> // Needed for MFC ODBC classes.
CDatabase db;
CString SQL;
// Change XXX to the name of your SQL Server.
SQL = "SELECT * INTO "
"[ODBC;Driver=SQL Server;SERVER=XXX;DATABASE=Pubs;UID=sa;PWD=;]."
"[RemoteShippers] "
"FROM [Shippers]";
try
{
// Open database and execute SQL statement to copy data.
db.OpenEx( "Driver=Microsoft Access Driver (*.mdb);"
"DBQ=c:\\nw97.mdb;", CDatabase::noOdbcDialog );
db.ExecuteSQL( SQL );
}
catch( CDBException* pEX )
{
// Display errors.
AfxMessageBox( pEX->m_strError );
pEX->Delete();
}
#include <afxdao.h>
NOTE: Add this code before the call to DoModal().
if(!AfxOleInit())
{
AfxMessageBox("Cannot initialize COM services.");
return FALSE;
}
void CAutoExcelDlg::OnRun()
{
//For optional arguments
COleVariant vOpt(DISP_E_PARAMNOTFOUND, VT_ERROR);
CDaoDatabase db;
CDaoRecordset rs;
CString sConn;
long lNumCols;
//Get a recordset that represents all the records in the Products
//table of the sample Northwind database
sConn =
"C:\\Program Files\\Microsoft Office\\Office\\Samples\\Northwind.mdb";
db.Open(sConn, FALSE, FALSE);
rs.m_pDatabase = &db;
rs.Open(AFX_DAO_USE_DEFAULT_TYPE, "Select * From Products", 0);
lNumCols = rs.GetFieldCount();
//Start a new workbook in Excel
_Application oApp;
oApp.CreateDispatch("Excel.Application");
if (!oApp)
{
AfxMessageBox("Cannot start Excel");
return;
}
Workbooks oBooks = oApp.GetWorkbooks();
_Workbook oBook = oBooks.Add(vOpt);
Worksheets oSheets = oBook.GetWorksheets();
_Worksheet oSheet = oSheets.GetItem(COleVariant((short)1));
Range oRange;
//Transfer the data in the recordset to the worksheet
COleDispatchDriver rs2;
rs2.AttachDispatch((LPDISPATCH) rs.m_pDAORecordset);
oRange = oSheet.GetRange(COleVariant("A2"), vOpt);
oRange.CopyFromRecordset((LPUNKNOWN) rs2.m_lpDispatch, vOpt, vOpt);
rs2.DetachDispatch();
rs2.ReleaseDispatch();
//Add the field names to row 1
CDaoFieldInfo FieldInfo;
for(long i=0; i<=lNumCols-1;i++)
{
oRange = oSheet.GetRange(COleVariant("A1"), vOpt);
oRange = oRange.GetOffset(vOpt, COleVariant(i));
rs.GetFieldInfo(i, FieldInfo, AFX_DAO_PRIMARY_INFO);
oRange.SetValue(COleVariant(FieldInfo.m_strName));
}
//Format the worksheet
oRange = oSheet.GetRange(COleVariant("A1"), vOpt);
oRange = oRange.GetResize(COleVariant((short)1),
COleVariant(lNumCols));
Font oFont = oRange.GetFont();
oFont.SetBold(COleVariant((short)TRUE));
oRange = oRange.GetEntireColumn();
oRange.AutoFit();
//Make Excel visible and give the user control
oApp.SetVisible(TRUE);
oApp.SetUserControl(TRUE);
}
Importing and Exporting Data Using The CDaoDatabase Class
The following code example demonstrates how to copy the Customers table in NorthWind (named C:\Nw97.mdb) to Sheet1 of an Excel workbook named C:\Customers.xls using the CDaoDatabase method:
#include <afxdao.h> // Needed for MFC DAO classes.
CDaoDatabase db;
CString SQL;
SQL = "SELECT * INTO "
"[Excel 8.0;HDR=Yes;DATABASE=c:\\customers.xls].[Sheet1] "
"FROM [Customers]";
try
{
// Open database and execute SQL statement to copy data.
db.Open( "c:\\nw97.mdb" );
db.Execute( SQL, dbFailOnError );
}
catch( CDaoException * pEX )
{
// Display errors.
AfxMessageBox( pEX->m_pErrorInfo->m_strDescription );
pEX->Delete();
}
The following example demonstrates how to copy the authors table from a SQL Server pubs database into NorthWind:
#include <afxdao.h> // Needed for MFC DAO classes.
CDaoDatabase db;
CString SQL;
// Change XXX to the name of your SQL Server.
SQL = "SELECT * INTO "
"[LocalAuthors] "
"FROM "
"[ODBC;Driver=SQL Server;SERVER=XXX;DATABASE=Pubs;UID=sa;PWD=;]."
"[authors]";
try
{
// Open database and execute SQL statement to copy data.
db.Open( "c:\\nw97.mdb" );
db.Execute( SQL, dbFailOnError );
}
catch( CDaoException * pEX )
{
// Display errors.
AfxMessageBox( pEX->m_pErrorInfo->m_strDescription );
pEX->Delete();
}
Importing and Exporting Data Using The CDatabase Class
The following code example demonstrates how to copy the Customers table in NorthWind (our copy named C:\Nw97.mdb) to Sheet1 of an Excel workbook named C:\Customers.xls using the CDaoDatabase class:
#include <afxdao.h> // Needed for MFC DAO classes.
CDaoDatabase db;
CString SQL;
SQL = "SELECT * INTO "
"[Excel 8.0;HDR=Yes;DATABASE=c:\\customers.xls].[Sheet1] "
"FROM [Customers]";
try
{
// Open database and execute SQL statement to copy data.
db.Open( "c:\\nw97.mdb" );
db.Execute( SQL, dbFailOnError );
}
catch( CDaoException * pEX )
{
// Display errors.
AfxMessageBox( pEX->m_pErrorInfo->m_strDescription );
pEX->Delete();
}
The following example demonstrates how to copy the Shippers table from NorthWind to a SQL Server table named RemoteShippers in pubs:
#include <afxdb.h> // Needed for MFC ODBC classes.
CDatabase db;
CString SQL;
// Change XXX to the name of your SQL Server.
SQL = "SELECT * INTO "
"[ODBC;Driver=SQL Server;SERVER=XXX;DATABASE=Pubs;UID=sa;PWD=;]."
"[RemoteShippers] "
"FROM [Shippers]";
try
{
// Open database and execute SQL statement to copy data.
db.OpenEx( "Driver=Microsoft Access Driver (*.mdb);"
"DBQ=c:\\nw97.mdb;", CDatabase::noOdbcDialog );
db.ExecuteSQL( SQL );
}
catch( CDBException* pEX )
{
// Display errors.
AfxMessageBox( pEX->m_strError );
pEX->Delete();
}
Jneu 2001-11-02
- 打赏
- 举报
I mainly use Excel to dump the output of my database reports because of the very good printer output that I can get, and besides, my clients want all the output in Excel so that they can tweak the reports a little bit and still print it or reformat according to their whims.
To make my tips work, you must have at least Excel version 7, I haven't tried the earlier versions of Excel. I have been using OLE to drive Excel at a very acceptable speed for my clients, of course, if I know ALL about the Excel Type of Clipboard format to dump to Excel, that is the fastest way to go and dump my output there. However, OLE is here, and after digging into it, here are some things that I have uncovered by myself:
An example of program flow might be:
Procedure ExcelOut;
var XL:variant;
begin
//start Excel by creating an instance of Excel Application
//add a workbook where you dump your data
//do your data dump routines
//control Excel's cell format
//control Excel's page setup
//make Excel visible
//tell excel to preview/print your report
//tell Excel to save your output
//quit and free the instance of Excel
end;
Here are some details:
// start Excel by creating an instance of Excel Application
XL:=CreateOLEObject('Excel.Application');
// add a workbook where you dump your data
XL.WorkBooks.add;
You must include declaration of variant variables to facilitate your data dump, for example, included in the var statement following procedure ExcelOut;
// do your data dump routines
Var XL, XArr: Variant;
then after the begin statement you may wish to create XArr, ie.,
XArr:=VarArrayCreate([1,10],varVariant);
the array's purpose in our example is to dump 10 cells at a time per OLE call to Excel. Then load your data to XArr, for example:
XArr[1]:=12.85;
XArr[2]:='Yes';
XArr[3]:='California';
XArr[4]:=Table1.FieldByName('FIRSTNAME').AsString;
XArr[5]:='';
XArr[6]:=i; //i must have been predeclared elsewhere
XArr[7]:=TempStr; //TempStr is a string predeclared elsewhere
XArr[8]:=AVG; //AVG is a predeclared real number
XArr[9]:=STD; //STD is a predeclared real number
XArr[10]:=j; //j is a predeclared integer
of course, it is always better to load the array using a for-next loop instead of the brute-force illustration above. A sample of such application is to transfer a record of a database table to an Excel row, ie.:
XArr:=VarArrayCreate([1,Table1.FieldDefs.Count],varVariant);
for i:=1 to Table1.FieldDefs.Count do
XArr[i]:=Table1.Fields[i-1]; //first field of a table is 0
//FieldDefs.Count is no. of fields
whatever method you choose, you then dump XArr unto Excel for example, we want to dump XArr to first row and from columns A to J:
XL.Range('A1:J1').Value:=XArr;
another programmatic approach may be to define RowRange as string and then assign string values to RowRange controlled by our program, ie.:
RowRange:='A'+IntToStr(j)+':'+CHR(64+Table1.FieldDefs.Count)+IntToStr(j);
XL.Range(RowRange).Value:=XArr;
of course, as long as the number of fields do not go over 26 columns for the above example, where j in the above column is the desired row number in the excel spreadsheet. You may also dump a 2-dimensional array at a time but please avoid dumping one cell at a time because OLE process could become sooo slooowwww!
Assuming you have dumped your data, you may wish to format them, like add bold lines, thick lines, adjust to correct cell width, and here are the list of Excel OLE commands from within Delphi:
To format a cell or group of cells, you must select it first:
//control Excel's cell format
XL.Range('A1:J25').Select;
To select the entire spreadsheet cells use:
XL.cells.select;
Then apply the format commands:
XL.Selection.Font.Name:='Arial Cyr';
XL.Selection.Font.Size:=9;
XL.selection.Columns.AutoFit;
And here some other detailed cell formatting commands: These commands will generate a border for the selected cells using thin lines. For other types of lines, you may email me on how to get them
XL.Selection.Borders(xlLeft).Weight := xlThin;
XL.Selection.Borders(xlRight).Weight := xlThin;
XL.Selection.Borders(xlTop).Weight := xlThin;
XL.Selection.Borders(xlBottom).Weight := xlThin;
For these detailed commands to work, you must predeclare:
const
xlLeft=-4131;
xlRight=-4152;
xlTop=-4160;
xlBottom=-4107;
xlThin=2;
xlHairline=1;
xlNone=-4142;
xlAutomatic=-4105;
where did I get the values for these constants? email me!
//control Excel's page setup
XL.ActiveSheet.PageSetup.PrintTitleRows := 'A1:J1'; //Repeat this row/page
XL.ActiveSheet.PageSetup.LeftMargin:=18; //0.25" Left Margin
XL.ActiveSheet.PageSetup.RightMargin:=18; //0.25" will vary between printers
XL.ActiveSheet.PageSetup.TopMargin:=36; //0.5"
XL.ActiveSheet.PageSetup.BottomMargin:=36; //0.5"
XL.ActiveSheet.PageSetup.HeaderMargin:=18; //0.25"
XL.ActiveSheet.PageSetup.FooterMargin:=18; //0.25" Footer Margin
XL.ActiveSheet.PageSetup.CenterHorizontally:=1; //zero, means not centered
XL.ActiveSheet.PageSetup.Orientation:=2; //landscape=2, portrait=1
//make Excel visible
XL.visible:=true;
//tell excel to preview/print your report
XL.ActiveSheet.PrintPreview; //for previewing
XL.ActiveWindow.SelectedSheets.PrintOut (Copies := 1); //print directly
//tell Excel to save your output
XL.ActiveWorkBook.SaveAs ('MyOutput');
you can also save your output in other formats
//quit and free the instance of Excel
XL.visible:=False;
XL.quit;
XL:=unassigned;
How Did I Dig Out These Commands?
Well, it is easy, just run your Excel application, record a macro, and then do what you want, like page setup, open file, save file, sort, etc,... then stop recording the macro, then print your recorded macro, LO and behold!
All the commands are exposed for your perusal in Delphi, just tweak them to conform to Delphi Pascal's syntax.
My Best Tip for the Fastest Transfer of Database Data to Excel Using OLE:
Batchmove your query results, paradox tables, or sections of your large dBASE Tables into a temporary Table having a format of dBASEIV or earlier format and then using OLE, control Excel to convert the table instantly into Excel format or print the Table in Excel by opening your temporary table as a dBASE file.
For example, Temp.DBF is a dBASEIV formatted file produced as a result of a TBatchMove procedure from a query. To start the process of conversion to Excel file do the following:
Procedure ExcelOut;
var XL:variant;
begin
XL:=CreateOLEObject('Excel.Application');
XL.workbooks.open ('\Temp.DBF'); //supply the directory path if needed
XL.ActiveWorkBook.SaveAs (Filename := '\MyTable', FileFormat := -4143);
//the above line saves whatever loaded file as MyTable.XLS
//at this point, conversion to Excel File is done, no need for further code
//except for cleaning up the instance of Excel Application
//but say, you want a nice printed output, add the following codes:
XL.Cells.select; //Select all Cells and prepare for format
XL.Selection.Font.Name:='Arial';
XL.Selection.Font.Size:=9;
XL.selection.Columns.AutoFit;
XL.ActiveSheet.PageSetup.PrintTitleRows := '$1:$1'; //repeat column headings/page
XL.ActiveSheet.PageSetup.PrintGridlines := 1; //print with grid lines
XL.ActiveWindow.SelectedSheets.PrintOut (Copies := 1); //print directly to
//House-cleaning is required
XL.quit;
XL:=unassigned;
end;
The above example, is the fastest so far (less than 10 seconds on my Pentium 133 MHz machine to transfer 15 Fields by 9,656 records!) to convert your medium to large dBASE tables into Excel using only the current OLE technology but without using those expensive conversion DLL's or OCX's, and without using those QuickReports and other fancy printer formatting tools!
Well I hope you have some fun in using my tips! You can even build a faster OLEExcel component using my tips! Should you find my tip useful, kindly post my dream component in the appropriate places:
One of my wishes is really that if someone out there could write an XLDBGrid or XLStringGrid component:
This component should have the ease of use of cut and paste type of data entry for repetitive data as you would with an Excel spreadsheet, the use of ctrl-C, ctrl-V, Ctrl-X, direct cut and paste to/from an open Excel spreadsheet onto these Grid components are allowed, it should also facilitate the ease of inserting/deleting cells or entire rows or columns, it should have the same way of selecting the group of cells using a mouse or shift keys. No need to include the formula computations. You see, almost everybody who have some exposure to computers knows how to enter data in an Excel spreadsheet without the need for further training. Let them enter Tabular data in a DBGrid... AAARRRRGH!
The Paradox style DBGrid in Delphi is such a pain to enter your data, for example all the other columns are correct, except that there is one item you need to insert into that one-column and everything will be aligned correctly--you don't need to reenter or type over those other entries--just move them automatically after inserting an item! The same is true for the DBEdits, they are not suited for tabular types of data. Most receipts and scientific data are tabular, and you enter them in a tabular format, with the ease and convenience of an Excel or Lotus spreadsheet! If such a component is available, without me doing the code for those mouse and key controls, and without digging those Microsoft Clipboard Excel format, it would be a very nice front end for tabular types of data entry all controlled within Delphi. Yes there is a Formula One OCX, that is available but it is an overkill! and besides, you have to intercept or redefine the way it is handling the clipboard. Lots of work to do to emulate it to behave like an Excel-style of data entry and the OCX that ships together with your program is a big file. So please, if somebody out there who has this type of StringGrid or DBGrid, I'd gladly buy that component. For me, it would be an indispensable data entry front-end.
To make my tips work, you must have at least Excel version 7, I haven't tried the earlier versions of Excel. I have been using OLE to drive Excel at a very acceptable speed for my clients, of course, if I know ALL about the Excel Type of Clipboard format to dump to Excel, that is the fastest way to go and dump my output there. However, OLE is here, and after digging into it, here are some things that I have uncovered by myself:
An example of program flow might be:
Procedure ExcelOut;
var XL:variant;
begin
//start Excel by creating an instance of Excel Application
//add a workbook where you dump your data
//do your data dump routines
//control Excel's cell format
//control Excel's page setup
//make Excel visible
//tell excel to preview/print your report
//tell Excel to save your output
//quit and free the instance of Excel
end;
Here are some details:
// start Excel by creating an instance of Excel Application
XL:=CreateOLEObject('Excel.Application');
// add a workbook where you dump your data
XL.WorkBooks.add;
You must include declaration of variant variables to facilitate your data dump, for example, included in the var statement following procedure ExcelOut;
// do your data dump routines
Var XL, XArr: Variant;
then after the begin statement you may wish to create XArr, ie.,
XArr:=VarArrayCreate([1,10],varVariant);
the array's purpose in our example is to dump 10 cells at a time per OLE call to Excel. Then load your data to XArr, for example:
XArr[1]:=12.85;
XArr[2]:='Yes';
XArr[3]:='California';
XArr[4]:=Table1.FieldByName('FIRSTNAME').AsString;
XArr[5]:='';
XArr[6]:=i; //i must have been predeclared elsewhere
XArr[7]:=TempStr; //TempStr is a string predeclared elsewhere
XArr[8]:=AVG; //AVG is a predeclared real number
XArr[9]:=STD; //STD is a predeclared real number
XArr[10]:=j; //j is a predeclared integer
of course, it is always better to load the array using a for-next loop instead of the brute-force illustration above. A sample of such application is to transfer a record of a database table to an Excel row, ie.:
XArr:=VarArrayCreate([1,Table1.FieldDefs.Count],varVariant);
for i:=1 to Table1.FieldDefs.Count do
XArr[i]:=Table1.Fields[i-1]; //first field of a table is 0
//FieldDefs.Count is no. of fields
whatever method you choose, you then dump XArr unto Excel for example, we want to dump XArr to first row and from columns A to J:
XL.Range('A1:J1').Value:=XArr;
another programmatic approach may be to define RowRange as string and then assign string values to RowRange controlled by our program, ie.:
RowRange:='A'+IntToStr(j)+':'+CHR(64+Table1.FieldDefs.Count)+IntToStr(j);
XL.Range(RowRange).Value:=XArr;
of course, as long as the number of fields do not go over 26 columns for the above example, where j in the above column is the desired row number in the excel spreadsheet. You may also dump a 2-dimensional array at a time but please avoid dumping one cell at a time because OLE process could become sooo slooowwww!
Assuming you have dumped your data, you may wish to format them, like add bold lines, thick lines, adjust to correct cell width, and here are the list of Excel OLE commands from within Delphi:
To format a cell or group of cells, you must select it first:
//control Excel's cell format
XL.Range('A1:J25').Select;
To select the entire spreadsheet cells use:
XL.cells.select;
Then apply the format commands:
XL.Selection.Font.Name:='Arial Cyr';
XL.Selection.Font.Size:=9;
XL.selection.Columns.AutoFit;
And here some other detailed cell formatting commands: These commands will generate a border for the selected cells using thin lines. For other types of lines, you may email me on how to get them
XL.Selection.Borders(xlLeft).Weight := xlThin;
XL.Selection.Borders(xlRight).Weight := xlThin;
XL.Selection.Borders(xlTop).Weight := xlThin;
XL.Selection.Borders(xlBottom).Weight := xlThin;
For these detailed commands to work, you must predeclare:
const
xlLeft=-4131;
xlRight=-4152;
xlTop=-4160;
xlBottom=-4107;
xlThin=2;
xlHairline=1;
xlNone=-4142;
xlAutomatic=-4105;
where did I get the values for these constants? email me!
//control Excel's page setup
XL.ActiveSheet.PageSetup.PrintTitleRows := 'A1:J1'; //Repeat this row/page
XL.ActiveSheet.PageSetup.LeftMargin:=18; //0.25" Left Margin
XL.ActiveSheet.PageSetup.RightMargin:=18; //0.25" will vary between printers
XL.ActiveSheet.PageSetup.TopMargin:=36; //0.5"
XL.ActiveSheet.PageSetup.BottomMargin:=36; //0.5"
XL.ActiveSheet.PageSetup.HeaderMargin:=18; //0.25"
XL.ActiveSheet.PageSetup.FooterMargin:=18; //0.25" Footer Margin
XL.ActiveSheet.PageSetup.CenterHorizontally:=1; //zero, means not centered
XL.ActiveSheet.PageSetup.Orientation:=2; //landscape=2, portrait=1
//make Excel visible
XL.visible:=true;
//tell excel to preview/print your report
XL.ActiveSheet.PrintPreview; //for previewing
XL.ActiveWindow.SelectedSheets.PrintOut (Copies := 1); //print directly
//tell Excel to save your output
XL.ActiveWorkBook.SaveAs ('MyOutput');
you can also save your output in other formats
//quit and free the instance of Excel
XL.visible:=False;
XL.quit;
XL:=unassigned;
How Did I Dig Out These Commands?
Well, it is easy, just run your Excel application, record a macro, and then do what you want, like page setup, open file, save file, sort, etc,... then stop recording the macro, then print your recorded macro, LO and behold!
All the commands are exposed for your perusal in Delphi, just tweak them to conform to Delphi Pascal's syntax.
My Best Tip for the Fastest Transfer of Database Data to Excel Using OLE:
Batchmove your query results, paradox tables, or sections of your large dBASE Tables into a temporary Table having a format of dBASEIV or earlier format and then using OLE, control Excel to convert the table instantly into Excel format or print the Table in Excel by opening your temporary table as a dBASE file.
For example, Temp.DBF is a dBASEIV formatted file produced as a result of a TBatchMove procedure from a query. To start the process of conversion to Excel file do the following:
Procedure ExcelOut;
var XL:variant;
begin
XL:=CreateOLEObject('Excel.Application');
XL.workbooks.open ('\Temp.DBF'); //supply the directory path if needed
XL.ActiveWorkBook.SaveAs (Filename := '\MyTable', FileFormat := -4143);
//the above line saves whatever loaded file as MyTable.XLS
//at this point, conversion to Excel File is done, no need for further code
//except for cleaning up the instance of Excel Application
//but say, you want a nice printed output, add the following codes:
XL.Cells.select; //Select all Cells and prepare for format
XL.Selection.Font.Name:='Arial';
XL.Selection.Font.Size:=9;
XL.selection.Columns.AutoFit;
XL.ActiveSheet.PageSetup.PrintTitleRows := '$1:$1'; //repeat column headings/page
XL.ActiveSheet.PageSetup.PrintGridlines := 1; //print with grid lines
XL.ActiveWindow.SelectedSheets.PrintOut (Copies := 1); //print directly to
//House-cleaning is required
XL.quit;
XL:=unassigned;
end;
The above example, is the fastest so far (less than 10 seconds on my Pentium 133 MHz machine to transfer 15 Fields by 9,656 records!) to convert your medium to large dBASE tables into Excel using only the current OLE technology but without using those expensive conversion DLL's or OCX's, and without using those QuickReports and other fancy printer formatting tools!
Well I hope you have some fun in using my tips! You can even build a faster OLEExcel component using my tips! Should you find my tip useful, kindly post my dream component in the appropriate places:
One of my wishes is really that if someone out there could write an XLDBGrid or XLStringGrid component:
This component should have the ease of use of cut and paste type of data entry for repetitive data as you would with an Excel spreadsheet, the use of ctrl-C, ctrl-V, Ctrl-X, direct cut and paste to/from an open Excel spreadsheet onto these Grid components are allowed, it should also facilitate the ease of inserting/deleting cells or entire rows or columns, it should have the same way of selecting the group of cells using a mouse or shift keys. No need to include the formula computations. You see, almost everybody who have some exposure to computers knows how to enter data in an Excel spreadsheet without the need for further training. Let them enter Tabular data in a DBGrid... AAARRRRGH!
The Paradox style DBGrid in Delphi is such a pain to enter your data, for example all the other columns are correct, except that there is one item you need to insert into that one-column and everything will be aligned correctly--you don't need to reenter or type over those other entries--just move them automatically after inserting an item! The same is true for the DBEdits, they are not suited for tabular types of data. Most receipts and scientific data are tabular, and you enter them in a tabular format, with the ease and convenience of an Excel or Lotus spreadsheet! If such a component is available, without me doing the code for those mouse and key controls, and without digging those Microsoft Clipboard Excel format, it would be a very nice front end for tabular types of data entry all controlled within Delphi. Yes there is a Formula One OCX, that is available but it is an overkill! and besides, you have to intercept or redefine the way it is handling the clipboard. Lots of work to do to emulate it to behave like an Excel-style of data entry and the OCX that ships together with your program is a big file. So please, if somebody out there who has this type of StringGrid or DBGrid, I'd gladly buy that component. For me, it would be an indispensable data entry front-end.
Jneu 2001-11-02
- 打赏
- 举报
在Visual C++ 中调用Excel 2000
在开发软件时，经常要将数据输出到Excel 2000中，在Excel 2000中对该数据进行进一步地格式化处理
或进行计算处理。在Visual Basic中处理起来较简单，Excel 2000的VB编程帮助中有较为详细的介绍。
在Visual C++中如何进行处理了？利用Excel 2000的ActiveX Automate功能，处理起来同VB中类似。但
要注意以下几点：
对于对象的属性值的读取或赋值，需要用GetProperty()或SetProperty(NewValue)函数，不能象VB中直接
通过属性名称取值或赋值。例如：Worksheet.GetCount(), Worksheet.SetName(“Sheet1”)。
对集合对象中的成员对象的引用，必须使用集合对象的GetItem()函数。例如：Worksheets.GetItem
(ColeVariant((long)1))或Worksheets.GetItem(ColeVariant(“Sheet1”))取得第一个工作表。
在COM接口中，时常用到Variant，BSTR，SafeArray数据类型。Variant数据类型是一个联合，可表示几
乎所有的类型的数据，具体用法见MSDN中的相关介绍，类_variant_t是对VARIANT数据类型的封装。在
Excel 2000的VB编程帮助中，如果提到某函数或属性需要一个值，该值的数据类型通常是Variant，在
封装Excel 2000对象的类定义中，说明了具体需要的数据类型。BSTR是一个包括了字符串和字符串长度
的数据结构，类_bstr_t是对BSTR数据类型的封装。在Excel 2000的VB编程帮助中提到的字符串通常指
BSTR。具体函数参数或属性的数据类型，见封装该对象的类的定义。SafeArray是一个包括数组和数组边
界的结构，数组边界外的内容不允许访问。在Excel 2000的VB编程帮助中提到的数组是指SafeArray。关
于SafeArray的处理，请见MSDN的相关帮助。
对于缺省参数和缺省值。在VB中，函数的参数可以空缺，在VC++中不允许，必须将所有的参数填写完全。
如果你希望指定某个参数为缺省值，根据参数数据类型的不同，可指定不同的缺省值。当参数数据类型为
字符串时，可以用长度为0的字符串。如果参数是Variant类型，可用常量vtMissing，该常量在comdef.h
中定义。也可用_variant_t(DISP_E_PARAMNOTFOUND, VT_ERROR)产生一个Variant对象。
Excel对象中的集合对象有时包括的子对象是不一定的，例如：Range对象，可以表示Cell的集合，也可以
表示Column的集合或Row的集合，Range.GetItem(1)可以返回Cell或Column或Row对象。
对对象的引用或传递对象，使用IDispatch类对象，有时利用Variant对IDispatch进行包装。
以下是一段源程序，演示如何启动Excel 2000，利用一个模板文件产生一个新文档，在该文档的”Sheet1”
工作表的第一个单元中填写一段文字，设置第一列的列宽，然后调用一个模板中的宏，执行一段程序，最
后打印预览该Excel文档。模板文件名称：MyTemplate.xlt。程序在Visual C++ 6.0 sp4，Windows 2000
Professional sp-1下调试通过。
首先利用Visual C++ 6.0，建立一个MFC基于对话框的工程项目，共享DLL，Win32平台。工程名称
ExcelTest。在主对话框中加入一个按钮，
ID IDC_EXCELTEST
Caption Test Excel
双击该按钮，增加成员函数void CExcelTestDlg::OnExceltest()。
在BOOL CExcelTestApp::InitInstance()中，dlg.DoModal();之前增加代码：
if (CoInitialize(NULL)!=0)
{
AfxMessageBox("初始化COM支持库失败!");
exit(1);
}
在return FALSE; 语句前，加入：
CoUninitialize();
选择Menu->View->ClassWizade，打开ClassWizade窗口，选择Add Class->From a type library，
选择D:\Program Files\Microsoft Office\office\Excel9.OLB(D:\Program Files\Microsoft Office
是本机上Microsoft Office 2000的安装目录，可根据个人机器上的实际安装目录修改)。选择
_Application、Workbooks、_Workbook、Worksheets、_Worksheet、Range，加入新类，分别为
_Application、Workbooks、_Workbook、Worksheets、_Worksheet、Range，头文件Excel9.h，
源文件Excel9.cpp。
在ExcelTestDlg.cpp文件的头部，#include "ExcelTestDlg.h"语句之下，增加 ：
#include "comdef.h"
#include "Excel9.h"
在void CExcelTestDlg::OnExceltest() 函数中增加如下代码：
void CExcelTestDlg::OnExceltest()
{
_Application ExcelApp;
Workbooks wbsMyBooks;
_Workbook wbMyBook;
Worksheets wssMysheets;
_Worksheet wsMysheet;
Range rgMyRge;
//创建Excel 2000服务器(启动Excel)
if (!ExcelApp.CreateDispatch("Excel.Application",NULL))
{
AfxMessageBox("创建Excel服务失败!");
exit(1);
}
//利用模板文件建立新文档
wbsMyBooks.AttachDispatch(ExcelApp.GetWorkbooks(),true);
wbMyBook.AttachDispatch(wbsMyBooks.Add(_variant_t("g:\\exceltest\\MyTemplate.xlt")));
//得到Worksheets
wssMysheets.AttachDispatch(wbMyBook.GetWorksheets(),true);
//得到sheet1
wsMysheet.AttachDispatch(wssMysheets.GetItem(_variant_t("sheet1")),true);
//得到全部Cells，此时,rgMyRge是cells的集合
rgMyRge.AttachDispatch(wsMysheet.GetCells(),true);
//设置1行1列的单元的值
rgMyRge.SetItem(_variant_t((long)1),_variant_t((long)1),_variant_t("This Is A Excel Test Program!"));
//得到所有的列
rgMyRge.AttachDispatch(wsMysheet.GetColumns(),true);
//得到第一列
rgMyRge.AttachDispatch(rgMyRge.GetItem(_variant_t((long)1),vtMissing).pdispVal,true);
//设置列宽
rgMyRge.SetColumnWidth(_variant_t((long)200));
//调用模板中预先存放的宏
ExcelApp.Run(_variant_t("CopyRow"),_variant_t((long)10),vtMissing,vtMissing,
vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,
vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,
vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,
vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing);
//打印预览
wbMyBook.SetSaved(true);
ExcelApp.SetVisible(true);
wbMyBook.PrintPreview(_variant_t(false));
//释放对象
rgMyRge.ReleaseDispatch();
wsMysheet.ReleaseDispatch();
wssMysheets.ReleaseDispatch();
wbMyBook.ReleaseDispatch();
wbsMyBooks.ReleaseDispatch();
ExcelApp.ReleaseDispatch();
}
在开发软件时，经常要将数据输出到Excel 2000中，在Excel 2000中对该数据进行进一步地格式化处理
或进行计算处理。在Visual Basic中处理起来较简单，Excel 2000的VB编程帮助中有较为详细的介绍。
在Visual C++中如何进行处理了？利用Excel 2000的ActiveX Automate功能，处理起来同VB中类似。但
要注意以下几点：
对于对象的属性值的读取或赋值，需要用GetProperty()或SetProperty(NewValue)函数，不能象VB中直接
通过属性名称取值或赋值。例如：Worksheet.GetCount(), Worksheet.SetName(“Sheet1”)。
对集合对象中的成员对象的引用，必须使用集合对象的GetItem()函数。例如：Worksheets.GetItem
(ColeVariant((long)1))或Worksheets.GetItem(ColeVariant(“Sheet1”))取得第一个工作表。
在COM接口中，时常用到Variant，BSTR，SafeArray数据类型。Variant数据类型是一个联合，可表示几
乎所有的类型的数据，具体用法见MSDN中的相关介绍，类_variant_t是对VARIANT数据类型的封装。在
Excel 2000的VB编程帮助中，如果提到某函数或属性需要一个值，该值的数据类型通常是Variant，在
封装Excel 2000对象的类定义中，说明了具体需要的数据类型。BSTR是一个包括了字符串和字符串长度
的数据结构，类_bstr_t是对BSTR数据类型的封装。在Excel 2000的VB编程帮助中提到的字符串通常指
BSTR。具体函数参数或属性的数据类型，见封装该对象的类的定义。SafeArray是一个包括数组和数组边
界的结构，数组边界外的内容不允许访问。在Excel 2000的VB编程帮助中提到的数组是指SafeArray。关
于SafeArray的处理，请见MSDN的相关帮助。
对于缺省参数和缺省值。在VB中，函数的参数可以空缺，在VC++中不允许，必须将所有的参数填写完全。
如果你希望指定某个参数为缺省值，根据参数数据类型的不同，可指定不同的缺省值。当参数数据类型为
字符串时，可以用长度为0的字符串。如果参数是Variant类型，可用常量vtMissing，该常量在comdef.h
中定义。也可用_variant_t(DISP_E_PARAMNOTFOUND, VT_ERROR)产生一个Variant对象。
Excel对象中的集合对象有时包括的子对象是不一定的，例如：Range对象，可以表示Cell的集合，也可以
表示Column的集合或Row的集合，Range.GetItem(1)可以返回Cell或Column或Row对象。
对对象的引用或传递对象，使用IDispatch类对象，有时利用Variant对IDispatch进行包装。
以下是一段源程序，演示如何启动Excel 2000，利用一个模板文件产生一个新文档，在该文档的”Sheet1”
工作表的第一个单元中填写一段文字，设置第一列的列宽，然后调用一个模板中的宏，执行一段程序，最
后打印预览该Excel文档。模板文件名称：MyTemplate.xlt。程序在Visual C++ 6.0 sp4，Windows 2000
Professional sp-1下调试通过。
首先利用Visual C++ 6.0，建立一个MFC基于对话框的工程项目，共享DLL，Win32平台。工程名称
ExcelTest。在主对话框中加入一个按钮，
ID IDC_EXCELTEST
Caption Test Excel
双击该按钮，增加成员函数void CExcelTestDlg::OnExceltest()。
在BOOL CExcelTestApp::InitInstance()中，dlg.DoModal();之前增加代码：
if (CoInitialize(NULL)!=0)
{
AfxMessageBox("初始化COM支持库失败!");
exit(1);
}
在return FALSE; 语句前，加入：
CoUninitialize();
选择Menu->View->ClassWizade，打开ClassWizade窗口，选择Add Class->From a type library，
选择D:\Program Files\Microsoft Office\office\Excel9.OLB(D:\Program Files\Microsoft Office
是本机上Microsoft Office 2000的安装目录，可根据个人机器上的实际安装目录修改)。选择
_Application、Workbooks、_Workbook、Worksheets、_Worksheet、Range，加入新类，分别为
_Application、Workbooks、_Workbook、Worksheets、_Worksheet、Range，头文件Excel9.h，
源文件Excel9.cpp。
在ExcelTestDlg.cpp文件的头部，#include "ExcelTestDlg.h"语句之下，增加 ：
#include "comdef.h"
#include "Excel9.h"
在void CExcelTestDlg::OnExceltest() 函数中增加如下代码：
void CExcelTestDlg::OnExceltest()
{
_Application ExcelApp;
Workbooks wbsMyBooks;
_Workbook wbMyBook;
Worksheets wssMysheets;
_Worksheet wsMysheet;
Range rgMyRge;
//创建Excel 2000服务器(启动Excel)
if (!ExcelApp.CreateDispatch("Excel.Application",NULL))
{
AfxMessageBox("创建Excel服务失败!");
exit(1);
}
//利用模板文件建立新文档
wbsMyBooks.AttachDispatch(ExcelApp.GetWorkbooks(),true);
wbMyBook.AttachDispatch(wbsMyBooks.Add(_variant_t("g:\\exceltest\\MyTemplate.xlt")));
//得到Worksheets
wssMysheets.AttachDispatch(wbMyBook.GetWorksheets(),true);
//得到sheet1
wsMysheet.AttachDispatch(wssMysheets.GetItem(_variant_t("sheet1")),true);
//得到全部Cells，此时,rgMyRge是cells的集合
rgMyRge.AttachDispatch(wsMysheet.GetCells(),true);
//设置1行1列的单元的值
rgMyRge.SetItem(_variant_t((long)1),_variant_t((long)1),_variant_t("This Is A Excel Test Program!"));
//得到所有的列
rgMyRge.AttachDispatch(wsMysheet.GetColumns(),true);
//得到第一列
rgMyRge.AttachDispatch(rgMyRge.GetItem(_variant_t((long)1),vtMissing).pdispVal,true);
//设置列宽
rgMyRge.SetColumnWidth(_variant_t((long)200));
//调用模板中预先存放的宏
ExcelApp.Run(_variant_t("CopyRow"),_variant_t((long)10),vtMissing,vtMissing,
vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,
vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,
vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,
vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing);
//打印预览
wbMyBook.SetSaved(true);
ExcelApp.SetVisible(true);
wbMyBook.PrintPreview(_variant_t(false));
//释放对象
rgMyRge.ReleaseDispatch();
wsMysheet.ReleaseDispatch();
wssMysheets.ReleaseDispatch();
wbMyBook.ReleaseDispatch();
wbsMyBooks.ReleaseDispatch();
ExcelApp.ReleaseDispatch();
}
Ganzl 2001-11-02
- 打赏
- 举报
使用Excel的Object Library
