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.

Gagravarr · Accepted Answer · 2014-10-15 09:57:12Z

5

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

edited Oct 15, 2014 at 9:57

answered Sep 24, 2014 at 16:33

Gagravarr

48.5k11 gold badges114 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Léo Martel Over a year ago

Thank you very much. What you suggested did not work for me (I don't undurstand why yet) but it put me on the way, see my edit to get the workaround i found.

Gagravarr Over a year ago

Sounds like you're using a format string that POI doesn't handle fully yet. You'll therefore want to report a bug to the Apache POI project, and provide a small spreadsheet that shows the problem, so it can get fixed

Léo Martel Over a year ago

All right, i'll report with the spreadsheet I used to test. Thanks for your help.

Soham Ghosh · Accepted Answer · 2021-12-20 15:34:30Z

1

This worked for me.

XSSFCell cell = row.getCell(1);
int value = new Double(cell.getNumericCellValue()).intValue();
// To change it to string
String text_value = String.valueOf(value);

answered Dec 20, 2021 at 15:34

Soham Ghosh

1682 silver badges3 bronze badges

Comments

zbender · Accepted Answer · 2023-06-02 13:05:15Z

1

You can use org.apache.poi.ss.util.NumberToTextConverter:

 NumberToTextConverter.toText(cell.getNumericCellValue())

answered Jun 2, 2023 at 13:05

zbender

3873 silver badges15 bronze badges

Comments

mahendra sonavane · Accepted Answer · 2016-06-13 09:33:01Z

0

You will get an error when you try to fetch the numeric cell as String.

You should be setting the cellType as string like below, before fetching the numeric cell.

` HSSFCell yourCell = sheet1.getRow(row).getCell(column);
   yourCell.setCellType(Cell.CELL_TYPE_STRING);
   String data = yourCell.getStringCellValue();`

answered Jun 13, 2016 at 9:33

mahendra sonavane

111 silver badge3 bronze badges

3 Comments

Gagravarr Over a year ago

Did you try reading the javadocs for the method you've mentioned? Only they're pretty explicit that you shouldn't a setCellType(Cell.CELL_TYPE_STRING); to get a numeric cell as a string as it looses formatting!

mahendra sonavane Over a year ago

Need solution .. and it work properly .. check ur end :) think before giving anyone -1.

Gagravarr Over a year ago

It may work in some cases, but as the documentation makes clear it won't work in all. The documentation also points to the correct solution, so advising people to use something that's known to have issues isn't a good idea!

Collectives™ on Stack Overflow

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

4 Answers 4

3 Comments

Comments

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related