How to convert Excel to XML using java?

Question

i want to convert my input Excel file into the Output XML file.

If anybody has any solution in java for how to take input Excel file and how to write to XML as output,please give any code or any URL or any other solution.

Thanks,

Mishal Shah

Answer 1

Download jxl and use this code

    import java.io.File;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import javax.swing.text.BadLocationException;
import jxl.Cell;
import jxl.CellType;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Font;
import jxl.read.biff.BiffException;

public class XlsToXml {
public String toXml(File excelFile) throws IOException, BiffException {
    try {
        String xmlLine = "";
        String rowText = "";
        String colText = "";
        String isBold = "";
        Font font = null;
        String cellCol = "";
        String cellAddress = "";
        Cell cell = null;
        Workbook workbook = Workbook.getWorkbook(excelFile);
        xmlLine += "<workbook>" + "\n";
        for (int sheet = 0; sheet < workbook.getNumberOfSheets(); sheet++) {
            Sheet s = workbook.getSheet(sheet);
            xmlLine += "  <sheets>" + "\n";
            Cell[] row = null;
            for (int i = 0; i < s.getRows(); i++) {
                row = s.getRow(i);
                for (int j = 0; j < row.length; j++) {
                    if (row[j].getType() != CellType.EMPTY) {
                        cell = row[j];
                        cellCol=columnName(cell.getColumn());
                        cellCol=" colLetter=\""+cellCol+"\"";
                        cellAddress=" address=\""+cellAddress(cell.getRow()+1,cell.getColumn())+"\"";
                        isBold = cell.getCellFormat().getFont().getBoldWeight() == 700 ? "true" : "false";
                        isBold = (isBold == "false" ? "" : " isBold=\"true\"");
                        colText += "      <col number=\"" + (j + 1) + "\"" + isBold +cellAddress+ ">";
                        colText += "<![CDATA[" + cell.getContents() + "]]>";
                        colText += "</col>" + "\n";
                        rowText += cell.getContents();
                    }
                }
                if (rowText != "") {
                    xmlLine += "    <row number=\"" + (i + 1) + "\">" + "\n";
                    xmlLine += colText;
                    xmlLine += "    </row>" + "\n";
                }
                colText = "";
                rowText = "";
            }
            xmlLine += "  </sheet>" + "\n";;
        }
        xmlLine += "</workbook>";
        return xmlLine;
    } catch (UnsupportedEncodingException e) {
        System.err.println(e.toString());
    }
    return null;
}
private String cellAddress(Integer rowNumber, Integer colNumber){
    //return "$"+columnName(colNumber)+"$"+rowNumber;
    return columnName(colNumber)+rowNumber;
}
private String columnName(Integer colNumber) {
    Base columns = new Base(colNumber,26);
    columns.transform();
    return columns.getResult();
}

class Base {
    String[] colNames = "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".split(",");
    String equalTo;
    int position;
    int number;
    int base;
    int[] digits;
    int[] auxiliar;

    public Base(int n, int b) {
        position = 0;
        equalTo = "";
        base = b;
        number = n;
        digits = new int[1];
    }

    public void transform() {
        if (number < base) {
            digits[position] = number;
            size();
        } else {
            digits[position] = number % base;
            size();
            position++;
            number = number / base;
            transform();
        }
    }

    public String getResult() {
        for (int j = digits.length - 2; j >= 0; j--) {
            equalTo += colNames[j>0?digits[j]-1:digits[j]];
        }
        return equalTo;
    }

    private void size() {
        auxiliar = digits;
        digits = new int[auxiliar.length + 1];
        System.arraycopy(auxiliar, 0, digits, 0, auxiliar.length);
    }
}

}

Answer 2

I have done conversion of Excel(xlsx) to xml in Java recently. I assumed each row in excel as a single object here. Here are the steps I followed:-

1. Read Excel file using Apache POI
2. Created a xsd file and generated corresponding classes
3. Read each row created, created corresponding objects and initilaized values using the generated getter/setter methods in the classes
4. Added the objects to an arraylist which holds only objects the same type
5. Using Jaxb Marshelled the arraylist object to an output file

Ready to provide code if required Here's where you can start https://sites.google.com/site/arjunwebworld/Home/programming/jaxb-example

Answer 3

File excelFile = new File(excelFilename);

// Create model for excel file
if (excelFile.exists()) {
    try {
        Workbook workbook = Workbook.getWorkbook(excelFile);
        Sheet sheet = workbook.getSheets()[0];

        TableModel model = new DefaultTableModel(sheet.getRows(), sheet.getColumns());
        for (int row = 0; row < sheet.getRows(); row++) {
            for (int column = 0; column < sheet.getColumns(); column++) {
                String content = sheet.getCell(column, row).getContents();
                model.setValueAt(content, row, column);
            }
        }

        previewTable.setModel(model);
    } catch (Exception e) {
        JOptionPane.showMessageDialog(null, "Error: " + e);
    }

} else {
    JOptionPane.showMessageDialog(null, "File does not exist");
}

Answer 4

JExcel was easy for me to use. Put jxl.jar on the classpath and code something like:

    File excelFile = new File(excelFilename);

    // Create model for excel file
    if (excelFile.exists()) {
        try {
            Workbook workbook = Workbook.getWorkbook(excelFile);
            Sheet sheet = workbook.getSheets()[0];

            TableModel model = new DefaultTableModel(sheet.getRows(), sheet.getColumns());
            for (int row = 0; row < sheet.getRows(); row++) {
                for (int column = 0; column < sheet.getColumns(); column++) {
                    String content = sheet.getCell(column, row).getContents();
                    model.setValueAt(content, row, column);
                }
            }

            previewTable.setModel(model);
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, "Error: " + e);
        }

    } else {
        JOptionPane.showMessageDialog(null, "File does not exist");
    }

See http://jexcelapi.sourceforge.net/resources/faq/ to get started and link to download area.

Answer 5

Look into the jexcel or Apache POI libraries for reading in the Excel file.

Creating an XML file is simple, either just write the XML out to a file directly, or append to an XML Document and then write that out using the standard Java libs or Xerces or similar.