java - Apache POI : getting a number as a string, as displayed in excel

Question

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.

Answer 1

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