Results 1 to 2 of 2
  1. #1
    Szdnez is offline Member
    Join Date
    Mar 2009
    Posts
    3
    Rep Power
    0

    Question Import Excel: How to get a blank, when the result of a formula is blank?

    Hello,

    I am trying to read an excel file via poi. Everything works fine except one thing. In my imported file there are two sheets. Some cells on the first page refers via a formular to cells on sheet 2, for example
    Java Code:
    =Sheet2!U3
    . I only import sheet 0 and take the values from cells like this:
    Java Code:
    final ForumaleEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
    final DataFormatter formatter = new DataFormatter();
    final String value = formatter.format( cell, evaluator );
    If Sheet2!U3 is empty, I always get "0" as value. After debugging I found out why:
    WorkbookEvaluator.derefenceResult():
    Java Code:
    public static ValueEval dereferenceResult( ValueEval evaluationResult, int srcRowNum, int srcColNum )
    {
        ...
        if ( value == BlankEval.instance ) {
            return NumberEval.ZERO;
            // Formulas never evaluate to blank. If a formula appears to have evlautated to blank,
            // the actual value is empty string. This can be verified with ISBLANK().
        ...
    }
    Is there any possibility to change this behaviour? What is about the method ISBLANK()? I could not find it anywhere? It also would be helpful, if I have access to the returned ValueEval (to do something like this:
    Java Code:
    if ( value == NumberEval.ZERO ) return null;
    ). But as I have seen the ValueEval is wasted during the format()-operation and only the number value of NumberEval is evaluated.

  2. #2
    Szdnez is offline Member
    Join Date
    Mar 2009
    Posts
    3
    Rep Power
    0

    Default Re: Import Excel: How to get a blank, when the result of a formula is blank?

    Hey,

    I found a solution for my problem. I do something like this:
    Java Code:
    if ( cell.getCellType() == XSSFCell.CELL_TYPE_FORMULA ) {
        final String oldFormula = cell.getCellFormula();
        final String newFormula = "IF( \"\" = ( " + oldFormula + " ), \"BlAnK\", " + oldFormula + " )";
        cell.setCellFormula( newFormula );
    }
    String value = formatter.formatCellValue( cell, formulaEvaluator ); 
    if ( "BlAnK".equals( value ) ) {
       value = null;
    }
    I do not know if it works for every possible formula but in my case everything is fine. Only BlAnK has to be unique.

Similar Threads

  1. Blank Row in Table
    By torres9 in forum New To Java
    Replies: 0
    Last Post: 12-06-2012, 10:58 AM
  2. struts-blank.war
    By Dayanand in forum Web Frameworks
    Replies: 2
    Last Post: 08-02-2011, 01:08 PM
  3. Blank screen
    By dewitrydan in forum New To Java
    Replies: 14
    Last Post: 08-12-2010, 05:19 PM
  4. Blank result for jsp_servlet in Eclipse
    By Unni in forum Eclipse
    Replies: 2
    Last Post: 07-12-2007, 04:30 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •