JExcel Programmer's Guide


Version: 1.8
Last Updated: August 25, 2016

Table of Contents
1. Introduction
2. System Requirements
3. JExcel Overview
4. Excel Automation Basics
4.1. Creating a New Application
4.2. Creating a New Workbook
4.3. Opening an Existing Workbook
4.4. Saving a Workbook
4.5. Obtaining and Modifying Workbook Additional Information
4.6. Managing Worksheets
4.7. Working with Workbook Windows
4.8. Working with a List of Recently Opened Files
5. Event Listening and Handling
5.1. Application Events
5.2. Workbook Events
5.2.1. Listening to Workbook Events
5.2.2. Handling Workbook Events
5.3. Worksheet Events
5.3.1. Listening to Worksheet Events
5.3.2. Handling Worksheet Events
6. Working with Cells
6.1. Referencing Cells
6.2. Setting New Values
6.3. Obtaining Existing Values
7. Working with Ranges
7.1. Referencing Ranges
7.2. Converting a Cell to a Range and Vice Versa
7.3. Finding Values in Ranges
7.4. Sorting a Range
7.5. Obtaining Ranges Intersection
7.6. Merging and Unmerging Cells of a Range
8. Reading and writing bulk operations
8.1. Reading bulk operation
8.2. Writing bulk operation
9. Customizing Cells and Ranges
9.1. Changing a Number Format
9.2. Customizing Text Alignment and Orientation
9.2.1. Changing Text Alignment
9.2.2. Changing Text Orientation
9.3. Customizing Text Font
9.4. Changing Background Pattern and Color in Cells
9.5. Border Customization
10. Integrating a Workbook into a Java Swing Application
10.1. Opening and Saving an Embedded Workbook
10.2. Printing an Embedded Workbook
10.3. Displaying a Workbook in Static Mode
10.4. Listening to JWorkbook Events
10.5. Multiple JWorkbook instances
10.6. JWorkbook objects handling on application exit
11. Working with Native Peers and Thread Issues
12. Where to Get a New Version

Chapter 1. Introduction


There are lots of programming tasks that deal with generating reports in the tabular format. Usually such documents should be designed for reviewing by people who are not closely familiar with different technologies and prefer to obtain reports in a usual format. For Microsoft Windows users, the most common office solution is Microsoft Office, and when talking about electronic worksheets, it is undoubtedly Microsoft Excel.

The JExcel library allows Java programmers for Windows to easily create, modify or display Microsoft Excel files. Using this library, you can work with workbook files, print a workbook, worksheet or even a specific range of cells, and modify cell values, cell number formats, etc. Also, there is an ability to customize cell appearance, including text font, cell background and border, text alignment and orientation, etc.

JExcel can be used for solving different tasks from simple display of a workbook in a standalone Excel application to handling Excel events and embedding a workbook into Java Swing applications. The use of the library is very simple and doesn't require from a Java programmer specific knowledge of Microsoft Office programming.

In the JExcel distribution you can view a demo application (JExcelDemo) showing how to embed an Excel workbook in a Java Swing application using the JWorkbook component.

Each chapter below contains samples which you can also find in the samples\Guide folder of the JExcel distribution.

Chapter 2. System Requirements


JExcel System Requirements

Chapter 3. JExcel Overview


The JExcel functionality can be divided into the following parts:

  • Automation of an Excel application, workbooks, worksheets, etc.

    This is the core functionality of the JExcel library which allows you to work with Excel applications, workbooks, worksheets, ranges and cells.

  • Embedding a workbook in a Java Swing application.

    JExcel provides the JWorkbook component that allows embedding an Excel workbook in a Java Swing application as an ordinary Swing component.

  • Listening to workbook or worksheet events.

    Workbook and Worksheet JExcel classes allow adding listeners of events generated by a workbook or worksheet respectively.

  • Handling workbook and worksheet events.

    Unlike event listeners, event handlers allow you not only to obtain events, but also affect the application behavior.

  • Working with native peers.

    Native peers are provided to allow you to add custom functionality based on JExcel.

Chapter 4. Excel Automation Basics


This chapter discusses the functionality of the following JExcel classes:

  • Application

  • Workbook

  • Worksheet

  • Window

All these classes reside in the com.jniwrapper.win32.jexcel package.

4.1. Creating a New Application

Your work with Excel starts with the creation of a new Excel application. The JExcel library includes the Application class that represents an Excel application and provides related functionality. A new application is created by calling the Application constructor:

        Application application = new Application();

When the Excel application is created, it is not visible. To make the application visible, the Application.setVisible() method should be called:

        if (!application.isVisible())
        {
            application.setVisible(true);
        }

Similarly, the following call hides the application, but doesn't close it:

        application.setVisible(false);

For the complete sample, please refer to the following file:

basics/ApplicationSample.java

4.2. Creating a New Workbook

To create a new workbook, call the Application.createWorkbook() method which returns a newly created workbook:

        Workbook workbook = application.createWorkbook("Custom title");

The title parameter can be null.

4.3. Opening an Existing Workbook

The Application.openWorkbook() method allows you to open a workbook of any format supported by the current version of Excel. If a workbook requires a password, it should be passed to the password parameter. Set the readOnly parameter to true to prevent an opened workbook from modifying.

        File xlsFile = new File("C:\\Workbook.xls");
        Workbook workbook = application.openWorkbook(xlsFile, true, "password");

The sample above opens the Workbook.xls file protected with the password "password" in read-only mode.

For the complete sample, please refer to the following file:

basics/WorkbookSample.java

4.4. Saving a Workbook

A workbook can be saved in any format supported by the current version of Excel. The FileFormat class from the com.jniwrapper.win32.jexcel package is an enumeration of all file formats supported by Excel version 9.0. The default Excel file format is FileFormat.WORKBOOKNORMAL. All changes in an opened workbook can be saved by calling the Workbook.save() method:

        workbook.save();

The Workbook.save() method should be called only if the workbook was opened not in read-only mode, or was created and saved before.

To save a newly created workbook or to save a workbook to another file or format, call the Workbook.saveAs() method:

        //Saving the workbook to a new file in the default Excel format
        File newXlsFile = new File("C:\\Workbook2.xls");
        workbook.saveAs(newXlsFile, FileFormat.WORKBOOKNORMAL, true);

        //Saving the workbook to a new file in the XML format
        File newXmlFile = new File("C:\\Workbook2.xml");
        workbook.saveAs(newXmlFile, FileFormat.XMLSPREADSHEET, true);

The Workbook.saveAs() method saves a workbook to a different file and makes it active in Excel. To make the current workbook active after saving its copy, use the Workbook.saveCopyAs() method:

        //Saving a copy of the workbook
        File workbookCopy = new File("C:\\WorkbookCopy.xls");
        workbook.saveCopyAs(workbookCopy);

For the complete sample, please refer to the following file:

basics/WorkbookSample.java

4.5. Obtaining and Modifying Workbook Additional Information

The Workbook class provides functionality for obtaining a workbook name, title, author, and file path:

        String fileName = workbook.getFile().getAbsolutePath();
        String name = workbook.getName();
        String title = workbook.getTitle();
        String author = workbook.getAuthor();

The Workbook.getHasPassword() method allows you to know whether a workbook is protected by a password:

        if (workbook.getHasPassword())
        {
            System.out.println("The workbook is protected with a password");
        }
        else
        {
            System.out.println("The workbook is not protected with a password");
        }

To know whether a workbook is opened in read-only mode, call the Workbook.isReadOnly() method:

        if (workbook.isReadOnly())
        {
            System.out.println("Read-only mode");
        }

All the workbook attributes mentioned above, except the workbook name, can be modified by calling the appropriate methods of the Workbook class:

        workbook.setTitle("New title");
        workbook.setPassword("xxx001");
        workbook.setAuthor("John Smith");

The workbook name is the workbook file name which is modified automatically with the file name change.

For the complete sample, please refer to the following file:

basics/WorkbookSample.java

4.6. Managing Worksheets

JExcel allows you to manage worksheets by different ways.

A worksheet can be obtained via its name or index using the Workbook.getWorksheet() method:

        //Obtaining a worksheet by its name
        Worksheet customSheet = workbook.getWorksheet("Custom sheet");

        //Obtaining a worksheet by its index
        int lastIndex = workbook.getWorksheetsCount();
        Worksheet lastWorksheet = workbook.getWorksheet(lastIndex);

To add a new worksheet to the workbook, use the Workbook.addWorksheet() method. This method allows you to add a new worksheet after the specified worksheet and assign the name of the new worksheet:

        Worksheet sheet2 = workbook.getWorksheet("Sheet2");
        workbook.addWorksheet(sheet2, "Custom sheet");

To add a new worksheet to the beginning of the worksheet list, pass null in place of the worksheet parameter or use the Workbook.addWorksheet() method with a single name parameter. The following function calls are equal:

        {
            Worksheet worksheet1 = workbook.addWorksheet("New sheet 1");
        }
        {
            Worksheet worksheet1 = workbook.addWorksheet(null, "New sheet 1");
        }

The Workbook.getWorksheets() method allows you to get a list of all workbook worksheets :

        List worksheets = workbook.getWorksheets();

        for (int i = 0; i < worksheets.size(); i++)
        {
            Worksheet worksheet = (Worksheet)worksheets.get(i);
            //Some action
        }

A worksheet also can be moved to a specified position using the Workbook.moveWorksheet() method. method. The method takes worksheet instance for moving and another after worksheet reference after which, the moved worksheet will be placed. If null is passed to the after parameter, the worksheet will be moved to the beginning of the worksheet list:

        //Moving customSheet to the end of the worksheet list
        workbook.moveWorksheet(customSheet, lastWorksheet);

        if (customSheet.getIndex() == workbook.getWorksheetsCount())
        {
            System.out.println(customSheet.getName() + " is the last worksheet.");
        }

        //Moving customSheet to the beginning of the worksheet list
        workbook.moveWorksheet(customSheet, null);

        if (customSheet.getIndex() == 1)
        {
            System.out.println(customSheet.getName() + " is the first worksheet.");
        }

In the same way you can copy worksheets using copyWorksheet() method as inside single workbook and between different workbooks:

        GenericWorkbook workbook = application.createWorkbook(null);
        GenericWorkbook anotherWorkbook = application.createWorkbook(null);

        Worksheet worksheet = workbook.addWorksheet(null, null);
        //Here we copy worksheet as a last worksheet
        anotherWorkbook.copyWorksheet(worksheet, null, anotherWorkbook.getWorksheet(3));
        //Here we insert worksheet copy before second worksheet
        anotherWorkbook.copyWorksheet(worksheet, anotherWorkbook.getWorksheet(2), null);

High level mergeWorkbook() method allows copying all worksheets from the specified workbook into another workbook instance:

        GenericWorkbook firstWorkbook = application.createWorkbook(null);
        GenericWorkbook secondWorkbook = application.createWorkbook(null);

        //Merging workbook instances
        firstWorkbook.mergeWorkbook(secondWorkbook);
        //Merging workbook with workbook file
        firstWorkbook.mergeWorkbook(new File("c:/test.xls"));

Please note that this method copies only worksheets but skips VBA scripts.

To remove a worksheet from a workbook, call the Workbook.removeWorksheet() method. Note that a workbook must contain at least one worksheet, so the last worksheet cannot be removed.

        //Removing customSheet
        workbook.removeWorksheet(customSheet);

For the complete sample, please refer to the following file:

basics/WorksheetSample.java

4.7. Working with Workbook Windows

Every Excel workbook is displayed in a separate window. The JExcel library provides the Window class for obtaining and modifying window properties. This class resides in the com.jniwrapper.win32.jexcel package.

The Application.getWindows() method allows you to get all windows belonging to the application:

        List windows = application.getWindows();
        for (int i = 0; i < windows.size(); i++)
        {
            Window window = (Window)windows.get(i);
            //Some action
        }

A window can also be obtained for a single workbook:

        Window window = workbook.getWindow();

The Window class allows you to get and modify the window caption, size, zoom and window state. To work with window states, JExcel provides the Window.State class, which is an enumeration of MINIMIZED, MAXIMIZED and NORMAL constants. The following sample demonstrates how to get the window properties:

        Window window = workbook.getWindow();

        System.out.println("Caption: " + window.getCaption());
        System.out.println("Width: " + window.getWidth());
        System.out.println("Height: " + window.getHeight());
        System.out.println("State: " + printState(window));
        System.out.println("Zoom: " + window.getZoom());
        System.out.println("Index: " + window.getIndex());

All the window properties, except the index, can be modified:

        Window window = workbook.getWindow();

        window.setCaption("New window caption");
        window.setState(Window.State.NORMAL);
        window.setHeight(450);
        window.setWidth(600);
        window.setZoom(150.0);

To know what workbook is currently displayed in the window, call the Window.getWorkbook() method:

        Workbook workbook = window.getWorkbook();

For the complete sample, please refer to the following file:

basics/WindowsSample.java

4.8. Working with a List of Recently Opened Files

The Application class allows you to obtain a list of recently opened Excel files:

        List files = application.getRecentFiles();
        for (int i = 0; i < files.size(); i++)
        {
            File file = (File)files.get(i);
            //Some action
        }

If a workbook is opened or saved, it doesn't fall into the list of recently opened files automatically. The Application.addToRecentFiles() method allows you to add a file to the recently opened files list manually:

        File file = new File("C:\\Workbook.xls");
        application.addToRecentFiles(file);

For the complete sample, please refer to the following file:

basics/ApplicationSample.java

Chapter 5. Event Listening and Handling


Excel sends notifications about its various events. JExcel provides the ApplicationEventListener, WorksheetEventListener and WorkbookEventListener interfaces for the events related to applications, worksheets, and workbooks respectively.

Excel also generate some controlled events that can affect a workbook or worksheet behavior. Note that listeners and adapters allow you just to obtain notifications. To hande such events, there are also the WorkbookEventHandler and WorksheetEventHandler interfaces provided. A notification about a controlled event is sent to event listeners after it is processed by a handler, because the handler can forbid processing the event and in this case listeners don't obtain the notification. Instances of Application, Workbook and Worksheet can have several listeners, however each instance cannot have more than one event handler.

5.1. Application Events

Excel sends notifications about the following application events:

  • A new workbook is created.

  • A workbook is opened.

JExcel provides the ApplicationEventListener interface for obtaining notifications about application events. This interface resides in the com.jniwrapper.win32.jexcel package. The following sample demonstrates how to add a listener of application events:

        Application application = new Application();
        application.addApplicationEventListener(new ApplicationEventListener()
        {
            public void newWorkbook(ApplicationEventObject eventObject)
            {
                System.out.println(eventObject.getWorkbook().getName() + " workbook is created.");
            }

            public void openWorkbook(ApplicationEventObject eventObject)
            {
                System.out.println(eventObject.getWorkbook().getName() + " workbook is opened.");
            }
        });

For the complete sample, please refer to the following file:

basics/ApplicationEventsSample.java

5.2. Workbook Events

Excel sends notifications about the following workbook events:

  • A workbook is activated.

  • A workbook is deactivated.

  • A new worksheet is added to a workbook.

  • Before a workbook is saved.

  • Before a workbook is closed.

To obtain notifications about workbook events, JExcel provides the WorkbookEventListener interface and WorkbookEventAdapter class which is added as a convenience for creating listener objects. To handle controlled events, use the WorkbookEventHandler interface of the JExcel library. All these classes and interfaces reside in the com.jniwrapper.win32.jexcel package.

5.2.1. Listening to Workbook Events

JExcel provides the WorkbookEventListener interface and the WorkbookEventAdapter class. The WorkbookEventListener interface allows you to obtain a notification, but doesn't provide an ability to affect the workbook's behavior. The WorkbookEventAdapter class implements the WorkbookListener interface and contains empty method bodies. This class allows you to implement not all methods of the WorkbookEventListener interface, but to extend the WorkbookEventAdapter class and implement only necessary methods.

The following sample demonstrates how to add a listener of the workbook activation and deactivation events:

        workbook.addWorkbookEventListener(new WorkbookEventAdapter()
        {
            public void activate(WorkbookEventObject eventObject)
            {
                System.out.println("\"" + eventObject.getWorkbook().getName() + "\" is activated.");
            }

            public void deactivate(WorkbookEventObject eventObject)
            {
                System.out.println("\"" + eventObject.getWorkbook().getName() + "\" is deactivated.");
            }
        });

For the complete sample, please refer to the following file:

basics/EventListenersSample.java

5.2.2. Handling Workbook Events

To handle controlled events, use the WorkbookEventHandler interface provided. The methods of this interface are called when events that can be handled occur and the result affects the workbook's behavior.

The WorkbookEventHandler.beforeSave() method is called before processing the save operation. The handler can forbid the save operation by returning false. In this case the workbook will not be saved and the workbook event listeners will not obtain a notification about the beforeSave event. To allow saving the workbook, the handler should return true.

The WorkbookEventHandler.beforeClose() method is called before processing the workbook's close operation. To forbid the workbook closing, the handler should return false, otherwise the return value should be true. If the operation is forbidden, listeners will not obtain a notification about the beforeClose event.

The following sample demonstrates how to set up a workbook event handler:

        workbook.setEventHandler(new WorkbookEventHandler()
        {
            public boolean beforeClose(WorkbookEventObject eventObject)
            {
                //Allow closing any workbooks
                return true;
            }

            public boolean beforeSave(WorkbookEventObject eventObject)
            {
                //Forbid saving any workbooks
                return false;
            }
        });

For the complete sample, please refer to the following file:

basics/EventHandlersSample.java

5.3. Worksheet Events

Excel sends notifications about the following worksheet events:

  • A worksheet is activated.

  • A worksheet is deactivated.

  • A cell range of values is changed.

  • Selection in a worksheet is changed.

  • Before processing a double-click event.

  • Before processing a right-click event.

The JExcel library provides the WorksheetEventListener interface and the WorksheetEventAdapter class for listening to worksheet events and the WorksheetEventHandler for handling controlled events. All these classes and interfaces reside in the com.jniwrapper.win32.jexcel package.

5.3.1. Listening to Worksheet Events

The WorksheetEventListener interface allows you to obtain notifications about worksheet events. The WorksheetEventAdapter class is an empty implementation of the WorksheetEventListener interface and it is provided for more convenient implementation of some part of the interface.

The following sample demonstrates how to add a worksheet listener of activation, deactivation and worksheet changed events:

        worksheet.addWorksheetEventListener(new WorksheetEventAdapter()
        {
            public void changed(WorksheetEventObject eventObject)
            {
                System.out.println(eventObject.getRange().getAddress() + " is changed.");
            }

            public void activated(WorksheetEventObject eventObject)
            {
                System.out.println("\"" + eventObject.getWorksheet().getName() + "\" is activated.");
            }

            public void deactivated(WorksheetEventObject eventObject)
            {
                System.out.println("\"" + eventObject.getWorksheet().getName() + "\" is deactivated.");
            }
        });  

For the complete sample, please refer to the following file:

basics/EventListenersSample.java

5.3.2. Handling Worksheet Events

JExcel lets you handle controlled events via the WorksheetEventHandler interface.

The WorksheetEventHandler.beforeRightClick() method is called when the user right-clicks in a worksheet, but before processing the event. The handler can forbid the event processing by returning false. In this case neither listeners will obtain the notification, nor Excel will process the event (Excel usually shows a pop-up menu). Return true to allow processing the right-click event.

The WorksheetEventHandler.beforeDoubleClick() method is called when the user double-clicks a cell, but before processing the event. Return true to allow processing the double-click event, or false to forbid it.

The sample below demonstrates how to set up a worksheet event handler:

        worksheet.setEventHandler(new WorksheetEventHandler()
        {
            public boolean beforeDoubleClick(WorksheetEventObject eventObject)
            {
                //Forbid double-clicking on "A1" cell
                if (eventObject.getCell().equals(eventObject.getWorksheet().getCell("A1")))
                {
                    return false;
                }
                else
                {
                    return true;
                }
            }

            public boolean beforeRightClick(WorksheetEventObject eventObject)
            {
                //Allow right-clicking only on "A1" cell
                if (eventObject.getRange().equals(eventObject.getWorksheet().getRange("A1")))
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
        }); 

For the complete sample, please refer to the following file:

basics/EventHandlersSample.java

Chapter 6. Working with Cells


6.1. Referencing Cells

JExcel allows you to reference cells by coordinates, address or name. The com.jniwrapper.win32.jexcel package includes the Cell class which provides cell-related functionality. To obtain an instance of the Cell class, call the Worksheet.getCell() method.

The sample below demonstrates how to obtain cells by address and coordinates:

        //Obtaining a cell by its address
        Cell cell = worksheet.getCell("A1");

        //Obtaining the forth cell in the third row by coordinates
        Cell cell2 = worksheet.getCell(3, 4);

The Cell class has the getRow() and getColumn() methods that let you know the cell's coordinates. To know the cell's address, call the Cell.getAddress() method:

        String cellAddress = cell.getAddress();

The Cell.setName() method is used for creating named cells. To know whether the cell is named, call the Cell.getName() method. If the return value is null, the cell is not named, otherwise the method returns the cell's name.

        if (cell.getName() == null)
        {
            System.out.println(cell.getAddress() + " name is not set up.");
            cell.setName("Profit");
        }
        else
        {
            System.out.println(cell.getAddress() + " name is \"" + cell.getName() + "\"");
        }

Referencing named cells is done in the same way as passing the cell name instead of its address:

        //Obtaining a cell by its name
        Cell cell = worksheet.getCell("Profit");

For the complete sample, please refer to the following file:

cell/NamedCellsSample.java

6.2. Setting New Values

The current version of the JExcel library allows you to assign values of four types: double, long, String and java.util.Date. The Cell class has the setValue() method which takes a value of any of the mentioned types.

The sample below demonstrates the way of setting cell values:

        Cell cell = worksheet.getCell("A1");
        //Setting a string value
        cell.setValue("String value");

        cell = worksheet.getCell("A2");
        //Setting a long value
        cell.setValue(220);

        cell = worksheet.getCell("A3");
        //Setting a double value
        cell.setValue(122.1);

        cell = worksheet.getCell("A4");
        //Setting a Date value
        cell.setValue(new Date());

        //Setting a formula
        cell.setValue("=SUM(A1:B12)");

For the complete sample, please refer to the following file:

cell/SettingValuesSample.java

6.3. Obtaining Existing Values

The Cell class provides several methods for obtaining values of different types:

  • The Cell.getNumber() method returns a numeric value as an instance of the java.lang.Number class. If the cell value cannot be converted to a number, null is returned.

  • The Cell.getDate() method returns a date value as an instance of the java.util.Date class. If the cell value cannot be converted to the date format, null is returned.

  • The Cell.getString() method returns a cell value in the string format.

        //Getting a string value
        cell = worksheet.getCell("A3");
        String strValue = cell.getString();
        System.out.println("A3 string value: " + strValue);

        //Getting a double value
        cell = worksheet.getCell("C2");
        Number numValue = cell.getNumber();
        double doubleValue = numValue.doubleValue();
        System.out.println("C2 double value: " + doubleValue);

        //Getting a Date value
        cell = worksheet.getCell("D1");
        Date dateValue = cell.getDate();
        System.out.println("D1 date value: " + dateValue);

If a cell contains a formula, methods for getting a cell value return a calculated result. To know whether the cell value is calculable or not, call the Cell.hasFormula() method, which returns true if the cell value is calculable or false if otherwise. The Cell.getFormula() method allows to obtain a cell's formula in the string format:

        //Getting cell's formula and value
        cell = worksheet.getCell("B5");

        if (cell.hasFormula())
        {
            String formula = cell.getFormula();
            System.out.println("B5 formula: " + formula);
        }
        long value = cell.getNumber().longValue();
        System.out.println("B5 value: " + value);

Also, the Cell class provides functions letting you to know whether a cell is empty (the Cell.isEmpty() method), whether a cell contains a text value (the Cell.isText() method), or a numeric one (the Cell.isNumber() method) or an error value (the Cell.isError() method). All these methods return a boolean value.

For the complete sample, please refer to the following file:

cell/GettingValuesSample.java

Chapter 7. Working with Ranges


7.1. Referencing Ranges

JExcel provides the Range class for working with ranges of cells. To obtain an instance of the Range class, the Worksheet class has the Workbook.getRange() method. This method takes a string representation of a necessary range of cells in any format supported by Excel. Despite this fact, it is recommended to refer to only simple ranges like "A1:V1" or "B23:AA45" and avoid referencing a range like "A1:B4;A7;D12:G45", because compound ranges use a separator (in the example it is the ';' symbol) which differs depending on Excel settings. So an application that uses compound range references may work incorrectly if Excel has another separator settings.

        //Referencing the range "A1:G12
        Range simpleRange = worksheet.getRange("A1:G12");

        //Not recommended
        Range compoundRange = worksheet.getRange("B1:B4;D11;H1:H13");

For creating compound ranges, the Range class provides the Range.include() method which takes a range to be included as an instance of the Range class or its string representation. The Range.include() method returns a Range class instance that represents the extended range. This makes it possible to create a compound range using a chain of method calls:

        //Creating compound ranges
        {
            Range range = worksheet.getRange("B1:B4");
            range.include("D11");
            range.include("H1:H13");
        }
        //More convenient way
        {
            Range range = worksheet.getRange("B1:B4").include("D11").include("H1:H13");
        }

To know the range's address, call the Range.getAddress() method:

        String rangeAddress = range.getAddress();

For the complete sample, please refer to the following file:

range/ReferencingRangesSample.java

7.2. Converting a Cell to a Range and Vice Versa

The Range class has a public constructor Range(Cell) that allows you to convert a cell to an appropriate range.

The following sample demonstrates this technique:

        Cell cell = worksheet.getCell("A1");

        //Converting a cell to a range
        Range range = new Range(cell);

To obtain a list of range cells, call the Range.getCells() method:

        //Converting a range to cells
        Range range = worksheet.getRange("B12:D12");
        List cells = range.getCells();
        for (int i = 0; i < cells.size(); i++)
        {
            Cell cellFromRange = (Cell)cells.get(i);
            //Some action
        }

For the complete sample, please refer to the following file:

range/ReferencingRangesSample.java

7.3. Finding Values in Ranges

The Range class provides two methods Range.find() and Range.findNext() for finding a value in a range. The Range.find() method finds a first occurrence of the value. The Range.findNext() method doesn't take any parameters and returns the location of the next occurrence of the value passed to the previous call of Range.find(). JExcel allows you to find values of four types: double, long, String and java.util.Date. The Range.find() method may take any of these types.

The Range.find() method allows to specify search attributes by passing an instance of the Range.Search Attributes class to the function call. The Range.SearchAttributes class provides functionality for specifying the following settings:

  • Case-sensitive search.

    To specify whether the search is case-sensitive or not, call the Range.SearchAttributes.setCaseSensitive() method passing true or false respectively. By default, the search is case-insensitive.

  • Forward or backward search direction.

    Call the Range.SearchAttributes.setForwardDirection() method passing true to set forward direction search or false if otherwise. By default, search is performed in forward direction.

  • Searching for the whole phrase.

    To set search for the whole phrase, call the Range.SearchAttributes.setFindWholePhrase() method passing true, otherwise Excel will try to find the value as part of a phrase (the default setting).

  • Type of information to be searched for.

    Search can be performed in values, formulas or comments. To specify the type of information to be searched, call the Range.SearchAttributes.setLookIn() method which takes one of the predefined instances of the Range.FindLookIn class: Range.FindLookIn.VALUES, Range.FindLookIn.COMMENTS or Range.FindLookIn.FORMULAS. The default value is Range.FindLookIn.VALUES.

If a value is found in the range, the Range.find() method returns the cell where the value was found, otherwise the method returns null. The Range.findNext() works similarly.

The sample below demonstrates the technique of searching for values of different types:

        //Getting a necessary range
        Range range = worksheet.getRange("A1:C5").include("D1");

        //Specifying search attributes
        Range.SearchAttributes searchAttributes = new Range.SearchAttributes();
        searchAttributes.setCaseSensetive(true);
        searchAttributes.setLookIn(Range.FindLookIn.VALUES);
        searchAttributes.setForwardDirection(true);

        //Looking for a string value
        String strValue = "Grapefruit";
        Cell cell = range.find(strValue, searchAttributes);
        if (cell == null)
        {
            System.out.println("\"" + strValue + "\" was not found.");
        }
        else
        {
            System.out.println("\"" + strValue + "\" was found in " + cell.getAddress());
        }
        cell = range.findNext();
        if (cell == null)
        {
            System.out.println("\"" + strValue + "\" was not found.");
        }
        else
        {
            System.out.println("\"" + strValue + "\" was found in " + cell.getAddress());
        }

        //Looking for a long calculated value
        long longValue = 39;
        cell = range.find(longValue, searchAttributes);
        if (cell == null)
        {
            System.out.println(longValue + " was not found.");
        }
        else
        {
            System.out.println(longValue + " was found in " + cell.getAddress());
        }

For the complete sample, please refer to the following file:

range/FindValuesSample.java

7.4. Sorting a Range

JExcel provides functionality for sorting rows or columns in a range of cells. For this purpose, use the Range.sort() method. It takes a column name (for example, "A", "D", "AB") for sorting rows by the values of the specified column and a row number (for example, 1, 4, 100) for sorting columns by the values of the specified row.

For sorting values in the ascending order, set the ascending argument to true (or false if otherwise). If the caseSensitive argument is true, case-sensitive sorting will be done.

The following sample demonstrates the technique of sorting ranges:

        //Getting a necessary range
        Range range = worksheet.getRange("A1:D5");

        //Sorting the range by column "A", in the ascending order, case-sensitive
        range.sort("A", true, true);

        //Sorting the range by the third row, in the descending order, case-sensitive
        range.sort(3, false, true);

For the complete sample, please refer to the following file:

range/SortValuesSample.java

7.5. Obtaining Ranges Intersection

The Range class has two methods for working with range intersections: the Range.intersects(Range) method allows you to know whether the current range intersects the specified one, and the Range.getIntersection(Range) method returns the range of common cells of the current range and the specified one:

        //Getting necessary ranges
        Range range1 = worksheet.getRange("A1:B3");
        Range range2 = worksheet.getRange("A2:C5");

        //Checking intersections
        if (range1.intersects(range2))
        {
            Range commonRange = range1.getIntersection(range2);
            System.out.println(range1 + " intersects " + range2 + ". The common range is " + commonRange);
        }

For the complete sample, please refer to the following file:

range/IntersectionSample.java

7.6. Merging and Unmerging Cells of a Range

The Range.merge() method allows you to merge cells of a range. Note that all data in the merged cell will be lost in this case.

        //Getting the necessary range
        Range range1 = worksheet.getRange("A1:B3");

        //Merging cells of range1
        range1.merge();

To unmerge cells of a range, call the Range.unmerge() method:

        //Unmerging cells of range1
        range1.unmerge();

For the complete sample, please refer to the following file:

range/IntersectionSample.java

Chapter 8. Reading and writing bulk operations


This chapter describes the technique of reading and writing bulk operations, which allows improve the performance of data exchange with Excel worksheets. MS Excel has setValue2 and getValue2 interfaces for these operations.

8.1. Reading bulk operation

If you try to iterate over cells in range to read cell values the performance will be extremely low. Such result is predictable because each cell reading operation produces several COM interface related Java objects, which allocate some physical memory and will not be immediately removed from memory after interface call. Large numbers of the objects can result out of memory. To avoid it and improve reading performance you can use getValues() method:

        //Getting a values from range
        Range range = worksheet.getRange("A1:A3");
        Variant[][] results = range.getValues();

The method wraps getValue2 interface of MS Excel and improves reading performance in several times.

8.2. Writing bulk operation

Like getValues() method, a coherent writing bulk operation fillWithArray() method in Worksheet class resolves the same issues. Using this method, you can insert the data from a Java array into the worksheet:

        //Here we create 2d String array and fill according range
        final int ARRAY_SIZE = 10;
        int count = 0;
        final String string_array[][] = new String[ARRAY_SIZE][ARRAY_SIZE] ;
        for (int i = 0; i < ARRAY_SIZE; i++)
        {
            for (int j = 0; j < ARRAY_SIZE ; j++)
            {
                string_array[i][j] = "result: "+Integer.toString(count);
                count++;
            }
        }
        sheet.fillWithArray("A1:J10", string_array);

The method wraps setValue2 interface of MS Excel and improves writing performance in several times.

For the complete sample, please refer to the following file:

range/RangFillingSample.java

Chapter 9. Customizing Cells and Ranges


This chapter discusses both Cell and Range classes. The functionality for customizing a cell or a range is identical, so the code samples below demonstrate techniques only for the Range class.

9.1. Changing a Number Format

The range number format reflects the way of displaying numeric data. For instance, a double value can be represented as an integer or as a date. A number format in Excel is set by a string pattern in a specific locale-dependent format. For detailed specification of the number format, please refer to appropriate articles on Excel.

JExcel allows you to obtain the current number format of a range as a string. For this purpose, use the Range.getNumberFormat() method:

        //Getting a range number format
        Range range = worksheet.getRange("A1:A3");
        String numberFormat = range.getNumberFormat();
        System.out.println("A1:A3 number format is " + numberFormat);

The Range.setNumberFormat(String) method changes the number format of a range:

        //Setting a custom number format
        String newNumberFormat = "0,00%";
        range.setNumberFormat(newNumberFormat);
        System.out.println("A1:A3 new number format is " + range.getNumberFormat());

For the complete sample, please refer to the following file:

range/CustomizationSample.java

9.2. Customizing Text Alignment and Orientation

9.2.1. Changing Text Alignment

JExcel provides the TextAlignment class, which is an enumeration of all alignment types supported by Excel. The Range (Cell) class enables you to obtain and modify both horizontal and vertical text alignment.

The Range.getHorizontalAlignment() method returns the current horizontal text alignment as an instance of the TextAlignment class. The possible values are TextAlignment.CENTER, TextAlignment.DISTRIBUTED, TextAlignment.FILL, TextAlignment.GENERAL, TextAlignment.JUSTIFY, TextAlignment.LEFT and TextAlignment.RIGHT. If the range cells have mixed horizontal alignment, the return value is null. To change horizontal alignment of a range, call the Range.setHorizontalAlignment() method:

        //Setting custom horizontal text alignment
        range.setHorizontalAlignment(TextAlignment.RIGHT);

        //Checking horizontal text alignment
        if (range.getHorizontalAlignment().equals(TextAlignment.RIGHT))
        {
            System.out.println("A1:A3 range: new horizontal text alignment was applied successfully.");
        }
        else
        {
            System.out.println("Horizontal text alignment failed to be applied.");
        }

Working with vertical text alignment is similar to horizontal alignment: the Range.setVerticalAlignment() method sets a new value and the Range.getVerticalAlignment() returns the current setting as an instance of the TextAlignment class. The possible values are TextAlignment.BOTTOM, TextAlignment.CENTER, TextAlignment.DISTRIBUTED, TextAlignment.TOP and TextAlignment.JUSTIFY. If the range cells have mixed vertical alignment, the return value is null.

        //Setting custom vertical text alignment
        range.setVerticalAlignment(TextAlignment.TOP);

        //Checking vertical text alignment
        if (range.getVerticalAlignment().equals(TextAlignment.TOP))
        {
            System.out.println("A1:A3 range: new vertical text alignment was applied successfully.");
        }
        else
        {
            System.out.println("Vertical text alignment failed to be applied.");
        }

For the complete sample, please refer to the following file:

range/CustomizationSample.java

9.2.2. Changing Text Orientation

For working with a range (cell) text orientation, JExcel provides the TextOrientation class. This class is an enumeration of all supported text orientation types. The Range.getTextOrientation() method returns the current text orientation setting as an instance of the TextOrientation class. The possible values are TextOrientation.DOWNWARD, TextOrientation.HORIZONTAL, TextOrientation.UPWARD and TextOrientation.VERTICAL. If the range cells have mixed text orientation, the return value is null. To change text orientation, call the Range.setTextOrientation() method:

        //Setting up custom text orientation
        range.setTextOrientation(TextOrientation.UPWARD);

        //Checking text orientation
        if (range.getTextOrientation().equals(TextOrientation.UPWARD))
        {
            System.out.println("A1:A3 range: new text orientation was applied successfully.");
        }
        else
        {
            System.out.println("Text orientation failed to be changed.");
        }

For the complete sample, please refer to the following file:

range/CustomizationSample.java

9.3. Customizing Text Font

The Font class from the com.jniwrapper.win32.jexcel package provides the ability to customize fonts in Excel.

The following font attributes can be obtained or modified:

  • Font name

    Call the Font.getName() method to get the font name, and the Font.setName(String) method to specify the font name.

  • Font size

    Call the Font.getSize() method to get the font size, and the Font.setSize() method to specify the font size.

  • General font styles

    The Font class allows you to specify whether the font is bold, italic or strike-through.

  • Font underline style

    The Font.UnderlineStyle class is an enumeration of five underline styles supported by Excel: UnderlineStyle.NONE, UnderlineStyle.SINGLE, UnderlineStyle.SINGLEACCOUNTING, UnderlineStyle.DOUBLE, and UnderlineStyle.DOUBLEACCOUNTING. The Font.getUnderlineStyle() method returns the current underline style as an instance of the Font.UnderlineStyle class. The return value can be one of the predefined values listed above. To change an underline style, call the Font.setUnderlineStyle() method.

  • Font color

    Call the Font.getColor() method to get the currently set font color, and the Font.setColor() method to specify the font color. A color value in both functions is an instance of the java.awt.Color class.

  • Font alignment style

    The Font class allows you to specify whether the text is normally aligned, subscript or superscript using the Font.setAlignment() method. This method takes one of the three predefined instances of the Font.Alignment class: Alingment.NORMAL, Alignment.SUBSCRIPT or Alignment.SUPERSCRIPT. To obtain the current font alignment, call the Font.getAlignment() method.

The following sample demonstrates the technique of changing text font:

        //Creating a new instance of the com.jniwrapper.win32.jexcel.Font class
        Font font = new Font();

        //Changing font name
        font.setName("Courier New");

        //Changing font styles
        font.setBold(true);
        font.setStrikethrough(true);
        font.setUnderlineStyle(Font.UnderlineStyle.DOUBLE);

        //Changing font color
        font.setColor(Color.ORANGE);

        //Applying new font setting
        range.setFont(font);

To compare constant instances of the Font.Alignment or Font.UnderlineStyle classes, use the equals() method. For example:

    public String getAlignmentAsString(Font.Alignment alignment)
    {
        if (alignment.equals(Font.Alignment.SUBSCRIPT))
        {
            return "Subscript";
        }
        else if (alignment.equals(Font.Alignment.SUPERSCRIPT))
        {
            return "Superscript";
        }
        else
        {
            return "Normal";
        }
    }

For the complete sample, please refer to the following file:

range/FontOperationsSample.java

9.4. Changing Background Pattern and Color in Cells

The JExcel library provides the following ways to customize a range (or cell) background:

  • Changing the background color.

    Call the Range.getInteriorColor() method to obtain the background color and the Range.setInteriorColor() method to specify the background color:

            //Getting the interior color
            java.awt.Color interiorColor = range.getInteriorColor();
    
            //Changing the interior color
            range.setInteriorColor(Color.BLUE);
  • Changing the background pattern.

    Excel allows you to set up various kinds of background patterns. JExcel provides the InteriorPattern class which is an enumeration of all kinds of background patterns supported by Excel version 1.5.

    The following sample demonstrates how to get and set the background pattern:

            //Getting the interior pattern
            InteriorPattern interiorPattern = range.getInteriorPattern();
    
            //Changing the interior pattern
            range.setInteriorPattern(InteriorPattern.DOWN);
  • Changing the background pattern color.

    Call the Range.getInteriorPatternColor() method to obtain the background pattern color and the Range. setInteriorPatternColor() method to specify the background pattern color:

            //Getting the interior pattern color
            java.awt.Color interiorPatternColor = range.getInteriorPatternColor();
    
            //Changing the interior pattern color setting
            range.setInteriorPatternColor(Color.RED);

For the complete sample, please refer to the following file:

range/InteriorCustomizationSample.java

9.5. Border Customization

To customize a range or cell border, you need to specify the kind of border to work with. There are several standard kinds of borders which are provided in JExcel as instances of the Border.Kind class. For example, Border.Kind.EDGELEFT or Border.Kind.EDGETOP.

The Range.getBorder() method allows you to obtain an instance of the Border class that corresponds to some border kind. The Border class resides in the com.jniwrapper.win32.jexcel package.

The Border class provides functionality for working with border color, line style and line weight. The line style is set using the constants from the Border.LineStyle class, such as LineStyle.CONTINUOUS, LineStyle.DASHDOT, etc. The line weight is set using the constants from the Border.LineWeight class: LineWeight.HAIRLINE, LineWeight.MEDIUM, etc.

The following sample demonstrates the technique of customizing the range border:

        //Getting the top border
        Border topBorder = range.getBorder(Border.Kind.EDGETOP);

        //Getting the border style
        java.awt.Color borderColor = topBorder.getColor();
        Border.LineStyle lineStyle = topBorder.getLineStyle();
        Border.LineWeight lineWeight = topBorder.getWeight();

        //Setting new border style
        Border border = new Border();
        border.setColor(Color.CYAN);
        border.setLineStyle(Border.LineStyle.DASHDOT);
        border.setWeight(Border.LineWeight.MEDIUM);

        //Applying the border settings to the top border
        range.setBorder(Border.Kind.EDGETOP, border);

For the complete sample, please refer to the following file:

range/BorderCustomizationSample.java

Chapter 10. Integrating a Workbook into a Java Swing Application


JExcel provides the JWorkbook class which allows you to embed an Excel workbook into a Java Swing application as an ordinary component. The JWorkbook component is an OLE container for an Excel workbook and provides functionality for working with its contents. However, an embedded workbook is a little less functional than an ordinary one.

The sample below demonstrates the technique of embedding JWorkbook into JFrame:

        JFrame frame = new JFrame();
        Container cp = frame.getContentPane();
        cp.setLayout(new BorderLayout());

        //Creating a JWorkbook instance
        JWorkbook jWorkbook = new JWorkbook();

        cp.add(jWorkbook);    

The JWorkbook.close() method closes the embedded workbook.

10.1. Opening and Saving an Embedded Workbook

A workbook can be opened in the JWorkbook component in two ways: using the JWorkbook(File)constructor or the JWorkbook.openWorkbook()method. An embedded workbook can be saved by calling the JWorkbook.saveCopyAs(File) method.

        File workbookFile = new File("C:\\Workbook.xls");

        //Opening the specified file in the JWorkbook component
        JWorkbook jWorkbook = new JWorkbook(workbookFile);

        //Some actions...
        File newWorkbook = new File("C:\\NewWorkbook.xls");
        jWorkbook.saveCopyAs(newWorkbook);         

10.2. Printing an Embedded Workbook

JWorkbook allows you to print an embedded workbook by displaying the Print dialog or without it. If the Print dialog appears, the printing process starts after the user clicks the "Print" button. To display the Print dialog, call the JWorkbook.showPrintDialog() method:

        JWorkbook jWorkbook = new JWorkbook();
        jWorkbook.showPrintDialog();

To print a workbook without displaying the Print dialog, call the JWorkbook.print() method:

        jWorkbook.print(1);

Also, the JWorkbook class lets you display an embedded workbook in preview mode. For this purpose, call the JWorkbook.printPreview() method.

10.3. Displaying a Workbook in Static Mode

A workbook can be displayed in static (read-only) or normal mode. The normal mode is the default one. If it is necessary to display just a workbook's snapshot and forbid any UI actions, switch to static mode. To display a workbook in static mode, call the JWorkbook.setStaticMode(boolean) method:

        JWorkbook jWorkbook = new JWorkbook();

        //Setting up the static mode
        jWorkbook.setStaticMode(true);     

10.4. Listening to JWorkbook Events

JExcel allows you to obtain notifications about the following JWorkbook events:

  • A new workbook is created.

  • A workbook is opened.

  • A workbook is saved.

  • A workbook is closing.

  • A workbook is switched to print preview mode or back.

To listen to JWorkbook events, use the JWorkbookEventListener interface or the JWorkbookEventAdapter class. JWorkbookEventAdapter is an empty implementation of the JWorkbookEventListener interface. The following sample demonstrates the technique of how to add a listener of the JWorkbook events:

        _workbook.addJWorkbookEventListener(new JWorkbookEventAdapter()
        {
            public void newWorkbook(JWorkbookEventObject eventObject)
            {
                System.out.println("New workbook: " + eventObject.getWorkbook().getName() + ".");
            }

            public void workbookOpened(JWorkbookEventObject eventObject)
            {
                System.out.println("Opened workbook: " + eventObject.getWorkbook().getName() + ".");
            }

            public void beforeWorkbookClose(JWorkbookEventObject eventObject) throws JWorkbookInterruptException
            {
                System.out.println("Workbook \"" + eventObject.getWorkbook().getName() + "\" closed.");
            }

            public void printPreviewStateChanged(JWorkbookEventObject eventObject)
            {
                if (eventObject.getJWorkbook().isPrintPreview())
                {
                    printLog("Workbook \"" + eventObject.getWorkbook().getName() + "\" is in the print preview mode.");
                }
                else
                {
                    printLog("The print preview mode is closed.");
                }
            }
        });

For more information please refer to the following: JExcelDemo.java and JExcelDemo application

10.5. Multiple JWorkbook instances

Since JExcel ver.1.4 JWorkbook allows creating multiple embedded MS Excel workbook instances in Swing applications. These instances are connected to common MS Excel process. Such feature allows implementing MDI applications and provides more flexible way for MS Excel documents processing.

This feature is implemented without changes in JWorkbook public interfaces so to achieve several JWorkbook instances you just need instantiating several JWorkbook objects:

        JFrame frame = new JFrame("JWorkbooks on tabs");

        frame.setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);
        Container cp = frame.getContentPane();
        cp.setLayout(new BorderLayout());
        JPanel panel = new JPanel();
        cp.add(panel);
        panel.setLayout(new BorderLayout());

        JWorkbook workbook = new JWorkbook(false);
        JWorkbook anotherWorkbook = new JWorkbook(false);

        JTabbedPane tabbedPane = new JTabbedPane();
        panel.add(tabbedPane, BorderLayout.CENTER);
        tabbedPane.addTab("First JWorkbook", workbook);
        tabbedPane.addTab("Second JWorkbook", anotherWorkbook);

        frame.setBounds(100, 100, 500, 500);
        frame.setVisible(true);

This code demonstrates creating two JWorkbook objects on tabbed interface.

For more information please refer to corresponding samples in JExcel package.

10.6. JWorkbook objects handling on application exit

If you are using JWorkbook objects in different JFrames you need correctly handling application exit closing all JWorkbook instances. This issue is due by JWorkbook native resource releasing requirement. When you close the main window using EXIT_ON_CLOSE flag, JVM doesn't send the corresponding window close event to all opened JFrames and JWorkbook objects cannot be released automatically. Therefore in such case JWorkbook related application produces exception on exit and hangs up EXCEL process.

To avoid such issue, you need to call JWorkbook close() method for all JWorkbook objects on application exit:

private java.util.List jworkbookList = new LinkedList();

...

//Add all JWorkbook references to jworkbookList
JWorkbook jworkbook = new JWorkbook();
jworkbookList.add(jworkbook);

...

//On application window close correctly release JWorkbook objects
frame.addWindowListener(new WindowAdapter()
{
    public void windowClosing(WindowEvent e)
    {
      while (!jworkbookList.isEmpty())
      {
        JWorkbook jworkbook = (JWorkbook) jworkbookList.remove(0);
        if (jworkbook != null && !jworkbook.isClosed())
        {
            jworkbook.close();
        }
      }
    }
});

For the full sample code please refer to MultipleJWorkbookWindows.java file in JExcel package.

Chapter 11. Working with Native Peers and Thread Issues


JExcel provides peers for the following classes: Application, Workbook, Worksheet, Range, and Cell. The peers can be useful for implementation of Excel functions that are not available in the current version of JExcel. Excel controls are not thread-safe, therefore their functions should be called from the same thread. For the JExcel library, it is the OLE message loop. This is important when you are working with native peer objects, namely when calling methods and querying the interface.

The following sample demonstrates the correct usage of the Application native peer and the OLE message loop:

        Runnable runnable = new Runnable()
        {
            public void run()
            {
                _Application nativeApp = application.getPeer();
                BStr decSeparator = new BStr(newSeparator);
                nativeApp.setDecimalSeparator(decSeparator);
            }
        };
        application.getOleMessageLoop().doInvokeLater(runnable);       

The OleMessageLoop class provides two methods for executing methods in this thread: doInvokeLater() and doInvokeAndWait(). The difference between them is that the former returns immediately, and the latter blocks further execution while the task defined by Runnable method parameter is being executed. Use the doInvokeLater() method in a Swing thread for time-consuming tasks to prevent this thread from blocking.

NOTE: Native windows should be opened in the OleMessageLoop of JWorkbook to correctly repaint objects.

For the complete sample, please refer to the following file:

NativePeerSample.java

Chapter 12. Where to Get a New Version


To get the latest version of JExcel, please visit:

http://www.teamdev.com/jexcel/downloads.jsf

Copyright © 2002-2021 TeamDev Ltd.