Bug 128122 - BASIC CCur should obey locale setting
Summary: BASIC CCur should obey locale setting
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: BASIC (show other bugs)
Version:
(earliest affected)
6.2.7.1 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: difficultyInteresting, easyHack, skillCpp, topicDebug
Depends on:
Blocks: Macro-Documentation
  Show dependency treegraph
 
Reported: 2019-10-13 19:14 UTC by Johnny Rosenberg
Modified: 2021-03-26 11:42 UTC (History)
8 users (show)

See Also:
Crash report or crash signature:
Regression By:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Johnny Rosenberg 2019-10-13 19:14:32 UTC
From LibreOffice Help:
”CCur Function
Converts a string expression or numeric expression to a currency expression. The locale settings are used for decimal separators and currency symbols.”

My locale is Swedish, so my currency is ”kr” and my decimal symbol is a comma.
Yet, the CCur function doesn't accept anything else than a decimal point and with no currency symbol at all:

Print CCur("75,50 kr") ⇨ Illegal value or data type. Wrong data type.
Print CCur("75.50 kr") ⇨ Illegal value or data type. Wrong data type.
Print CCur("75,50") ⇨ 7550.0000
Print CCur("75.50") ⇨ 75.5000

If I create a dialogue with a currency field in it, and enter 75, the result is ”75,00 kr”, which is correct for my locale.

So either the function doesn't work properly or the help text is wrong.
Comment 1 raal 2019-10-14 18:35:36 UTC
I can confirm with Version: 6.4.0.0.alpha0+
Build ID: 9b5dad13b56bdde7c40970351af3da3a2c3c9350
CPU threads: 4; OS: Linux 4.15; UI render: default; VCL: x11; 

use
print ccur(str("75,50"))
print ccur(cdbl("75,50"))

I don't think it's a bug, because ccur should be compatible with VBA (or it's VBA function).
Comment 2 Johnny Rosenberg 2019-10-14 19:24:56 UTC
So then the manual is wrong (”The locale settings are used for decimal separators and currency symbols”, but they are clearly not). Should I report that in a separate bug report for the documentation?
Comment 3 Buovjaga 2020-04-24 14:17:08 UTC Comment hidden (obsolete)
Comment 4 Andreas Heinisch 2021-01-17 18:15:25 UTC
This bug report could be qualified as difficultyInteresting, since it requests the usage of the numberformatter including the locale settings with additional testing of the function itself.
Comment 6 Buovjaga 2021-01-20 11:15:47 UTC Comment hidden (obsolete)
Comment 7 Andreas Heinisch 2021-01-20 11:59:20 UTC
Imho, it is clearly a malfunction of the ccur-function, since all of the conversion are hard coded and not locale dependent: https://opengrok.libreoffice.org/xref/core/basic/source/sbx/sbxcurr.cxx?r=7ddedd25&fi=ImpGetCurrency#88
Comment 8 Buovjaga 2021-01-20 12:10:51 UTC Comment hidden (obsolete)
Comment 9 Andreas Heinisch 2021-01-20 12:22:18 UTC
Tested with MS-Excel using a German locale, where the group separator is "." and the decimal separator is "," respectively:

MsgBox CCur("75,50 €") ' 75.5
MsgBox CCur("75.50 €") ' 7550
MsgBox CCur("75,50") ' 75.5
MsgBox CCur("75.50") ' 7550

Same examples in LO using a German locale:

MsgBox CCur("75,50 €") ' Illegal value or data type. Wrong data type.
MsgBox CCur("75.50 €") ' Illegal value or data type. Wrong data type.
MsgBox CCur("75,50") ' 7550.0000
MsgBox CCur("75.50") ' 75.5000
Comment 10 Buovjaga 2021-01-20 12:25:29 UTC Comment hidden (obsolete)
Comment 11 Mike Kaganski 2021-03-16 05:55:00 UTC
Code pointer: SvNumberFormatter::IsNumberFormat is used to convert strings to numbers.
Comment 12 Mike Kaganski 2021-03-16 06:20:27 UTC
FTR, regarding VBA's CCur locale dependence:

VBA CCur documentation [1] tells:

> You should use the data-type conversion functions instead of Val to provide
> internationally aware conversions from one data type to another. For example,
> when you use CCur, different decimal separators, different thousand separators,
> and various currency options are properly recognized depending on the locale
> setting of your computer.

So indeed CCur is documented to take locale into account when converting strings.

[1] https://docs.microsoft.com/en-us/office/vba/language/concepts/getting-started/type-conversion-functions
Comment 13 Arpit Bandejiya 2021-03-20 20:04:16 UTC
Hi! I would like to work on this issue.
Comment 14 Arpit Bandejiya 2021-03-20 20:07:23 UTC
If possible can someone tell me how to generate the error ( how to change the locale to german) 
Thanks!
Comment 15 Johnny Rosenberg 2021-03-20 20:19:39 UTC
”If possible can someone tell me how to generate the error ( how to change the locale to german)”

I think it depends on your operating system.
Comment 16 Buovjaga 2021-03-20 21:19:37 UTC
(In reply to Arpit Bandejiya from comment #14)
> If possible can someone tell me how to generate the error ( how to change
> the locale to german) 

At least on Linux, you can first generate the German locale https://wiki.archlinux.org/index.php/Locale

So in /etc/locale.gen you want to uncomment the line with de_DE.UTF-8 UTF-8 and generate with locale-gen

Then you can launch LibreOffice from the command line with

LC_ALL=de_DE.UTF-8 libreoffice

(or point to where your soffice is in case of self-built version)
Comment 17 Arpit Bandejiya 2021-03-20 21:24:30 UTC
(In reply to Johnny Rosenberg from comment #15)
> ”If possible can someone tell me how to generate the error ( how to change
> the locale to german)”
> 
> I think it depends on your operating system.

I have debian based Linux system
Comment 18 Arpit Bandejiya 2021-03-20 21:25:46 UTC
(In reply to Buovjaga from comment #16)
> (In reply to Arpit Bandejiya from comment #14)
> > If possible can someone tell me how to generate the error ( how to change
> > the locale to german) 
> 
> At least on Linux, you can first generate the German locale
> https://wiki.archlinux.org/index.php/Locale
> 
> So in /etc/locale.gen you want to uncomment the line with de_DE.UTF-8 UTF-8
> and generate with locale-gen
> 
> Then you can launch LibreOffice from the command line with
> 
> LC_ALL=de_DE.UTF-8 libreoffice
> 
> (or point to where your soffice is in case of self-built version)

Sure will look into it...
Comment 19 Arpit Bandejiya 2021-03-23 05:30:29 UTC
Hi, I looked into the issue, the ImpStringToCurrency is hard coded which is causing the issue, as per now I think it is there for only for one standard. If possible can I get some references where a more generic string parser is written or to the struct or library I should use to handle this problem. Thanks! 

Link for the ImpStringToCurrency
https://opengrok.libreoffice.org/xref/core/basic/source/sbx/sbxcurr.cxx?r=7ddedd25&fi=ImpGetCurrency#88
Comment 20 Andreas Heinisch 2021-03-23 07:16:42 UTC
You may start with the number formatter:

LanguageType eLangType = Application::GetSettings().GetLanguageTag().getLanguageType();
std::shared_ptr<SvNumberFormatter> pNumberFormatter =
    std::make_shared<SvNumberFormatter>( comphelper::getProcessComponentContext(), eLangType );

double fResult = 0;
sal_uInt32 nIndex = pNumberFormatter->GetStandardIndex( eLangType );
bool bSuccess = pNumberFormatter->IsNumberFormat( rStr, nIndex, fResult );
if ( bSuccess )
{
    SvNumFormatType nType = pNumberFormatter->GetType( nIndex );
    bSuccess = nType & SvNumFormatType::CURRENCY || nType & SvNumFormatType::NUMBER;
}
Comment 21 Arpit Bandejiya 2021-03-26 11:13:07 UTC
Hi, sorry for the late response got some unpredicted work. Thanks for the help earlier.

Can someone help me with how to find the library to get the class in the file. As in our case, we need SvNumberFormatter, SvNumFormatType, Application, and LanguageType as of now, I figured that most of the files in which there is a call to the LanguageType had the following header included:
      #include <vcl/settings.hxx>  though it didn't work

later I figured that #include <i18nlangtag/lang.h> is the main header for the LanguageType. Is there a general way to find the header? or Is it by exploration only? Please do let me know. Sry for asking a basic doubt, I'm fairly new to the code base so I'm not much aware of it.

Thanks!
Comment 22 Arpit Bandejiya 2021-03-26 11:15:54 UTC
Also if there is any other communication method in the Org please do let me know.. any mailing list or any other medium to get generally asked questioned resolved. It will be of great help.
Comment 23 Buovjaga 2021-03-26 11:42:25 UTC
(In reply to Arpit Bandejiya from comment #22)
> Also if there is any other communication method in the Org please do let me
> know.. any mailing list or any other medium to get generally asked
> questioned resolved. It will be of great help.

https://wiki.documentfoundation.org/Development/Mailing_List
https://wiki.documentfoundation.org/Website/IRC