Recent

Author Topic: Different row height for odf,xlsx,xlx format?  (Read 736 times)

Soner

  • Sr. Member
  • ****
  • Posts: 285
Different row height for odf,xlsx,xlx format?
« on: April 13, 2022, 09:25:21 am »
Does anyone know why the row heights are different if one document is saved as odf, xlsx or xlx format?

Look at the pictures.

Zvoni

  • Hero Member
  • *****
  • Posts: 1511
Re: Different row height for odf,xlsx,xlx format?
« Reply #1 on: April 13, 2022, 09:50:00 am »
looking at the XML for a xlsx-sheet: "ht" describes the Rowheight for a Row-Object.
Funny enough: i entered "47" as the Height, and the xml has ht = "47.1", so probably an adjustment to "full" pixels, since the Unit of RowHeight is Points, and there are 72 points per Inch, and depending on your DPI you get different Values for Pixels (basic conversion).

So, at a guess, probably having to do with RoundingUp/Down in FPSpreadsheets internal calculations?

EDIT: Just calculated by hand:
your "0,45 cm" for the odf is equal to 12.75 Points, which actually is the default height of Rows (at least in Excel) https://bettersolutions.com/excel/rows-columns/row-heights.htm
"0,53 cm" ==> 15 Points which is the default in Excel, too?!?!?!?
"0,58 cm" ==> 16.4 Points
« Last Edit: April 13, 2022, 09:56:41 am by Zvoni »
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

Soner

  • Sr. Member
  • ****
  • Posts: 285
Re: Different row height for odf,xlsx,xlx format?
« Reply #2 on: April 13, 2022, 11:07:07 am »
It looks like fpspreadsheet has bug. The content.xml file from odf-file has: style:row-height="5.292mm" style:use-optimal-row-height="true"
and the  sheet1.xml from xlsx-file has:
ht="16.50" defaultRowHeight="15.00"

I think ht sould be 15,000945 (5.292mm=15,000945 pt) or ht should not be saved for every row like odf-file when the user don't change the  row height.

wp

  • Hero Member
  • *****
  • Posts: 10281
Re: Different row height for odf,xlsx,xlx format?
« Reply #3 on: April 13, 2022, 11:38:46 am »
I used the following code to create an xls, xlsx and ods file from the same worksheet, based on the elemental fpspreadsheet functions alone (without complications due to the DefaultRowHeight imposed by the worksheet grid):
Code: Pascal  [Select][+][-]
  1. uses
  2.   fpspreadsheet, fpstypes, fpsallformats;
  3. var
  4.   wb: TsWorkbook;
  5.   ws: TsWorksheet;
  6. begin
  7.   wb := TsWorkbook.Create;
  8.   try
  9.     ws := wb.AddWorksheet('Test');
  10.     //ws.WriteDefaultRowHeight(20, suPoints);
  11.     ws.WriteNumber(0, 0, 1.23);
  12.     ws.WriteNumber(1, 0, 2.34);
  13.     ws.WriteNumber(2, 1, 3.45);
  14.    
  15.     wb.WriteToFile('test.xls', sfExcel8, true);
  16.     wb.WriteToFile('test.xlsx', sfOOXML, true);
  17.     wb.WriteToFile('test.ods', sfOpenDocument, true);
  18.   finally
  19.     wb.Free;
  20.   end;
  21. end.
Opening the xls and xlsx files in Excel and querying the height of an arbitrary row yields the value 15, which is in points - and this is what fpspreadsheet is set up to start with (TsWorksheet.Create: FDefaultRowHeight := ptsToMM(15)). Opening the same files in LibreOffice Calc yields a row height of 0.53 cm, and this is the equivalent of 15 pts. Fine.

But opening the ods file in LibreOffice Calc yields a row height of 0.45 cm (12.75 pts). Even deliberately changing the worksheet's DefaultRowhigh (by activated to commented-out line in the code above) makes no difference. The row style node in the xml file looks like this:
Code: XML  [Select][+][-]
  1. <style:style style:name="ro1" style:family="table-row">
  2.   <style:table-row-properties style:row-height="5.292mm" style:use-optimal-row-height="true" fo:break-before="auto" />
  3. </style:style>
"use-optimal-row-height" probably means that the row height will be determined by the font height, no matter which value is specified in the "style:row-height" attribute. In fact, changing "use-optimal-row-height" to false and entering some different value for "style:row-height" yields in different row heights, but only for the rows containing cells with values, not for the empty rows. Probably the style of a non-empty row must be repeated for the following empty rows - this concept with "rows-repeated" in OpenDocument has a detrimental effect on the speed and memory performance of fpspreadsheet which will create more than 1 million row records in reading.

I will definitely not touch this since the "rows-repeated" and "columns-repeated" gave me lots of gray hair already.

Zvoni

  • Hero Member
  • *****
  • Posts: 1511
Re: Different row height for odf,xlsx,xlx format?
« Reply #4 on: April 13, 2022, 11:55:03 am »
WP, OK.
But it doesn't explain his 3rd value (0,58 cm / 16.5 points)
The only difference i see, he's using/querying different rows for ods, xls, xlsx
« Last Edit: April 13, 2022, 11:56:42 am by Zvoni »
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

wp

  • Hero Member
  • *****
  • Posts: 10281
Re: Different row height for odf,xlsx,xlx format?
« Reply #5 on: April 13, 2022, 02:40:04 pm »
The grid makes it more complicated because it has its own DefaultRowHeight. It propagates the 22 pixels of the standard LCL grid row height to the worksheet as new default rowheight which becomes 5.82 mm now, or 16.5 pts.

The problem is that the grid writes also row records to the worksheet with these new rowheights. But I think this is is wrong - it should not write row records at all since their row height is just equal to the default rowheight. I'll have to investigate why this happens.

The other problem is that the xls and xlsx writers seem to handle this case (presence of row records containing only the default rowheights) differently: the xls writer ignores the row records since they do not contain any new information, but the xlsx writer does add the row height to the row nodes. Another point to investigate...

Soner

  • Sr. Member
  • ****
  • Posts: 285
Re: Different row height for odf,xlsx,xlx format?
« Reply #6 on: April 19, 2022, 09:52:10 pm »
wp thanks for the hint TsWorksheet.Create: FDefaultRowHeight := ptsToMM(15). I changed  it to 12.75 and now the row heights are same when i create the file from your test application above. For all file formats 0,45 cm.
When I save it from TsWorksheetGrid then xlsx-file has still 0,58 cm.

 

TinyPortal © 2005-2018