Stringgrid to XLSX with border lines.
lazarus / 49-FPSpreadsheet

----------------------------------------------------------------------
seghele0 · 2023-08-06 14:59

Can you give me the code to get lines between rows and columns.
The exported XLSX file will then look clearer to read.

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i unit
Unit1;{$mode objfpc}{$H+}interfaceuses  Classes, SysUtils, Forms,
Controls, Graphics, Dialogs, Grids, ExtCtrls,  StdCtrls,
fpSpreadsheet, fpsTypes, xlsxOOXML;type  { TForm1 }  TForm1 =
class(TForm)    Button1: TButton;    Panel1: TPanel;    StringGrid1:
TStringGrid;    procedure Button1Click(Sender: TObject);  private 
public  end;var  Form1: TForm1;implementationuses  DateUtils;{$R
*.lfm}function MyStrToFloat(s: String): Double;var  fs:
TFormatSettings;begin  fs := FormatSettings;  fs.DecimalSeparator :=
'.';  if not TryStrToFloat(s, Result, fs) then  begin   
fs.DecimalSeparator := ',';    Result := StrToFloat(s, fs); 
end;end; function MyStrToDate(s: String): TDate;begin  Result :=
ScanDateTime('yyyy"/"mm"/"dd', s);end; function MyStrToCurr(s:
String): Double;begin  Result := MyStrToFloat(Copy(s, 1,
Length(s)-2));end;//**************************{ TForm1 }procedure
TForm1.Button1Click(Sender: TObject);var  workbook: TsWorkbook; 
worksheet: TsWorksheet;  r, c: Integer;begin  // Create a workbook 
workbook := TsWorkbook.Create;  try    // Add a worksheet to the
workbook    worksheet := workbook.AddWorksheet('Export from grid');  
// Write the title row    for c := 0 to StringGrid1.ColCount-1 do    
worksheet.WriteText(0, c, StringGrid1.Cells[c, 0]);    // Write the
data cells    for r := 1 to StringGrid1.RowCount-1 do    begin     
for c := 0 to StringGrid1.ColCount-1 do        case c of          0:
worksheet.WriteNumber(r, c, StrToInt(StringGrid1.cells[c, r]),
nfFixed, 0);          1: worksheet.WriteNumber(r, c,
MyStrToFloat(StringGrid1.Cells[c, r]), nfGeneral);          2:
worksheet.WriteText(r, c, StringGrid1.Cells[c, r]);          3:
worksheet.WriteDateTime(r, c, MyStrToDate(StringGrid1.Cells[c, r]),
nfShortDate);          4: worksheet.WriteCurrency(r, c,
MyStrToCurr(StringGrid1.Cells[c, r]), nfCurrency, 2, '$');       
end;    end;    // Save the workbook as xlsx file   
workbook.WriteToFile('Test.xlsx', sfOOXML, true);//  
 workbook.WriteToFile('Test.ods', sfOpenDocument, true);  // or save
it for LibreOffice  finally    // Destroy the workbook after usage.  
workbook.Free;  end;end;end.

----------------------------------------------------------------------
wp · 2023-08-06 16:04

I suppose you mean cell border lines?

Call worksheet.WriteBorders to define which side of the cell will get
a border, the sides are specified as a set of the values cbNorth,
cbSouth, cbEast, cbWest, cbDiagUp, cbDiagDown (type TsCellBorder).

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i   //
horizontal border lines above and below cell B2 
worksheet.WriteBorders(1, 1, [cbNorth, cbSouth]);
If you don't do anything else these borders will have black solid
lines. Additionally, you can also specify the appearance of each
border line by calling WriteBorderColor and/or WriteBorderLineStyle:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i   // The
bottom border line of cell B2 should be a thick line in red 
worksheet.WriteBorderLineStyle(1, 1, cbSouth, lsThick); 
worksheet.WriteBorderColor(1, 1, cbSouth, scRed);
Both calls can be combined if you define a cell border style and use
it in WriteBorderStyle:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i const 
THICK_RED_BORDER: TsCellBorderStyle = (LineStyle:lsThick;
Color:scRed);...  worksheet.WriteBorderStyle(1, 1, cbSourth,
THICK_RED_BORDER);
See attachment for a sample project similar to yours.

----------------------------------------------------------------------
seghele0 · 2023-08-07 15:51

Thanks for the example (zip).
However, what I hope to get is lines around all cells, horizontally
and vertically.
The number of rows can change and are therefore not predetermined.
Can someone correct the example?
Thanks.

----------------------------------------------------------------------
rvk · 2023-08-07 17:03

Quote from: seghele0 on August 07, 2023, 05:51:29 pmHowever, what I
hope to get is lines around all cells, horizontally and vertically.
Aren't there already lines between your rows and columns on screen?
Or is this for print (in which case you could enable that as page-
options)?

There are several options for lines between rows and columns.
1) The normal view grid lines. These can also be turned on for
printing.
2) More solid bold lines around each cell. (They will always show up
on print.)

Personally I find the second a bit overwhelming when viewing a
complete sheet.

----------------------------------------------------------------------
wp · 2023-08-07 17:18

Quote from: seghele0 on August 07, 2023, 05:51:29 pmThanks for the
example (zip).
However, what I hope to get is lines around all cells, horizontally
and vertically.
The number of rows can change and are therefore not predetermined.
Can someone correct the example?
Thanks.

Yes, I could. But I don't want to... Doing something by oneself is the
most important step in learning it. It's really easy, and I told you
everything you need in the previous post. Look at the code, try to
understand it. Ask if there is something which I did not describe well
enough. But don't ask me for a ready-made code sample.

P.S.
Well, I should be fair enough to point you also to the fpspreadsheet
documentation:
https://wiki.lazarus.freepascal.org/FPSpreadsheet#Cell_borders

----------------------------------------------------------------------
seghele0 · 2023-08-07 20:23

Ok I get it.
For a moment I forgot that a professional programmer doesn't want to
share his own efforts.
Anyway, thanks for the hints and friendly support.

----------------------------------------------------------------------
rvk · 2023-08-07 20:29

Quote from: seghele0 on August 07, 2023, 10:23:58 pmOk I get it.
For a moment I forgot that a professional programmer doesn't want to
share his own efforts.
Anyway, thanks for the hints and friendly support.

Uh? You do know wp wrote the whole fpspreadsheet you like to use.
And you say he doesn't like to share his own efforts.
Wow

----------------------------------------------------------------------
seghele0 · 2023-08-08 07:06

rvk + wp:
Sorry, my words were too hasty and ithey ndicate my stupidity.
This forum helped me a lot.

I apologize to everyone.

----------------------------------------------------------------------
rvk · 2023-08-08 11:01

Quote from: seghele0 on August 07, 2023, 10:23:58 pmOk I get it.
Did you find the part wp was talking about (and the solution)?

----------------------------------------------------------------------
seghele0 · 2023-08-08 13:18

At the moment I don't understand how to change the code.
I'll continue to look for code for a final solution.
Life goes on, even without or with a solution for the lines around the
cells.
Thank you for the sympathetic interventions.

----------------------------------------------------------------------
wp · 2023-08-08 13:47

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
worksheet.WriteBorders[r, c, [cbNorth, cbSouth, cbEast, cbWest])adds a
standard (black, thin line) border to all sides of the cell at row r
and column c. All you have to do is to put this into two loops running
r and c over all cells. The lower index is 0 in each case
(usually...), and in the code in my previous post, you can find out
how to determine the upper column and row index.

----------------------------------------------------------------------
seghele0 · 2023-08-08 14:40

Thanks.
I'm absent for a few days.
Will let you know later if I can handle it myself to modify the code.

----------------------------------------------------------------------
seghele0 · 2023-08-10 12:40

My programming level appears to give a poor result.
I changed the StringGrid with four text fields.
The cell borders are still missing on the whole xlsx grid.

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i unit
Unit1;{$mode objfpc}{$H+}interfaceuses  Classes, SysUtils, Forms,
Controls, Graphics, Dialogs, Grids, StdCtrls;type  { TForm1 }  TForm1
= class(TForm)    Button1: TButton;    StringGrid1: TStringGrid;   
procedure Button1Click(Sender: TObject);  private  public  end; var 
Form1: TForm1; implementationuses  DateUtils, fpSpreadsheet, fpstypes,
xlsxooxml; {$R *.lfm} { TForm1
}//**************************************************procedure
TForm1.Button1Click(Sender: TObject);var  VarWorkbook:  TsWorkbook; 
VarWorksheet: TsWorksheet;  VarR: Integer;  // Row  VarC: Integer;  //
Column  VarLR: Integer; // Last ColIndex  VarLC: Integer; // Last
RowIndexbegin  // Create a workbook  VarWorkbook :=
TsWorkbook.Create;  try    // Add a worksheet to the workbook   
VarWorksheet := varWorkbook.AddWorksheet('Export from grid');    //
Write the title row    for VarC := 0 to StringGrid1.ColCount-1 do    
VarWorksheet.WriteText(0, VarC, StringGrid1.Cells[VarC, 0]);    //
Write the data cells    for VarR := 1 to StringGrid1.RowCount-1 do   
begin      for VarC := 0 to StringGrid1.ColCount-1 do        case VarC
of          0: VarWorksheet.WriteText             (VarR, VarC,
StringGrid1.Cells[VarC, VarR]);          1: VarWorksheet.WriteText   
        (VarR, VarC, StringGrid1.Cells[VarC, VarR]);          2:
VarWorksheet.WriteText             (VarR, VarC,
StringGrid1.Cells[VarC, VarR]);          3: VarWorksheet.WriteText   
        (VarR, VarC, StringGrid1.Cells[VarC, VarR]);        end;   
end;    VarLC := VarWorksheet.GetLastColIndex;    VarLR :=
VarWorksheet.GetLastRowIndex;    //  ->  horizontal lines //   
//*********************************************************    for
VarC := 1 to Varlc-1 do    begin     
 VarWorksheet.WriteBorders(VarC,VarR,[cbNorth, cbSouth, cbEast,
cbWest]);    end;    //  ->  vertical lines  //   
//*********************************************************    for
VarR := 1 to Varlr-1 do     begin     
 VarWorksheet.WriteBorders(VarC,VarR,[cbNorth, cbSouth, cbEast,
cbWest]);     end;   
//*********************************************************     //
Save the workbook as xlsx file    Showmessage('Will save to xlsx');  
VarWorkbook.WriteToFile('Test.xlsx', sfOOXML, true);    //
workbook.WriteToFile('Test.ods', sfOpenDocument, true);    // or save
it for LibreOffice    Showmessage('Saving done !');  finally    //
Destroy the workbook after usage.    VarWorkbook.Free;  end;end; end.

----------------------------------------------------------------------
bytebites · 2023-08-10 12:56

Should the loop be like
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i for VarC :=
1 to Varlc-1 do  for VarR := 1 to Varlr-1 do    begin     
 VarWorksheet.WriteBorders(VarC,VarR,[cbNorth, cbSouth, cbEast,
cbWest]);    end;

----------------------------------------------------------------------
seghele0 · 2023-08-10 14:06

Thanks for the tip, but apparently it's incorrect.
Attachment:  the xlsx.


Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i unit
Unit1;{$mode objfpc}{$H+}interfaceuses  Classes, SysUtils, Forms,
Controls, Graphics, Dialogs, Grids, StdCtrls;type  { TForm1 }  TForm1
= class(TForm)    Button1: TButton;    StringGrid1: TStringGrid;   
procedure Button1Click(Sender: TObject);  private  public  end;var 
Form1: TForm1; implementationuses  DateUtils, fpSpreadsheet, fpstypes,
xlsxooxml; {$R *.lfm} { TForm1
}//**************************************************procedure
TForm1.Button1Click(Sender: TObject);var  VarWorkbook:  TsWorkbook; 
VarWorksheet: TsWorksheet;  VarR: Integer;  // Row  VarC: Integer;  //
Column  VarLR: Integer; // Last ColIndex  VarLC: Integer; // Last
RowIndexbegin  // Create a workbook  VarWorkbook :=
TsWorkbook.Create;  try    // Add a worksheet to the workbook   
VarWorksheet := varWorkbook.AddWorksheet('Export from grid');    //
Write the title row    for VarC := 0 to StringGrid1.ColCount-1 do    
VarWorksheet.WriteText(0, VarC, StringGrid1.Cells[VarC, 0]);    //
Write the data cells    for VarR := 1 to StringGrid1.RowCount-1 do   
begin      for VarC := 0 to StringGrid1.ColCount-1 do        case VarC
of          0: VarWorksheet.WriteText             (VarR, VarC,
StringGrid1.Cells[VarC, VarR]);          1: VarWorksheet.WriteText   
        (VarR, VarC, StringGrid1.Cells[VarC, VarR]);          2:
VarWorksheet.WriteText             (VarR, VarC,
StringGrid1.Cells[VarC, VarR]);          3: VarWorksheet.WriteText   
        (VarR, VarC, StringGrid1.Cells[VarC, VarR]);        end;   
end;    VarLC := VarWorksheet.GetLastColIndex;    VarLR :=
VarWorksheet.GetLastRowIndex;    //  ->  horizontal lines //   
//*********************************************************    for
VarC := 1 to VarLC-1 do      for VarR := 1 to VarLR-1 do      begin  
    VarWorksheet.WriteBorders(VarC,VarR,[cbNorth, cbSouth, cbEast,
cbWest]);    end;   
//*********************************************************     //
Save the workbook as xlsx file    Showmessage('Will save to xlsx');  
VarWorkbook.WriteToFile('Test.xlsx', sfOOXML, true);    //
workbook.WriteToFile('Test.ods', sfOpenDocument, true);    // or save
it for LibreOffice    Showmessage('Saving done !');  finally    //
Destroy the workbook after usage.    VarWorkbook.Free;  end;end;end.

----------------------------------------------------------------------
rvk · 2023-08-10 14:10

Quote from: seghele0 on August 10, 2023, 04:06:38 pmThanks for the
tip, but apparently it's incorrect.@wp said:
worksheet.WriteBorders[r, c, [cbNorth, cbSouth, cbEast, cbWest])

So I assume the first parameter is r for ROW and second c for Column.

You have
VarWorksheet.WriteBorders(VarC,VarR,[cbNorth, cbSouth, cbEast,
cbWest]);

So you are reversing Column and Rows.

And he also mentioned it was a zero based index (0-based).
So you should begin from 0 to count - 1.

Does that help you fix your code?

----------------------------------------------------------------------
seghele0 · 2023-08-10 14:42

Have already tried to start from 0 instead of 1.
The result is already a little bit better, but still no solution.
As you can see in the attachment, not all cells are outlined, the top
lines continue beyond the active cells.

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i unit
Unit1;{$mode objfpc}{$H+}interfaceuses  Classes, SysUtils, Forms,
Controls, Graphics, Dialogs, Grids, StdCtrls;type  { TForm1 }  TForm1
= class(TForm)    Button1: TButton;    StringGrid1: TStringGrid;   
procedure Button1Click(Sender: TObject);  private  public  end; var 
Form1: TForm1; implementationuses  DateUtils, fpSpreadsheet, fpstypes,
xlsxooxml; {$R *.lfm} { TForm1
}//**************************************************procedure
TForm1.Button1Click(Sender: TObject);var  VarWorkbook:  TsWorkbook; 
VarWorksheet: TsWorksheet;  VarR: Integer;  // Row  VarC: Integer;  //
Column  VarLR: Integer; // Last ColIndex  VarLC: Integer; // Last
RowIndexbegin  // Create a workbook  VarWorkbook :=
TsWorkbook.Create;  try    // Add a worksheet to the workbook   
VarWorksheet := varWorkbook.AddWorksheet('Export from grid');    //
Write the title row    for VarC := 0 to StringGrid1.ColCount-1 do    
VarWorksheet.WriteText(0, VarC, StringGrid1.Cells[VarC, 0]);    //
Write the data cells    for VarR := 0 to StringGrid1.RowCount-1 do   
begin      for VarC := 0 to StringGrid1.ColCount-1 do        case VarC
of          0: VarWorksheet.WriteText             (VarR, VarC,
StringGrid1.Cells[VarC, VarR]);          1: VarWorksheet.WriteText   
        (VarR, VarC, StringGrid1.Cells[VarC, VarR]);          2:
VarWorksheet.WriteText             (VarR, VarC,
StringGrid1.Cells[VarC, VarR]);          3: VarWorksheet.WriteText   
        (VarR, VarC, StringGrid1.Cells[VarC, VarR]);        end;   
end;    VarLC := VarWorksheet.GetLastColIndex;    VarLR :=
VarWorksheet.GetLastRowIndex;    //  ->  horizontal lines //   
//*********************************************************    for
VarR := 0 to VarLR-1 do      for VarC := 0 to VarLC-1 do      begin  
    VarWorksheet.WriteBorders(VarR,VarC,[cbNorth, cbSouth, cbEast,
cbWest]);    end;   
//*********************************************************     //
Save the workbook as xlsx file    Showmessage('Will save to xlsx');  
VarWorkbook.WriteToFile('Test.xlsx', sfOOXML, true);    //
workbook.WriteToFile('Test.ods', sfOpenDocument, true);    // or save
it for LibreOffice    Showmessage('Saving done !');  finally    //
Destroy the workbook after usage.    VarWorkbook.Free;  end;end;end.

----------------------------------------------------------------------
rvk · 2023-08-10 15:25

Quote from: seghele0 on August 10, 2023, 04:42:47 pmHave already tried
to start from 0 instead of 1.
The result is already a little bit better, but still no solution.
As you can see in the attachment, not all cells are outlined, the top
lines continue beyond the active cells.
Well... it's all in the name.
It's zero-based index and the function is called GetLastColIndex.
What do you think GetLastColIndex means.
It's not a count (like I suggested  earlier) but an INDEX number.

So you shouldn't iterate to -1 but to the INDEX itself.

Does that make sense (and help your code)?

----------------------------------------------------------------------
seghele0 · 2023-08-11 12:53

Had to think a lot, but the solution has finally arrived, thanks to
the positive hints from this forum.

Sincere thanks to everyone.
 
My brain is exhausted but happy.

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i unit
Unit1;// Update:   11/08/2023.{$mode objfpc}{$H+}interfaceuses 
Classes, SysUtils, Forms, Controls, Graphics, Dialogs, Grids,
StdCtrls;type  { TForm1 }  TForm1 = class(TForm)    Button1: TButton; 
  StringGrid1: TStringGrid;    procedure Button1Click(Sender:
TObject);  private  public  end;var  Form1: TForm1;implementationuses 
 DateUtils, fpSpreadsheet, fpstypes, xlsxooxml; {$R *.lfm}{ TForm1
}//**************************************************procedure
TForm1.Button1Click(Sender: TObject);var  VarWorkbook:  TsWorkbook; 
VarWorksheet: TsWorksheet;  VarR: Integer;  // Row  VarC: Integer;  //
Column  VarLR: Integer; // Last ColIndex  VarLC: Integer; // Last
RowIndexbegin  // Create a workbook  VarWorkbook :=
TsWorkbook.Create;  try    // Add a worksheet to the workbook   
VarWorksheet := varWorkbook.AddWorksheet('Export from grid');    //
Write the title row    for VarC := 0 to StringGrid1.ColCount-1 do    
VarWorksheet.WriteText(0, VarC, StringGrid1.Cells[VarC, 0]);    //
Write the data cells    for VarR := 0 to StringGrid1.RowCount-1 do   
begin      for VarC := 0 to StringGrid1.ColCount-1 do        case VarC
of          0: VarWorksheet.WriteText             (VarR, VarC,
StringGrid1.Cells[VarC, VarR]);          1: VarWorksheet.WriteText   
        (VarR, VarC, StringGrid1.Cells[VarC, VarR]);          2:
VarWorksheet.WriteText             (VarR, VarC,
StringGrid1.Cells[VarC, VarR]);          3: VarWorksheet.WriteText   
        (VarR, VarC, StringGrid1.Cells[VarC, VarR]);          4:
VarWorksheet.WriteText             (VarR, VarC,
StringGrid1.Cells[VarC, VarR]);        end;    end;    // [b]ColIndex
+ 1[/b] //    VarLC := VarWorksheet.GetLastColIndex +1;    VarLR :=
VarWorksheet.GetLastRowIndex +1;    //  ->  horizontal lines //   
//*********************************************************    for
VarR := 0 to VarLR-1 do      for VarC := 0 to VarLC-1 do      begin  
    VarWorksheet.WriteBorders(VarR,VarC,[cbNorth, cbSouth, cbEast,
cbWest]);    end;   
//*********************************************************     //
Save the workbook as xlsx file    Showmessage('Will save to xlsx');  
VarWorkbook.WriteToFile('Test.xlsx', sfOOXML, true);    //
workbook.WriteToFile('Test.ods', sfOpenDocument, true);    // or save
it for LibreOffice    Showmessage('Saving done !');  finally    //
Destroy the workbook after usage.    VarWorkbook.Free;  end;end;end.

----------------------------------------------------------------------
rvk · 2023-08-11 13:13

Did you read my post?

You are still doing VarLR-1 in the loop but now you have added one in
the assignment of VarLR (and VarLC) (+1).

It might have been better to just do the loop to VarLR instead of
VarLR-1 (and not add 1 in the assignment of VarLC and VarLR).

Quote from: rvk on August 10, 2023, 05:25:53 pmWell... it's all in the
name.
It's zero-based index and the function is called GetLastColIndex.
What do you think GetLastColIndex means.
It's not a count (like I suggested  earlier) but an INDEX number.

So you shouldn't iterate to -1 but to the INDEX itself.

Does that make sense (and help your code)?


Your way works too but is a bit illogical (since VarLR and VarLC now
hold a value of last index + 1).

----------------------------------------------------------------------
seghele0 · 2023-08-11 13:59

Have modified the code.
THANKS.

How can the tkst be placed in the middle of the cells?


Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i unit
Unit1;// Update:   11/08/2023.{$mode objfpc}{$H+}interfaceuses 
Classes, SysUtils, Forms, Controls, Graphics, Dialogs, Grids,
StdCtrls;type  { TForm1 }  TForm1 = class(TForm)    Button1: TButton; 
  StringGrid1: TStringGrid;    procedure Button1Click(Sender:
TObject);  private  public  end; var  Form1:
TForm1; implementationuses  DateUtils, fpSpreadsheet, fpstypes,
xlsxooxml; {$R *.lfm}{ TForm1
}//**************************************************procedure
TForm1.Button1Click(Sender: TObject);var  VarWorkbook:  TsWorkbook; 
VarWorksheet: TsWorksheet;  VarR: Integer;  // Row  VarC: Integer;  //
Column  VarLR: Integer; // Last ColIndex  VarLC: Integer; // Last
RowIndexbegin  // Create a workbook  VarWorkbook :=
TsWorkbook.Create;  try    // Add a worksheet to the workbook   
VarWorksheet := varWorkbook.AddWorksheet('Export from grid');    //
Write the title row    for VarC := 0 to StringGrid1.ColCount-1 do    
VarWorksheet.WriteText(0, VarC, StringGrid1.Cells[VarC, 0]);    //
Write the data cells    for VarR := 0 to StringGrid1.RowCount-1 do   
begin      for VarC := 0 to StringGrid1.ColCount-1 do        case VarC
of          0: VarWorksheet.WriteText             (VarR, VarC,
StringGrid1.Cells[VarC, VarR]);          1: VarWorksheet.WriteText   
        (VarR, VarC, StringGrid1.Cells[VarC, VarR]);          2:
VarWorksheet.WriteText             (VarR, VarC,
StringGrid1.Cells[VarC, VarR]);          3: VarWorksheet.WriteText   
        (VarR, VarC, StringGrid1.Cells[VarC, VarR]);          4:
VarWorksheet.WriteText             (VarR, VarC,
StringGrid1.Cells[VarC, VarR]);        end;    end;    VarLC :=
VarWorksheet.GetLastColIndex;      VarLR :=
VarWorksheet.GetLastRowIndex;    //  ->  horizontal lines //   
//*********************************************************    for
VarR := 0 to VarLR do      for VarC := 0 to VarLC do      begin      
VarWorksheet.WriteBorders(VarR,VarC,[cbNorth, cbSouth, cbEast,
cbWest]);    end;   
//*********************************************************     //
Save the workbook as xlsx file    Showmessage('Will save to xlsx');  
VarWorkbook.WriteToFile('Test.xlsx', sfOOXML, true);    //
workbook.WriteToFile('Test.ods', sfOpenDocument, true);    // or save
it for LibreOffice    Showmessage('Saving done !');  finally    //
Destroy the workbook after usage.    VarWorkbook.Free;  end;end;end.

----------------------------------------------------------------------
rvk · 2023-08-11 14:12

Quote from: seghele0 on August 11, 2023, 03:59:30 pmHow can the tkst
be placed in the middle of the cells?

Probably with this function:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i function
TsWorksheet.WriteHorAlignment(ARow, ACol: Cardinal; AValue:
TsHorAlignment): PCell; overload;
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i type 
TsHorAlignment = (haDefault, haLeft, haCenter, haRight);
A google search for fpspreadsheet horizontal alignment
https://www.google.com/search?q=fpspreadsheet+horizontal+alignment
would have gotten you to this site where it is explained.
https://wiki.lazarus.freepascal.org/FPSpreadsheet#Text_alignment

It was easy to find.

----------------------------------------------------------------------
seghele0 · 2023-08-11 15:02

Thank you.
Will try it out in the next few days.

----------------------------------------------------------------------
wp · 2023-08-11 20:34

Ignoring the fact that you write row 0 twice (once in the split-off
header handling, and once in the data block where your row loop
includes the header row again), and that you use a case instruction
although all cases are the same, your code is correct.

But you can further optimize the code because you can do the
formatting task in the same loop in which you populate the worksheet.
And when you do several operations on the same cell you could store
the return value of the first WriteXXXX method in a variable of type
PCell which it is a pointer to the cell handled. You could use this
cell pointer in the next WriteXXXX call to identify the cell to be
used; this is faster than using the row and column indices because in
this case the internal data structure of the worksheet does not have
to be searched for the cell every time.

Here is a modification of your code, demonstrating these ideas (I also
formatted the header row in bold to emphasize it)

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i var 
VarWorkbook: TsWorkbook;  VarWorksheet: TsWorksheet;  VarCell:
PCell;begin  // Create a workbook  VarWorkbook := TsWorkbook.Create; 
try    // Add a worksheet to the workbook    VarWorksheet :=
varWorkbook.AddWorksheet('Export from grid');    // Write the title
row    for VarC := 0 to StringGrid1.ColCount-1 do    begin     
VarCell := VarWorksheet.WriteText(0, VarC, StringGrid1.Cells[VarC,
0]);         // write value      VarWorksheet.WriteBorders(VarCell,
[cbNorth, cbSouth, cbEast, cbWest]);         // add borders     
VarWorksheet.WriteHorAlignment(VarCell, haCenter);                   
         // centered text      VarWorksheet.WriteFontStyle(VarCell,
[fssBold]);                                // bold title    end;    //
Write the data cells    for VarR := 1 to StringGrid1.RowCount-1 do  
// First index must be 1 because row 0 already has been exported!   
begin      for VarC := 0 to StringGrid1.ColCount-1 do      begin     
 { --- why a case instruction here, when every case does the same ?  
     case VarC of          0: VarCell := VarWorksheet.WriteText(VarR,
VarC, StringGrid1.Cells[VarC, VarR]);          1: VarCell :=
VarWorksheet.WriteText(VarR, VarC, StringGrid1.Cells[VarC, VarR]);   
     2: VarCell := VarWorksheet.WriteText(VarR, VarC,
StringGrid1.Cells[VarC, VarR]);          3: VarCell :=
VarWorksheet.WriteText(VarR, VarC, StringGrid1.Cells[VarC, VarR]);   
     4: VarCell := VarWorksheet.WriteText(VarR, VarC,
StringGrid1.Cells[VarC, VarR]);        end;        }        VarCell :=
VarWorksheet.WriteText(VarR, VarC, StringGrid1.Cells[VarC, VarR]);  
// cell text        VarWorksheet.WriteBorders(VarCell, [cbNorth,
cbSouth, cbEast, cbWest]);         // add borders       
VarWorksheet.WriteHorAlignment(VarCell, haCenter);                   
         // centered text      end;    end;    ....

----------------------------------------------------------------------
seghele0 · 2023-08-13 14:59

Many thanks for your the code.
Tried to adjust the font size, my code works. But I don't know if it's
done the right way.

I can't figure out how to specify the rowheight of the stringgrid.

As you can see, everything is moving forward, sometimes slowly but
always hopeful.
Fortunately, I can count on your support.

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i unit
Unit1;// Update:   13/08/2023.{$mode objfpc}{$H+}interfaceuses 
Classes, SysUtils, Forms, Controls, Graphics, Dialogs, Grids,
StdCtrls;type  { TForm1 }  TForm1 = class(TForm)    Button1: TButton; 
  StringGrid1: TStringGrid;    procedure Button1Click(Sender:
TObject);  private  public  end;var  Form1: TForm1;implementationuses 
DateUtils, fpSpreadsheet, fpstypes, xlsxooxml; {$R *.lfm}{ TForm1
}//**************************************************procedure
TForm1.Button1Click(Sender: TObject);var  VarWorkbook:  TsWorkbook; 
VarWorksheet: TsWorksheet;  VarCell: pCell;  VarR: Integer;  // Row 
VarC: Integer;  // Column  VarLR: Integer; // Last ColIndex  VarLC:
Integer; // Last RowIndex [i][b]  Vari:    Integer;  Varfont:
TsFont;[/b][/i]begin   Vari:= 0;  // Create a workbook  VarWorkbook :=
TsWorkbook.Create;  try    // Add a worksheet to the workbook   
VarWorksheet := varWorkbook.AddWorksheet('Export from grid');    //
Write the title row    for VarC := 0 to StringGrid1.ColCount-1 do   
begin      VarcELL:= VarWorksheet.WriteText(0, VarC,
StringGrid1.Cells[VarC, 0]);     
VarWorksheet.writeborders(VarCell,[cbNorth,cbSouth,cbEast,cbWest]);  
  VarWorksheet.WriteHorAlignment(VarCell,haCenter);     
VarWorksheet.WriteFontStyle(VarCell, [fssBold]);[i][b]     // Make de
fontsize from Row 0 (Header) //      VarFont :=
VarWorkbook.GetFont(Vari);      VarFont.Size := 12;[/b][/i]    end;  
// Write the data cells    for VarR := 1 to StringGrid1.RowCount-1 do 
  begin      for VarC := 0 to StringGrid1.ColCount-1 do      begin   
   VarCell:= VarWorksheet.WriteText               
 (VarR,VarC,StringGrid1.Cells[VarC,VarR]);       
VarWorksheet.WriteBorders               
 (VarCell,[cbNorth,cbSouth,cbEast,cbWest]);       
VarWorksheet.WriteHorAlignment                 (VarCell,haCenter);   
 end;    end;    VarLC := VarWorksheet.GetLastColIndex; //+1;    VarLR
:= VarWorksheet.GetLastRowIndex; //+1;    //  ->  horizontal lines // 
  //*********************************************************    for
VarR := 0 to VarLR do      for VarC := 0 to VarLC do      begin      
VarWorksheet.WriteBorders(VarR,VarC,[cbNorth, cbSouth, cbEast,
cbWest]);        end; [i][b]   // Make de fontsize from Row 1 to end
//     VarFont := VarWorkbook.GetFont(Vari);     VarFont.Size :=
15;[/b][/i]   
//*********************************************************     //
Save the workbook as xlsx file    Showmessage('Will save to xlsx');  
VarWorkbook.WriteToFile('Test.xlsx', sfOOXML, true);    //
workbook.WriteToFile('Test.ods', sfOpenDocument, true);    // or save
it for LibreOffice    Showmessage('Saving done !');  finally    //
Destroy the workbook after usage.    VarWorkbook.Free;  end;end;end.

----------------------------------------------------------------------
wp · 2023-08-13 16:43

The easiest way to change the font size is to call
Worksheet.WriteFontSize(cell, size, units) where "units" should be
suPoints if "size" is given in points (other convenient units, for
examples, are suMillimeters or suInches).

The way you change the font size in the title row looks correct. I
don't think, however, that the way how you set the font size for the
data rows has any effect because this command is placed outside the
loop.

Moreover, you can remove the code belonging to the comment "// -->
horizontal lines //" because that code is already contained in the
upper loop filling the cells.

If you have other wishes for formatting you should read the
documentation
(https://wiki.lazarus.freepascal.org/FPSpreadsheet#Cell_formatting),
and/or study the attached sample project which is based on your case
but has different data types in the columns, formats the header row
with gray background and thick bottom border line, etc. Just read the
comments which explain what is happening...

----------------------------------------------------------------------
seghele0 · 2023-08-14 11:57

WP,
Many thanks for your explanation.
I'll try to make something out of it.
Succeeding is something else .......

----------------------------------------------------------------------
seghele0 · 2023-08-15 11:12

WP,
May I hope that now the modified code is correct?
Your last example is a fantastic help for low level programmers like
me.

For me it is purely a hobby and a positive event for the brain.

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i unit
Unit1;// Update:   15/08/2023.{$mode objfpc}{$H+}interfaceuses 
Classes, SysUtils, Forms, Controls, Graphics, Dialogs, Grids,
StdCtrls;type  { TForm1 }  TForm1 = class(TForm)    Button1: TButton; 
  StringGrid1: TStringGrid;    procedure Button1Click(Sender:
TObject);  private  public  end;var  Form1: TForm1;implementationuses 
DateUtils,  fpSpreadsheet, fpstypes, fpsUtils,  xlsxooxml;{$R *.lfm}{
TForm1 }//**************************************************procedure
TForm1.Button1Click(Sender: TObject);var  VarWORKBOOK:  TsWorkbook; 
VarWORKSHEET: TsWorksheet;  VarCELL: pCell;  VarR: Integer;  // Row 
VarC: Integer;  // Column  VarLR: Integer; // Last Col-Index  VarLC:
Integer; // Last Row-Indexbegin 
//---------------------------------------------------------  // Create
a workbook 
//--------------------------------------------------------- 
VarWORKBOOK := TsWorkbook.Create;  try    // Add a worksheet to the
workbook    VarWORKSHEET := VarWORKBOOK.AddWorksheet('Export from
grid');    // set default Font for the entire workbook  //    // Later
on the Font Size for the "header" will be changed !!! //   
VarWORKBOOK.SetDefaultFont('Arial',16);   
//************************************************************    //
set default Column widths like in the StrngGrid //    //Making use of
"lpsUtils" in uses //    for VarC := 0 to StringGrid1.ColCount-1 do  
VarWORKSHEET.writecolwidth    (VarC,
pxToPts(StringGrid1.ColWidths[VarC],Screen.PixelsPerInch),suPoints); 
 //--------------------------------------------------------    //
Write the title row   
//---------------------------------------------------------    for
VarC := 0 to StringGrid1.ColCount-1 do    begin      //Write the text
of the header cell //      VarCELL:= VarWORKSHEET.WriteText(0, VarC,
StringGrid1.Cells[VarC, 0]);      // Center text Horizontally //     
VarWORKSHEET.WriteHorAlignment(VarCELL,haCenter);      // Center text
Vertically //      VarWORKSHEET.WriteVertAlignment(VarCELL,vaCenter); 
    // Draw a default border around each cell     
VarWORKSHEET.writeborders(VarCELL,[cbNorth,cbSouth,cbEast,cbWest]);  
  // text in header row in Bold //     
VarWORKSHEET.WriteFontStyle(VarCELL, [fssBold]);      // text header
font size //      VarWORKSHEET.WriteFontSize(VarCELL,10);      // name
of the Font //      VarWORKSHEET.WriteFontName(VarCELL, 'Arial');    
// Color Background of header cell //     
VarWORKSHEET.WriteBackgroundColor(VarCELL,scYellow);     end;   
//-------------------------------------------------    // Write the
data cells    //-------------------------------------------------   
for VarR := 1 to StringGrid1.RowCount-1 do    begin      for VarC := 0
to StringGrid1.ColCount-1 do      begin        VarCELL:=
VarWORKSHEET.WriteText               
 (VarR,VarC,StringGrid1.Cells[VarC,VarR]);        // write border
around the cells //        VarWORKSHEET.WriteBorders               
 (VarCELL,[cbNorth,cbSouth,cbEast,cbWest]);       
VarWORKSHEET.WriteHorAlignment                 (VarCELL,haCenter);   
 end;    end;    VarLC := VarWORKSHEET.GetLastColIndex; //+1;    VarLR
:= VarWORKSHEET.GetLastRowIndex; //+1;   
//---------------------------------------------------------    // Save
the workbook as xlsx file   
//---------------------------------------------------------   
Showmessage('Will save to xlsx');   
VarWORKBOOK.WriteToFile('Test.xlsx', sfOOXML, true);    //
workbook.WriteToFile('Test.ods', sfOpenDocument, true);    // or save
it for LibreOffice    Showmessage('Saving done !');  finally    //
Destroy the workbook after usage.    VarWORKBOOK.Free;  end;end;end.

----------------------------------------------------------------------
wp · 2023-08-15 12:26

AFAICS, the assignment to VarLC and VarLR in the two lines before the
"Save workbook to XLSX file" comment are not needed anywhere, you can
remove them, as well as the associated variable declarations in "var".

----------------------------------------------------------------------
seghele0 · 2023-08-15 14:03

THANKS.

----------------------------------------------------------------------
seghele0 · 2023-08-16 13:54

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
Varworksheet.WriteRowHeight(0, round(14*1.5), suPoints);

----------------------------------------------------------------------
wp · 2023-08-16 15:58

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i unit
Unit1;// Update:   15/08/2023.{$mode objfpc}{$H+}interfaceuses 
Classes, SysUtils, Forms, Controls, Graphics, Dialogs, Grids,
StdCtrls;type  { TForm1 }  TForm1 = class(TForm)    Button1: TButton; 
  StringGrid1: TStringGrid;    procedure Button1Click(Sender:
TObject);  private  public  end;var  Form1: TForm1;implementationuses 
DateUtils,  fpSpreadsheet, fpstypes, fpsUtils,  xlsxooxml;{$R *.lfm}{
TForm1 }//**************************************************procedure
TForm1.Button1Click(Sender: TObject);const  HEADER_FONT_SIZE = 10;  
// in Points  DEFAULT_FONT_SIZE = 12;  // in Points 
NICE_ROWHEIGHT_FACTOR = 1.5;  // adds extra margin to the cellvar 
VarWORKBOOK:  TsWorkbook;  VarWORKSHEET: TsWorksheet;  VarCELL:
pCell;  VarR: Integer;  // Row  VarC: Integer;  // Columnbegin 
//---------------------------------------------------------  // Create
a workbook 
//--------------------------------------------------------- 
VarWORKBOOK := TsWorkbook.Create;  try    // Add a worksheet to the
workbook    VarWORKSHEET := VarWORKBOOK.AddWorksheet('Export from
grid');    // set default Font for the entire workbook  //    // Later
on the Font Size for the "header" will be changed !!! //   
VarWORKBOOK.SetDefaultFont('Arial',DEFAULT_FONT_SIZE);    VarWORKSHEET
.WriteDefaultRowHeight(DEFAULT_FONT_SIZE*NICE_ROWHEIGHT_FACTOR,
suPoints);   
//************************************************************    //
set default Column widths like in the StrngGrid //    //Making use of
"lpsUtils" in uses //    for VarC := 0 to StringGrid1.ColCount-1 do  
  VarWORKSHEET.writecolwidth        (VarC,
pxToPts(StringGrid1.ColWidths[VarC],Screen.PixelsPerInch),suPoints); 
 //--------------------------------------------------------    //
Write the title row   
//---------------------------------------------------------    for
VarC := 0 to StringGrid1.ColCount-1 do    begin      //Write the text
of the header cell //      VarCELL:= VarWORKSHEET.WriteText(0, VarC,
StringGrid1.Cells[VarC, 0]);      // Center text Horizontally //     
VarWORKSHEET.WriteHorAlignment(VarCELL,haCenter);      // Center text
Vertically //      VarWORKSHEET.WriteVertAlignment(VarCELL,vaCenter); 
    // Draw a default border around each cell     
VarWORKSHEET.writeborders(VarCELL,[cbNorth,cbSouth,cbEast,cbWest]);  
  // text in header row in Bold //     
VarWORKSHEET.WriteFontStyle(VarCELL, [fssBold]);      // text header
font size //     
VarWORKSHEET.WriteFontSize(VarCELL,HEADER_FONT_SIZE);      // name of
the Font //      VarWORKSHEET.WriteFontName(VarCELL, 'Arial');      //
Color Background of header cell //     
VarWORKSHEET.WriteBackgroundColor(VarCELL,scYellow);     end;    //
Set the row height for the header row which has a smaller font than  
// the rest    VarWORKSHEET.WriteRowHeight(0,
HEADER_FONT_SIZE*NICE_ROWHEIGHT_FACTOR, suPoints);   
//-------------------------------------------------    // Write the
data cells    //-------------------------------------------------   
for VarR := 1 to StringGrid1.RowCount-1 do    begin      for VarC := 0
to StringGrid1.ColCount-1 do      begin        VarCELL:=
VarWORKSHEET.WriteText               
 (VarR,VarC,StringGrid1.Cells[VarC,VarR]);        // write border
around the cells //        VarWORKSHEET.WriteBorders               
 (VarCELL,[cbNorth,cbSouth,cbEast,cbWest]);       
VarWORKSHEET.WriteHorAlignment                 (VarCELL,haCenter);   
   // By default cells are bottom-aligned. It looks better if they are
centered.        VarWORKSHEET.WriteVertAlignment               
 (VarCELL,vaCENTER);      end;    end;    (*    // Just to show you
automatic row height adjustment (not needed since the    // row
heights have been handled already).    
//---------------------------------------------------------    //
Adjust the row heights   
//---------------------------------------------------------    for
VarR := 0 to VarWORKSHEET.GetLastRowIndex do    begin     
VarWORKSHEET.WriteRowHeight(VarR, NICE_ROWHEIGHT_FACTOR *
VarWORKSHEET.CalcAutoRowHeight(VarR), VarWORKBOOK.Units);    end;   
*)    //---------------------------------------------------------   
// Save the workbook as xlsx file   
//---------------------------------------------------------   
Showmessage('Will save to xlsx');   
VarWORKBOOK.WriteToFile('Test.xlsx', sfOOXML, true);    //
workbook.WriteToFile('Test.ods', sfOpenDocument, true);    // or save
it for LibreOffice    Showmessage('Saving done !');  finally    //
Destroy the workbook after usage.    VarWORKBOOK.Free;  end;end; end.

----------------------------------------------------------------------
seghele0 · 2023-08-17 10:05

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.