• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 781
  • Last Modified:

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?
0
rwallacej
Asked:
rwallacej
  • 8
  • 4
1 Solution
 
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
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
CEHJCommented:
You could try the OpenOffice API for Calc too
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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 8
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now