Apache POI的基本Excel货币格式

I'm able to get cells to format as Dates, but I've been unable to get cells to format as currency... Anyone have an example of how to create a style to get this to work? My code below show the styles I'm creating... the styleDateFormat works like a champ while styleCurrencyFormat has no affect on the cell.

我能够将单元格格式化为Dates,但我无法将单元格格式化为货币......任何人都有一个如何创建样式以使其工作的示例?我下面的代码显示了我正在创建的样式... styleDateFormat就像一个冠军,而styleCurrencyFormat对单元格没有影响。

private HSSFWorkbook wb;
private HSSFCellStyle styleDateFormat = null;
private HSSFCellStyle styleCurrencyFormat = null;

......

public CouponicsReportBean(){
    wb = new HSSFWorkbook();
    InitializeFonts();

}

public void InitializeFonts()
{
    styleDateFormat = wb.createCellStyle();
    styleDateFormat.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));


    styleCurrencyFormat = wb.createCellStyle();
    styleCurrencyFormat.setDataFormat(HSSFDataFormat.getBuiltinFormat("$#,##0.00"));

}

4 个解决方案

#1

After digging through the documentation a bit more, I found the answer:

在仔细阅读文档之后,我找到了答案:

http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFDataFormat.html

Just need to find an appropriate pre-set format and supply the code.

只需找到合适的预设格式并提供代码即可。

    styleCurrencyFormat.setDataFormat((short)8); //8 = "($#,##0.00_);[Red]($#,##0.00)"

Here are more examples: http://www.roseindia.net/java/poi/setDataFormat.shtml

以下是更多示例:http://www.roseindia.net/java/poi/setDataFormat.shtml

#2

For at least Excel 2010: Go into Excel. Format a cell they way you want it.

至少对于Excel 2010:进入Excel。按照您希望的方式格式化单元格。

Then go back into the format dialogue. Select custom.

然后回到格式对话框。选择自定义。

Copy paste the text it has on the top row under Type: into

将其所拥有的文本复制粘贴到Type:into下的顶行

createHelper.createDataFormat().getFormat("<here>");

Example:

createHelper.createDataFormat().getFormat("_($* #,##0.00_);_($* (#,##0.00);_($* \"-\"??_);_(@_)"); //is the "Accounting" format.

Make sure you set your populate your cells by using a double. Using the index may cause problems with different versions of Excel. Note that the new format you create above ends up in the custom dialogue from step two.

确保使用double设置填充单元格。使用索引可能会导致不同版本的Excel出现问题。请注意,您在上面创建的新格式最终会出现在第二步的自定义对话框中。

#3

Just an update to above reply. short '8' doesn't work for me but the '7' does.

只是对上述回复的更新。短'8'对我不起作用,但'7'不起作用。

cell.setCellValue(416.17);      
cellStyle.setDataFormat((short)7);
cell.setCellStyle(cellStyle);

O/P is $416.00

O / P是416.00美元

#4

You can try this code to format your cell with currency mode (with thowsand separator like used in Brazil or Germany. Eg. 12.345,67):

您可以尝试使用此代码以货币模式格式化您的单元格(使用像巴西或德国一样的thowsand分隔符。例如12.345,67):

HSSFCellStyle cell = yourWorkBook.createCellStyle();
CreationHelper ch = yourWorkBook.getCreationHelper();
cell.setDataFormat(ch.createDataFormat().getFormat("#,##0.00;\\-#,##0.00"));