Download it now!
Bug 120168 - FILEOPEN XLSX: Text Orientation and alignment in cells
Summary: FILEOPEN XLSX: Text Orientation and alignment in cells
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard: target:6.2.0
Keywords: filter:xlsx
Depends on:
Blocks: XLSX
  Show dependency treegraph
 
Reported: 2018-09-27 19:55 UTC by Gabor Kelemen
Modified: 2019-06-10 15:01 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
Example file from Excel (14.84 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2018-09-27 19:55 UTC, Gabor Kelemen
Details
Screenshot of the document in Excel and Calc side by side (93.79 KB, image/png)
2018-09-27 19:55 UTC, Gabor Kelemen
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Gabor Kelemen 2018-09-27 19:55:18 UTC
Created attachment 145229 [details]
Example file from Excel

If we set the text orientation in a Microsoft Excel cell to 45° or -45° the text alignment will be left and right.

Steps to reproduce:
1. Create a new spreadsheet with Microsoft Excel 2010/2013/2016
2. Fill the A1 and A2 cell with some text
3. Set the text orientation of A1 cell to 45° and A2 to -45° (the text will be automatically left and right align)
4. Save the file as *.xlsx
5. Open the file with LibreOffice Calc

Actual results:
The text alignment will be center at the A1 and A2 cell

Expected results:
The text alignment should be left at the A1 cell and right at the A2 cell.

Version: 6.2.0.0.alpha0+
Build ID: a27ae800fed5a974c9b255f7ce2b38ec2dbaa426
CPU threads: 4; OS: Windows 6.3; UI render: default; 
Locale: hu-HU (hu_HU); Calc: CL
Comment 1 Gabor Kelemen 2018-09-27 19:55:50 UTC
Created attachment 145230 [details]
Screenshot of the document in Excel and Calc side by side
Comment 2 MM 2018-10-02 22:12:08 UTC
If you set format cells > alignment > reference edge to '[...] From Upper Cell Border' then it works.
Does excel set (and save) such an option or does it just automatically set this when setting the text orientation ???
If excel doesn't have/save the option, calc can't load it...
Comment 3 Gabor Kelemen 2018-10-04 12:29:32 UTC
(In reply to MM from comment #2)
> If you set format cells > alignment > reference edge to '[...] From Upper
> Cell Border' then it works.
> Does excel set (and save) such an option or does it just automatically set
> this when setting the text orientation ???
> If excel doesn't have/save the option, calc can't load it...

There is no similar explicit setting on the UI, see here:
https://www.homeandlearn.co.uk/excel2007/excel2007s1p5.html
https://www.homeandlearn.co.uk/excel2007/images2016/opening/format_cells_dialogue_box.gif

The sheet1.xml contains this:

<sheetData><row r="1" spans="1:2" ht="39" customHeight="1" x14ac:dyDescent="0.25"><c r="A1" s="1" t="s"><v>0</v></c><c r="B1" s="2" t="s"><v>0</v></c></row><row r="2" spans="1:2" ht="39" customHeight="1" x14ac:dyDescent="0.25"><c r="A2" s="1" t="s"><v>1</v></c><c r="B2" s="2" t="s"><v>1</v></c></row></sheetData>

I don't see here or elsewhere in the file a similar setting stored. I'd guess it's just heuristics.
Comment 4 Gabor Kelemen 2018-10-04 14:23:06 UTC
18.8.1 alignment (Alignment) section in the ISO/IEC 29500-1:2016(E) standard says:
---
textRotation (Text Rotation)
Text rotation in cells. Expressed in degrees. Values are in the range 0 to 180. The first letter of the text is considered the center-point of the arc.
For 0 - 90, the value represents degrees above horizon. For 91-180 the degrees below the horizon is calculated as:

[degrees below horizon] = 90 - textRotation.
---

And it provides a few screenshots about how different values look:
45 - text starts in the bottom left corner, vertically aligned left
90 - text starts in the bottom right corner, vertically aligned right
135 - text ends in the bottom right corner, vertically aligned right
180 - text ends in the bottom left corner, vertically aligned left

Also there are more attributes defined:
---
horizontal
(Horizontal Alignment)
Specifies the type of horizontal alignment in cells.

vertical (Vertical Alignment)
Vertical alignment in cells.
---

But these two are not present in my example files styles.xml, only textRotation is, with 45 and 135 values:
<xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0" applyAlignment="1"><alignment textRotation="45"/></xf>
and
<xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0" applyAlignment="1"><alignment textRotation="135"/></xf>
Comment 5 Xisco Faulí 2018-10-04 14:58:14 UTC
Reproduced in

Version: 6.2.0.0.alpha0+
Build ID: 8ea0b0f4dc5eba2d0bafc2e42415ef1824ff604e
CPU threads: 4; OS: Linux 4.15; UI render: default; VCL: gtk3; 
Locale: ca-ES (ca_ES.UTF-8); Calc: threaded

Version: 5.2.0.0.alpha0+
Build ID: 3ca42d8d51174010d5e8a32b96e9b4c0b3730a53
Threads 4; Ver: 4.10; Render: default; 


Version 4.1.0.0.alpha0+ (Build ID: efca6f15609322f62a35619619a6d5fe5c9bd5a)

LibreOffice 3.3.0 
OOO330m19 (Build:6)
tag libreoffice-3.3.0.4
Comment 6 MM 2018-10-04 22:34:28 UTC
(In reply to Gabor Kelemen from comment #4)
 
> But these two are not present in my example files styles.xml, only
> textRotation is, with 45 and 135 values:
> <xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"
> applyAlignment="1"><alignment textRotation="45"/></xf>
> and
> <xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"
> applyAlignment="1"><alignment textRotation="135"/></xf>

Does that mean Excel is not respecting the standard ?
Comment 7 Gabor Kelemen 2018-10-05 06:44:19 UTC
(In reply to MM from comment #6)
> (In reply to Gabor Kelemen from comment #4)
>  
> > But these two are not present in my example files styles.xml, only
> > textRotation is, with 45 and 135 values:
> > <xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"
> > applyAlignment="1"><alignment textRotation="45"/></xf>
> > and
> > <xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"
> > applyAlignment="1"><alignment textRotation="135"/></xf>
> 
> Does that mean Excel is not respecting the standard ?

No, I don't mean that. That's just a set of possible parameters that looked relevant in the documentation, but not even those are written to the file. That's fine.
Comment 8 Markus Mohrhard 2018-11-06 21:35:32 UTC
I think I have a simple fix by adapting the default horizontal alignment based on the rotation value. I need to double check with a MSO version whether this covers all cases.
Comment 9 Commit Notification 2018-11-07 22:31:17 UTC
Markus Mohrhard committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/+/d278f59c6112d39ae0f0cf23ec04697d87bbf870%5E%21

tdf#120168, adapt horizontal alignment based on text rotation

It will be available in 6.2.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 10 Luke 2018-11-13 00:32:05 UTC
Verified fixed in Version: 6.2.0.0.alpha1+ (x64)
Build ID: 50519e036a2707aca59cc8a7b58c4fdd7aeff580

Markus,
Any reason you want this left open?
Comment 11 Cathy Crumbley 2018-11-29 16:04:04 UTC
I see similar behavior in 6.2.0.0.beta1. However, it is unrelated to opening an Excel file. It happens with spreadsheets created as .ods files. 

By the way, here are other reports of similar issues with horizontal alignment. I am not sure how they should be included here.

Bug 53654 - FORMATTING: Format Cells dialog; Alignment tab; Text orientation 
Bug 38446 - Text orientation<> 0 + particlar 'Reference Edge' destroys horizontal text alignment 
Bug 120168 - FILEOPEN XLSX: Text Orientation and alignment in cells
Comment 12 Cathy Crumbley 2018-11-29 16:04:38 UTC Comment hidden (obsolete)
Comment 13 Xisco Faulí 2019-06-10 15:01:02 UTC
Dear Markus Mohrhard,
This bug has been in ASSIGNED status for more than 3 months without any
activity. Resetting it to NEW.
Please assigned it back to yourself if you're still working on this.