Microsoft Excel 2000 Java Programmer’s Guide

Summary

This document shows Java programmers how to programmatically operate basic tasks for Microsoft Excel™ with J-Integra® .

Introduction

With J-Integra® , COM-based (component object model) modules can be converted to Java classes. As a result, Java programmers can convert object libraries (.olb) to Java classes and access the modules from Java as if they were using Microsoft Visual Basic (VB) or Visual Basic for Application (VBA).

By using DCOM mode, programmers can use Java code on a machine without Microsoft Office installed (Windows, Linux, or Unix) to manipulate Microsoft Office modules on remote machines (Figure 1).

Figure 1 J-Integra® and Microsoft Office

After all the Office methods, objects, and properties have been converted to Java classes, it is necessary for Java programmers to learn the basic object model for Microsoft Office and learn the common-used Java classes. The whole object model and reference for Microsoft Office can be found in the MSDN Office Developer Documentation; however, it is mainly written for VB and VBA developers. The Microsoft Excel 2000 Java Programmer’s Guide has been written as an Office reference from a Java programmer’s perspective.

Contents

  1. Microsoft Excel Object Model and the Excel JAPI
  2. Introducing Application Objects
  3. Introducing Workbooks and Workbook Objects
  4. Creating, Saving, Opening, and Closing Workbook Objects
    1. Creating Workbook Objects
    2. Saving Workbook Objects
    3. Opening Workbook Objects
    4. Closing Workbook Objects
    5. Example
  5. Introducing Sheets and Worksheet Objects
  6. Getting, Adding, Deleting, Copying, and Moving Worksheet Objects
    1. Getting Worksheet Objects
    2. Adding Worksheet Objects
    3. Deleting Worksheet Objects
    4. Copying Worksheet Objects
    5. Moving Worksheet Objects
    6. Example
  7. Introducing Range Objects
  8. Using Range Objects
    1. Inputting Cell Data
    2. Calculating Cell Data
    3. Setting the Width of Rows and Columns
    4. Borders
    5. Fonts
    6. Simulating a User
    7. Example
  9. Invoking VBA from the Excel JAPI
  10. Appendix: Object Library Filenames
  11. Supplement: The JExcelGuide Example

Microsoft Excel Object Model and the Excel JAPI

The Microsoft Excel object model contains several dozen objects and collections. By using these objects in your application, you can manipulate Microsoft Excel as if you were using its user interface locally.

The common tasks for Microsoft Excel are modifying a spreadsheet, filling in data, doing calculations, and printing out results. These frequently-used tasks only relate to a small subset of the whole Microsoft Excel object model. The most frequently used collections and objects contain the Application object, the Workbooks collection, the Workbook object, the Worksheets collection, and the Worksheet object. This document will cover the major methods and properties of these objects and collections. However, if you want to learn more, please refer to the MSDN Office Developer Documentation.

Unlike object-oriented languages such as Java and C++, traditional Visual Basic (versions before Visual Basic.NET) does not support inheritance. Visual Basic programmers use the concepts of object and collection rather than class to describe the world. J-Integra® contains a set of Java library files which contain the converted version of the Excel object library (.olb) to Java proxies. These libraries are called Excel JAPI, and when you download J-Integra® , you will get three jar files: excel2000.jar (this is for Excel 2000), excel97.jar (this is for Excel 97), and excelXP.jar (this is for Excel XP). All native objects, collections, and enumerations in Microsoft Excel API are converted to Java classes, and enumeration.

Figure 2 shows the object model for Microsoft Excel in Java classes (the Excel JAPI). Note that the Worksheets collection has been renamed the Sheets class rather than use the original name.

Figure 2 Basic classes in Excel JAPI

If you are familiar with VB or VBA, please refer to the section on Working with Microsoft Excel Objects in the MSDN Office Developer Documentation. This may clarify some aspects of this Programmer's Guide by presenting the material from a VB/VBA perspective.

Introducing Application Objects

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.

As the top-level object in the Microsoft Excel object model, the Application object represents the Microsoft Excel application itself. You must create this object before you get references for other objects such as Workbook and Worksheet. When you create the Application object, a new instance of Microsoft Excel is opened in the background. After you process all your tasks inside Microsoft Excel, you should quit the instance by using the quit method of the Application object.

The code creating the Application object looks like this:

Application _xlApp = new Application();

After you execute the above code, you may see Microsoft Excel running in the background. If you want Microsoft Excel to be visible, invoke its setVisible method:

_xlApp.setVisible( true );

By default, after it is invoked, Microsoft Excel may display an alert dialog box when you work with it. For example, Microsoft Excel may ask you if macros should be enabled or disabled when you open a workbook; or after data is filled out, Microsoft Excel may ask you if you want to save the changes. Since those alert dialog boxes require your local interaction, they cannot be used in DCOM mode, in which you may work with Microsoft Excel from a remote machine. In this case, you can disable alert dialog box by using the following code.

_xlApp.setDisplayAlerts( false );

Another important method of the Application object that should be discussed here is getDefaultFilePath and setDefaultFilePath. DefaultFilePath refers to the directory storing Excel spreadsheet files. For example, if you want to work with MyFile.xls in the C:\MS Office directory, you should set the DefaultFilePath to C:\MS Office. In DCOM mode, DefaultFilePath refers to the path on the machine running Microsoft Office. For example:

System.out.println(_xlApp.getDefaultFilePath() );
_xlApp.setDefaultFilePath( "c:\\MS Office" );

The following example shows how to manipulate basic properties of the Application object.

import excel.*; 
import java.util.*; 
 
public class JExcelGuide { 
   
  public JExcelGuide() { 
  } 
 
  public static void main(String[] args) { 
 
    try { 
      JExcelGuide JExcelGuide1 = new JExcelGuide(); 
      JExcelGuide1.useApplication(  
                                  "MyApplication", 
                                  "C:\\JExB\\examples"); 
    } 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 
  */ 
  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(); 
    } 
  } 
} 

Introducing Workbooks and Workbook Objects

If you consider the Application object to be a Microsoft Excel application, you can also consider a Workbook object to be an Excel .xls or .xla workbook file. You can use the Workbook object to work with a single Excel workbook, and use the Workbooks object (originally called the Workbooks collection in the Microsoft Excel object model) to work with all open Workbook objects.

Basically, you create an Application object, get the Workbooks object from it, and then create or open a Workbook object from the Workbooks object. The following code shows how to obtain the Workbooks object from the Application object.

Application _xlApp = new Application(); 
Workbooks workbooks=_xlApp.getWorkbooks();

Another issue is the getCount method in the Workbooks object. In the Microsoft Excel object model, Count property references the current active workbook in the Workbooks collection, which is 1 by default because of a hidden Personal.xls workbook storing macros. However, if you invoke getCount in the Workbooks Java class, it returns 0 by default.

Creating, Saving, Opening, and Closing Workbook Objects

Creating Workbook Objects

When you try to create a new Workbook object, you have to use the add method in the Workbooks class. The add method creates a new Workbook object, opens it, and returns the reference to the object.

The only parameter of the add method is an Integer or String object, representing how the new workbook is created. This argument can be a string specifying the name of an existing Microsoft Excel file. In this case the new workbook is created with the specified file as a template. This argument can also be an Integer object based on a set of constants representing the specified type. The constants can be one of the following: XlWBATemplate.xlWBATChart, XlWBATemplate.xlWBATExcel4IntlMacroSheet, XlWBATemplate.xlWBATExcel4MacroSheet, or XlWBATemplate.xlWBATWorksheet. If this argument is set to null, Microsoft Excel creates a new workbook with a number of blank sheets (the number of sheets is set by the SheetsInNewWorkbook property).

Workbooks workbooks = null;

...
// after obtaining workbooks from Application
Workbook wb = workbooks.add( new Integer(XlWBATemplate.xlWBATWorksheet) );
Workbook wb2 = workbooks.add( null );

Saving Workbook Objects

You have three choices when saving a Workbook object to an XLS file: saving it with the default name in the default directory, saving it as a copy with a custom file path, or saving it with a custom file path and different properties.

The save method of the Workbook object saves the file in default directory (The default file path can be returned with the getDefaultFilePath method of the Application object) and with the default file name (book1.xls, book2.xls, etc.).

The saveCopyAs method of the Workbook object allows you to specify a String for the filename or the file path as the only parameter.

If you want to control the advanced properties when you save a workbook, use the saveAs method of the Workbook object. The first parameter is a String for the file name or file path. The remaining ten parameters can be null, but you can set the features such as password. Note that the seventh parameter, which represents the workbook access mode, is a C int type rather than a Java object. You may set it to XlSaveAsAccessMode.XlExclusive, XlSaveAsAccessMode.xlNoChange, and XlSaveAsAccessMode.xlShared. When you use XlsaveAsAccessMode.xlShared, the workbook should include at least one worksheet.

The following code shows how to use the three saving methods of the Workbook object.

Workbook wb1;
...
// save as default name
wb1.save();

// save as custom name
wb1.saveCopyAs( "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
    );

Opening Workbook Objects

The open method of the Workbooks object opens an existing workbook. The first parameter of open method is a String, representing the file name of the workbook. You can also input a full file path. The remaining twelve parameters include a read-only flag, password, format, etc. and can be null. Read the MSDN Microsoft Excel Visual Basic Reference for advanced settings. The following code shows how to open a workbook with a file path of C:\temp\temp.xls.

Workbook wb = workbooks.open(
                "C:\\temp\\temp.xls",
                null,
                null,
                null,
                null,
                null,
                null,
                null,
                null,
                null,
                null,
                null,
                null
);

Closing Workbook Objects

To close a workbook, you should use the close method of the Workbook object. The first parameter is a Boolean object, representing if save changed properties, the other two parameters should be null. Here is an example:

Workbook wb;
...
// do something
wb.close( new Boolean (true), null, null );

Example

The following example shows how to create two workbooks and save them with a default name (book1.xls and book2.xls) and user-defined names in different way. Then the example shows how to open one of the workbooks and print out some of its properties.

 import excel.*; 
import java.util.*; 
 
public class JExcelGuide { 
     
  public JExcelGuide(){ 
  } 
 
  public static void main(String[] args) { 
   
    try { 
      JExcelGuide JExcelGuide1 = new JExcelGuide(); 
 
      JExcelGuide1.createWorkbook( 
                                  "MyApplication", 
                                  "C:\\JExB\\examples"); 
 
      JExcelGuide1.openWorkbook( 
                                "MyApplication", 
                                "C:\\JExB\\examples", 
                                "saveCopyAs.xls"); 
    } catch(Exception e) { 
      e.printStackTrace(); 
    } finally { 
      // Release all remote objects that haven't already been garbage collected. 
      com.linar.jintegra.Cleaner.releaseAll(); 
    } 
  } 
 
  /* 
  * This function show how to create, save and close a new Workbook object 
  * and save it. 
  */ 
  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 ); 
       
      // 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(strNewApplicationFilePath + "\\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( new Boolean (true), 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. 
  */ 
  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 ); 
       
      // 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(); 
    } 
  } 
}

Introducing Sheets and Worksheet Objects

Most of the work that you will do in Excel will be within the context of a worksheet, also known as a spreadsheet. A worksheet contains a grid of cells you can use to work with data, properties, methods, and events.

A workbook may contain more than one worksheet (a new workbook contains three worksheets by default). In the Excel object library, a worksheet is represented by the Worksheet object and a collection of worksheets is represented by the Worksheets collection. In the Excel JAPI, the Excel Worksheet object is described by the Worksheet class and the Excel Worksheets collection is described by the Sheets class.

The following code shows how to get the Sheets object and the number of Worksheet inside it:

Workbook wb1;

...

Sheets worksheets;

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

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

Getting, Adding, Deleting, Copying, and Moving Worksheet Objects

The Worksheet object represents a physical worksheet. By default, a new workbook contains three worksheets named Sheet1, Sheet2, and Sheet3, and Sheet1 is activated. These three worksheets are indexed by 1-based cardinality. If you want to add an additional new worksheet, the new worksheet will become Sheet4 by default.

Getting Worksheet Objects

To get a specific Worksheet object, use the getItem method of the Sheets class, and input an Integer object, which represents the index, as the parameter. The following example shows how to get a specific Worksheet object, change its name to newsheet, and activate it.

Sheets worksheets = null;
// obtain worksheets
...

Worksheet ws1 = null;

Integer iIndex = new Integer( 1 );

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

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

ws1.activate();

Adding Worksheet Objects

To add a new Worksheet object, call the add method of the Sheets class. There are four parameters in the add method.

The first parameter is a Worksheet object that specifies the worksheet before which the new worksheet is added.

The second parameter is a Worksheet object that specifies the worksheet after which the new worksheet is added. If both of the first two parameters are set to null, the new worksheet will be inserted before the active worksheet (by default, the active worksheet is the first worksheet). 

The third parameter is an Integer object representing the number of sheets to be added. If it is set to null, one worksheet is created. The fourth parameter is an Integer object representing the worksheet type. There are five worksheet types: XlSheetType.xlChart, XlSheetType.xlDialogSheet, XlSheetType.xlExcel4IntlMacroSheet, XlSheetType.xlExcel4MacroSheet, and XlSheetType.xlWorksheet. Notice that these types are constant integers and you have to wrap them into the Integer object when setting parameters. If the fourth parameter is null, the default value is XlSheetType.xlWorksheet.

The following code shows how to add a new Worksheet object.

Sheets worksheets = null;
// obtain worksheets
...

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

Deleting Worksheet Objects

To delete a Worksheet object, simply use the delete method of the Worksheet class. There is no parameter.

Worksheet ws4 = null;
//obtain worksheet ws4
...
ws4.delete();

Copying Worksheet Objects

By invoking copy method of Worksheet class, you can copy the worksheet to another location in the workbook. The first parameter of copy method is a Worksheet object, representing the worksheet before which the copied worksheet will be placed. You cannot specify the first parameter if you specify the second one. The second parameter of copy method is a Worksheet object, representing the worksheet after which the copied worksheet will be placed. You cannot specify the second parameter if you specify the first one. Following code shows how to copy an existing worksheet.

Worksheet ws1 = null;
Worksheet ws2 = null;

//obtain worksheet ws1 and ws2
...

ws2.copy( ws1, null );

Moving Worksheet Objects

By invoking move method of Worksheet class, you can move the worksheet to another location in the workbook. The first parameter of move method is a Worksheet object, representing the worksheet before which the moved worksheet will be placed. You cannot specify the first parameter if you specify the second parameter. The second parameter of move method is a Worksheet object, representing the worksheet after which the moved worksheet will be placed. You cannot specify the second parameter if you specify the first parameter. Following code shows how to move an existing worksheet.

Worksheet ws1 = null;
Worksheet ws2 = null;

//obtain worksheet ws1 and ws2
...

ws2.move( ws1, null );

Example

The following sample shows working with Sheets and Worksheet objects.

import excel.*; 
import java.util.*; 
 
public class JExcelGuide { 
   
  public JExcelGuide() { 
  } 
   
  public static void main(String[] args) { 
        
    try { 
      JExcelGuide JExcelGuide1 = new JExcelGuide(); 
       
      JExcelGuide1.operateWorksheet( 
                                  "MyApplication", 
                                  "C:\\JExB\\examples", 
                                  "JExcelGuide.xls"); 
    } catch(Exception e) { 
      e.printStackTrace(); 
    } finally { 
      // Release all remote objects that haven't already been garbage collected. 
      com.linar.jintegra.Cleaner.releaseAll(); 
    } 
  } 
 
  /* 
  * This function show how to manipulate Worksheet and Sheets object 
  * from an existing Workbook object, i.e. add, delete, copy, and move 
  * Worksheet object 
  */ 
  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 ); 
       
      // 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(); 
    } 
  } 
} 

Introducing Range Objects

The Microsoft Office 2000/Visual Basic Programmer's Guide calls the Range object "the most powerful, dynamic, and often-used object." When you do some real tasks with Excel, such as filling in data, creating tables, and calculating results, you are actually using the Range object.

The Excel Range object is the collection of cells you are working with. It can be a single cell or a collection of cells. It can be a row or column, or it can represent a three-dimensional collection of cells that span multiple worksheets. Unlike other objects that exist as objects and as members of a collection of objects, it is unnecessary for Microsoft to provide a Range collection containing all Range objects in a workbook or worksheet. In Excel JAPI, the Range class represents the Range object in Visual Basic.

Because the Range object is such a fundamental entity within Excel, and there are so many properties and methods, this document only introduces the essential parts that will be frequently used.

You can use the getRange method of the Worksheet class to retrieve a Range object. Both the first and the second parameters are String objects, representing special expressions of ranges. Usually you will input the first parameter and set the second to null. For example:

Worksheet ws1 = null;
// obtain ws1
...
Range range = ws1.getRange( "B3:D6", null );

 The following table illustrates how to use a String object to obtain a Range object.

Reference

Meaning

Range("A1")

Cell A1

Range("A1:B5")

Cells A1 through B5

Range("C5:D9,G9:H16")

A multiple-area selection

Range("A:A")

Column A

Range("1:1")

Row 1

Range("A:C")

Columns A through C

Range("1:5")

Rows 1 through 5

Range("1:1,3:3,8:8")

Rows 1, 3, and 8

Range("A:A,C:C,F:F")

Columns A, C, and F

Using Range Objects

After obtaining a Range object, you can start doing things such as inputting data, creating tables, setting up formulae, calculating results, and making some visual changes to the worksheet.

Inputting Cell Data

One of the essential tasks in Excel is setting a value or values to a cell or a set of cells. In the Excel JAPI, you can use the setValue method of the Range class to complete this task. The only parameter of the method is Object, which can be an Integer object, a Boolean object, a String object, a Double object, an array of Objects, or something else.

Here is an example of inputting an Object array to a set of cells by using the setValue method.

Worksheet ws1;
// get ws1
...
Range range = ws1.getRange( "B3:D6", null );

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

Calculating Cell Data

Another essential Excel task is calculation, which means setting a formula in a cell, calculating the result, and displaying the result in that cell. You can still use the setValue method of the Range class and input a String object, which is actually a formula. For example:

Worksheet ws1;
// get ws1
...
Range range = null;

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

Setting the Width of Rows and Columns

If you want to set the height of a row of cells or the width of a column of cells, you can use the setRowHeight method and setColumnWidth method of the Range class.

Worksheet ws1;
// get ws1
...

Range range = null;

// set range width and height
range = ws1.getRange( "A10", null );
range.select();
Double dWidth = (Double)range.getColumnWidth();

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

Borders

To create a table in a worksheet, you may need to set the weight of the border of a cell or a set of cells. In the Excel JAPI, the Borders class represents border of a set of cells. You can invoke the getBorders method of the Range class to get a Borders object.

Range range;
// get range
...
Borders bds = range.getBorders();

After obtaining reference to Borders object, you may query and change the weight of the border line by invoking getWeight and setWeight methods of Borders object. Notice that maximum weight is four.

Borders bds;
// get bds
...

Integer iWeight = (Integer)bds.getWeight();
int iTemp = iWeight.intValue();
iTemp += 2;//note: maximum weight=4
iWeight = new Integer( iTemp );
bds.setWeight( iWeight );

You may set the style of border line by calling the setLineStyle method of the Borders class. The only parameter is an Integer object, which is a wrapper of the following constant integers. You can guess the border line style from the naming convention.

XlLineStyle.xlContinuous

XlLineStyle.xlDash

XlLineStyle.xlDashDot

XlLineStyle.xlDashDotDot

XlLineStyle.xlDot

XlLineStyle.xlDouble

XlLineStyle.xlSlantDashDot

XlLineStyle.xlLineStyleNone

By default the line style is XlLineStyle.xlContinuous.

Borders bds;
// get bds
...

bds.setLineStyle( new Integer( XlLineStyle.xlContinuous ) );

If you want to change the weight or line style of any edge of a Range object, rather than change the borders of each cell in that Range object use the Borders class. You can get a Border object by invoking the getItem method of the Borders class. The only parameter is one of the following integers. You can guess the type of border from the naming convention.

XlBordersIndex.xlInsideHorizontal

XlBordersIndex.xlInsideVertical

XlBordersIndex.xlDiagonalDown

XlBordersIndex.xlDiagonalUp

XlBordersIndex.xlEdgeBottom

XlBordersIndex.xlEdgeLeft

XlBordersIndex.xlEdgeRight

XlBordersIndex.xlEdgeTop

After you get a Border object, you can set the weight, line style, or the other properties as shown in the following example:

Borders bds;
// get bds
...

Integer iLineStyle = new Integer( XlLineStyle.xlDot );

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

Fonts

You can also get the Font object from Range object. By invoking methods of the Font class such as setBold, setItalic, setShadow, setUnderline, setColor, and setSize, you can change the appearance, color, and size of text in cells.

Range range;
// get range
...

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

// set Font color
Double dbColor = (Double)ft.getColor();
dTemp = dbColor.doubleValue() + 32700;
dbColor = new Double(dTemp);
ft.setColor( dbColor );

// set Font size
Double dbSize = (Double)ft.getSize();
dTemp = dbSize.doubleValue() * 1.5;
dbSize = new Double( dTemp );
ft.setSize( dbSize );

Simulating a User

You can also emulate an end user’s interaction, such as selecting and activating a set of cells, by using the select and activate methods of the Range class. There is no parameter for the two methods. They are useful in invoking an existing VBA script or macro embedded in a workbook. 

Example

The following code shows the basic usage of Range object by using Excel JAPI.

import excel.*; 
import java.util.*; 
 
public class JExcelGuide { 
     
  public JExcelGuide() { 
  } 
 
  public static void main(String[] args) { 
         
    try { 
      JExcelGuide JExcelGuide1 = new JExcelGuide(); 
       
      JExcelGuide1.operateRange( 
                                "MyApplication", 
                                "C:\\JExB\\examples", 
                                "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 show how to manipulate Range object from an existing 
  * Worksheet, i.e. fill out data, do calculation, invoke VBA script 
  * if applicable. 
  */ 
  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(); 
    } 
  } 
} 

Invoking VBA from the Excel JAPI

VBA (Visual Basic For applications) is a macro language that is identical to the Visual Basic programming language. By using VBA you can easily handle the objects and collections exposed by Microsoft Office.

Since VBA can be embedded inside an Excel workbook, you can use the Excel JAPI to trigger a VBA macro by emulating an end user’s interaction.

For example, create an empty workbook with three default worksheets named Sheet1, Sheet2, and Sheet3. Edit and compile the following VBA code on Sheet2 and save the workbook. (For how to edit and compile VBA code in an Excel workbook, please refer to the MSDN Office Developer Documentation.)

Private Sub Worksheet_Activate()
    Range("B2:B14").Value = 10000
    Range("B15").Formula = "=Sum(B2:B14)"
    Range("B15").Font.Bold = True
    Range("B15").Font.Color = RGB(0, 255, 0)
End Sub

Using the Excel JAPI, you can open the saved workbook, search for a worksheet named Sheet2 and activate it to trigger the VBA macro.

Workbook wb;
// get wb
...

Sheets worksheets = wb.getWorksheets();

Integer iTemp2 = null;
int iTemp = worksheets.getCount();
String strTemp = null;
String strTemplateWorksheetName = "Sheet2";

while( iTemp > 0 ) {
  iTemp2 = new Integer(iTemp);
  ws = new Worksheet( worksheets.getItem( iTemp2 ) );

  strTemp = ws.getName();

  if( strTemplateWorksheetName.equals( strTemp ) ) {
    break;
  }

  iTemp--;
}

ws.activate();

The worksheet Sheet2 will be opened and cells in the range of B2:B14 will be filled out with the integer 10000. Cell B15 will display the sum of B2:B14, 130000, in a bold and green font. Figure 3 shows this result.

Figure 3 Invoking a VBA Macro

Rather than using Java to invoke VBA, another way to integrate VBA with Microsoft Excel is by using VBA to invoke a Java object or an EJB object. The following figures are taken from the Excel VBA to EJB Example, and show how VBA in an Excel worksheet remotely invokes a sample EJB object in a BEA Weblogic Server

Figure 4 An Excel worksheet on a computer running Microsoft Office

Figure 5 Starting the EJB Weblogic Application Server

Figure 6 The command line on the computer running EJB Weblogic Application Server

Appendix: Object Library Filenames

This table shows the object libraries for Microsoft Office 97, 2000 and XP. 

Note: the object library for Microsoft XP is Excel.exe.

   Application                       

Type Library

   

Microsoft Access 97

Msacc8.olb

Microsoft Jet Database 3.5

DAO350.dll

Microsoft Binder 97

Msbdr8.olb

Microsoft Excel 97

Excel8.olb

Microsoft Graph 97

Graph8.olb

Microsoft Office 97

Mso97.dll

Microsoft Outlook 97

Msoutl97.olb

Microsoft PowerPoint 97

Msppt8.olb

   

Microsoft Word 97

Msword8.olb

Microsoft Access 2000

Msacc9.olb

Microsoft Jet Database 3.51

DAO360.dll

Microsoft Binder 2000

Msbdr9.olb

Microsoft Excel 2000

Excel9.olb

Microsoft Graph 2000

Graph9.olb

Microsoft Office 2000

Mso9.dll

Microsoft Outlook 2000

Msoutl9.olb

Microsoft PowerPoint 2000

Msppt9.olb

Microsoft Word 2000

Msword9.olb

   

Microsoft Access 2002

Msacc.olb

Microsoft Excel 2002

Excel.exe

Microsoft Graph 2002

Graph.exe

Microsoft Office 2002

MSO.dll

Microsoft Outlook 2002

MSOutl.olb

Microsoft PowerPoint 2002

MSPpt.olb

Microsoft Word 2002

MSWord.olb