Apache POI exception in Apache OFBiz

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 th开发者_开发知识库e 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.

---

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();

---

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.