D
Dr.johns
Unregistered / Unconfirmed
GUEST, unregistred user!
这是一个用ado连接access数据库的例程。
{-----------------------------------------------------------------------------
Unit Name: Unit1
Author: Administrator
Purpose: 用Ado控件连接MsAccess数据库并把数据库导出为Excel表的例程
History: 0.1版
DataTime: 2002-12-18 15:32:32
-----------------------------------------------------------------------------}
unit Unit1;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, StdCtrls, DB, ADODB, Grids, DBGrids, ComObj, Excel2000,
OleServer;
type
TForm1 = class(TForm)
ListBox1: TListBox;
ADOTable1: TADOTable;
DataSource1: TDataSource;
DBGrid1: TDBGrid;
ADOConnection1: TADOConnection;
Edit1: TEdit;
Label1: TLabel;
Label2: TLabel;
Label3: TLabel;
Button1: TButton;
Button2: TButton;
OpenDialog1: TOpenDialog;
ExcelWorkbook1: TExcelWorkbook;
procedure Button1Click(Sender: TObject);
procedure OpenDialog1CanClose(Sender: TObject; var CanClose: Boolean);
procedure ListBox1Click(Sender: TObject);
procedure FormClose(Sender: TObject; var Action: TCloseAction);
procedure Button2Click(Sender: TObject);
private
{ Private declarations }
procedure CopyDbGridToExcel(Target: TDbGrid ; aExlName: String);
public
{ Public declarations }
end;
var
Form1: TForm1;
implementation
{$R *.dfm}
procedure TForm1.Button1Click(Sender: TObject);
begin
opendialog1.Execute;
end;
{-----------------------------------------------------------------------------
Purpose: 连接数据库
Procedure: TForm1.OpenDialog1CanClose
Author: Administrator
Date: 2002-12-18 15:38:10
Arguments: Sender: TObject; var CanClose: Boolean
Result: None
-----------------------------------------------------------------------------}
procedure TForm1.OpenDialog1CanClose(Sender: TObject;
var CanClose: Boolean);
begin
AdoConnection1.ConnectionString:='Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+OpenDialog1.filename+';Persist Security Info=False';
AdoConnection1.Connected:= True;
AdoConnection1.GetTableNames(ListBox1.Items,False);
CanClose:=True;
end;
procedure TForm1.ListBox1Click(Sender: TObject);
begin
AdoTable1.Active :=False;
AdoTable1.TableName :=ListBox1.Items[ListBox1.ItemIndex];
AdoTable1.Active :=True;
end;
procedure TForm1.FormClose(Sender: TObject; var Action: TCloseAction);
begin
if MessageBox(form1.Handle ,'是否退出?',
'提示',MB_OKCANCEL+MB_ICONINFORMATION+MB_DEFBUTTON1)=IDOK
then Action:=caFree
else Action:=caNone
end;
procedure TForm1.Button2Click(Sender: TObject);
begin
Form1.CopyDbGridToExcel(Dbgrid1,edit1.Text);
end;
{-----------------------------------------------------------------------------
Purpose: 将DbGrid中的数据写入Excel中,用OLE实现。
Procedure: TForm1.CopyDbGridToExcel
Author: Administrator
Date: 2002-12-18 15:38:37
Arguments: Target: TDbGrid ; aExlName: String
Result: None
-----------------------------------------------------------------------------}
procedure TForm1.CopyDbGridToExcel (Target: TDbGrid ; aExlName: String);
var
iCount, jCount: Integer;
XLApp: Variant;
Sheet: Variant;
begin
Screen.Cursor := crHourGlass;
if not VarIsEmpty(XLApp) then
begin
XLApp.DisplayAlerts := False;
XLApp.Quit;
VarClear(XLApp);
end;
//通过ole创建Excel对象
try
XLApp := CreateOleObject('Excel.Application');
except
Screen.Cursor := crDefault;
Exit;
end;
XLApp.WorkBooks.Add[XLWBatWorksheet];
XLApp.WorkBooks[1].WorkSheets[1].Name := aExlName;
Sheet := XLApp.Workbooks[1].WorkSheets[aExlName];
if not Target.DataSource.DataSet.Active then
begin
Screen.Cursor := crDefault;
Exit;
end;
Target.DataSource.DataSet.first;
for iCount := 0 to Target.Columns.Count - 1 do
begin
Sheet.cells[1, iCount + 1] := Target.Columns.Items[iCount].Title.Caption;
end;
jCount := 1;
while not Target.DataSource.DataSet.Eof do
begin
for iCount := 0 to Target.Columns.Count - 1 do
begin
Sheet.cells[jCount + 1, iCount + 1] := Target.Columns.Items[iCount].Field.AsString;
end;
Inc(jCount);
Target.DataSource.DataSet.Next;
end;
XlApp.Visible := True;
Screen.Cursor := crDefault;
end;
end.
这个程序能成功编译,也能成功执行,但我在编程的过程中遇到了很多困惑。
1、在adoconnection1下键入“.”后没有connectstring属性。
2、在opendialog1下键入“.”后没有filename属性。
在D6中以上的属性是我手工键入的。但在D5中有能自动出来。真是奇了怪了!
{-----------------------------------------------------------------------------
Unit Name: Unit1
Author: Administrator
Purpose: 用Ado控件连接MsAccess数据库并把数据库导出为Excel表的例程
History: 0.1版
DataTime: 2002-12-18 15:32:32
-----------------------------------------------------------------------------}
unit Unit1;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, StdCtrls, DB, ADODB, Grids, DBGrids, ComObj, Excel2000,
OleServer;
type
TForm1 = class(TForm)
ListBox1: TListBox;
ADOTable1: TADOTable;
DataSource1: TDataSource;
DBGrid1: TDBGrid;
ADOConnection1: TADOConnection;
Edit1: TEdit;
Label1: TLabel;
Label2: TLabel;
Label3: TLabel;
Button1: TButton;
Button2: TButton;
OpenDialog1: TOpenDialog;
ExcelWorkbook1: TExcelWorkbook;
procedure Button1Click(Sender: TObject);
procedure OpenDialog1CanClose(Sender: TObject; var CanClose: Boolean);
procedure ListBox1Click(Sender: TObject);
procedure FormClose(Sender: TObject; var Action: TCloseAction);
procedure Button2Click(Sender: TObject);
private
{ Private declarations }
procedure CopyDbGridToExcel(Target: TDbGrid ; aExlName: String);
public
{ Public declarations }
end;
var
Form1: TForm1;
implementation
{$R *.dfm}
procedure TForm1.Button1Click(Sender: TObject);
begin
opendialog1.Execute;
end;
{-----------------------------------------------------------------------------
Purpose: 连接数据库
Procedure: TForm1.OpenDialog1CanClose
Author: Administrator
Date: 2002-12-18 15:38:10
Arguments: Sender: TObject; var CanClose: Boolean
Result: None
-----------------------------------------------------------------------------}
procedure TForm1.OpenDialog1CanClose(Sender: TObject;
var CanClose: Boolean);
begin
AdoConnection1.ConnectionString:='Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+OpenDialog1.filename+';Persist Security Info=False';
AdoConnection1.Connected:= True;
AdoConnection1.GetTableNames(ListBox1.Items,False);
CanClose:=True;
end;
procedure TForm1.ListBox1Click(Sender: TObject);
begin
AdoTable1.Active :=False;
AdoTable1.TableName :=ListBox1.Items[ListBox1.ItemIndex];
AdoTable1.Active :=True;
end;
procedure TForm1.FormClose(Sender: TObject; var Action: TCloseAction);
begin
if MessageBox(form1.Handle ,'是否退出?',
'提示',MB_OKCANCEL+MB_ICONINFORMATION+MB_DEFBUTTON1)=IDOK
then Action:=caFree
else Action:=caNone
end;
procedure TForm1.Button2Click(Sender: TObject);
begin
Form1.CopyDbGridToExcel(Dbgrid1,edit1.Text);
end;
{-----------------------------------------------------------------------------
Purpose: 将DbGrid中的数据写入Excel中,用OLE实现。
Procedure: TForm1.CopyDbGridToExcel
Author: Administrator
Date: 2002-12-18 15:38:37
Arguments: Target: TDbGrid ; aExlName: String
Result: None
-----------------------------------------------------------------------------}
procedure TForm1.CopyDbGridToExcel (Target: TDbGrid ; aExlName: String);
var
iCount, jCount: Integer;
XLApp: Variant;
Sheet: Variant;
begin
Screen.Cursor := crHourGlass;
if not VarIsEmpty(XLApp) then
begin
XLApp.DisplayAlerts := False;
XLApp.Quit;
VarClear(XLApp);
end;
//通过ole创建Excel对象
try
XLApp := CreateOleObject('Excel.Application');
except
Screen.Cursor := crDefault;
Exit;
end;
XLApp.WorkBooks.Add[XLWBatWorksheet];
XLApp.WorkBooks[1].WorkSheets[1].Name := aExlName;
Sheet := XLApp.Workbooks[1].WorkSheets[aExlName];
if not Target.DataSource.DataSet.Active then
begin
Screen.Cursor := crDefault;
Exit;
end;
Target.DataSource.DataSet.first;
for iCount := 0 to Target.Columns.Count - 1 do
begin
Sheet.cells[1, iCount + 1] := Target.Columns.Items[iCount].Title.Caption;
end;
jCount := 1;
while not Target.DataSource.DataSet.Eof do
begin
for iCount := 0 to Target.Columns.Count - 1 do
begin
Sheet.cells[jCount + 1, iCount + 1] := Target.Columns.Items[iCount].Field.AsString;
end;
Inc(jCount);
Target.DataSource.DataSet.Next;
end;
XlApp.Visible := True;
Screen.Cursor := crDefault;
end;
end.
这个程序能成功编译,也能成功执行,但我在编程的过程中遇到了很多困惑。
1、在adoconnection1下键入“.”后没有connectstring属性。
2、在opendialog1下键入“.”后没有filename属性。
在D6中以上的属性是我手工键入的。但在D5中有能自动出来。真是奇了怪了!