Merged cell height calculation [ANSWERED]
lazarus / 49-FPSpreadsheet

----------------------------------------------------------------------
totya · 2020-07-12 14:07

Hi wp master!

I have an excel table with many merged cells (with line break). Can I
determine and set the height of this row to fit the text?

See sample picture.

And what is the present time recommended auto row height force
recalculation method for non-merged cells?

Thank you!

----------------------------------------------------------------------
wp · 2020-07-12 14:36

I guess you mean "lines with line break" rather than "merged cells".
The former ones are simple cells which contain line ending characters
in their text; the latter ones is a single cell which extends over the
adjacent horizontal and/or vertical cells. The content of the merged
block is given by the top/left cell; therefore, it is not possible to
merge cells with content in A1, A2 and A3 into a common block
extending from A1 to A3 - the merged block will only display the
content of cell A1.

In order to define a cell with line break just call WriteWordWrap for
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
worksheet.WriteWordWrap(row, col, 'line1' + lineending + 'line2');
In order to determine the wrapped row height you can call
CalcAutoRowHeight(row). Note, however, that fpspreadsheet does not
know the font metrics here, and the calculated value will only be
approximate.

In order to make Excel use the wrapped row height you must call
WriteRowHeight with the result of CalcAutoRowHeight as a parameter.
This is exactly what Excel seems to be doing itself.

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 procedure
TForm1.Button1Click(Sender: TObject);var  wb: TsWorkbook;  sh:
TsWorksheet;  cell: PCell;begin  wb := TsWorkbook.Create;  try    sh
:= wb.AddWorksheet('Test');    cell := sh.WriteText(0, 0, 'Line 1' +
Lineending + 'Line 2' + LineEnding + 'Line 3');   
sh.WriteWordwrap(cell, true);    sh.WriteRowHeight(0,
sh.CalcAutoRowHeight(0));    wb.WritetoFile('d:\test.xlsx', true); 
finally    wb. Free;  end;end;

----------------------------------------------------------------------
totya · 2020-07-12 15:11

Hi master!

Thanks for the detailed answer!

Sorry my English, with linebreak I mean the excel function name is
(translated): line breaks into multiple lines. I need this, the cell
text do not contain any linebreak. Modified code:

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 procedure
TForm1.Button1Click(Sender: TObject);var  wb: TsWorkbook;  sh:
TsWorksheet;  cell: PCell;begin  wb := TsWorkbook.Create;  try    sh
:= wb.AddWorksheet('Test');    cell := sh.WriteText(0, 0,      'Long
line without linebreak in the cell xyz xyz xyz' +      'ŐÚŰ xyz xyz
xyz xyz xyz xyz ŐÚŰ xyz xyz' +      'xyz xyz xyz xyz xyz ŐÚŰ xyz xyz
xyz xyz xyz ŐÚŰ xyz');    sh.WriteWordwrap(cell, True);   
sh.MergeCells(0, 0, 0, 2);    sh.WriteRowHeight(0,
sh.CalcAutoRowHeight(0));    wb.WritetoFile('test.xlsx', True); 
finally    wb.Free;  end;end;
The result is perfect, see attached picture. Thank you!

Otherwise QuoteThis is exactly what Excel seems to be doing itself.The
Excel (2013) can't handle these merged cell, the MS Excel auto row
height function doesn't work with these cells... so, the fps is better
than MS Excel...

ps.: I got Quoteunit1.pas(48,50) Warning: Symbol "WriteRowHeight" is
deprecated: "Use version with parameter AUnits"message for the
WriteRowHeight... but works...

----------------------------------------------------------------------
wp · 2020-07-12 17:44

Sorry about the "deprecated" - there is still a lot with the old
Microsoft length units (characters and lines) in the library... You
can avoid the "deprecated" by using one of the following lines:

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
sh.WriteRowHeight(0, sh.CalcAutoRowHeight(0), wb.Units, rhtAuto); //
orsh.WriteRowHeight(0, sh.CalcRowHeight(0), wb.Units, rhtAuto);
The second line is a bit more general than the first one because it
also takes care of lines having a fixed height.

----------------------------------------------------------------------
totya · 2020-07-12 18:14

Thank for this code, but doesn't work none of them. Only this produce
good result: 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 sh.WriteRowHeight(0, sh.CalcAutoRowHeight(0));
Edit.: ... and I say again: in Office 2013 it doesn't work too...
Perhaps the new MS method wrong... (click to format/autorowheight).

----------------------------------------------------------------------
wp · 2020-07-12 21:44

I checked again. My code is correct, at least with Office 2016 and
with OpenOffice Calc. I had developed this code with Excel2007 (which
I do not have any more), but it definitely was correctly working here,
too.

In contrast to what I posted previously, "sh.WriteRowHeight(0,
CalcAutoRowHeight(0))" is not correct because WriteRowHeight expects
the row height in lines, but CalcAutoRowHeight returns it in
Workbook.Units, i.e. millimeters. That it was working in my first test
must have been a matter of coincidence.

Here is an extended version with easily changeable line count in the
cell and with modifiable font size:
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 procedure
TForm1.Button1Click(Sender: TObject);var  wb: TsWorkbook;  sh:
TsWorksheet;  cell: PCell;  s: String;  i: Integer;begin  wb :=
TsWorkbook.Create;  try    sh := wb.AddWorksheet('Test');    s :=
'Line 1';    for i := 2 to 10 do      s := s + LineEnding + ' Line' +
IntToStr(i);    cell := sh.WriteText(0, 0, s);   
sh.WriteWordwrap(cell, true);     // Change font height   
sh.WriteFont(cell, 'Calibri', 16, [], scBlack);     // Select one of
these cases:     // This is wrong...//    sh.WriteRowHeight(0,
sh.CalcAutoRowHeight(0));     // This is correct (each one)://  
 sh.WriteRowHeight(0, sh.CalcAutoRowHeight(0), wb.Units, rhtAuto);   
sh.WriteRowHeight(0, sh.CalcRowHeight(0), wb.Units, rhtAuto);    
wb.WritetoFile('d:\test.xlsx', true);    wb.WriteToFile('d:\test.ods',
true);  finally    wb. Free;  end;end;

----------------------------------------------------------------------
totya · 2020-07-13 04:30

Hi wp master

As I said, the new two code lines doesn't work, that's simple. I open
(check) created excel file in the Office 2013.

Now I see in the Libre Office v6.2.4.2. Well, the new methods are both
wrong too. The old methods slightly better, but not perfect here, see
attachment.

Again, I tested this situation with this code with latest svn fps:

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 procedure
TForm1.Button1Click(Sender: TObject);var  wb: TsWorkbook;  sh:
TsWorksheet;  cell: PCell;begin  wb := TsWorkbook.Create;  try    sh
:= wb.AddWorksheet('Test');    cell := sh.WriteText(0, 0,      'Long
line without linebreak in the cell xyz xyz xyz' +      'ŐÚŰ xyz xyz
xyz xyz xyz xyz ŐÚŰ xyz xyz' +      'xyz xyz xyz xyz xyz ŐÚŰ xyz xyz
xyz xyz xyz ŐÚŰ xyz');    sh.WriteWordwrap(cell, True);   
sh.MergeCells(0, 0, 0, 2);     // Old worikng method...   
sh.WriteRowHeight(0, sh.CalcAutoRowHeight(0));     // New unworkable
methods...    //sh.WriteRowHeight(0, sh.CalcAutoRowHeight(0),
wb.Units, rhtAuto);    //sh.WriteRowHeight(0, sh.CalcRowHeight(0),
wb.Units, rhtAuto);     wb.WritetoFile('test.xlsx', True);  finally  
wb.Free;  end;end;

----------------------------------------------------------------------
totya · 2020-07-13 09:17

I tested it with MS Office 2010 too... but the result same as with the
MS Office 2013. "Old" method produce good output, but the two new
methods doesn't work. Thank you!

----------------------------------------------------------------------
wp · 2020-07-13 09:48

Sorry I missed that your cell text does not contain linebreaks (yes,
you said that...) and thus fpspreadsheet must find the break
positions. But I fear this is not possible because it does not know
the font metrics.

Your favoured "old working method" (sh.WriteRowHeight(0,
sh.CalcAutoRowHeight(0)) is not working, too - it just looks like...
Add a '123' to the cell string, and run the modified program. When
open the saved xlsx file in Exel you will see a wrapped block, but you
will not see the '123' at the end, i.e. the height of the row is too
small. A similar effect with ods and Calc.

Let me investigate how Excel/Calc are doing this.

----------------------------------------------------------------------
wp · 2020-07-13 21:42

Here is a usable solution, not very elegant, though.

The problem is that you merge cells, want to wrap the long text inside
the merged cells and adjust the row height such that the text fits
into the merged block vertically. I was astonished that Excel 2016 is
not able to solve this task. Calc is able to, and fpspreadsheet, of
course, as well.

It is not simple, though, because the basic spreadsheet library does
not know the character size of the fonts and thus cannot wrap the
lines at the word boundaries. The TsWorksheetGrid, however, is an LCL-
based control and does know the font metrics. Therefore you can use an
auxiliary, hidden worksheetgrid and let it do the work:

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 procedure
TForm1.Button1Click(Sender: TObject);const  s = 'Long line without
linebreak in the cell xyz xyz xyz' +      'ŐÚŰ xyz xyz xyz xyz xyz xyz
ŐÚŰ xyz xyz' +      'xyz xyz xyz xyz xyz ŐÚŰ xyz xyz xyz xyz xyz ŐÚŰ
xyz 123';var  wb: TsWorkbook;  sh: TsWorksheet;  cell: PCell;  h:
Single;  grid: TsWorksheetGrid;  r: Integer;begin  wb :=
TsWorkbook.Create;   sh := wb.AddWorksheet('Test');  cell :=
sh.WriteText(0, 0, s);  sh.WriteWordwrap(cell, True); 
sh.MergeCells(0, 0, 0, 2);   cell := sh.WriteText(5, 0, s); 
sh.WriteWordWrap(cell, true);   grid := TsWorksheetGrid.Create(self); 
try    grid.Visible := false;    grid.Parent := self;   
grid.LoadfromWorkbook(wb);     // calc auto-row height of row 0    r
:= grid.GetGridRow(0);    grid.AutoRowHeight(r);    h :=
pxToPts(grid.RowHeights[r], Screen.PixelsPerInch);   
sh.WriteRowHeight(0, h, suPoints, rhtCustom);     // calc auto-row
height of row 4    r := grid.GetGridRow(5);    grid.AutoRowHeight(r); 
  h := pxToPts(grid.RowHeights[r], Screen.PixelsPerInch);   
sh.WriteRowHeight(5, h, suPoints, rhtCustom);    
wb.WritetoFile('test.xlsx', True);    wb.WritetoFile('test.ods',
True);  finally    grid.Free;  end;   // DO NOT FREE THE WORKBOOK (wb)
- it was destroyed by the gridend; 
There are still problems left, however: The way Excel calculates the
row height is not documented, there are some mysterious margins which
I do not understand quantitatively. Therefore it might occur that the
cell is too tight for the wrapped text. I'd recommend to increase the
AutoRowHeight value (h, in the example) by some value.

----------------------------------------------------------------------
totya · 2020-07-14 13:29

Thank you master, but it slightly overcomplicated, working partially,
and hard to use from own class (for me).

In old times I calculate textwidth/height from text and font, but I
can't remember how can I do it.

Can I get the merged cells width easily (without I add merged cells
column width one by one)?

Thank you fro this huge component! This problem doesn't matter really,
and the Excel myself too can't do it...

----------------------------------------------------------------------
totya · 2020-07-14 18:45

Hi Master!

Quick, dirty, but works with all merged cells, and no except!

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
TsWorksheetHelper.myGetColsWidthPixel(  const AFirstCol, ALastCol:
cardinal): integer;var  i: integer;begin  Result := 0;   for i :=
AFirstCol to ALastCol do    Result := Result + Round(GetColWidth(i,
suPoints));end; function TsWorksheetHelper.myCalcTextWidthPixel(const
AText: string;  const AFontName: string; const AFontSize: integer; 
const AFontStyle: TsFontStyles): integer;var  bmp: TBitmap;  FS:
TsFontStyle;begin  Result := 0;   bmp := TBitmap.Create;  try   
bmp.Canvas.Font.Name := AFontName;    bmp.Canvas.Font.Size :=
AFontSize;    bmp.Canvas.Font.Style := [];     with bmp.Canvas.Font
do      for FS := Low(AFontStyle) to High(AFontStyle) do        case
FS of          fssBold: Style := Style + [fsBold];          fssItalic:
Style := Style + [fsItalic];          fssUnderline: Style := Style +
[fsUnderline];        end;     Result := bmp.Canvas.TextWidth(AText); 
finally    bmp.Free;  end;end; function
TsWorksheetHelper.myCalcTextHeightPixel(const AText: string;  const
AFontName: string; const AFontSize: integer;  const AFontStyle:
TsFontStyles): integer;var  bmp: TBitmap;  FS: TsFontStyle;begin 
Result := 0;   bmp := TBitmap.Create;  try    bmp.Canvas.Font.Name :=
AFontName;    bmp.Canvas.Font.Size := AFontSize;   
bmp.Canvas.Font.Style := [];     with bmp.Canvas.Font do      for FS
:= Low(AFontStyle) to High(AFontStyle) do        case FS of         
fssBold: Style := Style + [fsBold];          fssItalic: Style := Style
+ [fsItalic];          fssUnderline: Style := Style + [fsUnderline]; 
     end;     Result := bmp.Canvas.TextHeight(AText);  finally   
bmp.Free;  end;end; procedure
TsWorksheetHelper.myWriteAutoRowHeight(const ARow: cardinal;  const
AFirstCol, ALastCol: cardinal; const AFontName: string;  const
AFontSize: integer; const AFontStyle: TsFontStyles);var 
MergedColsWidthPixel: integer;  TextWidthPixel, TextHeightPixel:
integer;begin  MergedColsWidthPixel := myGetColsWidthPixel(AFirstCol,
ALastCol);   TextWidthPixel := myCalcTextWidthPixel(ReadAsText(ARow,
AFirstCol),    AFontName, AFontSize, AFontStyle);   TextHeightPixel :=
myCalcTextHeightPixel(ReadAsText(ARow, AFirstCol),    AFontName,
AFontSize, AFontStyle);   if TextWidthPixel < MergedColsWidthPixel
then    WriteRowHeight(ARow, TextHeightPixel + (TextHeightPixel div
2), suPoints)  else    WriteRowHeight(ARow, Trunc(((TextWidthPixel /
MergedColsWidthPixel) + 1) *      TextHeightPixel), suPoints);end;
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 +
(TextHeightPixel div 2) only looks good like... but not needed.

----------------------------------------------------------------------
wp · 2020-07-14 20:14

Good that you found a solution for yourself. Just let me give you a
hint on already existing code in unit fpsVisualUtils (in the
laz_fpspreadsheet_visual package) doing the same but in a more general
way:

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
RichTextWidth(ACanvas: TCanvas; AWorkbook: TsWorkbook; ARect: TRect; 
const AText: String; ARichTextParams: TsRichTextParams; AFontIndex:
Integer;  ATextRotation: TsTextRotation; AWordWrap, ARightToLeft:
Boolean;  AZoomFactor: Double): Integer; function
RichTextHeight(ACanvas: TCanvas; AWorkbook: TsWorkbook; ARect: TRect; 
const AText: String; ARichTextParams: TsRichTextParams; AFontIndex:
Integer;  ATextRotation: TsTextRotation; AWordWrap, ARightToLeft:
Boolean;  AZoomFactor: Double): Integer;
These routines (used by the grid's AutoRowHeight) also take care of
- individually formatted characters within each cell ("rich text")
- rotated text (vertical painting direction)
- zoomed text (worksheet's ZoomFactor <> 1)

"AText" is the cell text to be measured, ARichTextParams an array of
parameters for individual character fonts (set to nil if not needed).
"ARect" is the rectangle within the text should fit (assumung
WordWrap). The other arguments are self-explanatory. The function
result is in pixels.

----------------------------------------------------------------------
totya · 2020-07-15 11:08

Thanks for the answer wp master, I will look your code later!