ExcelWorkSheetAccessor Bean Suite User's Guide

• Overview
• System Requirements
• Installation Instructions
• Bean Usage
  • ExcelWorkbook Bean
    • Properties
    • Events
    • Methods
    • Wiring Instructions
  • ExcelRange Bean
    • Properties
    • Events
    • Methods
    • Wiring Instructions
  • ExcelCell Bean
    • Properties
    • Events
    • Methods
    • Wiring Instructions
• Programmer's Reference

Copyright © 1998 IBM Corp. All rights reserved.

Java, JavaBeans and 100% Pure Java are trademarks of Sun Microsystems, Inc. All other trademarks are the property of their respective owners.

Overview

The ExcelWorkSheetAccessor bean suite can be used to visually program a Java application to access and modify the contents of a Microsoft Excel worksheet. This bean suite consists of three non-visual beans that can be easily wired with other beans in various IDE's to create applications.

The ExcelWorkSheetAccessor beans can be used to access the following properties of a range in a worksheet:

• Value
• Formula
• Format
• Note text

Note : This document assumes that you are familiar with Microsoft Excel. For details on Microsoft Excel, please refer to the Microsoft Excel documentation.

The ExcelWorkSheetAccessor bean suite consists of the following beans:

• ExcelWorkbook: The ExcelWorkbook bean handles Excel's workbook related functions such as opening and closing of a workbook file. (The workbook may or may not have password protection). This bean can also be used to unprotect a structure-protected workbook.
• ExcelRange: The ExcelRange bean, as the name suggests, allows you to access the properties of a range in a workbook. It also facilitates the insertion and removal of worksheets in a workbook.
• ExcelCell: The ExcelCell bean, as the name suggests, allows you to access the properties of a cell in a workbook. If the range specified contains more than one cell then the ExcelCell bean operates on the first cell (i.e. the cell in the top-left corner) of the range. It also facilitates the insertion and removal of worksheets in a workbook. For a detailed explanation of each bean, refer to the section, Bean Usage below.

The ExcelRange or ExcelCell bean has to be used together with the ExcelWorkbook bean. However, you may want to work on multiple worksheets or ranges in a single workbook. Thus, you may have one instance of the ExcelWorkbook bean with multiple instances of the ExcelRange/ExcelCell bean, all accessing the single ExcelWorkbook.

Here's an example of how you can use the ExcelWorkSheetAccessor beans to access a range in a workbook:

The ExcelRange bean uses the ExcelWorkbook bean to access an Excel workbook file. After the ExcelRange bean is successfulyl initialized, the properties of the range are available as bound properties on the ExcelRange bean.

System Requirements

Hardware Requirements

The ExcelWorkSheetAccessor bean suite uses native code (using the Java Native Interface) and is not 100% pure Java. Since the suite uses ActiveX controls, it requires a Windows environment. These beans can be executed on any hardware platform that supports:

• Java
• Windows NT 4.0 (or above), or Windows 95 with DCOM extensions. (The DCOM extensions for Windows 95 can be downloaded from http://www.microsoft.com/com/dcom/dcom1_2/dcom1_2.asp).

Approximately 170 KB of disk space is required to install the excelaccesor.jar file and 196 KB is required to install the excelaccessor.dll file.

Software Requirements

• The ExcelWorkSheetAccessor bean suite is designed to work with Java Development Kit Version 1.1 and above.
• Any Java application builder, (e.g. VisualAge for Java from IBM Corp, VisualCafé from Symantec Corp., JBuilder from Inprise Corp.), which supports JavaBeans may be used to develop applications incorporating these beans.

Note: The ExcelWorkSheetAccessor bean suite will not work in the current implementation of BeanBox (BDK March 1998). The jar file containing the bean has a class containing native calls. However, BeanBox's implementation of Class Loader (SimpleClassLoader), does not work with classes containing native code. Refer to the FAQ.txt for more details.

• The ExcelWorkSheetAccessor bean suite works only in the Windows environment. Therefore, you must have Windows NT 4.0 (or above), or Windows 95 with DCOM extensions, installed on your machine.
• Since the beans use native calls to Microsoft Excel, the machine where the beans are being used, (whether by the application developer or the end-user), must have Microsoft Excel version 5.0c or Microsoft Excel 97. (For details of known problems, refer to Readme.txt ).

Installation Instructions

1. After downloading excelaccessor.zip, unzip the file using the jar command or any unzip utility. It will automatically create a new directory called excelaccessor and extract files into it.
2. Move the excelaccessor.dll file from the lib directory, to any of the following directories:
   • The Windows system directory or the Windows directory.
   • The directories listed in the PATH environment variable.

   Note: The ExcelWorkbook bean accesses native code that must be shipped as a Dynamic Link Library (DLL), along with the bean. This DLL needs to be present in the system path, when the bean is being used, either by the application developer or by the end-user.

3. To use the component in an IDE, import the excelaccessor.jar file. If you are using VisualCafé, import the excelaccessor_vcafe.jar from the lib directory.

   Note: Please refer to the User's Guide of your IDE for details on how to import a bean into the IDE.

4. To run the demo application provided with this package, follow the instructions in the Readme.txt file, in the demo directory.

Bean Usage

ExcelWorkbook Bean

The ExcelWorkbook bean represents the Excel workbook upon which the ExcelRange bean acts. It handles the opening and closing of an Excel file. It also enables you to unprotect a structure-protected workbook. Before closing a workbook, you can choose to save or discard any modifications that have been made. The bean interacts with the DLL through the Java Native Interface. It loads the DLL and invokes the Excel application for further use.

When the ExcelWorkbook bean accesses a workbook:

• If the workbook is being accessed for the first time, the bean will automatically open it.
• A workbook can only be opened once in the write mode. If you try to open a workbook in the write mode, that is already open in the write mode elsewhere, the ExcelWorkbookErrorEvent will be fired. This event will also be fired if any errors or exceptions are encountered while accessing the workbook.

When the ExcelWorkbook bean opens a workbook:

• If the workbook has a protection password, the method fetchWkbkProtectionPasswd(FetchPasswordEvent) is invoked.
• If a workbook with a write reservation password is opened in the read-write mode, the method fetchWkbkWriteReservationPasswd(FetchPasswordEvent) is invoked.
• If the workbook is opened successfully, the list of worksheets in the workbook is made available.

Note: If the workbook is opened in the write mode (i.e. the readOnly flag is set to false), the bean ignores a "read only recommended" status for the file. The bean will also not run any Auto_Open macros that may be present in the workbook.

When the ExcelWorkbook bean attempts to unprotect a workbook structure:

• If the workbook has a structure protection password, the method fetchWkbkStructureProtectionPasswd(FetchPasswordEvent) is invoked.

When the ExcelWorkbook bean closes a workbook:

• If the file has been modified, the FileSavingEvent is fired. (This event can be wired to the saveOnClose property. If this property is set to true, the workbook will be saved automatically before closing. If it is set to false, changes are discarded).

Properties

The following are the significant properties of the ExcelWorkbook bean:

* R = read, W = write, B = bound, E = expert

Events

The ExcelWorkbook bean fires the following events:

• ExcelWorkbookEvent

  This event is fired to signal the opening or closing of a workbook. It is also fired when the ExcelWorkbook bean is used to unprotect workbook structure.

  Listener Methods: workbookClosed(), workbookOpened(), workbookStructureUnprotected()

• FetchPasswordEvent

  This event is fired when:

  • A password-protected workbook is opened.
  • A workbook that has a write reservation password, is opened in the write mode.
  • The structure of a structure-protected workbook is unprotected using the ExcelWorkbook bean.

  Listener Methods: fetchWkbkProtectionPasswd(), fetchWkbkWriteReservationPasswd(), fetchWkbkStructureProtectionPasswd()

• FileSavingEvent

  This event is fired to signal that a modified workbook file is being closed. The status of the saveOnClose property of the ExcelWorkbook is then used to determine whether the file should be saved before closing. If the value of this flag is set to true, the file is saved. If it is set to false, changes are discarded.

  Listener Methods: savingFile()

• ExcelWorkbookErrorEvent

  This event is fired when:

  • The ExcelWorkbook bean encounters an error from Excel while performing workbook-related operations.
  • An attempt is made to write to a workbook that has been opened in the read only mode.
  • An attempt to open a workbook in the write mode fails, because the workbook is already open in write mode elsewhere.
  Listener Methods : excelError(), inaccessibleWorkbook(), writeModeOpenFailed()

• FetchArgumentsEvent

  This event is fired when an Excel workbook is opened by the openWorkbook() call. After the firing of this event, the workbookName and readOnly properties of the bean are used for opening the workbook file.

  Listener Method: fetchArgumentsCalled()

• PropertyChangeEvent

  This event is fired when any bound property of the ExcelWorkbook bean is changed.

  Listener Method: propertyChange()

Methods

The ExcelWorkbook bean implements the following significant methods:

• synchronized void openWorkbook()
  This method opens the workbook file. Before opening the file, it fires the FetchArgumentsEvent. If protection passwords are required, it fires the FetchPasswordEvent while opening the file. When the workbook has been successfully opened, it invokes the listener method workbookOpened(ExcelWorkbookEvent).
• synchronized void closeWorkbook()
  This method closes the workbook file. It fires the FileSavingEvent before closing a file that has been modified. Upon successful closing of the file, it invokes the listener method workbookClosed(ExcelWorkbookEvent).
• synchronized void unprotectWorkbookStructure()
  This method is used to unprotect workbook structure. If the workbook structure is password protected, it invokes the listener method fetchWkbkStructureProtectionPassword(FetchPasswordEvent). Once the structure has been successfully unprotected, it invokes the listener method workbookStructureUnprotected(ExcelWorkbookEvent).

Wiring Instructions

The ExcelWorkbook and ExcelRange beans must be used together to access a range in a worksheet. (For more details, refer to Overview, above.)

This sub-section describes the wiring of the Excel Workbook bean. (The wiring of the ExcelRange bean is illustrated in Figure 2, below.)

Refer to Figure 1 for the wiring diagram of the ExcelWorkbook bean.

The ExcelWorkbook bean provides a trigger, openWorkbook() method. This method is used to open the Excel workbook specified by the workbookName property of the bean. If the workbook is opened successfully, the workbookOpened (ExcelWorkbookEvent)event is fired. To close an open workbook file, the ExcelWorkbook bean provides the closeWorkbook() method. This method is invoked when the workbook is closed.

A concrete scenario is defined as follows:

The wiring diagram consists of a frame containing:

• A text field and a label used to specify the Excel workbook name.
• Two buttons (one each for opening and closing the workbook).
• A label to indicate the successful opening and closing of the workbook.

Step-by-step wiring instructions are as follows:

1. Customize properties for the ExcelWorkbook bean and set the saveOnClose property to false.
2. Wire the actionPerformedEvent(java.awt.ActionEvent) of the Open Workbook button to the openWorkbook() method of the ExcelWorkbook bean (conn1).
3. Wire the FetchArgumentsCalledEvent(com.ibm.excelaccessor.event.FetchArgumentsEvent) of the ExcelWorkbook bean to the workbookName property of the same bean (conn2). The parameter for this connection is obtained from the text property of the text field (conn3).
4. Wire the ExcelWorkbook's workbookOpened(com.ibm.excelaccessor.event.ExcelWorkbookEvent) to the text property of the label. Set the parameter for this connection to "Workbook opened." (conn4).
5. Wire the actionPerformedEvent(java.awt.ActionEvent)of the Close Workbook button to the closeWorkbook()method of the ExcelWorkbook bean (conn5).
6. Wire the ExcelWorkbook's workbookClosed(com.ibm.excelaccessor.event.ExcelWorkbookEvent) to the text property of the label. Set the parameter for this connection to "Workbook closed." (conn6).

Note: When the ExcelWorkbook bean is used along with the ExcelRange bean, the openWorkbook() call need not be specifically wired. The Excel workbook is automatically opened by the ExcelWorkbook bean when the workbook is first accessed by the ExcelRange bean. Once the Excel workbook is opened successfully, the worksheets in that workbook file are available as an output bound property, i.e. worksheets (an array of Strings) on the ExcelWorkbook bean.

ExcelRange Bean

The ExcelRange bean can be used to access the properties of a range of cells in an Excel workbook. This bean uses the facilities provided by the ExcelWorkbook bean to access a range. The range is identified by either of the following:

• The worksheet name and the range name
• The worksheet name and range position.

You can use the ExcelRange bean to perform the following functions:

• Read or write properties for cells in a specified range.
• Clear all, or only specific properties from cells in a specified range.
• Associate a name with a range in a worksheet.
• Insert or remove worksheets from a workbook.

Note: It is not necessary to initialize the ExcelRange bean before you insert or remove worksheets from a workbook.

If errors or exceptions, not related to workbook operations, are encountered while performing any of these functions, the ExcelRangeErrorEvent is fired.

Note: There are two ways of referencing a cell. The "A1" style of reference identifies a cell by its name. i.e. a cell say, B4, is referenced by the name "B4". The "R1C1" style of reference, on the other hand, references a cell using the row and column number. In this case, the cell B4 would be referenced as "row 4, column 2". The ExcelRange bean allows you to select a range using either of these methods (it provides setters for both cases). In case you invoke both types of setters on the ExcelRange bean, the setter that was invoked last is used to identify the range to be accessed. (For more information refer to the Microsoft Excel documentation.)

Before it can be used for accessing range properties, the ExcelRange bean must be initialized. While initializing, the autoCreateSheet property can be set to create a worksheet of the specified name, if it does not already exist in the workbook file. The autoUnprotectSheet property can also be set to unprotect a worksheet during initialization. If the worksheet is password protected, the listener method, fetchWorkSheetProtectionPassword(FetchPasswordEvent), is invoked.

A single instance of the range bean can be used to access different ranges. Calling the setter for the range name (or selecting a different range using row and column numbers) results in the range bean getting uninitialized. initRange has to be invoked again to initialize the range.

Properties

Following are the significant properties of the ExcelRange bean:

* R = read, W = write, B = bound, E = expert

Note: The following properties of the ExcelRange bean are valid only after a range has been initialized:
rowHeaders, columnHeaders, nRows, nCols, startRow, endRow startCol, endCol, value, formula, format, noteText, text, defaultText

Events

The ExcelRange bean fires the following events:

• ExcelSheetEvent

  This event is fired to signal the insertion or removal of a worksheet from an Excel workbook.

  Listener Methods : sheetInserted(), sheetRemoved()

• FetchArgumentsEvent

  This event is fired when the ExcelRange bean is initialized by calling the initRange() method. It can be used for setting input parameters such as rangeName, worksheetName, autoCreateSheet and autoUnprotectSheet for the ExcelRange bean. This event is also fired when the methods insertWorksheet() or removeWorksheet() are invoked.

  Listener Method: fetchArgumentsCalled()

• ExcelRangeEvent

  This event is fired when a range has been successfully initialized or when the ExcelRange bean is un-initialized. The range gets un-initialized when any of the ExcelRange bean properties, worksheetName, rangeName or xlWorkbook are changed.

  Listener Methods: rangeInitialized(), rangeUninitialized()

• FetchPasswordEvent

  This event is fired when a password-protected worksheet is unprotected.

  Listener Method: fetchWorkSheetProtectionPassword()

• ExcelRangeErrorEvent

  This event is fired when the ExcelRange bean encounters an error from Excel, while performing an operation.

  Listener Method: excelError()

• PropertyChangeEvent

  This event is fired when any of the bound properties for the ExcelRange bean are changed.

  Listener Method : propertyChange()

Methods

The ExcelRange bean implements the following significant methods:

• synchronized void clear()
  This method clears all properties for all the cells in the range. i.e. the format, value, notes and formula are cleared.
• synchronized void clearContents()
  This method clears only the contents for the cells in the range. Other properties, i.e. format and note Text are retained.
• synchronized void clearFormats()
  This method clears all formatting information for each cell in the range. Other properties, i.e. value, formula and noteText are retained.
• synchronized void clearNotes()
  This method clears the notes for all the cells in the range. Other properties, i.e. value, format and formula are retained.
• synchronized void createNamedRange(String) This method takes a string as a parameter and assigns a name to the current range.
• synchronized Boolean hasArray()
  This method returns true if all the cells in this range belong to an array, false if no cells belong to an array and null if some cells in the range belong to an array.
• synchronized Boolean hasFormula()
  This method returns true if all cells in this range contain a formula, false if no cells contain a formula and null if some cells in the range contain a formula.
• synchronized void unprotectWorksheet()
  This method is used to unprotect the worksheet for this range. If the worksheet is password- protected, the FetchPasswordEvent is fired. This event can be used to set the password to unprotect the worksheet.
• synchronized void initRange()
  This method must be called before ExcelRange bean can be used. It acts as a trigger that initializes the range and essentially turns it on for further use. Calling this method fires the FetchArgumentsEvent that can be used to set the rangeName and workSheetName properties. On successful initialization, the listener method rangeInitialized(ExcelRangeEvent)is invoked.
• synchronized void insertWorksheet()
  This method inserts a worksheet into the given workbook and renames it with the name set in the property worksheetName. It fires a FetchArgumentsEvent before inserting the worksheet. Upon successful insertion of the worksheet, the listener method sheetInserted(ExcelSheetEvent) is invoked.
• synchronized void removeWorksheet()
  This method removes a worksheet from the given workbook. It fires a FetchArgumentsEvent before removing the worksheet. Upon successful removal of the worksheet, the listener method sheetRemoved(ExcelSheetEvent) is invoked.
• void setCellR1C1(int row, int col)
  This method can be used to identify a single cell using the R1C1 style of reference, thus, allowing access to a cell using row and column numbers, rather than name strings.
• void setRangeR1C1(int topRow, int leftCol, int bottomRow, int rightCol)
  This method can be used to identify a range using the R1C1 style of reference, thus, allowing access to a range using row and column numbers, rather than name strings. It takes as input the top-left and bottom-right cells of the range.
• synchronized void setFormulAsObject(Object)
  This method sets the formula property of the range. It can be used to set the formula property by giving a single object or an array of objects or a Vector.
• synchronized void setValueAsObject(Object)
  This method sets the value property of the range. It can be used to set the value property by giving a single object or an array of objects or a Vector.
• synchronized Object getValueAsArray()
  This method returns the value as a 2-Dimensional array of objects, which represent the values in the range. If the range is a single cell, a single object representing the value is returned.
• synchronized Object getFormulaAsArray()
  This method returns the value as a 2-Dimensional array of objects, which represent the formula property of the range. If the range is a single cell, a single object representing the formula is returned.

Wiring Instructions

The ExcelRange bean must be used together with the ExcelWorkbook bean to access a range in a worksheet. (For an explanation, refer to Overview, above.) This sub-section describes the wiring of the ExcelRange bean. (The wiring of the ExcelWorkbook bean is illustrated in Figure 1, above.)

Refer to Figure 2 for the wiring diagram of the ExcelRange bean.

Figure 2 - ExcelRange bean wiring diagram

This wiring diagram describes how the ExcelRange bean can be used to access the properties of a range in an Excel workbook.

The ExcelRange bean provides a trigger, i.e. initRange() that needs to be called before the ExcelRange bean can be used to access or set any range properties. Once the ExcelRange bean has been initialized, the properties of the range are available as bound properties on the bean. Once the ExcelRange bean is successfully initialized, the rangeInitialized(com.ibm.excelaccessor.event.ExcelRangeEvent) is fired.

The following scenario demonstrates the getting and setting of the "format" property of an Excel range.

The wiring diagram consists of a frame containing:

• A text field and a label for the format property of the range.
• Two buttons (one each for getting and setting the format property).

1. Customize properties for the ExcelWorkbook bean by setting:
   • The workbookName property to the name of the Excel workbook.
   • The readOnly property to false.
2. Customize properties for the ExcelRange bean by setting:
   • The worksheetName property to the name of the worksheet to be accessed.
   • The rangeName property to the name of the range to be accessed.
3. Set the enabled property of the Set Format button to false. (This is an expert feature, so the "Show expert features" checkbox should be ticked.)
4. Wire the this property of the ExcelWorkbook bean to the xlWorkbook property of the ExcelRange bean (conn1).
5. Wire the actionPerformed(java.awt.event.ActionEvent) event of the Get Format button to the initRange() method of the ExcelRange bean (conn2).
6. Wire the ExcelRange bean's format property to the setText(java.lang.String) method of the text field (conn3). The parameter for this connection comes from the format property of the ExcelRange (conn4).
7. Wire the rangeInitialized(com.ibm.excelaccessor.event.ExcelRangeEvent) of the ExcelRange bean to the enabled property of the Set Format button (conn5). Set the parameter for this connection as true.
8. Wire the actionPerformed(java.awt.event.ActionEvent)event of the Set Format button to the format property of the ExcelRange bean (conn6). The parameter for this connection comes from the text property of the text field (conn7).
9. Wire the windowClosing(java.awt.event.WindowEvent) event of the frame to the closeWorkbook() method of the ExcelWorkbook bean (conn8).

The other properties of the range, i.e. formula, value and noteText can be accessed in a similar fashion. After the range has been initialized, these properties are available as bound properties on the ExcelRange bean.

Note: The openWorkbook()method of the ExcelWorkbook bean need not be explicitly connected in such a scenario. However, a closeWorkbook()call is recommended, otherwise changes made to the workbook may be lost.

The above scenario assumes that the workbook and worksheet being accessed are not protected. In case the workbook is protected, the ExcelWorkbook bean fires a FetchPasswordEvent before opening the file. This event can be used to set the workbookProtectionPassword or workbookWriteReservationPassword properties on the ExcelWorkbook bean. The specified password is then used to open the file.

If the worksheet is password protected and the autoUnprotect flag is set to true, a FetchPasswordEvent is fired by the ExcelRange bean during range initialization. This can be used to set the worksheetProtectionPassword property, which is then used to unprotect the worksheet.

ExcelCell Bean

The ExcelCell bean can be used to access the properties of a cell in an Excel workbook. If the range specified contains more than one cell then this bean operates on the first cell (i.e. the cell in the top-left corner) of the range. This bean uses the facilities provided by the ExcelWorkbook bean to access a cell. The cell/range is identified by either of the following:

• The worksheet name and the range name
• The worksheet name and range position.

You can use the ExcelCell bean to perform the following functions:

• Read or write properties for a cell.
• Clear all, or only specific properties from a cell.
• Insert or remove worksheets from a workbook.

Note: It is not necessary to initialize the ExcelCell bean before you insert or remove worksheets from a workbook.

If errors or exceptions, not related to workbook operations, are encountered while performing any of these functions, the ExcelCellErrorEvent is fired.

Note: There are two ways of referencing a cell. The "A1" style of reference identifies a cell by its name. i.e. a cell say, B4, is referenced by the name "B4". The "R1C1" style of reference, on the other hand, references a cell using the row and column number. In this case, the cell B4 would be referenced as "row 4, column 2". The ExcelCell bean allows you to select a range using either of these methods (it provides setters for both cases). In case you invoke both types of setters on the ExcelCell bean, the setter that was invoked last is used to identify the range to be accessed. (For more information refer to the Microsoft Excel documentation.)

Before it can be used for accessing range properties, the ExcelCell bean must be initialized. While initializing, the autoCreateSheet property can be set to create a worksheet of the specified name, if it does not already exist in the workbook file. The autoUnprotectSheet property can also be set to unprotect a worksheet during initialization. If the worksheet is password protected, the listener method, fetchWorkSheetProtectionPassword(FetchPasswordEvent), is invoked.

A single instance of the cell bean can be used to access different cells. Calling the setter for the range name (or selecting a different range using row and column numbers) results in the cell bean getting uninitialized. initCell has to be invoked again to initialize the cell bean.

Properties

Following are the significant properties of the ExcelCell bean:

* R = read, W = write, B = bound, E = expert

Note: The following properties of the ExcelCell bean are valid only after a range has been initialized:
rowHeaders, columnHeaders, nRows, nCols, startRow, endRow startCol, endCol, value, formula, format, noteText, text

Events

The ExcelCell bean fires the following events:

• ExcelSheetEvent

  This event is fired to signal the insertion or removal of a worksheet from an Excel workbook.

  Listener Methods : sheetInserted(), sheetRemoved()

• FetchArgumentsEvent

  This event is fired when the ExcelCell bean is initialized by calling the initCell() method. It can be used for setting input parameters such as rangeName, worksheetName, autoCreateSheet and autoUnprotectSheet for the ExcelRange bean. This event is also fired when the methods insertWorksheet() or removeWorksheet() are invoked.

  Listener Method: fetchArgumentsCalled()

• ExcelCellEvent

  This event is fired when a cell/range has been successfully initialized or when the ExcelCell bean is un-initialized. The cell bean gets un-initialized when any of the ExcelCell bean properties, worksheetName, rangeName or xlWorkbook are changed.

  Listener Methods: cellInitialized(), cellUninitialized()

• FetchPasswordEvent

  This event is fired when a password-protected worksheet is unprotected.

  Listener Method: fetchWorkSheetProtectionPassword()

• ExcelCellErrorEvent

  This event is fired when the ExcelCell bean encounters an error from Excel, while performing an operation.

  Listener Method: excelError()

• PropertyChangeEvent

  This event is fired when any of the bound properties for the ExcelCell bean are changed.

  Listener Method : propertyChange()

Methods

The ExcelCell bean implements the following significant methods:

• synchronized void clear()
  This method clears all properties of the cell. i.e. the format, value, noteText and formula are cleared.
• synchronized void clearContents()
  This method clears only the contents of the cell. Other properties, i.e. format and noteText are retained.
• synchronized void clearFormats()
  This method clears all formatting information of the cell. Other properties, i.e. value, formula and noteText are retained.
• synchronized void clearNotes()
  This method clears the noteText of the cell. Other properties, i.e. value, format and formula are retained.
• synchronized void createNamedCell(String) This method takes a string as a parameter and assigns a name to the cell.
• synchronized Boolean hasArray()
  This method returns true if the cell belongs to an array, false if otherwise.
• synchronized Boolean hasFormula()
  This method returns true if the cell contains a formula, false otherwise.
• synchronized void unprotectWorksheet()
  This method is used to unprotect the worksheet for this cell/range. If the worksheet is password- protected, the FetchPasswordEvent is fired. This event can be used to set the password to unprotect the worksheet.
• synchronized void initCell()
  This method must be called before the ExcelCell bean can be used. It acts as a trigger that initializes the bean and essentially turns it on for further use. Calling this method fires the FetchArgumentsEvent that can be used to set the rangeName and workSheetName properties. On successful initialization, the listener method cellInitialized(ExcelCellEvent)is invoked.
• synchronized void insertWorksheet()
  This method inserts a worksheet into the given workbook and renames it with the name set in the property worksheetName. It fires a FetchArgumentsEvent before inserting the worksheet. Upon successful insertion of the worksheet, the listener method sheetInserted(ExcelSheetEvent) is invoked.
• synchronized void removeWorksheet()
  This method removes a worksheet from the given workbook. It fires a FetchArgumentsEvent before removing the worksheet. Upon successful removal of the worksheet, the listener method sheetRemoved(ExcelSheetEvent) is invoked.
• void setCellR1C1(int row, int col)
  This method can be used to identify a single cell using the R1C1 style of reference, thus, allowing access to a cell using row and column numbers, rather than name strings.
• void setRangeR1C1(int topRow, int leftCol, int bottomRow, int rightCol)
  This method can be used to identify a range using the R1C1 style of reference, thus, allowing access to a range using row and column numbers, rather than name strings. It takes as input the top-left and bottom-right cells of the range. The ExcelCell bean operates on the cell in the top-left corner of the range.

Wiring Instructions

The ExcelCell bean must be used together with the ExcelWorkbook bean to access a cell in a worksheet. (For an explanation, refer to Overview, above.) This sub-section describes the wiring of the ExcelCell bean. (The wiring of the ExcelWorkbook bean is illustrated in Figure 1, above.)

Refer to Figure 3 for the wiring diagram of the ExcelCell bean.

Figure 3 - ExcelCell bean wiring diagram

This wiring diagram describes how the ExcelCell bean can be used to access the properties of a cell in an Excel workbook.

The ExcelCell bean provides a trigger, i.e. initCell() that needs to be called before the ExcelCell bean can be used to access or set any range properties. Once the ExcelCell bean has been initialized, the properties of the cell are available as bound properties on the bean. Once the ExcelCell bean is successfully initialized, the cellInitialized(com.ibm.excelaccessor.event.ExcelCellEvent) is fired.

The following scenario demonstrates the getting and setting of the "format" property of a cell.

The wiring diagram consists of a frame containing:

• A text field and a label for the format property of the cell.
• Two buttons (one each for getting and setting the format property).

1. Customize properties for the ExcelWorkbook bean by setting:
   • The workbookName property to the name of the Excel workbook.
   • The readOnly property to false.
2. Customize properties for the ExcelCell bean by setting:
   • The worksheetName property to the name of the worksheet to be accessed.
   • The rangeName property to the name of the range to be accessed. (If the range contains multiple cells then this bean operates on the cell in the top-left corner of the range.)
3. Set the enabled property of the Set Format button to false. (This is an expert feature, so the "Show expert features" checkbox should be ticked.)
4. Wire the this property of the ExcelWorkbook bean to the xlWorkbook property of the ExcelCell bean (conn1).
5. Wire the actionPerformed(java.awt.event.ActionEvent) event of the Get Format button to the initCell() method of the ExcelCell bean (conn2).
6. Wire the ExcelCell bean's format property to the setText(java.lang.String) method of the text field (conn3). The parameter for this connection comes from the format property of the ExcelCell bean (conn4).
7. Wire the cellInitialized(com.ibm.excelaccessor.event.ExcelCellEvent) of the ExcelCell bean to the enabled property of the Set Format button (conn5). Set the parameter for this connection as true.
8. Wire the actionPerformed(java.awt.event.ActionEvent)event of the Set Format button to the format property of the ExcelCell bean (conn6). The parameter for this connection comes from the text property of the text field (conn7).
9. Wire the windowClosing(java.awt.event.WindowEvent) event of the frame to the closeWorkbook() method of the ExcelWorkbook bean (conn8).

The other properties of the cell, i.e. formula, value and noteText can be accessed in a similar fashion. After the cell bean has been initialized, these properties are available as bound properties on the ExcelCell bean.

Note: The openWorkbook()method of the ExcelWorkbook bean need not be explicitly connected in such a scenario. However, a closeWorkbook()call is recommended, otherwise changes made to the workbook may be lost.

The above scenario assumes that the workbook and worksheet being accessed are not protected. In case the workbook is protected, the ExcelWorkbook bean fires a FetchPasswordEvent before opening the file. This event can be used to set the workbookProtectionPassword or workbookWriteReservationPassword properties on the ExcelWorkbook bean. The specified password is then used to open the file.

If the worksheet is password protected and the autoUnprotect flag is set to true, a FetchPasswordEvent is fired by the ExcelCell bean during cell initialization. This can be used to set the worksheetProtectionPassword property, which is then used to unprotect the worksheet.