Copy Db grid to stringgrid

[Petrus Vorster]

Hi all

I need to be output the results in a Tdbgrid to a tstringgrid so I can work with it in a spreadsheet.
I am just using a simple DBF file.

Code: Pascal  [Select][+][-]

1. procedure tmainform.CopyDBGridToStringGrid(DBGrid: TDBGrid; StringGrid: TStringGrid);
2. var
3.   i, j: Integer;
4.   dbb:tdataset;
5. begin
6.   dbb:=dbgrid.DataSource.DataSet;
7.   StringGrid.RowCount := DBGrid.DataSource.DataSet.RecordCount + 1;
8.   StringGrid.ColCount := DBGrid.Columns.Count;
9.  
10.   // Set the header
11.   for i := 0 to DBGrid.Columns.Count - 1 do
12.   begin
13.     StringGrid.Cells[i, 0] := DBGrid.Columns[i].Title.Caption;
14.   end;
15.  
16.   // Copy data
17.   DBGrid.DataSource.DataSet.First;
18.   for i := 1 to StringGrid.RowCount - 1 do
19.   begin
20.     for j := 0 to dbb.FieldCount - 1 do
21.      begin
22.       StringGrid.Cells[j, i] := DBGrid.Columns[j].Field.AsString;
23.     end;
24.     DBGrid.DataSource.DataSet.Next;
25.   end;
26. end;                        

This works on filtered and not filtered data, but i am a bit lost here.
It copies the displayed data in the DBgridview perfectly, but it repeats the last Row of data about two hundred times.
It just has to be one of these loops causing it, but I am too blind to see where this went wrong.

Thank you for all the help.

-Peter

[wp]

Maybe the RecordCount of the dbf is not yet known at this time. You should move to the last and then back to the first record to enforce recalculation of the Recordcount:

Code: Pascal  [Select][+][-]

1.   DBGrid1.DataSource.Dataset.Last;
2.   DBFGrid1.DataSource.Dataset.First;

But why export to a stringgrid when you want to use the data in a spreadsheet? We have FPSpreadsheet to write spreadsheet files directly. The attached project creates a dbf file with some dummy data and exports them to an xlsx file. Note that you must install the FPSpreadsheet library to run this sample (it is available in OPM).

[Sieben]

If you iterate over a dataset with .First und .Next you usually use a while not EOF loop:

Code: Pascal  [Select][+][-]

1. // Copy data
2.   i := 1;
3.   DBGrid.DataSource.DataSet.First;
4.   while not DBGrid.DataSource.DataSet.EOF do
5.   begin
6.     for j := 0 to dbb.FieldCount - 1 do
7.      begin
8.       StringGrid.Cells[j, i] := DBGrid.Columns[j].Field.AsString;
9.     end;
10.     DBGrid.DataSource.DataSet.Next;
11.     inc(i);
12.   end;

This makes sure that you don't read past the datasets capacity (resulting in unwanted multiple copies of the last record) nor short of it.
Just as a general rule, independent of better solutions to your task.

[Petrus Vorster]

Thank you.

Nope, getting out of range errors.
Just have to dig a bit more! 

Its most probably something small I am just overlooking.

-Peter

[Petrus Vorster]

Wp, that would seem the way to go.
Thank you!

[Petrus Vorster]

That works like an absolute charm.

Thank you very much!

-Peter