Created attachment 148870 [details]
Calc ignores horizontal alignment of this XLSX
I don't have full confidence wheather this is a bug or not because I don't have Microsoft Excel.
Anyway I've created a very simple XLSX file using Apache POI Java library. But LibreOffice Calc ignores horizontal alignment of cells.
It seems that this file completely conforms XLSX format. I checked its styles.xml and sheet1.xml file in the attached XLSX file.
I can confirm that result is different than in excel2010, althought I cannot reproduce it with file created in excel. So it's related to Apache POI Java library.
Build ID: 1ac54de7869d5809a312acad0a37d48028ad9d3b
CPU threads: 4; OS: Linux 4.15; UI render: default; VCL: gtk3;
regression, in version Version 22.214.171.124.alpha0+ (Build ID: efca6f15609322f62a35619619a6d5fe5c9bd5a) is the result same as in excel.
Created attachment 148883 [details]
compare excel and LO 6.3
This seems to have begun at the below commit.
Adding Cc: to Noel Power ; Could you possibly take a look at this one?
7768846a5953a624076782f3cc0fbdcca5f5c160 is the first bad commit
Author: Matthew Francis <firstname.lastname@example.org>
Date: Sat Sep 5 17:55:40 2015 +0800
Author: Noel Power <email@example.com>
AuthorDate: Wed May 22 10:00:34 2013 +0100
Commit: Noel Power <firstname.lastname@example.org>
CommitDate: Wed May 22 10:03:26 2013 +0100
fix for bnc#819865 itemstate in parent style incorrectly reported as set
Problem occurs because attrs set with default values are reported as set when queried
Running into this issue with spreadsheets generated by SheetJS Pro.
In the <xf> records, Excel assumes that applyAlignment is true if an <alignment> child is present. You are expected to specify applyAlignment="0" if alignment does not apply.
LibreOffice (including the latest version 126.96.36.199) assumes that applyAlignment is false even if an <alignment> child is present. The default behavior is incorrect.
To verify this is the root cause, you can take the original sample file and add the applyAlignment="1" attribute to the <cellXfs> <xf> tag:
<xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0" applyAlignment="1">
/* Default value of the apply*** attributes is dependent on context:
true in cellStyleXfs element, false in cellXfs element... */
maModel.mbAlignUsed = rAttribs.getBool( XML_applyAlignment, !maModel.mbCellXf );
This came from the original import in 2008.
According to comment 4, this is not correct. The documentation I saw didn't mention anything about the default. Changing this to true fixes out.xlsx and passes a make check.
I texted out.xlsx with old version Excel 2003, and it matched excel in comment 2.
Created attachment 176571 [details]
out_NoApplyCellXF5.xlsx: even applies in Excel 2016 with applyAlignment="0"
Am I doing something wrong? I tried getting the apply to be false, but Excel seems to ignore it completely, so it always applies.
I wouldn't say that commit 3 is truly regressive - it seems to have just exposed some kind of flaw or poor handling in the import process.
I note that some handling has been done in 2021 Apache POI v5.0 that takes into account the adjustAlignment variable.
I'm not sure why commit 3 would be involved here.
I have narrowed it down to SheetDataBuffer::addColXfStyles, which tries to consolidate styles using addIfNotInMyMap(). In that comparison, it only checks if mbAlignUsed.
(stylesbuffer.cxx's bool operator==(rXf1, rXf2))
Created attachment 176617 [details]
out_exaggerated.xlsx: includes applyProtection elements
In the current implementation, the first style that is applied affects all cells. The alignment/protection settings are accepted regardless of whether it is considered "used" or not.
Proposed patch at http://gerrit.libreoffice.org/c/core/+/126177
Justin Luth committed a patch related to this issue.
It has been pushed to "master":
tdf#123139 sc oox: CellXf should default to applyAlignment
It will be available in 7.4.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:
Affected users are encouraged to test the fix and report feedback.
No intention to backport a trivial fix which goes against documentation just because that matches Excel in testing.