JExcelChart example

Summary

This example demonstrates how to use Java to make a dynamic Microsoft Excel chart. Java and J-Integra® are used to animate a Microsoft Excel chart by cycling the chart’s source data, contained in two columns, before printing the final chart.

Introduction

You will use the Java proxies generated by J-Integra® to to interact with the objects, properties and methods exposed by Microsoft Excel either locally or remotely. This example shows how to access the essential collections and objects of Microsoft Excel, that is, Application, Workbooks, Workbook, Worksheets (which is renamed Sheets in the generated Java proxes), Worksheet, and Range.

You need one computer running Microsoft Windows and Microsoft Office if you want to run this example locally. You need two computers if you want to run this example remotely--in this case at least one computer has to run 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. Office server and Office client can be the same computer--in this case, we do not need to configure DCOM.

Prerequisites

  1. Download and install the Java™ Developers Kit

  2. Download and install J-Integra®

  3. Install Microsoft Excel on the Office server computer.

  4. Ensure that you have you have installed the J-Integra® license.

The Steps Involved

  1. Configure Your Environment

  2. Create and Compile the JExcelChart Sample Code

  3. Run the JExcelChart Example

  4. Remote Access – configure DCOM Access to Excel
  5. More about Excel programming

Configure Your Environment

This example assumes that you run this example on one Microsoft Windows 2000 platform without configuring DCOM, and install:

  1. JDK under C:\jdk1.3.1\bin

  2. Microsoft Excel is installed under C:\Program Files\Microsoft Office\Office

  3. J-Integra® is installed under C:\jintegra

If the above directories are different from your installation, please modify your code and configuration for running this example.

  1. If necessary, configure the environment for JDK according to the JDK documentation.

  2. Next, 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
  3. Update the CLASSPATH to include the JDK, the JExB class libraries and the J-Integra® Java proxies by typing this command at the command prompt: 
    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 and Compile the JExcelChart Sample Code

This article is based on Excel 2000. If you are using Excel 97 or Excel XP, there may be subtle differences in the API.

  1. Create the file JExcelChart.java by copying and pasting the following code from your Web browser, and save this file in jintegra\examples\java-excel\JExcelChart directory. This file is included in your jintegra\examples\java-excel\JExcelChart directory when you download J-Integra®.

    import excel.*;
import java.io.*;

public class JExcelChart {

  public JExcelChart() {
  }

  public static void main(String[] args) {

    try {
      String strHostName      = "localhost";
      String strUserID        = null;
      String strPassword      = null;
      String strMSOfficeHost  = "localhost";

      // set command line parameters
      if (args.length == 4) {
        strHostName     = args[0];
        strUserID       = args[1];
        strPassword     = args[2];
        strMSOfficeHost = args[3];
      } else if (args.length > 0) {
        System.out.println("Usage:  java JExcelChart [hostname] [username] [password] [excelhost]");
        System.exit(1);
      }
      JExcelChart myInstance = new JExcelChart();

      String strExcelFilename = "C:\\jintegra\\examples\\java-excel\\JExcelChart";
      try {
        File f = new File("");
        strExcelFilename = f.getAbsolutePath();
      } catch (Exception ex) {
      }
      strExcelFilename += "\\chart.xls";
      System.out.println("strExcelFilename = " + strExcelFilename + "\n");

      myInstance.testJExcelChart(
        strHostName,
        strUserID,
        strPassword,
        strMSOfficeHost,
        strExcelFilename,
        XlSaveAsAccessMode.xlShared,
        true,
        false
      );

    } finally {
      // Release all remote objects that haven't already been garbage collected.
      com.linar.jintegra.Cleaner.releaseAll();
    }
  }

  /**
  * This function opens an Excel chart on either local machine
  *      or remote machine (By DCOM) which runs Microsoft Office,
  *      changes the chart by changing the source data, and then
  *      prints it out.
  * @param strDomainName - [String] the domain name or host name for DCOM
  *      authentication, null means localhost. You may input host
  *      name for the machine installing and running Microsoft
  *      Office. This parameter will be ignored when strUserID is null.
  * @param strUserID - [String] the user ID for the domain name
  *      or host name for DCOM authentication, null means the
  *      parameters of strDomainName and strUserPassword will be
  *      ignored, i.e. the sample will run at the same machine as the
  *      machine installing and running Microsoft Office.
  * @param strUserPassword - [String] the password for the domain
  *      name or host name for DCOM authentication, null means
  *      empty password. This parameter will be ignored when
  *      strUserID is null.
  * @param strMSOfficeHost - [String] the host name for the host running
  *      Microsoft Office in for DCOM authentication,
  *      null means localhost. This parameter will be ignored when
  *      strUserID is null.
  * @param strWorkbookName - [String] the file name of the chart.
  * @param iOpeningMode - [int] the access mode when new template file is saved.
  *      Can be one of the following constants: XlSaveAsAccessMode.xlShared
  *      (shared list), XlSaveAsAccessMode.xlExclusive (exclusive mode),
  *      or XlSaveAsAccessMode.xlNoChange (don't change the access mode).
  * @param bExcelAppVisible - [boolean] indicates if showing the running
  *      Microsoft Office as visible window when this application is
  *      running. If it is false, Microsoft Office will run in
  *      the background.
  * @param bExcelAppDisplayAlerts - [boolean] indicates if display
  *      alert dialog box when necessary in running this application.
  *      If this is true, the alert dialog box will be displayed and
  *      require user's interaction.
  */
  public static void testJExcelChart(
                                     String strDomainName,
                                     String strUserID,
                                     String strUserPassword,
                                     String strMSOfficeHost,
                                     String strWorkbookName,
                                     int iOpeningMode,
                                     boolean bExcelAppVisible,
                                     boolean bExcelAppDisplayAlerts) {

    try {
      Application xlApp = null;
      Workbooks workbooks = null;
      Workbook workbook = null;
      Worksheet sheet = null;
      Sheets worksheets = null;
      Range range = null;

      //////////////////////////////////////////////
      // DCOM authentication
      if (strUserID != null) {
        if (strDomainName == null) {
          strDomainName = "127.0.0.1";
        }
        if (strUserPassword == null) {
          strUserPassword = "";
        }

        com.linar.jintegra.AuthInfo.setDefault(
          strDomainName,
          strUserID,
          strUserPassword
        );
      }

      //////////////////////////////////////////////
      // operating Application object
      if( strMSOfficeHost != null ) {
        System.out.print("Connecting to " + strMSOfficeHost + ".... ");
        xlApp = new Application(strMSOfficeHost);
      } else {
        System.out.print("Connecting to localhost.... ");
        xlApp = new Application();
      }
      System.out.println("Connected!");

      xlApp.setVisible( bExcelAppVisible );
      xlApp.setDisplayAlerts( bExcelAppDisplayAlerts );

      // Use Excel objects to get at a range in the displayed Worksheet
      workbooks = xlApp.getWorkbooks();

      // Open the excel file
      // For Excel XP, use:
      // workbook = workbooks.open(
      //   strWorkbookName,
      //   new Boolean(true),
      //   new Boolean(true),
      //   null,
      //   null,
      //   null,
      //   null,
      //   null,
      //   null,
      //   null,
      //   null,
      //   null,
      //   null,
      //   null,
      //   null
      // );
      // For Excel 2000, use:
      workbook = workbooks.open(
         strWorkbookName,
         new Boolean(true),
         new Boolean(true),
         null,
         null,
         null,
         null,
         null,
         null,
         null,
         null,
         null,
         null
      );

      // get the worksheet
      worksheets  = workbook.getWorksheets();
      sheet = new Worksheet(worksheets.getItem(new Integer(1)));

      // select the range where the graph data is stored
      range = sheet.getRange("A1:B19", null);

      // New contents for the range -- notice the standard Java types
      Object [][] newValue = {
        { new Integer(121), new Integer(117) },
        { new Integer(111), new Integer(156) },
        { new Integer(132), new Integer(138) },
        { new Integer(116), new Integer(119) },
        { new Integer(148), new Integer(126) },
        { new Integer(163), new Integer(143) },
        { new Integer(174), new Integer(135) },
        { new Integer(136), new Integer(142) },
        { new Integer(142), new Integer(163) },
        { new Integer(121), new Integer(117) },
        { new Integer(111), new Integer(156) },
        { new Integer(132), new Integer(138) },
        { new Integer(116), new Integer(119) },
        { new Integer(148), new Integer(126) },
        { new Integer(163), new Integer(143) },
        { new Integer(174), new Integer(135) },
        { new Integer(136), new Integer(142) },
        { new Integer(142), new Integer(163) },
        { new Integer(121), new Integer(117) }
      };

      // For Excel XP, use: range.setValue2(newValue);
      range.setValue(newValue); // Update the spreadsheet

      for (int j=0; j<60; j++) {
        Thread.sleep(300);
        Object temp1 = newValue[0][0];
        Object temp2 = newValue[0][1];
        int i = 0;
        for (i=1; i<newValue.length; i++) {
          for (int k=0; k<newValue[i-1].length; k++) {
            newValue[i-1][k] = newValue[i][k];
          }
        }

        newValue[i-1][0] = temp1;
        newValue[i-1][1] = temp2;
       // For Excel XP, use: range.setValue2(newValue);
        range.setValue(newValue); // Update the spreadsheet
      }

      // Embed a chart
      ChartObjects chartObjects = new ChartObjectsProxy(sheet.chartObjects(null));
      chartObjects.add(100, 30, 400, 250);
      ChartObject chartObject = new ChartObjectProxy(chartObjects.item(new Integer(1)));
      Chart chart = chartObject.getChart();
      chart.setSourceData(range, null);

      Thread.sleep(5000);

      /////////////////////////////////////////////////////////
      // Print out the worksheet
      printWorkSheet(sheet, XlPageOrientation.xlLandscape, 90);

      // False means don't prompt to save changes
      workbook.close(new Boolean(false), null, null);
      xlApp.quit();
    } catch (Exception e) {
      e.printStackTrace();
    }
  }


  /**
  * Print out a worksheet
  * @param ws - [Worksheet] the worksheet to print out.
  * @param iPageOrientation - [int] the print orientation of
  *      the page, can be one of the following contants:
  *      <ul>
  *          <li>XlPageOrientation.xlPortrait</li>
  *          <li>XlPageOrientation.xlLandscape</li>
  *      </ul>
  * @param iPercentZoom - [int] the percentage to zoom the
  *      worksheet to before printing.
  */
  public static void printWorkSheet (
                                    Worksheet ws,
                                    int iPageOrientation,
                                    int iPercentZoom) {
    try {
      PageSetup pg = ws.getPageSetup();

      pg.setOrientation(iPageOrientation);

      // shrink or enlarge the worksheet size, default is 100%
      pg.setZoom(new Integer(iPercentZoom));

      /////////////////////////////////////////////////////////
      // print out
      ws.printOut(null,null,null,null,null,null,null,null);
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
}

  2. Go to jintegra\examples\java-excel\JExcelChart and compile the source code of Java example by typing the command

    javac -J-mx32m -J-ms32m JExcelChart.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 Java sample code will create the JExcelChart.class will be created in the jintegra\examples\java-excel\JExcelChart directory.

Run the JExcelChart example

  1. Run the example by typing the command

    java JExcelChart

  2. Here is a screen shot of the result in Microsoft Excel:

After the Chart example has finished running it will automatically print the sheet with the final values.

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.

Remote Access–configure DCOM access to Excel

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 JExcelChart.java on a remote UNIX box:

  1. Install J-Integra® (the jintegra.jar and the corresponding Excel.jar) on the UNIX machine.
  2. Configure the DCOM of Excel on the Excel Windows machine.
  3. Compile and pass [hostname] [username] [password] [excelhost] when running the JExcelChart.java on the UNIX machine.

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. The quickest way to start programming Java-to-Excel is to find a VB example first, and then map the VB example to Java.