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

问题描述

我想用Java读取Excel中的数据，Excel中单元格的数据有两种类型是NUMERIC和STRING.所以当我想读取数据为 NUMERIC 时，它只显示数字 101125340004，而不是这样的 1.01E+11 因为它是一个电话属性.我的代码正在运行，但对于某些值 (1%) 仍然显示浮点数，但它们实际上是整数，我不知道为什么.

Excel 365 工作表中的数据

DataFormatter fmt = new DataFormatter();如果 (cellType.toString().equals("NUMERIC"))companyTel = fmt.formatCellValue(currentRow.getCell(8));别的companyTel = currentRow.getCell(8).toString();

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

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

请告诉我上面的代码有什么问题或者我如何解决这个问题?谢谢大家.

解决方案

Microsoft Excel 使用单元格数字格式 General 时，以科学记数法转换超过 11 位数字的值.所以 842223622111 导致 8,42224E+11 在 Excel 单元格中具有数字格式 General.如果在 Excel 中单元格应显示 842223622111，则需要特殊的数字格式 (0) 而不是 General.

您可以阅读Excel for Office 365 中的可用数字格式 以及大数字(12 位或更多位)的 General 格式.

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

现在，如果 apache poi 得到一个包含 842223622111 并具有数字格式 General 的单元格，那么 DataFormatter 将像 Excel 这样的格式也可以.

如果您希望 DataFormatter 的格式更像 Libreoffice/OpenOffice Calc，那么您可以这样做:

<预><代码>...DataFormatter fmt = new DataFormatter();...fmt.addFormat("General", new java.text.DecimalFormat("#.###############"));...

使用这种方法，无需更改 Excel 工作表.但当然 apache poi 的 DataFormatter 的默认行为已更改.为避免这种情况，您可以使用数字格式 0 在 Excel 中格式化受影响的单元格，即 Number 没有千位分隔符和小数位数= 0.

但由于您提到此列包含电话号码，最常见的解决方案是使用数字格式 Text (@) 格式化整个列.这将单元格的内容视为文本并完全按照您键入的内容显示内容，即使您键入数字也是如此."因此，应用该格式后，科学记数法不会再发生任何变化.

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.

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

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 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 will not do so. There 842223622111 stays 842223622111 in cells having number format General.

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

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("#.###############"));
...

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.

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