Basic Excel currency format with Apache POI

Question

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.

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

}

It'll be cool if you can accept your own answer sometime. ;) — Buhake Sindi, Jun 30 '11 at 11:37

Dave K · Accepted Answer · 2009-12-14T23:48:30.747

53

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

edited Dec 14 '09 at 23:48

answered Nov 26 '08 at 06:33

Dave K

1,674
4
16
22

4

The list of built in formats is here: https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/BuiltinFormats.html – bradvido Dec 08 '14 at 21:28
The output looks nice. Pardon me, but if I click on 'Format Cells' in Excel, I just see it falls in the category 'Custom'. Now how can I make it to fall into the category 'Currency'? – Just a HK developer Jul 26 '17 at 04:52
3

The built-in format table is useful... but who refers to hard-coded numerical values these days? Would it hurt to map POI_FORMAT_CURRENCY=8, etc? – Slawomir Oct 29 '17 at 02:42

score 27 · Answer 2 · answered Dec 03 '14 at 17:26

27

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

enter image description here

Then go back into the format dialogue. Select custom.

enter image description here

Copy paste the text it has on the top row under 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.

answered Dec 03 '14 at 17:26

Andrew

761
7
9

2

Great! This is a life saver. I am able to get the Currency correct. That means if I format the cell using this method, when I open the doc in Excel, click on the cell, right click to get 'Format cells', I am able to get the 'Currency' under the format category! Thanks Thanks Thanks – Just a HK developer Jul 26 '17 at 04:55
superb. Thank you. – java-addict301 Sep 13 '18 at 18:11

score 8 · Answer 3 · answered Mar 28 '12 at 12:33

8

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

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

O/P is $416.00

answered Mar 28 '12 at 12:33

user1073214

173
2
10

deldev · Answer 4 · 2015-02-09T16:47:46.947

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

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

edited Feb 09 '15 at 16:47

answered Feb 09 '15 at 16:37

deldev

1,296
18
27

Basic Excel currency format with Apache POI

4 Answers4

Linked

Related