JExcelGuide Example

Summary

This example shows you how to use Java to process regular tasks with Microsoft Excel via J-Integra® and the Excel JAPI generated proxy classes.

Introduction

You will use Excel JAPI 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 Excel JAPI), Worksheet, and Range.

If you want to run this example locally, you need one computer running Microsoft Windows and Microsoft Office. Because this example is focused on how to understand and use the Microsoft Excel object model, and serves as a supplement of Microsoft Excel 2000 Java Programmer’s Guide, accessing a computer running Microsoft Office remotely via DCOM mode is beyond the scope of this document. If you are interested in that topic, please review the Java Excel Expense Template Example.

Prerequisites

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.

The Steps Involved

Configure Your Environment
Create and Compile the JExcelGuide Sample Code
Run the JExcelGuide Example
Remote Access to the Excel Workbook
More about Excel Programming

Configure Your Environment

This example assumes that you have the following configuration:

Operating System: Windows 2000
Microsoft Excel installed under C:\Program Files\Microsoft Office\Office
JDK installed under C:\jdk1.3.1\bin
Excel JAPI installed under C:\jintegra

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 Excel JAPI. 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
```
Update the CLASSPATH to include the JDK and the Excel JAPI class libraries 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 JExcelGuide Sample Code

This document is based on Microsoft Excel 2000 API. If you are using Microsoft Excel 97, or Microsoft Excel XP, please refer to API Differences Between Excel 97, 2000 & XPto learn about the API differences between these APIs.

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

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

public class JExcelGuide {

  public JExcelGuide() {
  }

  public static void main(String[] args) {

    try {

      JExcelGuide JExcelGuide1 = new JExcelGuide();

      String strDefaultPath = "C:\\jintegra\\examples\\java-excel\\JExcelGuide";
      try {
        File f = new File("");
        strDefaultPath = f.getAbsolutePath();
      } catch (Exception ex) {
      }

      JExcelGuide1.useApplication(
                                  "MyApplication",
                                  strDefaultPath);

      JExcelGuide1.createWorkbook(
                                  "MyApplication",
                                  strDefaultPath);

      JExcelGuide1.openWorkbook(
                                "MyApplication",
                                strDefaultPath,
                                "saveCopyAs.xls");

      JExcelGuide1.operateWorksheet(
                                  "MyApplication",
                                  strDefaultPath,
                                  "Worksheet.xls");

      JExcelGuide1.operateRange(
                              "MyApplication",
                              strDefaultPath,
                              "JExcelGuide.xls",
                              "JExcelGuideSheet");
    } catch(Exception e) {
      e.printStackTrace();
    } finally {
      // Release all remote objects that haven't already been garbage collected.
      com.linar.jintegra.Cleaner.releaseAll();
    }
  }

  /**
  * This function shows how to operate Application object
  *
  * @param strNewCaption  - [String] The name that appears in the title
  *   bar of the main Microsoft Excel window.
  * @param strNewFilePath - [String] The default file path
  */
  public void useApplication(
                            String strNewCaption,
                            String strNewFilePath) {
    try {
      System.out.println( "UseApplication starts." );

      Application _xlApp = new Application();
      System.out.println( "Application object created." );

      // MS Office will be invoked visually
      _xlApp.setVisible( true );

      // If this is set to true, MS Office requires user's interaction
      _xlApp.setDisplayAlerts( false );

      System.out.println( "Application object properties:" );

      String strTemp;
      strTemp = _xlApp.getCaption();
      System.out.println( "    [Application::DefaultCaption]" + strTemp );

      _xlApp.setCaption( strNewCaption );
      strTemp = _xlApp.getCaption();
      System.out.println( "    [Application::CurrentCaption]" + strTemp );

      strTemp = _xlApp.getDefaultFilePath();
      System.out.println( "    [Application::DefaultFilePath]" + strTemp );

      _xlApp.setDefaultFilePath( strNewFilePath );
      strTemp = _xlApp.getDefaultFilePath();
      System.out.println( "    [Application::CurrentFilePath]" + strTemp );

      strTemp = _xlApp.getName();
      System.out.println( "    [Application::Name]" + strTemp );

      strTemp = _xlApp.getPath();
      System.out.println( "    [Application::Path]" + strTemp );

      strTemp = _xlApp.getStartupPath();
      System.out.println( "    [Application::StartupPath]" + strTemp );

      strTemp = _xlApp.getUserName();
      System.out.println( "    [Application::UserName]" + strTemp );

      strTemp = _xlApp.getUserLibraryPath();
      System.out.println( "    [Application::UserLibraryPath]" + strTemp );

      strTemp = _xlApp.getUserName();
      System.out.println( "    [Application::DefaultUserName]" + strTemp );

      strTemp = _xlApp.getVersion();
      System.out.println( "    [Application::Version]" + strTemp );

      System.out.println( "Application::quit is invoked." );

      System.out.println("UseApplication ends.");
    } catch(Exception e) {
        e.printStackTrace();
    }
  }

  /**
  * This function show how to create, save and close a new Workbook object
  * and save it.
  *
  * @param strNewApplicationCaption   - [String] The name that appears
  *   in the title bar of the main Microsoft Excel window.
  * @param strNewApplicationFilePath  - [String] The directory to save
  *   the new Workbook to.
  */
  public void createWorkbook(
                            String strNewApplicationCaption,
                            String strNewApplicationFilePath) {
    try {
      System.out.println( "createWorkbook starts." );

      Application _xlApp = new Application();
      System.out.println( "Application object created." );

      // MS Office will be invoked visually
      _xlApp.setVisible( true );

      // If this is set to true, MS Office requires user's interaction
      _xlApp.setDisplayAlerts( false );

      // set caption and default path
      _xlApp.setCaption( strNewApplicationCaption );
      _xlApp.setDefaultFilePath( strNewApplicationFilePath );

      // operating Workbook object
      Workbooks workbooks=_xlApp.getWorkbooks();
      System.out.println( "get workbooks." );

      int iWorkbookCount = workbooks.getCount();
      System.out.println( "    [Workbook::Count]" + iWorkbookCount );

      Workbook wb1=workbooks.add( null );
      System.out.println( "add 1st workbook" );

      Workbook wb2=workbooks.add( new Integer(XlWBATemplate.xlWBATWorksheet) );
      System.out.println( "add 2nd workbook" );

      iWorkbookCount = workbooks.getCount();
      System.out.println( "    [Workbook::Count]" + iWorkbookCount );

      // save as default name
      wb1.save();
      System.out.println("workbook 1 saved as " + wb1.getFullName());

      // save as default name
      wb2.save();
      System.out.println("workbook 2 saved as " + wb2.getFullName());

      // save as custom name
      wb1.saveCopyAs( "saveCopyAs.xls" );
      System.out.println("workbook 1 saved as " +
                         strNewApplicationFilePath +"saveCopyAs.xls");

      // save as custom name using shared mode
      // You may use XlSaveAsAccessMode.xlShared.
      // To share this workbook, it must contain at
      // least one visible worksheet.
      wb1.saveAs(
                "saveAs.xls",
                null,
                null,
                null,
                null,
                null,
                XlSaveAsAccessMode.xlExclusive,
                null,
                null,
                null,
                null);

      System.out.println("workbook 1 saved as " +
                         strNewApplicationFilePath +"saveAs.xls");

      // clear up Workbooks and Workbook objects
      wb1.close( null, null, null );
      wb2.close( new Boolean (true), null, null );
      workbooks.close();

      // quit application
      _xlApp.quit();

      System.out.println( "createWorkbook ends." );
    } catch(Exception e) {
      e.printStackTrace();
    }
  }

  /**
  * This function show how to open and close an existing Workbook object.
  *
  * @param strNewApplicationCaption   - [String] The name that appears
  *   in the title bar of the main Microsoft Excel window.
  * @param strNewApplicationFilePath  - [String] The file path to the
  *   existing Workbook.
  * @param strNewWorkbookName         - [String] The name of the
  *   Workbook.
  */
  public void openWorkbook(
                          String strNewApplicationCaption,
                          String strNewApplicationFilePath,
                          String strNewWorkbookName) {
    try {
      System.out.println( "openWorkbook starts." );

      Application _xlApp = new Application();
      System.out.println( "Application object created." );

      // MS Office will be invoked visually
      _xlApp.setVisible( true );

      // If this is set to true, MS Office requires user's interaction
      _xlApp.setDisplayAlerts( false );

      // set caption
      _xlApp.setCaption( strNewApplicationCaption );

      // operating Workbook object
      Workbooks workbooks = _xlApp.getWorkbooks();
      System.out.println( "get workbooks." );

      Workbook wb = workbooks.open(
                                  strNewApplicationFilePath + "\\" + strNewWorkbookName,
                                  null,
                                  null,
                                  null,
                                  null,
                                  null,
                                  null,
                                  null,
                                  null,
                                  null,
                                  null,
                                  null,
                                  null);
      System.out.println("open workbook in " + wb.getFullName());

      wb.activate();
      System.out.println( "Workbook is activated. " );

      int iWorkbookCount = workbooks.getCount();
      System.out.println( "    [Workbook::Count]" + iWorkbookCount );

      String strTemp = wb.getName();
      System.out.println( "    [Workbook::Name]" + strTemp );

      strTemp = wb.getPath();
      System.out.println( "    [Workbook::Path]" + strTemp );

      strTemp = wb.getAuthor();
      System.out.println( "    [Workbook::Author]" + strTemp );

      strTemp = wb.getSubject();
      System.out.println( "    [Workbook::Subject]" + strTemp );

      strTemp = wb.getTitle();
      System.out.println( "    [Workbook::Title]" + strTemp );

      // clear up Workbooks and Workbook objects
      wb.close( new Boolean (true), null, null );
      workbooks.close();

      // quit application
      _xlApp.quit();

      System.out.println( "openWorkbook ends." );
    } catch(Exception e) {
      e.printStackTrace();
    }
  }

  /**
  * This function show how to manipulate Worksheet and Sheets object
  * from an existing Workbook object, i.e. add, delete, copy, and move
  * Worksheet object
  *
  * @param strNewApplicationCaption   - [String] The name that appears
  *   in the title bar of the main Microsoft Excel window.
  * @param strNewApplicationFilePath  - [String] Directory to save
  *   the Workbook.
  * @param strWorkbookName            - [String]
  */
  public void operateWorksheet(
                              String strNewApplicationCaption,
                              String strNewApplicationFilePath,
                              String strWorkbookName) {
    try {
      System.out.println( "operateWorksheet starts." );

      Application _xlApp = new Application();
      System.out.println( "Application object created." );

      // MS Office will be invoked visually
      _xlApp.setVisible( true );

      // If this is set to true, MS Office requires user's interaction
      _xlApp.setDisplayAlerts( false );

      // set caption and default path
      _xlApp.setCaption( strNewApplicationCaption );
      _xlApp.setDefaultFilePath( strNewApplicationFilePath );

      // operating Workbook object
      Workbooks workbooks = _xlApp.getWorkbooks();
      System.out.println( "get workbooks." );

      int iWorkbookCount = workbooks.getCount();
      System.out.println( "    [Workbook::Count]" + iWorkbookCount );

      Workbook wb1 = workbooks.add( null );

      Sheets worksheets;

      // get Worksheets collection
      worksheets = wb1.getWorksheets();

      System.out.println("    [Sheets::DefaultCount]" + worksheets.getCount() );

      Worksheet ws1 = null;
      Worksheet ws2 = null;
      Worksheet ws4 = null;

      // By default there are 3 Worksheet objects, after adding a new Worksheet,
      // The count becomes 4 and the new one becomes the first Worksheet
      ws4 = new Worksheet( worksheets.add( null, null, null, null ) );
      System.out.println("    [Sheets::CurrentCount]" + worksheets.getCount() );

      Integer iIndex = new Integer( 1 );
      // get the 1st Worksheet(new one)
      ws1 = new Worksheet( worksheets.getItem( iIndex ) );

      // change the name of 1st Worksheet to "newsheet"
      ws1.setName( "newsheet" );

      ws1.activate();

      Thread.sleep( 1000 );

      // add another new worksheet
      iIndex = new Integer(2);
      ws2 = new Worksheet(worksheets.getItem(iIndex));
      ws2.setName("testsheet");

      // move ws2
      Thread.sleep( 1000 );
      ws2.move( ws1, null );

      // copy
      Thread.sleep( 1000 );
      ws2.copy( ws1, null );

      // add another new worksheet
      iIndex = new Integer( 4 );
      ws4 = new Worksheet( worksheets.getItem( iIndex ) );

      Thread.sleep( 1000 );

      // delete this new sheet
      ws4.delete();

      // get 1st worksheet's name and index:
      iIndex = new Integer( 1 );
      ws1 = new Worksheet( worksheets.getItem( iIndex ) );
      ws1.activate();

      System.out.println("    [FirstSheet::Index]" + ws1.getIndex() );
      System.out.println("    [FirstSheet::Name]" + ws1.getName() );

      // clear up Workbooks and Workbook objects
      wb1.setSaved( true );
      wb1.close( new Boolean (true), null, null );
      workbooks.close();

      // quit application
      _xlApp.quit();

      System.out.println( "operateWorksheet ends." );

    } catch(Exception e) {
      e.printStackTrace();
    }
  }

  /**
  * This function show how to manipulate Range object from an existing
  * Worksheet, i.e. fill out data, do calculation, invoke VBA script
  * if applicable.
  *
  * @param strNewApplicationCaption   - [String] The name that appears
  *   in the title bar of the main Microsoft Excel window.
  * @param strNewApplicationFilePath  - [String] Default file directory
  * @param strWorkbookName            - [String] Filename to save a copy
  *   of the workbook as.
  * @param strWorksheetName           - [String] The name to set
  *   Sheet 1 to.
  */
  public void operateRange(
                          String strNewApplicationCaption,
                          String strNewApplicationFilePath,
                          String strWorkbookName,
                          String strWorksheetName) {
    try {
      System.out.println( "operateRange starts." );

      Application _xlApp = new Application();

      // MS Office will be invoked visually
      _xlApp.setVisible( true );

      // If this is set to true, MS Office requires user's interaction
      _xlApp.setDisplayAlerts( false );

      // set caption and default path
      _xlApp.setCaption( strNewApplicationCaption );
      _xlApp.setDefaultFilePath( strNewApplicationFilePath );

      // operating Workbook object
      Workbooks workbooks=_xlApp.getWorkbooks();

      int iWorkbookCount = workbooks.getCount();

      Workbook wb1 = workbooks.add( null );

      Sheets worksheets = null;
      Worksheet ws1 = null;

      // get Worksheets collection
      worksheets = wb1.getWorksheets();

      Integer iIndex = new Integer( 1 );
      // get the 1st Worksheet(new one)
      ws1 = new Worksheet( worksheets.getItem( iIndex ) );

      // change the name of 1st Worksheet to "newsheet"
      ws1.setName( strWorksheetName );

      ws1.activate();
      System.out.println( "Activate " + strWorksheetName + " obejct." );

      // make Range and selected
      Range range = null;

      range = ws1.getRange( "B3:D6", null );

      range.select();
      Thread.sleep( 1000 );

      // set values
      Object[][] newValue = {
        { "ninja", new Boolean(false), new Double(98765.0/12345.0) },
        { "07/11/1998", new Integer(5454), new Float(22.0/7.0) },
        { new Boolean( true ), "samurai", "07/11/1999" },
        { new Integer(123), "SOAP", "Space" }
      };
      range.setValue(newValue); //Use setValue2 for Excel XP/2003
      Thread.sleep( 1000 );

      // clear values
      range = ws1.getRange( "B3:D3", null );

      range.select();
      range.clearContents();
      Thread.sleep( 1000 );

      // set values and formula, calculate the result
      range = ws1.getRange( "A8:C8", null );
      range.select();
      int[] newValue2 = { 10, 20, 30 };
      range.setValue( newValue2 ); //Use setValue2 for Excel XP/2003
      Thread.sleep( 1000 );

      range = ws1.getRange( "D8", null );
      range.select();
      range.setValue( "=SUM(A8:C8)" ); //Use setValue2 for Excel XP/2003

      Thread.sleep( 1000 );

      // set range width and height
      range = ws1.getRange( "A10", null );
      range.select();
      Double dWidth = (Double)range.getColumnWidth();
      System.out.println("    [Range::DefaultColumnWidth]" + dWidth );
      double dTemp = dWidth.doubleValue();
      dTemp *= 1.5;
      dWidth = new Double(dTemp);
      range.setColumnWidth( dWidth );

      Double dHeight = (Double)range.getRowHeight();
      System.out.println("    [Range::DefaultRowHeight]" + dHeight );
      dTemp = dHeight.doubleValue();
      dTemp *= 1.5;
      dHeight = new Double(dTemp);
      range.setRowHeight( dHeight );

      Thread.sleep( 1000 );

      // set border weight by using Borders object
      range = ws1.getRange( "C10:D12", null );
      range.select();

      Borders bds = range.getBorders();
      Integer iWeight = (Integer)bds.getWeight();
      System.out.println("    [Borders::DefaultWeight]" + iWeight );
      int iTemp = iWeight.intValue();
      iTemp += 2;//maximum weight=4
      iWeight = new Integer( iTemp );
      bds.setWeight( iWeight );

      // set border line style by using Borders obejct
      bds.setLineStyle( new Integer( XlLineStyle.xlContinuous ) );

      Thread.sleep( 1000 );

      // set edge left border use Border object
      Integer iLineStyle=new Integer( XlLineStyle.xlDot );

      Border bd = bds.getItem( XlBordersIndex.xlEdgeLeft );
      bd.setLineStyle( iLineStyle );

      Thread.sleep( 1000 );

      // set Font style
      range = ws1.getRange( "B4:D5", null );
      range.select();

      Font ft = range.getFont();
      ft.setBold( new Boolean( true ) );

      ft.setItalic( new Boolean( true ) );

      ft.setShadow( new Boolean( true ) );

      ft.setUnderline( new Boolean( true ) );

      Thread.sleep( 1000 );

      // set Font color
      Double dbColor = (Double)ft.getColor();
      System.out.println("    [Font::DefaultColor]" + dbColor );
      dTemp = dbColor.doubleValue() + 32700;
      dbColor = new Double(dTemp);
      ft.setColor( dbColor );

      // set Font size
      Double dbSize = (Double)ft.getSize();
      System.out.println("    [Font::DefaultSize]" + dbSize );
      dTemp = dbSize.doubleValue() * 1.5;
      dbSize = new Double( dTemp );
      ft.setSize( dbSize );

      Thread.sleep( 1000 );

      // save
      wb1.setSaved( true );
      wb1.saveCopyAs( strWorkbookName );

      // clear up Workbooks and Workbook objects
      wb1.close( new Boolean (true), null, null );
      workbooks.close();

      // quit application
      _xlApp.quit();

      System.out.println( "operateRange ends." );
    } catch(Exception e) {
      e.printStackTrace();
    }
  }
}

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 _xlApp = new Application( tmpSheet.getPropertyByName( "Application" ) ); ...
To compile the Java sample code, type the command
```
javac -J-mx32m -J-ms32m JExcelGuide.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 JExcelGuide.class will be created in the C:\jintegra\examples\java-excel\JExcelGuide directory.

Run the JExcelGuide Example

Go to jintegra\examples\java-excel\JExcelGuide and run the example by typing this command at the command prompt:
```
java JExcelGuide
```

Here are some screen shots of the results:

Here is the result printed in the command-line window:

After you finish running this application, there are several Excel workbooks saved in the C:\jintegra\examples\java-excel\JExcelGuide directory:

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 to the Excel Workbook

When running under Windows, Excel JAPI can use native code to pick up your current login identity. If you would rather that Excel JAPI did not do this, or if you are not logged in to an NT domain (for example if your Java code is on a UNIX box), then you must configure DCOM access to Excel, and you must specify the NT domain, user and password to be used by the Excel JAPI.

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

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.