3

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

Community
  • 1
  • 1
gordon613
  • 2,770
  • 12
  • 52
  • 81
  • What if you try formatting it with an explicit dollar based format, rather than the built in "excel may localise" one? – Gagravarr Jul 15 '14 at 21:02
  • 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 Jul 16 '14 at 12:18

1 Answers1

4

This works (this is Gagravarr's suggestion)

CellStyle dollarStyle=wb.createCellStyle();
DataFormat df = wb.createDataFormat();
dollarStyle.setDataFormat(df.getFormat("$#,#0.00"));
gordon613
  • 2,770
  • 12
  • 52
  • 81
  • This is making the excel read only in some versions. How do we avoid that? – yaswanth May 03 '17 at 08:23
  • I have never encountered this! Do you mean the entire workbook is read-only, or just that particular cell where you applied the formatting? – gordon613 May 03 '17 at 12:01
  • What do you mean by "some versions"? Does the behaviour happen consistently? Is it possible that you have created too many cellStyles in the workbook? You may want to open a new question, as the code above does work for me and for others. – gordon613 May 03 '17 at 12:34
  • I am using excel on mac with version 15.33. It opens in read only mode for me. I haven't checked with other versions on other machines. I assumed if it worked for you and is not working for me, it must be an issue with only some of the versions. – yaswanth May 03 '17 at 12:43