Apache POI：获取一个数字作为字符串，如excel所示 [英] Apache POI : getting a number as a string, as displayed in excel

问题描述

我正在尝试用POI解析excel工作簿。在表格中，我有一些数字显示为：

06 85 85 65 45

（这是一个法国电话号码，始终以0开头，每2位都有空格）

我需要从excel中获取一个字符串，零和

以下是调查中的一些内容：

  double doubleValue = cell.getNumericCellValue（）; // value 6.85856545E8 
 String dataStringFormat = cell.getCellStyle（）。getDataStringFormat（）; // value 0＃######## 
  

我猜想POI API有一种方法可以将dataStringFormat应用于doubleValue来获得像06 85 85 65 45这样的东西。

任何人都有想法关于我该怎么做？

非常感谢。

编辑：
Gagravarr put我在路上他的

  DataFormatter fmt = new DataFormatter（）; 
 String phoneNumber = fmt.formatCellValue（cell）; 
  

仍然返回685856545但我感谢他，我发现了CellNumberFormatter类。并得到我的06 85 85 65 45与以下代码：

  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

有些因为语言环境：
- 123 456 789成为123,456,789（分隔符为数千和数百万我在法语而不是'，'）
- 123,12成为123.45（再次'，'是法语中的小数分隔符，而不是'）
- €？ （编码在这里？）

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

我会尝试调查这个。

感谢您的帮助。 >

解决方案

存储为浮点数的Excel中的数字（除了几个边缘情况）。 Java中的浮点数，格式化为字符串时，会打印一个尾随的小数点，如果看到的话，以科学（指数）格式显示

假设你真正想要的是给我一个看起来像Excel为这个单元格显示的字符串，然后不要调用cell.toString（），并且不要获取数字double值+ print一个字符串这些不会给你你需要的。

相反，你需要使用 DataFormatter类，它提供了读取应用于单元格的Excel格式规则的方法，然后重新创建（尽可能地）那些在Java中

您的代码应该是：

  DataFormatter fmt = new DataFormatter（）; 
 
 String phoneNumber = fmt.formatCellValue（cell）; 
  

这将根据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

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

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#" "##" "##" "##" "##

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 ?

Thank you very much.

Edit: Gagravarr put me on the way. His

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

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

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

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 ?)

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.

Thanks for your help.

解决方案

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

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.

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

Your code should be:

DataFormatter fmt = new DataFormatter();

String phoneNumber = fmt.formatCellValue(cell);

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