Home : J-Integra COM for Excel 2016 and Windows 10
Q183259 - HOWTO: J-Integra COM for Excel 2016 and Windows 10
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.

1 Introduction

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.


2 Run the Java Client on Local Windows Machine

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:

Accessing Excel from Java: Update environment variables


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.

  1. Start the com2java tool:

    Accessing Excel from Java: Start com2java

    If you get an error about the command not being found, check that you set your path environment variable correctly above.


  2. Click "..." next to the Type Library text field to select the type library to be processed:

    Accessing Excel from Java: Select type library


  3. Select Excel's Type Library, which is located in C:\Program Files(x86)\Microsoft Office\root\Office16\EXCEL.EXE

    Accessing Excel from Java: Select Excel type library


  4. Enter 'E:\pure' in the Output Dir field.

  5. Enter 'excel' in the package field

  6. Select Options from the Settings Menu and verify that the correct options have been set

  7. 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

  1. 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
                    ...
              
              

  2. Compile the example using the javac ExcelExample.java command.

    Accessing Excel from Java: Compile Java proxies

    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:
Accessing Excel from Java: Run the example

The example displays the content of the same range of cells, perhaps modified by the user:
Accessing Excel from Java: Display output


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.

  1. 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;
               }
              }
              
  2. 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);
              
  3. 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):

    Accessing Excel from Java: Display output with events

3 Running the Java Client on Remote Non-Windows Platforms

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):

  1. Move the com2java tool to the Windows machine to generate the Java proxies of Excel and put them in an Excel.jar file, and then move the Excel.jar file from the Windows to the UNIX machine.
  2. Install J-Integra® (the jintegra.jar and the corresponding Excel.jar) on the non-Windows machine and include the jar files in CLASSPATH.
  3. Use DCOMCNFG to configure the Excel on the Excel Windows machine.
  4. Uncomment the line in the ExcelExample.java:
    com.linar.jintegra.AuthInfo.setDefault("NT DOMAIN", "NT USER", "NT PASSWORD");
    and pass the correct login credentials that are used to access the Window machine.
  5. Uncomment the line in the ExcelExample.java and pass the IP address or the computer name to the constructor of Excel in the ExcelExample.java:
    Application app = new Application("123.456.78.9");
  6. Move ExcelExample.java to the Linux machine. Compile and run it in DCOM mode without using DJINTEGRA_NATIVE_MODE property:
    java ExcelExample

4 More about Excel Programming

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, pleaserefer 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.

Related Articles
No Related Articles Available.

Article Attachments
No Attachments Available.

Related External Links
No Related Links Available.
Help us improve this article...
What did you think of this article?

poor 
1
2
3
4
5
6
7
8
9
10

 excellent
Tell us why you rated the content this way. (optional)
 
Approved Comments...
No user comments available for this article.
Created on 1/19/2017.
Last Modified on 1/19/2017.
Last Modified by J-Integra KB Admin.
Skill Level: Beginner.
Article has been viewed 1018 times.
Rated 8 out of 10 based on 5 votes.
Print Article
Email Article