Formatting Excel cell as Dollar with Apache POI where the machine currency is not dollar

3
votes

I am using Java and Apache POI to format an Excel worksheet.

I want to format a cell as dollars.

Using the suggestion in Basic Excel currency format with Apache POI, what I get is that the cell is formatted as shekels (which happens to be the default currency of my machine)

How do I format the cell as dollars, even if the default currency of my machine is not dollars.

The code I am using is as follows

CellStyle dollarStyle=wb.createCellStyle();
dollarStyle.setDataFormat(7);
CellUtil.getCell(myRow, 1).setCellStyle(dollarStyle);

The number 7 comes from http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/BuiltinFormats.html

1
javaexcelapache-poi
What if you try formatting it with an explicit dollar based format, rather than the built in "excel may localise" one? – Gagravarr
Thank you. If you want to repost as an answer than I will accept it, otherwise I posted the code below as an answer... – gordon613

1 Answers

4
votes

This works (this is Gagravarr's suggestion)

CellStyle dollarStyle=wb.createCellStyle();
DataFormat df = wb.createDataFormat();
dollarStyle.setDataFormat(df.getFormat("$#,#0.00"));