[Solved] Custom Number Format

[MEllis2]

I want to set a User-Defined Format Code on a cell using the uno api.

I found the Property Value "NumberFormatValue", but nothing seems to reference what interface this is pulled from. XCell? XCellAddressable?

I generated the following extremely unhelpful Macro

Code: Select all

dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "NumberFormatValue"
args2(0).Value = 116

dispatcher.executeDispatch(document, ".uno:NumberFormatValue", "", 0, args2())

but this leaves me with more questions. The format code I entered was 0.0" M", why is it setting the format to 116?
Where is my format code stored?
What object's propertySet did the PropertyValue "NumberFormatValue" come from?

The code I would like to end up with should look something like

Code: Select all

PropertyValue[] xps = new PropertyValue[1];
xps[0] = new PropertyValue();
xps[0].Name = "NumberFormatValue";
xps[0].Value = new uno.Any("0.0\" M\"");

<some currently unknown interface or object>.Set<some currently unknown function>(xps);

If someone could fill in the current unknowns, that would be amazing.

[FJCC]

I happen to have some Basic code for setting a number format. I hope you can use it and the API reference to find the interfaces you need.

Code: Select all

Dim sLocale as New com.sun.star.lang.Locale
FormatString = "0.0"" M"""
oDoc = ThisComponent
NumForms = oDoc.getNumberFormats()
DateKey = NumForms.queryKey(FormatString, sLocale, True)
IF DateKey = -1 Then  '-1 means no value found for the requested format
	DateKey = NumForms.addNew(FormatString, sLocale) 'create a new key
end if
Sheet = oDoc.Sheets.getByIndex(0) 
Cell = Sheet.getCellByPosition(0,0)
Cell.NumberFormat = DateKey
Cell.Value = 40002

[MEllis2]

Very, VERY close. I didnt realize you needed to create it in the formats list first, in order to get the key.

Here's what I ended up with

Code: Select all

XNumberFormatsSupplier XNumFSupply = (XNumberFormatsSupplier)Doc;
XNumberFormats xnf = XNumFSupply.getNumberFormats();
//Add new format//Get Key of new format
int Key = xnf.addNew(format, new Locale("en", "us", ""));
//Set "NumberFormatValue" property of XCellRange propertySet
XCellRange xcr = ooSheet.getCellRangeByName(String.Concat(cell, ":", cell));
((XPropertySet)xcr).setPropertyValue("NumberFormat", new uno.Any(Key));

I make the assumption that you need a new one every time...i guess the better way to code it would be to check and see if its already there first. Thanks for that =D

Also good to know, even though the basic code uses "NumberFormatValue" for the Property Key, the actual Property is "NumberFormat".

[Charlie Young]

The relevant interfaces are

com.sun.star.util.XNumberFormatsSupplier
com.sun.star.util.XNumberFormats

and the Struct

com.sun.star.lang.Locale

The formats are stored in the document, and the reason you are getting 116 is because they are referenced by a long.

I have a Basic function that gets a format number from a string

Code: Select all

Function getformat(f As String) As Long
   Dim oDoc As Object
   Dim NumberFormats As Object
   Dim Loc as New com.sun.star.lang.Locale
   Dim formatID As Long
      
   oDoc = ThisComponent
         
   Loc.Language = "en"
   Loc.Country = "US"
   
   NumberFormats = oDoc.NumberFormats
   
   formatId = NumberFormats.queryKey(f, Loc, False)
   If formatId = -1 Then
      formatId = NumberFormats.addNew(f, Loc)
   End If
   
   getformat  = formatID
   
End Function

I also have that one in c++

Code: Select all

long getformat(Reference< XSpreadsheetDocument > rSheetDoc, char *f)
{
	Reference< XNumberFormatsSupplier > xNumberFormatsSupplier(rSheetDoc, UNO_QUERY);
	Reference< XNumberFormats > xNumberFormats = xNumberFormatsSupplier->getNumberFormats();
   
	Locale Loc;
	long formatId;
          
	Loc.Language = OUString::createFromAscii("en");
	Loc.Country = OUString::createFromAscii("US");
   
   formatId = xNumberFormats->queryKey(OUString::createFromAscii(f), Loc, 0);
   if(formatId == -1)
      formatId = xNumberFormats->addNew(OUString::createFromAscii(f), Loc);
      
   return formatId;
}

I don't do much java, but hopefully FJCC's and my examples can get you on the right track.

Edit: Guess I was a bit slow again.