使用java将excel数据解析为xml

Parse excel data to xml using java

提问by n1ckgun

I have the code below which reads excel files and displays it in java. I'd like to implement the code after reading the data from the excel file to java, it will to convert in XML format and save it on XML file.

我有下面的代码，它读取excel文件并将其显示在java中。我想在将excel文件中的数据读取到java后实现代码，它将转换为XML格式并将其保存在XML文件中。

Any code sample or reference will be thankful;

任何代码示例或参考将不胜感激；

public class POIExcelReader {

    public POIExcelReader (){
    }

    public void displayFromExcel (String xlsPath)
    {
        InputStream inputStream = null; 
        try
        {
            inputStream = new FileInputStream (xlsPath);
        }
        catch (FileNotFoundException e)
        {
            System.out.println ("File not found in the specified path.");
            e.printStackTrace ();
        }

        POIFSFileSystem fileSystem = null;

        try {
            fileSystem = new POIFSFileSystem (inputStream);
            HSSFWorkbook      workBook = new HSSFWorkbook (fileSystem);
            HSSFSheet         sheet    = workBook.getSheetAt (0);
            Iterator<?> rows     = sheet.rowIterator ();

            while (rows.hasNext ())
            {
                HSSFRow row = (HSSFRow) rows.next();

                // display row number
                System.out.println ("Row No.: " + row.getRowNum ());

                // get a row, iterate through cells.
                Iterator<?> cells = row.cellIterator ();

                while (cells.hasNext ())
                {
                    HSSFCell cell = (HSSFCell) cells.next ();
                    //System.out.println ("Cell : " + cell.getCellNum ());
                    switch (cell.getCellType ())
                    {
                    case HSSFCell.CELL_TYPE_NUMERIC :
                    {
                        // NUMERIC CELL TYPE
                        System.out.println ("Numeric: " + cell.getNumericCellValue ());
                        break;
                    }
                    case HSSFCell.CELL_TYPE_STRING :

                    {
                        // STRING CELL TYPE
                        HSSFRichTextString richTextString = cell.getRichStringCellValue ();

                        System.out.println ("String: " + richTextString.getString ());
                        break;
                    }
                    default:
                    {
                        // types other than String and Numeric.
                        System.out.println ("Type not supported.");
                        break;
                    }
                }
            }
        }
    }
        catch(IOException e)
        {
            e.printStackTrace ();
        }
    }


    public static void main (String[] args)
    {
        POIExcelReader poiExample = new POIExcelReader ();
        String xlsPath ="c://Users//Secured//Desktop//artikli.xls";
        poiExample.displayFromExcel (xlsPath);
    }
}

采纳答案by openmike

You can use the classes in the package: javax.xml.parsers. The package provides classes allowing the processing of XML documents. e.g. DocumentBuilder, DocumentBuilderFactory, to mention a few.

您可以使用包中的类：javax.xml.parsers。该包提供了允许处理 XML 文档的类。例如 DocumentBuilder、DocumentBuilderFactory 等等。

Using the java code (extended your original code) below and the file located in this location: http://base.google.com/base/products.xls

使用下面的 java 代码（扩展您的原始代码）和位于此位置的文件：http: //base.google.com/base/products.xls

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Iterator;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerConfigurationException;
import javax.xml.transform.TransformerException;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.w3c.dom.Document;
import org.w3c.dom.Element;

public class POIExcelReader {

public POIExcelReader (){
}

public void displayFromExcel (String xlsPath)
{
    InputStream inputStream = null; 
    try
    {
        inputStream = new FileInputStream (xlsPath);
    }
    catch (FileNotFoundException e)
    {
        System.out.println ("File not found in the specified path.");
        e.printStackTrace ();
    }

    POIFSFileSystem fileSystem = null;

    try {
        //Initializing the XML document
        DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
        DocumentBuilder builder = factory.newDocumentBuilder();
        Document document = builder.newDocument();
        Element rootElement = document.createElement("products");
        document.appendChild(rootElement);


        fileSystem = new POIFSFileSystem (inputStream);
        HSSFWorkbook      workBook = new HSSFWorkbook (fileSystem);
        HSSFSheet         sheet    = workBook.getSheetAt (0); 
        Iterator<?> rows     = sheet.rowIterator ();

        ArrayList<ArrayList<String>> data = new ArrayList<ArrayList<String>>();
        while (rows.hasNext ()) 
        {
            HSSFRow row = (HSSFRow) rows.next(); 

            int rowNumber = row.getRowNum ();
            // display row number
            System.out.println ("Row No.: " + rowNumber);

            // get a row, iterate through cells.
            Iterator<?> cells = row.cellIterator (); 

            ArrayList<String> rowData = new ArrayList<String>();
            while (cells.hasNext ())
            {
                HSSFCell cell = (HSSFCell) cells.next ();
                //System.out.println ("Cell : " + cell.getCellNum ());
                switch (cell.getCellType ())
                {
                case HSSFCell.CELL_TYPE_NUMERIC :
                {
                    // NUMERIC CELL TYPE
                    System.out.println ("Numeric: " + cell.getNumericCellValue ());
                    rowData.add(cell.getNumericCellValue () + "");
                    break;
                }
                case HSSFCell.CELL_TYPE_STRING :

                {
                    // STRING CELL TYPE
                    HSSFRichTextString richTextString = cell.getRichStringCellValue ();

                    System.out.println ("String: " + richTextString.getString ());
                    rowData.add(richTextString.getString ());
                    break;
                }
                default:
                {
                    // types other than String and Numeric.
                    System.out.println ("Type not supported.");
                    break;
                }
                } // end switch

            } // end while
            data.add(rowData);


        } //end while

        int numOfProduct = data.size();

        for (int i = 1; i < numOfProduct; i++){
            Element productElement = document.createElement("product");
            rootElement.appendChild(productElement);

            int index = 0;
            for(String s: data.get(i)) {
                String headerString = data.get(0).get(index);
                if( data.get(0).get(index).equals("image link") ){
                    headerString = "image_link";
                }

                if( data.get(0).get(index).equals("product type") ){
                    headerString = "product_type";
                }

                Element headerElement = document.createElement(headerString);
                productElement.appendChild(headerElement);
                headerElement.appendChild(document.createTextNode(s));
                index++;
            }
        }

        TransformerFactory tFactory = TransformerFactory.newInstance();

        Transformer transformer = tFactory.newTransformer();
        //Add indentation to output
        transformer.setOutputProperty
        (OutputKeys.INDENT, "yes");
        transformer.setOutputProperty(
                "{http://xml.apache.org/xslt}indent-amount", "2");

        DOMSource source = new DOMSource(document);
        StreamResult result = new StreamResult(new File("products.xml"));
        //StreamResult result = new StreamResult(System.out);
        transformer.transform(source, result);

    }
    catch(IOException e)
    {
        System.out.println("IOException " + e.getMessage());
    } catch (ParserConfigurationException e) {
        System.out.println("ParserConfigurationException " + e.getMessage());
    } catch (TransformerConfigurationException e) {
        System.out.println("TransformerConfigurationException "+ e.getMessage());
    } catch (TransformerException e) {
        System.out.println("TransformerException " + e.getMessage());
    }
}


public static void main (String[] args)
{
    POIExcelReader poiExample = new POIExcelReader ();
    String xlsPath ="products.xls";
    poiExample.displayFromExcel (xlsPath);
}
}

回答by RKP

import java.io.*;
import java.util.*;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.FactoryConfigurationError;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Result;
import javax.xml.transform.Source;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.w3c.dom.Document;
import org.w3c.dom.Element;


public class MyExp {
protected DocumentBuilderFactory domFactory = null;
protected DocumentBuilder domBuilder = null;

public MyExp(){
    try {
        domFactory = DocumentBuilderFactory.newInstance();
        domBuilder = domFactory.newDocumentBuilder();
    } catch (FactoryConfigurationError exp) {
        System.err.println(exp.toString());
    } catch (ParserConfigurationException exp) {
        System.err.println(exp.toString());
    } catch (Exception exp) {
        System.err.println(exp.toString());
    }
}
public void convertFile(String xlsFileName, String xmlFileName){

    try {
        Document newDoc = domBuilder.newDocument();
        Element rootElement = newDoc.createElement("XMLCreators");
        newDoc.appendChild(rootElement);
        InputStream InputStream=new FileInputStream(new File(xlsFileName));
        HSSFWorkbook      workBook = new HSSFWorkbook (InputStream);
        HSSFSheet         sheet    = workBook.getSheetAt (0); 
        Iterator<?> rows     = sheet.rowIterator ();
        List<String> headers = new ArrayList<String>(5);
        while (rows.hasNext ()) 
        {
            HSSFRow row = (HSSFRow) rows.next(); 

            int rowNumber = row.getRowNum ();
            Iterator<?> cells = row.cellIterator (); 
            ArrayList<String> rowData = new ArrayList<String>();
            while (cells.hasNext ())
            {
                HSSFCell cell = (HSSFCell) cells.next ();
                switch (cell.getCellType ())
                {
                case HSSFCell.CELL_TYPE_NUMERIC :
                {
                    // NUMERIC CELL TYPE
                    rowData.add(cell.getNumericCellValue () + "");
                    break;
                }
                case HSSFCell.CELL_TYPE_STRING :

                {
                    // STRING CELL TYPE
          HSSFRichTextString richTextString = cell.getRichStringCellValue();
                    rowData.add(richTextString.getString ());
                    break;
                }
                default:
                {
                    break;
                }
              }



            } // end while
            if(rowNumber==1){
                headers.addAll(rowData);
            }
            else
            {
                    Element rowElement = newDoc.createElement("row");
                    rootElement.appendChild(rowElement);
                    for (int col = 0; col < headers.size(); col++) {

                        String header = headers.get(col);
                        String value = null;

                        if (col < rowData.size()) {

                            value = rowData.get(col);

                        } else {
                            value = "";
                        }

                        Element curElement = newDoc.createElement(header);
                       curElement.appendChild(newDoc.createTextNode(value));
                        rowElement.appendChild(curElement);

                    }

            }

        } //end while

        ByteArrayOutputStream baos = null;
        OutputStreamWriter osw = null;

        try {

            baos = new ByteArrayOutputStream();
            osw = new OutputStreamWriter(baos);

          TransformerFactory tranFactory = ransformerFactory.newInstance();
            Transformer aTransformer = tranFactory.newTransformer();
            aTransformer.setOutputProperty(OutputKeys.INDENT, "yes");
            aTransformer.setOutputProperty(OutputKeys.METHOD, "xml");
          aTransformer.setOutputProperty("http://xml.apache.org/xslt}indent-amount", "4");

            Source src = new DOMSource(newDoc);
            Result result = new StreamResult(new File(xmlFileName));
            aTransformer.transform(src, result);

            osw.flush();
            System.out.println(new String(baos.toByteArray()));

        } catch (Exception exp) {
            exp.printStackTrace();
        } finally {
            try {
                osw.close();
            } catch (Exception e) {
            }
            try {
                baos.close();
            } catch (Exception e) {
            }
        }

    }
    catch(IOException e)
    {
        System.out.println("IOException " + e.getMessage());
    }
}


public static void main (String[] args)
{
    MyExp poiExample = new MyExp ();
    poiExample.convertFile("srcfile.xls", "destfile.xml");
}

}

}