// you have to use the FormatSupplier interface to get the CellFormat enumeration
XCellFormatRangesSupplier xCellFormatSupplier = (XCellFormatRangesSupplier)
UnoRuntime.queryInterface(XCellFormatRangesSupplier.class, xSheet );
// getCellFormatRanges() has the interfaces for the enumeration
XEnumerationAccess xEnumerationAccess = (XEnumerationAccess)
UnoRuntime.queryInterface( XEnumerationAccess.class,
xCellFormatSupplier.getCellFormatRanges() );
XEnumeration xRanges = xEnumerationAccess.createEnumeration();
while( xRanges.hasMoreElements() ) {
// the enumeration returns a cellrange
XCellRange xCellRange = (XCellRange) UnoRuntime.queryInterface(
XCellRange.class, xRanges.nextElement());
// the PropertySet the get and set the properties from the cellrange
XPropertySet xCellProp = ( XPropertySet ) UnoRuntime.queryInterface(
XPropertySet.class, xCellRange );
// getPropertyValue returns an Object, you have to cast it to type that you need
Object oNumberObject = xCellProp.getPropertyValue( "NumberFormat" );
int iNumberFormat = ((Integer) oNumberObject).intValue();
// get the properties from the cellrange numberformat
XPropertySet xFormat = (XPropertySet) xNumberFormats.getByKey( iNumberFormat );
short fType = ((Short) xFormat.getPropertyValue("Type")).shortValue();
String sCurrencySymbol = ((String) xFormat.getPropertyValue("CurrencySymbol")).toString();
// change the numberformat only on cellranges with a currency numberformat
if( ( (fType & com.sun.star.util.NumberFormat.CURRENCY) > 0) &&
( sCurrencySymbol.compareTo( sOldSymbol ) == 0 ) ) {
AnyConverter aAnyConv = new AnyConverter();
boolean bThousandSep = aAnyConv.toBoolean(
xFormat.getPropertyValue("ThousandsSeparator"));
boolean bNegativeRed = aAnyConv.toBoolean(xFormat.getPropertyValue("NegativeRed"));
short fDecimals = aAnyConv.toShort(xFormat.getPropertyValue("Decimals"));
short fLeadingZeros = aAnyConv.toShort(xFormat.getPropertyValue("LeadingZeros"));
Locale oLocale = (Locale) aAnyConv.toObject(
new com.sun.star.uno.Type(Locale.class),xFormat.getPropertyValue("Locale"));
// create a new numberformat string
String sNew = xNumberFormats.generateFormat( iSimpleKey, oLocale, bThousandSep, bNegativeRed, fDecimals, fLeadingZeros );
// get the NumberKey from the numberformat
int iNewNumberFormat = NumberFormat( xNumberFormats, sNew, oLocale );
// set the new numberformat to the cellrange DM->EUR
xCellProp.setPropertyValue( "NumberFormat", new Integer( iNewNumberFormat ) );
// interate over all cells from the cellrange with an content and use the DM/EUR factor
XCellRangesQuery xCellRangesQuery = (XCellRangesQuery) UnoRuntime.queryInterface(
com.sun.star.sheet.XCellRangesQuery.class, xCellRange );
XSheetCellRanges xSheetCellRanges = xCellRangesQuery.queryContentCells(
(short) com.sun.star.sheet.CellFlags.VALUE );
if( xSheetCellRanges.getCount() > 0 ) {
XEnumerationAccess xCellEnumerationAccess = xSheetCellRanges.getCells();
XEnumeration xCellEnumeration = xCellEnumerationAccess.createEnumeration();
while( xCellEnumeration.hasMoreElements() ) {
XCell xCell = (XCell) UnoRuntime.queryInterface(
XCell.class, xCellEnumeration.nextElement());
xCell.setValue( (double) xCell.getValue() / fFactor );