You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Faraz Rozi <fr...@gmail.com> on 2008/03/07 20:14:46 UTC
Regarding Writing to existing excel worksheet using HSSF usermodel
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 POI-HSSF usermodel. I have written a program which produces
a
JTable of certain data and then exports this into a pre-existing excel
spreadsheet based on user interaction from the GUI. My spreadsheet has
certain cells with pre-existing 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.
Re: Regarding Writing to existing excel worksheet using HSSF
usermodel
Posted by Nick Burch <ni...@torchbox.com>.
On Thu, 13 Mar 2008, Faraz Rozi wrote:
> It should be noted that the scratchpad jar file needs to be added to the
> project. Why such a critical feature such as cell evaluation (one that
> has its own page per the link above) is not included in the main POI jar
> file I don't know. However, I'm grateful that it's at least in the
> scratchpad. Thanks again.
Formula evaluation is fairly new, so was in scratchpad while it was being
developed. It ought to be moving into the main jar before the next release
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
Re: Regarding Writing to existing excel worksheet using HSSF usermodel
Posted by Faraz Rozi <fr...@gmail.com>.
Thank you so much Nick. That fixed my problem perfectly. I was simply not
aware that the formulas need to be manually recalculated after the values
are entered. I think this would have been great information to include in
the HSSF documentation. The code for recalculating the formulas can be found
here: http://poi.apache.org/hssf/eval.html . It should be noted that the
scratchpad jar file needs to be added to the project. Why such a critical
feature such as cell evaluation (one that has its own page per the link
above) is not included in the main POI jar file I don't know. However, I'm
grateful that it's at least in the scratchpad. Thanks again.
Faraz
On Tue, Mar 11, 2008 at 8:30 AM, Nick Burch <ni...@torchbox.com> wrote:
> On Fri, 7 Mar 2008, Faraz Rozi wrote:
> > 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 POI-HSSF usermodel. I have written a program
> > which produces a JTable of certain data and then exports this into a
> > pre-existing excel spreadsheet based on user interaction from the GUI.
> > My spreadsheet has certain cells with pre-existing formulas which rely
> > on the values of other cells that the program is writing to.
>
> Are you running a full formula evaluation once you're done with the
> jtable? That'd be my suggestion. Simply changing the values in dependent
> cells isn't enough, you'll still need to tell poi to go and re-calculate
> the formula cells for you
>
> Nick
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
Re: Regarding Writing to existing excel worksheet using HSSF
usermodel
Posted by Nick Burch <ni...@torchbox.com>.
On Fri, 7 Mar 2008, Faraz Rozi wrote:
> 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 POI-HSSF usermodel. I have written a program
> which produces a JTable of certain data and then exports this into a
> pre-existing excel spreadsheet based on user interaction from the GUI.
> My spreadsheet has certain cells with pre-existing formulas which rely
> on the values of other cells that the program is writing to.
Are you running a full formula evaluation once you're done with the
jtable? That'd be my suggestion. Simply changing the values in dependent
cells isn't enough, you'll still need to tell poi to go and re-calculate
the formula cells for you
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org