Bug 68117 - FILESAVE: xlsx not saving boolean columns
Summary: FILESAVE: xlsx not saving boolean columns
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.1.0.4 release
Hardware: Other All
: medium major
Assignee: ursache
URL:
Whiteboard: BSA target:4.5.0 target:4.3.7 target:...
Keywords: bibisected, bisected, regression
Depends on:
Blocks:
 
Reported: 2013-08-14 17:15 UTC by Mark C
Modified: 2015-12-17 07:22 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
Centered cells are retained after close. (7.45 KB, application/vnd.oasis.opendocument.spreadsheet)
2013-08-15 15:35 UTC, Mark C
Details
Same file as above, saved as xlsx. (4.51 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2013-08-15 15:39 UTC, Mark C
Details
ods file with whole columns set to different formats (8.12 KB, application/vnd.oasis.opendocument.spreadsheet)
2013-09-02 16:39 UTC, Mark C
Details
Screenshot of Excel 2010 cell type dialog (16.26 KB, image/png)
2014-05-21 12:56 UTC, Mark C
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Mark C 2013-08-14 17:15:52 UTC
Problem description: I centred a column of cell in a spreadsheet and save it as xlsx. When I open it, it has forgotten the formatting of all the empty cells. Saving as ods preserves the formatting.

Steps to reproduce:
1. Centre a column of cells
2. Save the file as xlsx and close it.
3. Open it again and type something in one the empty cells in the column

Current behavior: The cells are default left-aligned

Expected behavior: The cells should be centred like I set them.

              
Operating System: Windows 8
Version: 4.1.0.4 release
Comment 1 retired 2013-08-15 09:41:14 UTC
Could you please attach the example ods so others can reproduce and then confirm this bug? Thanks :)
Comment 2 Mark C 2013-08-15 15:35:22 UTC
Created attachment 84105 [details]
Centered cells are retained after close.

I created a new spreadsheet,centred column A, saved as ods, closed and reopened. Everything's fine.
Comment 3 Mark C 2013-08-15 15:39:39 UTC
Created attachment 84106 [details]
Same file as above, saved as xlsx.

I saved the same file as xlsx, closed it, reopened and it forgot all the empty cells' formatting. I haven't thoroughly tested, but I think other formatting is forgotten as well.
I confirmed that MS Excel 2010 acts like Calc with its own files. Files saved from Excel and opened in Calc exhibit the same behaviour as above.
Comment 4 ign_christian 2013-08-27 05:34:08 UTC
Not reproducible on LO 4.0.5.2 (Win7 32bit)

Please try resetting user profile:
https://wiki.documentfoundation.org/UserProfile
Comment 5 Mark C 2013-08-27 11:09:50 UTC
I tried it on LO 4.0.5.2 on Arch Linux and can't reproduce the problem. I'm going to try a fresh install on LO 4.1 on Windows today. I'll get back to you.
Comment 6 Mark C 2013-09-02 16:37:31 UTC
I just verified the bug on LO 4.1.1.2 on Arch Linux. I've added another attachment. Save it as an .xlsx and then reopen it. The formatting is gone except for populated cells.
Comment 7 Mark C 2013-09-02 16:39:27 UTC
Created attachment 85081 [details]
ods file with whole columns set to different formats

saveas this file as a .xlsx and then reopen it. See the column formatting disappear, except for populated cells.
Comment 8 ign_christian 2013-09-03 09:15:03 UTC
So we can set All platform bug & marks (perhaps) regression against 4.0.5.2. 
And let someone with 4.1 confirm this.
Comment 9 Markus Mohrhard 2013-09-07 07:09:14 UTC
Can't reproduce this behavior in master.
Comment 10 Robinson Tryon (qubit) 2013-10-16 15:22:06 UTC
Changing 'regression' -> 'PossibleRegression' until we have independent confirmation of a regression.
Comment 11 QA Administrators 2014-05-17 00:34:02 UTC
Dear Bug Submitter,

Please read the entire message before proceeding.

This bug has been in NEEDINFO status with no change for at least 6 months. Please provide the requested information as soon as possible and mark the bug as UNCONFIRMED. Due to regular bug tracker maintenance, if the bug is still in NEEDINFO status with no change in 30 days the QA team will close the bug as INVALID due to lack of needed information.

For more information about our NEEDINFO policy please read the wiki located here: 
https://wiki.documentfoundation.org/QA/FDO/NEEDINFO

If you have already provided the requested information, please mark the bug as UNCONFIRMED so that the QA team knows that the bug is ready to be confirmed.


Thank you for helping us make LibreOffice even better for everyone!


Warm Regards,
QA Team
Comment 12 Mark C 2014-05-17 11:40:23 UTC
Sorry. I didn't know that I needed to do this (changing the status, I mean).
It's now unconfirmed. I'll test it in the current 4.2.4 (archlinux) on a fresh user profile and submit my results.
Comment 13 Joel Madero 2014-05-21 04:42:29 UTC
I can confirm that the boolean format is lost in column c of the document after saving as docx. The other formats are not lost.


Ubuntu 14.04 x64
LibreOffice 4.2.4.2 release
LibreOffive 4.3 built Wed May 14 14:34:42 2014 +0200

Marking as NEW and updating title

@Marc C - if you can just confirm the above, if you're still seeing total loss (including center and bold) then we'll have to go from there. Thanks!

Also - can confirm that this is a regression
Works on 3.3, will attempt to bibisect now

Technically major (loss of format - loss of data)
Leaving as medium - doesn't seem to be a big deal if it's only boolean being lost. Would be nice if someone could poke at other formatting setings
Comment 14 Joel Madero 2014-05-21 04:52:49 UTC
Another two interesting points that I'm seeing while bibisecting:

I could verify the original report in an earlier release within bibisect - that is now resolved

Even in older releases boolean is converted to user-defined (correctly) with xlsx (expected?)
Comment 15 Joel Madero 2014-05-21 05:10:18 UTC
boolean turned to "number" after xlsx save

bd951694841e40420fe7938e162ddc5e6b577d1b is the first bad commit
commit bd951694841e40420fe7938e162ddc5e6b577d1b
Author: Bjoern Michaelsen <bjoern.michaelsen@canonical.com>
Date:   Thu Oct 17 07:28:27 2013 +0000

    source-hash-f2321c529adf6d4a455742c30fb75fbe3f7efe02
    
    commit f2321c529adf6d4a455742c30fb75fbe3f7efe02
    Author:     Jürgen Schmidt <jsc@apache.org>
    AuthorDate: Thu Dec 20 14:35:07 2012 +0000
    Commit:     Caolán McNamara <caolanm@redhat.com>
    CommitDate: Thu May 23 09:45:52 2013 +0100
    
        Resolves: #i121522# remove nonexistent, never published extensions...
    
        from ExtensionDependencies
    
        (cherry picked from commit 3484496e57ab59b4f613d7c9f727b5945e17e5b4)
    
        Conflicts:
        	officecfg/registry/data/org/openoffice/Office/ExtensionDependencies.xcu
    
        Change-Id: I1b2e7382837a4cfad667187a7f930ca54385c31e

:100644 100644 53818dd115409aba2cb8c46d98cb06c69412d9ca a387ff91c3848be8d8235a8b0a29b096f40f3d13 M	ccache.log
:100644 100644 3a78939d7d63cdf6030301a2146efb147b995e0e d0500a84fd70bb88043180bf56a26ae417a23717 M	commitmsg
:100644 100644 71e430639ffe015e5aa214e23e07da3c51de2333 6647aeb3429df04df5e2a942afd5f253dbae7829 M	dev-install.log
:100644 100644 e612bba29a2cbe23306349bb3dcaee825333c608 a4990d0024cbf11a17e00c2b9b3c773c159899e8 M	make.log
:040000 040000 a7ee6a82e726e255cbafa0ebcd782dfec2598797 6004b31ecdffff2b247e605b8ee72d5102dbbf49 M	opt



# bad: [793dbf6f80f497dfe587d560d6257f42a24273f6] source-hash-1581b1fc3ac82a7bd62df968226e98604a4ca52d
# good: [65fd30f5cb4cdd37995a33420ed8273c0a29bf00] source-hash-d6cde02dbce8c28c6af836e2dc1120f8a6ef9932
git bisect start 'latest' 'oldest'
# good: [8092559c5013969ebda017d79200463b9b975038] source-hash-fd84daf696a368c2c7561b5253b32a63ecdeca4a
git bisect good 8092559c5013969ebda017d79200463b9b975038
# bad: [0270ef1b76a6de423b30f7927362cc01c1a0fc38] source-hash-b1f7dd66b898b03cb4bd8d434b6370310ea95946
git bisect bad 0270ef1b76a6de423b30f7927362cc01c1a0fc38
# good: [aedcb9e93c73792e6d4f6bc5d74050efbe5af7c1] source-hash-358b60b3b172968a7605b428af01df456d7669b2
git bisect good aedcb9e93c73792e6d4f6bc5d74050efbe5af7c1
# good: [63ac4ab9665db60fac1e1813c9c80da52b2e87c6] source-hash-66e39940d763586060c4bcc8c3cd213495c40b79
git bisect good 63ac4ab9665db60fac1e1813c9c80da52b2e87c6
# bad: [318bcb373da01174e1947da5e3ce77e078a33a77] source-hash-4a143c44fe7ad266ab9ab7dca317b0099b1438d0
git bisect bad 318bcb373da01174e1947da5e3ce77e078a33a77
# bad: [e7831b72c3ba577f9b95d9e45f8a8e0aa3f02be3] source-hash-bb6ecd8b40313b7cc83d4e619029f4e001334a52
git bisect bad e7831b72c3ba577f9b95d9e45f8a8e0aa3f02be3
# bad: [796cc4f8eec3565f31fbd2adc95588a325312df2] source-hash-b45876bf0f2eeafba0a4f9f8f30cd4279eb2aa3e
git bisect bad 796cc4f8eec3565f31fbd2adc95588a325312df2
# bad: [c4afc1db4ee817735ba7f28ea7fd39c06b8f784f] source-hash-911186d98f06b43d4563b401244e2cc216b76f33
git bisect bad c4afc1db4ee817735ba7f28ea7fd39c06b8f784f
# bad: [bd951694841e40420fe7938e162ddc5e6b577d1b] source-hash-f2321c529adf6d4a455742c30fb75fbe3f7efe02
git bisect bad bd951694841e40420fe7938e162ddc5e6b577d1b
# first bad commit: [bd951694841e40420fe7938e162ddc5e6b577d1b] source-hash-f2321c529adf6d4a455742c30fb75fbe3f7efe02
Comment 16 Mark C 2014-05-21 12:55:17 UTC
Tested again on
Windows
LO 4.2.3.3

Centering is kept in empty cells in columns formatted with 'Centre'
Bold is kept in empty cells in columns formatted with 'Bold'
Boolean formatting seems to be lost:
It shows up as 1 and 0, but the format is 'General' and there is a formula in place "=True()" or "=False()"
All empty cells have lost their type and are now 'General'.

Just for fun I opened the xlsx in MS Office 2010. The Centred cells' formatting was preserved. The Bold was lost, even in populated cells.
The Boolean was set to 'Custom' and has a "TRUE","FALSE" formatting applied. There doesn't seem to be a 'Boolean' type in Excel 2010.
I've attached a screenshot of the Excel formatting dialog.
Comment 17 Mark C 2014-05-21 12:56:41 UTC
Created attachment 99505 [details]
Screenshot of Excel 2010 cell type dialog
Comment 18 Matthew Francis 2015-01-12 14:19:42 UTC
The behaviour of exporting the "BOOLEAN" cell format to .xlsx changed in the below commit. However, it didn't work perfectly before. Before the commit, empty cells got given a manual "TRUE";"TRUE";"FALSE" format code, while filled cells just got a General format. After the commit, empty cells also just get a General format.

Adding a Cc: to nopower@novell.com. Any chance you'd be interested in taking a look at this? Thanks


commit bf8e9b29aaebcbdd8f2f06b42ac97b8d9f8f4503
Author: Noel Power <noel.power@suse.com>
Date:   Wed May 22 10:00:34 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
Comment 19 Michael Meeks 2015-01-29 20:23:52 UTC
Interesting, if we can't reproduce this in master; is that really the case ?

Also - it's not entirely un-expected I guess to have trouble here; Excel has a built-in 'boolean' type - but LibreOffice does not - we use a boolean format on top of a double to simulate that. I guess that (in case this ever worked properly) something has gone wrong with the boolean format detection in the XLSX export logic. I wonder what the performance impact of that format lookup on each double export is however.

I guess for now we should prolly just export a custom / boolean format for those cells to Excel to avoid XLSX export perf. regressions. Does that make sense Markus ?
Comment 20 Markus Mohrhard 2015-01-30 09:38:38 UTC
(In reply to Michael Meeks from comment #19)
> Interesting, if we can't reproduce this in master; is that really the case ?
> 
> Also - it's not entirely un-expected I guess to have trouble here; Excel has
> a built-in 'boolean' type - but LibreOffice does not - we use a boolean
> format on top of a double to simulate that. I guess that (in case this ever
> worked properly) something has gone wrong with the boolean format detection
> in the XLSX export logic. I wonder what the performance impact of that
> format lookup on each double export is however.
> 
> I guess for now we should prolly just export a custom / boolean format for
> those cells to Excel to avoid XLSX export perf. regressions. Does that make
> sense Markus ?

Just to leave some comments here.

We are only talking about the boolean format export for now. We export a custom number format for that of the form TRUE;TRUE;FALSE which has the same effect. The issue that has been reported here is not an export issue. If you inspect the exported files you can find the correct content so it is purely an import issue which is consistent with the patch that seems to have introduced this issue.
Comment 21 Commit Notification 2015-02-10 21:20:12 UTC
Ursache Vladimir committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=39b81f2fd09f020d183fec2319eb688d2db9d8a9

tdf#68117 Partially fix boolean labels in XLSX

It will be available in 4.5.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 22 Commit Notification 2015-02-14 00:42:18 UTC
Ursache Vladimir committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=23159e5a23687683d8478a302b5634097e6c518d

tdf#68117 Unit test

It will be available in 4.5.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 23 Commit Notification 2015-02-14 00:48:27 UTC
Ursache Vladimir committed a patch related to this issue.
It has been pushed to "libreoffice-4-3":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=9c0910765954fba7611611befc1d42ab19d010ef&h=libreoffice-4-3

tdf#68117 Partially fix boolean labels in XLSX

It will be available in 4.3.7.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 24 Commit Notification 2015-02-14 00:48:41 UTC
Ursache Vladimir committed a patch related to this issue.
It has been pushed to "libreoffice-4-4":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=167cb85eb8e904cd79525fd589c0e66e47c1564d&h=libreoffice-4-4

tdf#68117 Partially fix boolean labels in XLSX

It will be available in 4.4.2.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 25 Markus Mohrhard 2015-02-14 00:54:25 UTC
The boolean export regression has been fixed. If you still see another problem please open a new bug report.
Comment 26 Robinson Tryon (qubit) 2015-12-17 07:22:55 UTC
Migrating Whiteboard tags to Keywords: (bibisected)
[NinjaEdit]