Basic NumberFormat Codes

[RobertG]

Searching for the codes of NumberFormat, which could be set by Basic to get a right formatted cell, I won’t get any complete answer here. Have seen Basic Macro - Date NumberFormat , but there isn’t any example.
I have created a Writer-template and started the following code in a document, which is saved in same path as the template:

SUB HardcodedFormatCode 
	DIM oDB AS OBJECT
	DIM oNewDoc AS OBJECT
	DIM oTables AS OBJECT
	DIM oTable AS OBJECT
	DIM oRows AS OBJECT
	DIM stDir AS STRING
	DIM i AS INTEGER
	DIM k AS INTEGER
	DIM inCols AS INTEGER
	oDB = ThisComponent
	stDir = Left(oDB.Location,Len(oDB.Location)-Len(oDB.Title))
	stDir = stDir & "HardFormatCodes.ott"
	DIM args(0) AS NEW com.sun.star.beans.PropertyValue
	args(0).Name = "AsTemplate"
	args(0).Value = True
	oNewDoc = StarDesktop.loadComponentFromURL(stDir,"_blank",0,args)
	oTables = oNewDoc.getTextTables
	oTable = oTables.getByName("Printout")
	i = 1
	FOR k = 1 TO 120
		oTable.getCellByPosition(0,i).setValue(k)
		oTable.getCellByPosition(1,i).setValue(44992.25)	'2023-03-07 06:00:00
		oTable.getCellByPosition(1,i).NumberFormat = k
		oRows = oTable.getRows()
		oRows.insertByIndex(oRows.getCount(),1) 
		i = i + 1
	NEXT
	oRows.removeByIndex(oRows.getCount()-1,1) ' Last Row has to be deleted
END SUB

This will write all hard coded number formats (Starting with number 1, ending with number 120) into a Writer table.
This won’t show the special format which you get when opening dialog “Format Number”. So there is missing, for example, in German list code for currency together with €. Contains only DM, which has been gone since 2002.

I don’t know why, but when getting a NumberFormat together with locale, it will show the user defined formats also:

FUNCTION CellSetNumberFormat(stNumberFormat AS STRING, oDoc AS OBJECT) AS LONG
	DIM aLocale	AS NEW com.sun.star.lang.Locale
	DIM oNumberFormats AS OBJECT
	DIM loFormatKey	AS LONG
	oNumberFormats = oDoc.getNumberFormats()
	loFormatKey = oNumberFormats.queryKey(stNumberFormat, aLocale, FALSE)
	IF loFormatKey = -1 THEN loFormatKey = oNumberFormats.addNew(stNumberFormat, aLocale)
	CellSetNumberFormat = loFormatKey
End Function

With his function I could get the right number for getting my special currency symbol. I only looked at the code in dialog “Number Format”:

CellSetNumberFormat("#.##0,00 [$€-407];[ROT]-#.##0,00 [$€-407]", oNewDoc)

Hope this will help others to find the right number for NumberFormat. I’m using this for getting data from a Base file into a Writer document.
HardFormatCodes.ott (12.8 KB)

[mikekaganski]

How to create a Macro to insert the current date in the current language

My experiments show the following: StarBasic Format() run-time function accepts all the number format codes mentioned here. You may use the [$-langid] syntax to use the desired locale in the Format() output, like: sText = Format(Now(),"[$-41e]D MMMM YYYY") ' Thai The LangIDs might be taken from this page. This, however, needs the parts from @Lupp’s answer to get the current char locale to create complete solution. EDIT 2017-10-31 To go with field creation, you may use this code: …

@RobertG

This will write all hard coded number formats (Starting with number 1, ending with number 120)

Please do not hard-code any format number. Querying a format string, and adding when missing, is robust. Maybe @erAck can explain when/if it’s OK to use format code constants…

@RobertG

when getting a NumberFormat together with locale, it will show the user defined formats also

This is normal and correct. The registered number formats are locale-specific, so the query gives locale-specific results, too.

[erAck]

@How to create a Macro to insert the current date in the current language

The LangIDs might be taken from this page

Note that linked page is outdated from 2015 and covered only the languages their Speech API supported back then. Also, MS likes to move things around and not update existing pages. The one canonical page for some years now seems to be [MS-LCID]: Windows Language Code Identifier (LCID) Reference | Microsoft Learn .

@mikekaganski

Maybe @erAck can explain when/if it’s OK to use format code constants

It’s complicated… in Calc (!) the format key constants 0 to 61 represent predefined number formats of the current LibreOffice locale (system locale if not set differently) when the document was opened, but not tied to the locale (i.e. when saved and loaded in a different locale the representation is different, it’s always the current locale, except currencies). In Writer (I think) they represent the predefined number formats of the document’s locale. Same keys identify similar formats where possible (and locale data contributors followed the advises given), but default display formats may have different keys, as indicated by locale data. As noted, currency formats are generated when needed because they depend on the actual currency in use, and for some long existing locales the then actual currency is used in the format code and replaced if different for a new format. That information in turn is obtained from locale data again. Which is what the number formatter and dialog does when offering currency formats.
.
In Draw and Base I don’t know off-head whether 0…61 are of current locale or document locale. Anyhow, all keys >=62 are arbitrary per locale and may not even exist or have completely different meaning, may be defined additionally in locale data, or user-defined. If formats of more than one locale exist in one number formatter then the second locale (currently) starts at 10000 and is predefined until 10061, and so on.
.
So in general, if you don’t know exactly what you are doing then don’t rely on format keys.
.
This LibreOffice Developer's Guide: Chapter 6 - Office Development - The Document Foundation Wiki has some paragraphs and a code snippet on Number Formats.
Maybe also helpful are the locale data LC_FORMAT documentation and some code interna about internal number formats including newly added.
The XNumberFormats interface returned by XNumberFormatsSupplier:: getNumberFormats() covers all formats known by the document. The unset empty lang::Locale you use with queryKey() and addNew() btw denotes the current LibreOffice locale. The 407 of [$€-407] force a de-DE context for the currency but the . dot group and , comma decimal separators you use work only by chance because your current locale is de-DE (I assume). It may fail when run in a locale that uses different separators. (though some number formatter stuff tries hard to make such things work anyway…).
.
If you want to know what number formats a locale defines (not the document) then use interface XNumberFormatCode of service NumberFormatMapper. These do not include user-defined formats. Again, for currency formats it may include a legacy currency, so you’d have to consult locale data with XLocaleData::getAllCurrencies() (that interface btw also has XLocaleData::getAllFormats() to obtain raw number format data).

[sokol92]

@erAck , thanks for the fundamental clarification!