Accessing Excel from Java
Accessing Excel from Java |
|
This example demonstrates how to access the Microsoft Excel API from Java. J-Integra® for COM is a Java interoperability component that bridges Java and Microsoft Excel. It provides bi-directional access of Java objects and COM components.
You may read this example simply to get a feel for how easy it is to use J-Integra® to access existing applications, however we urge you to try out this example. Even if you are not familiar with Java, give it a go!
If you do wish to try out this example, you should first download and install the Java Developers Kit (1.2.x and upwards is supported). Note also that a large quantity of Java files are automatically generated from Excel in this example; compiling them takes quite a long time, and should probably only be attempted on a machine with more than 32MB of memory.
You should also download and install J-Integra®.
This example will require access to a machine that is running Windows NT or Windows 95/98, and has Excel installed on it, in order to generate the pure Java proxies which can then be used to access Excel from local Windows machine or from any remote machine that supports a standard Java Virtual Machine (JVM), such as UNIX, Linux, Solaris, Windows, Mac OS and etc. This proxy generation step need only be done once.
This example assumes that you have correctly installed J-Integra®.
the JDK under C:\java\j2sdk1.4.2
Excel under C:\Program Files\Microsoft Office,
We will be performing this example under C:\pure. Create that directory, and an excel directory under it. Update your path environment variable to include the JDK and J-Integra® bin directories, and update your CLASSPATH environment variable to include the J-Integra® runtime:
Type Libraries contain information about COM Components. This example is based on EXCEL.EXE which is the type library for Excel XP. The name of the type library depends on the version of Excel. For instance, use EXCEL9.OLB if you are using Excel 97. We will be using the information contained in Excel's type library to generate pure Java proxies which can be used to access Excel from a standard JVM.
Start the com2java tool:
If you get an error about the command not being found, check that you set your path environment variable correctly above.
Click "..." next to the Type Library text field to select the type library to be processed:
Select Excel's Type Library, which is located in C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE:
Enter 'c:\pure' in the Output Dir field.
Enter 'excel' in the package field
Select Options from the Settings Menu and verify that the correct options have been set
Take a look at some of the generated files to get a feel for what is being generated.
You will notice that comments are included in the generated files in order to make them easier to understand. You may wish to use the javadoc documentation generation tool on the files (the comments are compatible with that tool).
This example is based on Excel XP/2003. If you are using Excel 97 or Excel 2000, there may be subtle differences in the APIs.
Create the file c:\pure\ExcelExample.java, by cutting and pasting from your Web browser.
import excel.*;
import java.util.Date;
public class ExcelExample {
public static void main(java.lang.String[] args) {
try {
// Create a jintegra.log file in the same directory as this Java file
com.linar.jintegra.Log.logImmediately(3, "jintegra.log");
// Uncomment this line if ExcelExample.java remotely accesses Excel:
// com.linar.jintegra.AuthInfo.setDefault("NT DOMAIN", "NT USER", "NT PASSWORD");
// Uncomment this line if ExcelExample.java remotely accesses Excel
// Pass IP Address of remote machine to its constructor
// Application app = new Application("123.456.78.9");
// Create an instance of Excel.Application
Application app = new Application();
app.setVisible(true); // Nice to see what is happening
// Use Excel objects to get at a range in the displayed Worksheet
Workbooks workbooks = app.getWorkbooks();
Workbook workbook = workbooks.add(null);
Sheets worksheets = workbook.getWorksheets();
Worksheet sheet = new Worksheet(worksheets.add(null, null, null, null));
Range range = sheet.getRange("A1:C3", null);
// New contents for the range -- notice the standard Java types
Object[][] newValue = {
{ "defe", new Boolean(false), new Double(98765.0/12345.0)},
{ new Date(), new Integer(5454), new Float(22.0/7.0) },
{ new Boolean(true), "dffe", new Date() }
};
// For Excel 2000, use: range.setValue(newValue);
// For Excel XP or 2003, use:
range.setValue2(newValue); // Update the spreadsheet
Thread.sleep(10000); // Sleep 10 seconds
// Get the new content of the range
// For Excel 2000, use: Object[][] values = (Object[][])range.getValue();
// For Excel XP or 2003, use:
Object[][] values = (Object[][])range.getValue2();
// Print them out. Again, the values are standard Java types
for(int i = 0; i < values.length; i++){
for(int j = 0; j < values[i].length; j++){
System.out.print(values[i][j] + "\t");
}
System.out.println();
}
//False means don't prompt to save changes
workbook.close(new Boolean(false), null, null);
app.quit();
}catch(Exception e){
e.printStackTrace();
}
finally{
// Release all remote objects that haven't already been garbage collected.
com.linar.jintegra.Cleaner.releaseAll();
}
}
} |
If you are running under Windows 98 or Windows 95, then you will need to modify the example slightly. Replace the initial section with the following. We start up a Worksheet rather than the Application. (We get the Application property using "late binding" since a Worksheet started this way only seems to support IDispatch):
Worksheet tmpSheet = new Worksheet();
Application app = new Application(tmpSheet.getPropertyByName("Application"));
app.setVisible(true); // Nice to see what is happening
// Use Excel objects to get at a range in the displayed Worksheet
... |
Compile the example using the javac -J-mx128m -J-ms128m ExcelExample.java command.
The -J parameters make more memory available to the compiler than it would normally have, since there are a lot of generated files in this example:
If you get a The name specified is not recognized as an internal or external command ... error, then it is likely that you did not set you path environment variable correctly (see the first section of this example)
If you get a ExcelExample.java:1: Package excel not found in import... error, then your CLASSPATH environment variable does not correctly include a dot: "."
If you get a .\excel\Application.java:3: Package com.linar.jintegra not found in import... error, then your CLASSPATH environment variable does not correctly include jintegra.jar
If you get an error like error: File .\excel\_Application.java does not contain type excel._Application as expected. Please adjust the class path so that the file does not appear in the package excel., then it is likely that you did not specify the package excel when generating the proxy files from the type library.
Run the example in J-Integra®'s native mode (you need to use DCOM mode if remotely accessing Excel):
java -DJINTEGRA_NATIVE_MODE ExcelExample
Excel will start up, the values will be set in the spreadsheet, Excel
will be shut down again after ten seconds. During that time, you may wish
to modify a couple of the cells, so that you see differing values being
fetched back by the Java example:
The example displays the content of the same range of cells, perhaps
modified by the user:
The Excel Application component implements the _Application COM Interface (hidden from you by J-Integra®). The Application component has an additional interface associated with it, called AppEvents.
Take a quick look at the AppEvents.java file that was generated. It includes methods such as newWorkbook, sheetSelectionChange, and sheetBeforeDoubleClick. As is often the case with event interfaces, there is an AppEventsAdapter class, which was generated by J-Integra®. This class implements the AppEvents interface, and provides empty implementations for each of the methods in the interface. You can derive your event listener class from this class, and implement the methods you require.
You will also notice that J-Integra® creates classes that contain the parameters to each of the event methods. This is because GUI development tools assume that event methods only have a single parameter, which is the event.
In COM terms, this is a source interface, meaning that the Excel Application component can invoke the methods in that interface on other components, that register with the Excel component.
The generated Application Java class includes two methods -- addAppEventsListener() and removeAppEventsListener(). As a Java developer, you deal with standard Java events, in the way you would expect.
Create the AppListener class that derives from the the AppEventsAdapter class
Add the following class to the end of
your ExcelExample.java file (after the final "}"). The new AppListener
class extends the AppEventsAdapter class, and each method simply displays
the fact that it has been invoked. Right clicks will alternate between
being cancelled, and not being cancelled:
class AppListener extends excel.AppEventsAdapter {
boolean cancelRightClick = true;
public void newWorkbook(AppEventsNewWorkbookEvent theEvent) {
System.out.println("newWorkbook Event");
}
public void sheetSelectionChange(AppEventsSheetSelectionChangeEvent theEvent) {
System.out.println("sheetSelectionChange Event");
}
public void sheetBeforeRightClick(AppEventsSheetBeforeRightClickEvent theEvent) {
System.out.println("sheetBeforeRightClick Event. Cancelling?: " + cancelRightClick);
theEvent.setCancel(cancelRightClick);
cancelRightClick = !cancelRightClick;
}
} |
Register an instance of the AppListener with Excel
The final steps are to register and unregister an AppListener with Excel. Add the following two lines immediately after app.setVisible(true);
AppListener appListener = new AppListener(); app.addAppEventsListener(appListener); |
Add the following line immediately after
Thread.sleep(10000);
app.removeAppEventsListener(appListener); |
Rebuild and re-run the example
If you now recompile (using the javac command), and run the new example, you will now see that Excel is invoking the AppListener to inform it of various events (you can cause some of these events, by selecting text, for example):
If you want to run your Java client on a remote non-Windows machine, then you must do the following (The steps below also apply to remote Windows machine):
J-Integra® is a generic bi-directional bridge between any Java objects and any COM components. We do not provide the documentation of the generated Java proxies since the Java proxies are just mapped from the programming API of the COM component. For more information about Microsoft Excel programming, please refer to the Microsoft Excel VBA Language Reference. It's also easier to find a VB example first, and then covert the VB example to Java program using the reference Mapping VB Code to Java Code. We can assist you if you encounter any difficulty when mapping the VB code to Java code.