导入EXCEL到数据库中

 

procedure Import(SourceDS, DestDS: Tadoquery); var   sFileName, sTableName: string;   sl: TStringList;   i: integer;   conn: TADOConnection;   dialog: TOpenDialog; begin   dialog := TOpenDialog.Create(Application);   if not dialog.Execute then Exit;   try     sFileName := dialog.FileName;     conn := TADOConnection.Create(Application);     conn.Connected := False;     conn.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0;Password="";Data Source=' +       sFileName + ';Extended Properties="Excel 8.0;IMEX=1";Persist Security Info=False';     conn.Connected := True;     sl := TStringList.Create;     conn.GetTableNames(sl); // 记住 Excel 文件的 底部 必须是 sheet1   否则不能导入     sTableName := sl[0];     if Pos('$', sTableName) > 0 then sTableName := '[' + sTableName + ']';     with sourceds do     begin       connection := conn;       Close;       SQL.Clear;       SQL.Text := 'SELECT * FROM ' + sTableName;       Open;     end;     sl.Free;     if sourceds.isempty then Exit;     with destds do     begin       Close;       sql.clear;       Sql.Text := 'SELECT top 1 * FROM sp where 1=0';       Open;     end;    sourceds.First;     while not sourceds.eof do        begin       destds.Append;       destds.FieldValues['编号'] := sourceds.FieldValues['编号'];       destds.FieldValues['名称'] := sourceds.FieldValues['名称'];       destds.FieldValues['价格'] := sourceds.FieldValues['价格'];       destds.Post;       sourceds.Next;     end;     Conn.Free;     dialog.Free;     showmessage('Import success')   except     showmessage('Import fail')   end; end;