在Delphi中如何快速的导出Excel?

Question

在Delphi中如何快速的导出Excel?,
我这后导出,总是一条条的导出,导出来很慢,有的计算机甚至出现死机的现象,请问大家有什么快速的导出方法.?

Answer 1

fastreport 中可以直接导出execl 具体哪个属性忘记了好像是xml

Answer 2

导出excel貌似就只能一条条地导出记录吧。

Answer 3

procedure Tfm_excel.bt_excelClick(Sender: TObject);var
i:integer;
xls:Olevariant;
begin
   with datam.aq_excel do begin
    close;
    SQL.Clear;
    SQL.Add('select * from zsb ');
    Open ;
   end;
    if datam.aq_excel.RecordCount =0 then
    begin
     messageBox(handle,'这个时间段没有数据!',pchar(caption),mb_IconInformation+mb_ok);
     exit;
    end;
try
 xls:=CreateOleObject('Excel.Application');//调用excel
 xls.workbooks.add;//增加一个表
 xls.visible:=true;
 xls.cells(1,1):='出入记录';
 
 //----------------------------------------------------------------------------
 xls.cells(2,1):='序号';
 xls.cells(2,2):='发货单位';
 xls.cells(2,3):='收货单位';
 xls.cells(2,4):='承运单位';
 //xls.cells(2,5):='地区标志';
 xls.cells(2,5):='车辆号码';
 xls.cells(2,6):='货物名称';
 xls.cells(2,7):='货物规格';
 xls.cells(2,8):='毛重';
 xls.cells(2,9):='皮重';
 xls.cells(2,10):='净重';
 xls.cells(2,11):='扣除';
 xls.cells(2,12):='日期';
 xls.cells(2,13):='时间';
 xls.cells(2,14):='司磅员';
//----------------------------------------------------------------------
 i:=3;
 datam.aq_excel.First;
 while not datam.aq_excel.Eof  do //是否最后
       begin
        xls.cells(i,1):=datam.aq_excel.FieldByName('pjh').AsString ;
        xls.cells(i,2):=datam.aq_excel.FieldByName('fhdw').AsString;//'发货单位'
        xls.cells(i,3):=datam.aq_excel.FieldByName('shdw').AsString;//'承运单位';
        xls.cells(i,4):=datam.aq_excel.FieldByName('cydw').AsString;//'地区标志';
        //xls.cells(i,5):=datam.aq_excel.FieldByName('dqbz').AsString;//'车辆号码';
        xls.cells(i,5):=datam.aq_excel.FieldByName('clhm').AsString;//'发货单位';
        xls.cells(i,6):=datam.aq_excel.FieldByName('hwmc').AsString;//'货物名称';
        xls.cells(i,7):=datam.aq_excel.FieldByName('hwgg').Asstring;//'毛重'
        xls.cells(i,8):=datam.aq_excel.FieldByName('mz').AsFloat;//'净重';
        xls.cells(i,9):=datam.aq_excel.FieldByName('pz').AsFloat;//'皮重';
        xls.cells(i,10):=datam.aq_excel.FieldByName('jz').AsFloat;//'货物标重';
        xls.cells(i,11):=datam.aq_excel.FieldByName('kouc').AsFloat;//'过磅人员';
        xls.cells(i,12):=datam.aq_excel.FieldByName('rq').Asstring ;
        xls.cells(i,13):=datam.aq_excel.FieldByName('sj').Asstring;//'误差';
        xls.cells(i,14):=datam.aq_excel.FieldByName('gby').Asstring;//'扣率';
        i:=i+1;
        datam.aq_excel.Next ;
        end;
        i:=i+1;
except
       messageBox(handle,'导出失败,excel未安装或系统内部错误!',pchar(caption),mb_IconError+mb_ok);
       exit;
end;
datam.aq_excel.Close;
end;

Answer 4

Ehlib控件包，
用DBGridEh控件，
导出的excel相当快的！

Answer 5

同意楼上的，Ehlib控件包，
用DBGridEh控件，导出的excel相当快的！虽然导出的Excel版本低了点，但还行。

Answer 6

  慢的。但格式比較容易控制procedure saveToExcel(); 
var 
Eclapp,workbook:variant; 
i,n:integer; 
begin 
if not adoquery1.Active then exit; 
if adoquery1.RecordCount<=0 then exit; if application.MessageBox('确认导出excel表吗？','提示',mb_okcancel+mb_iconinformation)=idcancel then exit; 
Eclapp := createoleobject('Excel.Application'); 
Eclapp.workbooks.add; 
for i:=0 to dbgrid2.FieldCount-1 do 
begin 
Eclapp.cells[1,i+1]:=dbgrid2.Columns[i].Title.Caption; 
end; 
Eclapp.cells[1,5]:='签字'; adoquery1.First; 
n:=2; 
while not adoquery1.Eof do 
begin 
eclapp.cells[n,1] := adoquery1.Fields[0].AsString; 
eclapp.cells[n,2] := adoquery1.Fields[1].AsString; 
eclapp.cells[n,3] := adoquery1.Fields[2].AsString; 
eclapp.cells[n,4] := adoquery1.Fields[4].AsString; 
eclapp.cells[n,6] :=' '; 
inc(n); 
adoquery1.Next; 
end; eclapp.cells[n,1] := '满足条件记录的总数为：'+inttostr(adoquery1.RecordCount)+'条'; 
application.MessageBox('数据导出完成!','提示',mb_ok+mb_iconinformation); 
eclapp.visible := true; end; 

Answer 7

用cxgrid控件uses
  cxGridExportLink;
procedure TRES_DCC_ECRN_F.btnSB_PrintClick(Sender: TObject);
begin
  inherited;
  if SaveDialog1.Execute then
  begin
    ExportGridToExcel(SaveDialog1.FileName,cxGrid1,True,True,True,'xls');
    MessageDlg('數據導出成功!',mtInformation,[mbOK],0);
  end;
end;

Answer 8

http://blog.csdn.net/jmlei/archive/2008/10/13/3068916.aspx

Answer 9

使用 XML格式输出，快且容易控制。

Answer 10

用ole方式调用excelapplication速度肯定慢。
参照excel文件的格式规范BIFF8格式，直接写二进制excel文件，速度暴快，网上很多这样的代码的。
其实fastreport,enhlib这些控件就是给予excel二进制文件格式直接写的，并不是调用excel OLE服务写。
有很多三方控件比如xlswriter就是直接读写excel二进制文件实现电子表格的快速读写，不过这样操作注意excel97以后的的版本格式和excel95的文件格式是不同的

Answer 11

使用CReport控件，导出速度非常快，只是要给他一个固定的存储路径
要改位置需要在Excel里另存为

Answer 12

用Grid++report导出Excel速度很快

Answer 13

导出csv文件
1,2,3
4,5,6
就这样导出的EXCL比OLE快100倍以上.下面是C++ builder的代码        char * sFileName;
        char FileName[200];
        sFileName=filename1.c_str();//"./mytest.csv";
        sprintf(FileName,"%s.csv\0", sFileName);
     
        ofp=fopen(FileName,"a+");
        if(ofp==NULL)
        {
                ShowMessage("文件无法创建！！");
        }
        // fputs("<table border='1' style='border-style: solid'>\n", ofp);
         //---------------------
         fputs("\n", ofp);
             ss="编号";//IntToStr(vehno);
            // fprintf(ofp, "<td height=24 >%s</td>\n", ss.c_str());
             fprintf(ofp, "%s,", ss.c_str());
             ss="时间";//IntToStr(speed);
            // fprintf(ofp, "<td height=24 >%s</td>\n", ss.c_str());
            fprintf(ofp, "%s,", ss.c_str());
             ss="轴数";//IntToStr(speed);
            // fprintf(ofp, "<td height=24 >%s</td>\n", ss.c_str());
             fprintf(ofp, "%s,", ss.c_str());
             ss="速度";//IntToStr(weight);
             //fprintf(ofp, "<td height=24 >%s</td>\n", ss.c_str());
             fprintf(ofp, "%s,", ss.c_str());
             ss="重量";//IntToStr(weight);
            // fprintf(ofp, "<td height=24 >%s</td>\n", ss.c_str());
             fprintf(ofp, "%s,", ss.c_str());
            for(int ii=0;ii<disveh.axlenum;ii++)
            {
              ss="轴"+IntToStr(ii+1);//disveh.LAxle[ii].weight+disveh.RAxle[ii].weight);
              //fprintf(ofp, "<td height=24 >%s</td>\n", ss.c_str());
              fprintf(ofp, "%s,", ss.c_str());
            }
            for(int ii=disveh.axlenum;ii<=7;ii++)
            {
             ss="轴"+IntToStr(ii+1);;
            // fprintf(ofp, "<td height=24 >%s</td>\n", ss.c_str());
            fprintf(ofp, "%s,", ss.c_str());
            }
            ss="平均";//IntToStr(weight);
            // fprintf(ofp, "<td height=24 >%s</td>\n", ss.c_str());
            fprintf(ofp, "%s,", ss.c_str());
             ss="误差";//IntToStr(weight);
           //  fprintf(ofp, "<td height=24 >%s</td>\n", ss.c_str());
           fprintf(ofp, "%s,", ss.c_str());
         //---------------------------------
         DBGrid1->DataSource->DataSet->Open();
         DBGrid1->DataSource->DataSet->First();
         while(!DBGrid1->DataSource->DataSet->Eof)
          {
            fflush(ofp);
            fputs("\n", ofp);
             String ss;
            int vehno,speed,axlenum;
            long weight;
            vehno=DBGrid1->DataSource->DataSet->FieldByName("vehno")->AsInteger;
            speed=DBGrid1->DataSource->DataSet->FieldByName("speed")->AsInteger;
            weight=DBGrid1->DataSource->DataSet->FieldByName("weight")->AsInteger;
            AnsiString *sp=new AnsiString((char*)&disveh,sizeof(vehdata));
            AnsiString d=*sp;
            d=DBGrid1->DataSource->DataSet->FieldByName("AXLEDETAIL")->AsString;
   // int vehtype=DBGrid1->DataSource->DataSet->FieldByName("VEHTYPE")->AsInteger;
            memcpy((void*)&disveh,d.c_str(),d.Length());
            //delete sp;
             ss=IntToStr(vehno);
            // fprintf(ofp, "<td height=24 >%s</td>\n", ss.c_str());
            fprintf(ofp, "%s,", ss.c_str());
             ss=DBGrid1->DataSource->DataSet->FieldByName("DateTime")->AsString;
             //fprintf(ofp, "<td height=24 >%s</td>\n", ss.c_str());
             fprintf(ofp, "%s,", ss.c_str());
             ss=IntToStr(disveh.axlenum);
             //fprintf(ofp, "<td height=24 >%s</td>\n", ss.c_str());
             fprintf(ofp, "%s,", ss.c_str());
             ss=IntToStr(speed);
             //fprintf(ofp, "<td height=24 >%s</td>\n", ss.c_str());
             fprintf(ofp, "%s,", ss.c_str());
             ss=IntToStr(int(weight));
            // fprintf(ofp, "<td height=24 >%s</td>\n", ss.c_str());
             fprintf(ofp, "%s,", ss.c_str());
            for(int ii=0;ii<disveh.axlenum;ii++)
            {
              //ss=IntToStr(int(disveh.LAxle[ii].weight*par1)+int(disveh.RAxle[ii].weight*par2));
              float whelp;
             // whelp=weightcorrect(disveh.LAxle[ii].weight*par1);
             // whelp=whelp*1.042;
             // disveh.LAxle[ii].weight=disveh.LAxle[ii].weight*par1*whelp;
             // whelp=weightcorrect(disveh.RAxle[ii].weight*par2);
             // whelp=whelp*1.042;
             // disveh.RAxle[ii].weight=disveh.RAxle[ii].weight*par2*whelp;
              ss=IntToStr(int(disveh.LAxle[ii].weight)+int(disveh.RAxle[ii].weight));
             // fprintf(ofp, "<td height=24 >%s</td>\n", ss.c_str());
             fprintf(ofp, "%s,", ss.c_str());
            }
            for(int ii=disveh.axlenum;ii<=7;ii++)
            {
             //ss="0";
            /*  float shelp;
              if(ii==disveh.axlenum)
              {
              shelp=speedcorrect(disveh.LAxle[0].speed);
              disveh.LAxle[0].weight=disveh.LAxle[0].weight*shelp;
              shelp=speedcorrect(disveh.RAxle[0].speed);
              disveh.RAxle[0].weight=disveh.RAxle[0].weight*shelp;
              ss=IntToStr(int(disveh.LAxle[0].weight)+int(disveh.RAxle[0].weight));
              }
              else if(ii==(disveh.axlenum+1))
              {
                shelp=speedcorrect(disveh.LAxle[1].speed);
              disveh.LAxle[1].weight=disveh.LAxle[1].weight*shelp;
              shelp=speedcorrect(disveh.RAxle[1].speed);
              disveh.RAxle[1].weight=disveh.RAxle[1].weight*shelp;
              ss=IntToStr(int(disveh.LAxle[1].weight)+int(disveh.RAxle[1].weight));
              }
              else  */
               ss="";            // fprintf(ofp, "<td height=24 >%s</td>\n", ss.c_str());
            fprintf(ofp, "%s,", ss.c_str());
            }
             ss="";//IntToStr(weight);
            // fprintf(ofp, "<td height=24 >%s</td>\n", ss.c_str());
            fprintf(ofp, "%s,", ss.c_str());
             ss="";//IntToStr(weight);
            // fprintf(ofp, "<td height=24 >%s</td>\n", ss.c_str());
            fprintf(ofp, "%s,", ss.c_str());
            // fputs("</tr>\n", ofp);
             DBGrid1->DataSource->DataSet->Next();
          }
       // fputs("</table>\n", ofp);
        fclose(ofp);
   }
   MessageBox(0, "导出数据完成！","DBGrid2Excel", MB_OK | MB_ICONINFORMATION);