Align numbers before saving to a file.

[seghele0]

The code below works.
90% of the code is not mine (TOO complex).
What I would like to add is the alignment of the scores (numbers) on the right side.
Thanks already.
 

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

1. //  SAVE StringGrid
2. //  Saving an XLSX and TXT file.
3. procedure BewaarStringGrid(StringGrid1: TStringGrid);
4. const
5.   HEADER_FONT_SIZE = 11;   // in Points
6.   DEFAULT_FONT_SIZE = 15;  // in Points
7.   NICE_ROWHEIGHT_FACTOR = 1.5;  // adds extra margin to the cell
8. var
9.   varScoreBestandsnaam : String;
10.   varLengteLangsteNaam : Integer;
11.   varKolomBreedte : Integer;
12.   varTitelLijn, varNamenLijn, varScoreLijn: String;
13.   varRow : Integer;
14.   varCol : Integer;
15.   varFile1 : TextFile;
16.   // Following for XLSX
17.   VarWORKBOOK:  TsWorkbook;
18.   VarWORKSHEET: TsWorksheet;
19.   VarCELL: pCell;
20.   VarR: Integer;  // Row
21.   VarC: Integer;  // Column
22. begin
23.   // Show vertical and horizontal lines
24.   StringGrid1.Options := StringGrid1.Options + [goVertLine, goHorzLine];
25.   // Adjust column widths automatically
26.   StringGrid1.AutoSizeColumns;
27.   // Create a TXT file
28.   // First check the length of the longest name
29.   varLengteLangsteNaam := 0;
30.   for varCol := 0 to StringGrid1.ColCount - 1 do
31.   begin
32.     if (Length(StringGrid1.Cells[varCol, 0]) > varLengteLangsteNaam) then
33.     begin
34.        varLengteLangsteNaam := Length(StringGrid1.Cells[varCol, 0]);
35.     end;
36.   end;
37.   // Extra space between columns
38.   varKolomBreedte := varLengteLangsteNaam + 2;
39.   // End of preparation
40.   varScoreBestandsnaam := 'WIEZEN' + '.txt';
41.   // Write to the file
42.   AssignFile(varFile1, varScoreBestandsnaam);
43.   Try
44.     Rewrite(varFile1);
45.     varTitelLijn := 'Resultaat Wiezen - ' + formatdatetime('dd/mm/YYYY HH:MM',
46.                      Now);
47.     // Write the "title" line
48.     Writeln(varFile1, varTitelLijn);
49.     // Then put the names of the players in a line
50.     varNamenLijn := '';
51.     for varCol := 0 to StringGrid1.ColCount - 1 do
52.     begin
53.       varNamenLijn := varNamenLijn + PadRight(StringGrid1.Cells[varCol, 0],
54.                       varKolomBreedte);
55.     end;
56.     // Write the names line
57.     Writeln(varFile1, varNamenLijn);
58.     // The lines with the scores
59.      for varRow := 1 to StringGrid1.RowCount - 1 do
60.     begin
61.       varScoreLijn := '';
62.       for varCol := 0 to StringGrid1.ColCount - 1 do
63.       begin
64.          varScoreLijn := varScoreLijn + PadRight(StringGrid1.Cells[varCol,
65.                          varRow], varKolomBreedte);
66.       end;
67.       //Write the score line
68.       Writeln(varFile1, varScoreLijn);
69.     end;
70.   Finally
71.    //Close the file
72.    CloseFile(varFile1);
73.   End;
74. //**********************
75. //* CREATE a WORKBOOK  *
76. //**********************
77. VarWORKBOOK := TsWorkbook.Create;
78. try
79.   // Add a worksheet to the workbook
80.   VarWORKSHEET := VarWORKBOOK.AddWorksheet('Export from grid');
81.   // set default Font for the entire workbook  //
82.   // Later on, the Font Size for the "header" will be changed !!! //
83.   VarWORKBOOK.SetDefaultFont('Arial',DEFAULT_FONT_SIZE);
84.   VarWORKSHEET.WriteDefaultRowHeight(DEFAULT_FONT_SIZE*NICE_ROWHEIGHT_FACTOR, suPoints);
85.   // set default Column widths like in the StrngGrid //
86.   // Making use of "lpsUtils" in uses //
87.   for VarC := 0 to StringGrid1.ColCount-1 do
88.     VarWORKSHEET.writecolwidth
89.       (VarC, pxToPts(StringGrid1.ColWidths[VarC],Screen.PixelsPerInch),suPoints);
90.   //--------------------------------------------------------
91.   // Write the title row
92.   //---------------------------------------------------------
93.   for VarC := 0 to StringGrid1.ColCount-1 do
94.   begin
95.     //Write the text of the header cell //
96.     VarCELL:= VarWORKSHEET.WriteText(0, VarC, StringGrid1.Cells[VarC, 0]);
97.     // Center text Horizontally //
98.     VarWORKSHEET.WriteHorAlignment(VarCELL,haCenter);
99.     // Center text Vertically //
100.     VarWORKSHEET.WriteVertAlignment(VarCELL,vaCenter);
101.     // Draw a default border around each cell
102.     VarWORKSHEET.writeborders(VarCELL,[cbNorth,cbSouth,cbEast,cbWest]);
103.     // text in header row in Bold //
104.     VarWORKSHEET.WriteFontStyle(VarCELL, [fssBold]);
105.     // text header font size //
106.     VarWORKSHEET.WriteFontSize(VarCELL,HEADER_FONT_SIZE);
107.     // name of the Font //
108.     VarWORKSHEET.WriteFontName(VarCELL, 'Arial');
109.     // Color Background of header cell //
110.     VarWORKSHEET.WriteBackgroundColor(VarCELL,scYellow);
111.    end;
112.   // Set the row height for the header row which has a smaller font than
113.   // the rest
114.   VarWORKSHEET.WriteRowHeight(0, HEADER_FONT_SIZE*NICE_ROWHEIGHT_FACTOR, suPoints);
115.   // Write the PageHeader: DATE.
116.   VarWORKSHEET.PageLayout.Headers[HEADER_FOOTER_INDEX_ALL]:= '&C&D';
117.   // Write the data cells
118.   for VarR := 1 to StringGrid1.RowCount-1 do
119.   begin
120.     for VarC := 0 to StringGrid1.ColCount-1 do
121.     begin
122.       VarCELL:= VarWORKSHEET.WriteText
123.                (VarR,VarC,StringGrid1.Cells[VarC,VarR]);
124.       // write border around the cells //
125.       VarWORKSHEET.WriteBorders
126.                (VarCELL,[cbNorth,cbSouth,cbEast,cbWest]);
127.       VarWORKSHEET.WriteHorAlignment
128.                (VarCELL,haCenter);
129.       // By default cells are bottom-aligned. It looks better if they are centered.
130.       VarWORKSHEET.WriteVertAlignment
131.                (VarCELL,vaCENTER);
132.     end;
133.   end;
134.   // Just to show you automatic row height adjustment (not needed since the
135.   // row heights have been handled already).
136.   // Adjust the row heights
137.   for VarR := 0 to VarWORKSHEET.GetLastRowIndex do
138.   begin
139.     VarWORKSHEET.WriteRowHeight
140.       (VarR, NICE_ROWHEIGHT_FACTOR * VarWORKSHEET.CalcAutoRowHeight(VarR),
141.       VarWORKBOOK.Units);
142.   end;
143.   //---------------------------------------------------------
144.  // Save the workbook as xlsx file
145.  //Showmessage('Will save to xlsx');
146.  VarWORKBOOK.WriteToFile('WIEZEN.xlsx', sfOOXML, true);
147.  // workbook.WriteToFile('Test.ods', sfOpenDocument, true);
148.  // or save it for LibreOffice
149. finally
150.  // Destroy the workbook after usage.
151.  VarWORKBOOK.Free;
152. end;
153. end;      
154.  

[Thaddy]

There should never be white space in a data file. You are barking up the wrong tree.

[paweld]

Write as number:

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

1. //  SAVE StringGrid
2. //  Saving an XLSX and TXT file.
3. procedure BewaarStringGrid(StringGrid1: TStringGrid);
4. const
5.   HEADER_FONT_SIZE = 11;   // in Points
6.   DEFAULT_FONT_SIZE = 15;  // in Points
7.   NICE_ROWHEIGHT_FACTOR = 1.5;  // adds extra margin to the cell
8. var
9.   varScoreBestandsnaam: String;
10.   varLengteLangsteNaam: Integer;
11.   varKolomBreedte: Integer;
12.   varTitelLijn, varNamenLijn, varScoreLijn: String;
13.   varRow: Integer;
14.   varCol: Integer;
15.   varFile1: TextFile;
16.   // Following for XLSX
17.   VarWORKBOOK: TsWorkbook;
18.   VarWORKSHEET: TsWorksheet;
19.   VarCELL: pCell;
20.   VarR: Integer;  // Row
21.   VarC: Integer;  // Column
22.   // cell value
23.   tmpValue: Single;
24. begin
25.   // Show vertical and horizontal lines
26.   StringGrid1.Options := StringGrid1.Options + [goVertLine, goHorzLine];
27.   // Adjust column widths automatically
28.   StringGrid1.AutoSizeColumns;
29.   // Create a TXT file
30.   // First check the length of the longest name
31.   varLengteLangsteNaam := 0;
32.   for varCol := 0 to StringGrid1.ColCount - 1 do
33.   begin
34.     if (Length(StringGrid1.Cells[varCol, 0]) > varLengteLangsteNaam) then
35.     begin
36.       varLengteLangsteNaam := Length(StringGrid1.Cells[varCol, 0]);
37.     end;
38.   end;
39.   // Extra space between columns
40.   varKolomBreedte := varLengteLangsteNaam + 2;
41.   // End of preparation
42.   varScoreBestandsnaam := 'WIEZEN' + '.txt';
43.   // Write to the file
44.   AssignFile(varFile1, varScoreBestandsnaam);
45.   try
46.     Rewrite(varFile1);
47.     varTitelLijn := 'Resultaat Wiezen - ' + formatdatetime('dd/mm/YYYY HH:MM', Now);
48.     // Write the "title" line
49.     Writeln(varFile1, varTitelLijn);
50.     // Then put the names of the players in a line
51.     varNamenLijn := '';
52.     for varCol := 0 to StringGrid1.ColCount - 1 do
53.     begin
54.       varNamenLijn := varNamenLijn + PadRight(StringGrid1.Cells[varCol, 0], varKolomBreedte);
55.     end;
56.     // Write the names line
57.     Writeln(varFile1, varNamenLijn);
58.     // The lines with the scores
59.     for varRow := 1 to StringGrid1.RowCount - 1 do
60.     begin
61.       varScoreLijn := '';
62.       for varCol := 0 to StringGrid1.ColCount - 1 do
63.       begin
64.         varScoreLijn := varScoreLijn + PadRight(StringGrid1.Cells[varCol, varRow], varKolomBreedte);
65.       end;
66.       //Write the score line
67.       Writeln(varFile1, varScoreLijn);
68.     end;
69.   finally
70.     //Close the file
71.     CloseFile(varFile1);
72.   end;
73.   //**********************
74.   //* CREATE a WORKBOOK  *
75.   //**********************
76.   VarWORKBOOK := TsWorkbook.Create;
77.   try
78.     // Add a worksheet to the workbook
79.     VarWORKSHEET := VarWORKBOOK.AddWorksheet('Export from grid');
80.     // set default Font for the entire workbook  //
81.     // Later on, the Font Size for the "header" will be changed !!! //
82.     VarWORKBOOK.SetDefaultFont('Arial', DEFAULT_FONT_SIZE);
83.     VarWORKSHEET.WriteDefaultRowHeight(DEFAULT_FONT_SIZE * NICE_ROWHEIGHT_FACTOR, suPoints);
84.     // set default Column widths like in the StrngGrid //
85.     // Making use of "lpsUtils" in uses //
86.     for VarC := 0 to StringGrid1.ColCount - 1 do
87.       VarWORKSHEET.writecolwidth
88.       (VarC, pxToPts(StringGrid1.ColWidths[VarC], Screen.PixelsPerInch), suPoints);
89.     //--------------------------------------------------------
90.     // Write the title row
91.     //---------------------------------------------------------
92.     for VarC := 0 to StringGrid1.ColCount - 1 do
93.     begin
94.       //Write the text of the header cell //
95.       VarCELL := VarWORKSHEET.WriteText(0, VarC, StringGrid1.Cells[VarC, 0]);
96.       // Center text Horizontally //
97.       VarWORKSHEET.WriteHorAlignment(VarCELL, haCenter);
98.       // Center text Vertically //
99.       VarWORKSHEET.WriteVertAlignment(VarCELL, vaCenter);
100.       // Draw a default border around each cell
101.       VarWORKSHEET.writeborders(VarCELL, [cbNorth, cbSouth, cbEast, cbWest]);
102.       // text in header row in Bold //
103.       VarWORKSHEET.WriteFontStyle(VarCELL, [fssBold]);
104.       // text header font size //
105.       VarWORKSHEET.WriteFontSize(VarCELL, HEADER_FONT_SIZE);
106.       // name of the Font //
107.       VarWORKSHEET.WriteFontName(VarCELL, 'Arial');
108.       // Color Background of header cell //
109.       VarWORKSHEET.WriteBackgroundColor(VarCELL, scYellow);
110.     end;
111.     // Set the row height for the header row which has a smaller font than
112.     // the rest
113.     VarWORKSHEET.WriteRowHeight(0, HEADER_FONT_SIZE * NICE_ROWHEIGHT_FACTOR, suPoints);
114.     // Write the PageHeader: DATE.
115.     VarWORKSHEET.PageLayout.Headers[HEADER_FOOTER_INDEX_ALL] := '&C&D';
116.     // Write the data cells
117.     for VarR := 1 to StringGrid1.RowCount - 1 do
118.     begin
119.       for VarC := 0 to StringGrid1.ColCount - 1 do
120.       begin
121.         //check if value is number
122.         if TryStrToFloat(StringGrid1.Cells[VarC, VarR], tmpValue) then
123.           VarCELL := VarWORKSHEET.WriteNumber(VarR, VarC, tmpValue)
124.         else
125.           VarCELL := VarWORKSHEET.WriteText(VarR, VarC, StringGrid1.Cells[VarC, VarR]);
126.         // write border around the cells //
127.         VarWORKSHEET.WriteBorders
128.         (VarCELL, [cbNorth, cbSouth, cbEast, cbWest]);
129.         VarWORKSHEET.WriteHorAlignment
130.         (VarCELL, haCenter);
131.         // By default cells are bottom-aligned. It looks better if they are centered.
132.         VarWORKSHEET.WriteVertAlignment
133.         (VarCELL, vaCENTER);
134.       end;
135.     end;
136.     // Just to show you automatic row height adjustment (not needed since the
137.     // row heights have been handled already).
138.     // Adjust the row heights
139.     for VarR := 0 to VarWORKSHEET.GetLastRowIndex do
140.     begin
141.       VarWORKSHEET.WriteRowHeight
142.       (VarR, NICE_ROWHEIGHT_FACTOR * VarWORKSHEET.CalcAutoRowHeight(VarR),
143.         VarWORKBOOK.Units);
144.     end;
145.     //---------------------------------------------------------
146.     // Save the workbook as xlsx file
147.     //Showmessage('Will save to xlsx');
148.     VarWORKBOOK.WriteToFile('WIEZEN.xlsx', sfOOXML, True);
149.     // workbook.WriteToFile('Test.ods', sfOpenDocument, true);
150.     // or save it for LibreOffice
151.   finally
152.     // Destroy the workbook after usage.
153.     VarWORKBOOK.Free;
154.   end;
155. end;
156.  

[seghele0]

 
Many thanks for your answer.
Can only implement the code from Monday due to illness.
Will keep you informed.

[Thaddy]

Get well soon 

[seghele0]

Hello
Stomach flu is over.
 
Have modified the code in my application and it works fine for the WIEZEN.xlsx file.
 
Is there also a solution for the first part of the procedure, drawing up Wiezen.txt.
The alignment to the right of the numbers is not optimal.
 
Thank you already.

[paweld]

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

1. //  SAVE StringGrid
2. //  Saving an XLSX and TXT file.
3. procedure BewaarStringGrid(StringGrid1: TStringGrid);
4. const
5.   HEADER_FONT_SIZE = 11;   // in Points
6.   DEFAULT_FONT_SIZE = 15;  // in Points
7.   NICE_ROWHEIGHT_FACTOR = 1.5;  // adds extra margin to the cell
8. var
9.   varScoreBestandsnaam: String;
10.   varCellLen: Integer;
11.   varLijn: String;
12.   varRow: Integer;
13.   varCol: Integer;
14.   varFile1: TextFile;
15.   // Following for XLSX
16.   VarWORKBOOK: TsWorkbook;
17.   VarWORKSHEET: TsWorksheet;
18.   VarCELL: pCell;
19.   // cell value
20.   tmpValue: Single;
21.   //col length array
22.   tmpColCountArr: array of Integer;
23. begin
24.   // Show vertical and horizontal lines
25.   StringGrid1.Options := StringGrid1.Options + [goVertLine, goHorzLine];
26.   // Adjust column widths automatically
27.   StringGrid1.AutoSizeColumns;
28.   // Create a TXT file
29.   // check max length for each column
30.   SetLength(tmpColCountArr, StringGrid1.ColCount);
31.   for varCol := 0 to StringGrid1.ColCount - 1 do
32.   begin
33.     for varRow := 0 to StringGrid1.RowCount - 1 do
34.     begin
35.       varCellLen := Length(StringGrid1.Cells[varCol, varRow]);
36.       if varRow = 0 then
37.         tmpColCountArr[varCol] := varCellLen
38.       else if tmpColCountArr[varCol] < varCellLen then
39.         tmpColCountArr[varCol] := varCellLen;
40.     end;
41.   end;
42.   // End of preparation
43.   varScoreBestandsnaam := 'WIEZEN' + '.txt';
44.   // Write to the file
45.   AssignFile(varFile1, varScoreBestandsnaam);
46.   try
47.     Rewrite(varFile1);
48.     varLijn := 'Resultaat Wiezen - ' + formatdatetime('dd/mm/YYYY HH:MM', Now);
49.     // Write the "title" line
50.     Writeln(varFile1, varLijn);
51.     // Then put data
52.     for varRow := 1 to StringGrid1.RowCount - 1 do
53.     begin
54.       varLijn := '';
55.       for varCol := 0 to StringGrid1.ColCount - 1 do
56.       begin
57.         if TryStrToFloat(StringGrid1.Cells[varCol, varRow], tmpValue) then
58.           varLijn := varLijn + Format('%' + IntToStr(tmpColCountArr[varCol] + 's', [FormatFloat('0.####', tmpValue)]) + '  '
59.         else
60.           varLijn := varLijn + Format('%-' + IntToStr(tmpColCountArr[varCol] + 's', [StringGrid1.Cells[varCol, varRow]]) + '  ';
61.       end;
62.       //Write the line
63.       Writeln(varFile1, varLijn);
64.     end;
65.   finally
66.     //Close the file
67.     CloseFile(varFile1);
68.   end;
69.   //**********************
70.   //* CREATE a WORKBOOK  *
71.   //**********************
72.   VarWORKBOOK := TsWorkbook.Create;
73.   try
74.     // Add a worksheet to the workbook
75.     VarWORKSHEET := VarWORKBOOK.AddWorksheet('Export from grid');
76.     // set default Font for the entire workbook  //
77.     // Later on, the Font Size for the "header" will be changed !!! //
78.     VarWORKBOOK.SetDefaultFont('Arial', DEFAULT_FONT_SIZE);
79.     VarWORKSHEET.WriteDefaultRowHeight(DEFAULT_FONT_SIZE * NICE_ROWHEIGHT_FACTOR, suPoints);
80.     // set default Column widths like in the StrngGrid //
81.     // Making use of "lpsUtils" in uses //
82.     for varCol := 0 to StringGrid1.ColCount - 1 do
83.       VarWORKSHEET.writecolwidth(varCol, pxToPts(StringGrid1.ColWidths[varCol], Screen.PixelsPerInch), suPoints);
84.     //--------------------------------------------------------
85.     // Write the title row
86.     //---------------------------------------------------------
87.     for varCol := 0 to StringGrid1.ColCount - 1 do
88.     begin
89.       //Write the text of the header cell //
90.       VarCELL := VarWORKSHEET.WriteText(0, varCol, StringGrid1.Cells[varCol, 0]);
91.       // Center text Horizontally //
92.       VarWORKSHEET.WriteHorAlignment(VarCELL, haCenter);
93.       // Center text Vertically //
94.       VarWORKSHEET.WriteVertAlignment(VarCELL, vaCenter);
95.       // Draw a default border around each cell
96.       VarWORKSHEET.writeborders(VarCELL, [cbNorth, cbSouth, cbEast, cbWest]);
97.       // text in header row in Bold //
98.       VarWORKSHEET.WriteFontStyle(VarCELL, [fssBold]);
99.       // text header font size //
100.       VarWORKSHEET.WriteFontSize(VarCELL, HEADER_FONT_SIZE);
101.       // name of the Font //
102.       VarWORKSHEET.WriteFontName(VarCELL, 'Arial');
103.       // Color Background of header cell //
104.       VarWORKSHEET.WriteBackgroundColor(VarCELL, scYellow);
105.     end;
106.     // Set the row height for the header row which has a smaller font than
107.     // the rest
108.     VarWORKSHEET.WriteRowHeight(0, HEADER_FONT_SIZE * NICE_ROWHEIGHT_FACTOR, suPoints);
109.     // Write the PageHeader: DATE.
110.     VarWORKSHEET.PageLayout.Headers[HEADER_FOOTER_INDEX_ALL] := '&C&D';
111.     // Write the data cells
112.     for varRow := 1 to StringGrid1.RowCount - 1 do
113.     begin
114.       for varCol := 0 to StringGrid1.ColCount - 1 do
115.       begin
116.         //check if value is number
117.         if TryStrToFloat(StringGrid1.Cells[varCol, varRow], tmpValue) then
118.           VarCELL := VarWORKSHEET.WriteNumber(varRow, varCol, tmpValue)
119.         else
120.           VarCELL := VarWORKSHEET.WriteText(varRow, varCol, StringGrid1.Cells[varCol, varRow]);
121.         // write border around the cells //
122.         VarWORKSHEET.WriteBorders
123.         (VarCELL, [cbNorth, cbSouth, cbEast, cbWest]);
124.         VarWORKSHEET.WriteHorAlignment
125.         (VarCELL, haCenter);
126.         // By default cells are bottom-aligned. It looks better if they are centered.
127.         VarWORKSHEET.WriteVertAlignment
128.         (VarCELL, vaCENTER);
129.       end;
130.     end;
131.     // Just to show you automatic row height adjustment (not needed since the
132.     // row heights have been handled already).
133.     // Adjust the row heights
134.     for varRow := 0 to VarWORKSHEET.GetLastRowIndex do
135.     begin
136.       VarWORKSHEET.WriteRowHeight
137.       (varRow, NICE_ROWHEIGHT_FACTOR * VarWORKSHEET.CalcAutoRowHeight(varRow),
138.         VarWORKBOOK.Units);
139.     end;
140.     //---------------------------------------------------------
141.     // Save the workbook as xlsx file
142.     //Showmessage('Will save to xlsx');
143.     VarWORKBOOK.WriteToFile('WIEZEN.xlsx', sfOOXML, True);
144.     // workbook.WriteToFile('Test.ods', sfOpenDocument, true);
145.     // or save it for LibreOffice
146.   finally
147.     // Destroy the workbook after usage.
148.     VarWORKBOOK.Free;
149.   end;
150. end;
151.  

[seghele0]

Sorry, but I get errors.
Can you please send me your updated code again.
I send you the old code again.

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

1. //  SAVE StringGrid
2. //  Saving an XLSX and TXT file.
3. procedure BewaarStringGrid(StringGrid1: TStringGrid);
4. const
5.   HEADER_FONT_SIZE = 11;   // in Points
6.   DEFAULT_FONT_SIZE = 15;  // in Points
7.   NICE_ROWHEIGHT_FACTOR = 1.5;  // adds extra margin to the cell
8. var
9.   varScoreBestandsnaam : String;
10.   varLengteLangsteNaam : Integer;
11.   varKolomBreedte : Integer;
12.   varTitelLijn, varNamenLijn, varScoreLijn: String;
13.   varRow : Integer;
14.   varCol : Integer;
15.   varFile1 : TextFile;
16.   // Following for XLSX
17.   VarWORKBOOK:  TsWorkbook;
18.   VarWORKSHEET: TsWorksheet;
19.   VarCELL: pCell;
20.   VarR: Integer;  // Row
21.   VarC: Integer;  // Column
22.   tmpvalue : Single;
23. begin
24.   // Show vertical and horizontal lines
25.   StringGrid1.Options := StringGrid1.Options + [goVertLine, goHorzLine];
26.   // Adjust column widths automatically
27.   StringGrid1.AutoSizeColumns;
28.   // Create a TXT file
29.   // First check the length of the longest name
30.   varLengteLangsteNaam := 0;
31.   for varCol := 0 to StringGrid1.ColCount - 1 do
32.   begin
33.     if (Length(StringGrid1.Cells[varCol, 0]) > varLengteLangsteNaam) then
34.     begin
35.        varLengteLangsteNaam := Length(StringGrid1.Cells[varCol, 0]);
36.     end;
37.   end;
38.   // Extra space between columns
39.   varKolomBreedte := varLengteLangsteNaam + 2;
40.   // End of preparation
41.   varScoreBestandsnaam := 'WIEZEN' + '.txt';
42.   // Write to the file
43.   AssignFile(varFile1, varScoreBestandsnaam);
44.   Try
45.     Rewrite(varFile1);
46.     varTitelLijn := 'Resultaat Wiezen - ' + formatdatetime('dd/mm/YYYY HH:MM',
47.                      Now);
48.     // Write the "title" line
49.     Writeln(varFile1, varTitelLijn);
50.     // Then put the names of the players in a line
51.     varNamenLijn := '';
52.     for varCol := 0 to StringGrid1.ColCount - 1 do
53.     begin
54.       varNamenLijn := varNamenLijn + PadLeft(StringGrid1.Cells[varCol, 0],
55.                       varKolomBreedte);
56.     end;
57.     // Write the names line
58.     Writeln(varFile1, varNamenLijn);
59.     // The lines with the scores
60.      for varRow := 1 to StringGrid1.RowCount - 1 do
61.     begin
62.       varScoreLijn := '';
63.       for varCol := 0 to StringGrid1.ColCount - 1 do
64.       begin
65.          varScoreLijn := varScoreLijn + PadLeft(StringGrid1.Cells[varCol,
66.                          varRow], varKolomBreedte);
67.       end;
68.       //Write the score line
69.       Writeln(varFile1, varScoreLijn);
70.     end;
71.   Finally
72.    //Close the file
73.    CloseFile(varFile1);
74.   End;
75. //**********************
76. //* CREATE a WORKBOOK  *
77. //**********************
78. VarWORKBOOK := TsWorkbook.Create;
79. try
80.   // Add a worksheet to the workbook
81.   VarWORKSHEET := VarWORKBOOK.AddWorksheet('Export from grid');
82.   // set default Font for the entire workbook  //
83.   // Later on, the Font Size for the "header" will be changed !!! //
84.   VarWORKBOOK.SetDefaultFont('Arial',DEFAULT_FONT_SIZE);
85.   VarWORKSHEET.WriteDefaultRowHeight(DEFAULT_FONT_SIZE*NICE_ROWHEIGHT_FACTOR, suPoints);
86.   // set default Column widths like in the StrngGrid //
87.   // Making use of "lpsUtils" in uses //
88.   for VarC := 0 to StringGrid1.ColCount-1 do
89.     VarWORKSHEET.writecolwidth
90.       (VarC, pxToPts(StringGrid1.ColWidths[VarC],Screen.PixelsPerInch),suPoints);
91.   //--------------------------------------------------------
92.   // Write the title row
93.   //---------------------------------------------------------
94.   for VarC := 0 to StringGrid1.ColCount-1 do
95.   begin
96.     //Write the text of the header cell //
97.     VarCELL:= VarWORKSHEET.WriteText(0, VarC, StringGrid1.Cells[VarC, 0]);
98.     // Center text Horizontally //
99.     VarWORKSHEET.WriteHorAlignment(VarCELL,haCenter);
100.     // Center text Vertically //
101.     VarWORKSHEET.WriteVertAlignment(VarCELL,vaCenter);
102.     // Draw a default border around each cell
103.     VarWORKSHEET.writeborders(VarCELL,[cbNorth,cbSouth,cbEast,cbWest]);
104.     // text in header row in Bold //
105.     VarWORKSHEET.WriteFontStyle(VarCELL, [fssBold]);
106.     // text header font size //
107.     VarWORKSHEET.WriteFontSize(VarCELL,HEADER_FONT_SIZE);
108.     // name of the Font //
109.     VarWORKSHEET.WriteFontName(VarCELL, 'Arial');
110.     // Color Background of header cell //
111.     VarWORKSHEET.WriteBackgroundColor(VarCELL,scYellow);
112.    end;
113.   // Set the row height for the header row which has a smaller font than
114.   // the rest
115.   VarWORKSHEET.WriteRowHeight(0, HEADER_FONT_SIZE*NICE_ROWHEIGHT_FACTOR, suPoints);
116.   // Write the PageHeader: DATE.
117.   VarWORKSHEET.PageLayout.Headers[HEADER_FOOTER_INDEX_ALL]:= '&C&D';
118.   // Write the data cells
119.   for VarR := 1 to StringGrid1.RowCount-1 do
120.   begin
121.     for VarC := 0 to StringGrid1.ColCount-1 do
122.     begin
123.       // check if value is number // from Forum
124.       if trystrtofloat(Stringgrid1.Cells[VarC,VarR],tmpvalue)then
125.       VarCELL:= VarWORKSHEET.Writenumber(VarR, VarC,tmpvalue)
126.       else
127.       // above from Forum
128.       VarCELL:= VarWORKSHEET.WriteText
129.                (VarR,VarC,StringGrid1.Cells[VarC,VarR]);
130.       // write border around the cells //
131.       VarWORKSHEET.WriteBorders
132.                (VarCELL,[cbNorth,cbSouth,cbEast,cbWest]);
133.       VarWORKSHEET.WriteHorAlignment
134.                (VarCELL,haCenter);
135.       // By default cells are bottom-aligned. It looks better if they are centered.
136.       VarWORKSHEET.WriteVertAlignment
137.                (VarCELL,vaCENTER);
138.     end;
139.   end;
140.   // Just to show you automatic row height adjustment (not needed since the
141.   // row heights have been handled already).
142.   // Adjust the row heights
143.   for VarR := 0 to VarWORKSHEET.GetLastRowIndex do
144.   begin
145.     VarWORKSHEET.WriteRowHeight
146.       (VarR, NICE_ROWHEIGHT_FACTOR * VarWORKSHEET.CalcAutoRowHeight(VarR),
147.       VarWORKBOOK.Units);
148.   end;
149.   //---------------------------------------------------------
150.  // Save the workbook as xlsx file
151.  //Showmessage('Will save to xlsx');
152.  VarWORKBOOK.WriteToFile('WIEZEN.xlsx', sfOOXML, true);
153.  // workbook.WriteToFile('Test.ods', sfOpenDocument, true);
154.  // or save it for LibreOffice
155. finally
156.  // Destroy the workbook after usage.
157.  VarWORKBOOK.Free;
158. end;
159. end;
160.  
161. procedure waitproc;//SpeedButton1Click(Sender: TObject);
162. begin
163.   try
164.      Application.CreateForm(TFwait, Fwait);
165.      Fwait.Show;
166.      Fwait.Update;
167.      Fwait.Width := 0;
168.      Fwait.Height := 0;
169.      while (Fwait.Width < 500) and (Fwait.Height < 300) do
170.      begin
171.         Fwait.Width := Fwait.Width + 5;
172.         Fwait.Height := Fwait.Height + 1;
173.         Fwait.Update;
174.         sleep(1);
175.         Fwait.Panelwait.caption := ' E I N D E  !!!';
176.         Fwait.Update;
177.      end;
178.      // eventueel uitvoeren van eigen proc.
179.   finally
180.      sleep(5000);
181.      while (Fwait.Width > 0) and (Fwait.Height > 0) do
182.      begin
183.         Fwait.Width := Fwait.Width - 5;
184.         Fwait.Height := Fwait.Height - 1;
185.         Fwait.Update;
186.         sleep(2);
187.       end;
188.   end;
189.       Fwait.Hide;
190.       Fwait.Release;
191. end;            

[paweld]

Sorry, but I get errors.

show error messages

[seghele0]

C:\Lazarus3\LAZARUSPROGS\WIEZEN-20241116\umain.pas(478,27) Hint: (5091) Local variable "tmpColCountArr" of a managed type does not seem to be initialized
C:\Lazarus3\LAZARUSPROGS\WIEZEN-20241116\umain.pas(506,77) Error: (3284) Operator is not overloaded: "LongInt" + "Char"
C:\Lazarus3\LAZARUSPROGS\WIEZEN-20241116\umain.pas(507,9) Fatal: (2003) Syntax error, ")" expected but "ELSE" found
Fatal: (1018) Compilation aborted
Error: C:\Lazarus3\fpc\3.2.2\bin\x86_64-win64\ppcx64.exe returned an error exitcode

I did a copy/paste of the entire procedure.
Thks.

[paweld]

try now:

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

1. //  SAVE StringGrid
2. //  Saving an XLSX and TXT file.
3. procedure BewaarStringGrid(StringGrid1: TStringGrid);
4. const
5.   HEADER_FONT_SIZE = 11;   // in Points
6.   DEFAULT_FONT_SIZE = 15;  // in Points
7.   NICE_ROWHEIGHT_FACTOR = 1.5;  // adds extra margin to the cell
8. var
9.   varScoreBestandsnaam: String;
10.   varCellLen: Integer;
11.   varLijn: String;
12.   varRow: Integer;
13.   varCol: Integer;
14.   varFile1: TextFile;
15.   // Following for XLSX
16.   VarWORKBOOK: TsWorkbook;
17.   VarWORKSHEET: TsWorksheet;
18.   VarCELL: pCell;
19.   VarR: Integer;  // Row
20.   VarC: Integer;  // Column
21.   tmpvalue: Single;
22.   //col length array
23.   tmpColCountArr: array of Integer;
24. begin
25.   // Show vertical and horizontal lines
26.   StringGrid1.Options := StringGrid1.Options + [goVertLine, goHorzLine];
27.   // Adjust column widths automatically
28.   StringGrid1.AutoSizeColumns;
29.   // Create a TXT file
30.   // check max length for each column
31.   SetLength(tmpColCountArr, StringGrid1.ColCount);
32.   for varCol := 0 to StringGrid1.ColCount - 1 do
33.   begin
34.     for varRow := 0 to StringGrid1.RowCount - 1 do
35.     begin
36.       varCellLen := Length(StringGrid1.Cells[varCol, varRow]);
37.       if varRow = 0 then
38.         tmpColCountArr[varCol] := varCellLen
39.       else if tmpColCountArr[varCol] < varCellLen then
40.         tmpColCountArr[varCol] := varCellLen;
41.     end;
42.   end;
43.   // End of preparation
44.   varScoreBestandsnaam := 'WIEZEN' + '.txt';
45.   // Write to the file
46.   AssignFile(varFile1, varScoreBestandsnaam);
47.   try
48.     Rewrite(varFile1);
49.     varLijn := 'Resultaat Wiezen - ' + formatdatetime('dd/mm/YYYY HH:MM', Now);
50.     // Write the "title" line
51.     Writeln(varFile1, varLijn);
52.     // Then put data
53.     for varRow := 1 to StringGrid1.RowCount - 1 do
54.     begin
55.       varLijn := '';
56.       for varCol := 0 to StringGrid1.ColCount - 1 do
57.       begin
58.         if TryStrToFloat(StringGrid1.Cells[varCol, varRow], tmpValue) then
59.           varLijn := varLijn + Format('%' + IntToStr(tmpColCountArr[varCol]) + 's', [FormatFloat('0.####', tmpValue)]) + '  '
60.         else
61.           varLijn := varLijn + Format('%-' + IntToStr(tmpColCountArr[varCol]) + 's', [StringGrid1.Cells[varCol, varRow]]) + '  ';
62.       end;
63.       //Write the line
64.       Writeln(varFile1, varLijn);
65.     end;
66.   finally
67.     //Close the file
68.     CloseFile(varFile1);
69.   end;
70.   //**********************
71.   //* CREATE a WORKBOOK  *
72.   //**********************
73.   VarWORKBOOK := TsWorkbook.Create;
74.   try
75.     // Add a worksheet to the workbook
76.     VarWORKSHEET := VarWORKBOOK.AddWorksheet('Export from grid');
77.     // set default Font for the entire workbook  //
78.     // Later on, the Font Size for the "header" will be changed !!! //
79.     VarWORKBOOK.SetDefaultFont('Arial', DEFAULT_FONT_SIZE);
80.     VarWORKSHEET.WriteDefaultRowHeight(DEFAULT_FONT_SIZE * NICE_ROWHEIGHT_FACTOR, suPoints);
81.     // set default Column widths like in the StrngGrid //
82.     // Making use of "lpsUtils" in uses //
83.     for VarC := 0 to StringGrid1.ColCount - 1 do
84.       VarWORKSHEET.writecolwidth(VarC, pxToPts(StringGrid1.ColWidths[VarC], Screen.PixelsPerInch), suPoints);
85.     //--------------------------------------------------------
86.     // Write the title row
87.     //---------------------------------------------------------
88.     for VarC := 0 to StringGrid1.ColCount - 1 do
89.     begin
90.       //Write the text of the header cell //
91.       VarCELL := VarWORKSHEET.WriteText(0, VarC, StringGrid1.Cells[VarC, 0]);
92.       // Center text Horizontally //
93.       VarWORKSHEET.WriteHorAlignment(VarCELL, haCenter);
94.       // Center text Vertically //
95.       VarWORKSHEET.WriteVertAlignment(VarCELL, vaCenter);
96.       // Draw a default border around each cell
97.       VarWORKSHEET.writeborders(VarCELL, [cbNorth, cbSouth, cbEast, cbWest]);
98.       // text in header row in Bold //
99.       VarWORKSHEET.WriteFontStyle(VarCELL, [fssBold]);
100.       // text header font size //
101.       VarWORKSHEET.WriteFontSize(VarCELL, HEADER_FONT_SIZE);
102.       // name of the Font //
103.       VarWORKSHEET.WriteFontName(VarCELL, 'Arial');
104.       // Color Background of header cell //
105.       VarWORKSHEET.WriteBackgroundColor(VarCELL, scYellow);
106.     end;
107.     // Set the row height for the header row which has a smaller font than
108.     // the rest
109.     VarWORKSHEET.WriteRowHeight(0, HEADER_FONT_SIZE * NICE_ROWHEIGHT_FACTOR, suPoints);
110.     // Write the PageHeader: DATE.
111.     VarWORKSHEET.PageLayout.Headers[HEADER_FOOTER_INDEX_ALL] := '&C&D';
112.     // Write the data cells
113.     for VarR := 1 to StringGrid1.RowCount - 1 do
114.     begin
115.       for VarC := 0 to StringGrid1.ColCount - 1 do
116.       begin
117.         // check if value is number // from Forum
118.         if trystrtofloat(Stringgrid1.Cells[VarC, VarR], tmpvalue) then
119.           VarCELL := VarWORKSHEET.Writenumber(VarR, VarC, tmpvalue)
120.         else
121.           // above from Forum
122.           VarCELL := VarWORKSHEET.WriteText(VarR, VarC, StringGrid1.Cells[VarC, VarR]);
123.         // write border around the cells //
124.         VarWORKSHEET.WriteBorders
125.         (VarCELL, [cbNorth, cbSouth, cbEast, cbWest]);
126.         VarWORKSHEET.WriteHorAlignment
127.         (VarCELL, haCenter);
128.         // By default cells are bottom-aligned. It looks better if they are centered.
129.         VarWORKSHEET.WriteVertAlignment
130.         (VarCELL, vaCENTER);
131.       end;
132.     end;
133.     // Just to show you automatic row height adjustment (not needed since the
134.     // row heights have been handled already).
135.     // Adjust the row heights
136.     for VarR := 0 to VarWORKSHEET.GetLastRowIndex do
137.     begin
138.       VarWORKSHEET.WriteRowHeight
139.       (VarR, NICE_ROWHEIGHT_FACTOR * VarWORKSHEET.CalcAutoRowHeight(VarR),
140.         VarWORKBOOK.Units);
141.     end;
142.     //---------------------------------------------------------
143.     // Save the workbook as xlsx file
144.     //Showmessage('Will save to xlsx');
145.     VarWORKBOOK.WriteToFile('WIEZEN.xlsx', sfOOXML, True);
146.     // workbook.WriteToFile('Test.ods', sfOpenDocument, true);
147.     // or save it for LibreOffice
148.   finally
149.     // Destroy the workbook after usage.
150.     VarWORKBOOK.Free;
151.   end;
152. end;
153.  
154. procedure waitproc;//SpeedButton1Click(Sender: TObject);
155. begin
156.   try
157.     Application.CreateForm(TFwait, Fwait);
158.     Fwait.Show;
159.     Fwait.Update;
160.     Fwait.Width := 0;
161.     Fwait.Height := 0;
162.     while (Fwait.Width < 500) and (Fwait.Height < 300) do
163.     begin
164.       Fwait.Width := Fwait.Width + 5;
165.       Fwait.Height := Fwait.Height + 1;
166.       Fwait.Update;
167.       sleep(1);
168.       Fwait.Panelwait.Caption := ' E I N D E  !!!';
169.       Fwait.Update;
170.     end;
171.     // eventueel uitvoeren van eigen proc.
172.   finally
173.     sleep(5000);
174.     while (Fwait.Width > 0) and (Fwait.Height > 0) do
175.     begin
176.       Fwait.Width := Fwait.Width - 5;
177.       Fwait.Height := Fwait.Height - 1;
178.       Fwait.Update;
179.       sleep(2);
180.     end;
181.   end;
182.   Fwait.Hide;
183.   Fwait.Release;
184. end;
185.  

[seghele0]

Sincere thanks for your effort.
 
After copy/pasting the procedure "BewaarStringGrid", the application works without error.
I don't think the alignment 'right' turned out well.
You can find the printout in the attachment.
The "xlsx" is OK, but the "txt" is apparently left-aligned.

[paweld]

With me it works correctly, I just had to change the initial index in line 53 from 1 to 0. 
What version of lazarus and fpc do you have?

[wp]

but the "txt" is apparently left-aligned.

To see the alignment of the txt file you must use a font with fixed character widths for displaying. The Arial-like font used in your pdf is proportional, i.e. each character has a different width, and it is not possible to align characters this way. If you insist on this font you should remove the padding by spaces and use a tab character to separate the fields in the text file; still you may have to adjust the tab width in the viewing application to get perfect alignment.

[seghele0]

What type of font is best for showing numbers (instead of Arial)?