为什么我的execl导入效率很底，请帮忙，谢谢高手了？

Question

unit Unit1;interfaceuses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, comobj,db,StdCtrls;type
  TForm1 = class(TForm)
    Edit1: TEdit;
    Button1: TButton;
    Label1: TLabel;
    OpenDialog1: TOpenDialog;
    Button2: TButton;
    Label2: TLabel;
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;var
  Form1: TForm1;implementationuses Unit3;{$R *.dfm}procedure TForm1.Button1Click(Sender: TObject);
var    eclApp,WorkBook :variant ;
begin
 Form1.OpenDialog1.Title :='Excel文件导入到数据库表';
// OpenDialog1.Options := [ofAllowMultiSelect, ofFileMustExist]; //以只读的方式 
 Form1.OpenDialog1.InitialDir :=ExtractFilePath(Application.ExeName );
 Form1.OpenDialog1.Filter := 'Excel files (*.xls)|*.xls';
  if    (Form1.OpenDialog1.Execute ) then
        //xlsFileName :=ExtractFileName(Form1.OpenDialog1.FileName)
        edit1.text := OpenDialog1.FileName //文件路径
       // edit1.text :=ExtractFileName(Form1.OpenDialog1.FileName)//只有文件名
  else
        exit;
  try
         eclApp := CreateOleObject('Excel.Application');
         WorkBook :=CreateOleObject('Excel.Sheet');
  except
         showmessage('您系统未安装MS-EXCEL');
         exit;
  end;
  try
         workBook :=eclApp.WorkBooks.add ;
         eclApp.workBooks.open(Form1.OpenDialog1.FileName );
  except
        on  EOleException do
           begin
               WorkBook.close;
               eclApp.quit;
               eclApp:=Unassigned;
             exit;
           end;
  end;
         eclApp.visible :=false;
 end;procedure TForm1.Button2Click(Sender: TObject);
var    eclApp,WorkBook :variant ;
       xlsFileName :string;
       a_FiledCount:integer;       //数据库表中的列数
       b_filedCount:integer;      //excel 文件中的 列数
       b_row :integer;           // excel 文件的行熟
       i,j :integer;
       a_flag :boolean;
       sucess_row:integer;
       sucess_rows:string;
       iss:string;
begin
          cursor:=crHourGlass;
          sucess_row:=0;
          Form1.OpenDialog1.Title :='Excel文件 导入到数据库表';
          Form1.OpenDialog1.InitialDir :=ExtractFilePath(Application.ExeName );
          if    (Form1.OpenDialog1.Execute )    then
               begin
                xlsFileName :=ExtractFileName(Form1.OpenDialog1.FileName);
                 edit1.text:=OpenDialog1.FileName;
                 end          else
                 exit  ;          try
              eclApp := CreateOleObject('Excel.Application');
              WorkBook :=CreateOleObject('Excel.Sheet');
          except
                showmessage('您系统未安装MS-EXCEL');
                 exit;
          end;
          try
              workBook :=eclApp.WorkBooks.add ;
             eclApp.workBooks.open(Form1.OpenDialog1.FileName );
          except
                on  EOleException do
                    begin
                             WorkBook.close;
                             eclApp.quit;
                             eclApp:=Unassigned;
                             exit;
                    end;
          end;
          eclApp.visible :=false;
          try   //try ..finally
            try //try  ..except
                 With  Data_Mod.table1    do
                    begin
                         close ;
                         active :=true;
                         a_FiledCount :=FieldCount;                   end;                   b_filedCount :=eclApp.ActiveSheet.UsedRange.columns.Count;//返回excel 表中的列数
                   b_row :=eclApp.activesheet.UsedRange.rows.count;        //返回excel 表中的行数                   if    (a_FiledCount <>b_FiledCount)     //当数据 表和导入的excel表中的列数不一样，说明导入的excel文件不是正确的
                   then  begin
                          showmessage('您选择导入的excel文件错误'+#13+#10+'请您重新选择');
                          WorkBook.close;
                          eclApp.quit;
                          eclApp:=Unassigned;
                          exit;
                         end
                   else   begin
                             for  i :=1 to  b_filedCount do
                                begin
                                     // if    eclApp.activesheet.cells.item[2,i].value<>Data_Mod.table1.Fields[i-1].FieldName  //判断字段名是否相等
                                     if    eclApp.activesheet.cells.item[1,i].value<>Data_Mod.table1.Fields.Fields[i-1].FieldName
                                     then  begin
                                                   showmessage('1您选择导入的excel文件错误'+#13+#10+'请您重新选择');
                                                   WorkBook.close;
                                                   eclApp.quit;
                                                   eclApp:=Unassigned;
                                                   Data_Mod.table1.Close ;
                                                   exit;
                                           end;   
                                end; //for   i:=.....  
                        end;     //end with  else                  for  i :=2 to  b_row do            //行
                      begin
                            iss:=inttostr(i-1);
                            a_flag :=Data_Mod.table1.Locate(eclApp.activesheet.cells.item[1,1],eclApp.activesheet.cells.item[i,1],[loCaseInsensitive]);
                                      if    (a_flag =true) then
                                            begin
                                               showmessage('该记录已经存在');
                                               Data_Mod.table1.Next ;
                                               continue;
                                            end
                                        else
                                           sucess_row:=sucess_row+1;                            With  Data_Mod.table1  do
                               begin
                                     close ;
                                     //TableName :=true;
                                     active :=true;
                                     Append;
                               end;
                            For j :=1 to    b_filedCount do    //列
                               begin     //开始导入数据库
                                     Data_Mod.table1.FieldByName(eclApp.activesheet.cells.item[1,j]).Value :=eclApp.activesheet.cells[i,j].value;                               end ;      //end with For j :=1 to    b_filedCount do
                         Data_Mod.table1.Post ;
                         //Data_Mod.table1.Refresh ;
                  //label2.Caption:='正在导入第'+iss+'条数据,请等待...';
                  //label2.Font.Color:=clred;
                  Form1.Font.Color:=clred; 
                  Form1.Caption:= '正在导入第'+iss+'条数据,请等待...';                  end;
                  Form1.Caption:= '';
                 sucess_rows:=inttostr(sucess_row);
                 showmessage('成功导入'+sucess_rows+'条数据');
            except
                   WorkBook.close;
                   eclApp.quit;
                   eclApp:=Unassigned;
                   Data_Mod.table1.Close ;
            end;   //end  try  except
          finally  //操作错误，退出
                   WorkBook.close;
                   eclApp.quit;
                   eclApp:=Unassigned;
                   Data_Mod.table1.Close ;
          end;end;
end.

Answer 1

用createobject方法调用execel一般效率就是低，用delphi自带的servers控件面板上的控件效率要高一些。

Answer 2

to:fengzhengren
怎么实现呢，有代码最好了！
谢谢！

Answer 3

参考http://community.csdn.net/Expert/topic/3961/3961400.xml?temp=.7742121在Form中分别放入ExcelApplication, ExcelWorkbook和ExcelWorksheet（在控件面板servers中）
在button1的click事件中写：
ExcelApplication1.Connect;
ExcelApplication1.Visible[0]:=true;
ExcelApplication1.Workbooks.Add(null,0);
Excelworkbook1.ConnectTo(ExcelApplication1.workbooks[ExcelApplication1.Workbooks.count]);
//Excelworkbook1.ConnectTo(ExcelApplication1.workbooks[1] as Excelworkbook);
Excelworksheet1.ConnectTo(ExcelWorkbook1.Sheets[1] as _worksheet);
ExcelWorkSheet1.Cells.NumberFormat :='@';//在数据为字符串类型时加这句
……//写入数据
//最后断开连接
Excelworksheet1.Disconnect;
Excelworkbook1.Disconnect;
ExcelApplication1.Disconnect;

Answer 4

如果你是SQL SERVER数据库的话直接用BCP，速度很快

Answer 5

用SQL语句就会快一些，给一个例子，希望有用
procedure TForm1.execl1Click(Sender: TObject);
var
  a:string; b:string; c:string;s:string;
begin
 SaveDialog1.Execute;
 a:= ExtractFilePath(savedialog1.FileName); c:=savedialog1.FileName;
delete(c,1,length(ExtractFilePath(c)));
  s:='Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%s;'+
  'Extended Properties=Excel 5.0;Persist Security Info=False';
  adoConnection2.Connected := false;
  adoConnection2.ConnectionString :=format(s,[a]);
  adoConnection2.Connected := true;
  s:='SELECT * into '+c+' FROM stucj IN [ODBC][ODBC;Driver=SQL Server;UID=sa;PWD=*963.;Server=zhangqiang;DataBase=myjob;]';
  try
  adoConnection2.Execute(s);
  showmessage('导出成功');
  except
  showmessage('导的过程中出现问题,仔细看看是不是文件已经存在！');
  end;
end;

Answer 6

to:huangp014(唐僧)
我按照您说的，做了，速度很快；但是每次执行程序时，都要打开一个新的EXCEL文件，请问怎样去掉自动关闭这个EXCEL文件那？
多谢各位了！！！！

Answer 7

我也是照别人教的依葫芦画瓢的，呵呵
你问的我没做过，大概可以设ExcelApplication1.Visible[0]:=false;
在保证数据都写入Excel后，给它一个默认的存放路径，取一个唯一的文件名下面是转的贴：
22) 工作表保存：
if not ExcelApplication1.ActiveWorkBook.Saved then ExcelApplication1.ActiveSheet.PrintPreview;
23) 工作表另存为：
ExcelApplication1.SaveAs( 'C:\Excel\Demo1.xls' );
24) 放弃存盘：
ExcelApplication1.ActiveWorkBook.Saved := True;

Answer 8

to:huangp014(唐僧)
为什么我的EXCEL文件成功导入结束后，进程里面怎么还有EXCEL进程（EXCEL文件没有打开），请问怎么办？
谢谢大侠了！

Answer 9

to:all
为什么我的EXCEL文件成功导入结束后，进程里面怎么还有EXCEL进程（EXCEL文件没有打开），请问怎么办？
谢谢大侠了！

Answer 10

你可以在csdn上搜索相关的帖子看看有没有满足你需求的下面的仍然是转贴，我没试过，你也许可以试试：
25) 关闭工作簿：
ExcelApplication1.WorkBooks.Close;26) 退出 Excel：
ExcelApplication1.Quit;
ExcelApplication1.Disconnect;

Answer 11

我的导入 导出方法~~~你试试~~:)很简单的~~~
procedure TForm16.Button2Click(Sender: TObject);
var path:string;
    empty:olevariant;
begin
path:=extractfilepath(application.ExeName);
empty:=emptyparam;with accessapplication1 do
  begin
     if messagedlg('确定要导入该数据表？导入将不能恢复！~',mtconfirmation,[mbyes,mbno],0)=mryes then
      begin
        connect;
        opencurrentdatabase(path+'bjmanage\'+'bj.mdb',false,'');
        visible:=true;
        docmd.TransferSpreadsheet(acimport,acspreadsheettypeexcel8,'bjinfo',path+'bakup\'+'bjinfo.xls',true,empty,empty);
        showmessage('已成功导入该数据表！~');
      end;
  end;
end;如果是导出:只要将acimport 改为acexport

Answer 12

to:wxbsjd() 
大侠：
opencurrentdatabase(path+'bjmanage\'+'bj.mdb',false,'');
visible:=true;
docmd.TransferSpreadsheet(acimport,acspreadsheettypeexcel8,'bjinfo',path+'bakup\'+'bjinfo.xls',true,empty,empty);
请问上面三句是什么意思？
还有我的数据库是SYBASE 或者 MSSQL！
谢谢了！

Answer 13

http://www.playicq.com.cn/dispdocnew.php?id=21449
去下载一个 XLSReadWriteII 对excel文件的读写，很快的，里面有示例。

Answer 14

1：
uses
 tlhelp32;
2:加入过程
procedure TerminateOLE;
var
  FSnapshotHandle:THandle;
  FProcessEntry32:TProcessEntry32;
  Ret : BOOL;
  ProcessID : integer;
  s:string;
begin
  FSnapshotHandle:=CreateToolhelp32Snapshot(TH32CS_SNAPPROCESS,0);
  FProcessEntry32.dwSize:=Sizeof(FProcessEntry32);
  Ret:=Process32First(FSnapshotHandle,FProcessEntry32);
  while Ret do
  begin
    s:=ExtractFileName(FProcessEntry32.szExeFile);
    if s='EXCEL.EXE' then
    begin
      ProcessID:=FProcessEntry32.th32ProcessID;
      TerminateProcess(OpenProcess(PROCESS_TERMINATE,false,ProcessID),1);
      s:='';
    end;
    Ret:=Process32Next(FSnapshotHandle,FProcessEntry32);
  end;
end;
3:
finally  //操作错误，退出
            WorkBook.close;
            eclApp.quit;
            eclApp:=Unassigned;
            Data_Mod.table1.Close ;
            TerminateOLE;//杀死excel进程

Answer 15

杀死进程的方法找到了！
1：
uses
 tlhelp32;
2:加入过程
procedure TerminateOLE;
var
  FSnapshotHandle:THandle;
  FProcessEntry32:TProcessEntry32;
  Ret : BOOL;
  ProcessID : integer;
  s:string;
begin
  FSnapshotHandle:=CreateToolhelp32Snapshot(TH32CS_SNAPPROCESS,0);
  FProcessEntry32.dwSize:=Sizeof(FProcessEntry32);
  Ret:=Process32First(FSnapshotHandle,FProcessEntry32);
  while Ret do
  begin
    s:=ExtractFileName(FProcessEntry32.szExeFile);
    if s='EXCEL.EXE' then
    begin
      ProcessID:=FProcessEntry32.th32ProcessID;
      TerminateProcess(OpenProcess(PROCESS_TERMINATE,false,ProcessID),1);
      s:='';
    end;
    Ret:=Process32Next(FSnapshotHandle,FProcessEntry32);
  end;
end;
3:
finally  //操作错误，退出
            WorkBook.close;
            eclApp.quit;
            eclApp:=Unassigned;
            Data_Mod.table1.Close ;
            TerminateOLE;//杀死excel进程