Fórum [Delphi] Formatar planilha no Excel com OLE Objects #587099

....
uses
  ComObj, uDM, System.SysUtils, System.Variants, Vcl.Dialogs, System.UITypes;

Type

  TRelatorioVenda = class
  public
    class procedure Exportar;
  end;

implementation

class procedure TRelatorioVenda.Exportar;
Var
  Excel: Variant;
  AbaResumo: Variant;
  Linha: Integer;
begin
  try
    Excel  		    := CreateoleObject('Excel.Application');
    Excel.Visible := True;
    Excel.WorkBooks.add;

    Excel.Workbooks[1].WorkSheets[1].cells[1,1].Value               := 'Relatório de Vendas';
    Excel.Workbooks[1].WorkSheets[1].cells[1,1].Font.Name           := 'Arial';
    Excel.Workbooks[1].WorkSheets[1].cells[1,1].Font.Bold           := True;
    Excel.Workbooks[1].WorkSheets[1].cells[1,1].Font.Italic         := True;
    Excel.Workbooks[1].WorkSheets[1].cells[1,1].Font.Size           := 22;
    Excel.Workbooks[1].WorkSheets[1].cells[1,1].Font.Color          := $00F32131;
    Excel.Workbooks[1].WorkSheets[1].cells[1,1].Interior.Color      := $00F2DFC6;
    Excel.Workbooks[1].WorkSheets[1].cells[1,1].RowHeight           := 30;
    Excel.Workbooks[1].WorkSheets[1].cells[1,1].Borders.LineStyle   := 1;
    Excel.Workbooks[1].WorkSheets[1].cells[1,1].HorizontalAlignment := 3;
    Excel.Workbooks[1].WorkSheets[1].Range['A1','E1'].MergeCells    := True;

    Excel.Workbooks[1].WorkSheets[1].Name := 'Rel. Vendas';

    Excel.Workbooks[1].WorkSheets[1].cells[2,1].Value                := 'Cód.';
    Excel.Workbooks[1].WorkSheets[1].cells[2,2].Value                := 'Cliente';
    Excel.Workbooks[1].WorkSheets[1].cells[2,3].Value                := 'Data';
    Excel.Workbooks[1].WorkSheets[1].cells[2,4].Value                := 'Valor';
    Excel.Workbooks[1].WorkSheets[1].cells[2,5].Value                := 'Qtde';
    Excel.Workbooks[1].WorkSheets[1].Range['A2','E2'].Font.Bold      := True;
    Excel.Workbooks[1].WorkSheets[1].Range['A2','E2'].Font.Size      := 12;
    Excel.Workbooks[1].WorkSheets[1].Range['A2','E2'].Font.Color     := $00F32131;
    Excel.Workbooks[1].WorkSheets[1].Range['A2','E2'].Interior.Color := $00F2DFC6;

    Linha := 3;

    DM.FDQryVenda.Open;
    while not DM.FDQryVenda.Eof do
    begin
      Excel.Workbooks[1].WorkSheets[1].cells[Linha,1].Value := DM.FDQryVenda.FieldByName('IDVenda'    ).AsInteger;
      Excel.Workbooks[1].WorkSheets[1].cells[Linha,2].Value := DM.FDQryVenda.FieldByName('Cliente'    ).AsString;
      Excel.Workbooks[1].WorkSheets[1].cells[Linha,3].Value := DM.FDQryVenda.FieldByName('Data'       ).AsDateTime;
      Excel.Workbooks[1].WorkSheets[1].cells[Linha,4].Value := DM.FDQryVenda.FieldByName('Valor_Total').AsCurrency;
      Excel.Workbooks[1].WorkSheets[1].cells[Linha,5].Value := DM.FDQryVenda.FieldByName('Qnt_Itens'  ).AsInteger;
      DM.FDQryVenda.Next;
      Linha := Linha +1;
    end;

    Excel.Workbooks[1].WorkSheets[1].cells[Linha, 1].Value          := '=COUNT(A3:A'+IntToStr(Linha-1)+')';
    Excel.Workbooks[1].WorkSheets[1].cells[Linha, 1].Font.Bold      := True;
    Excel.Workbooks[1].WorkSheets[1].cells[Linha, 1].RowHeight      := 25;

    Excel.Workbooks[1].WorkSheets[1].cells[Linha, 4].Value          := '=SUM(D3:D'+IntToStr(Linha-1)+')';
    Excel.Workbooks[1].WorkSheets[1].cells[Linha, 4].NumberFormat   := 'R$#.##0,00';
    Excel.Workbooks[1].WorkSheets[1].cells[Linha, 4].Font.Bold      := True;
    Excel.Workbooks[1].WorkSheets[1].cells[Linha, 4].RowHeight      := 25;

    Excel.Workbooks[1].WorkSheets[1].Columns.Autofit;

    AbaResumo                        := Excel.Worksheets.Add(EmptyParam, Excel.Workbooks[1].WorkSheets[1]);
    AbaResumo.Name                   := 'Resumo';
    AbaResumo.cells[1,1].Value       := 'Resumo das vendas';
    AbaResumo.cells[1,1].Font.Bold   := True;
    AbaResumo.cells[1,1].Font.Italic := True;
    AbaResumo.cells[1,1].Font.Size   := 22;
    AbaResumo.cells[1,1].RowHeight   := 30;

    AbaResumo.Columns.Autofit;
    Excel.Workbooks[1].SaveAs('d:\\planilha_venda8s.xlsx');
   //Excel.Quit;
  finally
    if not VarIsEmpty(Excel) then
      Excel := Unassigned;
    if not VarIsEmpty(AbaResumo) then
      AbaResumo := Unassigned;
  end;

end;