You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Tony Nelson <tn...@starpoint.com> on 2008/09/08 17:07:46 UTC
#Value in MS Office
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
Re: #Value in MS Office
Posted by Tony Nelson <tn...@starpoint.com>.
On Sep 8, 2008, at 11:23 AM, Nick Burch wrote:
> On Mon, 8 Sep 2008, Tony Nelson wrote:
>> No, I am not recalculating formulas. Should I try it? On new
>> spreadsheets? I am always creating them, I never edit them.
>
> Excel caches the result of the formula along with the formula
> itself. You'll want to populate that cache, so that excel will
> properly display the result before you go and edit the cell.
>
> (Alternately, you can try setting the flag that tells excel that all
> formulas need to be re-calculated, but that seems to be less reliable)
>
I have tried adding.. sheet.setForFormulaRecalculation(true) and that
didn't help either.
Any other suggestions? Or should I roll back to 2.1. This is the
only thing that's holding me up at this point.
Thanks again
Tony
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: #Value in MS Office
Posted by Nick Burch <ni...@torchbox.com>.
On Mon, 8 Sep 2008, Tony Nelson wrote:
> No, I am not recalculating formulas. Should I try it? On new
> spreadsheets? I am always creating them, I never edit them.
Excel caches the result of the formula along with the formula itself.
You'll want to populate that cache, so that excel will properly display
the result before you go and edit the cell.
(Alternately, you can try setting the flag that tells excel that all
formulas need to be re-calculated, but that seems to be less reliable)
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: #Value in MS Office
Posted by Tony Nelson <tn...@starpoint.com>.
On Sep 8, 2008, at 11:13 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
>
Thank you for the quick reply. I'm sorry but I don't understand your
answer.
No, I am not recalculating formulas. Should I try it? On new
spreadsheets? I am always creating them, I never edit them.
Thank You
Tony
Re: #Value in MS Office - Ignore my last message, you tried this
already.
Posted by Nick Burch <ni...@torchbox.com>.
On Mon, 8 Sep 2008, Tony Nelson wrote:
> setForceFormulaRecalulation() didn't fix the problem I am having. Just
> wanted to let ou know.
Have you tried with HSSFFormulaEvaluator and a recent svn checkout /
nightly build? If it still plays up for you with a recent build of poi
(last few days), please open a new bug on bugzilla, along with the code
you're using, so someone can take a look
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: #Value in MS Office - Ignore my last message, you tried this already.
Posted by Tony Nelson <tn...@starpoint.com>.
On Sep 8, 2008, at 5:10 PM, Dobson, Paul L CTR USAF AFMC 416 SCMS/OBN
wrote:
> I'm running into the same problem. A temporary workaround I just
> found
> is pressing ctrl+alt+F9 in excel will automatically recalculate all
> formulas in all sheets of the workbook. At least it gives my users a
> workaround until I get the code fixed with
> setForceFormulaRecalculation().
>
setForceFormulaRecalulation() didn't fix the problem I am having.
Just wanted to let ou know.
Tony
> -----Original Message-----
> From: Anthony Andrews [mailto:pythonaddict@yahoo.com]
> Sent: Monday, September 08, 2008 10:16 AM
> To: POI Users List
> Subject: Re: #Value in MS Office - Ignore my last message, you tried
> this already.
>
>
>
> --- On Mon, 9/8/08, Anthony Andrews <py...@yahoo.com> wrote:
> From: Anthony Andrews <py...@yahoo.com>
> Subject: Re: #Value in MS Office
> To: "POI Users List" <us...@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 <tn...@starpoint.com> wrote:
> From: Tony Nelson <tn...@starpoint.com>
> Subject: Re: #Value in MS Office
> To: "POI Users List" <us...@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
>
>
>
>
>
>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
RE: #Value in MS Office - Ignore my last message, you tried this already.
Posted by "Dobson, Paul L CTR USAF AFMC 416 SCMS/OBN" <Pa...@HILL.af.mil>.
I'm running into the same problem. A temporary workaround I just found
is pressing ctrl+alt+F9 in excel will automatically recalculate all
formulas in all sheets of the workbook. At least it gives my users a
workaround until I get the code fixed with
setForceFormulaRecalculation().
-----Original Message-----
From: Anthony Andrews [mailto:pythonaddict@yahoo.com]
Sent: Monday, September 08, 2008 10:16 AM
To: POI Users List
Subject: Re: #Value in MS Office - Ignore my last message, you tried
this already.
--- On Mon, 9/8/08, Anthony Andrews <py...@yahoo.com> wrote:
From: Anthony Andrews <py...@yahoo.com>
Subject: Re: #Value in MS Office
To: "POI Users List" <us...@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 <tn...@starpoint.com> wrote:
From: Tony Nelson <tn...@starpoint.com>
Subject: Re: #Value in MS Office
To: "POI Users List" <us...@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
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: #Value in MS Office - Ignore my last message, you tried this already.
Posted by Anthony Andrews <py...@yahoo.com>.
--- On Mon, 9/8/08, Anthony Andrews <py...@yahoo.com> wrote:
From: Anthony Andrews <py...@yahoo.com>
Subject: Re: #Value in MS Office
To: "POI Users List" <us...@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 <tn...@starpoint.com> wrote:
From: Tony Nelson <tn...@starpoint.com>
Subject: Re: #Value in MS Office
To: "POI Users List" <us...@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
Re: #Value in MS Office
Posted by Anthony Andrews <py...@yahoo.com>.
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 <tn...@starpoint.com> wrote:
From: Tony Nelson <tn...@starpoint.com>
Subject: Re: #Value in MS Office
To: "POI Users List" <us...@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
Re: #Value in MS Office
Posted by Tony Nelson <tn...@starpoint.com>.
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
Re: #Value in MS Office
Posted by Nick Burch <ni...@torchbox.com>.
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
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org