OpenXLS 13 Spreadsheet SDK

Introducing OpenXLS

Click for OpenXLS On GitHub

OpenXLS is an easy-to-use spreadsheet API that allows you to read, modify, and create Excel* compatible spreadsheets from your applications.

OpenXLS and ExtenXLS are used by companies around the world in diverse mission-critical applications.

With its best-in-class Excel* compatibility, your template input files can range from a variety of complex spreadsheets, such as surveys, checklists, what-if scenarios, and financial reports.

OpenXLS has dozens of methods which can create new WorkBooks from scratch, allowing you to insert new cells with text, and numeric data. With OpenXLS you can also customize the output with hundreds of fonts, colors, patterns, borders, formulas, named ranges, and built-in formats such as: currency, date, financial and numeric formatting patterns.

Quick-Start

This section is intended to get you started quickly programming with OpenXLS.

OpenXLS is designed for use by programmers familiar with the Java syntax, the concepts of file templates, and spreadsheets.

Basic use of the API requires the following steps:

1. Copy OpenXLS.jar and openxls.lic to a project directory. Make sure that the jar is in the classpath of your project. The OpenXLS.jar file needs to be on your classpath in order for the JVM to find the OpenXLS class files.

2. In your Java code, import the com.valkyrlabs.OpenXLS.* package.

3. Create a new WorkBookHandle. This can either be a new, empty WorkBook with three sheets, or you can parse an existing XLS file in the form of a byte array from a file or other data source. Review the WorkBookHandle constructors in the API documentation to see which WorkBookHandle is appropriate for your application.

4. Work with a WorkSheet by using the WorkBookHandle.getWorkSheet(String sheetname) method. You must catch the WorkSheetNotFoundException in case the expected sheet does not exist already in the file.

5. Access Cell values using the WorkSheetHandle.getCell(Sheetname:CellAddress) method. You must catch the CellNotFoundException in case the expected Cell does not exist already in the file. You can add a new Cell to the sheet and get a CellHandle with one line of code by using the CellHandle WorkSheetHandle.add(Object ob, String address) method.

6. Set and get the value of Cells using CellHandle.setCellVal(Object ob) and Object CellHandle.getCellVal() methods.

7. Stream the WorkBook bytes to an output file using the WorkBookHandle.getBytes() method. If you are writing a web application, you can send the bytes over a ServletResponse to a web browser or you can write them out to a file or to any byte array consumer.

How to use OpenXLS

OpenXLS can be broken down into four-levels of functionality:

• Application
• WorkBook
• WorkSheet
• Cell

We will explore each of these levels in-depth to give you a greater understanding of how to fully utilize all that OpenXLS has to offer.

Application-Level Functions

• Convert and output spreadsheets as XML, and HTML • Generate multiple output files from a single input template spreadsheet merging different data into each output file • Output spreadsheets to a variety of devices such as hard disks and networks, including client web browsers over HTTP, email clients, and XML consumers

WorkBook-Level Functions

• Create and modify new WorkBooks • Calculate formulas • Modify existing WorkBooks • Copy and insert WorkSheets between WorkBooks • Set row and column size defaults for WorkBooks • Create, access, and modify named ranges

WorkSheet Functions

• Manipulate and copy charts • Select and reorder sheet tabs • Get and set header and footer text for printing WorkSheets • Add, move, change, and delete rows and columns • Protect and unprotect WorkSheets • Get and set grouping and hiding for rows and columns • Get and set row and column formats • Get and set row and column size defaults for WorkSheets • Get and set sheet names • Add and remove cells, rows, and columns

Cell Functions

• Modify cell formatting • Create and modify formulas • Manipulate formula cell references • Calculate formulas and retrieve new values • Add, move, modify and delete cell values • Create hyperlinks for cells

The Application-Level Functions

The Application level functions cover the broad category of how OpenXLS can be used in your program.

Multiple Output Files from a Single Input Template Spreadsheet

The best way to get the most out of existing investments in spreadsheet development is by updating existing spreadsheets with data from databases. The ability to reuse formulas, charts, formatting and VB macros from a single template and output it many thousands of times with different data saves countless hours of copying and pasting, eliminating human error, and ensuring standardization within your organization.

Whether you are outputting financial spreadsheets, compliance checklists, or portfolio performance charts, using OpenXLS will ensure that your end-users are able to access their information in a highly functional format that they are confidently familiar with. Spreadsheet Output

OpenXLS can be output to a variety of devices such as: XML, HTML, networks, including client web browsers, over HTTP, email clients, and hard disks. By automating the generation of spreadsheets, users get their information when and where it is most useful to them.

Convert to XML and HTML

OpenXLS can convert any spreadsheet to XML, and HTML. With appropriate XSLT files the API can transform the XML output to HTML using a single line of code.

This highly flexible architecture gives you the ability to create your own XSLT files with custom handling for application-specific output, as well as alternative output formats such as PDF.

You can also write XML applications that consume XML output from your spreadsheet spreadsheets. A template can be updated with data fetched from a database, results can be outputted as XML (formulas will be executed and the XML output will reflect the calculated values).

The WorkBook Functions

OpenXLS is not strictly a template-based API. With OpenXLS, you can create new spreadsheet files, cells, formats, and formulas. Using the dynamic capabilities of OpenXLS you can also convert data programmatically into spreadsheet files, and then handle the spreadsheet output as you would any other apllication.

The com.valkyrlabs.OpenXLS.WorkBookHandle object is your primary handle to your spreadsheet file.

With WorkBookHandle you can:

• Create a WorkBooks • Modify existing WorkBooks • Copy and Insert WorkSheets between WorkBooks • Set row and column size defaults for WorkBooks • Calculate formulas

The WorkBookHandle object represents the WorkBook portion of the spreadsheet file (there are other portions of the file which OpenXLS preserves but does not modify, such as those containing VB code).

The WorkBookHandle class provides convenient access methods for the various functions of spreadsheet objects.

If you are reading from an existing spreadsheet, and would like to output to that file, you must create a file input and output file path.

Example input path: String finpath = "explore.xls"; Example output path: String foutpath = "explored.xls";

Next, read in the WorkBook from your template spreadsheet file.

Example:

WorkBookHandle book = new WorkBookHandle
              ("Desktop:Java:eclipse.1:workspace:OpenXLS:explore.xls");
System.out.println("OpenXLS version " + book.getVersion() + " loaded.");
 // Get a handle to the WorkSheet you want to work with
  WorkSheetHandle sheet1 =  book.getWorkSheet("Sheet1");

Creating a New WorkBook

Instantiating a WorkBookHandle class parses the binary spreadsheet file data (BIFF8 format) and creates Java objects that represent the document’s cells, values, formatting, and settings.

The following requirements are needed before instantiating a WorkBookHandle:

• Java VM version 1.4 or higher • A valid OpenXLS.jar file in your classpath • A valid license key for your installation in the same directory as the jar

Note: if your application is deployed within an EAR, WAR file, or any other case where the classes within the OpenXLS.jar are repackaged into another file, you will need to specify the location of the file containing the OpenXLS class files by setting the system property to “com.valkyrlabs.openxls.jarloc” property. Additionally, you will need to set the license key for OpenXLS using “com.valkyrlabs.openxls.licensekey” system property.

The constructor of the WorkBookHandle class reads from an array of file bytes. The data can be supplied from a database or network stream. You can create a WorkBookHandle on a new, empty WorkBook with the empty constructor. The empty constructor provides a default of three WorkSheets per WorkBook (Sheet1, Sheet2, and Sheet3).

To create a new WorkBook use the WorkBookHandle() constructor.

Example: WorkBookHandle book = new WorkBook();

To read from an existing WorkBook use WorkBookHandle(String) Example (OSX): WorkBookHandle book = new WorkBookHandle("Desktop:OpenXLS:explore.xls"); Example (Unix): WorkBookHandle book = new WorkBookHandle("/home/username/OpenXLS/explore.xls"); Example (Windows): WorkBookHandle book = new WorkBookHandle("C:/OpenXLS/explore.xls");

Tip: There are a variety of WorkBookHandle constructors that allow you to create WorkBookHandles from byte arrays and files, as well as giving you the ability to output debug information on the internal operation of the API as it parses your input file. Please review the API docs included with your distribution for details on all available WorkBookHandle constructors.

Deprecated WorkBookHandle Constructors

In order to lessen the complexity of the API, we have removed three WorkBookHandle constructors, which take a 'Working Directory' string parameter to denote the location of the template file.

Template path strings can be constructed prior to calling the simplified constructors, which now take a fully qualified path to the template file.

The removed constructors are:

public WorkBookHandle(String wd, String fname)
public WorkBookHandle(String wd, String fname, boolean relative)
public WorkBookHandle(byte[] barray, String workingdir)

Modifying Existing WorkBooks One of the many functions of OpenXLS is the ease of reusability of existing files.

To reuse spreadsheet templates files, read in the WorkBook containing VB macros and update the values from the database (or another source), then output the file as a new WorkBook.

When these files are opened, the VB macros will execute the updated values, as well as any complex formatting that has been applied.

This function preserves feature-rich spreadsheets by retaining all the functionality your users are accustomed to.

If you would like to apply the formatting of a “master” WorkBook to separate WorkSheets, copy the WorkSheets of your choice into a new WorkBook.

To retain the formatting in the new WorkBooks, create an empty or sheetless version of the original WorkBook, Adding in only the WorkSheets you desire.

OpenXLS provides two helper methods to assist with this scenario:

To get an empty duplicate of the Master that contains all of the shared formatting information, use the getNoWorkBook method

Example: WorkBookHandle bookRemoved = masterBook.getNoSheetWorkBook();

To pick a sheet from the master WorkBook, use:

Example:bookRemoved.addSheetFromWorkBook(masterBook,"SourceSheet","NewSheetName");

The bookRemoved will contain a copy of the WorkSheet SourceSheet from the original, master WorkBook.

The new sheet is named NewSheetName. Formatting will be retained from the original WorkBook. The new WorkBook can be further manipulated and worked with using OpenXLS.

Copying and Inserting WorkSheets Between WorkBooks

The WorkSheetHandle allows access to a particular WorkSheet within your WorkBookHandle. In order to gain this handle, use the getWorkSheet(String sheetname) method within the WorkBookHandle.

Example: WorkSheetHandle sheet = book.getWorkSheet(“Sheet1”);

You can get an array of handles to all of the WorkSheets in the WorkBook using them.

Example: WorkSheetHandle[] sheets = book.getWorkSheets();

Setting Rows and Column Size Default for WorkBooks WorkBook row and column sizes can be set and applied whenever a specific row or column size is unspecified. The RowHandle is used to work with individual rows in an XLS file. To set the default height for all rows use the WorkBookHandle.setDefaultRowHeight(int x) method.

Example: mybook.setDefaultRowHeight(10000);

The ColHandle is used to work with individual Columns in an XLS file. To set the default formatting for all columns use the WorkBookHandle.setDefaultColWidth(int x) method.

Example: mybook.setDefaultColWidth(10000);

Creating, Accessing, and Modifying Named Ranges To access an existing named range in a sheet, use NameHandle getNamedRange(String name) to retrieve the range.

Example: NameHandle nand = mybook.getNamedRange("nametest4"); Then use the getCells() method to access all the cells in the range

Example:

CellHandle[] ch = nand.getCells(); // access the Cells in the range
        for(int x = 0;x<ch.length;x++){
        ch[x].setVal(123 * x);
        ch[x].setFontColor(FormatHandle.PaleBlue);
       }
        System.out.println(nand.getName());

Exceptions Thrown: CellNotFoundException

To create a range of cells, use the CellRange (String range, WorkBookHandle bk). Next, use the NameHandle(String name, CellRange range) method to generate a named range consisting of the range of cells specified.

Example:

CellRange range = new CellRange( "Sheet1!D8:D13", mybook);
NameHandle newname = new NameHandle("NewNamedRange",range);

Calculating Formulas

To provide improved performance, as well as a finer-grained control over formula calculations, OpenXLS allows you to turn off the automatic calculation of formuals by using the setFormulaCalculationMode() method.

Example: book.setFormulaCalculationMode(WorkBookHandle.CALCULATE_EXPLICIT);

The WorkSheet Functions

The WorkSheetHandle provides a handle to a WorkSheet within an XLS file and includes convenience methods for working with the cell values within a sheet. The WorkSheetHandle allows access to a particular WorkSheet within your WorkBookHandle. In order to gain access to this handle, use the getWorkSheet(String sheetName) method within the WorkBookHandle object.

Example:

WorkBook mybook = new WorkBook();
WorkSheetHandle sheet = mybook.getWorkSheet(Sheet1);

You can get an array of handles to all of the WorkSheets in the WorkBook using the getWorkSheets() method:

Example: WorkSheetHandle[] sheets = book.getWorkSheets();

This will return an array of handles to all of the WorkSheets in the WorkBook allowing you the flexibility to work on each one individually.

Creating, Copying, and Removing WorkSheets

To create a new WorkSheet with the createWorkSheet(String newSheet) method. This will insert a new WorkSheet and place it at the end of the WorkBook.

Example: createWorkSheet("mysheet"); WorkSheetHandle mysheet = new WorkSheet();

To delete any number of WorkSheets,use the remove method. For your convenience, you can remove all WorkSheets in a WorkBook by using the removeAllWorkSheets() method.

Example: sheet.remove("mysheet");

Example: sheet.removeAllWorkSheets("mybook");

To copy an existing WorkSheet to your WorkBook, use the copyWorkSheet(String SourceSheetName NewSheetName) method. This will duplicate a WorkSheet in the WorkBook and add it to the end of the WorkBook with a new name.

Example:

WorkBook thisbook = new WorkBook();
WorkSheetHandle sheet = thisbook.getWorkSheet(sheet1);
createWorkSheet("newsheet");
copyWorkSheet("WorkSheet1", "WorkSheet4");

Creating Sheet Names

If you wish to modify or rename your WorkSheet, use the setSheetName(String newName) method. This method will change the name on the WorkSheet's tab as well as all programmatic and internal references to the name.

Example: setSheetName(“sheet1”, “Records”);

Named ranges can be created with a single line of code, Cells can be added (expanding the named range) or deleted (shrinking the named range.) Named ranges can also be defined as 3-dimensional and span multiple sheets. Getting, Setting and Reordering Tabs

Sheet tab names can be set and retrieved. To get the name of a WorkSheet use the getSheetName() method.

Example: String sheetname = mysheet.getSheetName();

To set the Tab name use the WorkSheetHandle.setSheetName(String NewName) method.

Example: mysheet.setSheetName("CreditTracking");

Sheets can be selected so that they appear as the first visible sheet when the output file is opened. You can also reorder sheets to your specifications. To select a WorkSheet and it to the first visible tab in the WorkBook use the setFirstVisibleTab(int x) method. Example:

	WorkSheetHandle workSheet = new WorkSheet();
    workSheet.setTabIndex("0", "WorkSheet4");

To reorder the display of the WorkSheet tabs use the setTabIndex(int idx) method. This is a zero-based index. Thus, the first tab you will see displayed in your WorkBook will be 0. Example: setTabIndex("1","WorkSheet1");

Selecting Header and Footer Text for Printing WorkSheets To change or set the header text, use the setHeaderText(String headerTextName) method. This will change the header to the desired text.

Example: setHeaderText("CompanyX");

To set or change the footer text use, the setFooterText(String footerTextName) method.

Example: setFooterText("Explore OpenXLS");

Protect and Unprotect WorkSheets

WorkSheet protection can be set or removed to control any modifications in the output file. To lock the values of a WorkSheet, set the WorkSheet to protect by using the setProtected(boolean b) method.

Example: mysheet.setProtected(true);

To unlock the protected WorkSheets, use the setProtected(boolean b) method.

Example:mysheet.setProtected(false);

Working with Rows and Columns

The RowHandle is used to work with individual rows in an XLS file. You can use the RowHandle to:

• Get a handle to the cells in a row • Change the height of a row • Hide or collapse a row • Set the default formatting for a row

To get a handle to an array cells in a row use the get getCells() method. Example:

 	try{
		RowHandle row1 = sheet1.getRow(0);
		CellHandle[] cells = row1.getCells();
	}catch(RowNotFoundException a) {
	// boo!
	}

Rows can be sized for the entire sheet, and will be used unless otherwise specified. To change the height of a row, use the setHeight(int i) method.

Example: RowHandle row1 = sheet1.getRow(0); row1.setHeight(10000);

To hide a row, use the setHidden(boolean b) method.

Example: ColHandle colA = sheet1.getCol(0); colA.setHidden(true);

To collapse a row, use the setCollapsed(boolean b) method.

Example: ColHandle colA = sheet1.getCol(0); colA.setCollapsed(true);

To set the default formatting for a row use the setFormatId(int i ) method. This will set the default for all the cells in the specified row.

Example:

RowHandle row1 = sheet1.getRow(0);
CellHandle[] cells = row1.getCells()
row1.setFormatId(2);

The ColHandle object provides access to columns and its cells within a WorkSheet. You can use the ColHandle to work with individual columns in an XLS file. With a ColHandle you can:

• Get a handle to the cells in a column • Set the width of a column • Hide or collapse a column • Set the default formatting for a column

To get a handle to an array of cells within a column use the getCells() method. Example: ColHandle colA = sheet1.getCol(0); CellHandle[] cells = colA.getCells();

Columns widths can be specified for an entire sheet. To set the width of a column, use the setWidth(int i) method.

Example: ColHandle colA = sheet1.getCol(0); colA.setWidth(10000);

To hide a column, use the setHidden(boolean b) method.

Example: ColHandle colA = sheet1.getCol(0); colA.setHidden(true);

To collapse a column, use the setCollapsed(boolean b) method.

Example: ColHandle colA = sheet1.getCol(0); colA.setCollapsed(true);

To set the default formatting for a column, use the setFormatId(int i) method. This will set the default for all the cells in the specified column.

Example:

ColHandle colA = sheet1.getCol(0);
CellHandle[] cells = colA.getCells();
colA.setFormatId(2);

Adding and Removing Rows

To insert a new row into a WorkSheet use the insertRow(int rownum) method. This will insert a blank row into a WorkSheet and shift all rows below the cell down one.

Example: sheet1.insertRow(12); Note: This method is only necessary to move existing cells by inserting empty rows.

Tip: If you would like to create a new row that retains all the formatting of the prior row, use the insertRow (int rownum,boolean shiftrows) method. This will shift all existing subsequent rows down by one.

Example: sheet1.insertRow(3,true);

To remove a row from a WorkSheet, use the removeRow(int) method. This will remove the row and all associated cells from a WorkSheet. Example: mysheet.removeRow(10); To remove a row and shift all rows below the target up one, use the removeRow(int rownum boolean shiftrows) method.

Example: sheet1.removeRow(3,true);

Adding and Removing Columns

To insert a column into a WorkSheet, use the insertCol (String colNum) method. This will insert a blank column into the WorkSheet and shift all columns to the right of the cell over one. Adding new cells to non-existent columns will automatically create new columns in the file. Example: mysheet.insertCol("H");

Note: This method is only necessary to move existing cells by inserting empty columns

To remove a column and all associated cells within a WorkSheet use the removeCol(String colstr) method. Example: sheet1.removeCol("C"); To remove a column and shift all columns to the right of the target column by one, use the removeCol(String colstr boolean shiftcols) method. Example: sheet1.removeCol("C", false);

Working with Charts With ChartHandle, you can add cells to a chart, change a chart’s title, axis label, and copy charts to another WorkSheet or WorkBook. Begin by retrieving a ChartHandle from an existing chart in a WorkSheet. To retrieve a ChartHandle from an existing chart in a WorkSheet, use the ChartHandle WorkSheetHandle.getChart(String chartname) method.

Example:

ChartHandle performancechart = mysheet.getChart("Performance");
}catch(ChartNotFoundException e){
// oops!
}```

With a ChartHandle, you can add cells to the chart using the boolean ChartHandle.changeSeriesRange(String originalrange, String newrange) method:

Example:

if(performancechart.changeSeriesRange("Sheet1!C23:E23", "Sheet1!C23:G23")); System.out.println("Successfully Added Columns F and G to Series Range");

To change the chart title, use the Charthandle.setTitle(String title) method.

Example: `performancechart.setChartTitle("Athletic Performance Results");`

To change the axis labels, or other text labels in the chart use the ChartHandle.changeTextValue(String originaltext, String newtext) method.

Example:
```java
if(ct.changeTextValue("50 Meter Dash Times", "High Jump Distance"));
   System.out.println("Successfully Changed Categories Label");

To copy a chart into another sheet or WorkBook, use the…

Example:

try{
   book.copyChartToSheet("New Chart Title", "Sheet3");
}catch(Exception e){
   System.out.println(e);
}

Grouping and Hiding Rows and Columns Grouping rows and columns is a great presentation tool that allows for the hiding of large quantities of detailed information into section summaries. When more detail is required, the hidden information can be expanded to display all the desired information.

To group row and columns, use the get/set methods for the collapsed, outlinelevel, and hidden methods in both the RowHandle and ColHandle classes. See page ….in this guide or see the API for more detailed information on these methods.

collapsed - the state for the row/col determining whether the '+' or '-' sign is visible in the… outlinelevel - row/column groups can be nested, the outlinelevel determines which level of grouping the row/column is assigned hidden - whether the row/column is visible. collapsed rows and columns are also not visible

TODO: get some screen shots

CellRanges provide convenient methods for working with logical groups of cells:

This constructor will create missing Cells within the range automatically: CellRange databaseValues = new CellRange("Sheet1!A1:B8", mybook);

This constructor will NOT create missing Cells within the range automatically: CellRange databaseValues = new CellRange("Sheet1!A1:B8", mybook, false);

This constructor will create a 3D range of Cells, spanning WorkSheets: CellRange databaseValues = new CellRange("Sheet1!A1:Sheet3!B8", mybook);

You can add new cells to the range which will expand the rectangle of cells referenced by the CellRange to include the added Cell:

CellRange cellz = new CellRange("Sheet1!A1:A20", mybook); CellHandle b1 = sheet.getCell("B1"); cellz.addCellToRange(b1); CellHandle[] rangecells = cellz.getCells(); // now contains A1:B20 Creating, accessing, and modifying named ranges Named ranges can be created with a single line of code, Cells can be added (expanding the named range) or deleted (shrinking the named range.) Also, named ranges can be defined as 3-dimensional and span multiple sheets. Named ranges can then be created from a CellRange with a single line of code,

NameHandle valuecells = new NameHandle("ValCells",databaseValues);

Just as with CellRanges, Cells can be added (expanding the named range) or deleted (shrinking the named range.) Also, named ranges can be defined as 3-dimensional and span multiple sheets.

The main difference between NamedRanges and CellRanges is that CellRanges are for convenience within the OpenXLS API, whereas NamedRanges are a function of a Spreadsheet and will display and be accessible from the Named Range dropdown box in your Spreadsheet program.

TODO: get some screen shots of namedrange box in Excel

Cell Functions OpenXLS makes it convenient for you to work with cells individually. The cell functions allow you to modify and apply dynamic formatting to the cells in your spreadsheet. Some of the cell attributes that you can edit or apply are: • Move Cells • Remove Cells • Get and set Formatting patterns • Get and Set Font styles • Get and set border styles and colors • Get and set background and foreground patterns and colors • Add Dates • Add hyperlinks to cells • Change the value of existing Cells Moving Cells The CellHandle provides a handle to an XLS Cell and its values. Use the CellHandle to work with individual Cells in an XLS file. Cell objects are created through the parsing process and are the atomic element which spreadsheet table cells can be addressed. Using the CellHandle class, new values can be set on existing cells based on any data source available to your Java program. To get a handle to a particular CellHandle in the WorkSheetHandle, use the getCell(String address) method and pass in a string representing the address of the Cell in the WorkSheet: CellHandle cell = sheet.getCell(“B2”); To obtain the value from the WorkSheet, use the method: String s = cell.getStringVal(); // gets String representation of Cell value or Object s = cell.getVal(); // gets actual Cell value (Double, String, Integer, etc.) To change the value of the cell, simply use the “setVal(String val)” method like: c.setVal(“new value for cell”);To instantiate a CellHandle object, you must first have a valid WorkSheet and WorkBookHandle object. Note: You can add a cell or a group of cells to your WorkSheet by using the WorkSheetHandle object. Please see the Working with Rows and Columns on page…. To move a cell to a different column, use the moveToCol(String newaddress) method. Example: To move a cell to different row, use the moveToRow(int newrow) method Example: Note: If there is an existing cell in the specified address CellHandle will throw a CellPositionConflictException object. Removing Cells To remove a cell from your WorkSheet, use the remove(boolean nullme) method. Example:

Modifying Cell Formats There are three basic approaches to working with Cell formats using OpenXLS:

1. Modify the attributes of a Cell’s format using the CellHandle object.
2. Create a new FormatHandle object, modify its attributes, and apply it to Cells within the WorkBook. This is recommended in most cases.
3. Put Cells with your desired format in your template input file, then apply their format to other Cells dynamically using ‘targetCell.setFormatId(sourceCell.getFormatId())’ methods. The FormatHandle object gives you the flexibility to apply formatting to individual and multiple cells. You can also apply formats to cells by using the CellHandle object. In most cases, we recommend using the FormatHandle. Instantiating a FormatHandle object will create a new unshared format object, which can subsequently be applied to (and shared by) only the Cells you intend.

Modifying Cell Formats using the FormatHandle Object Like many of the objects in the Spreadsheet WorkBook, format objects are stored in the file as shared records, which can be applied to many different Cells in the WorkBook. In every new Spreadsheet file there is a default format within the file that is applied to all new Cells and existing Cells that have not had any explicit format settings applied to them. To share any Cell’s format information using OpenXLS, you should use the: getFormatId() method to get a pointer to this format, and then call: setFormatId(int x) to apply the shared format to another Cell. The FormatHandle object gives you more control over exactly which Cells will acquire new formats – which is an advantage over setting formatting values on CellHandles that may change other Cells sharing the underlying format object. Warning: When you change the cell formatting using the attributes of a CellHandle those changes will be reflected in all Cells sharing this format. You may wish to change the default format for every unformatted Cell in your file, in which case, grabbing any unformatted Cell and modifying its format attributes will alter the underlying format for all unformatted Cells, thus giving your file a new ‘default’ format.

Formatting Color, Style, and Text-Patterns The FormatHandle Class contains numeric (int) constants that supply the information on which colors and Patterns to apply to the Format. To access these constants, use the FormatHandle.CONSTANTNAME, like so: Example:

    FormatHandle.COLOR_LT_BLUE
                                 FormatHandle.COLOR_GOLD
        	    FormatHandle.PATTERN_HOR_STRIPES

Color Formatting Constants Available in FormatHandle

Pattern Formatting Constants in FormatHandle:

In addition to color and style formatting, text-pattern based Cell formats can also be applied to your Cells using the following patterns:

• "General"
Currency
• "0"
• "0.00"
• "#,##0"
• "#,##0.00"
• "($#,##0);($#,##0)"
• "($#,##0);[Red]($#,##0)"
• "($#,##0.00);[Red]($#,##0.00)"
• "($#,##0.00);[Red]($#,##0.00)"
• "(#,##0*);($#,##0)"
• "(#,##0*);[Red]($#,##0)"
• "(#,##0.00*);[Red]($#,##0.00)"
• "(#,##0.00*);[Red]($#,##0.00)"
• "_(\*#,##0_);_(\*($#,##0);_(_\"-\"*);*(@*)"
• "*($_#,##0*);*($*($#,##0);_($\*\"-\"_);_(@_)"
• "_(\*#,##0_);_(\*($#,##0);_(_\"-\"??*);*(@*)"
• "*($_#,##0*);*($*($#,##0);_($\*\"-\"??_);_(@_)"
• "0%"
• "0.00%"
• "0.00E+00"
• "# ?/?"
• "# ??/??"

Adding Dates to Cells

OpenXLS gives you the ability to apply date formatting to cells. You can apply dates directly to a WorkSheet or apply dates to individual cells.

To add dates directly to your WorkSheet use

java.util.Date d = new java.util.Date(System.currentTimeMillis());

The null parameter specifies the default date format ("m/d/yy h:mm".) Use the following steps to add dates to individual cells:

1. Instantiate a DateConverter() object
2. Get the FormatId
3. Apply the to the cells you intend There are a host of date formats accessible through the DataConverter object. Listed in the table below are the OpenXLS valid date format patterns:

m/d/y h:mm d-mmm-yy h:mm:ss d-mmm m/d/yy h:mm mmm-yy mm:ss h:mm AM/PM [h]:mm:ss h:mm:ss AM/PM mm:ss.0

Date/Time Formats • "m/d/y" • "d-mmm-yy" • "d-mmm" • "mmm-yy" • "h:mm AM/PM" • "h:mm:ss AM/PM" • "h:mm" • "h:mm:ss" • "m/d/yy h:mm" • "mm:ss" • "[h]:mm:ss" • "mm:ss.0" • "##0x0E+0" • "@"

Applying Font Attributes To modify a Font for a CellHandle, use the: setFont(String fontname, int fontstyle, int fontsize) method of the CellHandle. This will create a new format definition in the file, and will not be shared by other Cells with the original shared format. Here is an example of efficient use of the CellHandle formatting methods within a new WorkBook:

    void testFormats(String finpath, String sheetname){
    WorkBookHandle tbo = new WorkBookHandle();
    WorkSheetHandle sheet1 = tbo.getWorkSheet(sheetname);

try{
sheet1.add("Eurostile Template Cell","A1");
catch(CellPositionConflictException e){System.out.println(e);}
int SHAREDFORMAT = 0;
CellHandle b = null;
CellHandle a= null;
try{
b = sheet1.getCell("A1");
// Create a new Font format
b.setFont("Eurostile",Font.BOLD,14); // using setFont() creates a NEW Format Record – not shared!
for(int t = 1; t<=10;t++){
try{
sheet1.add(new Float(t\*67.5),"E" + t);
catch(CellPositionConflictException e){System.out.println(e);}
a = sheet1.getCell("E" + t);
// Share the format created above
a.setFormatId(SHAREDFORMAT); // share the new format explicitly
}
a.setFont("Tango",Font.BOLD,16);
// set a format pattern
a.setFormatPattern("[h]:mm:ss");
a.getCol().setWidth(5000);
sheet1.moveCell(a,"A10");
tbo.copyWorkSheet(sheetname, sheetname + " Copy");
}catch(Exception e){System.out.println(e);}
testWrite(tbo, workingdir + "formatout.xls");
}

To apply format for more than a single cell in your file, use the: getFormatId()/setFormatId() method to copy this format to any additional Cells that need. Here is an example of working with a FormatHandle: FormatHandle fmt1 = new FormatHandle(tbo); fmt1.setFont("Arial",Font.PLAIN,10); fmt1.setForegroundColor(FormatHandle.COLOR_LT_BLUE); fmt1.setFontColor(FormatHandle.COLOR_YELLOW); fmt1.setBackgroundPattern(FormatHandle.PATTERN_HOR_STRIPES3); sheet1.add("Hello World " + i, “A1”); cell1 = sheet1.getCell(addr); cell1.setFormat(fmt1); Warning: If you used setFont(name,type,size) on each Cell, the output file size can increase unnecessarily (depending on the number of cells you are working with). For more advanced formatting options, you can choose to instantiate a template file containing examples of formatted Cells and apply these template Cell formats to new Cells in the output file. Using Hyperlinks

One of the most useful features of OpenXLS is the ability to dynamically set hyperlinks on Cells in your output files. This functionality is especially useful in web applications that provide a "drill-down" view of Spreadsheet data. For example, your Servlet may produce a master spreadsheet with a number of referenced detail spreadsheets. You can program your application to create hyperlinks on the detail Cells in your master spreadsheet, which when clicked, open a request to the server for a detailed information spreadsheet containing data for the detail record. The setting of a dynamic URL is a simple method call on the CellHandle: mycell.setURL("http://yoursite.com/"); The following code demonstrates setting the URL on a number of new Cells:

try{
   // CellHandle salary3 = sheet1.getCell(st4);
   String ht="E3:E10";
   for(int t = 3; t<=10;t++){
      try{
         sheet1.add("OpenXLS Home Page","E" + t);
      }catch(CellPositionConflictException e){
         System.err.println(e);
      }
      CellHandle link1 = sheet1.getCell("E"+t);
      sheet1.moveCell(link1,st4 + t);
      link1.setURL("http://www.valkyrlabs.com/estore/product_detail.jsp?product_group_id=1");
   }
}catch(CellNotFoundException e){
   System.err.println(e);
}

Note: Due to the undocumented nature of the underlying HLink Spreadsheet record type, not all Hyperlinks contained in existing WorkBooks are supported. A warning will be issued to System.err when these are encountered during parsing of the WorkBook.

Template Guidelines OpenXLS is often used as a template-based system. Since Spreadsheet is a complete spreadsheet design tool, it often makes sense to define complex formatting, formulas and cell ranges using Spreadsheet. You can then read in this template file using OpenXLS and modify it programmatically at runtime using the API methods. After your code has modified the cell values, you can stream the XLS bytes to a file, web browser, or database. Since the XLS file is a template, you may want to maintain ‘placeholder’ values in the Cells that you wish to change. Please note, placeholder values are optional, but may aid in visual layout of you spreadsheets. Note: in template files containing large areas of value-less cells that contain formatting such as borders or background colors, Spreadsheet will store “Blank” cells which contain no data, but which provide pointers to the formatting for the Cell. This is fine in cases where you are not modifying these areas or there are minimal changes – OpenXLS will convert these Blank cells to value Cells. However, this conversion is an expensive process, especially if there are rows upon rows of blank cells and changes are made across the rows. In this case, it is recommended that you provide placeholder values so that OpenXLS will not need to perform the conversion. You can turn off the conversion of Blanks using the CONVERTMULBLANKS System property: System.getProperties().put(com.valkyrlabs.formats.XLS.WorkBook.CONVERTMULBLANKS, "false"); If you do use placeholder values, they should be the appropriate type for the cell to ensure that OpenXLS will not waste time converting the data types in the cell. For example, let’s say you want to put new text in cell B17 on Sheet1 of your WorkBook. If you decide to use placeholder text, save a text value in the cell B17 on Sheet1 of your template—the text does not matter as it will be replaced. One quirk of Spreadsheet is that if you do save the same String in two different Cells in your Template, Spreadsheet (and OpenXLS) will save only one copy of the underlying String record, which is then referenced by both Cells. As a consequence, if you change the String value of one of the Cells, the value will also change for the other cell. For this reason, each template Cell should have a different value for placeholder text otherwise all of the Cells in your output file will have the same text. Cells do not exist in an Spreadsheet file unless there is a value in them. For this reason, if there is no data in the cell, it is not saved to disk by Spreadsheet (for file optimization reasons) and likewise OpenXLS will not be able to find it. The API will throw a CellNotFoundException if you try to access a cell that using OpenXLS that does not exist on the template WorkSheet. If you want to work with a Cell in a WorkBook that may or may not exist at runtime, simply catch the CellNotFoundException and in the catch block call a WorkSheetHandle.add(Object r, String address) with the new value as your object parameter. You can then safely get a CellHandle to the cell. Template Compatibility Notes:

1. OpenXLS cannot parse or execute Visual Basic code contained in Macros. Unlike other products, it does preserve VB code and macros can be executed upon opening with Excel*.
2. OpenXLS does not allow for modification of certain features such as Cell notes, form objects, or embedded images. These features will be preserved and retained however in the output files.
3. For maximum compatibility, OpenXLS uses the BIFF8 compatible format. Please ensure that your template files are version Office 97 or later before using them with the system. Do not save your files as “Spreadsheet95/97” as this doubles the size of your output and creates a copy of the file in Spreadsheet95, which OpenXLS cannot modify.
4. OpenXLS is not compatible with XML versions of Excel* at this time. Support of Internal Spreadsheet Record Types and Features By design, OpenXLS does not implement the ability to modify or create every one of the XLS record types in the BIFF8 file format. Many of these settings are static in nature and/or can be determined by the template file instead of programmatically. Other features of Spreadsheet that have not yet been implemented simply reflect the development lifecycle of the OpenXLS product.

Performance Tuning

The memory and speed performance of OpenXLS applications can vary dramatically based on a number of factors. The file size of input templates (especially those with many thousands of non-numeric or empty cells) affects the speed of file parsing, writing, and memory usage.

OpenXLS can be fine-tuned based to enable the best mix of speed, compatibility (with Excel or other external consumers), and memory usage. When optimizing the performance of your application, care should be taken to minimize unnecessary input file content including large areas of empty cells with formatting.

The performance of OpenXLS can be broken into two groups: operations that affect only the reading or parsing of spreadsheet input files, and operations that are called after parsing the input file into a WorkBook object. To utilize the performance tuning and features of OpenXLS that relate to the parsing of a spreadsheet file in a WorkBook, you can set system properties to indicate how OpenXLS should instantiate new WorkBooks.

Conversion of Blank Records

By default, Excel converts blank records (Mulblank) to empty cells with formatting. This conversion allows setting of values on Blank cells without deleting/adding values. However, it significantly decreases speed and performance. To increase performance and speed, you can tune OpenXLS to change the internal Excel Mulblank record type to a Blank record type. To disable the conversion of blank Mulblank records on empty cells set the 1 System property com.valkyrlabs.openxls.convertmulblanks

Example:

System.getProperties().put("com.valkyrlabs.openxls.convertmulblanks", "false");

Tip: If your input Spreadsheet has many blank cells and you are experiencing performance issues reading your files, you may wish to disable this feature and test your program to see if the situation improves.

Increasing Runtime Performance of Existing WorkBooks

There are a variety of set methods that can be called upon the WorkBook to increase the performance tuning and features of OpenXLS on an existing WorkBook at runtime. To set Encoding mode of new Strings added to file, use the setStringEncodingMode Example: public void setStringEncodingMode(int mode)

OpenXLS can reduce file size by automating String conversion to Unicode encoding. Use the WorkBook.setStringEncodingMode(int MODE) method and set the StringEncoding mode to AUTOMATIC.

Example: mybook.setStringEncodingMode(WorkBook.AUTOMATIC)

Strings and Performance

Character Encoding

Performance levels vary greatly on how Character Encoding is applied to strings in the WorkBookHandle. OPtimizing speed and performance depends on the needs of the application. Finding a tuning soloution that is correct for your application is key to getting the most out of OpenXLS.

OpenXLS by default uses UNICODE. This will result in larger files than Excel, but will be considerably faster.

OpenXLS can save space in output Spreadsheet files if all characters in the String data are represented with a single byte (Compressed). If a String contains characters that need two bytes to represent (such as Eastern-language characters), then the string must be stored in an uncompressed Unicode format. You can set OpenXLS to automatically detect the mode for each String or you can set to all Strings. The auto mode is the most flexible, but requires processing overhead. OpenXLS has three modes for handling the internal encoding of String data added to a file.

If your files require mixed Unicode and non-Unicode Strings use WorkBookHandle.STRING_ENCODING_AUTO

Attributes: Slowest Performance Small file size

If all Strings are in Unicode use WorkBookHandle.STRING_ENCODING_UNICODE

Attributes: Fast performance Largest file size If all the new Strings are non-Unicode with high byte compression ability use WorkBookHandle.STRING_ENCODING_COMPRESSED Attributes: Faster than AUTO Smallest File size

String Sharing

String data in BIFF8 Spreadsheets are stored in the Shared String Table. Depending on the requirements of your application, adding new String information to WorkBooks can either be an important file size optimization or act as unnecessary performance depreciation.

The Duplicate String Mode determines the behavior of the String table when inserting new Strings. The String table shares a single entry for multiple Cells containing the same string. When multiple Cells have the same value, they share the same underlying string. Changing the value of any one of the Cells will change the value for any Cells sharing that reference.

For this reason, it is important to determine how tnew strings will be added to a Spreadsheet that are duplicates of strings in the table. If the changing the values of these new Cells is required, use the set Duplicate String Mode to ALLOWDUPES.

If the string table encounters a duplicate entry being added, it will insert a duplicate that can then be subsequently changed without affecting the other duplicate Cells.

Use the WorkBook.setDupeString(int DUPE_STRING_MODE) method to control the handling of new Strings to your WorkBook. Example setDupeStringMode

public void setDupeStringMode(int mode)Set Duplicate String Handling Mode

When adding a String to a WorkBook you have the choice to store it as a Shared String. This will reduce file size, but will cause subsequent changes to one cell (affecting the values of other cells sharing the identical String value. Non-identical Strings are NOT shareable.

Sharing identical Strings has a large impact on performance when the number of Strings gets too big. Essentially, if an application is Sharing Strings, OpenXLS has to try to find your new String in the table in order to share it. This process can become time-consuming.

Because of this, you may wish to NOT share strings, especially if you have very few duplicate strings, and/or have very high performance requirements and a lot of Strings to add to the WorkBook. Keep in mind that this will create a larger file size however. You should experiment with the settings to see what makes sense in your application.

WorkBookHandle.ALLOWDUPES

To turn OFF String Sharing - faster, smaller file sizes - duplicate String Cells share Values and modifications:

// higher performance, creates larger filesbookhandle.setDupeStringMode(WorkBookHandle.ALLOWDUPES);

WorkBookHandle.SHAREDUPES

To turn ON String Sharing: // slower performance, creates smaller filesbookhandle.setDupeStringMode(WorkBookHandle.SHAREDUPES); - Slower inserts

Changing Cell value has no effect on other Cells containing the same value

Debug Level

The internal workings of OpenXLS can be examined for debugging purposes by setting the debuglevel value for the WorkBook. The debuglevel outputs messages to System.out pertaining to the operation of the API, with levels of verbosity indicated by higher debuglevel values. The values range from 0 (no debug output, warnings and fatal error messages only) to 100 (very detailed information).

You can either set the debug level through the WorkBookHandle.setDebugLevel(int level) on an existing WorkBook or by passing it into the parsing of the WorkBook using one of the constructors contaning a DebugLevel parameter.

Example

WorkBookHandle.setDebugLevel(int level):

mybook.setDebuglevel(10);

Example:

mybook = new WorkBookHandle("C:/project/abook.xls", 10);

Sample Applications

Included with your installation is a sample template XLS file containing a number of example program files and template Spreadsheets. We recommend browsing and studying any sample code which might pertain to the application you are developing in order to gain an understanding of the relevant functions and syntax.

Additional Resources For general information on Java programming the Java Tutorial is a great place to start, quickly followed by Bruce Eckel’s invaluable “Thinking in Java” online book. OpenOffice.org, the Gnome Project and Lotus provide BIFF8 compatible desktop applications. Troubleshooting

If you are having problems with your spreadsheets, please review the table:

Symptom Possible Solutions

Report output is incorrect; spreadsheets are not created, data missing. Review any System.out and System.err logs and console messages for obvious warnings and errors.

Output spreadsheets display many duplicate and random incorrect values scattered about This is the result of setting the value of a cell containing a shared string. Please be sure that your template file contains unique placeholder values in any mapped cells and rows to ensure that you do not set a value (such as an empty space) which is shared between many other cells.

If you are adding Strings to the file, set the WorkBookHandle.setDupeStringMode( WorkBookHandle.ALLOWDUPES);

Please review the section on Important Issues With Strings on page 27

Report output does not match expectations. When other techniques fail to fix your problems, try the following:

• Test your spreadsheet using a completely blank template file. Check the rows/cols and reconcile with expectations. • Execute your queries using a SQL administration tool to verify that your data results are as expected.

More information can be found online by accessing the Valkyr Labs knowledgebase at: http://www.valkyrlabs.com/knowledgebase/KBList.jsp If you are a current support customer and have any questions regarding OpenXLS or need assistance, please call the Valkyr Labs support line 9-5pm Pacific Standard Time at:

or leave a message on our toll free incident spreadsheet line: Email support can be found at: support@valkyrlabs.com. If you have any comments or suggestions for improvement of this document, we would like to hear from you. If there is anything you would like to see documented that is not noted here, please email us at: support@valkyrlabs.com with your suggestions. Thank you for using OpenXLS.