what is it:

this example shows you how to transform xls files into xml files. It's so easy, we even don't need comments so far...

what do you need:

jakarta poi
jdom

how it works:

we read the xls file
open a workbook
read the metainformation
decide if we want to use the headers in the first row or just use a,b,c,d...
save it in jdom elements, becuase we are lazy and like the valdiation of jdom
we flush it into an outputstream


code:

/*
 * Created on Sep 1, 2005
 */

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;

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.jdom.Document;
import org.jdom.Element;
import org.jdom.output.XMLOutputter;
/**
 * converts a xls stream in a xml stream
 * @author wohlgemuth
 *
 */
public class XLSToXMLConverter {
    XMLOutputter outputter = new XMLOutputter();

    private boolean header = false;

    final static String HEADER[] = new String[] { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V",
            "W", "X", "Y", "Z" };

    /**
     * converts the inputfile to an xml file
     *
     * @throws Exception
     *
     * @see edu.ucdavis.genomics.metabolomics.binbase.meta.converter.AbstractConverter#convert(java.io.InputStream,
     *      java.io.OutputStream)
     */
    public void convert(InputStream in, OutputStream out) throws Exception {
        HSSFWorkbook workbook = new HSSFWorkbook(in);

        Document document = new Document();
        Element root = new Element("workbook");
        document.setRootElement(root);

        for (int a = 0; a < workbook.getNumberOfSheets() - 1; a++) {
            HSSFSheet sheet = workbook.getSheetAt(a);

            int min = sheet.getFirstRowNum();
            int max = sheet.getLastRowNum();

            String[] header = null;
            Element sheetElement = new Element("sheet");
            sheetElement.setAttribute("name", workbook.getSheetName(a));
            sheetElement.setAttribute("index", String.valueOf(a));

            for (int i = min; i < max; i++) {
                HSSFRow row = sheet.getRow(i);

                if (i == min) {

                    if (this.header == true) {
                        // header row
                        short firstCell = row.getFirstCellNum();
                        short lastCell = row.getLastCellNum();
                        header = new String[lastCell];

                        for (short x = firstCell; x < lastCell; x++) {
                            HSSFCell cell = row.getCell(x);


                            if(cell == null) {
                                header[x] = "NoHeader";
                            }
                            else {
                            switch (cell.getCellType()) {
                            case HSSFCell.CELL_TYPE_BLANK:
                                header[x] = "NoHeader";
                                break;
                            case HSSFCell.CELL_TYPE_STRING:
                                header[x] = cell.getStringCellValue();
                                break;

                            default:
                                throw new IOException("cell type is not as header supported");
                            }
                            }
                        }
                    } else {
                        // header row
                        short firstCell = row.getFirstCellNum();
                        short lastCell = row.getLastCellNum();
                        header = new String[lastCell];

                        for (short x = firstCell; x < lastCell; x++) {
                            header[x] = HEADER[x];
                        }
                    }
                }
               
                if(this.header == false){
                    createElement(header, sheetElement, row);  
                }
                else if(i > min) {
                    createElement(header, sheetElement, row);
                }
               
            }
            root.addContent(sheetElement);
        }
        outputter.output(document, out);

    }

    /**
     * @param header
     * @param sheetElement
     * @param row
     */
    private void createElement(String[] header, Element sheetElement, HSSFRow row) {
        short firstCell = row.getFirstCellNum();
        short lastCell = row.getLastCellNum();

        Element element = new Element("row");
        element.setAttribute("id", String.valueOf(row.getRowNum()));

        for (short x = firstCell; x < lastCell; x++) {
            HSSFCell cell = row.getCell(x);

            if (header[x].equals("NoHeader") == false) {
                Element content = new Element(header[x].replaceAll(" ", "_"));

                if(cell == null) {
                    content.setText("");
                }
                else {
                switch (cell.getCellType()) {
                case HSSFCell.CELL_TYPE_BLANK:
                    content.setText("");
                    break;
                case HSSFCell.CELL_TYPE_BOOLEAN:
                    content.setText(String.valueOf(cell.getBooleanCellValue()).trim());
                    break;
                case HSSFCell.CELL_TYPE_ERROR:
                    content.setText(String.valueOf(cell.getErrorCellValue()).trim());
                    break;
                case HSSFCell.CELL_TYPE_FORMULA:
                    content.setText(String.valueOf(cell.getCellFormula()).trim());
                    break;
                case HSSFCell.CELL_TYPE_NUMERIC:
                    content.setText(String.valueOf(cell.getNumericCellValue()).trim());
                    break;
                case HSSFCell.CELL_TYPE_STRING:
                    content.setText(String.valueOf(cell.getStringCellValue()).trim());
                    break;

                default:

                }
                }
                element.addContent(content);
            }

        }
        sheetElement.addContent(element);
    }

    /**
     *
     */
    public XLSToXMLConverter() {
        super();
        outputter.setNewlines(true);
        outputter.setIndent(true);
    }
   
    public static void main(String[] args) throws FileNotFoundException, Exception {
        new XLSToXMLConverter().convert(new FileInputStream(args[0]),new FileOutputStream(args[1]));
    }

    public boolean isHeader() {
        return header;
    }

    public void setHeader(boolean header) {
        this.header = header;
    }
}