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.

The error message says data type mismatch. Are you using the same data for your WorkBook in the standalone app and ofbiz? — CoolBeans, Feb 02 '11 at 04:13
coolBean thanks for ur comment.But i use same excel file in both. — SIVAKUMAR.J, Feb 02 '11 at 09:00
`Cannot get a text value from a numeric formula cell` - try printing the `cell.getNumericCellValue` — JoseK, Feb 02 '11 at 13:30

score 2 · Accepted Answer · edited Jun 14 '11 at 16:25

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

score 0 · Answer 2 · answered Jun 15 '11 at 09:26

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.

Apache POI exception in Apache OFBiz

2 Answers2