Hi folks,
I have been working on this problem for quite some time and haven't
gotten anywhere with it. The situation is quite critical and urgent.
I'm using Apache POIHSSF usermodel. I have written a program which produces
a
JTable of certain data and then exports this into a preexisting excel
spreadsheet based on user interaction from the GUI. My spreadsheet has
certain cells with preexisting formulas which rely on the values of
other cells that the program is writing to. There is one column in
particular in the excel spreadsheet, with dates (preset for dates in
excel) that initiates all the calculations. In other words, once the
spreadsheet realizes a certain row has a date, it realizes that row
must be a new entry and specific cells begin to evaluate formulas.
Here is the problem: The values do show up correctly in the excel
spreadsheet, but the formulas are not calculated. When I double
click on one of the date cells (any of them), and then click on
another cell or press enter (as if I just edited the cell), the
formulas kick in and values are computed. However, not all the
formulas work and certain cells in another sheet of the workbook,
with formulas that use cells from the first worksheet, show #VALUE. I set my
dates EXACTLY the
way they do it in the POI examples. I write to the file EXACTLY the
way they do it as well. Someone had mentioned to rewrite all the cells
to their current values once the data is there (refresh the cells). I tried
this and
nothing changes.
Here is how I set a cell's date:
public void setCellDateValue(String value, int row, int col)
{
try
{
HSSFRow sheetRow;
HSSFCell cell;
Calendar calendar;
String tokens[] = value.replaceFirst("^s+", "").split("/");
sheetRow = sheet.getRow(row);
cell = sheetRow.getCell((short) col);
if ( (tokens[2].charAt(0)) == '0' )
tokens[2] = ("20".concat(tokens[2]));
else
tokens[2] += ("19".concat(tokens[2]));
calendar = new GregorianCalendar(Integer.parseInt(tokens[2]),
Integer.parseInt(tokens[0]), Integer.parseInt(tokens[1])  1);
HSSFCellStyle cellStyle = workBook.createCellStyle();
cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));
cell.setCellValue(calendar);
cell.setCellStyle(cellStyle);
outputStream = new FileOutputStream("C:test.xls");
workBook.write(outputStream);
outputStream.close();
}
catch (FileNotFoundException e)
{
System.out.println("file not found\n");
e.printStackTrace();
}
catch (IOException e)
{
System.out.println("IO Exception\n");
}
}
Any ideas? Thanks so much.
