//excel
procedure TstartForm.SpeedButton10Click(Sender: TObject);
var MSExcel:Variant;
begin
MSExcel:=CreateOLEObject('Excel.Application');
MSExcel.WorkBooks.Add;
MSExcel.Visible:=True; for i:=5 to Query1.RecordCount+4 do
begin
MsExcel.Cells[i,1].value:=Query1.FieldByName('cpxhno').Asstring;
MsExcel.Cells[i,2].value:=Query1.FieldByName('cpname').Asstring;
MsExcel.Cells[i,3].value:=Query1.FieldByName('cpunit').Asstring;
MsExcel.Cells[i,4].value:=Query1.FieldByName('htnumber').Asstring;
MsExcel.Cells[i,5].value:=Query1.FieldByName('number').Asstring;
MsExcel.Cells[i,6].value:=Query1.FieldByName('ljno').Asstring;
end;
end;退出Excel:
MSExcel.Quit;
MSExcel:=Unassigned; //释放VARIANT变
***************
先加入ExcelApplication1;ExcelWorkbook1;ExcelWorksheet1;
procedure TForm1.n1Click(Sender: TObject);
var
row,brea:integer;
a,b,c,d,e:shortstring;
begin
if table1.active = True then begin
try
ExcelApplication1.Connect;
Except
ExcelApplication1.free;
Abort;
end;
brea := strtoint(inputbox('输入需导入行数!','行数','10'));
if brea<1 then brea := 10;
ProgressBar1.Visible := True;
ProgressBar1.Max := brea;
ExcelApplication1.Visible[0] := true;
for row := 2 to brea do
begin
a := ExcelWorksheet1.Cells.Item[row,2];
b := ExcelWorksheet1.Cells.Item[row,1];
c := ExcelWorksheet1.Cells.Item[row,3];
d := ExcelWorksheet1.Cells.Item[row,4];
e := ExcelWorksheet1.Cells.Item[row,5];
form1.show;
table1.AppendRecord([a,b,c,d,e]);
ProgressBar1.Position := row-1;
end;
ProgressBar1.Hide;
end;
end;
*****************
var
MSExcel: Variant;
i: Integer;
begin
OpenDialog1.Filter:='*.XLS|*.XLS';
OpenDialog1.DefaultExt:='XLS';
if OpenDialog1.Execute then
begin
MSExcel:=CreateOLEObject('Excel.Application');
MSExcel.WorkBooks.Open(OpenDialog1.FileName);
MSExcel.Visible:=False;
//从有数据的行逐行读入数据
for i:=1 to MSExcel.ActiveSheet.UsedRange.Rows.Count do
begin
Edit2.Text:=Edit2.Text+MSExcel.Cells[i,1].Value;
end;
MSExcel.ActiveWorkBook.Close;
MSExcel.Quit;
end;
end;
procedure TstartForm.SpeedButton10Click(Sender: TObject);
var MSExcel:Variant;
begin
MSExcel:=CreateOLEObject('Excel.Application');
MSExcel.WorkBooks.Add;
MSExcel.Visible:=True; for i:=5 to Query1.RecordCount+4 do
begin
MsExcel.Cells[i,1].value:=Query1.FieldByName('cpxhno').Asstring;
MsExcel.Cells[i,2].value:=Query1.FieldByName('cpname').Asstring;
MsExcel.Cells[i,3].value:=Query1.FieldByName('cpunit').Asstring;
MsExcel.Cells[i,4].value:=Query1.FieldByName('htnumber').Asstring;
MsExcel.Cells[i,5].value:=Query1.FieldByName('number').Asstring;
MsExcel.Cells[i,6].value:=Query1.FieldByName('ljno').Asstring;
end;
end;退出Excel:
MSExcel.Quit;
MSExcel:=Unassigned; //释放VARIANT变
***************
先加入ExcelApplication1;ExcelWorkbook1;ExcelWorksheet1;
procedure TForm1.n1Click(Sender: TObject);
var
row,brea:integer;
a,b,c,d,e:shortstring;
begin
if table1.active = True then begin
try
ExcelApplication1.Connect;
Except
ExcelApplication1.free;
Abort;
end;
brea := strtoint(inputbox('输入需导入行数!','行数','10'));
if brea<1 then brea := 10;
ProgressBar1.Visible := True;
ProgressBar1.Max := brea;
ExcelApplication1.Visible[0] := true;
for row := 2 to brea do
begin
a := ExcelWorksheet1.Cells.Item[row,2];
b := ExcelWorksheet1.Cells.Item[row,1];
c := ExcelWorksheet1.Cells.Item[row,3];
d := ExcelWorksheet1.Cells.Item[row,4];
e := ExcelWorksheet1.Cells.Item[row,5];
form1.show;
table1.AppendRecord([a,b,c,d,e]);
ProgressBar1.Position := row-1;
end;
ProgressBar1.Hide;
end;
end;
*****************
var
MSExcel: Variant;
i: Integer;
begin
OpenDialog1.Filter:='*.XLS|*.XLS';
OpenDialog1.DefaultExt:='XLS';
if OpenDialog1.Execute then
begin
MSExcel:=CreateOLEObject('Excel.Application');
MSExcel.WorkBooks.Open(OpenDialog1.FileName);
MSExcel.Visible:=False;
//从有数据的行逐行读入数据
for i:=1 to MSExcel.ActiveSheet.UsedRange.Rows.Count do
begin
Edit2.Text:=Edit2.Text+MSExcel.Cells[i,1].Value;
end;
MSExcel.ActiveWorkBook.Close;
MSExcel.Quit;
end;
end;
珠海西门子表计公司 汪永荣
---- OLE自动化是Windows应用程序之间互相操纵的一种技巧。被操纵的一方称为自动化服务器(也称自动化对象),典型的自动化服务器有Microsoft Word、Excel和Powerpoint。操纵自动化服务器的一方称为自动化控制器。在开发数据库应有程序中,经常需要借助Microsoft Excel的强大报表功能,把数据库中的数据输出到Excel表格中。Delphi 5.0以前的版本虽然也可以编写自动化控制器和自动化服务器,但编写程序较为复杂,不易掌握。Delphi 5.0对于OLE提供了强大的支持,利用Delphi 5.0最新提供的Servers栏控件可以很容易开发OLE自动化控制器实现对OLE自动化服务器的调用,发挥Word、Excel、Powerpoint的强大功能。
---- 下面给出一利用Delphi 5.0开发OLE自动化控制器操纵Excel的实例,希望对用Delphi开发OLE应用程序的编程人员有所帮助。
---- 首先新建一Application, 然后在Form1上放置Servers栏控件ExcelApplication1、 ExcelWorkbook1、 ExcelWorksheet1, 再放置控件Table1、 Datasource1、 Dbgrid1、 Button1、 Button2、 Button3、 Button4, 并设置Table1.databasename:=dbdemos, Table1.TableName:=Country.db, Table1.active:=True, Button1.Caption:='SaveToExcel', Button2.caption:='PrintPreview', Button3.caption:='Print', Button4.caption:='CloseExcel'。代码如下:
unit Unit1;
interface
uses
Windows, Messages, SysUtils, Classes,
Graphics, Controls,
Forms, Dialogs, Excel97, OleServer, Db, DBTables,
Grids, DBGrids, StdCtrls;
type
TForm1 = class(TForm)
ExcelApplication1: TExcelApplication;
ExcelWorkbook1: TExcelWorkbook;
ExcelWorksheet1: TExcelWorksheet;
Table1: TTable;
Table1Name: TStringField;
Table1Capital: TStringField;
Table1Continent: TStringField;
Table1Area: TFloatField;
Table1Population: TFloatField;
button1: TButton;
DataSource1: TDataSource;
DBGrid1: TDBGrid;
Button2: TButton;
Button3: TButton;
Button4: TButton;
procedure button1Click(Sender: TObject);
procedure Button2Click(Sender: TObject);
procedure Button3Click(Sender: TObject);
procedure Button4Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;
var
Form1: TForm1;
implementation
{$R *.DFM}
procedure TForm1.button1Click(Sender: TObject);
var
i,row,column:integer;
begin
Try
ExcelApplication1.Connect;
Except
MessageDlg('Excel may not be installed',
mtError, [mbOk], 0);
Abort;
End;
ExcelApplication1.Visible[0]:=True;
ExcelApplication1.Caption:='Excel Application';
ExcelApplication1.Workbooks.Add(Null,0);
ExcelWorkbook1.ConnectTo
(ExcelApplication1.Workbooks[1]);
ExcelWorksheet1.ConnectTo
(ExcelWorkbook1.Worksheets[1] as _Worksheet);
Table1.Open;
row:=1;
While Not(Table1.Eof) do
begin
column:=1;
for i:=1 to Table1.FieldCount do
begin
ExcelWorksheet1.Cells.Item[row,column]:
=Table1.fields[i-1].AsString;
column:=column+1;
end;
Table1.Next;
row:=row+1;
end;
end;
procedure TForm1.Button2Click(Sender: TObject);
begin
ExcelWorksheet1.PrintPreview;
end;
procedure TForm1.Button3Click(Sender: TObject);
begin
ExcelWorksheet1.PrintOut;
end;
procedure TForm1.Button4Click(Sender: TObject);
begin
ExcelApplication1.Disconnect;
ExcelApplication1.Quit;
end;
end.
---- 本程序在Delphi 5.0下调试通过。