XML and Excel Spreadsheet Conversions (Continued)

Going the Other Way
The spreadsheet example that is generated from the XML document example is available for download. The Java application XMLToExcel.java, which is used to convert an XML document to an Excel spreadsheet, is shown in Listing 2.

Now that we've seen how easy it is to generate an Excel document from an XML document, let's covert the other way, that is, convert the Excel document to an XML document. You also use the Apache POI HSSF API to parse an Excel spreadsheet and retrieve the cell values from the spreadsheet. Begin by importing the Apache POI HSSF API:

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

The root element of the XML document generated is catalog, and a journal element is added to correspond to each of the rows of the Excel spreadsheet. Generate an XML document, and specify the root element of the document:

DocumentBuilderFactory factory =
        DocumentBuilderFactory.
        newInstance();
DocumentBuilder builder = 
        factory.newDocumentBuilder();
Document document = 
        builder.newDocument();
Element catalogElement=
        document.createElement(
        "catalog");
document.appendChild(
        catalogElement);

Next, create an InputStream object for the Excel spreadsheet that will be converted to an XML document:

InputStream input=
        new FileInputStream(excelFile);

Obtain the workbook for the InputStream object:

HSSFWorkbook workbook=
        new HSSFWorkbook(input);

Obtain the spreadsheet for the Excel workbook:

HSSFSheet spreadsheet=
        workbook.getSheetAt(0);

Iterate over the rows in the spreadsheet, and add a journal element to the XML document for each of the rows:

for(int i=0; i<=
        spreadsheet.getLastRowNum(); 
        i++)

{

HSSFRow row=
        spreadsheet.getRow(i);

Element journalElement=
        document.createElement(
        "journal");
catalogElement.appendChild(
        journalElement);

}

A cell in a spreadsheet row is retrieved with the getCell() method, and a cell value is retrieved with the getStringCellValue() method. For example, the section cell value is retrieved from the spreadsheet and set in the XML document this way:

Element sectionElement=
        document.createElement(
        "section");
journalElement.appendChild(
        sectionElement);
sectionElement.appendChild(
        document.createTextNode(
        row.getCell((short)0).
        getStringCellValue()));

Similarly, the other cell values are retrieved from the spreadsheet and specified in the XML document. The Excel spreadsheet from which an XML document—catalog.xml—is generated is available for download. ExcelToXML.java, the Java application used to convert an Excel spreadsheet to an XML document, is listed in Listing 3.

About the Author
Deepak Vohra is a Sun-certified Java programmer and Sun-certified Web component developer, who has published several articles in publications that cover Java and XML content. Contact Deepak at .