13

I want to format some number cells, with a comma as thousands separator. For example:

12        -> 12
1200      -> 1,200
12000     -> 12,000
12000000  -> 12,000,000
120000000 -> 120,000,000

I have the following code. What should I use as formatStr? Is there an easy way? Or do I have to detect the number of zeros in order to produce something like this #,###,###?

String formatStr = "";
HSSFCellStyle style = workbook.createCellStyle();
HSSFDataFormat format = workbook.createDataFormat();
style.setDataFormat(format.getFormat(formatStr));
cell.setCellStyle(style);
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);

Keep in mind that I'm dealing with numbers. The cell type will be numeric, not string.

Update

enter image description here

Jean-François Corbett
  • 37,420
  • 30
  • 139
  • 188
Alkis Kalogeris
  • 17,044
  • 15
  • 59
  • 113

2 Answers2

11

Just add this:

style.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));

and it will follow the format you want.

danh
  • 62,181
  • 10
  • 95
  • 136
Ian
  • 391
  • 1
  • 17
8

Just #,### or #,##0 should be sufficient. Excel interprets this as having thousands separators every three digits (not just before the last three, which I infer is what you were expecting).

enter image description here

In the spirit of teaching a man to fish, this is how you can find out for yourself:

Format as Number, 0 decimal places, with 1000 separator:

enter image description here

Click OK, then re-open the number format dialog and go to Custom. Have a look at the formatting code ("Type"). It says #,##0, which for me gives the exact same result as #,###.

enter image description here

Jean-François Corbett
  • 37,420
  • 30
  • 139
  • 188
  • That's exactly what I want, but when I try this in excel I get `1200,`. Even when I tried your number example the same behavior remains (which is odd). I'm going to try it in my java code and come back. Please check my update for a screenshot (I might be doing something wrong). Just upvoted for now. – Alkis Kalogeris Dec 10 '14 at 12:51
  • Weird. Excel regional settings?... Have a look at my expanded answer. Does this get you any closer? – Jean-François Corbett Dec 10 '14 at 13:02
  • Ok now I understand. The second part solved it for me, since I search with the "thousand seperator" keywords and this came up http://stackoverflow.com/questions/14876548/apache-poi-thousand-separator . Accepted of course. Please don't think I'm lazy, I searched for an existing answer but not with the correct keywords apparently. – Alkis Kalogeris Dec 10 '14 at 13:09