poi wrong date formula value calculated.

Question

I have the following code

public boolean processCell(boolean hasData, StringBuffer rowData, Cell cell)
 {
  switch (cell.getCellType())
  {
   case Cell.CELL_TYPE_FORMULA:
   {
    try
    {
     this.evaluator.clearAllCachedResultValues();
     switch (this.evaluator.evaluateFormulaCell(cell))
     {
      case XSSFCell.CELL_TYPE_NUMERIC:
      {
       if (DateUtil.isCellDateFormatted(cell))
       {
        logger.warn(cell.getCellFormula());

        rowData.append(dateFormat.format(cell.getDateCellValue()));
        hasData = true;
       }
       else
       {
        rowData.append(numberFormat.format(cell.getNumericCellValue()));
        hasData = true;
       }
       break;
      }
      case XSSFCell.CELL_TYPE_STRING:
      {
       String stringVal = cell.getStringCellValue().trim().replaceAll("\n", "");
       if (stringVal.trim().equalsIgnoreCase("Total MoU/active customer"))
       {
        logger.warn("Last -  KPI ::" + stringVal);
        this.finalRecord = true;
       }
       rowData.append(stringVal);
       hasData = true;
       break;
      }
      case XSSFCell.CELL_TYPE_BOOLEAN:
      {
       rowData.append(cell.getBooleanCellValue());
       hasData = true;
       break;
      }
      case XSSFCell.CELL_TYPE_ERROR:
      {
       int eval = cell.getErrorCellValue();
       if (eval == DIVIDE_BY_ZERO)
        rowData.append("0");
       hasData = true;
       break;
      }
      case XSSFCell.CELL_TYPE_BLANK:
      {
       rowData.append("");
       hasData = true;
       break;
      }
     }
    }
    catch (java.lang.IllegalArgumentException e)
    {

     logger.error("  Formula [ " + (cell.getRowIndex() + 1) + "," + (cell.getColumnIndex() + 1) + " ]  "
       + e.getMessage());
     rowData.append("CellError");
     this.STATUS = FAILURE;
     hasData = true;
     break;
    }
    catch (java.lang.IllegalStateException e)
    {

     logger.error("  Formula [ " + (cell.getRowIndex() + 1) + "," + (cell.getColumnIndex() + 1) + " ]  "
       + e.getMessage());
     rowData.append("CellError");
     this.STATUS = FAILURE;
     hasData = true;
     break;
    }
    catch (java.lang.RuntimeException e)
    {
     this.STATUS = FAILURE;
     logger.error("  Formula [ " + (cell.getRowIndex() + 1) + "," + (cell.getColumnIndex() + 1) + " ]  "
       + e.getMessage());
     rowData.append("MissingFileError");
     hasData = true;
     break;
    }
    break;
   }
   case XSSFCell.CELL_TYPE_BLANK:
   {
    rowData.append("");
    hasData = true;
    break;
   }
   case XSSFCell.CELL_TYPE_NUMERIC:
   {
    if (DateUtil.isCellDateFormatted(cell))
    {
     rowData.append(dateFormat.format(cell.getDateCellValue()));
     hasData = true;
    }
    else
    {
     rowData.append(numberFormat.format(cell.getNumericCellValue()));
     hasData = true;
    }
    break;
   }
    // Formula evaluation ends here
   case XSSFCell.CELL_TYPE_STRING:
   {
    String stringVal = cell.getStringCellValue().trim().replaceAll("\n", "");
    if (stringVal.trim().equalsIgnoreCase("Total MoU/active customer"))
    {
     logger.warn("Last -  KPI ::" + stringVal);
     this.finalRecord = true;
     ;
    }
    rowData.append(stringVal);
    hasData = true;
    break;
   }
   case XSSFCell.CELL_TYPE_ERROR:
   {
    int eval = cell.getErrorCellValue();
    if (eval == DIVIDE_BY_ZERO)
     rowData.append("0");
    hasData = true;
    break;
   }
   case XSSFCell.CELL_TYPE_BOOLEAN:
   {
    rowData.append(cell.getBooleanCellValue());
    hasData = true;
    break;
   }
  }
  rowData.append(FIELD_SEPARATOR);
  return hasData;
 }

when i run the program. in date column i have series of date. the last date without formula is 31/12/2009 its cell reference oj at oj+1 in excel sheet i have 1/1/2010 but in excel sheet i got 29/04/2009 i have printed the formula on this cell and find out that poi 3.6 have wrong cell reference on this. instead of oj+1 its giving NE+1

please help me to resolve the problem

Answer 1

As your example is incomplete, I am unable to reproduce your result. Here is an sscce that produces the expected result:

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;

public class POIExcelReader {

    public static void main(String[] args) throws IOException {
        InputStream myxls = new FileInputStream("test.xls");
        HSSFWorkbook book = new HSSFWorkbook(myxls);
        FormulaEvaluator eval =
            book.getCreationHelper().createFormulaEvaluator();
        HSSFSheet sheet = book.getSheetAt(0);
        for (Row row : sheet) {
            for (Cell cell : row) {
                printCell(cell, eval);
                System.out.print("; ");
            }
            System.out.println();
        }
        myxls.close();
    }

    private static void printCell(Cell cell, FormulaEvaluator eval) {
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_BLANK:
                System.out.print("EMPTY");
                break;
            case Cell.CELL_TYPE_STRING:
                System.out.print(cell.getStringCellValue());
                break;
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    System.out.print(cell.getDateCellValue());
                } else {
                    System.out.print(cell.getNumericCellValue());
                }
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                System.out.print(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA:
                System.out.print(cell.getCellFormula());
                CellValue cellValue = eval.evaluate(cell);
                switch (cellValue.getCellType()) {
                    case Cell.CELL_TYPE_NUMERIC:
                        double v = cellValue.getNumberValue();
                        if (DateUtil.isCellDateFormatted(cell)) {
                            System.out.print(" = "
                                + DateUtil.getJavaDate(v, true));
                        } else {
                            System.out.print(" = " + v);
                        }
                        break;
                }
                break;
            default:
                System.out.print("DEFAULT");
        }
    }
}

Sun Jan 10 00:00:00 EST 2010; 1+1 = 2.0; 123.1; String1; true; 
A1+1 = Mon Jan 11 00:00:00 EST 2010; 2+2 = 4.0; 123.2; String2; false; 
A2+1 = Tue Jan 12 00:00:00 EST 2010; 3+3 = 6.0; 123.3; String3; true; 

Answer 2

yes, it produces result right most of times but when i write the same code and use it for XSSFWorkBook i got the wrong result some times but get right result most of the times i am not able to understand that problem.

Answer 3

how can i detect the possibility of synchronization problems. I dont have clue here. i dont know why its happening because my code and above mention code matches very much. so its may be POI bug