Hello,
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,
Richard
