Row height calculate by Excel

[straetch]

I am struggling for some time to pass the proper height for the rows in a worksheet.
The size of the cells are widely different, so I need a way to let the row height automatically follow the size of the cells in that row.
I read the chapter on column widths and row heights in the wiki.

1. Using TsWorksheet.WriteRowHeight (Cardinal, Single, TsSizeUnits, TsRowHeightType) with TsRowHeightType = rhtAuto seems the proper way, but what values should be given for the 2nd an 3th argument? These should be irrelevant as I interpret it. I get strange results (tiny rows).

2. It seems that TsWorksheet.WriteRowHeight always results in a calculated fixed row height.
The spreadsheet will later always be read by Excel or LO Calc. Why not let Excel do the row height calculation? I gather that in an Excel file there is a flag for each row that results in auto-calculation of the row height for each row by Excel.
Can fpspreadsheet set that flag? Something like:  TsWorksheet.WriteRowHeightFlag(Cardinal, boolean);

Maybe I am missing a point.

Using Lazarus 2.0.6 and fpspreadsheet 1.10.1 (no visuals).

[wp]

Please post small projects demonstrating the issues #1 and #2.

[straetch]

Here some experiments with row height:

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

1. unit main;
2.  
3. {$mode objfpc}{$H+}
4.  
5. interface
6.  
7. uses
8.   Classes, SysUtils, Forms, Controls, Graphics, Dialogs,
9.   fpstypes, fpsutils, fpspreadsheet, fpsallformats;
10.  
11. type
12.  
13.   { TForm1 }
14.  
15.   TForm1 = class(TForm)
16.     procedure FormShow(Sender: TObject);
17.   private
18.  
19.   public
20.  
21.   end;
22.  
23. var
24.   Form1: TForm1;
25.  
26. implementation
27.  
28. {$R *.lfm}
29.  
30. { TForm1 }
31.  
32. const
33.   t00 = '123';
34.   t01 = '1234';
35.   t10 = 'MMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMM'
36.     +'MMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMMM';
37.   t11 = 'abc';
38.   TargetDir = 'C:\temp99\';  // should be harmless
39. var
40.   myWB: TsWorkbook;
41.   myWS: TsWorksheet;
42.  
43. procedure TForm1.FormShow(Sender: TObject);
44. begin
45.   MyWB := TsWorkbook.Create;
46.   MyWS := MyWB.AddWorksheet('My_Table');
47.   CreateDir(TargetDir);
48.   MyWS.WriteText(0,0,t00); // fill with text
49.   MyWS.WriteText(0,1,t01);
50.   MyWS.WriteText(1,0,t10);
51.   MyWS.WriteText(1,1,t11);
52.   MyWs.WriteWordWrap(1,0,true);
53.   // case 1
54.   MyWs.WriteRowHeight (1, 0, suLines, rhtAuto);
55.   MyWB.WriteToFile(TargetDir + 'case1.xlsx',true);
56.   // case 2
57.   MyWs.WriteRowHeight (1, 5, suLines, rhtauto);
58.   MyWB.WriteToFile(TargetDir + 'case2.xlsx',true);
59.   // case 3
60.   MyWs.WriteRowHeight (1, 30, suLines, rhtauto);
61.   MyWB.WriteToFile(TargetDir + 'case3.xlsx',true);
62.   // case 4
63.   MyWs.WriteRowHeight (1, 5, suLines, rhtCustom);
64.   MyWB.WriteToFile(TargetDir + 'case4.xlsx',true);
65.   // case 5
66.   MyWs.WriteDefaultRowHeight(1,suLines);
67.   MyWB.WriteToFile(TargetDir + 'case5.xlsx',true);
68.  
69.   MyWB.Free;
70. end;
71. end.
72.  

This code generates 5 spreadsheet files.

Issue 1: When you open each one (I used LO Calc), the result is that  there is no automatic adaptation of the row height to the cell content, even with rhtAuto.
Also, the row size is fixed. Adding additional characters in Calc to the big cell does not auto-expand the row heigth.

Issue 2:
When any of the files is opened in Calc and the row with the large cell is right-clicked, clicking Optimal Row Height, the row height is expanded as expected. Saving the file and reopening it demonstrates that the auto-row height setting is persistent. So there must be a flag somewhere in the file for each row indicating that the row height is to be automatically adjusted.
I tested this for .xlsx, .xls and .ods files.
My question is: is there a way to set this flag with fpspreadsheet?
That would free fpspreadsheet from elaborate calculations. Indeed, it is  Excel or Calc that do the calculation.
Since there are no visuals active in my fpspreadsheet application, this seems optimal.

Of course, I could have misunderstood what fpspreadsheet does.
Kind regards,

[wp]

FPSpreadsheet does not perform row height calculations (did not check, but maybe it does when images are inserted because image positioning is relative to rows/columns in some formats).

Only when you want a specific rowheight, call Worksheet.WriteRowHeight() with RowHeightType rhtCustom and it writes a row record to the file. Rows without a row record are meant to use automatic row height. rhtAuto should have the same effect as "no row record". The reason why it is needed is that row records are also written when all cells in the row should have a given format; since the row height value is in the row record there must be a flag to ignore it: rhtAuto.

The reason why your files are not opened correctly by LOCalc is that you write xlsx files. When I open the files in Excel, the row height is expanded correctly. And when I write an ODS file (MyWB.WriteToFile(TargetDir + 'case1.ods', true)) the row heights are correct in LOCalc as well. So the issue seems to be an issue of the xlsx importer of Libre Office.

If you still have the issue: I remember there were some rowheight fixes some time ago, and since it took rather long to release v1.12 it could be that your version 1.10.1 does not contian these fixes. Please get yourself v1.12; it is in OPM now.

[straetch]

Thank you very much for the detailed insight. It is better to understand why things may go wrong than just apply a recipe for a correction.
I did the check with both .xlsx and .ods, with LO-Calc. Your conclusion is correct. I will flag this to LO.