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

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.

        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.");
            }
        });

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

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.

        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.");
            }
        });  

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

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

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

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

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

        //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.");
        }

        //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.");
        }

        //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.");
        }

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

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.