EXCEL sql 导入

来源：互联网发布：三星电视网络设置方法编辑：程序博客网时间：2024/04/29 13:08

procedure TForm1.Button1Click(Sender: TObject);
  var
  sqlstr,connstr,sheetName,filename:string;
begin
  FileName :='d:\d.xls' ;//EXCel文件名
  SheetName :='sheet1' ; //ExceL表名
  ADOQuery1.Close ;
  ADOQuery1.SQL.Clear ;
  ConnStr :='Provider=Microsoft.Jet.OLEDB.4.0;Data Source=' + FileName + ';Extended Properties=Excel 8.0;Persist Security Info=False';
  ADOQuery1.ConnectionString :=ConnStr ;
  SqlStr :='Select * from ' +'[' +SheetName + '$'+']';  //打開表
  ADOQuery1.SQL.Add(SqlStr) ;
  ADOQuery1.Prepared ;
  ADOQuery1.Open ;
end;

end.

用delphi如何实现将excel数据导入sql server 2000
--------------------------------------------------------------------------------
DataSet.SQL.Clear;
  sqlStr := 'SELECT * into #dcb_TempMend FROM OpenDataSource(' + #39 + 'Microsoft.Jet.OLEDB.4.0'+
  #39 +','+ #39 + 'Data Source=' + filePath + filename  + ';User ID=Admin;Password=;'+
  'Extended properties=Excel 8.0' + #39 + ')...' + copy(filename,1,pos('.',filename)-1) + '$' ;
  DataSet.SQL.Add(sqlStr);
--------------------------------------------------------------------------------
procedure TForm1.Button1Click(Sender: TObject);
begin
  ADOConnection1.Connected:=False;
  ADOConnection1.ConnectionString:='Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\yourtablename;Persist Security Info=False';
  ADOConnection1.Connected:=True;
  ADOConnection1.Execute('SELECT * into t1 FROM t2 IN [ODBC] [ODBC;Driver=SQL Server;UID=sa;PWD=;Server=(LOCAL);DataBase=x;]');
end;

用CreateOleObject( 'Excel.Application' );打开EXCEL文件，读入想要的单元格内容，再通过数据库控件写入数据库！看看下面代码：
function import_abclib_from_excel(Gau:TGauge;var emsg:string;var errorrow:integer;total,stnum:integer):integer;
var
  col:integer;
  temp:string;
  item_input:array[1..MAX_ABCLIB_COLS]of string;
  libno:longint;
  libtype:integer;
  libname,libfirst,liblast,liblist:string;
begin
emsg:='';
result:=0;
ExcelApp.WorkSheets[2].Activate;
while true do
begin
  // 读取一行记录
  for col:=1 to MAX_ABCLIB_COLS do
  begin
   temp:=create_excelrowcol_name(result+2,col);
   item_input[col]:=trim(get_excel_value(temp));
  end;
  if item_input[ABCLIBNUM_POS]='' then break;//导入完毕
  libno:=strtoint(item_input[LIBNO_POS]);
  libname:=item_input[LIBNAME_POS];
  libtype:=0;
  if item_input[LIBTYPE_POS]='输入' then  libtype:=1;
  libfirst:=item_input[LIBFIRST_POS];
  liblast:=item_input[LIBLAST_POS];
  liblist:=item_input[LIBLIST_POS];
  liblist:=get_liblist(item_input[LIBLIST_POS]);
  if not insert_abclib(libno,libname,libtype,libfirst,liblast,liblist,emsg) then
  begin
   errorrow:=result+2;
   exit;
  end;
  result:=result+1;
  Gau.Progress:=(result+stnum)*100 div total;
end;
end;