Reading xl sheeet by using java code

Question

Here I am trying to read xl sheet by using following java code but i am getting the exception while reading cell as below.

Xl reading code

Cell cell14 = row.getCell(14);
int celltype = cell4.getCellType();
if (celltype == HSSFCell.CELL_TYPE_BOOLEAN) {
    System.out.println("inide boolean");
    row.getCell(14).getBooleanCellValue();
    System.out.println("row.getCell(14).getBooleanCellValue()" + row.getCell(14).getBooleanCellValue());
} else if (celltype == HSSFCell.CELL_TYPE_ERROR) {
    System.out.println("inide CELL_TYPE_ERROR");
    row.getCell(14).getErrorCellValue();
    System.out.println("row.getCell(14).getErrorCellValue()" + row.getCell(14).getErrorCellValue());
} else if (celltype == HSSFCell.CELL_TYPE_FORMULA) {
    System.out.println("inide CELL_TYPE_FORMULA");
    row.getCell(14).getCellFormula();
    System.out.println(" row.getCell(14).getCellFormula()" + row.getCell(14).getCellFormula());
} else if (celltype == HSSFCell.CELL_TYPE_NUMERIC) {
    System.out.println("inide CELL_TYPE_NUMERIC");
    row.getCell(14).getNumericCellValue();
    System.out.println("row.getCell(14).getNumericCellValue()" + row.getCell(14).getNumericCellValue());
} else if (celltype == HSSFCell.CELL_TYPE_STRING) {
    System.out.println("inide CELL_TYPE_STRING");
    row.getCell(14).getRichStringCellValue();
    System.out.println("row.getCell(14).getStringCellValue();" + row.getCell(14).getRichStringCellValue());
}

Here is the exception

java.lang.IllegalStateException: Cannot get a text value from a numeric cell
    at org.apache.poi.hssf.usermodel.HSSFCell.typeMismatch(HSSFCell.java:643)
    at org.apache.poi.hssf.usermodel.HSSFCell.getRichStringCellValue(HSSFCell.java:720)
    at org.apache.poi.hssf.usermodel.HSSFCell.getRichStringCellValue(HSSFCell.java:67)

My number is there in xl is like this 975125351237 but inside numaric is reading like this 9.788184771442E12 can u plz solve this problem — user3214269, Feb 21 '14 at 07:55
That sounds like a different problem. Is there a row with similar value in the xls file? — Gábor Bakos, Feb 21 '14 at 07:59
@user3214269 for formatting issues I agree it's a different problem, perhaps refer to this solution http://stackoverflow.com/questions/21624528/how-to-read-exact-cell-content-of-excel-file-in-apache-poi/21625090#21625090 — StoopidDonut, Feb 21 '14 at 08:19
I do not think that is a formatting problem, those are differ in their 3rd digit. That should not be the case for rounding errors imho. — Gábor Bakos, Feb 21 '14 at 08:31
Do you have a number like 9788184771442 in your input text file? Do you still get an IllegalStateException? — Gábor Bakos, Feb 21 '14 at 09:25

score 2 · Answer 1 · answered Feb 21 '14 at 07:32

2

I think the following typo might be the cause of the problem:

Cell cell14 = row.getCell(14);
int celltype = cell4.getCellType();

Probably it should be:

Cell cell14 = row.getCell(14);
int celltype = cell14.getCellType();

(cellType is now based on cell14 instead of cell4.)

answered Feb 21 '14 at 07:32

Gábor Bakos

8,982
52
35
52

My number is there in xl is like this 975125351237 but inside numaric is reading like this 9.788184771442E12 cna u plz solve this problem. – user3214269 Feb 21 '14 at 07:51
@user3214269 Do you see the difference between your code and the proposed change? If not just copy the proposed change instead of the old code, else just put 1 to the proper place. – Gábor Bakos Feb 21 '14 at 11:08
975125351237 is 9.78818477144237E12, so that's entirely as expected. I'd suggest you read up on floating point, scientific notation, then how to tell java what format you want integers turned to strings with – Gagravarr Feb 21 '14 at 13:53

Reading xl sheeet by using java code

1 Answers1