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

Alexio

How many rows and columns are in the dataset you're exporting? You can export to Excel much faster by doing a bulk transfer using a variant array if the amount of data is reasonable in size; it's much faster than doing a row by row, column by column update. You also need to include the code you're currently using to do the export; there might be ways to speed it up so it doesn't take as long, but without code we can't tell. – Bolero

Also, please pay attention to the description of the tags you're choosing here. The advice tag you used does not mean the same as the advice you ask for in your subject and question text. Please don't just grab tags without knowing what they refer to here. StackOverflow is not an advice site. :-) – Bolero

@Cycascovar: The problem isn't how to get the data from your DBGrid to your ExcelApplication, but rather how to get the data from the dataset populating your DBGrid to it. a) What type of dataset is connected to the TDataSource connected to your DBGrid? b) Roughly how many records are in the dataset? – Blowout

@Martyn: Didn't I already say that in my first comment? ("How many rows and columns are in the dataset you're exporting?") :-) The type of dataset isn't really important; the amount of data is meaningful, though. – Bolero

@KenWhite: Thanks for telling me about the tags! And well, for instance, if the user wants to export all the rows in the DBGrid (6200+ rows) then they should be able to move the window. Here's the code I'm using to export: pastebin.com/FFWAPdey , and I'm exporting 8 columns. – Alexio

See djpate.freeserve.co.uk/AutoExcl.htm#CopyData for a discussion about how to do bulk moves, in case you're still doing it cell by cell (which is quite slow) – Thurgau

Posting the code somewhere else doesn't help us. Your code should be included in your question, as it's relevant to the question. Adding it as an external link means it's not available if the external site isn't, and it's not searchable by future readers here. Please include the relevant portions of it here; you can use the link as "If you want to see the entire code, it's here". Thanks. – Bolero

Most quick and dirty solution is to call Application.Process every N loop iterations (N > 0). Do not forget to disable controls. – Britnibrito

@KenWhite: I beg your pardon. I don't agree, though, that the type of dataset doesn't matter. If it were. e.g. a TClientDataSet, and all the data has already been retrieved into it, getting it from there to Excel can be done in much simpler ways than for, say, a TTable. – Blowout

@KenWhite: Sure, but e.g.: If I want to do the transfer in a secondary thread, with a CDS I find it much easier to create a temporary CDS, copy the data packet of the original CDS to that and then iterate over the temporary one to transfer the data to Excel, or even, for fun, do it using my favourite COM interface, viz CoMarshallInterthreadInterfaceInStream. Also (and I may be wrong because its 15+ years since I used BDE + TTable), but ISTR that a TTable connected to a DBGrid didn't necessarily retrieve all the records in the table unless you forced it to, as by traversing it. – Blowout

@KenWhite: Btw, I'm not arguing, just saying. IYSWIM ... – Blowout

@KenWhite: "But you don't transfer data from the DBGrid" I know, that's what I was meaning in my first comment to the OP. Anyway, I think we're talking somewhat at cross purposes and SO comments aren't the ideal vehicle for this kind of discussion, so I'll stop there. Cheers, Martyn – Blowout

@MartynA: Yep. I'm going to go back and clean up some of the clutter. :-) – Bolero

@RobertFrank: I posted code that does just that in my answer. Did you read it? – Bolero

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.

Cleanse

There's only one problem with using this for the poster's question, and that is the need to set up COM (using CoInitializeEx) for the thread, since it's using COM automation. You also need a new database connection and dataset for a separate thread, which means connecting to the DB and getting the proper rows and columns again in the thread's context. – Bolero

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].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.

Bolero