Bug 71897 - FILESAVE: Format Cells (Numbers) for a whole column not retained in empty cells if saving as XLSX
Summary: FILESAVE: Format Cells (Numbers) for a whole column not retained in empty cel...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.1.3.2 release
Hardware: All All
: high major
Assignee: Markus Mohrhard
URL:
Whiteboard: target:5.2.0
Keywords: bibisected, bisected, regression
: 83610 88328 (view as bug list)
Depends on:
Blocks:
 
Reported: 2013-11-22 00:41 UTC by Shahrizal Kamal
Modified: 2016-10-25 19:08 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
attachment-17588-0.html (2.86 KB, text/html)
2013-11-23 01:42 UTC, Shahrizal Kamal
Details
attachment-17588-1.dat (1 bytes, multipart/alternative)
2013-11-23 01:42 UTC, Shahrizal Kamal
Details
testing.xlsx (4.27 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2013-11-23 01:42 UTC, Shahrizal Kamal
Details
Test file with 3 example formats. (11.65 KB, application/vnd.oasis.opendocument.spreadsheet)
2015-09-13 17:45 UTC, Alexander
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Shahrizal Kamal 2013-11-22 00:41:00 UTC
Hello,

I have been using LibreOffice for two years now. My problem is mainly with LibreOffice Calc. I am utilizing it mostly for account book keeping. As such, I always have to format the cells by changing to my favorite fonts, number style (using separator, decimal point etc.) or date format (in accordance with the standard usage of my country i.e. dd/mm/yyyy).

However, the problem I always encounter with Calc is that whenever I re-open any existing files which I have formatted as explained above the cells revert to default values. This is quite stressful as I have to re-format the cells repeatedly.

I wish my report here will get your utmost attention.

Thank you.


regards,
Shahrizal Kamal
Comment 1 Dominique Boutry 2013-11-22 07:38:43 UTC
Hi,

- are your Calc file sufficiently anonymous to be provided to us for analysis ?
- what settings do you have in Tools > Options > Language settings > Language ?
- what format (ODS ? XLS ? other ?) do you use for saving and reopening ?
- do you format cells explicitely (what we call "direct formating"), not with the "Cell Style" feature ?

Regards
Comment 2 Shahrizal Kamal 2013-11-23 01:42:04 UTC
Created attachment 89665 [details]
attachment-17588-0.html

Hi,

Thanks for your prompt response. My reply are as below:

1. I am unable to provide my files as those are confidential files however,
I attached herewith a sample file for your attention. As you can see, the
first three rows are the format which I have set-up according to my
preference. The second three rows are new figures/data after I re-open the
file. It seems that it goes back to default.

2. The settings I have chosen in the 'Tools' menu for language is Malay
(Malaysian).

3. I always saved my files with 'xlsx' (Microsoft Office 2007/2010)
settings as sometimes I do have to share some of my files with my
colleagues who are mostly Windows users.

4. I do not understand fully the question but I guess I did the direct
formatting. To describe it further, I just highlighted the entire
column/row, right click at the highlighted column/row, chose 'Format Cell'
and did the necessary changes in accordance with my preferences.

Hope my explanation above suffices.


Thank you.


regards,
Shahrizal Kamal


On Fri, Nov 22, 2013 at 3:38 PM, <bugzilla-daemon@freedesktop.org> wrote:

>   *Comment # 1 <https://bugs.freedesktop.org/show_bug.cgi?id=71897#c1> on
> bug 71897 <https://bugs.freedesktop.org/show_bug.cgi?id=71897> from
> Dominique Boutry <dominique.boutry3@laposte.net> *
>
> Hi,
>
> - are your Calc file sufficiently anonymous to be provided to us for analysis ?
> - what settings do you have in Tools > Options > Language settings > Language ?
> - what format (ODS ? XLS ? other ?) do you use for saving and reopening ?
> - do you format cells explicitely (what we call "direct formating"), not with
> the "Cell Style" feature ?
>
> Regards
>
>  ------------------------------
> You are receiving this mail because:
>
>    - You reported the bug.
>
>
Comment 3 Shahrizal Kamal 2013-11-23 01:42:04 UTC
Created attachment 89666 [details]
attachment-17588-1.dat
Comment 4 Shahrizal Kamal 2013-11-23 01:42:04 UTC
Created attachment 89667 [details]
testing.xlsx
Comment 5 Tim Lloyd 2013-11-23 04:26:28 UTC
Hi,

I can confirm your problem which is specific to the xlsx format.

1. open your testing.xlsx doc
2. format column F to be a number with thousands separator
3. type some numbers in the column and observe the comma separator
4. save as xlsx
5. reopen doc
6. cells already populated are formatted correctly
7. cells not populated are not formatted

FYI I don't whether you can work around by storing as testing.xls where the formatting is maintained and your windows colleagues will also be able to read the file.

Cheers
Comment 6 Shahrizal Kamal 2013-11-23 04:54:04 UTC
Hi again,

Solved. Thanks for the advise. Really appreciate it.

Thank you.


On Sat, Nov 23, 2013 at 12:26 PM, <bugzilla-daemon@freedesktop.org> wrote:

>  Tim Lloyd <tim.lloyd@gmx.com> changed bug 71897<https://bugs.freedesktop.org/show_bug.cgi?id=71897>
>  What Removed Added  Status UNCONFIRMED NEW  Ever confirmed   1
>
>  *Comment # 5 <https://bugs.freedesktop.org/show_bug.cgi?id=71897#c5> on
> bug 71897 <https://bugs.freedesktop.org/show_bug.cgi?id=71897> from Tim
> Lloyd <tim.lloyd@gmx.com> *
>
> Hi,
>
> I can confirm your problem which is specific to the xlsx format.
>
> 1. open your testing.xlsx doc
> 2. format column F to be a number with thousands separator
> 3. type some numbers in the column and observe the comma separator
> 4. save as xlsx
> 5. reopen doc
> 6. cells already populated are formatted correctly
> 7. cells not populated are not formatted
>
> FYI I don't whether you can work around by storing as testing.xls where the
> formatting is maintained and your windows colleagues will also be able to read
> the file.
>
> Cheers
>
>  ------------------------------
> You are receiving this mail because:
>
>    - You reported the bug.
>
>
Comment 7 ign_christian 2014-09-09 02:24:30 UTC
*** Bug 83610 has been marked as a duplicate of this bug. ***
Comment 8 ign_christian 2014-09-09 02:44:09 UTC
Reproduced with LO 4.3.1.2, 4.2.6.3, 4.1.6.2 under Ubuntu 12.04 x86

Any numbers format (date, time, currency, etc) not retained for a whole column in empty cells.

Not reproduced with LO 4.0.6.2, 3.6.7.2 -> regression

Set Version to All per Bug 83610
Comment 9 Matthew Francis 2014-12-06 08:27:24 UTC
Results from bibisect-43all:

commit 4143274a9dca036358581c74fbeb91ec9e246b1a
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

# bad: [423a84c4f7068853974887d98442bc2a2d0cc91b] source-hash-c15927f20d4727c3b8de68497b6949e72f9e6e9e
# good: [65fd30f5cb4cdd37995a33420ed8273c0a29bf00] source-hash-d6cde02dbce8c28c6af836e2dc1120f8a6ef9932
git bisect start 'latest' 'oldest'
# bad: [e02439a3d6297a1f5334fa558ddec5ef4212c574] source-hash-6b8393474974d2af7a2cb3c47b3d5c081b550bdb
git bisect bad e02439a3d6297a1f5334fa558ddec5ef4212c574
# good: [8f4aeaad2f65d656328a451154142bb82efa4327] source-hash-1885266f274575327cdeee9852945a3e91f32f15
git bisect good 8f4aeaad2f65d656328a451154142bb82efa4327
# good: [9995fae0d8a24ce31bcb5e9cd0459b69cfbf7a02] source-hash-8600bc24bbc9029e92bea6102bff2921bc10b33e
git bisect good 9995fae0d8a24ce31bcb5e9cd0459b69cfbf7a02
# good: [8ad82bc1416a07501651e8d96fe268e47d3931d3] source-hash-13821254f88d2c5488fba9fe6393dcf4ae810db4
git bisect good 8ad82bc1416a07501651e8d96fe268e47d3931d3
# good: [d084d250b04446535ca1d7c29cf2062e6bd042b3] source-hash-688f72e3a2c3ef923389bbd21f6aea3afe1114db
git bisect good d084d250b04446535ca1d7c29cf2062e6bd042b3
# good: [c2069a369d738078124812312d51f21ea1ce2421] source-hash-f160e4935c474a5293b3d3c11b3d538efb4767a0
git bisect good c2069a369d738078124812312d51f21ea1ce2421
# bad: [a0f20bc04a32a7791ba765d2de2f44f1b74033d1] source-hash-1de66ba440855050a794b3b2a8647c1b02c210b8
git bisect bad a0f20bc04a32a7791ba765d2de2f44f1b74033d1
# bad: [10a508c66848944760e8ff2b4f76bd9c6da85c9e] source-hash-911186d98f06b43d4563b401244e2cc216b76f33
git bisect bad 10a508c66848944760e8ff2b4f76bd9c6da85c9e
# bad: [4143274a9dca036358581c74fbeb91ec9e246b1a] source-hash-f2321c529adf6d4a455742c30fb75fbe3f7efe02
git bisect bad 4143274a9dca036358581c74fbeb91ec9e246b1a
# good: [e818f97b99709bcedf56865e733647666cfae09c] source-hash-66e39940d763586060c4bcc8c3cd213495c40b79
git bisect good e818f97b99709bcedf56865e733647666cfae09c
# first bad commit: [4143274a9dca036358581c74fbeb91ec9e246b1a] source-hash-f2321c529adf6d4a455742c30fb75fbe3f7efe02
Comment 10 Matthew Francis 2015-01-09 11:08:50 UTC
The behaviour seems to have changed as of the below commit.

Adding Cc: to nopower@novell.com. Could you possibly take 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 11 Matthew Francis 2015-01-18 13:59:19 UTC
*** Bug 88328 has been marked as a duplicate of this bug. ***
Comment 12 Alexander 2015-09-13 17:45:56 UTC
Created attachment 118677 [details]
Test file with 3 example formats.
Comment 13 Alexander 2015-09-13 18:00:30 UTC
Hi,

I'm affected by this bug too. The definition is very similar, but first I've noticed this bug for the currency format.

OS: Windows 7 SP1 64-bit
LO: 4.4.4.3, 4.4.5.2

I've added the file "Test_2.ods" with 3 example formats: date; currency; number with a fractional part. You can see, that the colomns A,C,D are properly formatted (so, you can add entries to empty rows and the data will be formatted as expected). After saving this file to the .xlsx format you will lose all the formatting for the empty rows. If you'll try to manually restore columns formating for the .xslx file, it would work only till the file closing. After reopening the .xlsx file you will see all formatting for the empty rows is lost again.

P.S. It's quite strange, but I have not been affected by this bug for the currency format with LO 4.3.5 - 4.3.7. It started for me with LO 4.4.
Comment 14 Robinson Tryon (qubit) 2015-12-13 11:09:27 UTC Comment hidden (obsolete)
Comment 15 Markus Mohrhard 2016-04-18 23:49:38 UTC
This is an import and not an export bug. The file is correctly written by the export code.
Comment 16 Commit Notification 2016-04-19 01:43:12 UTC
Markus Mohrhard committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=1be6c440c645e441af0e04ba7711e4b2d80aad63

use the column style for as default, tdf#71897

It will be available in 5.2.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.