Download it now!
Bug 66506 - FORMATTING: Rendering date format not correct for xls and ods files in 4.1 LO
Summary: FORMATTING: Rendering date format not correct for xls and ods files in 4.1 LO
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.1.0.1 rc
Hardware: Other All
: highest major
Assignee: Not Assigned
URL:
Whiteboard: target:4.2.0 target:4.1.0.2
Keywords: regression
Depends on:
Blocks: mab4.2
  Show dependency treegraph
 
Reported: 2013-07-02 13:27 UTC by Mikeyy - L10n HR
Modified: 2014-07-30 15:34 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:


Attachments
File saved with 4.0.3 or earlier version (313.50 KB, application/vnd.ms-excel)
2013-07-02 13:27 UTC, Mikeyy - L10n HR
Details
File for february 2013 save with LO 4.0.3.3 (313.50 KB, application/vnd.ms-excel)
2013-07-02 13:28 UTC, Mikeyy - L10n HR
Details
File saved with LO 4.1 RC1 (314.50 KB, application/vnd.ms-excel)
2013-07-02 13:29 UTC, Mikeyy - L10n HR
Details
TEST1 without sheet protection (313.00 KB, application/vnd.ms-excel)
2013-07-02 14:42 UTC, Mikeyy - L10n HR
Details
TEST1 - ODS version (49.31 KB, application/vnd.oasis.opendocument.spreadsheet)
2013-07-02 15:31 UTC, Mikeyy - L10n HR
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Mikeyy - L10n HR 2013-07-02 13:27:17 UTC
Created attachment 81880 [details]
File saved with 4.0.3 or earlier version

Basicly, I have 2 problems here, but my guess it's same root cause. Maybe I'm not correct, but I'll report it both in this bug.

This is also regression since all this works without problems in LO 4.0.3 stable version.

BUG 1
----------
TEST1.xls
1. Open file with 4.0.3.3 for example.
2. In cell D9 enter 1st of July (1.7.2013).
3. All cells from E9:AH9 will change value and represent all days on month.

Now repeat steep 1 to 3 with 4.1. RC1 and you will notice that cell AH9 lost it's formatting.
If I open file saved with older version of LO which has date in cell AH9, for example file from May which has 31 days in month, that cell will keep it's formatting. But if I for example take file from february (TEST1FEB.xls), all cell that didn't have date in february (AF9:AH9) will loose it's formatting.
----------


BUG 2
----------
TEST2.xls
I can't tell you how to reproduce this since I didn't manage to do it my self, but this happened with files saved with LO 4.1 RC1.
TEST2.xls is same file as TEST1.xls after saved with LO 4.1 RC1
I have 3 files like this, but didn't manage to reproduce it deliberatly.
----------
Comment 1 Mikeyy - L10n HR 2013-07-02 13:28:21 UTC
Created attachment 81881 [details]
File for february 2013 save with LO 4.0.3.3
Comment 2 Mikeyy - L10n HR 2013-07-02 13:29:44 UTC
Created attachment 81882 [details]
File saved with LO 4.1 RC1

Formatting lost in cells C7:AH9
Comment 3 Eike Rathke 2013-07-02 14:30:29 UTC
@moggi:
AH9 has General number format (sheet is protected, need to unprotect to see) and in 4.1 doesn't inherit the format from AG9 anymore if the result changes, which earlier versions did.
Comment 4 Mikeyy - L10n HR 2013-07-02 14:42:05 UTC
Created attachment 81889 [details]
TEST1 without sheet protection

Did you try to remove sheet protection?
This is also bug, it doesn't asks for password at all. I removed it with 4.0.4.1 which I have installed home, and it didn't as for password (both sheet and document passwords).
Comment 5 Eike Rathke 2013-07-02 15:09:51 UTC
(In reply to comment #4)
> Did you try to remove sheet protection?
> This is also bug, it doesn't asks for password at all.

That is NOT a bug, it is possible to setup protection without password.
Comment 6 Mikeyy - L10n HR 2013-07-02 15:18:22 UTC
My bad then, but since I made those files and have both (ODS and xls) versions, I know I didn't make them that way. :)
Comment 7 Mikeyy - L10n HR 2013-07-02 15:31:51 UTC
Created attachment 81891 [details]
TEST1 - ODS version

I'm really sorry for spamming this bug report, but I just want to help at finding problem faster.
When I posted last comment it occured to me that I didn't test this on original ODS file. Tested it now and it shows same problem.

Attached you can find test ODS file.
Comment 8 Markus Mohrhard 2013-07-02 23:20:22 UTC
I think I have an idea what is going wrong with the number formats and why the issue does not sow up in master.

In master my code for inherited number formats has been broken by one of Kohei's merges. In 4-1 we don't have that merge so we still have my old code which tells the formula cell that it needs a number format because the number format is not yet set. Maybe the easiest solution is to first import the number format and set it to the cell and after that import the cell.

I'll fix the problems from the merge in the first step and then have a look at this bug here.
Comment 9 Commit Notification 2013-07-03 06:28:54 UTC
Markus Mohrhard committed a patch related to this issue.
It has been pushed to "master":

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

xls cells have all hard number format, fdo#66506



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 10 Mikeyy - L10n HR 2013-07-03 06:36:43 UTC
Not sure what does inherriting number formats means, but all those cell are formated as date, DD.MM.YY format. Checked both xls and ODS file versions.
Comment 11 Commit Notification 2013-07-03 08:19:03 UTC
Markus Mohrhard committed a patch related to this issue.
It has been pushed to "libreoffice-4-1":

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

xls cells have all hard number format, fdo#66506


It will be available in LibreOffice 4.1.

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 12 Mikeyy - L10n HR 2013-07-04 09:28:28 UTC
I was editing one file yesterday and today when I opened it again, all number formatting was gone. No % formatting, no 1000 separator, no fixed number of decimal places... Even some aligments and conditional formatting were gone.

I guess 4.1 RC1 has real problems with that and I would call it a blocker since it's destroys all document formatting.

If you want I can send you files privatly on email since I can't post them publicly.
Comment 13 Mikeyy - L10n HR 2013-07-08 06:05:21 UTC
Great work! Everything is working again for XLS files.
But this problem remains for ODS files.
Try opening my TEST1.ods file and changing first date to 1.7.2013 , issue remains.


Bug from comment 12 is corrected also. I assume it was bug 66242.
Comment 14 Mikeyy - L10n HR 2013-08-02 07:16:02 UTC
Bump.

Just wanted to divert attention that this is half fixed since it's not yet working for ODS files in LO 4.1. Couldn't test XLSX since when I save as XLSX all hell breaks loose and formatting collapses, but I'll report that in seperate bug when this is fixed.
Comment 15 tommy27 2013-08-20 08:04:24 UTC
according to previous comment I modify summary notes in order to include the .ods format
Comment 16 sophie 2013-11-05 12:41:34 UTC
Still present in Version: 4.1.3.1
Build ID: b42498da0e3f91b17e51b55c8295ec4f8f22087 - Sophie
Comment 17 Björn Michaelsen 2014-01-17 09:51:47 UTC
(This is an automated message.)

Setting priority to highest as this is a 4.1 MAB. This is part of an effort to make the importance of MAB reflected in priority too.
Comment 18 tommy27 2014-05-03 08:21:06 UTC
@Mikeyy - L10n HR

please give update of the status of this bug with current 4.2.3.3 release.

if all issues are gone set status to FIXED while if some part of the bug is still here, I suggest to close this one, set status to MOVED, then open a new clean report with only the relevant residual buggy behaviour (including new test files) and drop it's link here under "See also"
Comment 19 Mikeyy - L10n HR 2014-05-03 11:17:35 UTC
When opening with 4.2.4.1 ODS (last attachment in list), enter 1.1.2014 in cell D9, and you still have date formatted as number instead of date on last 3 date cells (29.01 / 30.01 / 31.01).
Comment 20 tommy27 2014-05-10 11:34:04 UTC
Ok. moving it to mab4.2 list
Comment 21 Kohei Yoshida 2014-05-18 17:10:44 UTC
You can add D.M.Y in the "Date acceptance patterns" in the Options dialog under

Language settings - Languages

and see if that solves the problem.  Eike said that (and I agree with him) date recognition is a super hard problem and "fixing" for one would likely "break" for another, which is why he added that option.
Comment 22 Mikeyy - L10n HR 2014-05-18 18:51:49 UTC
My locale (HR) has this pattern as default: D.M.Y;D.M;D.M.Y.;D/M/Y;D/M
So D.M.Y is already included and tested with.

Bug started with 4.1 version and it's still present in 4.2. There was no problems in 4.0
Problem with xls was solved in this bug, but not ods.
Comment 23 Markus Mohrhard 2014-07-19 12:15:46 UTC
The ods file is protected which makes working with it a pain. Can you please upload a document without a password or state the password.
Comment 24 Mikeyy - L10n HR 2014-07-19 19:30:38 UTC
You got mail.
Comment 25 Markus Mohrhard 2014-07-30 15:34:14 UTC
The problem with ODS is already in the file and works for a new and clean file or after fixing that file.

So there is nothing we can do here. This file was at one point written with a broken exporter and there is no way to recover from that.