You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Anthony Andrews <py...@yahoo.com> on 2008/09/08 18:15:35 UTC
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
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