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
