You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by "Elsberry, Richard C - Eagan, MN - Contractor" <Ri...@usps.gov> on 2004/12/21 22:55:43 UTC
Rookie formula problem!
Hello,
I've searched the archives and have come close but still need some
assistance. I am working on a project that requires the use of an excel
spreadsheet that contains formulas with some rather longstanding and
complex business rules. I need to use this spreadsheet as a template
that accepts user input to perform calculations to other areas of the
spreadsheet. I need to then display the outcome from those other areas
back to the user.
I've created a simple template to test. A worksheet with three rows. Row
three is the sum of rows one and two. (1+1=2) I've attached my code
below. I first print out the values, then add a numeric value 5 to row
two and save the result to a new .xls file. If I open the newly created
spreadsheet manually all looks fine, row 3 equals 6 (1+5=6). However, as
my output below indicates, the new .xls document always reflects the
original .xls document's value of 2 (1+1=2)
I can't read any calculated cells.
In addition when I run this in my actual template I get this error:
[WARNING] Unknown Ptg 18 (24) at cell (66,4)
thanks in advance for any pointers and happy holidays.....
public void updateSpreadSheet(String file, String newFile)
{
try
{
POIFSFileSystem fs = new POIFSFileSystem(new
FileInputStream(file));
HSSFWorkbook wb = new HSSFWorkbook(fs);
FileOutputStream stream = new
FileOutputStream(newFile);
HSSFSheet sheet = wb.getSheetAt(0);
HSSFRow row = sheet.getRow(1);
HSSFCell cell = row.getCell(( short ) 0);
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(5);
wb.write(stream);
stream.close();
System.err.println("update done");
}
catch(IOException ioe)
{
System.out.println("ExcelDAO: error loading
spreadsheet");
}
}
public void readSpreadSheet(String file, boolean b)
{
try
{
double value1 = 0.1;
double value2 = 0.1;
double value3 = 0.1;
POIFSFileSystem fs = new POIFSFileSystem(new
FileInputStream(file));
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
HSSFRow row1 = sheet.getRow(0);
HSSFRow row2 = sheet.getRow(1);
HSSFRow row3 = sheet.getRow(2);
HSSFCell cell1 = row1.getCell(( short ) 0);
value1 = cell1.getNumericCellValue();
System.err.println("row1 = "+value1);
HSSFCell cell2 = row2.getCell(( short ) 0);
value2 = cell2.getNumericCellValue();
System.err.println("row2 = "+value2);
HSSFCell cell3 = row3.getCell(( short ) 0);
value3 = cell3.getNumericCellValue();
System.err.println("row3 = "+value3);
}
catch(IOException ioe)
{
System.out.println("ExcelDAO: error loading
spreadsheet");
}
}
LOG OUTPUT BEFORE UPDATE (row3 is the sum of rows 1 and 2)
row1 = 1.0
row2 = 1.0
row3 = 2.0
update done
LOG OUTPUT AFTER UPDATE (row3 is the sum of rows 1 and 2)
row1 = 1.0
row2 = 5.0
row3 = 2.0
Re: Rookie formula problem!
Posted by Andrew Kharchuk <ak...@ukraina.com>.
Hi,
POI doesn't calculate formula values. Excel does it.
So all is right.
Best regards,
Andrew
----- Original Message -----
From: "Elsberry, Richard C - Eagan, MN - Contractor"
<Ri...@usps.gov>
To: <po...@jakarta.apache.org>
Sent: Tuesday, December 21, 2004 11:55 PM
Subject: Rookie formula problem!
Hello,
I've searched the archives and have come close but still need some
assistance. I am working on a project that requires the use of an excel
spreadsheet that contains formulas with some rather longstanding and
complex business rules. I need to use this spreadsheet as a template
that accepts user input to perform calculations to other areas of the
spreadsheet. I need to then display the outcome from those other areas
back to the user.
I've created a simple template to test. A worksheet with three rows. Row
three is the sum of rows one and two. (1+1=2) I've attached my code
below. I first print out the values, then add a numeric value 5 to row
two and save the result to a new .xls file. If I open the newly created
spreadsheet manually all looks fine, row 3 equals 6 (1+5=6). However, as
my output below indicates, the new .xls document always reflects the
original .xls document's value of 2 (1+1=2)
I can't read any calculated cells.
In addition when I run this in my actual template I get this error:
[WARNING] Unknown Ptg 18 (24) at cell (66,4)
thanks in advance for any pointers and happy holidays.....
public void updateSpreadSheet(String file, String newFile)
{
try
{
POIFSFileSystem fs = new POIFSFileSystem(new
FileInputStream(file));
HSSFWorkbook wb = new HSSFWorkbook(fs);
FileOutputStream stream = new
FileOutputStream(newFile);
HSSFSheet sheet = wb.getSheetAt(0);
HSSFRow row = sheet.getRow(1);
HSSFCell cell = row.getCell(( short ) 0);
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(5);
wb.write(stream);
stream.close();
System.err.println("update done");
}
catch(IOException ioe)
{
System.out.println("ExcelDAO: error loading
spreadsheet");
}
}
public void readSpreadSheet(String file, boolean b)
{
try
{
double value1 = 0.1;
double value2 = 0.1;
double value3 = 0.1;
POIFSFileSystem fs = new POIFSFileSystem(new
FileInputStream(file));
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
HSSFRow row1 = sheet.getRow(0);
HSSFRow row2 = sheet.getRow(1);
HSSFRow row3 = sheet.getRow(2);
HSSFCell cell1 = row1.getCell(( short ) 0);
value1 = cell1.getNumericCellValue();
System.err.println("row1 = "+value1);
HSSFCell cell2 = row2.getCell(( short ) 0);
value2 = cell2.getNumericCellValue();
System.err.println("row2 = "+value2);
HSSFCell cell3 = row3.getCell(( short ) 0);
value3 = cell3.getNumericCellValue();
System.err.println("row3 = "+value3);
}
catch(IOException ioe)
{
System.out.println("ExcelDAO: error loading
spreadsheet");
}
}
LOG OUTPUT BEFORE UPDATE (row3 is the sum of rows 1 and 2)
row1 = 1.0
row2 = 1.0
row3 = 2.0
update done
LOG OUTPUT AFTER UPDATE (row3 is the sum of rows 1 and 2)
row1 = 1.0
row2 = 5.0
row3 = 2.0
---------------------------------------------------------------------
To unsubscribe, e-mail: poi-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: poi-user-help@jakarta.apache.org