1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.
  2. Donation with Paypal!!!

    Go to your paypal account and send directly donation to [email protected]

    1 month - 10 $ - Standart VIP

    6 months - 20 $- Standart VIP

    1 year - 30 $- Standart VIP

    2 years - 50 $- Standart VIP

    Gold member for life - 150 $- Standart VIP

    High Vip (Standart VIP include) group please send PM or email to [email protected] for info

    After Donation please send email to [email protected]

  3. Donation Ways 2020


    Paysend
  4. Telegram

  5. Delphifan Magazine
Dismiss Notice

Donation with Paypal!!!

Go to your paypal account and send directly donation to [email protected]

1 month - 10 $ - Standart VIP

6 months - 20 $- Standart VIP

1 year - 30 $- Standart VIP

2 years - 50 $- Standart VIP

Gold member for life - 150 $- Standart VIP

High Vip (Standart VIP include) group please send PM or email to [email protected] for info

After Donation please send email to [email protected]

Dismiss Notice
For open hidden message no need write thanks, thank etc. Enough is click to like button on right side of thread.

EXPORT DBGRID TO EXCEL WITH TOTAL

Discussion in 'Delphi Programming' started by medreis, Nov 6, 2015.

  1. medreis
    Offline

    medreis DF Junior

    procedure pExportarDadosDBGridExcel(pDBGrid :TDBGrid; PastHeader :Boolean);
    var
      Excel         : Variant;
      Linha, i, Col : Integer;
      SavedBookMark : TBookMark;
      vTotal        : Array[0..1000] of Double;
    begin
      try
        if not pDBGrid.DataSource.DataSet.IsEmpty then
        begin
          Excel := CreateOleObject('Excel.Application');
          Excel.Workbooks.Add;

          Linha := 1;
          Col   := 1;

          // Percorrendo todas as colunas do DbGrid
          for i := 0 to (pDBGrid.Columns.Count-1) do
          begin
            // Testando se a coluna do grid está visível
            if pDBGrid.Columns.Items.Visible = True then
            begin
              // Atribuindo valor a célula no Excel
              Excel.WorkBooks[1].Sheets[1].Cells[Linha,Col] := pDBGrid.Columns.Title.Caption;
              // Formatando o Cabeçalho
              Excel.WorkBooks[1].Sheets[1].Cells[Linha,Col].font.name := 'Verdana'; // Fonte
              Excel.WorkBooks[1].Sheets[1].Cells[Linha,Col].font.size := 8; // Tamanho da Fonte
              Excel.WorkBooks[1].Sheets[1].Cells[Linha,Col].font.bold := true; // Negrito
              Excel.WorkBooks[1].Sheets[1].Cells[Linha,Col].font.italic := true; // Italico
              Excel.WorkBooks[1].Sheets[1].Cells[Linha,Col].font.color := clBlack; // Cor da Fonte
              Excel.WorkBooks[1].Sheets[1].Cells[Linha,Col].Interior.Color := clSkyBlue; // Cor
              // Formatando Borda no Cabeçalho da Planilha
              Excel.WorkBooks[1].Sheets[1].Cells[Linha,Col].Borders.LineStyle := 1;
              Excel.WorkBooks[1].Sheets[1].Cells[Linha,Col].Borders.Weight := 2;
              Excel.WorkBooks[1].Sheets[1].Cells[Linha,Col].Borders.ColorIndex := 1;
              Col := Col+1;
            end;
          end;

          Linha := 2;
          Col   := 1;

          // Utilizando o dataset do DBGrid
          with pDBGrid.DataSource.DataSet do
          begin
            // Salvando a posição atual do cursor - linha selecionada no DBGrid
            SavedBookMark := GetBookmark;
            // Evitando que a movimentação no DataSet provoque o scroll do DBGrid
            DisableControls;
            // Posicionando no primeiro registro do DataSet
            First;

            // Percorrendo todas as linhas no DBGrid
            While not EOF do
            begin
              // Percorrendo todas as colunas do DbGrid
              for i := 0 to (pDBGrid.Columns.Count-1) do
              // Testando se a coluna do grid está visível
              if pDBGrid.Columns.Items.Visible = True then
              begin
                // Atribuindo valor a célula no Excel
                if pDBGrid.Columns.Field.DataType in [ftDateTime, ftTimeStamp] then
                begin
                  Excel.WorkBooks[1].Sheets[1].Cells[Linha,Col].NumberFormat := 'dd/mm/aaaa hh:mm:ss';  //Formatando celula no Excel
                  Excel.WorkBooks[1].Sheets[1].Cells[Linha,Col]:= ' '+pDBGrid.Columns.Field.AsString;
                end
                else
                  if pDBGrid.Columns.Field.DataType in [ftBCD, ftFMTBcd, ftFloat, ftCurrency] then
                  begin
                    Excel.WorkBooks[1].Sheets[1].Cells[Linha,Col].NumberFormat := '###.##0,0000';  //Formatando celula no Excel
                    Excel.WorkBooks[1].Sheets[1].Cells[Linha,Col]:= pDBGrid.Columns.Field.AsFloat;
                  end
                  else
                    Excel.WorkBooks[1].Sheets[1].Cells[Linha,Col]:= pDBGrid.Columns.Field.AsString;

                if pDBGrid.Columns.Field.DataType in [ftinteger, ftFloat, ftCurrency, ftFMTBcd, ftBCD] then
                  vTotal:= nvl(vTotal,0) + nvl(pDBGrid.Columns.Field.AsFloat,0);

                Col := Col+1;
              end;

              // Indo para o Proximo registro
              Next;

              Linha := Linha+1;
              Col   := 1;
            end;

            // Percorrendo todas as colunas do DbGrid
            for i:= 0 to (pDBGrid.Columns.Count-1) do
            // Testando se a coluna do grid está visível
            if pDBGrid.Columns.Items.Visible = True then
            begin
              // Atribuindo valor a célula no Excel
              Excel.WorkBooks[1].Sheets[1].Cells[Linha,Col].NumberFormat := '###.###,00';  //Formatando celula no Excel
              Excel.WorkBooks[1].Sheets[1].Cells[Linha,col] := iif(vTotal=0,'', FormatFloat('###,###.##',vTotal));
              // Formatando o Resumo
              Excel.WorkBooks[1].Sheets[1].Cells[Linha,Col].font.name := 'Verdana'; // Fonte
              Excel.WorkBooks[1].Sheets[1].Cells[Linha,Col].font.size := 8; // Tamanho da Fonte
              Excel.WorkBooks[1].Sheets[1].Cells[Linha,Col].font.bold := true; // Negrito
              Excel.WorkBooks[1].Sheets[1].Cells[Linha,Col].font.italic := true; // Italico
              Excel.WorkBooks[1].Sheets[1].Cells[Linha,Col].font.color := clNavy; // Cor da Fonte
              Excel.WorkBooks[1].Sheets[1].Cells[Linha,Col].Interior.Color := clYellow; // Cor
              Col := Col+1;
            end;

            // Reposicionando o cursor na linha em que estava antes do processo
            GotoBookMark(SavedBookMark);
            // Liberando a memória alocada pelo BookMark
            FreeBookMark(SavedBookMark);
            // Habilitando Controles
            EnableControls;
            // Utilizando recurso auto-dimensionar para ajustar o tamanho das colunas
            Excel.Columns.AutoFit;
            // Abrindo Excel
            Excel.Visible := True;

          end;
        end;

      except
        raise Exception.Create('Opção Inválida: Maquina não possui Excel instalado ou versão do Excel não é compativel!!!');
      end;
    end;

     
     
    touhami likes this.
  2. ilanamlas1
    Online

    ilanamlas1 Guest

    oh goooood
     

Share This Page