Thousands separator format number in Excel using Apache POI

Question

I want to format some cells with a comma as a thousands separator. For instance:

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

I have the following code. What should be used as formatStr ? Is there an easy way? Or do I need to determine the number of zeros in order to create 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 am dealing with numbers. The cell type will be numeric, not string.

Update

Answer 1

Just #,### or #,##0 should be enough. Excel interprets this as having thousands of separators every three digits (not only until the last three, and I conclude what you expect).

In the spirit of teaching a person to fish, here's how you can find out:

Format in the form of a number, 0 decimal places, with a separator 1000:

Click OK, then open the number format dialog box and go to Custom. Look at the formatting code ("Type"). He says #,##0 , which for me gives the same result as #,### .

Answer 2

Just add this:

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

and it will match the format you want. You can send this link to get more format settings.