var
sheet : Variant;
XLApp : Variant;
begin
XLApp := CreateOleObject('Execl.Application');
XLApp.Visible := True;
Sheet := XLApp.WorkBooks[1].Worksheets['xxx'];
// Sheet.Cells[i,j]
end;
sheet : Variant;
XLApp : Variant;
begin
XLApp := CreateOleObject('Execl.Application');
XLApp.Visible := True;
Sheet := XLApp.WorkBooks[1].Worksheets['xxx'];
// Sheet.Cells[i,j]
end;
另外，你还可以使用OLE控件，然后在代码里使用 CreateObjectFromFile方法，在此之前，你可以好好文设置一下OLE的属性。
unit Main;interfaceuses
Windows, Messages, SysUtils,
Classes, Graphics, Controls,
Forms, Dialogs, StdCtrls;type
TForm1 = class(TForm)
Button1: TButton;
ListBox1: TListBox;
procedure Button1Click(Sender: TObject);
procedure FormDestroy(Sender: TObject);
private
XLApplication: Variant;
public
end;var
Form1: TForm1;implementationuses
ComObj;
{$R *.DFM}procedure TForm1.Button1Click(Sender: TObject);
const
{ XlSheetType }
xlChart = -4109;
xlDialogSheet = -4116;
xlExcel4IntlMacroSheet = 4;
xlExcel4MacroSheet = 3;
xlWorksheet = -4167;{ XlWBATemplate }
xlWBATChart = -4109;
xlWBATExcel4IntlMacroSheet = 4;
xlWBATExcel4MacroSheet = 3;
xlWBATWorksheet = -4167;
var
i, j: Integer;
Sheets: Variant;
begin
XLApplication := CreateOleObject('Excel.Application');
XLApplication.Visible := True;
XLApplication.Workbooks.Add;
XLApplication.Workbooks.Add(xlWBatChart);
XLApplication.Workbooks.Add(xlWBatWorkSheet);
XLApplication.Workbooks[2].Sheets.Add(,,1,xlChart);
XLApplication.Workbooks[3].Sheets.Add(,,1,xlWorkSheet);
for i := 1 to XLApplication.Workbooks.Count do begin
ListBox1.Items.Add('Workbook: ' + XLApplication.Workbooks[i].Name);
for j := 1 to XLApplication.Workbooks[i].Sheets.Count do
ListBox1.Items.Add(' Sheet: ' + XLApplication.Workbooks[i].Sheets[j].Name);
end;
end;procedure TForm1.FormDestroy(Sender: TObject);
begin
if not VarIsEmpty(XLApplication) then begin
XLApplication.DisplayAlerts := False; // Discard unsaved files....
XLApplication.Quit;
end;
end;end.
1、应用VARAIANT类型变量，采用CREATEOLEOBJECT函数生成EXCEL自动化对象。这种方法应用比较简单，但速度较慢，并且无法提供编译时的函数校验。
2、将EXCEL类型库引入DELPHI程序，这时可通过COM接口访问EXCEL自动化对象，速度较快。
此外，在DELPHI5中在SERVER页上提供了EXCEL自动化对象，可直接应用之。
抱歉，翻译太累了！Delphi 3 and Automation with Excel.Automation allows one application to control another application. The application being controlled is called an automation server (in our case Excel). The application controlling the server is called an automation controller.There are two ways that automation servers can be accessed:Late Binding (IDispatch interface) When using this method, function names and parameter datatypes are resolved at runtime, all parameters are passed as variants.As no errors in function names or parameter types are reported at compile time, this method is error prone.As function names and parameter types need to be looked up at runtime, performance is slow.The only advantage of this method for Delphi programming is that it is the only way optional parameters can be omitted from function calls.Early Binding (Using type libraries/interfaces) When using this method, function names and parameter datatypes are all resolved at compile time. A type library needs to be imported into Delphi. A type library is a language neutral description of all the objects and functions exposed by a server. (This is similar in nature to a C header file).All parameters need to be supplied, even when calling functions where the documentation states that some are optional. This enables many errors to be detected and corrected before ever running a program.Performance is better than for late binding.Due to the advantages of the second approach, the rest of the document demonstrates the basics of creating an application with early binding. All applications that use Excel Automation should use this technique unless there is a strong justification for not doing so.Preparing the Type Library.A pascal unit needs to be created from the type library file.Select Project and Import Type Library menu item.Press the add button and select the following filec:\program files\microsoft office\office\excel8.olbSelect OK.Unfortunately, this leaves the project in a state that will not compile, this is because the excel_tlb unit redefines the word application to mean something else. The easiest way to resolve this is to remove the excel_tlb unit from the project and only add it to the uses clause of the units that perform automation.DocumentationThe help file c:\program files\microsoft office\office\vbaxl8.hlp contains information about the objects available for excel.The macro recorder allows VBA code to be created. This can then be taken and translated into Delphi quite easily.Automation Example Download codeThe following code example demonstrates the creation of a simple spreadsheet and population of it with data. Ensure that the unit excel_tlb is included within the module. It is strongly recommended that automation code is kept within a separate module as this will prevent problems caused by name clashes.Unit sheet;
//--------------------------------------------------------------------
interface
//--------------------------------------------------------------------
uses
windows, sysutils, excel_tlb; Procedure CreateSpreadsheet;
//--------------------------------------------------------------------
implementation
//--------------------------------------------------------------------
Procedure CreateSpreadsheet(filename : string);
var
xla : _Application;
xlw : _Workbook;
LCID : integer;
begin
xla := CoApplication.Create;
LCID := GetUserDefaultLCID;
try
xla.Visible[LCID] := true;
// blank workbook
//xlw := xla.Workbooks.Add(xlWBATWorksheet, LCID);
// new workbook based on template
xlw := xla.Workbooks.Add('c:\delphi\excel\sample\demo.xlt',LCID);
xla.Range['A1', 'A1'].Value := 'Date';
xla.Cells[1, 2].Value := FormatDateTime('dd-mmm-yyyy', Now);
xla.Cells[3, 1].Value := 'Numbers';
xla.Range['B3', 'E3'].Value := VarArrayOf([1, 10, 100, 1000]);
xla.Range['F3', 'F3'].Formula := '=Sum(B3:E3)';
OLEVariant(xla).Run('Demo', FormatDateTime('dd-mmm-yyyy', Now));
xlw.SaveAs(filename,xlWorkbookNormal,'','',False,False,xlNoChange,
xlLocalSessionChanges,true,0,0,LCID);
finally
xla.Quit;
end;
end;
//--------------------------------------------------------------------
end.
Add the type library unit to the uses clause.uses
windows, sysutils, excel_tlb;
The first line of code creates an Excel application object. xla := CoApplication.Create;
The following line of code retrieves the user default locale identifier. This is required by many of Excel’s methods and properties. LCID := GetUserDefaultLCID;
The following line of code sets the visible property to true. This causes the excel window to become visible. This is useful for checking that the code is working correctly during development. Note : This call takes the LCID parameter. Unfortunately this is not documented within the Excel help file. The file c:\program files\borland\Delphi 3\imports\excel_tlb.pas shows the function property and method definitions. xla.visible[LCID] := true;
The following creates a new worksheet, assigning a reference to it within a Delphi variable. Under VBA, the template parameter is optional, Under Delphi it must be supplied. xlw := xla.Workbooks.Add('c:\delphi\excel\sample\demo.xlt', LCID);
Note : You do not need to supply an Excel template file (.xlt), though it is the best way to set the formatting information. The more that can be done within excel, the less needs to be done within Delphi. This leads to simpler and more maintainable systems. To create a blank workbook use: xlw := xla.Workbooks.Add(xlWBATWorksheet, LCID);
The following two lines each set a single cell with a value. They show the two different cell referencing techinques. xla.Range['A1', 'A1'].Value := 'Date';
xla.Cells[1, 2].Value := FormatDateTime('dd-mmm-yyyy', Now);
The following line demonstrates setting a row of data with one call. This give a significant performance advantage. xla.Range['A2', 'D2'].Value := VarArrayOf([1, 10, 100, 1000]);
The following line demonstrates setting a formula. xla.Range['E2', 'E2'].Formula := '=Sum(a2:d2)';
The follwing line of code executes a VBA function which is stored within the template file. The code does looks a bit puzzling at first. By typecasting xla to an OLEVariant forces the call to use late binding rather than early. (Causing the method name and parameters to be resolved at runtime rather than compile time). This is required for this call as Delphi cannot tell how many parameters the macro ‘Demo’ has. OLEVariant(xla).Run( 'Demo', FormatDateTime('dd-mmm-yyyy', Now));
The following code saves the spreadsheet as filename. All parameters must be supplied even although most are optional within VBA. xlw.SaveAs( filename, xlWorkbookNormal, '', '',False,False, xlNoChange, xlLocalSessionChanges,
true, 0, 0, LCID);
The following line of code closes Excel and de-allocates any memory associated with it. xla.quit;
Summary Always use early binding.
If late binding is required for some calls, use early binding in general and typecast the object variable to an OLEVariant for the calls that require late binding.
Do not include the type library unit within your project.
Keep automation code within a separate unit.
Use the Excel macro recorder to prototype your automation code.
Use the vbaxl8.hlp file for Excel object programming information.
Use the unit excel_tlb.pas to check for Delphi parameter types and numbers.
Load up Excel spreadsheet templates (.xlt files) that contain predefined formatting and then super-impose data. This is faster and reduces the programming required to create formatted spreadsheets. Templates MUST be stored with the application in the application preectory. This will remove the possibility of name clashes. Template files can also contain Excel macros that can be run from Delphi.
Ensure that the quit method is called for the Excel application object (xla.quit). Not calling xla.quit will quickly exhaust all windows resources as multiple instances of Excel will be created.
It is easy to check for multiple instances of Excel by using the NT Task Manager Processes Page (press CTL+ALT+Del to access this.)
On large spreadsheets, setting multiple cells on each automation call will dramatically improve performance. Try not to sacrifice code readability.
Appendix A – PerformanceAll tests were done on a P166/64mb memory. The initial timings were not recorded. This ensured that excel was available from cache rather than loading from disk. This made it easier to record consistent timings. For a real application there will be approx a 5 second delay in loading excel the first time it is used within the session. The test involved loading a blank spreadsheet with 10 cols by n rows worth of numeric data. Three techniques were used to test performance: Populating the sheet one cell at a time.
Populating the spreadsheet one row at a time.
Populating the spreadsheet in one go.
These timings do not include the time involved in retrieving data from the database. This will add a significant amount of time for the creation of large spreadsheets.All the times shown below are in minutes, time was measured to the nearest second.Spreadsheet size (rows * columns) Cell at a time Row at a time Sheet at a time
10 * 10 0:01 0:01 >0:01
100 * 10 0:07 0:01 0:01
1000 * 10 1:13 0:07 0:05
5000 * 10 5:22 0:35 0:25
Approx. cells/second 150 1500 2000 Only small spreadsheets can be efficiently populated one cell at a time.Larger sheets should be populated a row at a time.The additional complexity of implementing a sheet at a time data population is not worthwhile.Using the clipboard to transfer data is not recommended as this will corrupt the clipboard and may lead to obscure code.Saving data as a CSV file and loading it into Excel may be faster, but will require formatting to be done within code and make coding/error handling more complex.The procedures used for testing://-----------------------------------------------------------------------
procedure FillByCell;
var
xla : _Application;
xlw : _Workbook;
LCID : integer;
i,j : integer;
begin
xla := CoApplication.Create;
LCID := GetUserDefaultLCID;
try
xlw := xla.Workbooks.Add(xlWBATWorksheet, LCID);
for i:=1 to ROWS do begin
for j:=1 to 10 do begin
xla.Cells[i,j] := i+j;
end;
end;
xlw.close(false, '', false, LCID);
finally
xla.Quit;
end;
end;
//-----------------------------------------------------------------------
procedure FillByRow;
var
xla : _Application;
xlw : _Workbook;
CellFrom : string;
CellTo : string;
i,j : integer;
Row : array[1..10] of variant;
LCID : integer;
begin
xla := CoApplication.Create;
LCID := GetUserDefaultLCID;
try
xlw := xla.Workbooks.Add(xlWBATWorksheet, LCID);
for i:=1 to ROWS do begin
for j:=1 to 10 do begin
Row[j] := i+j;
end;
CellFrom := 'A' + InttoStr(i);
CellTO := 'J' + InttoStr(i);
xla.Range[CellFrom, CellTo].Value := VarArrayOf(Row);
end;
xlw.close(false, '', False, LCID);
finally
xla.Quit;
end;
end;
//-----------------------------------------------------------------------
procedure FillBySheet;
var
xla : _Application;
xlw : _Workbook;
CellFrom : string;
CellTo : string;
i,j : integer;
range : Variant;
row : array [1..10] of Variant;
LCID : integer;
begin
xla := CoApplication.Create;
LCID := GetUserDefaultLCID;
try
xlw := xla.Workbooks.Add(xlWBATWorksheet, LCID);
Range := VarArrayCreate([1, ROWS], varVariant);
for i:=1 to ROWS do begin
for j:=1 to 10 do begin
row[j] := i+j;
end;
Range[i] := VarArrayOf(row);
end;
CellFrom := 'A' + InttoStr(1);
CellTO := 'J' + InttoStr(ROWS);
xla.Range[CellFrom, CellTo].FormulaArray := Range;
xlw.close(false, '', False, LCID);
finally
xla.Quit;
end;
end;Appendix 2 – Using a Delphi Wrapper ClassDownload codeRather than embedding Automation calls preectly within your application, this example demonstrates how a simple Delphi class can be used to encapsulate the Excel objects for your applicaions. This can give you a simpler interface to the objects and may help deal with any changes to the object interfaces in future versions of Excel.unit sheet;interfaceuses
EXCEL_TLB, windows, sysutils;
//-------------------------------------------------------------------------
type
tExcel = class
private
xla : _Application;
xlw : _Workbook;
LCID : integer;
procedure fSetVisible(Visible : boolean);
function fGetVisible : boolean;
procedure fSetCell(Cell : string; Value : OLEVariant);
function fGetCell(Cell : string) : OleVariant;
public
constructor create;
destructor destroy; override; procedure AddWorkBook(Template : OleVariant);
procedure SaveAs(filename : string); property Visible : boolean read fGetVisible write fSetVisible;
property Cell[Cell : string] : OleVariant read fGetCell write fSetCell;
end;
//-------------------------------------------------------------------------
Procedure CreateSpreadsheet(filename : string);
//-------------------------------------------------------------------------implementation
//-------------------------------------------------------------------------
constructor tExcel.create;
begin
LCID := GetUserDefaultLCID;
xla := CoApplication.Create;
end;
//-------------------------------------------------------------------------
destructor tExcel.destroy;
begin
xla.Quit;
inherited;
end;
//-------------------------------------------------------------------------
procedure tExcel.AddWorkBook(Template : OleVariant);
begin
xlw := xla.Workbooks.Add(Template, LCID);
end;
//-------------------------------------------------------------------------
procedure tExcel.fSetVisible(Visible : boolean);
begin
xla.visible[lcid] := Visible;
end;
//-------------------------------------------------------------------------
function tExcel.fGetVisible : boolean;
begin
result := xla.visible[lcid];
end;
//-------------------------------------------------------------------------
procedure tExcel.fSetCell(Cell : string; Value : OLEVariant);
begin
xla.Range['A1', 'A1'].Value := value;
end;
//-------------------------------------------------------------------------
function tExcel.fGetCell(Cell : string) : OleVariant;
begin
result := xla.Range['A1', 'A1'].Value;
end;
//-------------------------------------------------------------------------
procedure tExcel.SaveAs(filename : string);
begin
xlw.SaveAs(filename,xlWorkbookNormal,'','',False,False,xlNoChange,
xlLocalSessionChanges,true,0,0,LCID);
end;//The following code example uses this class to create a spreadsheet.Procedure CreateSpreadsheet(filename : string);
var
xl : tExcel;
begin
xl := tExcel.create;
try
xl.AddWorkBook('c:\graham\excel\sample2\ssddemo.xlt');
xl.visible := true;
xl.cell['a1'] := 'testing';
xl.SaveAs(filename);
finally
xl.free;
end;
end;
Ver 1.0 GSM 29/12/1997
内存太多,速度太慢!很不理想!不过碰到这样的客户也没办法!:(
基本上使用下面方法就可以做你想做的事了:
...
V:variant;
...
try
V:=CreateOleObject('Excel.Application');
except
on EOleException do
begin
v.Quit;
exit;
end;
end; try
V.application.WorkBooks.Open('D:\1.xls');
except
on EOleException do
begin
v.Quit;
exit;
end;
end;