使用POI获取适用于Excel单元格的货币 [英] Get currency applied to an Excel Cell using POI

问题描述

我已经编写了一个Java程序来使用POI API逐单元读取Excel文件.货币的设置是使用MS Excel-设置单元格格式"选项明确完成的.

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.

但是，有什么方法可以识别使用POI应用于单元格的货币?

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

到目前为止，我已经尝试使用getCellStyle().getDataFormat()并返回一个唯一的数字.但是，问题在于它基于单元格样式，并且可以随单元格格式(字体，大小等)的变化而变化.

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.).

我尝试过:

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.

推荐答案

您可以通过id来查看

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());

或者您可以从 CellStyle

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

更新:

查看 HSSFDataFormat 类

国际格式

自2003版以来，Excel已支持国际格式.这些以前缀"[$ -xxx]"表示(其中xxx是1-7位数字十六进制数).请参阅Microsoft文章创建国际号码格式以了解更多详细信息在这些代码上.

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.

对于货币格式，前缀实际上是 [$ c-xxx] ，其中 c 是货币符号，而 xxx 是的十六进制值适当的语言和位置的区域设置标识符(LCID)，因此您可以解析数据格式字符串以获取币种.

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.

例如，对于货币"EUR€Spanish(Spain)" ，数据格式字符串为#.## 0 [$€-C0A];-#.## 0 [$€-C0A] ，因此您可以将 [$€-C0A] 转换为"Currency EUR-Spanish" ，换成"USD $英文(EE.UU.)"的数据格式字符串为 [$$-409]#.## 0;-[$$-409]#.## 0 ，因此您可以翻译 [$$-409] in "Currency USD-English(US)" ... etc

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

希望这会有所帮助.

这篇关于使用POI获取适用于Excel单元格的货币的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！