Re: #Value in MS Office - MAY have found an answer.

[Tony Nelson <tn...@starpoint.com>]

Anthony Andrews wrote:
> Morning Tony,
>
> Found a fix that appears to be in both 3.1 final and 3.5 beta 1 
> archives. There is a static method you can use like this;
>
>         cell = row.createCell((short)0);
>         cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
>         cell.setCellFormula("SUM(A1:A10)");
>
>         cell = row.createCell((short)1);
>         cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
>         cell.setCellFormula("SUM(B1:B10)");
>
>         row = sheet.createRow(11);
>        
>         cell = row.createCell((short)1);
>         cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
>         cell.setCellFormula("IF(B11=0,0,A11/B11)");
>        
>         HSSFFormulaEvaluator.evaluateAllFormulaCells(workbook);
>
> I would imagine that if you have a workbook containing multiple sheets 
> where each sheet holds hundreds of cells with forumlae then there 
> could be a performance hit but it does seem to overcome the immediate 
> problem.
>
> Make sure that you check the results; I did not, simply opening the 
> workbook and not seeing the #Value error message staring back at me 
> was enough!!
>

I wish I could replicate your results.  Unfortunately, this still didn't 
fix my problem.  I will try the head branch from SVN today, if I can 
figure out how to compile it ;)

Here the lastest code I've tried, and I always end up with the #Value.

I really do appreciate your attempts to help me.

Tony


-------------
import org.apache.poi.hssf.usermodel.*;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Random;

/**
 */
public class BorkedFormula
{
    public static void main(String[] args)
    {
        final File outfile = new File("/tmp/borked.xls");
        if (outfile.exists())
        {
            outfile.delete();
        }
       
        final HSSFWorkbook workbook = new HSSFWorkbook();
        final HSSFSheet sheet = workbook.createSheet("Borked");

        final HSSFFormulaEvaluator evaluator = new 
HSSFFormulaEvaluator(sheet, workbook);

        final Random random = new Random(System.currentTimeMillis());

        HSSFRow row;
        HSSFCell cell;

        for (int i = 0; i < 10; i++)
        {
            row = sheet.createRow(i);

            for (short col = 0; col < 2; col++)
            {
                double val = random.nextDouble() * 100;
                cell = row.createCell(col);
                cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                cell.setCellValue(val);
            }
        }

        row = sheet.createRow(10);
        evaluator.setCurrentRow(row);
        cell = row.createCell((short)0);
        cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
        cell.setCellFormula("SUM(A1:A10)");
        evaluator.evaluateFormulaCell(cell);

        cell = row.createCell((short)1);
        cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
        cell.setCellFormula("SUM(B1:B10)");
        evaluator.evaluateFormulaCell(cell);

        row = sheet.createRow(11);
        evaluator.setCurrentRow(row);
        cell = row.createCell((short)1);
        cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
        cell.setCellFormula("IF(B11=0,0,A11/B11)");
        evaluator.evaluateFormulaCell(cell);

        HSSFFormulaEvaluator.evaluateAllFormulaCells(workbook);
       
        FileOutputStream fos = null;

        try
        {
            fos = new FileOutputStream(outfile);
            workbook.write(fos);
        }
        catch (IOException e)
        {
            System.out.println("Whoops: " + e.getMessage());
            System.exit(8);
        }
        finally
        {
            try
            {
                if (fos != null)
                {
                    fos.close();
                }
            }
            catch (IOException e)
            {
                System.out.println("Closing the stream failed, we have 
issues.");
                System.exit(9);
            }
        }

        System.exit(0);
    }
}

Re: #Value in MS Office - MAY have found an answer.

[Tony Nelson <tn...@starpoint.com>]

On Sep 10, 2008, at 11:06 AM, Tony Nelson wrote:

> Anthony Andrews wrote:
>> Morning Tony,
>>
>> Found a fix that appears to be in both 3.1 final and 3.5 beta 1  
>> archives. There is a static method you can use like this;
>>
>> [snip]

> I wish I could replicate your results.  Unfortunately, this still  
> didn't fix my problem.  I will try the head branch from SVN today,  
> if I can figure out how to compile it ;)
>
> Here the lastest code I've tried, and I always end up with the #Value.
>
> I really do appreciate your attempts to help me.
>
> Tony
>
>

Good news.. testing against SVN trunk fixes every issue.  The original  
code snippet I sent in works fine.  No need to use the evaluator at  
all..

So.. any ideas when we can expect a new release?

Thanks again for everyones help.

Tony


---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org