Java 带有 Apache POI 的基本 Excel 货币格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/319438/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Basic Excel currency format with Apache POI
提问by Dave K
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.
我能够将单元格格式化为日期，但我一直无法将单元格格式化为货币......有人有如何创建样式以使其工作的示例吗？我下面的代码显示了我正在创建的样式...... 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"));
}
采纳答案by Dave K
After digging through the documentation a bit more, I found the answer:
在深入研究文档后，我找到了答案：
http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFDataFormat.html
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
回答by user1073214
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
原价为 416.00 美元
回答by Andrew
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 出现问题。请注意，您在上面创建的新格式最终会出现在第二步的自定义对话框中。
回答by dellasavia
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"));
