excelunit.pas

工程预算系统

  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<=qflist.Count do
  begin
    ntup:=ntop+nhheight;
    for i:=ntempcount to qflist.Count-1 do
    begin
      nmax:=1;
      for j:=1 to (getactiveform as TFrm_ys).temp_grid.ColCount-1 do
      begin
        nmin:=getlinecount(calqf(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;   //end for i:=ntempcount
    ntempcount:=i+1;
    inc(ntmppage);
  end;     //end while
  npagecount:=ntmppage;
{  mainform.ExcelApplication1.Visible[0]:=False;  //显示.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;  }

  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;
  while ntempcount<=qflist.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 qflist.Count-1 do
    begin
      rcj:=Tjc_rcj(qflist.Items[i]);

      nmax:=1;
      for j:=1 to (getactiveform as TFrm_ys).temp_grid.ColCount-1 do
      begin
        nmin:=getlinecount(calqf(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)].Font.Size:=9;
        mainform.ExcelApplication1.Range['B'+inttostr(nrow),'B'+inttostr(nrow)].FormulaR1C1:=inttostr(rcj.flag);
      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)].Font.Size:=9;
        mainform.ExcelApplication1.Range[Getabcd(j+1+nl)+inttostr(nrow),Getabcd(j+nl+1)+inttostr(nrow)].WrapText:=True;
        if isnumber(calqf(i,(getactiveform as TFrm_ys).temp_grid,j))=0  then
          mainform.ExcelApplication1.Range[Getabcd(j+1+nl)+inttostr(nrow),Getabcd(j+nl+1)+inttostr(nrow)].NumberFormatLocal:=setnumber(calqf(i,(getactiveform as TFrm_ys).temp_grid,j),j);

        if (lowercase(Trim((getactiveform as TFrm_ys).temp_grid.Cells[j,1]))='mc') then
          mainform.ExcelApplication1.Range[Getabcd(j+1+nl)+inttostr(nrow),Getabcd(j+nl+1)+inttostr(nrow)].FormulaR1C1:=calqf(i,(getactiveform as TFrm_ys).temp_grid,j)
        else if (i=qflist.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<qflist.Count) then
          mainform.ExcelApplication1.Range[Getabcd(j+1+nl)+inttostr(nrow),Getabcd(j+nl+1)+inttostr(nrow)].FormulaR1C1:=calqf(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(qflist.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 dtfltoExcel;
var
  i,j:integer;
  ntopdis,nleftdis,nbottomdis,nrightdis:integer;//报表上，下，左，右边距的值
  dy:integer;                                //报表纸张的宽度和高度
  nh,nb:integer;                                   //标题离纸的高度
  ntop:integer;                                //表头离纸的高度
  nbottomtop:integer;                          //纸张表注部分所占的高度
  nhheight:integer;                           //表头边框的高度
  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赋值
  getdtfl(true);

  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:=3;  ntmppage:=0;
  while ntempcount<=dtfllist.Count do
  begin
    ntup:=ntop+nhheight;
    for i:=ntempcount to dtfllist.Count-1 do
    begin
      nmax:=1;
      for j:=1 to (getactiveform as TFrm_ys).temp_grid.ColCount-1 do
      begin
        nmin:=getlinecount(calfl(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;
    ntempcount:=i+1;
    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:=3;
  nrow:=0;     npagetmp:=0;
  while ntempcount<=dtfllist.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 dtfllist.Count-1 do
    begin
      nmax:=1;
      for j:=1 to (getactiveform as TFrm_ys).temp_grid.ColCount-1 do
      begin
        nmin:=getlinecount(calfl(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)].Font.Size:=9;
        mainform.ExcelApplication1.Range['B'+inttostr(nrow),'B'+inttostr(nrow)].FormulaR1C1:=inttostr(i-2);
      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)].Font.Size:=9;
        mainform.ExcelApplication1.Range[Getabcd(j+1+nl)+inttostr(nrow),Getabcd(j+nl+1)+inttostr(nrow)].WrapText:=True;
        if isnumber(calfl(i,(getactiveform as TFrm_ys).temp_grid,j))=0  then
          mainform.ExcelApplication1.Range[Getabcd(j+1+nl)+inttostr(nrow),Getabcd(j+nl+1)+inttostr(nrow)].NumberFormatLocal:=setnumber(calfl(i,(getactiveform as TFrm_ys).temp_grid,j),j);


        if (lowercase(Trim((getactiveform as TFrm_ys).temp_grid.Cells[j,1]))='mc') then