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

问题描述

我想格式化一些数字单元格，逗号为千位分隔符。例如：

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

我有以下代码。我应该如何使用 formatStr ？有没有简单的方法？或者我必须检测零的数量以产生这样的东西＃，###，### ？

  String formatStr =; 
 HSSFCellStyle style = workbook.createCellStyle（）; 
 HSSFDataFormat format = workbook.createDataFormat（）; 
 style.setDataFormat（format.getFormat（formatStr））; 
 cell.setCellStyle（style）; 
 cell.setCellType（HSSFCell.CELL_TYPE_NUMERIC）; 
  

请记住，我正在处理数字。单元格类型将是数字，而不是字符串。

更新

解决方案

只需＃，### 或＃，## 0 就足够了。 Excel将这个解释为每三位数千个分隔符（不仅仅是在最后三个之前，我推断是你期望的）。

为了教一名男子钓鱼，如何找出自己：

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

单击确定，然后重新 - 打开数字格式对话框，然后转到自定义。看看格式化代码（Type）。它说＃，## 0 ，对我来说，与＃，### 完全相同的结果。 / p>

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

解决方案

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:

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

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屋！