Cannot get a NUMERIC value from a STRING cell

Question

Getting the error java.lang.IllegalStateException: Cannot get a NUMERIC value from a STRING cell

Employee class:`

@Entity
@Table(name="Employee")
public class Employee {

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name = "id")
private int Id;
@Column(name = "firstname")
private String FirstName;
@Column(name = "lastname")
private String LastName;
@Column(name = "email")
private String Email;
@Column(name = "mobile")
private long Mobile;

@Transient
private MultipartFile file;

getters and setters.....

Service Class:

Workbook book=null;
                try {
                    book = new HSSFWorkbook(file.getInputStream());
                } catch (IOException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
                Sheet sheet=book.getSheetAt(0);
                Iterator<Row> rows =    sheet.iterator();

        while(rows.hasNext()) {
            Row row = rows.next();

                emp.setId(row.getCell(0).getNumericCellValue());

                emp.setFirstName(row.getCell(1).getStringCellValue());

                emp.setLastName(row.getCell(2).getStringCellValue());

                emp.setEmail(row.getCell(3).getStringCellValue());

                emp.setMobile(NumberToTextConverter.toText(row.getCell(4).getNumericCellValue()) );
            }
        }

While executing this it is giving error like:java.lang.IllegalStateException: Cannot get a NUMERIC value from a STRING cell

Can you please set a comment beside the line where the error occurred? — Razib, Aug 08 '19 at 19:09
Does your excel sheet have headers? If so, it'll fail on parsing the header. — Compass, Aug 08 '19 at 19:10
Possible duplicate of [How can I read numeric strings in Excel cells as string (not numbers)?](https://stackoverflow.com/questions/1072561/how-can-i-read-numeric-strings-in-excel-cells-as-string-not-numbers) — Yoshikage Kira, Aug 08 '19 at 19:12
So, it's trying to parse a String header as a Numeric. Why not skip the header? — Compass, Aug 08 '19 at 19:16
@Compass I m storing values in DB. So I created column name same as headers name — , Aug 08 '19 at 19:18

score 0 · Answer 1 · answered Aug 09 '19 at 08:59

You can simply skip the first line as follows:

            Workbook book=null;
            try {
                book = new HSSFWorkbook(file.getInputStream());
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            Sheet sheet=book.getSheetAt(0);
            Iterator<Row> rows =    sheet.iterator();


    // SKIP HEADER
    if (rows.hasNext()) {
        rows.next();
    }

    while(rows.hasNext()) {
        Row row = rows.next();

            emp.setId(row.getCell(0).getNumericCellValue());

            emp.setFirstName(row.getCell(1).getStringCellValue());

            emp.setLastName(row.getCell(2).getStringCellValue());

            emp.setEmail(row.getCell(3).getStringCellValue());

           emp.setMobile(NumberToTextConverter.toText(row.getCell(4).getNumericCellValue()) );
        }
    }

Cannot get a NUMERIC value from a STRING cell

1 Answers1