如何使用java将Excel转换为XML？

How to convert Excel to XML using java?

提问by Developer

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

我想将我的输入 Excel 文件转换为输出 XML 文件。

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.

如果有人在java中有任何关于如何获取输入Excel文件以及如何写入XML作为输出的解决方案，请提供任何代码或任何URL或任何其他解决方案。

Thanks,

谢谢，

Mishal Shah

米沙尔·沙阿

采纳答案by JeeBee

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

查看 jexcel 或 Apache POI 库以读取 Excel 文件。

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.

创建 XML 文件很简单，只需将 XML 直接写出到文件，或者附加到 XML 文档，然后使用标准 Java 库或 Xerces 或类似工具将其写出。

回答by Thorbj?rn Ravn Andersen

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

JExcel 对我来说很容易使用。将 jxl.jar 放在类路径上并编写如下代码：

    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.

请参阅http://jexcelapi.sourceforge.net/resources/faq/以开始使用并链接到下载区域。

回答by pritish

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");
}

回答by viswanath patimalla

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:-

我最近在 Java 中完成了 Excel(xlsx) 到 xml 的转换。我在这里假设 excel 中的每一行都是一个对象。以下是我遵循的步骤：-

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

1. 使用 Apache POI 读取 Excel 文件
2. 创建了一个xsd文件并生成了相应的类
3. 使用类中生成的 getter/setter 方法读取创建的每一行，创建相应的对象和初始化值
4. 将对象添加到仅包含相同类型的对象的数组列表中
5. 使用 Jaxb 将 arraylist 对象封装到输出文件中

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

如果需要，准备提供代码这里是您可以开始的地方https://sites.google.com/site/arjunwebworld/Home/programming/jaxb-example

回答by jechaviz

Download jxl and use this code

下载 jxl 并使用此代码

    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);
    }
}

}

}