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

Question

I 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;

Answer 1

The delay is seeming confusion of logic, for each RECORD of the dataset it runs through ALL Fields.

Assuming 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 perform 50 * 25.

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

Edit:

It is worth remembering that in his example the:

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

It’s no use, use the for separate!

Answer 2

Column width, there is a property that leaves autosize (I already did this in VB6). I didn’t understand the thing of writing the column header with each iteration with the dataset, I should do it once and before While. Finally, the whole routine running in Thread will avoid the "locking" on the screen, giving an excellent and professional performance to your application.