Download it now!
Bug 79398 - Cell [NatNumX] formatting is lost when save as XLSX
Summary: Cell [NatNumX] formatting is lost when save as XLSX
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium normal
Assignee: Laurent BP
URL:
Whiteboard: target:5.3.0
Keywords:
Depends on:
Blocks:
 
Reported: 2014-05-29 08:07 UTC by Kevin Suo
Modified: 2016-09-16 11:26 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
ODS file in which cell A1 is formatted as [NatNum5] (14.12 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-05-29 08:07 UTC, Kevin Suo
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Kevin Suo 2014-05-29 08:07:48 UTC
Created attachment 100087 [details]
ODS file in which cell A1 is formatted as [NatNum5]

Description:
The attached ods file has "[NatNum5]General" formatting in A1. When save as XLSX, this format is lost.

Steps to reproduce:
1. Open the attached ods file with Calc. Notice that A1 is showing "壹佰贰拾" (which means ONE HUNDRED AND TWENTY).
2. Save as XLSX, then reopen with Calc. Notice that A1 is showing "120" rather than "壹佰贰拾".

(The reason maybe because LibreOffice uses "[NatNumX]" format, but Microsoft Office uses "[DBNumX]" format.)
Comment 1 yanjingtao 2014-05-29 08:51:20 UTC
Reproducible with 

OS:Fedora 20 x86_64
Version: 4.2.4.2
Build ID: 4.2.4.2-8.fc20

and

OS:Fedora 20 x86_64
Version: 4.3.0.0.beta1
Build ID: 2e39c7e59c8fc8b16a54c3d981dceef27fb0c07f

OS:Windows XP SP3
Version: 4.2.4.2
Build ID: 63150712c6d317d27ce2db16eb94c2f3d7b699f8
Comment 2 Kevin Suo 2014-05-29 08:57:24 UTC
Comment on attachment 100087 [details]
ODS file in which cell A1 is formatted as [NatNum5]

Fixing MIME TYPE
Comment 3 Kevin Suo 2014-05-29 09:28:55 UTC
Please note:
In order for A1 to show as "壹佰贰拾", your OS should have zh_CN locale installed, because [NatNumX] and [DBNumX] are locale related.
Comment 4 QA Administrators 2015-06-08 14:43:01 UTC Comment hidden (obsolete)
Comment 5 Kevin Suo 2015-06-24 01:51:23 UTC
(In reply to QA Administrators from comment #4)

This bug still exists in
Version: 5.0.0.1
Build ID: 9a0b23dd0ab9652e0965484934309f2d49a7758e
Locale: zh-CN (zh_CN)
Comment 6 Laurent BP 2016-08-12 10:54:44 UTC
This does not seem to have ever worked. I tested many versions of LibO (down to 3.3.0) and AOO (4.1.2) and OOo (3.3.0, 3.0.1), and always reproduced the bug. Same for XLS or XLSX formats.
Comment 7 Eike Rathke 2016-08-15 11:46:15 UTC
Apart from that the export from NatNumX to DBNumY isn't implemented, there is no mapping from NatNum5 (Korean: formal upper case text) to any DBNumY value. In fact LibreOffice knows 11 Korean NatNum values but Excel only 4 different DBNum values and "formal upper case text" is not among those. So which should we map to? In any case, reloading from .xls or .xlsx the original NatNum mode will be lost and DBNum could only be a substitute.

See http://api.libreoffice.org/docs/idl/ref/namespacecom_1_1sun_1_1star_1_1i18n_1_1NativeNumberMode.html for available mappings.
Comment 8 Commit Notification 2016-08-15 11:58:51 UTC
Laurent Balland-Poirier committed a patch related to this issue.
It has been pushed to "master":

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

tdf#79398 Export to XL DBNum codes

It will be available in 5.3.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 9 Commit Notification 2016-08-15 12:29:40 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

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

use SvNumberNatNum::IsComplete() instead of IsSet(), tdf#79398 follow-up

It will be available in 5.3.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 10 Eike Rathke 2016-08-15 13:36:26 UTC
(In reply to Eike Rathke from comment #7)
> Apart from that the export from NatNumX to DBNumY isn't implemented, there
> is no mapping from NatNum5 (Korean: formal upper case text) to any DBNumY
> value.
Darn, that comment is moot, it's about Chinese NatNum5 that maps to DBNum2, sorry for fuzz.
Comment 11 Eike Rathke 2016-08-15 14:30:36 UTC
Actually this is not fixed, there's more to it as the format string to be exported is converted to an en-US format string because of possible locale dependent keywords, see SvNumberFormatter::GetFormatStringForExcel(), and during that conversion under SvNumberFormatter::PutandConvertEntry() the new SvNumberNatNum loses the original locale's context.

Excel's locale support within number format works differently and we'll probably have to write the locale information into the format code as well. That likely was the reason why the MapNatNumToDBNum() code was #ifdef THE_FUTURE ;-)
Comment 12 Eike Rathke 2016-08-15 15:04:13 UTC
So, two things to do:
1. maintain the locale information for the converted SvNumberNatNum during PutandConvertEntry(), but probably only if the original format's locale was not LANGUAGE_SYSTEM
2. write the locale information in SvNumberformat::GetMappedFormatstring() similar to what is done in the special case for the Thai locale
Comment 13 Commit Notification 2016-08-23 12:42:05 UTC
Laurent Balland-Poirier committed a patch related to this issue.
It has been pushed to "master":

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

tdf#79398 Add LCID with DBNum during export to XL

It will be available in 5.3.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 14 Commit Notification 2016-08-23 13:49:39 UTC
Laurent Balland-Poirier committed a patch related to this issue.
It has been pushed to "master":

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

tdf#79398 Add qa unit test

It will be available in 5.3.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 15 Commit Notification 2016-09-01 09:19:04 UTC
Laurent Balland-Poirier committed a patch related to this issue.
It has been pushed to "master":

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

tdf#79398 Insert LCID in the correct sub-format

It will be available in 5.3.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 16 Commit Notification 2016-09-16 11:26:13 UTC
Laurent Balland-Poirier committed a patch related to this issue.
It has been pushed to "master":

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

tdf#79398 Add qa unit test for sub-format

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