poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Anthony Andrews <pythonadd...@yahoo.com>
Subject Re: #Value in MS Office - Ignore my last message, you tried this already.
Date Mon, 08 Sep 2008 16:15:35 GMT


--- On Mon, 9/8/08, Anthony Andrews <pythonaddict@yahoo.com> wrote:
From: Anthony Andrews <pythonaddict@yahoo.com>
Subject: Re: #Value in MS Office
To: "POI Users List" <user@poi.apache.org>
Date: Monday, September 8, 2008, 9:09 AM

Hello Tony,

Sorry to butt in on your conversation with Nick but there is one other option
you could try if I understand the problem correctly.

As I understand it, when you open the generated workbook using Excel, you do
not see the correct result of the formulae displayed. If this is the case then
the other option that I am guessing Nick alluded to is to call the
setForceFormulaRecalculation() method on the HSSFSheet instance. If you pass a
boolean value of true to this method then it should force Excel to calculate the
formulae before the sheet is displayed.

--- On Mon, 9/8/08, Tony Nelson <tnelson@starpoint.com> wrote:
From: Tony Nelson <tnelson@starpoint.com>
Subject: Re: #Value in MS Office
To: "POI Users List" <user@poi.apache.org>
Date: Monday, September 8, 2008, 8:26 AM

Nick Burch wrote:
> On Mon, 8 Sep 2008, Tony Nelson wrote:
>> I recently upgraded from an older version (2.1) to 3.1 final, and 
>> have found a small problem in one of my spreadsheets that I
haven't 
>> been able to fix. Unlike most problems I can reproduce it rather
easily.
>
> There was a bug with some formula calculations in 3.1 final. I think 
> Yegor's planning a new beta release very soon now.
>
> However, you're not re-calculating the formulas, which won't help 
> either. See http://poi.apache.org/hssf/eval.html
>
I updated my test code to call evaluator.evaluateFormulaCell(cell) as 
shown below.  This did not fix the problem.

Thanks again
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);

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


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




      


      
Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message