Jakarta POI and reading from an open Excel file?

[mrafn]

Gurus,

Here's what I want to do...

I have a spreadsheet open in a running Excel session. I would then
like to run a Java program that will: detect the running Excel
instance, connect to it(?), and read the data at the cursor position in
the active sheet. The filename of the Excel file is unknown at
runtime, it is simply the currently active workbook/worksheet.

I am looking at Jakarta POI HSSF package, but I don't know if I can
share an open file. Or how I can get the filename of a running
application, let alone the current cursor position. Is this even
possible?

Any help or pointers in the right direction would be ~greatly
appreciated.

TIA
-mark

 

[Joan]

Gurus,

Here's what I want to do...

I have a spreadsheet open in a running Excel session. I would
then
like to run a Java program that will: detect the running Excel
instance, connect to it(?), and read the data at the cursor
position in
the active sheet. The filename of the Excel file is unknown at
runtime, it is simply the currently active workbook/worksheet.

I am looking at Jakarta POI HSSF package, but I don't know if I
can
share an open file. Or how I can get the filename of a running
application, let alone the current cursor position. Is this
even
possible?

Any help or pointers in the right direction would be ~greatly
appreciated.

TIA
-mark

I use jExcelAPI on Windows XP and I get an error when I try to
read an excel file
that is already open --> my guess is that you would also with
POI, but it is just a guess.

 

[Chris Uppal]

I have a spreadsheet open in a running Excel session. I would then
like to run a Java program that will: detect the running Excel
instance, connect to it(?), and read the data at the cursor position in
the active sheet. The filename of the Excel file is unknown at
runtime, it is simply the currently active workbook/worksheet.

Then you will /have/ to talk program-to-program. You won't be able to get the
data you want by reading a file since there's a fair chance that the user
hasn't saved the file yet, and anyway the "current cursor position" is unlikely
to be part of the saved file (but with MS Office stuff, one never knows).

You /might/ be able to get an effect similar to what you are looking for by
having the user select and copy the cell to the Windows clipboard, whence (I
think, but I've never looked into it) you can retrieve it from Java. I have no
idea what format the data would turn out to be.

Otherwise, you'll probably have to use COM or DDE just as you would if you were
writing a C++ or VB app. There are Java<->COM bridges available (both free and
commercial); I'd guess that there are Java<->DDE bridges too, but I haven't
come across one myself (but then, I haven't looked).

-- chris

 

[mdR]

Do you know where I can get some sample code that shows how to connect
to the running Excel program, either in C++ or VB? -or are there
examples in the Java<>COM bridges?

regards
-mark

 

[Chris Uppal]

Do you know where I can get some sample code that shows how to connect
to the running Excel program, either in C++ or VB?

I don't know of any personally (not a thing I've ever wanted to do). But...

-or are there examples in the Java<>COM bridges?

I wouldn't be at all surprised. "Talking" to Excel seems to be something of a
favourite example for all sorts of COM-related documentation. (Which I find
irritating since I don't have Excel so I can't follow the examples...)

(Incidentally, Googling for:
java excel ( COM OR DDE )
shows quite a lot of stuff; the adverts specially (sorry, the "sponsored
links") might lead to example code.)

-- chris

 

[jfbriere]

It's easier then you might think.
The way to do it is by using automation.
To do this in JAVA, you must have a JAVA-COM bridge library.
Fortunately, there are some out there.
The one I used is free and open-source: JACOB.
http://danadler.com/jacob/
You can download the latest from Sourceforge:
http://sourceforge.net/projects/jacob-project/
Here is a little example I made to extract the value of one cell in an
already running Excel sheet:

import com.jacob.activeX.ActiveXComponent;
import com.jacob.com.ComThread;
import com.jacob.com.Dispatch;

public class ReadOpenedExcel
{

public static void main(String[] args)
throws Exception
{
new ReadOpenedExcel().exec();
}

void exec()
throws Exception
{
try
{
ComThread.InitMTA();
ActiveXComponent component =
ActiveXComponent.connectToActiveInstance("Excel.Application");

if (component != null)
{
Dispatch comExcel = component.getObject();
Dispatch comRange = Dispatch.get(comExcel,
"ActiveCell").toDispatch();
double value = Dispatch.get(comRange,
"Value").toDouble();

System.out.println("value=" + value);
}
}
finally
{
ComThread.Release();
}
}

}

Regards

 

[mdR]

Thanks everyone! that's the direction I needed.
I dl'ed JCOM...hmmm...the javadoc's appear to be in a foreign language!
I'll take a look at JACOB. From reading the archives here, it
~appears~ JCOM does not have a GetObject method.

thanks again
-mark

 

[j-integra_support]

You can connect to a running instance of Excel using "J-Integra for
COM" and access the entire Excel API. The Java proxies are generated
directly from the Excel type library and have one-to-one mappings. ie,
you can figure out which methods, classes, and interfaces to use based
on VB samples!

Here's how to connect to a running COM object (Excel is used as an
example)...
http://j-integra.intrinsyc.com/support/com/kb/article.asp?article=123167

There are many other Java-Excel examples in the documentation...
http://j-integra.intrinsyc.com/support/com/doc/

For a free evaluation, visit http://j-integra.intrinsyc.com/downloads/

Shane Sauer
J-Integra Interoperability Solutions
http://j-integra.intrinsyc.com/
high performance interop middleware for java, corba, com & .net