Download it now!
Bug 66242 - FILESAVE - Cell with formula lost formatting on save to xls
Summary: FILESAVE - Cell with formula lost formatting on save to xls
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.1.0.1 rc
Hardware: All All
: medium major
Assignee: Markus Mohrhard
URL:
Whiteboard: target:4.2.0 target:4.1.0.2
Keywords:
Depends on:
Blocks:
 
Reported: 2013-06-27 08:14 UTC by Jaise James
Modified: 2013-11-20 16:16 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
orginal ods file (9.24 KB, application/vnd.oasis.opendocument.spreadsheet)
2013-06-27 08:15 UTC, Jaise James
Details
Saved xls file (6.00 KB, application/vnd.ms-excel)
2013-06-27 08:16 UTC, Jaise James
Details
screen print in original file (51.30 KB, image/png)
2013-06-27 19:27 UTC, Cor Nouws
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Jaise James 2013-06-27 08:14:12 UTC
Calc looses formatting on save to xls for the cells having formula.


You loose cell borders on save to xls. You will not see any border or formatting like bold, colour etc when reopen the file. 


Attached :

1. Sample xls saved.
2. orginal ods file.
Comment 1 Jaise James 2013-06-27 08:15:22 UTC
Created attachment 81531 [details]
orginal ods file
Comment 2 Jaise James 2013-06-27 08:16:02 UTC
Created attachment 81532 [details]
Saved xls file

Saved xls file
Comment 3 Cor Nouws 2013-06-27 19:27:51 UTC
Created attachment 81578 [details]
screen print in original file

Hi Jesse,
thanks for the report. I can confirm this.
There is something strange with the formatting of the cells in the original file too. Look at the decimal/thousand separators.
   See attached screen print.

I work in a Dutch version. The language of the yellow cells (Ctrl+!) shows Dutch...
Comment 4 Eike Rathke 2013-06-27 19:58:02 UTC
(In reply to comment #3)
> There is something strange with the formatting of the cells in the original
> file too. Look at the decimal/thousand separators.

For the number formats you're a victim of cached values that are displayed. Hit Shift+Ctrl+F9 to get the separators of your locale. Cc'ing Moggi.
Comment 5 Noel Power 2013-06-28 15:35:57 UTC
*** Bug 65627 has been marked as a duplicate of this bug. ***
Comment 6 Markus Mohrhard 2013-06-29 18:52:16 UTC
(In reply to comment #4)
> (In reply to comment #3)
> > There is something strange with the formatting of the cells in the original
> > file too. Look at the decimal/thousand separators.
> 
> For the number formats you're a victim of cached values that are displayed.
> Hit Shift+Ctrl+F9 to get the separators of your locale. Cc'ing Moggi.

The problem with the cached values should be gone in 4.1.0.2 because we no longer use the string. Every cell has now an hard number format after import and the code for my ugly hack has been removed.

However I can reproduce the problem with the XLS export in master and I'm going to investigate that. It seems this only affects formula cells which would speak for a regression through my inherited number format removal.
Comment 7 Cédric_leporcq 2013-06-30 21:11:00 UTC
I can confirm this bug in 4.1.0.1 for saving in xls AND xlsx formats, but not ods.
Comment 8 Jaise James 2013-07-01 04:30:23 UTC
Xls Saved using old version also looses it cell formatting for cell with formula. So I feel it is bug during opening new version of libreoffice rather than saving.
Comment 9 Eike Rathke 2013-07-01 22:09:14 UTC
(In reply to comment #6)
> > > There is something strange with the formatting of the cells in the original
> > > file too. Look at the decimal/thousand separators.
> > 
> > For the number formats you're a victim of cached values that are displayed.
> > Hit Shift+Ctrl+F9 to get the separators of your locale. Cc'ing Moggi.
> 
> The problem with the cached values should be gone in 4.1.0.2 because we no
> longer use the string. Every cell has now an hard number format after import
> and the code for my ugly hack has been removed.

This is something different. The number format says to "format with decimal and group separator of the current locale", so the actual display string should change when loaded in a locale that uses different separators. It sounds as if that will not be covered by the changes, or am I wrong?
Comment 10 Markus Mohrhard 2013-07-02 00:56:47 UTC
(In reply to comment #9)
> (In reply to comment #6)
> > > > There is something strange with the formatting of the cells in the original
> > > > file too. Look at the decimal/thousand separators.
> > > 
> > > For the number formats you're a victim of cached values that are displayed.
> > > Hit Shift+Ctrl+F9 to get the separators of your locale. Cc'ing Moggi.
> > 
> > The problem with the cached values should be gone in 4.1.0.2 because we no
> > longer use the string. Every cell has now an hard number format after import
> > and the code for my ugly hack has been removed.
> 
> This is something different. The number format says to "format with decimal
> and group separator of the current locale", so the actual display string
> should change when loaded in a locale that uses different separators. It
> sounds as if that will not be covered by the changes, or am I wrong?

Why should that not be fixed with my code? We now don't import the cached string anymore, we always use a number format and the cached value!! to calculate the string. In the old code we used the cached string to workaround the missing inherited number formats but all of this code should be gone in master and 4.1.0.2.

I also have a fix for this bug here which is a regression. I accidentally removed the code that fetched the number format for formula cells.

If I'm not totally off in the current design there is no difference between a document that has been recalculated after import and a document where we use the cached value during import. In both cases we take the value of the result (either calculated or cached) and use the stored(either from import or in case of inherited number format from the first calculation) number format from ATTR_VALUE_FORMAT to create the formatted string. The only difference to the old 3.6 behavior is that for inherited number format we set the implicit number format as explicit number format after calculation. Please correct me if I made a really stupid mistake.
Comment 11 Commit Notification 2013-07-02 01:21:58 UTC
Markus Mohrhard committed a patch related to this issue.
It has been pushed to "master":

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

don't forget the formula cell style during xls/xlsx export, fdo#66242



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 Commit Notification 2013-07-02 07:28:29 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=4e4cad34f696ef820edc24eed0e26bbeae3757c2&h=libreoffice-4-1

don't forget the formula cell style during xls/xlsx export, fdo#66242


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 13 Eike Rathke 2013-07-02 10:44:45 UTC
(In reply to comment #10)
> > This is something different. The number format says to "format with decimal
> > and group separator of the current locale", so the actual display string
> > should change when loaded in a locale that uses different separators. It
> > sounds as if that will not be covered by the changes, or am I wrong?
> 
> Why should that not be fixed with my code? We now don't import the cached
> string anymore, we always use a number format and the cached value!! to
> calculate the string. In the old code we used the cached string to
> workaround the missing inherited number formats but all of this code should
> be gone in master and 4.1.0.2.

I just verified in 4-1 branch that indeed with
LC_ALL=nl_NL.UTF-8 soffice Sample\ ods\ file.ods
the numbers are displayed with group separator '.' instead of ','

All good, thanks!