HSSFWorkbook XLS to XML

[Mark Greene <gr...@gmail.com>]

Hi,

I started searching for API support to convert a workbook into XML. I
couldn't find any within a reasonable time frame so I just made my own. It
uses DOM4J for XML processing. Hopefully this can be of help to some people
or at least a starting point:


import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.dom4j.Document;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;
import org.dom4j.Namespace;
import org.dom4j.QName;
import org.dom4j.io.OutputFormat;
import org.dom4j.io.XMLWriter;

/*
 * Created on Jun 9, 2006
 */

/**
 * @author Mark J. Greene
 * greenemj@gmail.com
 */
public class Test {

    Namespace spreadsheetNS = new Namespace("",
"urn:schemas-microsoft-com:office:spreadsheet");

    Namespace oNS = new Namespace("o",
"urn:schemas-microsoft-com:office:office");

    Namespace xNS = new Namespace("x",
"urn:schemas-microsoft-com:office:excel");

    Namespace ssNS = new Namespace("ss",
"urn:schemas-microsoft-com:office:spreadsheet");

    Namespace htmlNS = new Namespace("html", "
http://www.w3.org/TR/REC-html40");

    public Test() throws IOException {
        int rowCount = 3;
        int colCount = 2;
        Document doc = this.generateExcelSkeletonDoc();
        Element root = doc.getRootElement();
        HSSFWorkbook wb = new HSSFWorkbook();

        this.createTestWorkbook(wb);

        this.writeXLSToXMLDoc(rowCount, colCount, root, wb);

        //      Pretty print the document to System.out
        OutputFormat format = OutputFormat.createPrettyPrint();
        XMLWriter writer = new XMLWriter(System.out, format);
        writer.write(doc);
    }


    private void writeXLSToXMLDoc(int rowCount, int colCount, Element root,
HSSFWorkbook wb) {
        int numOfSheets = wb.getNumberOfSheets();
        for (int i = 0; i < numOfSheets; i++) {
            HSSFSheet sheet = wb.getSheetAt(i);
            Element worksheet = root.addElement("Worksheet");
            worksheet.addAttribute(new QName("Name", ssNS), wb.getSheetName
(i));
            Element tableElm = worksheet.addElement("Table");

            for (int x = 0; x < rowCount; x++) {
                HSSFRow dataRow = sheet.getRow(x);
                Element rowElm = tableElm.addElement("Row");

                // Iterator would return cell values in reverse order...use
for loop instead
                //                Iterator cellItr = dataRow.cellIterator();
                //                while (cellItr.hasNext()) {
                //                    HSSFCell cell = (HSSFCell)
cellItr.next();
                //                    Element data = rowElm.addElement
("Cell").addElement("Data");
                //
                //                    data.addAttribute(new QName("Type",
ssNS), "String");
                //                    data.setText(cell.getStringCellValue
());
                //                }
                for (int y = 0; y < colCount; y++) {
                    HSSFCell cell = dataRow.getCell((short) y);
                    Element data = rowElm.addElement
("Cell").addElement("Data");

                    data.addAttribute(new QName("Type", ssNS), "String");
                    data.setText(cell.getStringCellValue());
                }
            }

        }
    }

    private void createTestWorkbook(HSSFWorkbook wb) {
        HSSFSheet tmpSheet = wb.createSheet();
        HSSFRow row1 = tmpSheet.createRow(0);
        HSSFRow row2 = tmpSheet.createRow(1);
        HSSFRow row3 = tmpSheet.createRow(2);

        row1.createCell((short) 0).setCellValue("Cell Value 1");
        row1.createCell((short) 1).setCellValue("Cell Value 2");

        row2.createCell((short) 0).setCellValue("Cell Value 3");
        row2.createCell((short) 1).setCellValue("Cell Value 4");

        row3.createCell((short) 0).setCellValue("Cell Value 5");
        row3.createCell((short) 1).setCellValue("Cell Value 6");
    }

    private Document generateExcelSkeletonDoc() {
        Document excelDoc = DocumentHelper.createDocument();

        Element root = excelDoc.addElement(
                "Workbook",
                "urn:schemas-microsoft-com:office:spreadsheet");
        root.add(oNS);
        root.add(xNS);
        root.add(ssNS);
        root.add(htmlNS);

        //setup <ExcelWorkbook> element
        Element excelWorkBook = root.addElement(
                "ExcelWorkbook",
                "urn:schemas-microsoft-com:office:excel");

        excelWorkBook.addElement("ProtectStructure").setText("False");
        excelWorkBook.addElement("ProtectWindows").setText("False");

        //setup <Styles> and <stlye>'s element
        Element styles = root.addElement("Styles");

        Element style = styles.addElement("Style");
        style.addAttribute(new QName("ID", ssNS), "Default");
        style.addAttribute(new QName("Name", ssNS), "Normal");
        style.addElement("Alignment").addAttribute(new QName("Vertical",
ssNS), "Bottom");
        style.addElement("Borders");
        style.addElement("Font");
        style.addElement("Interior");
        style.addElement("NumberFormat");
        style.addElement("Protection");

        style = styles.addElement("Style");
        style.addAttribute(new QName("ID", ssNS), "s26");
        style.addElement("NumberFormat").addAttribute(new QName("Format",
ssNS), "@");

        Element workbookOptions = root.addElement(
                "WorksheetOptions",
                "urn:schemas-microsoft-com:office:excel");
        workbookOptions.addElement("ProtectObjects").setText("False");
        workbookOptions.addElement("ProtectScenarios").setText("False");

        return excelDoc;
    }

    public static void main(String[] args) throws Exception {
        new Test();
    }
}