procedure TForm1.FormCloseQuery(Sender: TObject; var CanClose: Boolean); begin TxtConnection.Close; DBFConnection.Close; AccessConnection.Close; ExcelConnection.Close; end;procedure TForm1.AccessConnectionExecuteComplete(Connection: TADOConnection; RecordsAffected: Integer; const Error: Error; var EventStatus: TEventStatus; const Command: _Command; const Recordset: _Recordset); begin //记时1 iT2 := GetTickCount; StatusBar.Panels[1].Text := FormatFloat('#,##', iT2 - iT1) + '毫秒'; StatusBar.Panels[2].Text := '共导记录:' + IntToStr(RecordsAffected) + '条'; end;procedure TForm1.AccessConnectionWillExecute(Connection: TADOConnection; var CommandText: WideString; var CursorType: TCursorType; var LockType: TADOLockType; var CommandType: TCommandType; var ExecuteOptions: TExecuteOptions; var EventStatus: TEventStatus; const Command: _Command; const Recordset: _Recordset); begin iT1 := GetTickCount; end;//================================================================= //Access //Access->TXT procedure TForm1.Button1Click(Sender: TObject); begin sSql := 'select * into [Text;Database=f:\].aaa.txt from demo'; with AccessConnection do begin Connected := True; Execute(sSql); end; end;//Access->DBF procedure TForm1.Button3Click(Sender: TObject); begin sSql := 'select * into aaa in ''f:\'' ''dbase 5.0;'' from demo'; with AccessConnection do begin Connected := True; Execute(sSql); end; end;//Access->Excel //注意:前一个aaa为Excel文件中的aaa页,后一个aaa为Access文件demo.mdb中的一个表名 procedure TForm1.Button9Click(Sender: TObject); begin sSql := 'select * into aaa from aaa in ''E:\Delphilx\ADOSQL\PH\demo.mdb'''; with ExcelConnection do begin Connected := True; Execute(sSql); end; end;//Access->Access procedure TForm1.Button4Click(Sender: TObject); begin sSql := 'select * into aaa from demo in ''E:\Delphilx\ADOSQL\PH\demo.mdb'''; with AccessConnection do begin Connected := True; Execute(sSql); end; end;//TXT->Access procedure TForm1.Button2Click(Sender: TObject); begin sSql := 'select * into uform from [Text;Database=f:\].form.txt'; with AccessConnection do begin Connected := True; Execute(sSql); end; end;//DBF->Access procedure TForm1.Button5Click(Sender: TObject); begin sSql := 'select * into aaa from aaa in ''f:\'' ''dbase 5.0;'''; with AccessConnection do begin Connected := True; Execute(sSql); end; end;//Excel->Access //[aaa$]->Excel文件aaa.xls中的aaa页 procedure TForm1.Button10Click(Sender: TObject); begin sSql := 'select * into bbb from [excel 8.0;database=f:\aaa.xls].[aaa$]'; with AccessConnection do begin Connected := True; Execute(sSql); end; end;//================================================================= //Oracle //Oracle->Access procedure TForm1.Button6Click(Sender: TObject); begin sSql := 'select * into ouform from ouform in [ODBC][ODBC;Driver={Microsoft ODBC for Oracle};UID=bklskf;PWD=bklskf;SERVER=bkls;]'; with AccessConnection do begin Connected := True; Execute(sSql); end; end;//Oracle->TXT procedure TForm1.Button7Click(Sender: TObject); begin sSql := 'select VGH,VXM,VMM into lsygb.txt from (select * from lsygb in [ODBC][ODBC;Driver={Microsoft ODBC for Oracle};UID=BKLS;PWD=BKLS;SERVER=BKLS;])'; // sSql := // 'select * into form.txt from ouform in [ODBC][ODBC;Driver={Microsoft ODBC for Oracle};UID=bklskf;PWD=bklskf;SERVER=bkls;]'; with TxtConnection do begin Close; Connected := True; Execute(sSql); end; end;//Oracle->Excel procedure TForm1.Button8Click(Sender: TObject); begin sSql := 'select * into aaa from lsygb in [ODBC][ODBC;Driver={Microsoft ODBC for Oracle};UID=bkls;PWD=bkls;SERVER=bkls;]'; with ExcelConnection do begin Connected := True; Execute(sSql); end; end;//Oracle->DBF procedure TForm1.Button11Click(Sender: TObject); begin sSql := 'select * into aaa from lsygb in [ODBC][ODBC;Driver={Microsoft ODBC for Oracle};UID=bkls;PWD=bkls;SERVER=bkls;]'; with DBFConnection do begin Connected := True; Execute(sSql); end; end;//TXT->Oracle //Oracle数据库注意要将表名大写!不然创建出来的表可以看到表名但无法打开 procedure TForm1.Button12Click(Sender: TObject); begin sSql := 'select * into PH_TXT in [ODBC][ODBC;Driver={Microsoft ODBC for Oracle};UID=bkls;PWD=bkls;SERVER=bkls;] from ph_txt.txt'; with TxtConnection do begin Close; Connected := True; Execute(sSql); end; end;//DBF->Oracle procedure TForm1.Button13Click(Sender: TObject); begin sSql := 'select * into PH_DBF in [ODBC][ODBC;Driver={Microsoft ODBC for Oracle};UID=bkls;PWD=bkls;SERVER=bkls;] from aaa'; with DBFConnection do begin Connected := True; Execute(sSql); end; end;//Access->Oracle procedure TForm1.Button14Click(Sender: TObject); begin sSql := 'select * into PH_ACCESS in [ODBC][ODBC;Driver={Microsoft ODBC for Oracle};UID=bkls;PWD=bkls;SERVER=bkls;] from aaa'; with AccessConnection do begin Connected := True; Execute(sSql); end; end;//Excel->Oracle procedure TForm1.Button15Click(Sender: TObject); begin // 'select * into MLB in [ODBC][ODBC;Driver={Microsoft ODBC for Oracle};UID=BKLS;PWD=BKLS;Server=BKLS;] from MLB' //Provider=Microsoft.Jet.OLEDB.4.0;Data Source=f:\aaa.xls;Extended Properties=Excel 8.0;Persist Security Info=False sSql := 'select * into PH_EXCEL in [ODBC][ODBC;Driver={Microsoft ODBC for Oracle};UID=bkls;PWD=bkls;SERVER=bkls;] from aaa'; with ExcelConnection do begin Connected := True; Execute(sSql); end; end;procedure TForm1.Button16Click(Sender: TObject); begin sSql := 'select * into qxb in ''f:\'' ''dbase 5.0;'' from lsqxb.txt'; with TXTConnection do begin // Close; // ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;Password="";Data Source=f:\temp;Extended Properties="text;HDR=YES;FMT=Delimited";Persist Security Info=True'; Connected := True; Execute(sSql); end; end;end.csdn 搜到的。
用AdoQuery查询出来: with AdoQuery1 do begin Close; Sql.Clear; Sql.Add('select * from table1'); Open; end; 然后调用如下过程即可: procedure ExportToExcel(ADOQry: TADOQuery); var MyExcel: Variant; WorkBook: OleVariant; WorkSheet: OleVariant; i,j:integer; begin try MyExcel:=CreateOleObject('Excel.Application'); MyExcel.Application.WorkBooks.Add; MyExcel.Caption:='将数据导入到EXCEL表中'; MyExcel.Application.Visible:=true; WorkBook:=MyExcel.Application.workbooks[1]; worksheet:=workbook.worksheets.item[1]; except showmessage('EXCEL不存在!'); end; i:=1; with ADOQry do begin for j:=0 to FieldCount-1 do worksheet.cells[1,j+1]:=Fields[j].FieldName; First; while not Eof do begin inc(i); for j:=0 to fieldcount-1 do begin worksheet.cells[i,j+1].NumberFormatLocal :='@'; worksheet.cells[i,j+1].Borders.LineStyle:=1; worksheet.cells[i,j+1]:=Fields[j].asstring; end; next; end; end; end;
begin
TxtConnection.Close;
DBFConnection.Close;
AccessConnection.Close;
ExcelConnection.Close;
end;procedure TForm1.AccessConnectionExecuteComplete(Connection: TADOConnection;
RecordsAffected: Integer; const Error: Error;
var EventStatus: TEventStatus; const Command: _Command;
const Recordset: _Recordset);
begin
//记时1
iT2 := GetTickCount;
StatusBar.Panels[1].Text := FormatFloat('#,##', iT2 - iT1) + '毫秒';
StatusBar.Panels[2].Text := '共导记录:' + IntToStr(RecordsAffected) + '条';
end;procedure TForm1.AccessConnectionWillExecute(Connection: TADOConnection;
var CommandText: WideString; var CursorType: TCursorType;
var LockType: TADOLockType; var CommandType: TCommandType;
var ExecuteOptions: TExecuteOptions; var EventStatus: TEventStatus;
const Command: _Command; const Recordset: _Recordset);
begin
iT1 := GetTickCount;
end;//=================================================================
//Access
//Access->TXT
procedure TForm1.Button1Click(Sender: TObject);
begin
sSql := 'select * into [Text;Database=f:\].aaa.txt from demo';
with AccessConnection do
begin
Connected := True;
Execute(sSql);
end;
end;//Access->DBF
procedure TForm1.Button3Click(Sender: TObject);
begin
sSql := 'select * into aaa in ''f:\'' ''dbase 5.0;'' from demo';
with AccessConnection do
begin
Connected := True;
Execute(sSql);
end;
end;//Access->Excel
//注意:前一个aaa为Excel文件中的aaa页,后一个aaa为Access文件demo.mdb中的一个表名
procedure TForm1.Button9Click(Sender: TObject);
begin
sSql :=
'select * into aaa from aaa in ''E:\Delphilx\ADOSQL\PH\demo.mdb''';
with ExcelConnection do
begin
Connected := True;
Execute(sSql);
end;
end;//Access->Access
procedure TForm1.Button4Click(Sender: TObject);
begin
sSql := 'select * into aaa from demo in ''E:\Delphilx\ADOSQL\PH\demo.mdb''';
with AccessConnection do
begin
Connected := True;
Execute(sSql);
end;
end;//TXT->Access
procedure TForm1.Button2Click(Sender: TObject);
begin
sSql := 'select * into uform from [Text;Database=f:\].form.txt';
with AccessConnection do
begin
Connected := True;
Execute(sSql);
end;
end;//DBF->Access
procedure TForm1.Button5Click(Sender: TObject);
begin
sSql := 'select * into aaa from aaa in ''f:\'' ''dbase 5.0;''';
with AccessConnection do
begin
Connected := True;
Execute(sSql);
end;
end;//Excel->Access
//[aaa$]->Excel文件aaa.xls中的aaa页
procedure TForm1.Button10Click(Sender: TObject);
begin
sSql :=
'select * into bbb from [excel 8.0;database=f:\aaa.xls].[aaa$]';
with AccessConnection do
begin
Connected := True;
Execute(sSql);
end;
end;//=================================================================
//Oracle
//Oracle->Access
procedure TForm1.Button6Click(Sender: TObject);
begin
sSql :=
'select * into ouform from ouform in [ODBC][ODBC;Driver={Microsoft ODBC for Oracle};UID=bklskf;PWD=bklskf;SERVER=bkls;]';
with AccessConnection do
begin
Connected := True;
Execute(sSql);
end;
end;//Oracle->TXT
procedure TForm1.Button7Click(Sender: TObject);
begin
sSql := 'select VGH,VXM,VMM into lsygb.txt from (select * from lsygb in [ODBC][ODBC;Driver={Microsoft ODBC for Oracle};UID=BKLS;PWD=BKLS;SERVER=BKLS;])';
// sSql :=
// 'select * into form.txt from ouform in [ODBC][ODBC;Driver={Microsoft ODBC for Oracle};UID=bklskf;PWD=bklskf;SERVER=bkls;]';
with TxtConnection do
begin
Close;
Connected := True;
Execute(sSql);
end;
end;//Oracle->Excel
procedure TForm1.Button8Click(Sender: TObject);
begin
sSql :=
'select * into aaa from lsygb in [ODBC][ODBC;Driver={Microsoft ODBC for Oracle};UID=bkls;PWD=bkls;SERVER=bkls;]';
with ExcelConnection do
begin
Connected := True;
Execute(sSql);
end;
end;//Oracle->DBF
procedure TForm1.Button11Click(Sender: TObject);
begin
sSql :=
'select * into aaa from lsygb in [ODBC][ODBC;Driver={Microsoft ODBC for Oracle};UID=bkls;PWD=bkls;SERVER=bkls;]';
with DBFConnection do
begin
Connected := True;
Execute(sSql);
end;
end;//TXT->Oracle
//Oracle数据库注意要将表名大写!不然创建出来的表可以看到表名但无法打开
procedure TForm1.Button12Click(Sender: TObject);
begin
sSql := 'select * into PH_TXT in [ODBC][ODBC;Driver={Microsoft ODBC for Oracle};UID=bkls;PWD=bkls;SERVER=bkls;] from ph_txt.txt';
with TxtConnection do
begin
Close;
Connected := True;
Execute(sSql);
end;
end;//DBF->Oracle
procedure TForm1.Button13Click(Sender: TObject);
begin
sSql :=
'select * into PH_DBF in [ODBC][ODBC;Driver={Microsoft ODBC for Oracle};UID=bkls;PWD=bkls;SERVER=bkls;] from aaa';
with DBFConnection do
begin
Connected := True;
Execute(sSql);
end;
end;//Access->Oracle
procedure TForm1.Button14Click(Sender: TObject);
begin
sSql :=
'select * into PH_ACCESS in [ODBC][ODBC;Driver={Microsoft ODBC for Oracle};UID=bkls;PWD=bkls;SERVER=bkls;] from aaa';
with AccessConnection do
begin
Connected := True;
Execute(sSql);
end;
end;//Excel->Oracle
procedure TForm1.Button15Click(Sender: TObject);
begin
// 'select * into MLB in [ODBC][ODBC;Driver={Microsoft ODBC for Oracle};UID=BKLS;PWD=BKLS;Server=BKLS;] from MLB'
//Provider=Microsoft.Jet.OLEDB.4.0;Data Source=f:\aaa.xls;Extended Properties=Excel 8.0;Persist Security Info=False
sSql :=
'select * into PH_EXCEL in [ODBC][ODBC;Driver={Microsoft ODBC for Oracle};UID=bkls;PWD=bkls;SERVER=bkls;] from aaa';
with ExcelConnection do
begin
Connected := True;
Execute(sSql);
end;
end;procedure TForm1.Button16Click(Sender: TObject);
begin
sSql := 'select * into qxb in ''f:\'' ''dbase 5.0;'' from lsqxb.txt';
with TXTConnection do
begin
// Close;
// ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;Password="";Data Source=f:\temp;Extended Properties="text;HDR=YES;FMT=Delimited";Persist Security Info=True';
Connected := True;
Execute(sSql);
end;
end;end.csdn 搜到的。
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
StdCtrls, Db, Grids, DBGrids, DBTables, ComObj, DBGridEh, Excel97;const
X1NumberFormat = 'G/ͨÓøñʽ';type TLoadFromExcelObject = class(TObject)
private
Xls: Variant; //ÄÚ½¨EXCEL¶ÔÏó
FVisible: boolean; //½øÐвÙ×÷ʱEXCELÊÇ·ñ¿É¼û
HasStarted: boolean;
FFileName: TFileName;
FEndRow: Word;
FStartCol: Word;
FStartRow: Word;
FEndCol: Word; //ÄÚ²¿ÓÃÓڼǼEXCEL¶ÔÏóÊÇ·ñÆô¶¯
procedure SetVisible(const Value: boolean); //µÃµ½±í¸ñ±êÌâ×î´óµÄµþ¼Ó²ãÊý
procedure SetEndRow(const Value: Word);
procedure SetFileName(const Value: TFileName);
procedure SetStartCol(const Value: Word);
procedure SetStartRow(const Value: Word);
procedure SetEndCol(const Value: Word); public
//½«Ò»¸öÊý¾Ý¼¯£¨Table£¬Query£©×ªµ½EXCEL
procedure LoadFromExcel(ADataSet: TDBDataSet);
constructor Create(AFileName: string); overload;
destructor Destroy; override;
property Visible: Boolean read FVisible write SetVisible;
property FileName: TFileName read FFileName write SetFileName;
property StartRow: Word read FStartRow write SetStartRow;
property EndRow: Word read FEndRow write SetEndRow;
property StartCol: Word read FStartCol write SetStartCol;
property EndCol: Word read FEndCol write SetEndCol;
end;
procedure LoadFromExcel(ADataSet: TDBDataSet; XlsFile: TFileName; StartRow: Integer = 1;
EndRow: Integer = 1; StartCol: Integer = 1; EndCol: Integer = 1);
implementationprocedure LoadFromExcel(ADataSet: TDBDataSet; XlsFile: TFileName; StartRow: Integer = 1;
EndRow: Integer = 1; StartCol: Integer = 1; EndCol: Integer = 1);
var
LoadExcel: TLoadFromExcelObject;
begin
LoadExcel := TLoadFromExcelObject.Create(XlsFile);
LoadExcel.StartRow := StartRow;
LoadExcel.EndRow := EndRow;
LoadExcel.StartCol := StartCol;
LoadExcel.EndCol := EndCol;
LoadExcel.LoadFromExcel(ADataSet);
LoadExcel.Free;
end;{ TExcelObject }//ΪÁ˱ãÓÚÀ©Õ¹£¬ÒÔºó¿ÉÔÚ´ËÔö¼ÓÊôÐÔ³õʼ»¯constructor TLoadFromExcelObject.Create(AFileName: string);
begin
inherited Create;
Xls := CreateOleObject('Excel.Application');
Xls.Application.Workbooks.Open(AFileName);
Xls.Application.Visible := False;
StartRow := 1;
EndRow := 1;
StartCol := 1;
EndCol := 1;
end;
procedure TLoadFromExcelObject.SetVisible(const Value: boolean);
begin
if not HasStarted then exit;
FVisible := Value;
if Xls.Application.Visible <> FVisible then
Xls.Application.Visible := FVisible;
end;//µÃµ½±í¸ñ±êÌâ×î´óµÄµþ¼Ó²ãÊý
procedure TLoadFromExcelObject.LoadFromExcel
(ADataSet: TDBDataSet);
var
I, J: Integer;
Index: Integer;
Sheet: Variant;
ECol, ERow: Integer;
//fDate: TDatetime;
begin
Sheet := Xls.Application.ActiveWorkBook.ActiveSheet;
ADataSet.Active := true;
if ADataSet.State in dsEditModes then
ADataSet.Post; ECol := Sheet.UsedRange.Columns.Count;
if EndCol <> 1 then
ECol := EndCol; ERow := Sheet.UsedRange.Rows.Count;
if EndRow <> 1 then
ERow := EndRow; for I := StartRow to ERow do
begin
ADataSet.Append;
Index := 0;
J := StartCol;
while J <= ECol do
begin
if J > ADataSet.Fields.Count + 1 then
Break;
try
ADataSet.Fields[Index].AsString := VarToStr(Sheet.Cells[I, J].Value);
except
end;
Inc(J);
Inc(Index);
end;
ADataSet.Post;
end;
end;procedure TLoadFromExcelObject.SetEndCol(const Value: Word);
begin
FEndCol := Value;
end;procedure TLoadFromExcelObject.SetEndRow(const Value: Word);
begin
FEndRow := Value;
end;procedure TLoadFromExcelObject.SetFileName(const Value: TFileName);
begin
FFileName := Value;
end;procedure TLoadFromExcelObject.SetStartCol(const Value: Word);
begin
FStartCol := Value;
end;procedure TLoadFromExcelObject.SetStartRow(const Value: Word);
begin
FStartRow := Value;
end;
destructor TLoadFromExcelObject.Destroy;
var i: integer;
begin
try
for i := 1 to Xls.Application.WorkBooks.Count - 1 do
Xls.Application.WorkBooks[i].Close(True, );
Xls.Application.Quit;
HasStarted := False;
finally
end;
inherited;
end;end.
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
with AdoQuery1 do
begin
Close;
Sql.Clear;
Sql.Add('select * from table1');
Open;
end;
然后调用如下过程即可:
procedure ExportToExcel(ADOQry: TADOQuery);
var
MyExcel: Variant;
WorkBook: OleVariant;
WorkSheet: OleVariant;
i,j:integer;
begin
try
MyExcel:=CreateOleObject('Excel.Application');
MyExcel.Application.WorkBooks.Add;
MyExcel.Caption:='将数据导入到EXCEL表中';
MyExcel.Application.Visible:=true;
WorkBook:=MyExcel.Application.workbooks[1];
worksheet:=workbook.worksheets.item[1];
except
showmessage('EXCEL不存在!');
end;
i:=1; with ADOQry do
begin
for j:=0 to FieldCount-1 do worksheet.cells[1,j+1]:=Fields[j].FieldName;
First;
while not Eof do
begin
inc(i);
for j:=0 to fieldcount-1 do
begin
worksheet.cells[i,j+1].NumberFormatLocal :='@';
worksheet.cells[i,j+1].Borders.LineStyle:=1;
worksheet.cells[i,j+1]:=Fields[j].asstring;
end;
next;
end;
end;
end;
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
会出错啊编译不了啊
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
你要修改一下就可以用
Data Source="c:\test.xls"把路径改为你的.xls的位置
...xactions改为你的工作表的名称(如sheet1$)
我试过了一定有用的 ,还可以把5.0改为8.0
EXEC master..xp_cmdshell 'bcp testpubs.dbo.table1 out
D:\My Documents\课程\数据库应用\作业\book1.xls -c -q -S
"GNETDATA/GNETDATA" -U"sa" -P""'
我想把库中数据存入excel文件中,这句话执行后无效,应该怎么写?
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions