Stringgrid to XLSX with border lines.

[seghele0]

Hopefully my last question on this topic.
Can you please provide the code to adjust the RowHeight, so that the rows (from Row 1 to end) of the grid are set to a different height than for the header (Row 0) ?
 

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

1.   Varworksheet.WriteRowHeight(0, round(14*1.5), suPoints);  

[wp]

Here is a modification of your code which shows how to change row heights. I noticed that when the DefaultFont of the workbook is changed also the DefaultRowheight of the worksheets must be adapted - I added this. And I also introduced "speaking" constants for the "magic" values 12, 10, and 1.5 so that the code is easier to understand.

Somewhere towards the end I also show how rowheights can be adjusted automatically. This is not needed here because the row height already have been set (and in case of a large worksheet, the automatic calculation may slow down the code because it must iterate over all cells which, in the worst case, may have different fonts).

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

1. unit Unit1;
2. // Update:   15/08/2023.
3. {$mode objfpc}{$H+}
4. interface
5. uses
6.   Classes, SysUtils, Forms, Controls, Graphics, Dialogs, Grids, StdCtrls;
7. type
8.   { TForm1 }
9.   TForm1 = class(TForm)
10.     Button1: TButton;
11.     StringGrid1: TStringGrid;
12.     procedure Button1Click(Sender: TObject);
13.   private
14.   public
15.   end;
16. var
17.   Form1: TForm1;
18. implementation
19. uses
20.   DateUtils,
21.   fpSpreadsheet, fpstypes, fpsUtils,
22.   xlsxooxml;
23. {$R *.lfm}
24. { TForm1 }
25. //**************************************************
26. procedure TForm1.Button1Click(Sender: TObject);
27. const
28.   HEADER_FONT_SIZE = 10;   // in Points
29.   DEFAULT_FONT_SIZE = 12;  // in Points
30.   NICE_ROWHEIGHT_FACTOR = 1.5;  // adds extra margin to the cell
31. var
32.   VarWORKBOOK:  TsWorkbook;
33.   VarWORKSHEET: TsWorksheet;
34.   VarCELL: pCell;
35.   VarR: Integer;  // Row
36.   VarC: Integer;  // Column
37. begin
38.   //---------------------------------------------------------
39.   // Create a workbook
40.   //---------------------------------------------------------
41.   VarWORKBOOK := TsWorkbook.Create;
42.   try
43.     // Add a worksheet to the workbook
44.     VarWORKSHEET := VarWORKBOOK.AddWorksheet('Export from grid');
45.     // set default Font for the entire workbook  //
46.     // Later on the Font Size for the "header" will be changed !!! //
47.     VarWORKBOOK.SetDefaultFont('Arial',DEFAULT_FONT_SIZE);
48.     VarWORKSHEET.WriteDefaultRowHeight(DEFAULT_FONT_SIZE*NICE_ROWHEIGHT_FACTOR, suPoints);
49.     //************************************************************
50.     // set default Column widths like in the StrngGrid //
51.     //Making use of "lpsUtils" in uses //
52.     for VarC := 0 to StringGrid1.ColCount-1 do
53.       VarWORKSHEET.writecolwidth
54.         (VarC, pxToPts(StringGrid1.ColWidths[VarC],Screen.PixelsPerInch),suPoints);
55.     //--------------------------------------------------------
56.     // Write the title row
57.     //---------------------------------------------------------
58.     for VarC := 0 to StringGrid1.ColCount-1 do
59.     begin
60.       //Write the text of the header cell //
61.       VarCELL:= VarWORKSHEET.WriteText(0, VarC, StringGrid1.Cells[VarC, 0]);
62.       // Center text Horizontally //
63.       VarWORKSHEET.WriteHorAlignment(VarCELL,haCenter);
64.       // Center text Vertically //
65.       VarWORKSHEET.WriteVertAlignment(VarCELL,vaCenter);
66.       // Draw a default border around each cell
67.       VarWORKSHEET.writeborders(VarCELL,[cbNorth,cbSouth,cbEast,cbWest]);
68.       // text in header row in Bold //
69.       VarWORKSHEET.WriteFontStyle(VarCELL, [fssBold]);
70.       // text header font size //
71.       VarWORKSHEET.WriteFontSize(VarCELL,HEADER_FONT_SIZE);
72.       // name of the Font //
73.       VarWORKSHEET.WriteFontName(VarCELL, 'Arial');
74.       // Color Background of header cell //
75.       VarWORKSHEET.WriteBackgroundColor(VarCELL,scYellow);
76.      end;
77.     // Set the row height for the header row which has a smaller font than
78.     // the rest
79.     VarWORKSHEET.WriteRowHeight(0, HEADER_FONT_SIZE*NICE_ROWHEIGHT_FACTOR, suPoints);
80.     //-------------------------------------------------
81.     // Write the data cells
82.     //-------------------------------------------------
83.     for VarR := 1 to StringGrid1.RowCount-1 do
84.     begin
85.       for VarC := 0 to StringGrid1.ColCount-1 do
86.       begin
87.         VarCELL:= VarWORKSHEET.WriteText
88.                  (VarR,VarC,StringGrid1.Cells[VarC,VarR]);
89.         // write border around the cells //
90.         VarWORKSHEET.WriteBorders
91.                  (VarCELL,[cbNorth,cbSouth,cbEast,cbWest]);
92.         VarWORKSHEET.WriteHorAlignment
93.                  (VarCELL,haCenter);
94.         // By default cells are bottom-aligned. It looks better if they are centered.
95.         VarWORKSHEET.WriteVertAlignment
96.                  (VarCELL,vaCENTER);
97.       end;
98.     end;
99.     (*
100.     // Just to show you automatic row height adjustment (not needed since the
101.     // row heights have been handled already).
102.  
103.     //---------------------------------------------------------
104.     // Adjust the row heights
105.     //---------------------------------------------------------
106.     for VarR := 0 to VarWORKSHEET.GetLastRowIndex do
107.     begin
108.       VarWORKSHEET.WriteRowHeight(VarR, NICE_ROWHEIGHT_FACTOR * VarWORKSHEET.CalcAutoRowHeight(VarR), VarWORKBOOK.Units);
109.     end;
110.     *)
111.     //---------------------------------------------------------
112.     // Save the workbook as xlsx file
113.     //---------------------------------------------------------
114.     Showmessage('Will save to xlsx');
115.     VarWORKBOOK.WriteToFile('Test.xlsx', sfOOXML, true);
116.     // workbook.WriteToFile('Test.ods', sfOpenDocument, true);
117.     // or save it for LibreOffice
118.     Showmessage('Saving done !');
119.   finally
120.     // Destroy the workbook after usage.
121.     VarWORKBOOK.Free;
122.   end;
123. end;
124.  
125. end.

[seghele0]

WP,
Your programming work is great, thank you. 
Your support and dedication to my questions is a wonderful experience.

Perhaps adding this example to https://wiki.lazarus.freepascal.org/FPSpreadsheet,
would be an additional support for many beginners.