4

My aim is to read an Excel file using POI and print the values present in it. If the value is 5, then my output must be 5, but it is returning as 5.0.

Below is the code that I have tried:

FileInputStream fileInputStream = null;
XSSFSheet xssfResultSheet = null;
String filePath = "C:\\MyXcel.xlsx";
fileInputStream = new FileInputStream(new File(filePath));
XSSFWorkbook workbook = null;
workbook = new XSSFWorkbook(fileInputStream);
xssfResultSheet = workbook.getSheet("Sheet1");
int iRowCount = xssfResultSheet.getLastRowNum();

for (int i = 1; i <= iRowCount; i++) {
    Row resultRow = xssfResultSheet.getRow(i);
    System.out.println(resultRow.getCell(0));
}

My Excel sheet has values 1, 2, 3 but my output is 1.0, 2.0, 3.0. Instead my output should also be 1, 2, 3.

Improve this question
2
  • AFAIK, getCell always returns float values. Have you tried parsing to Integer and then doing Math.round() on the value? Commented Mar 6, 2014 at 10:32
  • 1
    All numeric values in Excel are (64 bit) doubles. Commented Mar 6, 2014 at 10:33

5 Answers 5

Reset to default
11

Change:

System.out.println(resultRow.getCell(0));

To:

System.out.println(new DataFormatter().formatCellValue(resultRow.getCell(0)));

Explanation:

Apache POI provides for DataFormatter class as utility to leverage the format of the content as it appears in Excel. You can choose custom formats too, a simple example would be (cell is reference to your XSSFCell object):

Excel sheet looks like:

enter image description here

Code:

System.out.println(new DataFormatter().formatCellValue(cell));

The above line would print:

50%
$ 1,200
12/21/14
9886605446

Whereas your normal print would interpret it differently:

0.5
1200.0
21-Dec-2014
9.886605446E9
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

try this,

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

2 Comments

This works flawlessly when the cell value to be taken as int
Just casting the returned value to int will work. (int) row.getCell(<CellPostion>).getNumericCellValue()
0

POI should have a cell.getRawValueAsString() method, but AFAIK that's not the case. The displayed text you see in excel is the sum of value + format. So if you want to recover it "as displayed":

  • Retrieve cell value from cell object
  • Retrieve cell format using associated CellStyle.
  • apply format to value using DataFormatter
Improve this answer

1 Comment

Apache POI does have such a method though! It's DataFormatter.formatCellValue(Cell)
-1

See if this helps:

Try parseInt, after converting the cell's value to string using toString. 

Cell cell=row.getCell(n);

First, convert cell's value to String using toString()

String cell_value = cell.toString()

Use parseInt to convert the same into integer:

int waitTime = Integer.parseInt(cell_value);

Now test whether the parseInt has done it's job or not.

System.out.print(waitTime+1);
Improve this answer

Comments

-2

Apache POI interprets your numerical cell values as double, in order to avoid this, try to set the cell type. Maybe this will help you:

for (int i = 1; i <= iRowCount; i++) {
    Row resultRow = xssfResultSheet.getRow(i);
    resultRow.getCell(i).setCellType(Cell.CELL_TYPE_STRING);
    System.out.println(Integer.parseInt(resultRow.getCell(0).toString()));
}
Improve this answer

2 Comments

If you look at the Apache POI Javadocs for setCellType, they're very explicit that this is not the right way to do that!
Yes, this is not the right way, but is a way;) A simple to use one. Make it work and after that make it clean ;)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.