Description: MSOffice is able to format cell to align right (or left) with indentation. LibreOffice Calc broke this spreadsheet more and more on every save (it not need modification save is enough) Steps to Reproduce: 1. Create XLSX document in MSoffice. Only one cell with formatting -> align to right, indent > 0 and save it. (Example document is in attachment, screenshot in attachment) 2. Open this document on LibreOffice calc, save it and close. 3. Repeat step 2 several times. Content of cell move to the left out of boundary. (screenshot in attachment) Actual Results: LibreOffice Calc broke this spreadsheet. Expected Results: LibreOffice Calc don't need support this feature. Not destroying spreadsheet is enough. Reproducible: Always User Profile Reset: No Additional Info:
Created attachment 157293 [details] Sample document before open in Calc
Created attachment 157294 [details] Step 1 screenshoot
Created attachment 157295 [details] Step 3 screenshoot
Thank you for reporting the bug. I can confirm the bug present in Version: 6.4.0.0.alpha1+ (x86) Build ID: ec7374ff84c71edfbb30d6e4dc5b486b6df7107f CPU threads: 2; OS: Windows 6.1 Service Pack 1 Build 7601; UI render: default; VCL: win; TinderBox: Win-x86@42, Branch:master, Time: 2019-11-10_21:37:30 Locale: en-US (en_US); UI-Language: en-US Calc: threaded But,not in LibreOffice 3.3.0 OOO330m19 (Build:6) tag libreoffice-3.3.0.4
Reproduced in Version: 4.3.0.0.alpha1+ Build ID: c15927f20d4727c3b8de68497b6949e72f9e6e9e but not in Version 4.1.0.0.alpha0+ (Build ID: efca6f15609322f62a35619619a6d5fe5c9bd5a)
I found this problem on version 6.1.4 originally. This behavior was my motivation for upgrade (to 6.3.4.2) and it doesn't help.
This isn't really a regression, as previously the indent wasn't applied properly, but the point of change could be bibisected using repo bibisect-42max. https://cgit.freedesktop.org/libreoffice/core/commit/?id=af169a601dee381b17d1e87c346f2d97bad9a69f author abdulmajeed ahmed <aalabdulrazzaq@kacst.edu.sa> 2013-05-31 10:05:55 +0200 committer abdulmajeed ahmed <aalabdulrazzaq@kacst.edu.sa> 2013-05-31 10:27:14 +0200 Fix fdo#51835 Indent changes in Calc forces alignment to left
It looks like duplicate to https://bugs.documentfoundation.org/show_bug.cgi?id=112220
*** Bug 112220 has been marked as a duplicate of this bug. ***
Set importance to HIGH MAJOR as this causes format loss.
format loss is different than data loss
I also have this issue in 6.4.7.2 on an xlsx sheet that is saved as an xlsx sheet. I don't remember whether the original was created in Excel, but it probably was.
I think I find the reason. In sc/source/filter/oox/stylesbuffer.cxx:1184-1186, the code is: > /* indentation: expressed as number of blocks of 3 space characters in > OOXML. */ > sal_Int32 nIndent = getUnitConverter().scaleToMm100( 3.0 * maModel.mnIndent, Unit::Space ); This seems to be wrong code. In case maModel.mnIndent is 0 (i.e., the cell is set to be no indent), it is fine because 3.0 * 0 is always 0. However, if maModel.mnIndent is 1, then after calculation it will be 3, which makes the indent shown to be 3 times wider than what it should be. Steps to observe the impact of this code: 1. Unzip the simple "test-indent-original.xlsx" file. Go to the stylesheet in xl/styles.xml. --> Observe that there are 3 nodes within "cellXfs", and the 3rd one has: > <alignment horizontal="left" vertical="center" textRotation="0" wrapText="false" indent="1" shrinkToFit="false"/> this is used by xl/worksheets/sheet1.xml 2. Open this test xlsx file with Calc. Observe that the indent for the cell has wide indent. If you hit 3 times the "decrease indent" toolbar icon to make it 0 indent, thus I can say that the indent is already set to 3 at the time of fileopen, although it should be 1. 3. Save as "test-indent-firstSave.xlsx". Then unzip the resaved xlsx file with Calc, and go to the style sheet. --> the indent becomes: > <alignment horizontal="left" vertical="center" textRotation="0" wrapText="false" indent="3" shrinkToFit="false"/> As discussed on 2 above, this indent was already 3 when the file is opened, thus it is not a filesave issue.
Created attachment 173214 [details] test-indent-original.xlsx
Created attachment 173215 [details] test-indent-firstSave.xlsx
Although I know the reason, it is out of my knowledge to fix this. I tried to change the code to: > sal_Int32 nIndent = getUnitConverter().scaleToMm100( maModel.mnIndent, Unit::Space ) and after this change there is no increase of indent after each save anymore, but the indent width shown after reopen seems to be a little different than the original with as shown on screen.
My bad... It was not FILEOPEN, it is FILESAVE. On XLSX import the indent is correctly detected (as integer value such as "1"), and is converted to a value which is 3 times the width of space in 100mm (such as 528), in https://opengrok.libreoffice.org/xref/core/sc/source/filter/oox/stylesbuffer.cxx?r=9964531f#1186 > sal_Int32 nIndent = getUnitConverter().scaleToMm100( 3.0 * maModel.mnIndent, Unit::Space ); However, when you now save the document, the width value in 100mm unit is then converted to the following: https://opengrok.libreoffice.org/xref/core/sc/source/filter/excel/xestyle.cxx?r=b8cfea65#1461 > nTmpIndent = (nTmpIndent + 100) / 200; The comment in this line says "1 Excel unit == 10 pt == 200 twips", so this 100mm unit value is treated as pt and converted to twips. The problem is that in xlsx file the indent value should be an integer number such, of which "1" means 3 space width, as correctly used in sc/source/filter/oox/stylesbuffer.cxx:1186. So the solution would be convert back the 100mm unit value to int value, which is a reverse operation of the import calculation. I see there is UnitConverter::scaleFromMm100 function in https://opengrok.libreoffice.org/s?refs=scaleFromMm100&project=core which can do this.
After some experiment I found that the correct calculation to restore the indent value should be: https://opengrok.libreoffice.org/xref/core/sc/source/filter/excel/xestyle.cxx?r=b8cfea65#1461: nTmpIndent = nTmpIndent / (3 * nSpaceWidth ) where the nSpaceWidth is the width of space char for the font in use. But I don't know how to get the width of space due to my poor understanding of the code. This is all I can do. Hope someone could fix this soon.
*** Bug 144116 has been marked as a duplicate of this bug. ***
I have submitted a patch on gerrit: https://gerrit.libreoffice.org/c/core/+/123111 Please review and provide feedback.
Reset assignee to default as I have abandoned the patch on gerrit, see discussion in: https://gerrit.libreoffice.org/c/core/+/123111
Created attachment 175572 [details] tdf130104_indent.xlsx
Kevin Suo committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/6b93ee72df1aa42d1a3482ffc396bd0c23134f8b tdf#130104 - FILESAVE XLSX: cell indent increased on each save It will be available in 7.3.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.
Big thanks to Mike Kaganski who has helped me fix this. With the patch merged above now the indent upon roundtrip is reserved. However, as stated on the commit message, the UI part need to be improved, so that after xlsx import, if the user hit the "Increase Indent" or "Decrease Indent" toolbar icon to change the indent, Calc should be able to detect that we are operating in an xlsx file, thus the "increment" should be 3 * width of space char, rather than the current SC_INDENT_STEP. Also, the if the user changes the default font of the xlsx document, then Calc should recalculate the indent for each cell to reflect the possible change in width of space char. I will open a new bug for this issue. In addition, on xlsx import of the file tdf130104_indent.xlsx, the default font (i.e. font for the "Normal" style) is "Times New Roman". However, when the UI locale is set to Simplified Chinese and "Asian" option is enabled in Tools -> Options -> Language Settigns -> Languages -> "Default Languages for Documents", upon resave as xlsx, the default font for the document is changed to "Noto Sans CJK SC" on my system, which causes the space-width detected on export to be different from the width detected on xlsx import. This seems to be bug 131349.
Kevin Suo committed a patch related to this issue. It has been pushed to "libreoffice-7-2": https://git.libreoffice.org/core/commit/7982723a2943d8dcfc533a507880ab5991a04908 tdf#130104 - FILESAVE XLSX: cell indent increased on each save It will be available in 7.2.3. 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 67774 has been marked as a duplicate of this bug. ***