delphi 多个dbgrideh 表导出到excel 同一个sheet

 前面有导出到 一个excel的 多个sheet

现在实现 导出到 一个sheet  

procedure ExportToExcel3batchonesheet(dbgrideh1: array of TdbgridehExcel; autoFit: Boolean);

  function isFloat(str: string): Boolean;
  begin
    try
      StrToFloat(str);
    except
      Result := False;
    end;
    Result := True;
  end;

  function getSpecCount(substr, str: string): integer;
  var
    i, count: integer;
  begin
    i := 0;
    count := 1;
    while pos(substr, str) > 0 do
    begin
      i := pos(substr, str);
      count := count + 1;
      str := RightStr(str, length(str) - i);
    end;
    result := count;
  end;

  function getSpaceI(sheet: Variant; row, col: integer): Integer;
  var
    i: integer;
  begin
    for i := row downto 2 do//首行为标题行
      if sheet.cells[i, col].Value <> '' then
        break;
    result := i;
  end;
const
  iCHAlignmentCenter = -4108 ;
  iCHAlignmentLeft = -4131 ;
  iCHAlignmentRight = -4152 ;
  xlContinuous = $00000001;


var
  XLApp: Variant;
  ExcelWorkBook1: OleVariant;
  Sheet: Variant;
  s1, s2: string;
  Caption: string;
  Row, Col: integer;
  iCount, jCount: Integer;
  FBookMark: TBookmark;
  FileName: string;
  SaveDialog1: TSaveDialog;
  i, ii, max,sheeti,j,range1,range2,currow: Integer;
  ti: TColumnTitleEh;
  titlename,SRange: string;
  ls: TStringList;
begin
  //如果数据集为空或没有打开则退出
  for I := 0 to Length(dbgrideh1) - 1 do
  begin
    if not dbgrideh1[i].adbgrideh.DataSource.DataSet.Active then
      Exit;
  end;
  SaveDialog1 := TSaveDialog.Create(Nil);
  SaveDialog1.FileName := 'OA信息_' + FormatDateTime('YYMMDDhhmmss', now);
  SaveDialog1.Filter := 'Excel文件|*.xls';
  if SaveDialog1.Execute then
    FileName := SaveDialog1.FileName;
  SaveDialog1.Free;
  if FileName = '' then
    Exit;
  Application.ProcessMessages;
//  Screen.Cursor := crHourGlass; //鼠标指针为沙漏状
  if not VarIsEmpty(XLApp) then
  begin
    XLApp.DisplayAlerts := False;
    XLApp.Quit;
    VarClear(XLApp);
  end; //通过ole创建Excel对象
  try
    XLApp := CreateOleObject('Excel.Application');
  except
    MessageDlg('创建Excel对象失败，请检查你的系统是否正确安装了Excel软件！', mtError, [mbOk], 0);
//    Screen.Cursor := crDefault;
    Exit;
  end; //生成工作页
  //XLApp.WorkBooks.Add[XLWBatWorksheet];
  //XLApp.WorkBooks[1].WorkSheets[1].Name := TitleName;
  //XLApp.Visible := True; //调试显示用
  ExcelWorkBook1 := XLApp.WorkBooks.Add;
  sheet := ExcelWorkBook1.worksheets.add;

  Sheet.name := 'OA表';

  currow := 1;
  for sheeti := 0 to Length(dbgrideh1) - 1 do
  begin

    currow :=  Sheet.Usedrange.Rows.count + 1;




//    XLApp.selection.MergeCells := True;  //合并 //写表头
    Sheet.cells[currow,1] := dbgrideh1[sheeti].sheetname;
    Sheet.rows[currow].Interior.Color :=  clYellow;
    Sheet.rows[currow].font.Size := 14;
    Sheet.rows[currow].font.name := '微软雅黑';
    Sheet.rows[currow].font.bold := true;


    currow := currow + 1;


    Sheet.range[Sheet.cells[currow, 1], Sheet.cells[currow, dbgrideh1[sheeti].adbgrideh.Columns.Count]].Select; //选择该列
    XLApp.selection.HorizontalAlignment := 3; //居中

//    for i := 0 to dbgrideh1[sheeti].adbgrideh.Columns.Count - 1 do
//    begin
//      ti := dbgrideh1[sheeti].adbgrideh.Columns[i].Title;
//      ii := getSpecCount('|', ti.Caption) + 1;
//      if max < ii then
//        max := ii;
//    end;

    for iCount := 0 to dbgrideh1[sheeti].adbgrideh.Columns.Count - 1 do
    begin
      Col := 1;
      Row := iCount + 1;
      Caption := dbgrideh1[sheeti].adbgrideh.Columns[iCount].Title.Caption;
      Sheet.cells[currow, Row] := Caption;

    end;
    Sheet.rows[currow].Interior.Color :=  clSilver;
    Sheet.rows[currow].font.Size := 12;
    Sheet.rows[currow].font.name := '微软雅黑';
    Sheet.rows[currow].font.bold := true;




    // 合并表头
    if dbgrideh1[sheeti].amergecellstring <> '' then
    begin
      Sheet.rows[currow].insert;
      Sheet.rows[currow].Interior.Color :=  clSilver;
      Sheet.rows[currow].font.Size := 12;
      Sheet.rows[currow].font.name := '微软雅黑';
      Sheet.rows[currow].font.bold := true;

      currow := currow + 1;
      ls := TStringList.Create;
      ls.Delimiter := ',';
      ls.DelimitedText := dbgrideh1[sheeti].amergecellstring;

      for I := 0 to ls.Count - 1 do
      begin
        range1 := 0;
        range2 := 0;
        for j := 0 to dbgrideh1[sheeti].adbgrideh.Columns.Count - 1 do
        begin
          s1 := sheet.cells[currow,j+1];
          if Pos(ls.Strings[i],s1) >0 then
          begin
            if range1 = 0 then range1 := j+1 ;
            range2 := j+1;
            sheet.cells[currow,j+1] := StringReplace(sheet.cells[currow,j+1],ls.Strings[i] + '|','',[rfReplaceAll]) ;
          end;
          sheet.Cells[currow-1,j+1].HorizontalAlignment := iCHAlignmentCenter ;

        end;
        if (range1 > 0) and (range2 > 0)  then
        begin
          Sheet.cells[currow-1,range1]   :=  ls.Strings[i];

          SRange :=GetColumnCharacters(range1) + (currow-1).toString + ':' + GetColumnCharacters(range2)+(currow-1).ToString;
          sheet.Range[SRange].MergeCells := True;
        end;

      end;
      ls.Free;
//      Sheet.rows[currow - 1].Font.size  := 12;
//      Sheet.rows[currow-1].Font.Color  := clred;
//      Sheet.rows[currow-1].Font.Bold  := True;
//      Sheet.rows[currow].Font.size  := 12;
//      Sheet.rows[currow].Font.Color  := clred;
//      Sheet.rows[currow].Font.Bold  := True;
    end else
    begin
//      Sheet.rows[currow-1].Font.size  := 12;
//      Sheet.rows[currow-1].Font.Color  := clred;
//      Sheet.rows[currow-1].Font.Bold  := True;
    end;

    currow := currow + 1;
    dbgrideh1[sheeti].adbgrideh.DataSource.DataSet.DisableControls;
    FBookMark := dbgrideh1[sheeti].adbgrideh.DataSource.DataSet.GetBookmark;
    dbgrideh1[sheeti].adbgrideh.DataSource.DataSet.First;
    while not dbgrideh1[sheeti].adbgrideh.DataSource.DataSet.Eof do
    begin
      for iCount := 1 to dbgrideh1[sheeti].adbgrideh.Columns.Count do
      begin
        Sheet.rows[currow].font.Size := 12;
        Sheet.rows[currow].font.name := '微软雅黑';
        Sheet.rows[currow].font.bold := false;
        Sheet.cells[currow, iCount] := dbgrideh1[sheeti].adbgrideh.Columns.Items[iCount - 1].Field.AsString;
        if (Pos('.', dbgrideh1[sheeti].adbgrideh.Columns.Items[iCount - 1].Field.AsString) > 0) and isFloat(dbgrideh1[sheeti].adbgrideh.Columns.Items[iCount - 1].Field.AsString) then
          Sheet.cells[currow, iCount].numberformatlocal := OleVariant('0.00');
      end;
      Inc(currow);

      dbgrideh1[sheeti].adbgrideh.DataSource.DataSet.Next;
    end;
    if dbgrideh1[sheeti].adbgrideh.DataSource.DataSet.BookmarkValid(FBookMark) then
      dbgrideh1[sheeti].adbgrideh.DataSource.DataSet.GotoBookmark(FBookMark);
    dbgrideh1[sheeti].adbgrideh.DataSource.DataSet.EnableControls; //读取表脚
    if dbgrideh1[sheeti].adbgrideh.FooterRowCount > 0 then
    begin
      currow := currow + 1;
      for Row := 0 to dbgrideh1[sheeti].adbgrideh.FooterRowCount - 1 do
      begin
        for Col := 0 to dbgrideh1[sheeti].adbgrideh.Columns.Count - 1 do
          Sheet.cells[currow, Col + 1] := dbgrideh1[sheeti].adbgrideh.GetFooterValue(Row, dbgrideh1[sheeti].adbgrideh.Columns[Col]);
        Inc(currow);
      end;
    end; //调整列宽
    if autoFit then
      for iCount := 1 to dbgrideh1[sheeti].adbgrideh.Columns.Count do
        Sheet.Columns[iCount].EntireColumn.AutoFit;
    Sheet.cells[currow, 1].Select;
    //划线
    SRange := 'A1' + ':' + GetColumnCharacters(Sheet.Usedrange.columns.count) + IntToStr(Sheet.Usedrange.rows.count);
    Sheet.Range[SRange].Borders.LineStyle := xlContinuous ;

// ExcelApp.ActiveSheet.Rows[2].Insert;
  end;

  ExcelWorkBook1.SaveAs(FileName);
  XLApp.Visible := True;
//  XLApp.ActiveWorkBook.SaveAs(FileName);
  XLApp := Unassigned;
//  Screen.Cursor := crDefault;
end;