导出到EXCEL（列不固定）

function cov(str:string):string;
var
 ss:string;
begin
 if str='' then
  result:='';
 ss:='';
 while pos(',',str)>0 do
 begin
  ss:=ss+',"'+copy(str,1,pos(',',str)-1)+'"';
  delete(str,1,pos(',',str));
 end;
 ss:=ss+',"'+str+'"';
 delete(ss,1,1);
 result:=ss;
end;

var
 tmpcom,tmp:tinterskyquery;
 msexcel:variant;
 msexcelworkbook:variant;
 msexcelworksheet:variant;
 //zdzitem:ZdzItems;
 pictures:tstringlist;
 filename,zm,cm,date1,date2,rq,khlist,nxlist,wxlist:string;
 intnumber,i,j,m,n,p,q,c,d,i1,j1:integer;
 nxje,wxje,hl:real;
 fwidth,fheight:double;  
 inti,intj:integer;
 int6,int7,int8,int10,int12:integer;
 //pgs:TcxDialogs;
begin

 
 tmpcom:=tinterskyquery.Create(nil);
 IApplication.DB_Initialize(tmpcom);
 tmp:=tinterskyquery.Create(nil);
 IApplication.DB_Initialize(tmp);
 
 
  if tmpcom.Active then tmpcom.close;
  tmpcom.sql.text:='select * from bj where number=:intnumber';
  tmpcom.parambyname('intnumber').asinteger:=self.Number;
  tmpcom.open;
 IDialogs.showprogress('畅想软件','正在生成Quotation..');
 filename:=tapppath.AppPath+'data/博朗/addonfiles/Quotation.xls';
 if filename<>'' then
 begin
   msexcel:=CreateOleObject('Excel.Application');
   IDialogs.changeprogress(5);
   msexcelworkbook:=msexcel.workbooks.open(filename);
   IDialogs.changeprogress(15);
   msexcelworksheet:=msexcel.workbooks[1].worksheets[1];
   msexcelworksheet.activate;
 //  try
   filename:=tapppath.AppPath+'data/博朗/addonfiles/temp.xls';
   if fileexists(filename) then deletefile(filename);
   msexcelworkbook.saveas(filename);
   IDialogs.changeprogress(20);
   if tmpcom.active then tmpcom.close;
   tmpcom.sql.text:='select count(bjsheet.number) as counter from bjsheet where bjsheet.father=:intnumber';
   tmpcom.parambyname('intnumber').asinteger:=self.Number;
   tmpcom.open;
   i:=tmpcom.FieldByName('counter').AsInteger;  
   i1:=0;
   if (i-11)>0 then
    i1:=i-11;
    //厚度个数
    //0.6
    inti:=0;
   if tmpcom.active then tmpcom.close;
   tmp.SQL.text:='select count(cpjg.number) as counter from cpjg,bjsheet where bjsheet.father=:intnumber and cpjg.father=bjsheet.number and cpjg.hd=0.6';
   tmp.parambyname('intnumber').asinteger:=self.Number;
   tmp.open; 
   intj:=tmp.FieldByName('counter').AsInteger;
   if intj>0 then
   begin
   inti:=inti+1;
   int6:=1;
   end
   else
   begin 
   int6:=0;
   end; 
      //0.7
   if tmpcom.active then tmpcom.close;
   tmp.SQL.text:='select count(cpjg.number) as counter from cpjg,bjsheet where bjsheet.father=:intnumber and cpjg.father=bjsheet.number and cpjg.hd=0.7';
   tmp.parambyname('intnumber').asinteger:=self.Number;
   tmp.open; 
   intj:=tmp.FieldByName('counter').AsInteger;
   if intj>0 then
   begin
   inti:=inti+1;
   int7:=1;
   end
   else
   begin 
   int7:=0;
   end; 
      //0.8
   if tmpcom.active then tmpcom.close;
   tmp.SQL.text:='select count(cpjg.number) as counter from cpjg,bjsheet where bjsheet.father=:intnumber and cpjg.father=bjsheet.number and cpjg.hd=0.8';
   tmp.parambyname('intnumber').asinteger:=self.Number;
   tmp.open; 
   intj:=tmp.FieldByName('counter').AsInteger;
   if intj>0 then
   begin
   inti:=inti+1;
   int8:=1;
   end
   else
   begin 
   int8:=0;
   end; 
      //1.0
   if tmpcom.active then tmpcom.close;
   tmp.SQL.text:='select count(cpjg.number) as counter from cpjg,bjsheet where bjsheet.father=:intnumber and cpjg.father=bjsheet.number and cpjg.hd=1.0';
   tmp.parambyname('intnumber').asinteger:=self.Number;
   tmp.open; 
   intj:=tmp.FieldByName('counter').AsInteger;
   if intj>0 then
   begin
   inti:=inti+1;
   int10:=1;
   end
   else
   begin 
   int10:=0;
   end;   
      //1.2
   if tmpcom.active then tmpcom.close;
   tmp.SQL.text:='select count(cpjg.number) as counter from cpjg,bjsheet where bjsheet.father=:intnumber and cpjg.father=bjsheet.number and cpjg.hd=1.2';
   tmp.parambyname('intnumber').asinteger:=self.Number;
   tmp.open; 
   intj:=tmp.FieldByName('counter').AsInteger;
   if intj>0 then
   begin
   inti:=inti+1;
   int12:=1;
   end
   else
   begin 
   int12:=0;
   end;   
    ////////////   
   msexcel.Sheets('Sheet1').Select;
   msexcel.Sheets('Sheet1').Activate;
   //画表格                        
    n:=i;
    p:=i;
    q:=10;
  //  c:=1;
    msexcel.Sheets('Sheet1').Select;
    //主表
    if tmp.Active then tmp.Close;
    tmp.SQL.text:='select * from bj where number=:intnumber';
    tmp.parambyname('intnumber').asinteger:=self.Number;
    tmp.Open; 
    msexcelworksheet.Cells[6,1].Value:=trim(tmp.FieldByName('bjbz').AsString);
    //从表头
//    msexcelworksheet.Range['A'+inttostr(q)+':'+'A'+inttostr(q+1)].Merge;
//    msexcelworksheet.Cells[inttostr(q),1].Value:='Items';
//    msexcelworksheet.Range['B'+inttostr(q)+':'+'B'+inttostr(q+1)].Merge;
//    msexcelworksheet.Cells[inttostr(q),2].Value:='Size';
//    msexcelworksheet.Range['C'+inttostr(q)+':'+'C'+inttostr(q+1)].Merge;
//    msexcelworksheet.Cells[inttostr(q),3].Value:='Depth';
//   
//    if inti=1 then
//        BEGIN 
//       // msexcelworksheet.Range['D'+inttostr(q)+':'+'E'+inttostr(q)].Merge;
//        msexcelworksheet.Cells[inttostr(q),4].Value:='Price(USD)';
//        msexcelworksheet.Range['E'+inttostr(q)+':'+'F'+inttostr(q)].Merge;
//        msexcelworksheet.Cells[inttostr(q),4].Value:='Price(USD)';
//        msexcelworksheet.Range['G'+inttostr(q)+':'+'H'+inttostr(q)].Merge;
//        msexcelworksheet.Cells[inttostr(q),4].Value:='Price(USD)';
//        END;
    //从表
    if tmp.Active then tmp.Close;
    tmp.SQL.text:='select * from bjsheet where father=:intnumber';
    tmp.parambyname('intnumber').asinteger:=self.Number;
    tmp.Open;
    while (tmp.EOF=false) and (p>0) do
     begin              
     //--填充
     begin 
      msexcelworksheet.Cells[inttostr(2+q),1].Value:=trim(tmp.FieldByName('cpbh').AsString);
      msexcelworksheet.Cells[inttostr(2+q),2].Value:=trim(tmp.FieldByName('cpgg').AsString);
      msexcelworksheet.Cells[inttostr(2+q),3].Value:=trim(tmp.FieldByName('cpgg2').AsString);
      //if inti=1 then
      begin 
      msexcelworksheet.Cells[inttostr(2+q),4+inti].Value:=trim(tmp.FieldByName('wxzl').AsString);
      msexcelworksheet.Cells[inttostr(2+q),5+inti].Value:=tmp.FieldByName('wxcd').AsString+'*'+tmp.FieldByName('wxkd').AsString+'*'+tmp.FieldByName('wxgd').AsString;
      msexcelworksheet.Cells[inttostr(2+q),6+inti].Value:=trim(tmp.FieldByName('zl20').AsString);
      msexcelworksheet.Cells[inttostr(2+q),7+inti].Value:=trim(tmp.FieldByName('zl40').AsString);
      end;  
     //                               
     p:=p-1;
     q:=q+1;
     //IDialogs.changeprogress(20+q*int(80/(i+j)));
     end;
     tmp.Next;
    end; 
    //0.6价格
    if int6=1 then
    begin
    p:=i;
    q:=10;
    if tmp.Active then tmp.Close;
    tmp.SQL.text:='select * from cpjg,bjsheet where bjsheet.father=:intnumber and cpjg.father=bjsheet.number and cpjg.hd=0.6';
    tmp.parambyname('intnumber').asinteger:=self.Number;
    tmp.Open;
    msexcelworksheet.Cells[inttostr(1+q),4].Value:='0.6mm';
    msexcelworksheet.Cells[q,8+inti].Value:='0.6mm(kg)';
    while (tmp.EOF=false) and (p>0) do
     begin              
     //--填充
     begin 
      msexcelworksheet.Cells[inttostr(2+q),4].Value:=trim(tmp.FieldByName('dj').AsString);
     
      begin 
      msexcelworksheet.Cells[inttostr(2+q),8+inti].Value:=trim(tmp.FieldByName('jz').AsString);
      msexcelworksheet.Cells[inttostr(2+q),9+inti].Value:=trim(tmp.FieldByName('mz').AsString);
      end;
     
     //                               
     p:=p-1;
     q:=q+1;
     //IDialogs.changeprogress(20+q*int(80/(i+j)));
     end;
     tmp.Next;
    end;  
    end;  
    //0.7价格
    if int7=1 then
    begin
    p:=i;
    q:=10;
    if tmp.Active then tmp.Close;
    tmp.SQL.text:='select * from cpjg,bjsheet where bjsheet.father=:intnumber and cpjg.father=bjsheet.number and cpjg.hd=0.7';
    tmp.parambyname('intnumber').asinteger:=self.Number;
    tmp.Open; 
    msexcelworksheet.Cells[inttostr(1+q),4+int6].Value:='0.7mm';
    msexcelworksheet.Cells[q,8+inti+int6*2].Value:='0.7mm(kg)';
    while (tmp.EOF=false) and (p>0) do
     begin              
     //--填充
     begin 
      msexcelworksheet.Cells[inttostr(2+q),4+int6].Value:=trim(tmp.FieldByName('dj').AsString);
    
      begin 
      msexcelworksheet.Cells[inttostr(2+q),8+inti+int6*2].Value:=trim(tmp.FieldByName('jz').AsString);
      msexcelworksheet.Cells[inttostr(2+q),9+inti+int6*2].Value:=trim(tmp.FieldByName('mz').AsString);
      end;
     
     //                               
     p:=p-1;
     q:=q+1;
     //IDialogs.changeprogress(20+q*int(80/(i+j)));
     end;
     tmp.Next;
    end; 
    end;
    //0.8价格
    if int8=1 then
    begin
    p:=i;
    q:=10;
    if tmp.Active then tmp.Close;
    tmp.SQL.text:='select * from cpjg,bjsheet where bjsheet.father=:intnumber and cpjg.father=bjsheet.number and cpjg.hd=0.8';
    tmp.parambyname('intnumber').asinteger:=self.Number;
    tmp.Open;        
    msexcelworksheet.Cells[inttostr(1+q),4+int6+int7].Value:='0.8mm';
    msexcelworksheet.Cells[q,8+inti+int6*2+int7*2].Value:='0.8mm(kg)';
    while (tmp.EOF=false) and (p>0) do
     begin              
     //--填充
     begin 
      msexcelworksheet.Cells[inttostr(2+q),4+int6+int7].Value:=trim(tmp.FieldByName('dj').AsString);
  
      begin 
      msexcelworksheet.Cells[inttostr(2+q),8+inti+int6*2+int7*2].Value:=trim(tmp.FieldByName('jz').AsString);
      msexcelworksheet.Cells[inttostr(2+q),9+inti+int6*2+int7*2].Value:=trim(tmp.FieldByName('mz').AsString);
      end;
     
     //                               
     p:=p-1;
     q:=q+1;
     //IDialogs.changeprogress(20+q*int(80/(i+j)));
     end;
     tmp.Next;
    end;      
    end;
    //1.0价格 
    if int10=1 then
    begin
    p:=i;
    q:=10;
    if tmp.Active then tmp.Close;
    tmp.SQL.text:='select * from cpjg,bjsheet where bjsheet.father=:intnumber and cpjg.father=bjsheet.number and cpjg.hd=1.0';
    tmp.parambyname('intnumber').asinteger:=self.Number;
    tmp.Open;
    msexcelworksheet.Cells[inttostr(1+q),4+int6+int7+int8].Value:='1.0mm';
    msexcelworksheet.Cells[q,8+inti+int6*2+int7*2+int8*2].Value:='1.0mm(kg)';
    while (tmp.EOF=false) and (p>0) do
     begin              
     //--填充
     begin 
      msexcelworksheet.Cells[inttostr(2+q),4+int6+int7+int8].Value:=trim(tmp.FieldByName('dj').AsString);
     
      begin 
      msexcelworksheet.Cells[inttostr(2+q),8+inti+int6*2+int7*2+int8*2].Value:=trim(tmp.FieldByName('jz').AsString);
      msexcelworksheet.Cells[inttostr(2+q),9+inti+int6*2+int7*2+int8*2].Value:=trim(tmp.FieldByName('mz').AsString);
      end;
     
     //                               
     p:=p-1;
     q:=q+1;
     //IDialogs.changeprogress(20+q*int(80/(i+j)));
     end;
     tmp.Next;
    end; 
    end;
    //1.2价格 
    if int12=1 then
    begin
    p:=i;
    q:=10;
    if tmp.Active then tmp.Close;
    tmp.SQL.text:='select * from cpjg,bjsheet where bjsheet.father=:intnumber and cpjg.father=bjsheet.number and cpjg.hd=1.2';
    tmp.parambyname('intnumber').asinteger:=self.Number;
    tmp.Open;
    msexcelworksheet.Cells[inttostr(1+q),4+int6+int7+int8+int10].Value:='1.2mm';
    msexcelworksheet.Cells[q,8+inti+int6*2+int7*2+int8*2+int10*2].Value:='1.2mm(kg)';
    while (tmp.EOF=false) and (p>0) do
     begin              
     //--填充
     begin 
      msexcelworksheet.Cells[inttostr(2+q),4+int6+int7+int8+int10].Value:=trim(tmp.FieldByName('dj').AsString);
     
      begin 
      msexcelworksheet.Cells[inttostr(2+q),8+inti+int6*2+int7*2+int8*2+int10*2].Value:=trim(tmp.FieldByName('jz').AsString);
      msexcelworksheet.Cells[inttostr(2+q),9+inti+int6*2+int7*2+int8*2+int10*2].Value:=trim(tmp.FieldByName('mz').AsString);
      end;
     
     //                               
     p:=p-1;
     q:=q+1;
     //IDialogs.changeprogress(20+q*int(80/(i+j)));
     end;
     tmp.Next;
    end;
    end;
   //---

   //---
   //金额合计
   {if tmp.active then tmp.close;
   tmp.sql.text:='select sum(khshsheet.syje) as nxje from khsh left outer join khshsheet on(khshsheet.father=khsh.number) where khsh.khjc in ('+nxlist+') and (khsh.shrq>=:date1 and khsh.shrq<=:date2)';
   tmp.parambyname('date1').AsString:=date1;
   tmp.parambyname('date2').AsString:=date2;
   tmp.open;
   nxje:=tmp.fieldbyname('nxje').asfloat; 
   if tmp.active then tmp.close;
   tmp.sql.text:='select sum(khshsheet.syje) as wxje from khsh left outer join khshsheet on(khshsheet.father=khsh.number) where khsh.khjc in ('+wxlist+') and (khsh.shrq>=:date1 and khsh.shrq<=:date2)';
   tmp.parambyname('date1').AsString:=date1;
   tmp.parambyname('date2').AsString:=date2;
   tmp.open;
   wxje:=tmp.fieldbyname('wxje').asfloat;
   msexcelworksheet.Range['G'+inttostr(5+i1)].value:=nxje*hl;
   msexcelworksheet.Range['H'+inttostr(5+i1)].value:=nxje;
   msexcelworksheet.Range['G'+inttostr(8+i1+j1)].value:=wxje*hl;
   msexcelworksheet.Range['H'+inttostr(8+i1+j1)].value:=wxje;
   msexcelworksheet.Range['G'+inttostr(9+i1+j1)].value:=(wxje+nxje)*hl;
   msexcelworksheet.Range['H'+inttostr(9+i1+j1)].value:=(wxje+nxje); }
  //
//    except
//   begin
//    {if ILoginInfo.language='$00000804' then
//      IApplication.ShowError('请先关闭已打开的Excel文件！')
//    else
//      begin
//       if ILoginInfo.language='$00000404' then
//         IApplication.ShowError('請先關閉已打開的Excel文件！')
//       else
//         IApplication.ShowError('Please Close The Excel Doucment Of Offer Sheet!');
//      end; }
//    IDialogs.changeprogress(100);
//    IDialogs.closeprogress;

//    tmp.Free;
//    tmpcom.free;

//    msexcelworksheet:=null;
//    msexcelworkbook:=null;
//    msexcel:=null;
//    exit;
//   end;
//  end;
 end;
 
 //msexcel.Sheets('Sheet1').Protect('yada2007',true,true);
 IDialogs.changeprogress(100);
 IDialogs.closeprogress;
 msexcel.visible:=true;
 if tmp.Active then tmp.Close;
 tmp.free;
 if tmpcom.Active then tmpcom.Close;
 tmpcom.free;
 msexcelworksheet:=msexcel.workbooks[1].worksheets[1];
 msexcelworksheet.activate;
 msexcel.visible:=true;
 msexcelworksheet:=null;
 msexcelworkbook:=null;
 msexcel:=null;
 msexcelworkbook.close;
end.