使用java解析excel数据到xml [英] Parse excel data to xml using java

问题描述

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

任何代码示例或引用都将感激;

  public class POIExcelReader {
 
 public POIExcelReader ）{
} 
 
 public void displayFromExcel（String xlsPath）
 {
 InputStream inputStream = null; 
 try 
 {
 inputStream = new FileInputStream（xlsPath）; 
} 
 catch（FileNotFoundException e）
 {
 System.out.println（在指定的路径中找不到文件）; 
 e.printStackTrace（）; 
} 
 
 POIFSFileSystem fileSystem = null; 
 
 try {
 fileSystem = new POIFSFileSystem（inputStream）; 
 HSSFWorkbook工作簿=新HSSFWorkbook（fileSystem）; 
 HSSFSheet sheet = workBook.getSheetAt（0）; 
迭代器<？> rows = sheet.rowIterator（）; 
 
 while（rows.hasNext（））
 {
 HSSFRow row =（HSSFRow）rows.next（）; 
 
 //显示行号
 System.out.println（Row No .:+ row.getRowNum（））; 
 
 //获取一行，遍历单元格。 
迭代器<？> 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 数字：+ cell.getNumericCellValue（））; 
 break; 
} 
 case HSSFCell.CELL_TYPE_STRING：
 
 {
 // STRING CELL TYPE 
 HSSFRichTextString richTextString = cell.getRichStringCellValue（）; 
 
 System.out.println（String：+ richTextString.getString（））; 
 break; 
} 
默认值：
 {
 //除String和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）; 
} 
} 
  

解决方案

你可以使用包中的类：javax.xml.parsers。该包提供允许处理XML文档的类。例如DocumentBuilder，DocumentBuilderFactory，提到一些。

使用下面的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（在指定的路径中找不到文件）; 
 e.printStackTrace（）; 
} 
 
 POIFSFileSystem fileSystem = null; 
 
 try {
 //初始化XML文档
 DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance（）; 
 DocumentBuilder builder = factory.newDocumentBuilder（）; 
文档文档= builder.newDocument（）; 
元素rootElement = document.createElement（products）; 
 document.appendChild（rootElement）; 
 
 
 fileSystem = new POIFSFileSystem（inputStream）; 
 HSSFWorkbook工作簿=新HSSFWorkbook（fileSystem）; 
 HSSFSheet sheet = workBook.getSheetAt（0）; 
迭代器<？> rows = sheet.rowIterator（）; 
 
 ArrayList< ArrayList< String>> data = new ArrayList< ArrayList< String>>（）; 
 while（rows.hasNext（））
 {
 HSSFRow row =（HSSFRow）rows.next（）; 
 
 int rowNumber = row.getRowNum（）; 
 //显示行号
 System.out.println（Row No .:+ rowNumber）; 
 
 //获取一行，遍历单元格。 
迭代器<？> 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 数字：+ 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; 
} 
默认值：
 {
 //除String和Numeric之外的类型。 
 System.out.println（Type not supported。）; 
 break; 
} 
} // end switch 
 
} // end while 
 data.add（rowData）; 
 
 
} // end while 
 
 int numOfProduct = data.size（）; （int i = 1; i< numOfProduct; i ++）{
元素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; 
} 
 
元素headerElement = document.createElement（headerString）; 
 productElement.appendChild（headerElement）; 
 headerElement.appendChild（document.createTextNode（s））; 
 index ++; 
} 
} 
 
 TransformerFactory tFactory = TransformerFactory.newInstance（）; 
 
变压器变压器= tFactory.newTransformer（）; 
 //添加缩进输出
 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）; 
} 
} 
  

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.

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

解决方案

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.

Using the java code (extended your original code) below and the file located in this location: 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);
}
}

这篇关于使用java解析excel数据到xml的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！