POI doesn't update cells

I have a simple spreadsheet which totals three values (I'm using this for testing POI before working on a large spreadsheet).

Using JAVA and POI I change one of the cell values. However, when outputting the total of the three values it remains the same. I save the file as another name and open it in Excel. I then see the total updating after the file is opened

Is there a way to force the spreadsheet to recalculate ( I need to read/write to an excel spreadsheet WITHOUT Excel or any other Microsoft installed on the PC)

Or is there an alternative way to do this without POI?
rwallacejAsked:
Who is Participating?
 
CEHJConnect With a Mentor Commented:
You could try the OpenOffice API for Calc too
0
 
CEHJCommented:
I think you're going to have to do the calculations 'manually'
0
 
CEHJCommented:
(Since, as you know, the functionality of auto-calc is macro-like and depends on a script interpreter) A non-intepreted 'dumb' binary file can't do anything without the interpreter. You'll need to do the calcs in code
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
aozarovCommented:
Assuming your "total cell" is a function/formula and not a based on macro (which is not suppoted by POI) I think that it should work for you.
Are you setting the formula cell using: http://jakarta.apache.org/poi/apidocs/org/apache/poi/hssf/usermodel/HSSFCell.html#setCellFormula(java.lang.String)
see http://jakarta.apache.org/poi/hssf/formula.html for more info about formla support.
A stronger (but commercial) library can be found at: http://officewriter.softartisans.com/officewriter-239.aspx
another one (free and I think supports formulas) can be found at: http://www.andykhan.com/jexcelapi/
0
 
CEHJCommented:
>>Are you setting the formula cell using ...

Where is setting a formula mentioned?
0
 
rwallacejAuthor Commented:
aozarov
there are hundreds or formulae in the final spreadsheet I'll be trying to read/write to without Excel, so it wouldn't be practical to set the cell formula all the time using POI. The formulae are all setup in Excel. I was hoping POI managed to use the Excel formulae and calculate the results automatically.

pity office writer is commercial - I need something free!

does http://www.andykhan.com/jexcelapi/ recalculate things about changes are made to the cells, without opening Excel?
0
 
rwallacejAuthor Commented:
I'm going to try that. I'm holiday for ten days, but will try openoffice and see what happens once I get back
0
 
CEHJCommented:
Yes. Download the program and try it out as a user (as opposed to a programmer) first on your spreadsheet if you can
0
 
rwallacejAuthor Commented:
I've done that - the spreadsheet works fine. What's the next step?
0
 
CEHJCommented:
I think checking out the API. I haven't used this personally but it seems to be here:

http://api.openoffice.org/docs/java/ref/overview-summary.html
0
 
CEHJCommented:
:-)

How did you get on?
0
 
rwallacejAuthor Commented:
well....
I've installed OpenOffice and my spreadsheets open ok(ish). They are huge and have some advanced statistics.

Calc can be run in "headless" mode so its not visible to the user and can automatically read values from a database to cells on the spreadsheet. I need the spreadsheet to have "live" data, do the calculations, then output the resulting data. I've reached a sticking point as I can't get a macro that outputs the calclulated cells. I can get the calculations with the Calc engine done so thats progress.

I want to then read the database and display values on a webpage which will be another hurdle as I'm not up to that! Ideas?
0
 
CEHJCommented:
>> Ideas?

Probably wisest to start here:

http://java.sun.com/j2ee/1.4/docs/tutorial/doc/
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.