XML and Excel Spreadsheet Conversions (Continued)

A row in the spreadsheet has cells corresponding to each of the elements in the journal tag of the XML document. You can set the column width of each of the columns in the spreadsheet. For example, specify the column width of the first cell of a row like this:

spreadSheet.setColumnWidth((
        short)0,  (short)(256*25));

Create an InputSource object for the XML document that you want to convert to an Excel spreadsheet:

InputSource inputSource = 
        new InputSource(
        new FileInputStream(
        new File(
        "C:/Excel/catalog.xml")));

Create an XPath object to parse the XML document:

XPathFactory  factory=
        XPathFactory.newInstance();
XPath xPath=factory.newXPath();

Specify the XPath expression for a node list you want to obtain:

String expression=
        "/catalog/journal";

Then obtain a node list for the specified XPath expression. The node list consists of nodes corresponding to the journal elements in the XML document example:

com.sun.org.apache.xml.internal.
        dtm.ref.DTMNodeList nodeList = 
        (com.sun.org.apache.xml.
        internal.dtm.ref.DTMNodeList) 
        (xPath.evaluate(
        expression, inputSource, 
        XPathConstants.NODESET));

Iterate over the node list, and add a row to the spreadsheet that corresponds to each of the journal nodes in the node list. Use the HSSFRow object to add a spreadsheet row:

for(int i=0; 
        i<nodeList.getLength(); 
        i++){
HSSFRow row=
        spreadSheet.createRow((
        short)i);

}

Now you can create a cell in a spreadsheet row for each of the elements in the journal element. Use the createCell() method of the HSSFRow object to create a cell:

HSSFCell cell=
        row.createCell((short)0);

Set the value of a row cell with the setCellValue() method. For example, the value of the cell for the section element is set like this:

cell.setCellValue(((Element)(
        nodeList.item(i))).
        getElementsByTagName(
        "section").item(0).
        getFirstChild().
        getNodeValue());

The first cell in a row has index 0; set the cell style of a cell with the setCellStyle() method of the HSSFCell object:

cell.setCellStyle(cellStyle);

Similarly, you can set the values of the columns for the other cells in a row. Create a FileOutputStream to output the Excel workbook to an XLS file:

FileOutputStream output=
        new FileOutputStream(
        new File(
        "C:/Excel/ExampleExcel.xls"));

Output the Excel workbook to an XLS file, and close the FileOutputStream:

wb.write(output);
 output.flush();
 output.close();

An Excel spreadsheet is generated.