如何在DataFormatter中编辑模式? [英] How do I edit pattern in DataFormatter?

问题描述

我想用Java读取Excel中的数据，Excel中的单元格数据有两种类型:NUMERIC和STRING.因此，当我想将数据读取为NUMERIC时，它仅显示数字101125340004，而不是此1.01E+11，因为它是电话属性.我的代码可以正常工作，但是对于某些值(1％)仍显示浮点数，但它们实际上是整数，我不知道为什么.

I want to read data in Excel with Java, and data of cell in Excel got 2 types is NUMERIC and STRING. So when I want to read the data as a NUMERIC, it only displays numbers 101125340004, not like this 1.01E+11 because it is a telephone attribute. My code is working, but for some values (1%) still display floating-point number but they are actually integers, and I don't know why.

Excel 365工作表中的数据

Data in sheet of Excel 365

DataFormatter fmt = new DataFormatter();
if (cellType.toString().equals("NUMERIC"))
    companyTel = fmt.formatCellValue(currentRow.getCell(8));
else
    companyTel = currentRow.getCell(8).toString();  

输出仍然在数据库中获得浮点数

Output still got floating-point number in database

我正在使用的Java Apache POI版本是3.17.

Version of Java Apache POI I'm using is 3.17.

请告诉我上面代码中的错误或如何解决该问题?谢谢大家.

Please tell me what wrong in my code above or how do I solve the problem? Thank you all.

推荐答案

Microsoft Excel当使用单元格编号格式General时，将转换具有科学计数形式的11位以上的值.因此，842223622111在具有数字格式General的Excel单元格中导致8,42224E+11.如果在Excel中该单元格应显示842223622111，则需要一种特殊的数字格式(0)，而不是General.

Microsoft Excel converts values having more than 11 digits in scientific notation when cell number format General is used. So 842223622111 leads to 8,42224E+11 in Excel cells having number format General. If in Excel the cell shall show 842223622111, then a special number format (0) is needed instead of General.

您可以阅读有关

You can read about available number formats in Excel for Office 365 and the special behavior of the General format for large numbers (12 or more digits).

Libreoffice/OpenOffice Calc不会这样做.在数字格式为General的单元格中，842223622111保持为842223622111.

Libreoffice/OpenOffice Calc will not do so. There 842223622111 stays 842223622111 in cells having number format General.

现在，如果apache poi获取一个包含842223622111且数字格式为General的单元格，则DataFormatter会像Excel一样格式化该格式.

Now, if apache poi gets a cell containing 842223622111 and having number format General, then DataFormatter will format this like Excel would also do.

如果您希望DataFormatter应该像Libreoffice/OpenOffice Calc那样设置格式，则可以执行以下操作:

If you wants DataFormatter should format this more like Libreoffice/OpenOffice Calc, then you could do:

...
DataFormatter fmt = new DataFormatter();
...
fmt.addFormat("General", new java.text.DecimalFormat("#.###############"));
...

使用这种方法，无需在Excel工作表中进行任何更改.但是当然可以更改apache poi的DataFormatter的默认行为.为避免这种情况，您可以使用数字格式0在Excel中将受影响的单元格格式化，该格式为Number，不带千位分隔符，小数点后十进制位数= 0.

Using this approach, no changes in the Excel sheet are necessary. But of course the default behavior of apache poi's DataFormatter is changed. To avoid this, you could format the affected cells in Excel using number format 0, which is Number without thousands separator and number of decimal decimal places = 0.

但是由于您提到此列包含电话号码，所以最常见的解决方案是使用数字格式Text(@)格式化整个列.这将单元格的内容作为文本进行处理，并且即使键入数字，也完全按照您键入的内容显示内容".因此，在应用格式之后，就不会再更改为科学计数法了.

But since you mentioned that this column contains telephone numbers, the most common solution would be formatting the whole column using number format Text (@) . This "treats the content of a cell as text and displays the content exactly as you type it, even when you type numbers." So after that formatting was applied nothing will change to scientific notation any more.

这篇关于如何在DataFormatter中编辑模式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！