(solved) Excel output vs Column Header

[Petrus Vorster]

Hi All

Thank you for all the advice! I have progressed by miles!!
I use one of these forum examples to create an excel file from a DBgrid. Works perfectly.

However, sometimes I used the dumbest field names in the DBF and it looks real silly when it is placed in the Excel file as header, or I have hidden some of the fields in the DB grid that is not required to be output to Excel.

I can easily bypass this for Lazreports as I set my own column headers there, but how would one go about writing to Excel using the Column headers I created in the DBgridview instead of the field names in the database?
And could one exclude the non-visible columns?

Thank you greatly!

-Peter

Here is the Excel code:

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

1. procedure tform1.MakeExcel(Mydb:tdbf;DBGrid: TDBGrid);
2.  var
3.   workbook: TsWorkbook;
4.   worksheet: TsWorksheet;
5.   cell: PCell;
6.   row, col: Integer;
7.   fn: String;
8.   dbf1:tdbf;
9. begin
10.   dbf1:=mydb;
11.   workbook := TsWorkbook.Create;
12.   try
13.     worksheet := workbook.AddWorksheet(Dbf1.TableName);
14.     for col := 0 to Dbf1.Fields.Count-1 do
15.     begin
16.       cell := worksheet.WriteText(0, col, Dbf1.Fields[col].FieldName);
17.       worksheet.WriteFontStyle(cell, [fssBold]);
18.     end;
19.     row := 1;
20.     Dbf1.DisableControls;
21.     try
22.       Dbf1.First;
23.       while not Dbf1.EoF do
24.       begin
25.         for col := 0 to Dbf1.Fields.Count-1 do
26.           case Dbf1.Fields[col].DataType of
27.             ftString: worksheet.WriteText(row, col, Dbf1.Fields[col].AsString);
28.             ftDate: worksheet.WriteDateTime(row, col, Dbf1.Fields[col].AsDateTime, nfShortDate);
29.             ftInteger: worksheet.WriteNumber(row, col, Dbf1.Fields[col].AsInteger);
30.             ftFloat: worksheet.WriteNumber(row, col, Dbf1.Fields[col].AsFloat);
31.             // not complete ... (extend list yourself)
32.           end;
33.         Dbf1.Next;
34.         inc(row);
35.       end;
36.       fn := ChangeFileExt(Dbf1.FilePathFull + Dbf1.TableName, '.xlsx');
37.       workbook.WriteToFile(fn, true);
38.       //ShowMessage(Format('%d records exported to %s', [row, fn]));
39.       opendocument(fn);
40.     finally
41.       Dbf1.EnableControls;
42.     end;
43.   finally
44.     workbook.Free;
45.   end;
46. end;      

[paweld]

something like this:

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

1.     procedure tform1.MakeExcel(Mydb:tdbf;DBGrid: TDBGrid);
2.      var
3.       workbook: TsWorkbook;
4.       worksheet: TsWorksheet;
5.       cell: PCell;
6.       row, col: Integer;
7.       fn: String;
8.       dbf1:tdbf;
9.     begin
10.       dbf1:=mydb;
11.       workbook := TsWorkbook.Create;
12.       try
13.         worksheet := workbook.AddWorksheet(Dbf1.TableName);
14.         for col := 0 to DBGrid1.Columns.Count-1 do
15.         begin
16.           if DBGrid1.Columns[col].Visible then //only visible columns
17.           begin
18.             cell := worksheet.WriteText(0, col, DBGrid1.Columns[col].Title.Caption); //grid columne title
19.             worksheet.WriteFontStyle(cell, [fssBold]);
20.           end;
21.         end;
22.         row := 1;
23.         Dbf1.DisableControls;
24.         try
25.           Dbf1.First;
26.           while not Dbf1.EoF do
27.           begin
28.             for col := 0 to DBGrid1.Columns.Count-1 do
29.             begin
30.               if DBGrid1.Columns[col].Visible then
31.               begin
32.                 case Dbf1.FieldByName[DBGrid1.Columns[col].FieldName].DataType of
33.                   ftString: worksheet.WriteText(row, col, Dbf1.FieldByName[DBGrid1.Columns[col].FieldName].AsString);
34.                   ftDate: worksheet.WriteDateTime(row, col, Dbf1.FieldByName[DBGrid1.Columns[col].FieldName].AsDateTime, nfShortDate);
35.                   ftInteger: worksheet.WriteNumber(row, col, Dbf1.FieldByName[DBGrid1.Columns[col].FieldName].AsInteger);
36.                   ftFloat: worksheet.WriteNumber(row, col, Dbf1.FieldByName[DBGrid1.Columns[col].FieldName].AsFloat);
37.                   // not complete ... (extend list yourself)
38.                 end;
39.               end;
40.             end;
41.             Dbf1.Next;
42.             inc(row);
43.           end;
44.           fn := ChangeFileExt(Dbf1.FilePathFull + Dbf1.TableName, '.xlsx');
45.           workbook.WriteToFile(fn, true);
46.           //ShowMessage(Format('%d records exported to %s', [row, fn]));
47.           opendocument(fn);
48.         finally
49.           Dbf1.EnableControls;
50.         end;
51.       finally
52.         workbook.Free;
53.       end;
54.     end;      

[Petrus Vorster]

Man, you are a guru.

Thanks a million.

-Peter

[Petrus Vorster]

Ah, its a pity.

I get a load of errors.

unit1.pas(1460,60) Error: Wrong number of parameters specified for call to "FieldByName"

[Zvoni]

something like this:

............
ftString: worksheet.WriteText(row, col, Dbf1.FieldByName[DBGrid1.Columns[col].FieldName].AsString);
..............

square brackets?!?!?!
I was under the Impression, that "FieldByName" is actually a function, which returns a TField. Not an indexed Property
As such it should have round Paranthesis

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

1. ...........
2. ftString: worksheet.WriteText(row, col, Dbf1.FieldByName(DBGrid1.Columns[col].FieldName).AsString);
3. ...........
4.  

I just picked that one line.
You'd have to change the other lines, too

[Petrus Vorster]

Oh man, I got a pile of stuff.

[Petrus Vorster]

Ok, those round brackets made the world of a difference.
Up and running.

Thank you very much.

-Peter

[Petrus Vorster]

Thank you for all the trouble.

There is just one issue left.
It now create only the visible columns, using the visible columns headers. : Correct
It skips the hidden fields, but it leaves a open column in Excel.

That has to be a integer counting, and I will go look for it tomorrow!
 
This really made my day.

-Peter

[paweld]

square brackets?!?!?!

Yes, there should be a rounded parenthesis there - thanks for the correction. I modified the code from my phone via rdp, and failed to avoid these errors.

@Petrus Vorster: If the topic has been solved then it is good practice to modify the title of the thread by preceding it with information about it, i.e., for example:
[SOLVED] Excel output vs Column Header

[Petrus Vorster]

Hi All

No, its not 100% solved yet.
First of all, thank you for all the help, but there is still one issue.

The output to the excel file now makes open columns in the excel file where the hidden field in my DBgrid is.
If you could just help me work around that, then this will be perfect!

Regards,

Peter

[Zvoni]

Thank you for all the trouble.

There is just one issue left.
It now create only the visible columns, using the visible columns headers. : Correct
It skips the hidden fields, but it leaves a open column in Excel.

That has to be a integer counting, and I will go look for it tomorrow!
 
This really made my day.

-Peter

What in blazes is an "open column"??!?!?!

EDIT: Ah, i see it
here it is

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

1. for col := 0 to DBGrid1.Columns.Count-1 do
2.         begin
3.           if DBGrid1.Columns[col].Visible then //only visible columns
4.           begin
5.             cell := worksheet.WriteText(0, col, DBGrid1.Columns[col].Title.Caption); //grid columne title
6.             worksheet.WriteFontStyle(cell, [fssBold]);
7.           end;
8.         end;
9.  

You are still running through all (!!) Columns of the Grid, and writing directly to the corresponding Column in your worksheet.
So, yeah: You skip the invisible columns of your grid, but you also skip the corresponding column in your Worksheet

You need two "Col"-Variables. one for the Grid, one for the Target-Worksheet
Change the For/Next-Loop to a Repeat/Until or a Do/While-Loop.
You'd have to increase the Col-Counter for The Target-Worksheet yourself everytime you find a "visible" column

NOTE: This is just the Code-Snippet for the Column-Headers.
I haven't looked through the whole code, but i'd bet, there is a second loop transferring the actual Data.
You'd have to change that one accordingly

[Petrus Vorster]

HI

Yes, i noticed the col counter must be the issue.
Going to make the second col counter now.

Many thanks.

-Peter

[Thaddy]

Also note you just need to disconnect the dataset. All db aware controls will be disconnected too.
You can subsequently pack the underlying database.