How to improve the performance of an xls file generator in my Delphi 4+ SQL Server 2000 application?

Question

How to improve the performance of an xls file generator in my Delphi 4+ SQL Server 2000 application?

Navigation

#1 by (1 votes)
#2 by (1 votes)

1

I have implemented the following procedure in an application made in Delphi 4, but the time to generate an xls file with Dataset data is 55 seconds and the dataset has approximately 50 lines. Any tips on how to improve performance?

procedure TfrmConsultaLeitos.ExpXLS(DataSet: TDataSet; Arq: string);
var
  ExcApp: OleVariant;
  i,l: integer;
begin
  Case MessageBox (Application.Handle, Pchar ('Aguarde! Será gerada uma planilha e esse processo demora alguns segundos, Deseja Continuar?'), 'Aplicação', MB_YESNO+MB_ICONINFORMATION+MB_DEFBUTTON2) of
  idYes:
  Begin
    dbgleitos.datasource.dataset.DisableControls;
    ExcApp := CreateOleObject('Excel.Application');
    ExcApp.WorkBooks.Add;
    DataSet.First;
    l := 1;
    DataSet.First;
    Gauge.MinValue := 0;
    Gauge.MaxValue := DataSet.RecordCount;

    Gauge.Visible := true;

    //------------Define largura das células--------------------------------------
    ExcApp.WorkBooks[1].Sheets[1].Cells[1, 1].columnwidth := 17;
    ExcApp.WorkBooks[1].Sheets[1].Cells[1, 2].columnwidth := 7;
    ExcApp.WorkBooks[1].Sheets[1].Cells[1, 3].columnwidth := 5;
    ExcApp.WorkBooks[1].Sheets[1].Cells[1, 4].columnwidth := 18;
    ExcApp.WorkBooks[1].Sheets[1].Cells[1, 5].columnwidth := 50;
    ExcApp.WorkBooks[1].Sheets[1].Cells[1, 6].columnwidth := 6;
    ExcApp.WorkBooks[1].Sheets[1].Cells[1, 7].columnwidth := 40;
    ExcApp.WorkBooks[1].Sheets[1].Cells[1, 8].columnwidth := 40;
    ExcApp.WorkBooks[1].Sheets[1].Cells[1, 9].columnwidth := 5;
    //----------------------------------------------------------------------------

    while not DataSet.EOF do
    begin
      for i := 0 to DataSet.Fields.Count - 1 do
      begin
        ExcApp.WorkBooks[1].Sheets[1].Cells[l, i + 1] := DataSet.Fields[i].DisplayText;

        //------------Preenche o cabeçalho da planilha--------------------------------
        ExcApp.WorkBooks[1].Sheets[1].Cells[1, 1] :='Local';
        ExcApp.WorkBooks[1].Sheets[1].Cells[1, 2] :='Quarto';
        ExcApp.WorkBooks[1].Sheets[1].Cells[1, 3] :='Leito';
        ExcApp.WorkBooks[1].Sheets[1].Cells[1, 4] :='Situação';
        ExcApp.WorkBooks[1].Sheets[1].Cells[1, 5] :='Paciente';
        ExcApp.WorkBooks[1].Sheets[1].Cells[1, 6] :='Idade';
        ExcApp.WorkBooks[1].Sheets[1].Cells[1, 7] :='Visitante';
        ExcApp.WorkBooks[1].Sheets[1].Cells[1, 8] :='Acompanhante';
        ExcApp.WorkBooks[1].Sheets[1].Cells[1, 9] :='Sexo';
        //------------Preenche o cabeçalho da planilha--------------------------------

        //campo em negrito.
        ExcApp.WorkBooks[1].Sheets[1].Cells[1, i + 1].Font.FontStyle := 'Negrito';

        ExcApp.WorkBooks[1].Sheets[1].Cells[l, i + 1].Borders.LineStyle := 1;

        //formato para numeros exemplos 1236,349 => 1.236,35
        ExcApp.WorkBooks[1].Sheets[1].Cells[l, i + 1].NumberFormat := '#.0';
      end;
      DataSet.Next;
      l := l + 1;

      Gauge.Progress := Gauge.Progress + 1;
    end;
    ExcApp.WorkBooks[1].SaveAs(Arq + '_'+ FormatDateTime('mm-dd-yyyy-hhnnss', now()));
    ExcApp.Visible := True;
  end;
  idNo:
       begin
            exit;   // Sai da Execução
       end;
   end;
   Gauge.Visible := false;
   dbgleitos.datasource.dataset.EnableControls;
end;

delphi excel

asked by anonymous 05.11.2018 / 16:27

2 answers

1

The delay is looking like logic confusion, for each dataset LOG it runs through ALL Fields.

Imagining that you have 50 records and each record has 25 columns:

while not DataSet.EOF do
begin
  for i := 0 to DataSet.Fields.Count - 1 do
  begin
  ...
  end;

  DataSet.Next;
end;

Soon he needs to do 50 * 25.

In case the intention is to get the DisplayText of the field, then separate the 2 loops, because no matter how many records, Fields will ALWAYS be the same.

Edit:

Remember that in your example the:

while not DataSet.EOF do
begin
  ...
  DataSet.Next
end;

Not working at all, use for separated!

06.11.2018 / 13:06

InFix to Postfix Conversion I want to "capture" the path and use it in another program

score 1 · Accepted Answer

Width of the columns, there is a property that leaves autosize (I already did this in VB6). I did not understand the bid to write the column header at each iteration with the dataset, I should do this once and before the While. Finally, the entire thread running on Thread will prevent "locking" the screen, giving excellent and professional performance to your application.