2.1 Configure Your Environment
This example assumes that you have correctly installed
J-Integra®.
- JDK 1.8 in C:\Program Files\Java\jdk1.8.0_25
- Microsoft Excel 2016 in C:\Program Files(x86)\Microsoft Office\root\Office16\EXCEL.EXE
- J-Integra® 64.02 in E:\jintegrarelease
- The J-Integra® license.
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:
Example code:
mkdir pure
cd pure
mkdir excel
set JAVA_HOME=C:\Program Files\Java\jdk1.8.0_25
set JINTEGRA_HOME=E:\jintegrarelease
set THIS_DIR=E:\pure
set PATH=%SystemRoot%;%SystemRoot%\system32;%JAVA_HOME%\bin;%JINTEGRA_HOME%\bin
set CLASSPATH=%THIS_DIR%;%JINTEGRA_HOME%\lib\jintegra.jar
Output:
2.2 Generate the Java Proxies for Excel
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(x86)\Microsoft Office\root\Office16\EXCEL.EXE
Enter 'E:\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
- click on the Generate Proxies button.
The
com2java program will generate:
- A Java interface and proxy class for each COM interface.
- A Java class for each COM class.
- A Java interface for each ENUM (a set of constant definitions).
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).
2.3 Create and Compile the Example Pure Java Client
This example is based on Excel 2016 on Windows 10. If you are using
Create the file E:\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();
}
}
}
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 ExcelExample.java command.
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.
2.4 Run the Example
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:
2.5 Bonus Step - Subscribe to Excel Events
The Excel Application component
implements the _Application COM Interface.
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):
