java.lang.IllegalStateException: Cannot get a numeric value from a text cell Exception while uploading excel sheet to server
According to doc getDateCellValue()
Get the value of the cell as a date. For strings we throw an exception. For blank cells we return a null. Returns: the value of the cell as a date
So you can check to see which type of cell it is using cell.getCellType(). And you can get the cell value as String and parse it into a Date object using SimpleDateFormat like this
SimpleDateFormat dateFormat = new SimpleDateFormat("dd/mm/yy");
Date date = dateFormat.parse(yourStringDateHere);
If your cell is formatted as date then you can use http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFDateUtil.html to check if the cell contains a date value then get date form it
if(HSSFDateUtil.isCellDateFormatted(cell)){
Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());
}
And if the cell is not formatted as date (which is the case here) but you know it's format and that it's a valid date.you can use the first method to get a date.
![Abhishek Singh](https://i.stack.imgur.com/q3eVx.jpg?s=256&g=1)
Comments
-
Abhishek Singh about 2 years
I am uploading an Excel file to server Ref : How to upload files to server using JSP/Servlet?
Here is my code which causes error
public static ArrayList<MOPBDMMapping> readExcel(InputStream fileInputStream) { ArrayList<MOPBDMMapping> mappings = new ArrayList<MOPBDMMapping>(); try{ HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream); HSSFSheet worksheet = workbook.getSheetAt(0); HSSFRow row1 = worksheet.getRow(0); int noOfRows = worksheet.getLastRowNum(); for(int i = 1 ; i <= noOfRows ; i++){ MOPBDMMapping mapping = new MOPBDMMapping(); HSSFCell cell1 = row1.getCell(0); mapping.setMappingID(cell1.getStringCellValue()); HSSFCell cell2 = row1.getCell(1); mapping.setMopID(cell2.getStringCellValue()); HSSFCell cell3 = row1.getCell(2); mapping.setMopName(cell3.getStringCellValue()); HSSFCell cell4 = row1.getCell(3); mapping.setBdmID(cell4.getStringCellValue()); HSSFCell cell5 = row1.getCell(4); mapping.setBdmName(cell5.getStringCellValue()); HSSFCell cell6 = row1.getCell(5); mapping.setBranch(cell6.getStringCellValue()); HSSFCell cell7 = row1.getCell(6); mapping.setStartDate(cell7.getDateCellValue()); //Error Line HSSFCell cell8 = row1.getCell(7); mapping.setEndDate(cell8.getDateCellValue()); HSSFCell cell9 = row1.getCell(8); mapping.setActive(cell9.getStringCellValue()); mappings.add(mapping); } System.out.println(mappings.size()); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return mappings; } }
This is the exception thrown
java.lang.IllegalStateException: Cannot get a numeric value from a text cell org.apache.poi.hssf.usermodel.HSSFCell.typeMismatch(HSSFCell.java:643) org.apache.poi.hssf.usermodel.HSSFCell.getNumericCellValue(HSSFCell.java:668) org.apache.poi.hssf.usermodel.HSSFCell.getDateCellValue(HSSFCell.java:689) com.tcs.rspm.mops.business.ExcelReader.readExcel(ExcelReader.java:52) com.tcs.rspm.mop.upload.ExcelUploadController.doPost(ExcelUploadController.java:42) javax.servlet.http.HttpServlet.service(HttpServlet.java:641) javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
Here is my Servlet code
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { System.out.println("In controller.."); try { List<FileItem> items = new ServletFileUpload(new DiskFileItemFactory()).parseRequest(request); for (FileItem item : items) { if (item.isFormField()) { // Process regular form field (input type="text|radio|checkbox|etc", select, etc). String fieldname = item.getFieldName(); String fieldvalue = item.getString(); System.out.println( "Normal Field : " + fieldname + fieldvalue); // ... (do your job here) } else { // Process form file field (input type="file"). String fieldname = item.getFieldName(); String filename = FilenameUtils.getName(item.getName()); System.out.println("File field : " + fieldname + filename ); InputStream filecontent = item.getInputStream(); ExcelReader.readExcel(filecontent); } } } catch (FileUploadException e) { throw new ServletException("Cannot parse multipart request.", e); } System.out.println("Out controller.."); // ... }
What can be a reason ? '08/12/13' is the entry in excel sheet ? Any solutions ?