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
– Buhake Sindi, Commented Jun 30, 2011 at 11:37

Dave K · Accepted Answer · 2009-12-14 23:48:30Z

54

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, 2009 at 23:48

answered Nov 26, 2008 at 6:33

Dave K

1,7044 gold badges16 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

bradvido Over a year ago

The list of built in formats is here: poi.apache.org/apidocs/org/apache/poi/ss/usermodel/…

Just a HK developer Over a year ago

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'?

Slawomir Over a year ago

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?

tschumann Feb 20 at 0:59

@Slawomir agreed - I can index by a number of by the format string itself - neither is particularly helpful.

Andrew · Accepted Answer · 2014-12-03 17:26:07Z

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 3, 2014 at 17:26

Andrew

7697 silver badges9 bronze badges

1 Comment

Just a HK developer Over a year ago

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

user1073214 · Accepted Answer · 2012-03-28 12:33:15Z

9

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, 2012 at 12:33

user1073214

3033 silver badges11 bronze badges

Comments

deldev · Accepted Answer · 2015-02-09 16:47:46Z

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 9, 2015 at 16:47

answered Feb 9, 2015 at 16:37

deldev

1,39620 silver badges28 bronze badges

Collectives™ on Stack Overflow

Basic Excel currency format with Apache POI

4 Answers 4

4 Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related