CODEWITHSUNDEEP
javaapache-poi
Dave K
Dave K

Reputation: 1694

Basic Excel currency format with Apache POI

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

}

Upvotes: 36

Views: 72769

Answers (4)

deldev
deldev

Reputation: 1376

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

Upvotes: 4

Andrew
Andrew

Reputation: 770

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.

Upvotes: 27

user1073214
user1073214

Reputation: 303

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

Upvotes: 9

Dave K
Dave K

Reputation: 1694

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

Upvotes: 54

Related Questions