HSSFWorkbook XLS to XML

[Mark Greene]

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
* [EMAIL PROTECTED]
*/
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();
   }
}