java.lang.IllegalStateException：无法从文本单元格异常中获取数值，同时将 Excel 工作表上传到服务器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19115421/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
java.lang.IllegalStateException: Cannot get a numeric value from a text cell Exception while uploading excel sheet to server
提问by Abhishek Singh
I am uploading an Excel file to server Ref : How to upload files to server using JSP/Servlet?
我正在将 Excel 文件上传到服务器 Ref：如何使用 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
这是我的 Servlet 代码
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 ?
什么是原因？'08/12/13' 是 Excel 表中的条目吗？任何解决方案？
回答by AlexR
Excel stores Dates as LONG (Numeric). What you want is a SimpleDateFormatto parse your STRING Date.
Excel 将日期存储为 LONG（数字）。你想要的是SimpleDateFormat解析你的字符串日期。
SimpleDateFormat sdf = new SimpleDateFormat("mm/dd/yy");
Date d = sdf.parse(cellValueAsString);
回答by noobandy
According to doc getDateCellValue()
根据文档 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
因此，您可以使用 cell.getCellType() 检查它正在使用哪种类型的单元格。您可以将单元格值作为 String 获取并使用 SimpleDateFormat 将其解析为 Date 对象，如下所示
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.htmlto check if the cell contains a date value then get date form it
如果您的单元格格式为日期，那么您可以使用http://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFDateUtil.html检查单元格是否包含日期值，然后从它获取日期
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.
如果单元格没有格式化为日期（这里就是这种情况），但你知道它的格式并且它是一个有效的日期。你可以使用第一种方法来获取日期。
回答by agad
The cell in excel is not set to a Date Format, so even the value is formated as date, excel treats it as a string. You need to set the cell as date or read it as string and parse to date.
excel 中的单元格没有设置为日期格式，所以即使是将值设置为日期格式，excel 也将其视为字符串。您需要将单元格设置为日期或将其读取为字符串并解析为日期。
