Java 在 Apache Poi 3.7 中以特定格式在数字单元格中写入 Double 值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9789627/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Write Double value in numeric cell with specific format in Apache Poi 3.7
提问by Dani
I need to write a Double value in a numeric cell using a specific format, i mean, the generated xls must have numeric cells containing Double values like, for example: 8,1. I am trying something like:
我需要使用特定格式在数字单元格中写入一个 Double 值，我的意思是，生成的 xls 必须包含包含 Double 值的数字单元格，例如：8,1。我正在尝试类似的东西：
DecimalFormat dFormat = new DecimalFormat("##.#");
dFormat.format(doubleValue);
But, since format method returns a String, no matter whether I create cells as numeric or not, they always behave as text cells. I was thinking about two options:
但是，由于 format 方法返回一个字符串，无论我是否将单元格创建为数字，它们总是表现为文本单元格。我在考虑两种选择：
- Forcing cells to behave as numeric cells.
- Forgetting about DecimalFormat and use Double class specifying comma as the decimal separator, what i'm not sure it's possible.
- 强制单元格表现为数字单元格。
- 忘记 DecimalFormat 并使用 Double 类指定逗号作为小数点分隔符，我不确定这是否可能。
Any idea?
任何的想法？
采纳答案by Gagravarr
I may be missing something, but I think you just want to style the cell with a format rule to display a single decimal place
我可能遗漏了一些东西，但我认为您只想使用格式规则设置单元格样式以显示单个小数位
// Do this only once per file
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setDataFormat(
wb.getCreationHelper().createDataFormat().getFormat("#.#"));
// Create the cell
Cell c = row.createCell(2);
c.setCellValue(8.1);
c.setCellStyle(cellStyle);
That will create a formatting rule, create a cell, set the cell to be the value 8.1, then style it
这将创建一个格式规则，创建一个单元格，将单元格设置为值 8.1，然后设置样式
回答by Ben Strombeck
Do you need to put both values in one cell? Can you split them up into 2 columns? Excel has a built-in "Texts to Columns" function that you should be able to reference that will split an entire column of text strings into multiple columns based on a delimiter (like a comma). In VBA, it looks something like this:
您是否需要将两个值都放在一个单元格中？你能把它们分成两列吗？Excel 有一个内置的“文本到列”函数，您应该能够引用该函数，该函数将基于分隔符（如逗号）将一整列文本字符串拆分为多列。在 VBA 中，它看起来像这样：
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
As for your first proposed solution, it is not possible to force Excel to treat something as a number if it is not convertable to a number.
至于您提出的第一个解决方案，如果某些内容不可转换为数字，则无法强制 Excel 将其视为数字。
回答by Santosh MV
I tried this working fine...
我试过这个工作正常......
HSSFCellStyle cellStyle = wb.createCellStyle();
HSSFDataFormat hssfDataFormat = wb.createDataFormat();
String cellVal = "2500";
cellStyle.setDataFormat(hssfDataFormat.getFormat("#,##0.000"));
newCell.setCellStyle(cellStyle);
newCell.setCellValue(new Double(cellVal));
newCell.setCellType(Cell.CELL_TYPE_NUMERIC);
The output is a numeric value with desired format: 2,500.000
输出是具有所需格式的数值：2,500.000
