Bug 125205 - Spreadsheet cell Horizontal alignment (Left) indent is not saved/preserved round trip to OOXML
Summary: Spreadsheet cell Horizontal alignment (Left) indent is not saved/preserved ro...
Status: RESOLVED DUPLICATE of bug 144991
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: filters and storage (show other bugs)
(earliest affected) release
Hardware: All All
: medium normal
Assignee: Not Assigned
Keywords: filter:xlsx
Depends on:
Blocks: XLSX
  Show dependency treegraph
Reported: 2019-05-10 12:03 UTC by stdedos
Modified: 2022-09-14 18:39 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:
Regression By:


Note You need to log in before you can comment on or make changes to this bug.
Description stdedos 2019-05-10 12:03:54 UTC
*Disclaimer: Replication details might be sketchy, since I've seen the bug so many times, and in so many "ways" to manifest*

I am creating a spreadsheet xlt template (cannot attach it, apologies), and I am setting padding of all cells to 1pt. Also, I am creating a custom style with the same 1pt. I save the file, include it in "My Templates", and close it.

Regardless if I open it for editing, or create a new file out of it (and save as xlsx): the padding setting is not preserved, file is "stuck" on 2pt

Earliest Affected Version might be way off, since I used to see it with the stock Ubuntu 16.04 one

Build ID: 1:6.2.3-0ubuntu0.16.04.1~lo1
CPU threads: 8; OS: Linux 4.15; UI render: default; VCL: gtk2; 
Locale: en-US (en_US.UTF-8); UI-Language: en-US
Calc: threaded

Steps to Reproduce:

(On description)

Actual Results:
(On description: format is not preserved)

Expected Results:
Obvious: Formatting is saved and shown

Reproducible: Always

User Profile Reset: No

Additional Info:
Comment 1 Usama 2019-05-25 05:30:41 UTC
Thank you for reporting. I confirm it on master version.

The issue is not limited to templates only but also effect other spreadsheet formats such as XLSX 

To reproduce:
1. select group of cells with text
2. format menu >> cells.. >> alignment
3. in horizontal set left to 1 pt
4. save file as XLSX, XLT or XLTX

Expected results:
opening the file should show the 1pt indenting

Actual results:
No indenting at all

Build ID: 40e2a0d7039eee9c5377996da3949680903e1016
CPU threads: 4; OS: Linux 4.9; UI render: default; VCL: gtk3; 
TinderBox: Linux-rpm_deb-x86_64@86-TDF, Branch:master, Time: 2019-05-22_13:55:35
Locale: hu-HU (en_GB.utf8); UI-Language: en-US
Calc: threaded
Comment 2 V Stuart Foote 2019-05-25 07:48:30 UTC
ODF - OOXML - ODF Round trip filter issue.

Horizontal padding handled via fo:margin-left maybe needs to be handled with different units on export to OOXML .xmls

1. Working in LibreOffice master/6.3.0alpha1+

A sample sheet with horizontal left spacing (indent) of 1 pt in ODF (as flat .fods) gets a fo:margin-left value of 0.99, it will save and reopen from ODF.
But the attribute seems to get mishandled in filter export to OOXML.

From LibreOffice master/6.3.0 a save-as export of file to OOXML (.xlsx) sets:
<alignment horizontal="general" vertical="bottom" textRotation="0" wrapText="false" indent="0" shrinkToFit="false"/>

And when that OOXML is opened in LibreOffice (or Excel) the horizontal alignment/indent shows "0". So issue with the export of the alignment horizontal indent.

2.) Creating similar sheet in Excel 2016, a left horizontal alignment of 1 set, showing noticeably wider indent that LO. 

Examine the file saved from Excel to OOXML, and also a copy saved to ODF.

These both open in LibreOffice master/6.3.0 showing a larger indent than the indent of 1 (pt) in LO had shown.

Looking at the Excel 2016 generated XML, IIUC the style.xml for
the OOXML shows: <alignment horizontal="left" indent="1"/>;
the ODF shows: <style:paragraph-properties fo:text-align="start" fo:margin-left="10.01pt"/>

Seems the GUI alignment units in Excel are not points but are chars! And the indent of 1ch becomes 10pt in ODF.

Bit of an interoperability issue on these two facets.

Note: binary XLS format may be affected also -- I did not check. But seems like this facet is on import is facet/dupe of bug 67774 (cmnt 10 there).
Comment 3 V Stuart Foote 2019-05-25 07:57:09 UTC
If from LibreOffice edit of an ODF spreadsheet. If I increase the Alignment Horizontal Left from 1pt to 15pt, when the ODF is opened into Excel 2016 the cell  alignment will read Horizontal -> Left(indent) will read "1"!

Points (ODF), Chars (OOXML) what Mike suggested in bug 67774

Oh, and this also affects Windows builds.


Build ID: 959e8ae7ea33ce94dd80ee8ea172b6db64593873
CPU threads: 4; OS: Windows 10.0; UI render: GL; VCL: win; 
Locale: en-US (en_US); UI-Language: en-US
Calc: threaded
Comment 4 alexandrepesca 2020-02-12 20:10:36 UTC
The bug persists on 6.4.0.
Comment 5 Camden Kevin 2021-04-28 15:24:08 UTC Comment hidden (spam)
Comment 6 Kevin Suo 2022-09-14 14:59:53 UTC
That is because in Excel (xls, xlsx, OOXML) the cell indent is measured using "1", "2", "3"... units, where the "1" represent  the width of 3 space chars (using the default style font) (the values must be integers not float numbers such as 1.5 or 1.01 etc),  while in ODF we use pt to measure the indent (indent can be 1pt, 1.01pt, 2.5pt, 15pt...)

As a result, if we set the cell indent to 1pt in LibreOffice, and assume the width of 3 space char is e.g. 10pt (i.e. "1" Excel indent unit), then upon filesave what value should it be? Round-down to zero? Round-up to 1 excel unit? Neither approach is correct.

I my opinion the only possible approach should be:

1. LibreOffice change its way to handle the cell indent to be consistent with Excel, for interoperability with Excel. I think no one will agree with this - why should we give up using pt as measurement and use the non-sense 1,2,3 units?

2. Or, LibreOffice detects to see if we are editing an Excel file, and if yes, when the user hit the "Increase Indent" or "Decrease Indent" toolbar icon, one-click will increase or decrease the indent by the width of 3 space character using the Normal (western) font. This is what I have proposed in bug 144991.
However, the headache under this approach is that, what if we create a new Calc file, set the indent, then save it as XLSX/XLS? Or, what if we open an ODS file which already contains some indent settings in some cells, then save as xlsx/xls? Seems like a WONTFIX under such conditions.

As a result I mark this as a duplicate of bug 144991, and add bug 130104 as SEE ALSO.

*** This bug has been marked as a duplicate of bug 144991 ***
Comment 7 stdedos 2022-09-14 18:39:31 UTC
I know you'll hate my response - but add a converting function maybe? 

Libre opens X file, converts it (if needed) to a "standard" format in-memory, and, if needed, writing it in Excel units on save?