procedure TForm1.Button2Click(Sender: TObject); var tmpfdname:string; a_filedNo,a_recno ,j,k:integer; sqlstr,sqlstr1:string; begin tmpfdname:=''; OpenDialog1.Filter:='*.xls|*.xls'; ADOCexcel.Connected:=false; ADOquery1.Close; if OpenDialog1.Execute then tmpfdname:=OpenDialog1.FileName;//这是你所要操作的EXCEL文件名 if trim(tmpfdname)<>'' then begin ADOCexcel.ConnectionString:='Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+tmpfdname+';Extended Properties=EXCEL 8.0;Persist Security Info=False'; try ADOCexcel.Connected:=true; adoquery1.Close; adoquery1.sql.Clear; ADOquery1.sql.Add('select * from [sheet1$]'); //注意这里是EXCEL文的页名. adoquery1.Prepared; ADOquery1.Open; adoquery3.Close; adoquery3.sql.Clear; ADOquery3.sql.Add('select count(*) from [sheet1$]'); //注意这里是EXCEL文的页名. adoquery3.Prepared; ADOquery3.Open; a_filedNo :=adoquery1.FieldCount ; a_recno:=strtoint(adoquery3.fieldbyname('expr1000').asstring); adoquery1.First; for k :=1 to a_recno do //Form3.a_recno begin sqlstr1:= adoquery1.Fields[0].AsString; for j :=2 to a_filedNo do //转化一个记录 begin sqlstr1:=''''+sqlstr1+''''+','+''''+adoquery1.Fields[j-1].AsString+''''; end; sqlstr:='insert into IDD values('+sqlstr1+')'; adoquery2.Close; adoquery2.SQL.Clear; adoquery2.SQL.Add(sqlstr) ; adoquery2.Prepared; adoquery2.ExecSQL; adoquery1.Next; ; end; // ToolButton2.Enabled:=true; except application.MessageBox('打开Excel文件失败!','程序提示',mb_ok or mb_iconstop); end; end; ADOQUERY4.Close; ADOQUERY4.Open; end;
在数据库所在的服务端执行: SELECT * into G1C1 FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="d:\store.xls";Extended Properties="Excel 8.0;HDR=Yes;";Persist Security Info=False')...sheet1$
var tmpfdname:string;
a_filedNo,a_recno ,j,k:integer;
sqlstr,sqlstr1:string;
begin
tmpfdname:='';
OpenDialog1.Filter:='*.xls|*.xls';
ADOCexcel.Connected:=false;
ADOquery1.Close;
if OpenDialog1.Execute then
tmpfdname:=OpenDialog1.FileName;//这是你所要操作的EXCEL文件名
if trim(tmpfdname)<>'' then
begin
ADOCexcel.ConnectionString:='Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+tmpfdname+';Extended Properties=EXCEL 8.0;Persist Security Info=False';
try
ADOCexcel.Connected:=true;
adoquery1.Close;
adoquery1.sql.Clear;
ADOquery1.sql.Add('select * from [sheet1$]'); //注意这里是EXCEL文的页名.
adoquery1.Prepared;
ADOquery1.Open;
adoquery3.Close;
adoquery3.sql.Clear;
ADOquery3.sql.Add('select count(*) from [sheet1$]'); //注意这里是EXCEL文的页名.
adoquery3.Prepared;
ADOquery3.Open; a_filedNo :=adoquery1.FieldCount ;
a_recno:=strtoint(adoquery3.fieldbyname('expr1000').asstring);
adoquery1.First;
for k :=1 to a_recno do //Form3.a_recno
begin
sqlstr1:= adoquery1.Fields[0].AsString;
for j :=2 to a_filedNo do //转化一个记录
begin
sqlstr1:=''''+sqlstr1+''''+','+''''+adoquery1.Fields[j-1].AsString+''''; end;
sqlstr:='insert into IDD values('+sqlstr1+')';
adoquery2.Close;
adoquery2.SQL.Clear;
adoquery2.SQL.Add(sqlstr) ;
adoquery2.Prepared;
adoquery2.ExecSQL;
adoquery1.Next; ;
end;
// ToolButton2.Enabled:=true;
except
application.MessageBox('打开Excel文件失败!','程序提示',mb_ok or mb_iconstop);
end;
end;
ADOQUERY4.Close;
ADOQUERY4.Open;
end;
SELECT * into G1C1 FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="d:\store.xls";Extended Properties="Excel 8.0;HDR=Yes;";Persist Security Info=False')...sheet1$
1。SQLServer2000有一个导入导出工具
2。在其中的数据源中选择Excel引擎
3。然后选择你的Excel文件4,下一步
4。然后在目的中选择选择你的数据库,下一步
5。选择从源数据库复制表和视图,下一步
6。选择你要复制的Sheet,然后在目的中写G1C1,然后点转换中的按钮
7。在转换中选择转换的字段和类型
8。继续下一步到结束。
9。OK,完成了