Get currency applied to an Excel Cell using POI

Question

I have written a Java program to read an Excel file cell-by-cell using POI API. The setting of the currency is done explicitly using the MS Excel - "Format Cell" option.

However, is there any way in which I can recognize the currency that has been applied to the cell using POI?

So far, I have tried using getCellStyle().getDataFormat() and it returns a unique number. However, the problem is that it is based on the cell style, and can change with a change in the cell formatting(font, size, etc.).

I tried this:

if(Cell.CELL_TYPE_NUMERIC)
{
  System.out.println("Value in Excel cell:: " + cell.getNumericCellValue());
  short styleType = cell.getCellStyle().getDataFormat();
  System.out.println("style (currency):: " + styleType);

  if(styleType == <Some unique number>)  //want this unique number
  {
    System.out.println("Currency applied to this cell:: " + <Currency name>);
  }
}

So, either I get the unique number which identifies the currency name or if I get the currency name directly, then that would be the best.

vzamanillo vzamanillo · Accepted Answer · 2014-03-24T09:39:43

You can identify the built in format by id looking at org.apache.poi.ss.usermodel.BuiltinFormats class, or get the format string using the same class

 String dataFormatString =
 BuiltinFormats.getBuiltinFormat(cell.getCellStyle().getDataFormat());

or you can get the currency format using the getDataFormatString() method from the CellStyle

String dataFormatString = cell.getCellStyle().getDataFormatString();

Update:

Looking at HSSFDataFormat class

International Formats

Since version 2003 Excel has supported international formats. These are denoted with a prefix "[$-xxx]" (where xxx is a 1-7 digit hexadecimal number). See the Microsoft article Creating international number formats for more details on these codes.

For currency format the prefix really is [$c-xxx] where c is the currency symbol and xxx is the hexadecimal value of the Locale Identifier (LCID) for the appropriate language and location, so you can parse the data format string to get the currency.

An example, for currency "EUR € Spanish (Spain)" the data format string is #.##0 [$€-C0A];-#.##0 [$€-C0A] so you can translate [$€-C0A] in "Currency EUR - Spanish", for "USD $ English (EE. UU.)" the data format string is [$$-409]#.##0;-[$$-409]#.##0 so you can translate [$$-409] in "Currency USD - English (U.S.)"... etc

Hope this helps.

Get currency applied to an Excel Cell using POI

1 Answers