Apache POI:以字符串形式获取数字，如excel中所示 [英] Apache POI : getting a number as a string, as displayed in excel

问题描述

我正在尝试使用 POI 解析 excel 工作簿.在工作表中，我将一些数字显示为:

I'm trying to parse an excel workbook with POI. In a sheet, i have some numbers displayed as :

06 85 85 65 45

06 85 85 65 45

(这是一个法国电话号码，它总是以 0 开头，每 2 位有一个空格).

(It is a french phone number, it always starts with a 0 and there is whitespaces every 2 digits).

我需要从 excel 单元格中获取一个带有零和空格的字符串.

My need is to get a string from the excel Cell with the zero and the spaces.

以下是我调查的一些内容:

Here are some elements from my investigations :

double doubleValue = cell.getNumericCellValue(); // worth 6.85856545E8
String dataStringFormat = cell.getCellStyle().getDataStringFormat(); // worth 0#" "##" "##" "##" "##

我想有一种方法可以使用 POI API 将dataStringFormat"应用于doubleValue"以获得类似 06 85 85 65 45 的东西.

I guess that there is a way with POI API to apply the "dataStringFormat" to the "doubleValue" to get somthing like 06 85 85 65 45.

有人知道我该怎么做吗?

Anyone has a idea about how I can do that ?

非常感谢.

Gagravarr让我上路.他的

Gagravarr put me on the way. His

DataFormatter fmt = new DataFormatter();
String phoneNumber = fmt.formatCellValue(cell);

仍然返回 685856545 但我感谢他，我找到了 CellNumberFormatter 类.并使用以下代码获取我的 06 85 85 65 45 :

still returned 685856545 but I thanks to him, I found the CellNumberFormatter class. And get my 06 85 85 65 45 with the followin code :

String stringFormat = cell.getCellStyle().getDataFormatString();
CellNumberFormatter fmt = new CellNumberFormatter(stringFormat);
String phoneNumber = fmt.format(cell.getNumericCellValue()); // = 06 85 85 65 45

我尝试了其他格式的技巧，但仍然存在一些问题.- 123 3/25 变成 123 ??/??(不支持分数格式?)- 1,23E+09 变成 1,23.1,E+09

I try the trick with other formats and there are some problems still. - 123 3/25 becomes 123 ??/?? (fraction format not supported ?) - 1,23E+09 becomes 1,23.1,E+09

有些是因为语言环境:- 123 456 789 变成 123,456,789(千千万万的分隔符我用法语''而不是',')- 123,12 变为 123.45(同样，',' 是法语中的小数点分隔符，而不是 '.')- 欧元变成?(在这里编码?)

Some because of locales : - 123 456 789 becomes 123,456,789 (separator for thousands and millions i ' ' in french instead of ',') - 123,12 becomes 123.45 (again ',' is decimal separator in french, not '.') - the € becomes ? (encoding here ?)

时间和日期有奇怪的行为:-12:12:12 PM 变成 [$-4.69]12:00:42 AM([$-4.69] 是 excel stringFormat 的一部分，POI 似乎不支持)-12/11/14 变成 01/01/04(我不明白那个...)

And there is strange behaviour with time and dates : -12:12:12 PM becomes [$-4.69]12:00:42 AM ([$-4.69] is part of the excel stringFormat and do not seem to be supported by POI) -12/11/14 becomes 01/01/04 (I don't understand that one...)

我会尝试对此进行调查.

I'll try to investigate on this.

感谢您的帮助.

推荐答案

Excel 中的数字(少数极端情况除外)存储为浮点数.Java 中的浮点数，当格式化为字符串时，会打印一个尾随小数点，如果大，则以科学(指数)格式打印，如您所见

Numbers in Excel are (except for a few edge cases) stored as floating point numbers. Floating point numbers in Java, when formatted as a string, are printed with a trailing decimal point, and in scientific (exponential) format if large, as you're seeing

假设您真正想要的是给我一个类似于 Excel 为该单元格显示的字符串"，然后不要调用 cell.toString()，并且不要获取数字双精度值+ 打印为字符串.这些不会给你你需要的东西.

Assuming what you really wanted was "give me a string that looks like what Excel shows for this cell", then do not call cell.toString(), and do not fetch the numeric double value + print as a string. These will not give you what you need.

相反，您需要使用 DataFormatter 类，它提供了读取应用于单元格的 Excel 格式规则的方法，然后在 Java 中(尽其所能)重新创建这些规则

Instead, you need to use the DataFormatter class, which provides methods which read the Excel format rules applied to a cell, then re-creates (as best it can) those in Java

您的代码应该是:

DataFormatter fmt = new DataFormatter();

String phoneNumber = fmt.formatCellValue(cell);

这将根据 Excel 中应用的格式规则来格式化数字，因此应该按照您的预期返回它

That will format the number based on the formatting rules applied in Excel, so should return it looking as you expect

这篇关于Apache POI:以字符串形式获取数字，如excel中所示的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！