Apache POI exception in Apache OFBiz

Question

Im working in a project.Its server side application is in ofbiz.I want to read,edit,update,etc the excel sheet.But in normal java program its working.Im using apache poi API.But when put in ofbiz then run the application.Then sometimes some exception .The following are

"
     [java] java.lang.IllegalStateException: Cannot get a text value from a nume
ric formula cell
     [java]     at org.apache.poi.hssf.usermodel.HSSFCell.typeMismatch(HSSFCell.
java:637)
     [java]     at org.apache.poi.hssf.usermodel.HSSFCell.checkFormulaCachedValu
eType(HSSFCell.java:642)
     [java]     at org.apache.poi.hssf.usermodel.HSSFCell.getRichStringCellValue
(HSSFCell.java:719)
     [java]
     [java]     at org.apache.poi.hssf.usermodel.HSSFCell.getStringCellValue(HSS
FCell.java:697)
     [java]     at org.ofbiz.productionmgntSystem.setTargetPlan.getRequiredData(
setTargetPlan.java:764)
     [java]     *****
     [java]     at org.ofbiz.productionmgntSystem.setTargetPlan.setTask(setTarge
tPlan.java:201)
     [java]     at org.ofbiz.productionmgntSystem.ThreadProcess.run(ThreadProces
s.java:113)

"

Here im using same apache poi API in both normal java & ofbiz.

Answer 1

I had this same problem when integer transaction numbers were being read. The natural way that occurred to me to fix this was to use:

if(cell.getCellType()==Cell.CELL_TYPE_NUMERIC) {
   String.valueOf(cell.getNumericCellValue());
}

This doesn't work as 12345 is converted to 12345.0

The workaround to get the string value exactly as it appears in the excel document is to do:

cell.setCellType(Cell.CELL_TYPE_STRING);
String cellValue=cell.getStringCellValue();

Answer 2

Excel will often store cells containing only numbers as numeric cells, rather than string cells. You may think it isn't a numeric cell, but it is...

You probably want something like:

DataFormatter formatter = new DataFormatter(Locale.US);

....

for(Cell cell : row) {
  String val;
  if(cell.getCellType()==Cell.CELL_TYPE_NUMERIC) {
     // Format the number to look like it does in excel
     val = formatter.formatCellValue(cell);
  }
  if(cell.getCellType()==Cell.CELL_TYPE_STRING) {
     // Just get the string as-is
     val = cell.getRichStringCellValue().getString();
  }
}

Have a look at the POI quick guide and howto to get you started with using POI.