Read Excel Numeric cell values as they are in Java

Question

When I am reading excel numeric cell values, i am getting the output with decimals. eg: 79 is reading as 79.0, 0.00 reading as 0.0. The code for my application I have written is:

int type = cell.getCellType();
if (type == HSSFCell.CELL_TYPE_NUMERIC)
  System.out.println(cell.getNumericCellValue());

score 18 · Accepted Answer · edited May 23 '17 at 12:10

18

This question comes up a lot on Stack Overflow, so you'd be well advised to look through many of the similar ones to see there answers!

Excel stores almost all numbers in the file format as floating point values, which is why POI will give you back a double for a numeric cell as that's what was really there. If you want it to look like it does in Excel, you need to apply the formatting rules defined against the cell to the number. Thus, you want to do exactly the same thing as in my answer here. To quote:

What you want to do is use the DataFormatter class. You pass this a cell, and it does its best to return you a string containing what Excel would show you for that cell. If you pass it a string cell, you'll get the string back. If you pass it a numeric cell with formatting rules applied, it will format the number based on them and give you the string back.

For your case, I'd assume that the numeric cells have an integer formatting rule applied to them. If you ask DataFormatter to format those cells, it'll give you back a string with the integer string in it.

All you need to do is:

// Only need one of these
DataFormatter fmt = new DataFormatter();

// Once per cell
String valueAsSeenInExcel = fmt.formatCellValue(cell);

edited May 23 '17 at 12:10

Community

1
1

answered May 10 '13 at 14:25

Gagravarr

47,320
10
111
156

Thank you, I used DataFormatter got the required solution. Thank you very much!! – Java Developer May 10 '13 at 14:50
note, that this solution will not work if your cell has formula data type, formula type will not be evaluated. – userxuser Oct 05 '20 at 10:02
@userxuser If you have a formula, you can use [DataFormatter.formatCellValue(Cell,FormukaEvaluator)](https://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/DataFormatter.html#formatCellValue-org.apache.poi.ss.usermodel.Cell-org.apache.poi.ss.usermodel.FormulaEvaluator-) to get the cell evaluated before formatting – Gagravarr Oct 05 '20 at 10:36
Hi, by this approach able to convert only one column of excel to numeric but next column not coming properly. Its coming as double value only. why? – Sumathi Oct 16 '20 at 07:36

score 2 · Answer 2 · answered Aug 13 '14 at 03:22

Use the DataFormatter as the following, it will detect the format of the cell automatically and produce the string output

                    DataFormatter formatter = new DataFormatter();
                    String df2 = formatter.formatCellValue(cell);

score 1 · Answer 3 · answered May 10 '13 at 13:08

1

I'm not 100% this would work but I believe what you're looking for is DecimalFormat.

answered May 10 '13 at 13:08

Tobes

11
1

Yes, the question really has nothing to do with POI... it is about formatting a Java double – Darius X. May 10 '13 at 13:42
This question has something to do with POI, I am using org.apachi.poi to read cell values.Please help me out in this issue. – Java Developer May 10 '13 at 14:08
I want to read numerical cell values as they are in Excel. But my code is reading 79-->79.0, 100.50-->100.5. please correct me where i am doing wrong, I could not find it. Help me! – Java Developer May 10 '13 at 14:30

score 1 · Answer 4 · answered May 10 '13 at 13:08

1

I assume you're using Apache POI.

You should consider playing with DataFormats. Here is some very minimal draft code.

Otherwise, if the data in your work book is consistent, you might want to round the double returned by getNumericCellValue to int.

System.out.println((int)Math.round(cell.getNumericCellValue()));

answered May 10 '13 at 13:08

Mena

47,782
11
87
106

cell also have double values(eg:100.50). After trying with the code you suggested I am getting the output as 100 which is not desired. If the values are 79, 100.50, I should read them as they are in Excel. Please help me. – Java Developer May 10 '13 at 13:14
I don't want to round any numeric cell value coming from Excel, I just want to read them as they are. But it is reading as 79-->79.0, 100.50--> 100.5. Please help me out in this issue. – Java Developer May 10 '13 at 13:34
Ok so you do have actual double values as well, hence rounding them will not work. Have you tried checking out the DataFormat for Apache POI? I believe it would guide you to the right direction. Here are some other links to get you started: http://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/DataFormatter.html and https://poi.apache.org/apidocs/org/apache/poi/ss/usermodel/BuiltinFormats.html – Mena May 10 '13 at 16:19

score 1 · Answer 5 · answered Oct 08 '20 at 11:49

1

still people are facing issues with this can use this https://poi.apache.org/apidocs/dev/org/apache/poi/ss/util/NumberToTextConverter.html

NumberToTextConverter.toText(cell.getNumberiCellValue())

answered Oct 08 '20 at 11:49

kamodh pandey

11
2

Read Excel Numeric cell values as they are in Java

5 Answers5

Linked

Related