在Apache Poi 3.7中以特定格式在数值单元中写入Double值

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，使用双类指定逗号作为十进制分隔符，我不确定这是否可能。

Any idea?

任何想法?

3 个解决方案

#1

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，然后对其进行样式化

#2

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

#3

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将其视为一个数字。

#1