Welcome to the ExcelWorksheetAccessor Suite of Beans
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=


Overview
========

The ExcelWorksheetAccessor suite of beans provides you with the 
functionality to visually program a Java application that can access 
the contents of an Excel worksheet. The suite contains three non-visual 
beans that can be easily wired with other beans in various IDE's to create 
applications. 

Excel provides a COM (Component Object Model) object that exposes 
the API to access an Excel workbook. As Java cannot access the COM 
object directly, it does so using the JNI (Java Native Interface). 
The native C++ code that interacts with the COM object is 
wrapped in a Dynamic Link Library (DLL), and accessed by a Java program 
using JNI. Java provides a mechanism to load the DLL and invoke the 
functions on it.

The ExcelWorksheetAccessor bean suite contains the following beans:

ExcelWorkbook:
-------------------------
The ExcelWorkbook bean handles  workbook-related functionality such as:

- Opening the workbook
        If the workbook is password protected, the fetchPasswordEvent is 
        thrown to enable you to access the workbook in the required mode.
        Once the workbook is open, the bean gets the worksheets in the 
        workbook. The worksheets property is bound and contains the list 
        of worksheets in the workbook.
- Unprotect workbook structure.
        The workbook structure needs to be unprotected (if it is 
        protected), before inserting or removing worksheets.
- Closing the workbook
        On closing, a workbook is saved by default. However, the 
        closeWorkbook method fires the fileSavingEvent to determine 
        whether workbook changes are to be saved to the file.
    
ExcelRange:
--------------------------
The ExcelRange bean provides the functionality to access data from a 
particular sheet in a workbook such as:

-   Read/write cell properties such as value, formula, format, text(read only)
    and notetext. While modifying the worksheet or reading hidden formulae,
    the worksheet needs to be unprotected (if it is password protected). 
    The fetchPasswordEvent is fired to get the password when a worksheet 
    is unprotected. All  the above mentioned properties are bound properties.

-   Clear cell properties such as formats, contents, notes or all (including 
    value and formula)

-   Assign a name to a range (i.e. create named range).

-   Insert/remove worksheets in the workbook.

-   Unprotect the worksheet.

ExcelCell:
--------------------------
The ExcelCell bean provides the functionality to access data from a single cell
in a workbook. It is similar in functionality to the ExcelRange bean. However, if 
a range containing multiple cells is specified to the ExcelCell bean, the bean 
operates only on the first cell (i.e. the cell in the top-left corner) of the
range.



Limitations of the ExcelWorksheetAccessor bean suite:
-----------------------------------------------------
    
The ExcelWorksheetAccessor suite of beans has the following limitations:

1.  The beans do not provide any Array Formula functionality. In Excel, 
    a group of cells can be designated as an array and a formula associated
    with the group. It is not possible to modify individual cells in such 
    an array. In the bean suite, if the  range you have selected contains
    any cells of an array formula, it is not possible to modify the values 
    in these cells. In other words, these beans are able to write values 
    to a range, only if the range does not contain any cells of an array 
    formula.  

2.  If an XLS file contains external links (i.e. links to cells in other 
    workbooks), these links will not be updated when the file is opened. 
    Thus, only the old values can be accessed for these cells.

3.  If a cell contains time or currency information, the value property is 
    returned as a Double object. You may request format information 
    to help in more detailed recognition of the data.

4.  For large ranges there may be a delay in accessing the data (i.e. during 
    initialization of the range, or setting of the range properties). This delay 
    is proportional to the number of cells in the range.

5.  The ExcelWorkbook bean supports valid .xls files only. The behavior 
    of the beans, when invalid files are supplied, is not defined.

6.  This suite of beans does not handle any other objects such as charts, 
    drawings etc., which might be embedded inside an Excel file. These 
    beans concern themselves with cell data only (such as value, format, 
    formula and notetext).

7.  If the workbook being opened has any Auto_Open macros in it, they 
    will not be executed when the bean opens the file.

8.  The ExcelRange bean works only with ranges which are continuous, 
    i.e. the range cannot contain multiple areas.

9.  Since the beans use native code, they cannot be used in an Applet.

  
    

Known Problems
==============

General:
--------


1.  In Excel v5.0, while removing the worksheet from a workbook an Excel 
    confirmation dialog box pops up. The worksheet will be deleted only if 
    you select "Yes". You can continue working with the beans after the 
    dialog is dismissed.

2.  While inserting a worksheet using the ExcelRange bean, a new worksheet is 
    added in the workbook. It is then renamed to the String set in the 
    worksheetName property of the ExcelRange. If the renaming of the worksheet 
    fails, the newly inserted worksheet is deleted. This deletion pops up a 
    confirmation dialog in Excel v5.0 (as in 1 above).

3.  Excel v5.0 xls files can be opened and accessed using the 
    ExcelWorksheetAccessor suite of beans, when the beans are using 
    Excel '97.  However, if you save changes to a workbook, an Excel 
    'Save As' dialog pops up. Using this dialog, the file can be saved 
    as required. If 'Cancel' is pressed, the Excel application (which is 
    running in the background and is invisible) becomes visible.
   

BeanBox:
--------
      
1.  As the beans use native code, they do not work in the current version 
    (BDK March, 1998) of BeanBox. However, you can drop the beans in beanbox 
    and customize various properties.


IBM VisualAge for Java v2.0:
----------------------------

1.  The fixpack 'Rollup1' has to be installed. This fixpack can be downloaded 
    from the IBM Visual Age web site. 
