CODEWITHSUNDEEP
javaexcelapache-poiopenxml
Ron
Ron

Reputation: 1508

Printing Phone Number (String) to excel using Apache POI show up as scientific notation (4.165551234E9)

How can I change the Cell so that it appears as a 'general' type instead of 'scientific'

Here's the line that causes issues, getPhoneNumber() returns a String

row.createCell(4).setCellValue(c.getPhoneNumber());

Phone numbers (strings) show up as 4.165551234E9, the data is there but in the wrong format.

I've tried setting the cell format to numeric

row.getCell(4).setCellType(Cell.CELL_TYPE_NUMERIC);

but this causes the 10-digit strings to show up as numbers between 11 and 165. i.e. 11, 16, 20, 26, 13, 31, 35

I've also tried parsing the phone number into a long

try {
    row.createCell(4).setCellValue(Long.parseLong(c.getPhoneNumber()));
} catch (NumberFormatException e) {
    row.createCell(4);
}

There is no setCellValue(Long) only setCellValue(Double), so I would assume that Strings are the proper way to handle this.

Upvotes: 1

Views: 4373

Answers (4)

SirArcher
SirArcher

Reputation: 9

I had the same issue when getting the value of a cell. I just changed the cell type in my program to a cell type string, and then it was fine.

Upvotes: 0

Gagravarr
Gagravarr

Reputation: 48346

Apache POI is behaving just the same as Excel does. If you put a really long number into Excel, in a default formatted cell, it will store the number as-is, but display it in scientific format.

What you need to do, which is the same as what you'd do in Excel, is to set the formatting rules on the cell to tell Excel to display lots of numbers.

For example, if I want a UK phone number to display properly, then I need to ask Excel to format the cell with the following format string:

0###########

Or if I wanted the normal spacing:

0##### ### ###

Assuming the latter, what I'd do is:

// Do this only once per style
DataFormat df = workbook.createDataFormat()

CellStyle phoneStyle = workbook.createCellStyle();
phoneStyle.setDataFormat( df.getFormat("0##### ### ###") );

....

// Create a cell, set the number, and apply our phone formatting to it
cell.setCellValue(phoneNumber);
cell.setCellStyle(phoneStyle);

Alternately, you can store the number as a string, but excel tends to sulk about that and complain you have a number stored as a string and give warnings about that. However, it does give you the chance to have complete control over the spaces, layout etc

Upvotes: 0

Ron
Ron

Reputation: 1508

The first attempt is correct:

row.createCell(4).setCellValue(c.getPhoneNumber());

The input was in an incorrect format, to get a String phoneNumber from a numeric type Cell use:

String phone = ((long) r.getCell(4).getNumericCellValue()) + "";

Upvotes: 0

Rahul
Rahul

Reputation: 45070

You can use the CELL_TYPE_STRING in your case and directly set the value of your phone number like this

row.createCell(4).setCellValue(c.getPhoneNumber()); // since getPhoneNumber returns a String

The HSSFCell#setCellType takes in an int value and CELL_TYPE_STRING or CELL_TYPE_NUMERIC are all static int fields provided by POI itself to set the cell type appropriately.

Upvotes: 1

Related Questions