email article printer friendly get the code

XML and Excel Spreadsheet Conversions
Apply APIs from the Jakarta Project to make XML-to-spreadsheet and spreadsheet-to-XML conversions
by Deepak Vohra

January 4, 2006

XML is the standard medium of data exchange, and often an XML document is presented in a Microsoft Excel spreadsheet format. The Jakarta POI project provides an API to create an Excel spreadsheet. The POI HSSF API can also be used to parse an Excel spreadsheet and convert it to another format such as XML. The HSSF API has the provision to set the layout, border settings, and fonts of an Excel spreadsheet document. Here, we'll generate an Excel spreadsheet example by parsing an XML document and adding data from the XML document to a spreadsheet. Subsequently we'll convert the Excel spreadsheet to an XML document.

The Jakarta POI HSSF API has classes to create an Excel workbook file and add spreadsheets to the workbook. With the POI API a workbook is represented by the HSSFWorkbook class. Spreadsheet fonts, sheet order, and cell styles are set in the HSSFWorkbook class.

A spreadsheet is represented with the HSSFSheet class. Sheet layout, including column widths, margins, header, footer, and print setup, is set with the HSSFSheet class. A spreadsheet row is represented with the HSSFRow class. The row height is set with the HSSFRow class. The HSSFCell class represents a cell in a spreadsheet row. The cell style is set with the HSSFCell class. The indexing of spreadsheets in a workbook, rows in a spreadsheet, and cells in a row is 0 based. In the procedure presented here, we'll convert an XML document example to an Excel spreadsheet, and then convert the spreadsheet to an XML document.

XML to Spreadsheet Data
You can create and parse an Excel spreadsheet by using the Apache POI HSSF API. Download the Apache POI JAR file, and extract it to an installation directory. Add poi-2.5.1-final-20040804.jar to the Classpath. The XML document example is parsed with the JDK 5.0 XPath API. You can download and install JDK 5.0 from the Java site, and you can use the Excel Viewer to open the Excel spreadsheet that is generated from the XML document example. Download and install the Excel Viewer; the Excel document can also be displayed in Excel itself.

Let's convert the XML document to an Excel spreadsheet by using the Apache HSSF API. Download the document example, catalog.xml (see Listing 1). The XML document is parsed with the JDK 5.0 XPath API, and the retrieved values are added to an Excel spreadsheet. Begin by importing the Apache POI HSSF package:

import  org.apache.poi.hssf.
        usermodel.*;

Use the HSSFWorkbook constructor to create an Excel workbook file:

HSSFWorkbook wb=
        new HSSFWorkbook();

Create a spreadsheet from the workbook:

HSSFSheet spreadSheet=
        wb.createSheet("spreadSheet");

Create a cell style object for the spreadsheet:

HSSFCellStyle cellStyle=
        wb.createCellStyle();

Next, specify the border settings for the HSSFCellStyle object:

cellStyle.setBorderRight(
        HSSFCellStyle.BORDER_MEDIUM);
cellStyle.setBorderTop(
        HSSFCellStyle.BORDER_MEDIUM);