How to have responsive UI (Form) when performing long-running export task?

Question

Good day people. First off, I'm not an native English speaker I might have some grammar mistakes or such.

I need an advice from people who has done something or an application alike mine, well, the thing is that I'm using a TProgressBar in my delphi form, another component called "TExcelApplication" and a TDBGrid.

When I export the DBGrid's content, the application "freezes", so I basically put that ProgressBar for the user to see how much the process is completed. I've realized that when the TDBGrid is retrieving and exporting each row to the new Excel workbook, you can't move the actual form, so you have to wait until the process is completed to move that form.

So, is it possible to do something (I thought about threads but I'm not sure if they could help) so the user could move the window if he wanted?

Thank you so much for taking your time in reading and giving me an advice. I'm using Delphi XE.

Here's the code I use to export the rows:

with ZQDetalles do
    begin
        First;
        while not EOF do
        begin
            i := i + 1;
            workSheet.Cells.Item[i,2] := DBGridDetalles.Fields[0].AsString;
            workSheet.Cells.Item[i,3] := DBGridDetalles.Fields[1].AsString;
            workSheet.Cells.Item[i,4] := DBGridDetalles.Fields[2].AsString;
            workSheet.Cells.Item[i,5] := DBGridDetalles.Fields[3].AsString;
            workSheet.Cells.Item[i,6] := DBGridDetalles.Fields[4].AsString;
            workSheet.Cells.Item[i,7] := DBGridDetalles.Fields[5].AsString;
            workSheet.Cells.Item[i,8] := DBGridDetalles.Fields[6].AsString;
            workSheet.Cells.Item[i,9] := DBGridDetalles.Fields[7].AsString;
            Next;
            barraProgreso.StepIt;
    end;
end;

If you want to see the whole code for the "Export" button, then feel free to see this link: http://pastebin.com/FFWAPdey

Answer 1

Whenever you're doing stuff that takes a significant amount of time in an application with GUI you want to put it in a seperate thread so the user can still operate the form. You can declare a simple thread as such:

TWorkingThread = class(TThread)
protected
  procedure Execute; override;
  procedure UpdateGui;
  procedure TerminateNotify(Sender: TObject);
end;

procedure TWorkingThread.Execute;
begin
  // do whatever you want to do
  // make sure to use synchronize whenever you want to update gui:
  Synchronize(UpdateGui);
end;

procedure TWorkingThread.UpdateGui;
begin
  // e.g. updating the progress bar
end;

procedure TWorkingThread.TerminateNotify(Sender: TObject);
begin
  // this gets executed when the work is done
  // usually you want to give some kind of feedback to the user
end;

  // ...
  // calling the thread:

procedure TSettingsForm.Button1Click(Sender: TObject);
  var WorkingThread: TWorkingThread;
begin
  WorkingThread := TWorkingThread.Create(true);
  WorkingThread.OnTerminate := TerminateNotify;
  WorkingThread.FreeOnTerminate := true;
  WorkingThread.Start;
end;

It's pretty straight forward, remember to always use Synchronize when you want to update visual elements from a thread. Usually, you also want to take care that the user can't invoke the thread again while it's still doing work as he's now able to use the GUI.

Answer 2

If the number of rows is small (and you know how many you'll have), you can transfer the data much more quickly (and all at once) using a variant array of variants, something like this:

var
  xls, wb, Range: OLEVariant;
  arrData: Variant;
  RowCount, ColCount, i, j: Integer;
  Bookmark: TBookmark;
begin
  // Create variant array where we'll copy our data
  // Note that getting RowCount can be slow on large datasets; if
  // that's the case, it's better to do a separate query first to
  // ask for COUNT(*) of rows matching your WHERE clause, and use
  // that instead; then run the query that returns the actual rows,
  // and use them in the loop itself
  RowCount := DataSet1.RecordCount;
  ColCount := DataSet1.FieldCount;
  arrData := VarArrayCreate([1, RowCount, 1, ColCount], varVariant);

  // Disconnect from visual controls
  DataSet1.DisableControls;
  try
    // Save starting row so we can come back to it after
    Bookmark := DataSet1.GetBookmark;
    try    
      {fill array}
      i := 1;
      while not DataSet1.Eof do
      begin
        for j := 1 to ColCount do
          arrData[i, j] := DataSet1.Fields[j-1, i-1].Value;
        DataSet1.Next;
        Inc(i);
        // If we have a lot of rows, we can allow the UI to
        // refresh every so often (here every 100 rows)
        if (i mod 100) = 0 then
          Application.ProcessMessages;
      end;
    finally
      // Reset record pointer to start, and clean up
      DataSet1.GotoBookmark;
      DataSet1.FreeBookmark;
  finally
    // Reconnect GUI controls
    DataSet1.EnableControls;
  end;

  // Initialize an instance of Excel - if you have one 
  // already, of course the next couple of lines aren't
  // needed
  xls := CreateOLEObject('Excel.Application');

  // Create workbook - again, not needed if you have it.
  // Just use ActiveWorkbook instead
  wb := xls.Workbooks.Add;

  // Retrieve the range where data must be placed. Again, your
  // own WorkSheet and start of range instead of using 1,1 when
  // needed.
  Range := wb.WorkSheets[1].Range[wb.WorkSheets[1].Cells[1, 1],
                                  wb.WorkSheets[1].Cells[RowCount, ColCount]];

  // Copy data from allocated variant array to Excel in single shot
  Range.Value := arrData;

  // Show Excel with our data}
  xls.Visible := True;
end;

It still takes the same amount of time to loop through the rows and columns of the data, but the time taken to actually transfer that data to Excel is drastically reduced, particularly if there's a good amount of data.