poi-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Tony Nelson <tnel...@starpoint.com>
Subject #Value in MS Office
Date Mon, 08 Sep 2008 15:07:46 GMT
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.

A simple spreadsheet w/ 2 columns of double values.  At the bottom of 
each column a sum.  Beneath that a simple division of the two sums with 
a divide by zero check.

Opening the resultant spreadsheet in OpenOffice everything looks fine, 
but opening it in MSOffice, the division cell shows a #Value error.  If 
you select the cell and simply hit enter it fixes itself so without 
changing the formula.

The following is sample code that will generate a spreadsheet that shows 
the problem.  Hopefully, I'm just missing something trivial to fix.

Thank you for your time
Tony Nelson
Starpoint Solutions

--- sample code below

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFCell;

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

        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
View raw message