Meaningless Drivel is fun!
The moose likes Other JSE/JEE APIs and the fly likes POI and Excel formulas Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Other JSE/JEE APIs
Bookmark "POI and Excel formulas" Watch "POI and Excel formulas" New topic

POI and Excel formulas

Richard Elsberry
Ranch Hand

Joined: Mar 14, 2001
Posts: 40

I can't be the only one with this dilemma so I thought I would post it in case others are struggling with the same requirements. Currently I am using POI to open and enter data into a MS Excel spreadsheet. This spreadsheet is being used in a web application in the background as a calculator. Reason being, there are way too many business rules that go into this spreadsheet to be able to realistically convert it into the application.

POI is a great tool and all works fine unless your data is being calculated -- the most important reason for me, and I suspect many others as well.

To read a cell that has been calculated from your entries, the spreadsheet needs to be somehow be opened up in Excel. In my testing I've found that using POI to enter values, stopping the app and opening and closing the spreadsheet in Excel, then reading the data via POI again works fine.

The problem is how to automate opening Excel to run the calcs programmatically. Do I use JNI to access a VB executable? This requires Excel on the server!!

I can't use VBScript in the browser as ActiveX controls are not allowed in our intranet.

There are some commercial products on the market that extend POI that support runtime calculations but only partially. Our business users could realistically enter a formula sometime in the future that isn't supported and breaks the app.

Anyway, I'm going the JNI route. It seems to be the only solution for us. Any thoughts on this would be very welcome. I can't imagine how many others have struggled with this as well.

Happy Holidays,
Sean Sullivan
Ranch Hand

Joined: Sep 09, 2001
Posts: 427
You should ask this question on the poi-user mailing list
Richard Elsberry
Ranch Hand

Joined: Mar 14, 2001
Posts: 40

I had posted a similar question on the poi users site but didn't really get much insight. However, in the meantime I did find another open source solution called JACOB. It's a Java/COM bridge and has been working fine for my needs. It does much more, but all I need to do is open the .xls file and close it to perform the calculations.

I agree. Here's the link:
subject: POI and Excel formulas
It's not a secret anymore!