我刚开始用DEPHI,老板让用dephi实现excel导入SQLserver,请高人看一下我写的程序出错之处,给我一个样板最好.
unit Unitx;interfaceuses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, StdCtrls, DB, ADODB, Buttons, DBTables;type
TForm1 = class(TForm)
ADOQuery1: TADOQuery;
Edit1: TEdit;
Button1: TButton;
Edit2: TEdit;
SpeedButton1: TSpeedButton;
OpenDialog1: TOpenDialog;
ADOQuery2: TADOQuery;
Label1: TLabel;
Button2: TButton;
Query1: TQuery;
procedure Button1Click(Sender: TObject);
procedure SpeedButton1Click(Sender: TObject);
procedure Button2Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;var
Form1: TForm1;implementation{$R *.dfm}procedure TForm1.Button1Click(Sender: TObject);
VAR
i,j:integer;
ExcelFileName:STRING;
s_sql:string;begin
ExcelFileName:=edit2.Text;
ADOQUERY1.ConnectionString:= 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source='
+ ExcelFileName + ';Extended Properties=Excel 8.0;Persist Security Info=False';with adoquery1 do
begin
adoquery1.Close;
adoquery1.sql.Clear;
adoquery1.sql.Add('select * from [Sheet1$]');
prepared:=true;
adoquery1.open;
end;
edit1.Text:=inttostr(adoquery1.RecordCount);
adoquery1.Close;
s_sql:='select * from student';
adoquery2.close;
adoquery2.sql.add(s_sql);
adoquery2.Open; for j:=1 to adoquery1.RecordCount do
begin
adoquery2.Append;
for i:=0 to adoquery1.FieldCount do
begin
adoquery2.Fields[0].AsVariant:=adoquery1.Fields[0].AsVariant;
end;
adoquery2.Post;
adoquery1.Next;
application.messagebox('导入成功!','',0); end;end;procedure TForm1.SpeedButton1Click(Sender: TObject);
begin
if opendialog1.Execute then
edit2.Text:=opendialog1.FileName
end;procedure TForm1.Button2Click(Sender: TObject);
begin
close;
end;end.
unit Unitx;interfaceuses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, StdCtrls, DB, ADODB, Buttons, DBTables;type
TForm1 = class(TForm)
ADOQuery1: TADOQuery;
Edit1: TEdit;
Button1: TButton;
Edit2: TEdit;
SpeedButton1: TSpeedButton;
OpenDialog1: TOpenDialog;
ADOQuery2: TADOQuery;
Label1: TLabel;
Button2: TButton;
Query1: TQuery;
procedure Button1Click(Sender: TObject);
procedure SpeedButton1Click(Sender: TObject);
procedure Button2Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;var
Form1: TForm1;implementation{$R *.dfm}procedure TForm1.Button1Click(Sender: TObject);
VAR
i,j:integer;
ExcelFileName:STRING;
s_sql:string;begin
ExcelFileName:=edit2.Text;
ADOQUERY1.ConnectionString:= 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source='
+ ExcelFileName + ';Extended Properties=Excel 8.0;Persist Security Info=False';with adoquery1 do
begin
adoquery1.Close;
adoquery1.sql.Clear;
adoquery1.sql.Add('select * from [Sheet1$]');
prepared:=true;
adoquery1.open;
end;
edit1.Text:=inttostr(adoquery1.RecordCount);
adoquery1.Close;
s_sql:='select * from student';
adoquery2.close;
adoquery2.sql.add(s_sql);
adoquery2.Open; for j:=1 to adoquery1.RecordCount do
begin
adoquery2.Append;
for i:=0 to adoquery1.FieldCount do
begin
adoquery2.Fields[0].AsVariant:=adoquery1.Fields[0].AsVariant;
end;
adoquery2.Post;
adoquery1.Next;
application.messagebox('导入成功!','',0); end;end;procedure TForm1.SpeedButton1Click(Sender: TObject);
begin
if opendialog1.Execute then
edit2.Text:=opendialog1.FileName
end;procedure TForm1.Button2Click(Sender: TObject);
begin
close;
end;end.
Create proc procname
@path varchar(800)
as
Select * into 新表名
from OPENROWSET('MICROSOFT.JET.OLEDB.4.0' ,'Excel 8.0;HDR=YES;
DATABASE='+@path+', Sheet1$)) as t
go
s1:string;
begin
screen.Cursor:=crSQLWait;
s1:='insert into table_bus '+
' (BS_NO,BS_TYPE,bs_whos,bs_mode,bs_seat,bs_soalseat,bs_drseat,bs_oiltype,bs_buycard,bs_unit,BS_BDATE,bs_edate) '+
' SELECT 车号,dbo.uf_GetFullNameCode(车型名称),dbo.uf_GetFullNameCode(所属单位),dbo.uf_GetFullNameCode(厂牌型号),核定座位,售票座位,驾驶室前排座位,dbo.uf_GetFullNameCode(燃料名称),购置证号,本外单位,启用日期,dbo.uf_ConvertNullDate(终止时间) '+
' FROM OpenDataSource(''Microsoft.Jet.OLEDB.4.0'',''Data Source="'+trim(LabeledEdit1.text)+'";Extended Properties=Excel 8.0;Persist Security Info=False'')...[Sheet1$] '+
' where 车号 not in(select bs_no from table_bus) ';
try
DataModule1.ADOConnection1.Execute(s1);
finally
screen.Cursor:=crArrow;
end;
end;adoconnection连接sql数据库,OpenDataSource取excel数据,就是上面各位的方法procedure TForm1.Button3Click(Sender: TObject);
var
oe:variant;
n1,str:string;
j:integer;
begin
if opendialog1.Execute then
opendialog1.DefaultExt:='xls';
str:=opendialog1.FileName;
oe:=CreateOleObject('Excel.Application');
oe.visible:=true;
oe.workbooks.open(str);
j:=2; //假定标题在第一行
try
with adoQuery2 do
begin
n1:='select * from ##tbl_cardgrant1 ';
close;
sql.Clear;
sql.Add(n1);
while oe.worksheets['查询结果1'].cells[j,1].text<>'' do
begin
for i:=1 to adoquery1.fieldcount do
Parameters.Items[i-1].Value:=oe.worksheets['查询结果1'].cells[j,1].text;
memo2.Text:=n1;
execsql;
j:=j+1;
end;
oe.visible:=false;
showmessage('导入完毕');
end;
except
oe.visible:=false;
showmessage('数据库错误或有重复编号,请检查');
end;
end;
ExcelApp: Variant;
excelapp:= createoleobject('Excel.Application')
ExcelApp.Cells[i,1].Value
jbno := trim(ExcelApp.Cells[i,3].Value);
empname := trim(excelapp.cells[i,2].value);
sql.Add('insert into qj_runtmp(empno,empname,jbno,startdate,enddate,starttime,endtime,starttime2,endtime2,qjreson,createdate,createuser,flag)');
sql.Add('values(:empno,:empname,:jbno,:startdate,:enddate,:starttime,:endtime,:starttime2,:endtime2,:qjreson,:createdate,:createuser,:flag)');
ADO_qj.Parameters.ParamByName('empno').Value := trim(empno);
ado_qj.Parameters.ParamByName('empname').Value := trim(empname);
ADO_qj.Parameters.ParamByName('jbno').Value := trim(jbno);procedure TForm1.Button1Click(Sender: TObject);
begin
ADOConnection1.Connected:=false;
ADOConnection1.ConnectionString:='Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\db1.mdb;Persist Security Info=False';
ADOConnection1.Execute('select * into a FROM [excel 8.0;database=C:\1.xls].[sheet1$]');
ADOConnection1.Connected:=true;
end;
完整代码如下:
unit Unit1;interfaceuses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, DB, ADODB, StdCtrls, Buttons;type
TForm1 = class(TForm)
Button1: TButton;
ADOConnection1: TADOConnection;
Edit1: TEdit;
Label1: TLabel;
SpeedButton1: TSpeedButton;
OpenDialog1: TOpenDialog;
Button2: TButton;
Edit2: TEdit;
Label2: TLabel;
procedure Button1Click(Sender: TObject);
procedure SpeedButton1Click(Sender: TObject);
procedure Button2Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;var
Form1: TForm1;implementation{$R *.dfm}procedure TForm1.Button1Click(Sender: TObject);
var
path:string;
N_TNAME:string;
begin
path:=trim(edit1.Text);
N_TNAME:=TRIM(edit2.text); adoconnection1.Execute('SELECT * into '+N_TNAME+' FROM OPENdatasource(''Microsoft.Jet.OLEDB.4.0'',''Data Source="'+path+'";User ID=Admin;Password=;Extended properties=Excel 5.0'')...sheet1$');
SHOWMESSAGE('导入成功');end;procedure TForm1.SpeedButton1Click(Sender: TObject);
begin
if opendialog1.Execute then
edit1.Text:=opendialog1.FileName;
end;procedure TForm1.Button2Click(Sender: TObject);
begin
CLOSE;
end;end.
另外,以后一定要把Delphi打对,别写成Dephi