How to respond to the user interface (Form) when performing a long-term export task?

Question

Good afternoon. Firstly, I am not a native speaker of English, I may have some grammatical errors or such.

I need advice from people who have done something or the application in the same way as mine, well, the thing is that I use TProgressBar in my delphi form, another component called "TExcelApplication" and TDBGrid.

When I export the contents of the DBGrid, the application freezes, so I basically set a ProgressBar for this to find out how far the process was completed. I realized that when TDBGrid extracts and exports each row to a new Excel workbook, you cannot move the actual form, so you need to wait until the process is complete to move this form.

So, is there anything you can do (I was thinking about threads, but I'm not sure if they can help) so that the user can move the window if he wants to?

Thank you so much for taking your time reading and giving me advice. I am using Delphi XE.

Here is the code that I use to export strings:

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 all the code for the Export button, feel free to see this link: http://pastebin.com/FFWAPdey

Answer 1

Whenever you make material that requires a significant amount of time in a GUI application, you want to put it in a separate stream so that the user can use the form. You can declare a simple stream 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 // eg 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; 

This is pretty straight forward, remember to always use Synchronize when you want to update visual elements from a stream. Usually, you should also take care that the user cannot call the thread again while it is still running, as it can now use the graphical interface.

Answer 2

If the number of rows is small (and you know how much you will have), you can transfer data much faster (and all at once) using a variant array option, 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 the case, it 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 iterate over rows and columns of data, but the time required to actually transfer this data to Excel is drastically reduced, especially if there is a good amount of data.