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
– fluminis, Commented Apr 22, 2014 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
– bosnjak, Commented Apr 22, 2014 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
– Karibasappa G C, Commented Apr 22, 2014 at 14:53

Zdend · Accepted Answer · 2014-04-23 12:15:43Z

1

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);

answered Apr 23, 2014 at 12:15

Zdend

6113 gold badges8 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Getting wrong numeric values from Excels first column

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related