Accessing Excel from Java

Accessing Excel from Java

Java/J2EE COM Interoperability Products Page

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.

Contents

  1. Introduction
  2. Run the Java Client on Local Windows Machine
    1. Configure Your Environment
    2. Generate the Java Proxies for Excel
    3. Create and Compile the Example Pure Java Client
    4. Run the Example
    5. Bonus Step -- Subscribe to Excel Events
  3. Run the Java Client on Remote Non-Windows Platforms
  4. More about Excel Programming

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

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:

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\Microsoft Office\OFFICE11\EXCEL.EXE:

    Accessing Excel from Java: Select Excel type library

  4. Enter 'c:\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.
com2java will generate:

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 XP/2003. If you are using Excel 97 or Excel 2000, there may be subtle differences in the APIs.

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

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

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