使用 Apache POI 在 Excel 中使用千位分隔符格式化数字 [英] Format number with thousands separator in Excel using Apache POI

问题描述

我想格式化一些数字单元格，用逗号作为千位分隔符.例如:

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

我有以下代码.我应该使用什么作为 formatStr?有没有简单的方法?或者我是否必须检测零的数量才能产生这样的#,###,###?

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.

更新

推荐答案

只要 #,### 或 #,##0 就足够了.Excel 将此解释为每三位数字有千位分隔符(不仅仅是最后三位之前，我推断这正是您所期望的).

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).

本着教人钓鱼的精神，您可以通过以下方式自行发现:

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

格式为数字，0 位小数，1000 分隔符:

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

单击确定"，然后重新打开数字格式对话框并转到自定义".看看格式代码(类型").它说 #,##0，对我来说，它给出的结果与 #,### 完全相同.

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 #,###.

这篇关于使用 Apache POI 在 Excel 中使用千位分隔符格式化数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！