Convert String to Currency (Swedish)

4 replies [Last post]
Albireo
Offline
Last seen: 18 hours 38 min ago
Title:
Joined: 19 Apr 2016
Posts: 2
Hi! I have problems dealing with numbers / currencies in LibreOffice. (Installed is the Swedish LibreOffice v5.1.2.2) In Sweden we have comma as decimal ex. 3,14 (not 3.14)

If I write the following numbers into a cell (2713,98),
It doesn’t work to use the following command .:
oSheet.getCellByPosition( 1, 5 ).setValue( 2713,98 ) ( the desired result was .: “2 713,98 kr” or “2 714 kr” )
If I do the same with setString .:
oSheet.getCellByPosition( 1, 5 ).setString( "2713,98" )
I got a string with that numbers –

How can I get this cell as number / Swedish currency in Calc?
(I want to use the cell later in a formula / calculation)

Regards!

mark_t
Offline
Last seen: 8 hours 17 min ago
Title: ★★★★
Joined: 26 Apr 2016
Posts: 90
Check that you have
Check that you have LibreOffice set to the correct language, under Tools, Options, Language Settings, Languages.

I’m not sure if language settings will change the numbers used by the Basis IDE, you might still need to use 2713.98 instead of 2713,98.

You would set the value of the cell using setValue, I find it easier to read the code using the following format and let Basic handle the setValue.


oSheet.getCellByPosition( 1, 5 ).Value = 2713,98

You will also need to set the correct cell format to display the currency. You can do this manually using the cell format dialog, or using the format string from cell format dialog using basic:-


oSheet.getCellByPosition( 1, 5 ).NumberFormat = _
            FindCreateNumberFormatStyle("# ##0,00 [$kr-41D];-# ##0,00 [$kr-41D]")

Where I copied the format string from the cell format dialog of Calc, after setting the language for that cell to Swedish.

Above uses the following function:-


Function FindCreateNumberFormatStyle (sFormat As String, Optional doc As Object, Optional locale As com.sun.star.lang.Locale) As Integer
'Author: Andrew Pitonyak
'email:   andrew@pitonyak.org 

	Dim oDoc As Object
	Dim aLocale As New com.sun.star.lang.Locale
	Dim oFormats As Object
	Dim formatNum As Integer

	oDoc = IIf(IsMissing(doc), ThisComponent, doc)

	oFormats = oDoc.NumberFormats()

	'	If you choose to query on types, you need to use the type 
	'	com.sun.star.util.NumberFormat.DATE
	'	I could set the locale from values stored at
	'	http://www.ics.uci.edu/pub/ietf/http/related/iso639.txt 
	'	http://www.chemie.fu-berlin.de/diverse/doc/ISO_3166.html 
	'	I use a NULL locale and let it use what ever it likes.
	'	First, see if the number format exists

	If Not IsMissing(locale) Then aLocale = locale

	formatNum = oFormats.queryKey (sFormat, aLocale, TRUE)

'	MsgBox "Current Format number is" & formatNum		'	Debug only

	'	If the number format does not exist then add it

	If (formatNum = -1) Then
		formatNum = oFormats.addNew(sFormat, aLocale)

		If (formatNum = -1) Then formatNum = 0

'		MsgBox "new Format number is " & formatNum		'	Debug only
	End If

	FindCreateNumberFormatStyle = formatNum 
End Function
LibreOffice 5.1.3.2, Windows 8.1
Albireo
Offline
Last seen: 18 hours 38 min ago
Title:
Joined: 19 Apr 2016
Posts: 2
mark_t wrote:Check that you
mark_t wrote:
Check that you have LibreOffice set to the correct language, under Tools, Options, Language Settings, Languages. I’m not sure if language settings will change the numbers used by the Basis IDE, you might still need to use 2713.98 instead of 2713,98.
What do you mean by “correct language”? Today I have the following configuration .:
  • User Interface …: Standard – Swedish (Sweden)
  • Regional settings …: Standard – Swedish (Sweden)
  • Decimal character …: Same as regional settings
  • Standard currency ….: Standard – SEK
  • Default language for documents …: Standard – Swedish (Sweden)

mark_t wrote:
… I find it easier to read the code using the following format and let Basic handle the setValue. …
Thank you!
Information
My desire is to build a fully editable spreadsheet with external data from a CSV file as a basis. (Which makes it all a bit more complicated).

The worksheet will be created by running a standalone application. (AutoHotkey, Visual Basic, Python, Java, or ….). i.e. the solution will be translated from Basic to another language.
But first, I must handle / understand some basic things.

(Having already encountered other problems, that seem to work in OpenOffice and not work in LibreOffice Sad )

Now is this solved. It was the comma that is not interpreted as comma. (It was interpreted as punctuation in the other programming languages).

__________________________

Next step
The next step is to format the number to display the currency (like this – 1.234,45 kr or 1 234,45 kr)

Is “FindCreateNumberFormatStyle” a built-in feature in LibreOffice?

//Jan

mark_t
Offline
Last seen: 8 hours 17 min ago
Title: ★★★★
Joined: 26 Apr 2016
Posts: 90
It looks to me that your
It looks to me that your language and currency settings are correct for what you are trying to do. I was testing with English UK settings and I wasn’t sure if the basic IDE would handle the comma correctly as decimal separator, I guess that was your problem in the other programming languages.

If you are translating to another programming language then maybe using the “.setValue ( ) “ format is easier than “.Value = “.

FindCreateNumberFormatStyle is not a built in feature, but a very useful function published by Andrew Pitonyak in his book and examples on line. I re-use it with slight modification and attribute source to Andrew. Hopefully no license or IP issues with this.

You could possibly run the basic macro from a command line, I think I saw a post somewhere recently but don’t remember details.

You’ll probably find the format you want from the cell formats dialog and you can copy the format string from there into your macro, or use the examples in cell formats dialog to see how the string should be constructed.

If you are moving a large number of values to and from the worksheet you might also consider using “.getData()” and “.getData().setData( )” where you can read and write an array of cells.

LibreOffice 5.1.3.2, Windows 8.1
mark_t
Offline
Last seen: 8 hours 17 min ago
Title: ★★★★
Joined: 26 Apr 2016
Posts: 90
I think you can also set the
I think you can also set the number format for a range of cells for example:

oSheet.getCellRangeByPosition ( 1, 1, 1, iMaxRow + 1 ) .NumberFormat = iNumberFormat

LibreOffice 5.1.3.2, Windows 8.1

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.