Arquivo usado para exportar dados do delphi para o Excel

unExcelHelpers.pas

{*******************************************************}
{                                                       }
{       Sistema de controle de acesso                   }
{                                                       }
{       Copyright (C) 2020 Green, Inc                   }
{                                                       }
{*******************************************************}

unit unExcelHelpers;

interface

uses
  Vcl.DBGrids, FireDAC.Comp.Client, System.Win.ComObj, System.DateUtils, System.SysUtils, Vcl.GraphUtil, Vcl.Graphics, Vcl.Dialogs, Excel2010, Datasnap.DBClient;

procedure GerarExcelTxCondominio(ADBGrid: TDBGrid; AQueryViewTxCondominio: TFDQuery);
procedure GerarExcelUsersHikivision(AQueryViewVinculo: TFDQuery);

implementation

uses
  unStrings, unConstants, unSQLHelpers, unHelpers, unDMdados;

{Gera um arquivo excel com informações sobre as Taxas Associativas.

 @param O DBGrid que as taxas estão populadas.
 @param AQueryViewTxCondominio Um query que contém informações da `view_txcondominio`}
procedure GerarExcelTxCondominio(ADBGrid: TDBGrid; AQueryViewTxCondominio: TFDQuery);
var
  Excel : Variant;
  I: Integer;
begin
  Excel := CreateOleObject('Excel.Application');
  Excel.Visible := True;
  Excel.Workbooks.Add;

  with ADBGrid do
  begin
    Excel.WorkBooks[1].Sheets[1].Cells[1, 1].Interior.ColorIndex := 55;
    Excel.WorkBooks[1].Sheets[1].Cells[1, 1].Font.ColorIndex := 2;
    Excel.WorkBooks[1].Sheets[1].Cells[1, 1].Font.Bold := True;
    Excel.WorkBooks[1].Sheets[1].Cells[1, 1] := SMDocumento;
    Excel.WorkBooks[1].Sheets[1].Cells[1, 1].ColumnWidth := 10;

    Excel.WorkBooks[1].Sheets[1].Cells[1, 2].Interior.ColorIndex := 55;
    Excel.WorkBooks[1].Sheets[1].Cells[1, 2].Font.ColorIndex := 2;
    Excel.WorkBooks[1].Sheets[1].Cells[1, 2].Font.Bold := True;
    Excel.WorkBooks[1].Sheets[1].Cells[1, 2] := SNome;
    Excel.WorkBooks[1].Sheets[1].Cells[1, 2].ColumnWidth := 50;

    Excel.WorkBooks[1].Sheets[1].Cells[1, 3].Interior.ColorIndex := 55;
    Excel.WorkBooks[1].Sheets[1].Cells[1, 3].Font.ColorIndex := 2;
    Excel.WorkBooks[1].Sheets[1].Cells[1, 3].Font.Bold := True;
    Excel.WorkBooks[1].Sheets[1].Cells[1, 3] := SValor;
    Excel.WorkBooks[1].Sheets[1].Cells[1, 3].ColumnWidth := 15;

    Excel.WorkBooks[1].Sheets[1].Cells[1, 4].Interior.ColorIndex := 55;
    Excel.WorkBooks[1].Sheets[1].Cells[1, 4].Font.ColorIndex := 2;
    Excel.WorkBooks[1].Sheets[1].Cells[1, 4].Font.Bold := True;
    Excel.WorkBooks[1].Sheets[1].Cells[1, 4] := SQuadra;
    Excel.WorkBooks[1].Sheets[1].Cells[1, 4].ColumnWidth := 6;

    Excel.WorkBooks[1].Sheets[1].Cells[1, 5].Interior.ColorIndex := 55;
    Excel.WorkBooks[1].Sheets[1].Cells[1, 5].Font.ColorIndex := 2;
    Excel.WorkBooks[1].Sheets[1].Cells[1, 5].Font.Bold := True;
    Excel.WorkBooks[1].Sheets[1].Cells[1, 5] := SLote;
    Excel.WorkBooks[1].Sheets[1].Cells[1, 5].ColumnWidth := 15;

    Excel.WorkBooks[1].Sheets[1].Cells[1, 6].Interior.ColorIndex := 55;
    Excel.WorkBooks[1].Sheets[1].Cells[1, 6].Font.ColorIndex := 2;
    Excel.WorkBooks[1].Sheets[1].Cells[1, 6].Font.Bold := True;
    Excel.WorkBooks[1].Sheets[1].Cells[1, 6] := SPeriodo;
    Excel.WorkBooks[1].Sheets[1].Cells[1, 6].ColumnWidth := 12;

    Excel.WorkBooks[1].Sheets[1].Cells[1, 7].Interior.ColorIndex := 55;
    Excel.WorkBooks[1].Sheets[1].Cells[1, 7].Font.ColorIndex := 2;
    Excel.WorkBooks[1].Sheets[1].Cells[1, 7].Font.Bold := True;
    Excel.WorkBooks[1].Sheets[1].Cells[1, 7] := SVencimento;
    Excel.WorkBooks[1].Sheets[1].Cells[1, 7].ColumnWidth := 15;

    Excel.WorkBooks[1].Sheets[1].Cells[1, 8].Interior.ColorIndex := 55;
    Excel.WorkBooks[1].Sheets[1].Cells[1, 8].Font.ColorIndex := 2;
    Excel.WorkBooks[1].Sheets[1].Cells[1, 8].Font.Bold := True;
    Excel.WorkBooks[1].Sheets[1].Cells[1, 8] := SMovimentoRetorno;
    Excel.WorkBooks[1].Sheets[1].Cells[1, 8].ColumnWidth := 20;
  end;

  I := 1;
  with AQueryViewTxCondominio do
  begin
    First;
    while not Eof do
    begin
      Excel.WorkBooks[1].Sheets[1].Cells[I + 1, 1].NumberFormat := '000000';
      Excel.WorkBooks[1].Sheets[1].Cells[I + 1, 1] := FieldByName(C_ID_TXCONDOMINIO).AsString;
      Excel.WorkBooks[1].Sheets[1].Cells[I + 1, 2] := FieldByName(C_NOME_PESSOA).AsString;
      Excel.WorkBooks[1].Sheets[1].Cells[I + 1, 3].NumberFormat := 'R$ #.##0,00_);(R$ #.##0,00)';
      Excel.WorkBooks[1].Sheets[1].Cells[I + 1, 3] := FieldByName(C_VALOR_ORIGINAL).AsFloat;
      Excel.WorkBooks[1].Sheets[1].Cells[I + 1, 4].NumberFormat := '@';
      Excel.WorkBooks[1].Sheets[1].Cells[I + 1, 4] := FieldByName(C_QUADRA).AsString;
      Excel.WorkBooks[1].Sheets[1].Cells[I + 1, 5].NumberFormat := '@';
      Excel.WorkBooks[1].Sheets[1].Cells[I + 1, 5] := FieldByName(C_LOTE).AsString;
      Excel.WorkBooks[1].Sheets[1].Cells[I + 1, 6].NumberFormat := 'mm/aaaa';
      Excel.WorkBooks[1].Sheets[1].Cells[I + 1, 6] := FormatDateTime('mm/yyyy', FieldByName(C_DATAREF).AsDateTime);
      Excel.WorkBooks[1].Sheets[1].Cells[I + 1, 7].NumberFormat := 'dd/mm/aaaa';
      Excel.WorkBooks[1].Sheets[1].Cells[I + 1, 7] := FieldByName(C_VENCIMENTO).AsDateTime;
      Excel.WorkBooks[1].Sheets[1].Cells[I + 1, 8] := FieldByName(C_MOVIMENTO_RETORNO_DESC).AsString;
      Inc(I);

      Next;
    end;
  end;

  Excel.WorkBooks[1].Sheets[1].Cells[I + 1, 3].Font.Bold := True;
  Excel.WorkBooks[1].Sheets[1].Cells[I + 1, 3].formula := '=sum(c2:c' + intTostr(I) + ')';
  // Configurar variáveis para impressão.
  Excel.ActiveSheet.PageSetup.RightFooter := 'Página &P de &N';
  Excel.ActiveSheet.PageSetup.LeftFooter := '&D';
  Excel.ActiveSheet.PageSetup.Orientation := 2;
  Excel.ActiveSheet.PageSetup.PaperSize := xlPaperA4;
  Excel.ActiveSheet.PageSetup.FitToPagesTall := False;
  Excel.ActiveSheet.PageSetup.Zoom := False;
  Excel.ActiveSheet.PageSetup.FitToPagesWide := 1;
end;

procedure GerarExcelUsersHikivision(AQueryViewVinculo: TFDQuery);
var
  Excel: OleVariant;
  linha, coluna, I, X: Integer;
  Memo: TMemo;
  Caminho: string;
begin
  Excel := CreateOleObject('Excel.Application');
  Excel.Visible := False; // Não necessita abrir o excel, apenas salva.
  Excel.Workbooks.Add;

  // Títulos
  Excel.WorkBooks[1].Sheets[1].Cells[1,1] := SPersonID;
  Excel.WorkBooks[1].Sheets[1].Cells[1,2] := SOrganization;
  Excel.WorkBooks[1].Sheets[1].Cells[1,3] := SPersonName;
  Excel.WorkBooks[1].Sheets[1].Cells[1,4] := SGender;
  Excel.WorkBooks[1].Sheets[1].Cells[1,5] := STel;
  Excel.WorkBooks[1].Sheets[1].Cells[1,6] := SEmail;
  Excel.WorkBooks[1].Sheets[1].Cells[1,7] := SEffectiveTime;
  Excel.WorkBooks[1].Sheets[1].Cells[1,8] := SExpiryTime;
  Excel.WorkBooks[1].Sheets[1].Cells[1,9] := SCardNo;
  Excel.WorkBooks[1].Sheets[1].Cells[1,10] := SRoomNo;
  Excel.WorkBooks[1].Sheets[1].Cells[1,11] := SFloorNo;

  I := 1;
  with AQueryViewVinculo do
  begin
    First;
    while not Eof do
    begin
      Excel.WorkBooks[1].Sheets[1].Cells[I + 1, 1] := FieldByName(C_ID_PESSOA).AsString;
      Excel.WorkBooks[1].Sheets[1].Cells[I + 1, 2] := '''AnaVille'; // 'AnaVille
      Excel.WorkBooks[1].Sheets[1].Cells[I + 1, 3] := FieldByName(C_NOME).AsString;
      Excel.WorkBooks[1].Sheets[1].Cells[I + 1, 4] := '1';
      Excel.WorkBooks[1].Sheets[1].Cells[I + 1, 5] := GetFoneSemMask(FieldByName(C_FONE1).AsString);
      Excel.WorkBooks[1].Sheets[1].Cells[I + 1, 7] := '''' + formatDateTimeUS(Now);
      Excel.WorkBooks[1].Sheets[1].Cells[I + 1, 8] := '''' + formatDateTimeUS(IncYear(Now, 10));
      Excel.WorkBooks[1].Sheets[1].Cells[I + 1, 9].NumberFormat := AnsiChar('@');
      Excel.WorkBooks[1].Sheets[1].Cells[I + 1, 9] := GetNumZeroEsquerda(FieldByName(C_ID_PESSOA).AsInteger, 10);
      Inc(I);

      Next;
    end;
  end;

  // Configurar variáveis para impressão.
  Excel.ActiveSheet.PageSetup.RightFooter := 'Página &P de &N';
  Excel.ActiveSheet.PageSetup.LeftFooter := '&D';
  Excel.ActiveSheet.PageSetup.Orientation := 2;
  Excel.ActiveSheet.PageSetup.PaperSize := xlPaperA4;
  Excel.ActiveSheet.PageSetup.FitToPagesTall := False;
  Excel.ActiveSheet.PageSetup.Zoom := False;
  Excel.ActiveSheet.PageSetup.FitToPagesWide := 1;

  Caminho := unDataPaths.DiretorioApp + 'hikivision\usuarios.xlsx';
  if FileExists(Caminho) then
    DeleteFile(Caminho);
  Excel.WorkBooks[1].Sheets[1].SaveAs(Caminho);

  Excel.Quit;
end;