This document shows Java programmers how to programmatically operate basic tasks for Microsoft Excel™ with J-Integra® .
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 OfficeAfter 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.
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.
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(); } } } |
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.
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 ); |
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 ); |
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 ); |
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 ); |
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(); } } } |
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() ); |
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.
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(); |
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 ) ); |
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(); |
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 ); |
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 ); |
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(); } } } |
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 |
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.
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 |
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 |
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 ); |
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 ); |
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 ); |
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.
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(); } } } |
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.
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 5 Starting the EJB Weblogic Application Server
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 |