excelunit.pas

工程预算系统

          mainform.ExcelApplication1.Range[Getabcd(j+1+nl)+inttostr(nrow),Getabcd(j+nl+1)+inttostr(nrow)].FormulaR1C1:=calfl(i,(getactiveform as TFrm_ys).temp_grid,j)
        else if (i=dtfllist.Count-1) and (Trim(array_jbxx[46])='1')  and (Trim((getactiveform as TFrm_ys).temp_grid.Cells[j,4])='') then
          mainform.ExcelApplication1.Range[Getabcd(j+1+nl)+inttostr(nrow),Getabcd(j+nl+1)+inttostr(nrow)].FormulaR1C1:=''
        else if (i<dtfllist.Count) then
          mainform.ExcelApplication1.Range[Getabcd(j+1+nl)+inttostr(nrow),Getabcd(j+nl+1)+inttostr(nrow)].FormulaR1C1:=calfl(i,(getactiveform as TFrm_ys).temp_grid,j);

      end;   //end for j

      mainform.ExcelApplication1.Range['B'+inttostr(nrow),Getabcd((getactiveform as TFrm_ys).temp_grid.ColCount+1)+inttostr(nrow)].Borders.LineStyle:=xlContinuous;
      nrow:=nrow+1;
      mainform.statusbar1.Panels[2].Text:='正在检查数据并导入到Excel['+inttostr(i+1)+'/'+inttostr(dtfllist.Count)+']，请稍候....................';
      mainform.StatusBar1.Update;

      ntup:=ntup+nht*nmax;
      if ntup+30>nbottomtop then
        break;


    end;   //end for i:=ntempcount
    ntempcount:=i+1;
    bzxtoExcel((getactiveform as TFrm_ys).temp_grid,nrow+1);
  end;     //end while

  mainform.excelworksheet1.SaveAs(Excel_path);//自动存档   ]
  mainform.ExcelWorkbook1.Close;
  mainform.ExcelApplication1.Quit;
  mainform.ExcelApplication1.Disconnect;
  mainform.ExcelWorkbook1.Disconnect;
  mainform.ExcelWorksheet1.Disconnect;
  except
    screen.Cursor:=crdefault;
    mainform.excelworksheet1.SaveAs(Excel_path);//自动存档   ]
    mainform.ExcelWorkbook1.Close;
    mainform.ExcelApplication1.Quit;
    mainform.ExcelApplication1.Disconnect;
    mainform.ExcelWorkbook1.Disconnect;
    mainform.ExcelWorksheet1.Disconnect;
  end;
//  mainform.ExcelWorksheet1.Cells.Borders.LineStyle[1,2]

  screen.Cursor:=crdefault;
  mainform.statusbar1.Panels[2].Text:='导入Excel成功!';
  mainform.statusbar1.Update;
  application.MessageBox('导入Excel成功','温馨提示',mb_iconinformation);
end;


{万用表导到Excel}
procedure wybtoExcel;
var
  i,j:integer;
  ntopdis,nleftdis,nbottomdis,nrightdis:integer;//报表上，下，左，右边距的值
  dy:integer;                                //报表纸张的宽度和高度
  nh,nb:integer;                                   //标题离纸的高度
  ntop:integer;                                //表头离纸的高度
  nbottomtop:integer;                          //纸张表注部分所占的高度
  nhheight:integer;                           //表头边框的高度
  rcj:Tjc_rcj;
  ntup:integer;
  strselectid:string;
  ntempcount:integer;
  nmin,nmax:integer;
  nht:integer;
  nrow:integer;
  nl:integer;
  ntmppage:integer;
begin
  if (getactiveform as Tfrm_ys).tv_js.Selected<>nil then
    strselectid:=PData2((getactiveform as Tfrm_ys).tv_js.Selected.Data)^.ID
  else
    abort;
  getxx(strselectid);  //取得报表基本信息
  setxx(strselectid,(getactiveform as Tfrm_ys).temp_grid);   //给临时的grid赋值
  getwyb(strselectid);

  if Trim(array_jbxx[1])='1' then
    nl:=1
  else
    nl:=0;

  nht:=strtoint(array_jbxx[48]);
  ntopdis:=strtoint(array_jbxx[5])*3;
  nbottomdis:=strtoint(array_jbxx[7])*3;
  nleftdis:=strtoint(array_jbxx[4])*3;
       nrightdis:=strtoint(array_jbxx[6])*3;

  nhheight:=gethead((getactiveform as TFrm_ys).temp_grid,strtoint(array_jbxx[48]));

  if Trim(array_jbxx[8])='1' then
    dy:=getpageh(0,True)
  else
    dy:=getpageh(0,False);

  nh:=getup(array_jbxx[26],array_jbxx[27],array_jbxx[28],array_jbxx[29],array_jbxx[30],array_jbxx[31],array_jbxx[32],array_jbxx[33],array_jbxx[34]);
  nb:=getup(array_jbxx[35],array_jbxx[38],array_jbxx[41],array_jbxx[36],array_jbxx[39],array_jbxx[42],array_jbxx[37],array_jbxx[40],array_jbxx[43]);

  ntop:=ntopdis+60+nh;                    //表头开始离纸的高度
  nbottomtop:=dy-nbottomdis-nb;           //作为换页的依据;

    ntempcount:=0; ntmppage:=0;
    while ntempcount<=wyblist.Count do
    begin
      ntup:=ntop+nhheight;
      for i:=ntempcount to wyblist.Count-1 do
      begin
        nmax:=1;
        for j:=1 to (getactiveform as TFrm_ys).temp_grid.ColCount-1 do
        begin
          nmin:=getlinecount(calwyb(i,(getactiveform as TFrm_ys).temp_grid,j),(getactiveform as TFrm_ys).temp_grid.ColWidths[j]);
          if nmin>nmax then
            nmax:=nmin;
        end;
        ntup:=ntup+nht*nmax;
        if ntup+30>nbottomtop then
          break;
      end;
      inc(ntmppage);
    end;
    npagecount:=ntmppage;

{  mainform.ExcelApplication1.Visible[0]:=True;  //显示.Excel

  mainform.ExcelApplication1.Workbooks.Add(NUll,0);
  mainform.ExcelWorkbook1.ConnectTo(mainform.ExcelApplication1.Workbooks[1]);
  mainform.ExcelWorksheet1.ConnectTo(mainform.ExcelWorkbook1.Sheets[1] as _worksheet);
  mainform.ExcelApplication1.ActiveWindow.DisplayGridlines:=False;  }
 // mainform.ExcelApplication1.Visible[0]:=false;  //不显示.Excel

  LinkExcel;
  try
  mainform.ExcelWorksheet1.PageSetup.BottomMargin:=nbottomdis;
  mainform.ExcelWorksheet1.PageSetup.TopMargin:=ntopdis;
  mainform.ExcelWorksheet1.PageSetup.LeftMargin:=nleftdis;
  mainform.ExcelWorksheet1.PageSetup.RightMargin:=nrightdis;
  if Trim(array_jbxx[8])='0' then
    mainform.ExcelWorksheet1.PageSetup.Orientation:=xlLandscape;

  mainform.ExcelApplication1.Range['A1','A1'].ColumnWidth:=1;
    if Trim(array_jbxx[1])='1' then
      mainform.ExcelApplication1.Range['B1','B1'].ColumnWidth:=(getactiveform as TFrm_ys).temp_grid.ColWidths[0]/8-0.57;
  for j:=1 to (getactiveform as TFrm_ys).temp_grid.ColCount-1 do
    mainform.ExcelApplication1.Range[Getabcd(j+1+nl)+inttostr(1),Getabcd(j+nl+1)+inttostr(1)].ColumnWidth:=(getactiveform as TFrm_ys).temp_grid.ColWidths[j]/8-0.57;

  ntempcount:=0;
  nrow:=0;         npagetmp:=0;
  if Trim(array_dyxx[1])='0' then
  begin
    while ntempcount<=wyblist.Count do
    begin

      if nrow=0 then
        nrow:=1
      else
        nrow:=nrow+getlinex(0)+1;
    //把表头导到
    npagetmp:=npagetmp+1;
      ExportbzstoExcel((getactiveform as TFrm_ys).temp_grid,nrow);
      nrow:=nrow+2+getline(0);
      bttoExcel((getactiveform as TFrm_ys).temp_grid,nrow);

      nrow:=nrow+strtoint(array_jbxx[44]);
      ntup:=ntop+nhheight;

      for i:=ntempcount to wyblist.Count-1 do
      begin
        rcj:=Tjc_rcj(wyblist.Items[i]);

        nmax:=1;
        for j:=1 to (getactiveform as TFrm_ys).temp_grid.ColCount-1 do
        begin
          nmin:=getlinecount(calwyb(i,(getactiveform as TFrm_ys).temp_grid,j),(getactiveform as TFrm_ys).temp_grid.ColWidths[j]);
          if nmin>nmax then
            nmax:=nmin;
        end;

        if Trim(array_jbxx[1])='1' then
        begin
        //  mainform.ExcelApplication1.Range['B'+inttostr(nrow),'B'+inttostr(nrow+strtoint(array_jbxx[44]))].MergeCells:=True;
          mainform.ExcelApplication1.Range['B'+inttostr(nrow),'B'+inttostr(nrow)].RowHeight:=nht*nmax*0.75;
          mainform.ExcelApplication1.Range['B'+inttostr(nrow),'B'+inttostr(nrow)].VerticalAlignment:=xlCenter;
          mainform.ExcelApplication1.Range['B'+inttostr(nrow),'B'+inttostr(nrow)].HorizontalAlignment:=xlCenter;
          mainform.ExcelApplication1.Range['B'+inttostr(nrow),'B'+inttostr(nrow)].ColumnWidth:=(getactiveform as TFrm_ys).temp_grid.ColWidths[0]/8-0.57;
          mainform.ExcelApplication1.Range['B'+inttostr(nrow),'B'+inttostr(nrow)].Font.Size:=9;
          mainform.ExcelApplication1.Range['B'+inttostr(nrow),'B'+inttostr(nrow)].FormulaR1C1:=rcj.column1;
        end;

        for j:=1 to (getactiveform as TFrm_ys).temp_grid.ColCount-1 do
        begin
          //mainform.ExcelApplication1.Range[Getabcd(j+1+nl)+inttostr(nrow),Getabcd(j+nl+1)+inttostr(nrow)].MergeCells:=True;
          mainform.ExcelApplication1.Range[Getabcd(j+1+nl)+inttostr(nrow),Getabcd(j+nl+1)+inttostr(nrow)].VerticalAlignment:=xlCenter;
          mainform.ExcelApplication1.Range[Getabcd(j+1+nl)+inttostr(nrow),Getabcd(j+nl+1)+inttostr(nrow)].RowHeight:=nht*nmax*0.75;
          mainform.ExcelApplication1.Range[Getabcd(j+1+nl)+inttostr(nrow),Getabcd(j+nl+1)+inttostr(nrow)].ColumnWidth:=(getactiveform as TFrm_ys).temp_grid.ColWidths[j]/8-0.57;
          mainform.ExcelApplication1.Range[Getabcd(j+1+nl)+inttostr(nrow),Getabcd(j+nl+1)+inttostr(nrow)].HorizontalAlignment:=xlleft;
          mainform.ExcelApplication1.Range[Getabcd(j+1+nl)+inttostr(nrow),Getabcd(j+nl+1)+inttostr(nrow)].WrapText:=True;
          mainform.ExcelApplication1.Range[Getabcd(j+1+nl)+inttostr(nrow),Getabcd(j+nl+1)+inttostr(nrow)].Font.Size:=9;
            mainform.ExcelApplication1.Range[Getabcd(j+1+nl)+inttostr(nrow),Getabcd(j+nl+1)+inttostr(nrow)].FormulaR1C1:=calwyb(i,(getactiveform as TFrm_ys).temp_grid,j);

        end;   //end for j

        mainform.ExcelApplication1.Range['B'+inttostr(nrow),Getabcd((getactiveform as TFrm_ys).temp_grid.ColCount+1)+inttostr(nrow)].Borders.LineStyle:=xlContinuous;
        nrow:=nrow+1;
        mainform.statusbar1.Panels[2].Text:='正在检查数据并导入到Excel['+inttostr(i+1)+'/'+inttostr(wyblist.Count)+']，请稍候....................';
        mainform.StatusBar1.Update;

        ntup:=ntup+nht*nmax;
        if ntup+30>nbottomtop then
          break;


      end;   //end for i:=ntempcount
      ntempcount:=i+1;
      bzxtoExcel((getactiveform as TFrm_ys).temp_grid,nrow+1);
    end;     //end while
  end;
  mainform.excelworksheet1.SaveAs(Excel_path);//自动存档   ]
  mainform.ExcelWorkbook1.Close;
  mainform.ExcelApplication1.Quit;
  mainform.ExcelApplication1.Disconnect;
  mainform.ExcelWorkbook1.Disconnect;
  mainform.ExcelWorksheet1.Disconnect;
  except
    screen.Cursor:=crdefault;
    mainform.excelworksheet1.SaveAs(Excel_path);//自动存档   ]
    mainform.ExcelWorkbook1.Close;
    mainform.ExcelApplication1.Quit;
    mainform.ExcelApplication1.Disconnect;
    mainform.ExcelWorkbook1.Disconnect;
    mainform.ExcelWorksheet1.Disconnect;
  end;
//  mainform.ExcelWorksheet1.Cells.Borders.LineStyle[1,2]

  screen.Cursor:=crdefault;
  mainform.statusbar1.Panels[2].Text:='导入Excel成功!';
  mainform.statusbar1.Update;
  application.MessageBox('导入Excel成功','温馨提示',mb_iconinformation);
end;

{单价分析导到Excel}
procedure djfxtoExcel;
var
  v:TfrMemoView;
  page:TfrPage;
  i,j,r,c,k:integer;
  ntopdis,nleftdis,nrightdis,nbottomdis:integer;//报表上，下，左，右边距的值
  dx,dy:integer;                                //报表纸张的宽度和高度
  npage:integer;
  str:string;
  strtmp:string;
  h:integer;
  nh:integer;  //表注上的总长度
  nb:integer;  //表注下的总长度
  zxm:Boolean;
  ntop,nhheight:integer;
  nw:integer;
  nbottomtop:integer;
  ntup:integer;
  cx:integer;
  ntemp:integer;  //
  c_s:integer;  //等级
  ntextheight:integer;
  nheight:integer;
  nxs:integer;      //数据行的高度系数
  nlinew:integer;   //根据
  nrest:integer;     //余下的宽度
  nxm:integer;    /// 项目编号或项目名称的长度
  nxmh:integer;
  nhj:integer;                 //合计部分的宽度
  nlineh:integer;       //报表每行的高度;
  nstr: array[1..20] of string;
  nmax:integer;
  nmin:integer;
  nbt:integer;
  nqz:integer;       //'其中字体的高度'
  njtmp:integer;       //定额数据临时用的
  strhj:string;         //临时
  ch:Boolean;        //
  l2:integer;
  l3:integer;
  nmod:integer;       //定额中除每页要报表的定额个数的余数
  ndiv:integer;       //定额中除每页报表的个数
  ntmppage:integer;   //报表以
  rcj:Trcjj;
  rcjj:Trcjj;
  lrcj:Trcjlist;
  djfx:Tjc_rcj;
  ntmpj:integer;
  nxh:integer;
  nrg:integer;
  ncl:integer;
  njx:integer;
  nsb:integer;
  nzc:integer;
  nqt:integer;
  nttmp:integer;
  strsl:string;
  strhl:string;
  strde:string;
  strsc:string;
  strhhj:string;
  ntempcount:integer;
  b_begin:boolean;
  ni,nj,nk:integer;
  ntemprcj:integer;
  nzpb:integer;
  nzpbh:integer;
  jj:integer;
  strselectid:string;