Cannot get a numeric value from a text cell

Question

I am trying to import the data from Excel to MySQL.

    java.util.ArrayList;
    import java.util.Iterator;

    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.DataFormatter;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;

    import moduleobject.Summary;

    public class SummaryReader implements SheetReader<ArrayList<Summary>> {

        public ArrayList<Summary> read(Sheet sheet) {

            Iterator<Row> rowiterator = sheet.iterator();
            rowiterator.next();
            ArrayList<Summary> list = new ArrayList<>();
            Summary obj = new Summary();
            while (rowiterator.hasNext()) {

                Row nextRow = rowiterator.next();
                Iterator<Cell> celliterator = nextRow.cellIterator();

                while (celliterator.hasNext()) {

                    obj.setProcessor(celliterator.next().getStringCellValue());
                    obj.setProcessName(celliterator.next().getStringCellValue());

                    obj.setLevel(celliterator.next().getNumericCellValue());
                    obj.setPA11(celliterator.next().getNumericCellValue());
                    obj.setPA21(celliterator.next().getNumericCellValue());
                    obj.setPA22(celliterator.next().getNumericCellValue());

                    // obj.setPA31(celliterator.next().getNumericCellValue());
                    // obj.setPA32(celliterator.next().getNumericCellValue());
                }
            }

            list.add(obj);

            return list;
        }

    }

The error I am getting is

Exception in thread "main" java.lang.IllegalStateException: Cannot get a numeric value from a text cell
    at org.apache.poi.xssf.usermodel.XSSFCell.typeMismatch(XSSFCell.java:994)
    at org.apache.poi.xssf.usermodel.XSSFCell.getNumericCellValue(XSSFCell.java:305)
    at reader.SummaryReader.read(SummaryReader.java:32)
    at reader.SummaryReader.read(SummaryReader.java:1)
    at controller.JobController.startJob(JobController.java:69) at job.JobLauncher.main(JobLauncher.java:21)

Why do I get this exception?

I think the error message is pretty clear. The value in the cell is formated as Text not as numeric — Jens
– Jens, Commented Jul 20, 2016 at 6:07
read the cell value as text and parse it using Integer.parse(string) — Jens
– Jens, Commented Jul 20, 2016 at 6:26

Draken · Accepted Answer · 2017-01-16 09:09:10Z

2

Please use:

DataFormatter formatter = new DataFormatter();
Cell cell = sheet.getRow(i).getCell(0);
String var_name = formatter.formatCellValue(cell);

edited Jan 16, 2017 at 9:09

Draken

3,19614 gold badges38 silver badges56 bronze badges

answered Jan 16, 2017 at 7:32

Nilesh chauhan

515 bronze badges

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

1 Comment

S Gaber Over a year ago

this thing doesn't work for me though, the out put of it something like: SUM(I38+D39+F39-G39-H39)

kevin · Accepted Answer · 2016-07-20 07:48:39Z

-1

Perhaps,one of the way is that you can set the cell type as

celliterator.next().setCellType(CELL_TYPE_STRING);

before you read the cell value and read the cell value as string.Then you can parse the string value to number if needed.

answered Jul 20, 2016 at 7:48

kevin

13 bronze badges

1 Comment

Gagravarr Over a year ago

The Apache POI JavaDocs are very clear on this being the wrong way to handle numbers to strings, so don't do it!

Collectives™ on Stack Overflow

Cannot get a numeric value from a text cell

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related