0 Replies Latest reply: Apr 20, 2011 1:21 AM by 814917 RSS

    Reading data from an excel file and generating xml with records as nodes

    814917
      Hi,I wrote a code that reads data from the excel file and i'm able to convert each record of the excel file to a node in XML.
      But the problem is that xml version tag is getting repeated for each node in the xml(i.e., for each record in excel file)
      could anybody help me get over this issue.Any help is greatly appreciated.


      Her's my code

      import jxl.Cell;
      import jxl.Sheet;
      import jxl.Workbook;
      import jxl.WorkbookSettings;
      import jxl.read.biff.BiffException;
      import java.io.FileInputStream;
      import java.io.IOException;
      import java.io.InputStream;
      import java.util.Locale;
      import java.io.File;
      import java.io.FileWriter;
      import javax.xml.bind.*;


      import javax.xml.bind.annotation.XmlElement;

      public class ReadExcel {


      WorkbookSettings ws = null;
      Workbook workbook = null;
      Sheet s = null;
      Cell rowData[] = null;
      int totalSheets = 0;
      Record record = null;




      public static void main(String[] args) throws Exception
      {


      ReadExcel excel=new ReadExcel();
      excel.readAndConvertToXML("C:\\Users\\257367\\Desktop
      karthik1.xls");


      }
      public void readAndConvertToXML(String filepath)
      {

      /**JAXBContext serves as the Entry Point for making use of the JAXB API.
      * It is initiated by the list of Class objects whose objects want to be represented as XML Documents.
      * Then an instance of Marshaller is created by calling JAXBContext.createMarshaller() method.
      * The Java object of type Person is marshalled into the XML Document by calling the Marshaller.marshall() method.
      * Since we want the XML Document to look Well-indented, we have set the property Marshaller.JAXB_FORMATTED_OUTPUT to true.
      */



      FileInputStream fs = null;


      try
      {
      JAXBContext context = JAXBContext.newInstance(Record.class);
      Marshaller marshaller = context.createMarshaller();
      marshaller.setProperty(Marshaller.JAXB_FORMATTED_OUTPUT, true);
      fs = new FileInputStream(new File(filepath));
      InputStream is = (InputStream)fs;
      ws = new WorkbookSettings();
      ws.setLocale(new Locale("en", "EN"));
      workbook = workbook.getWorkbook(is, ws);
      s = workbook.getSheet(0);
      FileWriter fileWriter = new FileWriter(".\\src\\Person.xml",true);


      for(int i=0;i<s.getRows();i++)
      {
      rowData = s.getRow(i);
      if(rowData.length>1)
      {
      record=new Record(rowData);
      marshaller.marshal(record,);


      }


      }
      workbook.close();
      }
      catch(IOException exception)
      {
      exception.printStackTrace();
      }
      catch(BiffException biffException)
      {
      biffException.printStackTrace();
      }
      catch(Exception exception)
      {
      exception.printStackTrace();
      }


      }



      }

      Here's my Record class.
      import jxl.Cell;
      import java.util.Date;
      import javax.xml.bind.annotation.XmlAttribute;
      import javax.xml.bind.annotation.XmlElement;
      import javax.xml.bind.annotation.XmlRootElement;
      import javax.xml.bind.annotation.XmlType;

      @XmlRootElement()

      public class Record {

           
           private String SNo;
           private String taskNo;
           private String taskName;
           private String taskDescription;
           
           public Record(){       
           }
           
           public Record(String SNo,String taskNo, String taskName,String taskDescription) {
           this.SNo = SNo;
           this.taskNo = taskNo;
           this.taskName = taskName;
           this.taskDescription = taskDescription;
           }
           
           public Record(Cell row[])
           {
                this.SNo=row[0].getContents();
           this.taskNo =row[1].getContents();
           this.taskName = row[2].getContents();
           this.taskDescription = row[3].getContents();
                
           }
           
           
           @XmlElement()
           public String getSNo() {
                     return SNo;
                }

                public void setSNo(String sNo) {
                     SNo = sNo;
                }
                @XmlElement()
                public String getTaskNo() {
                     return taskNo;
                }

                public void setTaskNo(String taskNo) {
                     this.taskNo = taskNo;
                }
                @XmlElement()
                public String getTaskName() {
                     return taskName;
                }

                public void setTaskName(String taskName) {
                     this.taskName = taskName;
                }
                @XmlElement()
                public String getTaskDescription() {
                     return taskDescription;
                }

                public void setTaskDescription(String taskDescription) {
                     this.taskDescription = taskDescription;
                }

      }




      And the resulting xml is


      <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
      <record>
      <SNo>1</SNo>
      <taskDescription>sdlhopsdih goi sdfghodfg dofgh dfgodsh o</taskDescription>
      <taskName>uploading data</taskName>
      <taskNo>1</taskNo>
      </record>
      <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
      <record>
      <SNo>2</SNo>
      <taskDescription>fghdfghfgjghjk</taskDescription>
      <taskName>development</taskName>
      <taskNo>4</taskNo>
      </record>
      <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
      <record>
      <SNo>3</SNo>
      <taskDescription>gfhkfgi hjf'nhji f'hjfi'</taskDescription>
      <taskName>testing</taskName>
      <taskNo>5</taskNo>
      </record>



      How can elimainate this "<?xml version="1.0" encoding="UTF-8" standalone="yes"?>" getting repeated for each node rather than appearing for the first time.

      Edited by: 811914 on Apr 19, 2011 11:20 PM