Applying Number Formats

From Apache OpenOffice Wiki
Jump to: navigation, search

To format numeric values, an XNumberFormatsSupplier is attached to an instance of a, available at the global service manager. For this purpose, its main interface has a method attachNumberFormatsSupplier(). When the XNumberFormatsSupplier is attached, strings and numeric values are formatted using the methods of the NumberFormatter. To specify the format to apply, you have to get the unique index key for one of the formats defined in NumberFormats. These keys are available at the XNumberFormats and XNumberFormatTypes interface of NumberFormats.

Numbers in documents, such as in table cells, formulas, and text fields, are formatted by applying the format key to the NumberFormat property of the appropriate element.

NumberFormatter Service

The service implements the interfaces and


The interface converts numbers to strings, or strings to numbers, or detects a number format matching a given string.

  void attachNumberFormatsSupplier ( [in] com::sun::star::util::XNumberFormatsSupplier xSupplier )
  com::sun::star::util::XNumberFormatsSupplier getNumberFormatsSupplier ()
  long detectNumberFormat ( [in] long nKey, [in] string aString ) 
  double convertStringToNumber ( [in] long nKey, [in] string aString ) 
  string convertNumberToString ( [in] long nKey, [in] double fValue ); 
  com::sun::star::util::color queryColorForNumber ( [in] long nKey, [in] double fValue, 
                                               [in] com::sun::star::util::color aDefaultColor )
  string formatString ( [in] long nKey, [in] string aString ); 
  com::sun::star::util::color queryColorForString ( [in] long nKey, [in] string aString, 
                                               [in] com::sun::star::util::color aDefaultColor )
  string getInputString ( [in] long nKey, [in] double fValue )


  string convertNumberToPreviewString ( [in] string aFormat, [in] double fValue, 
                                 [in] com::sun::star::lang::Locale nLocale, 
                                 [in] boolean bAllowEnglish ) 
  com::sun::star::util::color queryPreviewColorForNumber ( [in] string aFormat, [in] double fValue, 
                                 [in] com::sun::star::lang::Locale nLocale, 
                                 [in] boolean bAllowEnglish, 
                                 [in] com::sun::star::util::color aDefaultColor )

This interface converts values to strings according to a given format code without inserting the format code into the underlying collection.

The example below demonstrates the usage of these interfaces.

  public void doSampleFunction() throws RuntimeException, Exception
      // Assume:
      // maSpreadsheetDoc;
      // maSheet;
      // Query the number formats supplier of the spreadsheet document xNumberFormatsSupplier =
, maSpreadsheetDoc );
      // Get the number formats from the supplier xNumberFormats =
      // Query the XNumberFormatTypes interface xNumberFormatTypes =
, xNumberFormats );
      // Get the number format index key of the default currency format,
      // note the empty locale for default locale aLocale = new;
      int nCurrencyKey = xNumberFormatTypes.getStandardFormat(
, aLocale );
      // Get cell range B3:B11 xCellRange =
          maSheet.getCellRangeByPosition( 1, 2, 1, 10 );
      // Query the property set of the cell range xCellProp =
, xCellRange );
      // Set number format to default currency
      xCellProp.setPropertyValue( "NumberFormat", new Integer(nCurrencyKey) );
      // Get cell B3 xCell = maSheet.getCellByPosition( 1, 2 );
      // Query the property set of the cell
      xCellProp = (
, xCell );
      // Get the number format index key of the cell's properties
      int nIndexKey = ((Integer) xCellProp.getPropertyValue( "NumberFormat" )).intValue();
      // Get the properties of the number format xProp = xNumberFormats.getByKey( nIndexKey );
      // Get the format code string of the number format's properties
      String aFormatCode = (String) xProp.getPropertyValue( "FormatString" );
      System.out.println( "FormatString: `" + aFormatCode + "'" );
      // Create an arbitrary format code
      aFormatCode = "\"wonderful \"" + aFormatCode;
      // Test if it is already present
      nIndexKey = xNumberFormats.queryKey( aFormatCode, aLocale, false );
      // If not, add to number formats collection
      if ( nIndexKey == -1 )
              nIndexKey = xNumberFormats.addNew( aFormatCode, aLocale );
          catch( ex )
              System.out.println( "Bad number format code: " + ex );
              nIndexKey = -1;
      // Set the new format at the cell
      if ( nIndexKey != -1 )
          xCellProp.setPropertyValue( "NumberFormat", new Integer(nIndexKey) );
Content on this page is licensed under the Public Documentation License (PDL).
Personal tools
In other languages