This example shows you how to use J-Integra®, to create a new workbook and worksheet, write data to it and open Microsoft Excel to display the progress.
You will use J-Integra® to interact with the objects, properties and methods exposed by Microsoft Excel either locally or remotely. This example shows how to access essential Microsoft Excel collections and objects; that is, Application, Workbooks, Workbook, Worksheets (renamed Sheets in the J-Integra®), Worksheet, and Range.
If you want to run this example locally, you need one computer running Microsoft Windows and Microsoft Office. If you want to run this example remotely, you need two computers, one of which is running Microsoft Windows and Microsoft Office.
The computer running Microsoft Windows and Microsoft Office is referred to as the Office server. The computer running this example is referred to as the Office client. If you are running this example locally, the Office server and Office client are the same computer. By default, this example assumes that you are running locally, and DCOM need not be configured.
Install Microsoft Office (Microsoft Office 2000 is recommended.) on the Office server.
Download and install the Java™ Developers Kit on the Office server and the Office client.
Download and install J-Integra® on the Office server and the Office client.
Read the Microsoft Excel 2000 Java Programmer's Guide.
Ensure that you have installed the J-Integra® license.
This example assumes that you have the following configuration:
If the above directories are different from your installation, please modify your code and configuration for running this example.
If necessary, configure the environment for JDK according to the JDK documentation.
Set up the path for the J-Integra®. To set the PATH environment variable in the J-Integra® bin directory, type this command at the command prompt:
set PATH=%PATH%;C:\jintegra\bin
set CLASSPATH=%CLASSPATH%;C:\jintegra\lib\jintegra.jar; set CLASSPATH=%CLASSPATH%;C:\jintegra\lib\excel2000.jar;Note: if you have Excel 97 on your computer, replace the second line with:
set CLASSPATH=%CLASSPATH%;C:\jintegra\lib\excel97.jar;if you have Excel XP on your computer, replace the second line with:
set CLASSPATH=%CLASSPATH%;C:\jintegra\lib\excelXP.jar;
Create the file JSimpleExcel.java by copying and pasting the following code from your Web browser, and save this file in jintegra\examples\java-excel\JSimpleExcel directory. This file is included in your jintegra\examples\java-excel\JSimpleExcel directory when you download J-Integra®.
import excel.*;
import java.util.Date;
public class JSimpleExcel {
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");
// Create an instance of Excel.Applicaiton on the remote Windows machine
// using its IP address
// com.linar.jintegra.AuthInfo.setDefault("NT DOMAIN", "NT USER", "NT PASSWORD");
// Application app = new Application("123.456.78.9");
// Create an instance of Excel.Application on the local Windows machine
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 XP, use: range.setValue2(newValue);
// For Excel 2000, use:
range.setValue(newValue); //Use setValue2 for Excel XP/2003
Thread.sleep(10000); // Sleep 10 seconds
// maybe you want to change a value in the spreadsheet
// Get the new content of the range
// For Excel XP/2003, use: (Object[][])range.getValue2();
// For Excel 2000, use:
Object[][] values = (Object[][])range.getValue();
// 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. For these platforms, you must start up a Worksheet rather than an 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 ... |
Go to jintegra\examples\java-excel\JSimpleExcel and run the following command to compile the Java sample code
javac -J-mx32m -J-ms32m JSimpleExcel.java
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.
The JSimpleExcel.class
file will be created in the C:\jintegra\examples\java-excel\JSimpleExcell
directory.
Run the example by typing this command at the command prompt:
java JSimpleExcel
Here is a screen shot of the result in Microsoft Excel:
Here is the result printed in command-line Window:
The command-line output of the cells will reflect any changes that are made while Microsoft Excel is open.
If you get an error like "AutomationException: 0x5 - Access is denied," then make sure that the bin directory is in your PATH so that it can use native code to pick up your login identity.
The Excel Application COM component implements the _Application COM Interface (hidden from you by the 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 the 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 the J-Integra® creates classes that contain the parameters of each of the event methods. This is because GUI development tools assume that event methods only have a single parameter, 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.
To create the AppListener class that derives from the the AppEventsAdapter class, add the following class to the end of your JSimpleExcel.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 voidsheetSelectionChange(AppEventsSheetSelectionChangeEvent theEvent){ System.out.println("sheetSelectionChange Event"); } public voidsheetBeforeRightClick(AppEventsSheetBeforeRightClickEvent theEvent){ System.out.println("sheetBeforeRightClick Event. Cancelling?: "+ cancelRightClick); theEvent.setCancel(cancelRightClick); cancelRightClick = !cancelRightClick; } } |
The final steps are to register and unregister an AppListener with Excel. Add the following two lines immediately after app.setVisible(true);
// register an AppListener with Excel AppListener appListener = newAppListener(); app.addAppEventsListener(appListener); |
Add the following line immediately after Thread.sleep(10000)
// unregister an AppListener with Excel
app.removeAppEventsListener(appListener)
|
Now recompile by typing
javac -J-mx32m -J-ms32m JSimpleExcel.java
Run the new example. You will 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):
When running under Windows, J-Integra® can use native code to pick up your current login identity. If you would rather that J-Integra® did not do this, or if your Java client is on another remote machine of any OS, then you must configure DCOM access to Excel or to other COM components, and you must specify the NT domain, user and password to be used by J-Integra®.
Proceed to Configuring DCOM
If you are running Windows XP (currently Release Candidate version, Build 2526) on the Office server, configuring DCOM is slightly different because DCOM configuration is integrated with Component Services.
Proceed to Configuring DCOM for Windows XP
For example, to run the ExcelExample.java on a remote UNIX box:
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. The quickest way to start programming Java-to-Excel is to find a VB example first, and then map the VB example to Java.