# JExcel Programmer's Guide Version: 1.0 Last Updated: April 04, 2006 Copyright © 2002-2006 TeamDev Ltd. # 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. 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 3. 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. ## 3.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` ## 3.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`. ## 3.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` ## 3.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` ## 3.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` ## 3.6. Managing Worksheets JExcel allows you to manage worksheets in various ways. A worksheet can be obtained by 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 obtain a list of all worksheets in a workbook:
            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. The method takes the `worksheet` parameter to indicate a worksheet to be moved and the `after` parameter to indicate after which worksheet it 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.");
            }
To remove a worksheet from a workbook, call the `Workbook.removeWorksheet()` method. Note that a workbook must contain at least one worksheet. That's why the last remaining worksheet cannot be removed.
            //Removing customSheet
            workbook.removeWorksheet(customSheet);
For the complete sample, please refer to the following file: `basics/WorksheetSample.java` ## 3.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` ## 3.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 4. 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. ## 4.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` ## 4.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. ### 4.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` ### 4.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` ## 4.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. ### 4.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` ### 4.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 5. Working with Cells ## 5.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` ## 5.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` ## 5.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 6. Working with Ranges ## 6.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` ## 6.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` ## 6.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` ## 6.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` ## 6.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` ## 6.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 7. 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. ## 7.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` ## 7.2. Customizing Text Alignment and Orientation ### 7.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` ### 7.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` ## 7.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` ## 7.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` ## 7.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 8. 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. ## 8.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);
## 8.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 J`Workbook.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. ## 8.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);
## 8.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 # Chapter 9. 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 10. Support If you encounter any problems or have questions regarding our product, please e-mail us at: JExcel-support@jniwrapper.com ## 10.1. Reporting Problems Should you experience any problem or find any bugs, please submit the issue to us using a special report form on the JNIWrapper site at the following address: http://www.jniwrapper.com/pages/support/reportForm The form will help you provide all necessary information. ## 10.2. Troubleshooting For finding a solution to your problem, please visit the Troubleshooting page on our site at: http://www.jniwrapper.com/pages/jniwrapper/tshoot This page is regularly updated using information from support requests. If you don't find a solution, please e-mail us at JExcel-support@jniwrapper.com or report the problem as described in the previous section. # Chapter 11. Where to Get a New Version To obtain the latest version of JExcel and receive up-to-date information, please visit: http://www.jniwrapper.com
| Copyright © 2002-2005 TeamDev Ltd. |