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. Version: 6.3.0.0.alpha0+ Build ID: 1ac54de7869d5809a312acad0a37d48028ad9d3b CPU threads: 4; OS: Linux 4.15; UI render: default; VCL: gtk3; regression, in version Version 4.1.0.0.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? Thanks 7768846a5953a624076782f3cc0fbdcca5f5c160 is the first bad commit commit 7768846a5953a624076782f3cc0fbdcca5f5c160 Author: Matthew Francis <mjay.francis@gmail.com> Date: Sat Sep 5 17:55:40 2015 +0800 source-hash-bf8e9b29aaebcbdd8f2f06b42ac97b8d9f8f4503 commit bf8e9b29aaebcbdd8f2f06b42ac97b8d9f8f4503 Author: Noel Power <noel.power@suse.com> AuthorDate: Wed May 22 10:00:34 2013 +0100 Commit: Noel Power <noel.power@suse.com> 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 Change-Id: I89d6c3b09312fb78052d87ff20aa12c6fbe7bc98
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 6.4.1.2) 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: ```xml <xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0" applyAlignment="1"> <alignment horizontal="left"/> </xf> ```
/* 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": https://git.libreoffice.org/core/commit/ab99d16e21e56596803b7d9a28af0895b435ef3e 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: https://wiki.documentfoundation.org/Testing_Daily_Builds 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.
Fwiw, the actual documentation now is https://learn.microsoft.com/en-us/openspecs/office_standards/ms-oe376/59922f8b-0edc-4e93-a822-9f22254aec46 and the initial 2008 code based on the standard plus observed MS implementation quirks was in the right direction but slightly off-track.. Note that doc for each attribute says "The standard does not define different behaviors for @apply... depending on whether the xf is in the CellXfs element or the CellStyleXfs element." and then continues explaining the actual difference between the two for each. Great. :-/
Eike Rathke committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/909a25d30b09ebd3a023105a9c3cc4d20add094a Resolves: tdf#139934 always apply cellXfs xf explicits (tdf#123139 related) It will be available in 7.6.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.
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-7-4": https://git.libreoffice.org/core/commit/e53c02eac4b3d231fef7a6156321edfdea1fbb27 Resolves: tdf#139934 always apply cellXfs xf explicits (tdf#123139 related) It will be available in 7.4.6. 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.
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-7-5": https://git.libreoffice.org/core/commit/321c1a6db442a6d2afee25d1d1256ff36bdb004e Resolves: tdf#139934 always apply cellXfs xf explicits (tdf#123139 related) It will be available in 7.5.2. 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.
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-7-5-1": https://git.libreoffice.org/core/commit/58c84f408c4a2d5f1067b4ca5e7c204d6a9c9372 Resolves: tdf#139934 always apply cellXfs xf explicits (tdf#123139 related) It will be available in 7.5.1. 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.
*** Bug 144097 has been marked as a duplicate of this bug. ***