Getting wrong numeric values from Excels first column

Question

I read Excel spreadsheet (.xlsx) with Apache-poi 3.8. When I try to get numeric value from cell at 0 column index, I get wrong numberic value.

For example my table looks like this

+-----------+---------+---------+
| Header1   | Header2 | Header3 |
+-----------+---------+---------+
| 123456789 | AA      | BB      |
|     99999 | CC      | DD      |
+-----------+---------+---------+

I begin to read on Row index 1 and Cell Index 0. It should returns 123456789 but it returns wrong value 13. Next columns are correct even if it's numeric value. If the first cell contains string it returns correct value as well. Row 2 Cell 0 reads like 14 and next columns are correct.

The number I get from first column is somehow related to amount of rows in sheet, If I have 6 rows, it starts to return 16,17,18,19,20. It should not have to be related with other frameworks but I use myfaces tomahawk and jsf 1.2 for upload my Excel file.

public String getCellStr(final int x, final int y) {
    String cellValue = "";
    try {
        Row row = sheet.getRow(x);
        Cell cell = row.getCell(y);
        if (row == null || (row != null && cell == null)) {
            cellValue = "";
        } else {
            switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                cellValue = cell.toString();
                break;
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    cellValue = DateUtils.date2String(cell.getDateCellValue());
                } else {
                    Double value = cell.getNumericCellValue();
                    Long longValue = value.longValue();
                    cellValue = longValue.toString();
                }
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                cellValue = new String(new Boolean(
                                  cell.getBooleanCellValue()).toString());
                break;
            case Cell.CELL_TYPE_BLANK:
                cellValue = "";
                break;
            }
        }
    } catch (NullPointerException e) {
        return cellValue;
    }
    return cellValue;
}

DO NOT use new String(...), it is not necessary toString already return a String object. — fluminis, Apr 22 '14 at 14:48
Also, i do not think that excel indexing starts at zero, rather at one. So try fetching the column with index 1. — bosnjak, Apr 22 '14 at 14:49
Hi, first set the cell format as String before you read it and then you read it .. it can be set as cell.setCellType(Cell.CELL_TYPE_STRING); then read using your code cell.getNumericCellValue(); — Karibasappa G C, Apr 22 '14 at 14:53
@user1763507 same result. I tried many ways how to read a cell value. Only way how can i get number from first column is to put apostrophe before the number on Excel level. But it's not a solution. — Zdend, Apr 22 '14 at 15:15
Its strange... working fine on my end and giving the expected result. — Sankumarsingh, Apr 22 '14 at 18:24
possible duplicate of [How can I read numeric strings in Excel cells as string (not numbers) with Apache POI?](http://stackoverflow.com/questions/1072561/how-can-i-read-numeric-strings-in-excel-cells-as-string-not-numbers-with-apach) — Gagravarr, Apr 22 '14 at 19:23
@Sankumarsingh I think it that is problem in my configuration or file is corrupted through my uploading (myfaces tomahawk). — Zdend, Apr 23 '14 at 08:12
@Gagravarr No it's not, I tried their solution, but It's quite different problem. I have problem only with first column and only when there are numeric value. — Zdend, Apr 23 '14 at 08:13

score 1 · Accepted Answer · answered Apr 23 '14 at 12:15

Finally I find my mistake. This code was responsible for the strange behaviour. Apparently I cannot cast cell to other type and cast it back without destroying value.

int cellType = cell.getCellType();
cell.setCellType(Cell.CELL_TYPE_STRING);
String val = cell.getStringCellValue();
cell.setCellType(cellType);

Getting wrong numeric values from Excels first column

1 Answers1