Linking Java to Excel

[jim swarthout]

Hi. I'm trying to get a java program to read from and write to a live excel spreadsheet (i.e. if the user changes a cell in excel, the java program is informed immediately of the new value and if the java program wants to poke a new value into a particular cell, it can do so immediately as well). My programmers tell me that this cannot be done easily. Is this the case? Any guidance would be most appreciated. Thanks.

[Ryan McGuire]

Originally posted by jim swarthout:
Hi. I'm trying to get a java program to read from and write to a live excel spreadsheet (i.e. if the user changes a cell in excel, the java program is informed immediately of the new value and if the java program wants to poke a new value into a particular cell, it can do so immediately as well). My programmers tell me that this cannot be done easily. Is this the case? Any guidance would be most appreciated. Thanks.

Which are you unsure of: your programmer's honesty, competence or enthusiasm?

Having a Java program interact with a running instance of Excel would be quite tricky indeed. I'd think they would have to write a VBA program to run in Excel that...

Establishes some kind of inter-process communication (a TCP/IP connection?) with the Java program.

Watches for cell value changes and sends them out to the Java program

Listens for modification requests from the Java program and makes those changes.

And of course, the Java program at the other end of this comm channel would also have to be made.

I'm no VBA expert, so I'm not positive that this solution is even possible. What the heck, let one of your programmers take a couple days to find out. (The deliverable is not a working program but rather the INVESTIGATION of whether it's actually possible to make the tricky parts work.)

Are Excel and Java really part of the requirements? Could you get away with using a Java-based gui and saving files in a format other than Excel? In that case, an all Java solution is a little easier.

[Paul Wong]

Check out Apache POI. It's an open source API to read and write to Microsoft Format Files.
As for detecting if a cell changed in the Excel file, maybe run a deamon thread which checks for the date of the Excel file. Then, when the date has changed, do whatever you wish to the Excel file with Apache POI.
My experiences with POI has been pretty good so far maybe because the spreadsheets were pretty simple.
Good Luck!
[ August 04, 2005: Message edited by: Paul Wong ]

[Ulf Dittmer]

On the Mac, you could use AppleScript to drive Excel from within Java and vice-versa, and it wouldn't even be too hard.

On Windows, I would imagine that WSH (Windows Scripting Host from MS) allows a similar degree of interaction, particularly since it uses VBScript which is kind of the same as VBA (VB for Applications - Excels scripting languages). The trickiest part would be the Java <--> VBScript interaction, but Jakarta BSF can handle that.

I don't think that POI or JExcelApi can help you, because they assume you're working with Excel files, not a running program. They are very useful for what they do, though.

I'm interested in this kind of integration, so let me know how you make out, or if you hit any snags.
[ August 05, 2005: Message edited by: Ulf Dittmer ]

[Jonathan Newell]

This solution may not be practical for you, as it does cost a bit of money (in the few hundred dollars range), but if you are really talking about connecting to a 'live' excel spreadsheet, and you're prepared to spend a bit of money to do it, then your best bet may be to actually talk to the excel process using DDE.

I've done this using some software from Neva Object (it's not free but it is very good) - take a look at this document which gives an example of a java process watching for changes in an excel spreadsheet:

JavaDde

Hope this helps