Excel To XML Converter

Utility Overview: To maintain the applications which use XML for to generating dynamic web pages such as shopping cart, product advertisements etc...¦ In this context it will be easier to maintain the required data in common applications such as MS Excel. In a nutshell, this utility can be defined as one which fetches data from these common applications to create complex XMLs which in turn can be used to generate dynamic web pages.

Functional Description:
Instead of straightly using XML, Excel can be uploaded to the server .The converter util will convert it to XML at the time of uploading. As mentioned in the above example the xml required is

<PRD>
<PRODUCTS>
<ProductName>King Sized Furniture </ProductName>
<Description>King Sized Furniture </Description>
<Image>980-CH1MANGLED.jpg</Image>
<Price>10</Price>
<Warranty>x</ Warranty >
</PRODUCTS>
<PRD>

Instead of creating just upload excel file in the following manner

ProductName Description I mage Price Warranty

King Sized Furniture King Sized Furniture 980-CH1MANGLED.jpg 10 x
King Sized Sectionals King Sized Sectionals 9419_SOFA-EXTRA.jpg 20 y


Technical Description:

JXL is used to manipulate Excel Sheets. Java Excel API is a mature, open source java API enabling developers to read, write, and modify Excel spreadsheets dynamically. The first row of the sheet is read and same is used to create the nodes of XML. Traversing through each column to get the data and same time new text nodes will be created and appended as child.

import java.util.ArrayList;
import java.util.Iterator;
import org.w3c.dom.*;
import jxl.*;
import jxl.read.biff.BiffException;
import java.io.*;
import javax.xml.parsers.*;
import javax.xml.transform.*;
import javax.xml.transform.dom.*;
import javax.xml.transform.stream.StreamResult;

public class ConvertExcelToXMl 
{
 private String sourceFile  = null;
 private String destFile = null;
 
 public ConvertExcelToXMl(String sourceFile, String destFile) 
 {
  super();
  this.sourceFile = sourceFile;
  this.destFile = destFile;
 }

 public void convertIt() throws BiffException, IOException, ParserConfigurationException, TransformerException
 {
  Workbook wb = null;
  ArrayList headingList = null;
  Sheet sheet = null;
  Document document = null;
  Element root = null;
  
  wb = Workbook.getWorkbook(new File(this.sourceFile));
  sheet = wb.getSheet(0);
  document = DocumentBuilderFactory.newInstance().newDocumentBuilder().newDocument();
  root = (Element)document.createElement("PRD");
  document.appendChild(root);
  headingList = this.getHeadings(sheet);
    
  for(int i=1;i<getMaxCol(sheet)  ;i++)
  {
   Node products = document.createElement("PRODUCTS");
   root.appendChild(products);
   Iterator headingListIter = headingList.iterator();
   for(int j=0;j<getMaxRow(sheet);j++)
   {
    if(headingListIter.hasNext())
    {
     Node subNode = document.createElement((String)headingListIter.next());
     products.appendChild(subNode);
     subNode.appendChild(document.createTextNode(sheet.getCell(j,i).getContents()));
    }
   }
  }
  this.writeXML(document);
 }
 
 private void writeXML(Document document) throws FileNotFoundException, TransformerException
 {
  TransformerFactory tf = TransformerFactory.newInstance();
  Transformer trf = tf.newTransformer();
  DOMSource source = new DOMSource(document);
  FileOutputStream fos = new FileOutputStream(new File(this.destFile));
  StreamResult result = new StreamResult(fos);
  trf.transform(source, result);
 }
 
 private ArrayList getHeadings(Sheet sheet)
 {
  ArrayList l1 = new ArrayList();
  int counter = 0;
  Cell[] cl = sheet.getRow(0);
  while( counter < cl.length)
  {
   l1.add(cl[counter].getContents());
   counter++;
  }
  return l1;
 }
 
 private int getMaxCol(Sheet sheet)
 {
  return sheet.getColumn(0).length;
 }
 
 private int getMaxRow(Sheet sheet)
 {
  return sheet.getRow(0).length;
 }
}


package com.tcs.fileupload.util;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;

import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.xml.sax.SAXException;

public class DisplayXMLUtil 
{
 private String fileLoc = null;
 
 public DisplayXMLUtil(String fileLoc)
 {
  super();
  this.fileLoc = fileLoc;
 }

 public ArrayList getData(String tagName) throws ParserConfigurationException, SAXException, IOException
 {
  DocumentBuilder builder = null;
        Document doc = null;
        File xmlfile=new File(this.fileLoc);
        builder = DocumentBuilderFactory.newInstance().newDocumentBuilder();
        doc = builder.parse(xmlfile);
        Element e = doc.getDocumentElement();
        NodeList MainNodes = e.getElementsByTagName(tagName);
        ArrayList l1 = this.getNodAryList(MainNodes);
        return l1;
 }
 
 public ArrayList getNodAryList(NodeList mainNodList)
 {
  ArrayList l1 = new ArrayList();
     if(mainNodList != null)
        {
         for(int i=0;i<mainNodList.getLength();i++)
         {
          Node subNode = mainNodList.item(i);
          if(subNode.hasChildNodes())
          {
            NodeList subNodeChildNodes = subNode.getChildNodes();
            for(int j = 0; j < subNodeChildNodes.getLength(); j++)
            {
                       Node lastNode =subNodeChildNodes.item(j);
                       l1.add(lastNode.getNodeValue());
                     }
          }
         }
        }
        return l1;
 }
 
 public ArrayList mergeArryLst(ArrayList list1,ArrayList list2)
 {
  ArrayList resList = new ArrayList();
  Iterator l1Iter = list1.iterator();
  Iterator l2Iter = list2.iterator();
  while(l1Iter.hasNext() && l2Iter.hasNext())
  {
   ProductBean bean = new ProductBean();
   bean.setProductName((String)l1Iter.next());
   bean.setProductImg((String)l2Iter.next());
   resList.add(bean);
  }
  return resList;
 }
}