package ReadAndConvert;

import java.io.File;
import java.io.FileOutputStream;

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

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;
import org.w3c.dom.NodeList;

public class ReadMain {
 private static String   fileslOCATION   = "C:/Users/<User>/Desktop/New folder";
 private static String   filesOUTlOCATION  = fileslOCATION + "/Output/";
 private static String   filesOUTFILE   = filesOUTlOCATION + "OutputFile.xls";
 private static String   filesTYPE    = ".object";
 private static String   filesCONVERTTYPE = "";
 private static String   filesROOT    = "fields";
 private static String[] filesElementne     = {"fullName","description","type","label","length","precision","scale","referenceTo","relationshipLabel","relationshipName"};

 public static void main(String[] args){
  //open folder and get all file location in lst
  File folder = new File(fileslOCATION);
  File[] listOfFiles = folder.listFiles();
  HSSFWorkbook workbook = new HSSFWorkbook();
     for (int i = 0; i < listOfFiles.length; i++) {
       if (listOfFiles[i].isFile() && listOfFiles[i].getName().endsWith(filesTYPE)) {
        Document doc   = null;
        try {
       doc =  parseXML(listOfFiles[i]);
        }
        catch (Exception e) {
          System.out.println("111");
          e.printStackTrace();
        }

        NodeList nList = doc.getElementsByTagName(filesROOT);
        HSSFSheet sheet = null;
        HSSFRow rowhead = null;
        sheet = workbook.createSheet(listOfFiles[i].getName().replace(filesTYPE, filesCONVERTTYPE));
        rowhead = sheet.createRow(0);
              for(int k = 0; k < filesElementne.length; k++){
               rowhead.createCell(k).setCellValue(filesElementne[k]);
              }

        for (int j = 0; j < nList.getLength(); j++)
           {
               Element eElement = (Element) nList.item(j);
               HSSFRow row = sheet.createRow((short)j+1);
               for(int k = 0; k < filesElementne.length; k++){
                Element cElement =  (Element) eElement.getElementsByTagName(filesElementne[k]).item(0);
                  if(cElement != null){
                 row.createCell(k).setCellValue(cElement.getTextContent());
                  }
                  else {
                 row.createCell(k).setCellValue("");
                }
               }
           }

        try{
         FileOutputStream fileOut = new FileOutputStream(filesOUTFILE);
         workbook.write(fileOut);
         fileOut.close();
     }catch (Exception e) {
      System.out.println("Error while flushing/closing fileWriter !!!");
               e.printStackTrace();
     }
        }
     }
 }

 private static Document parseXML(File fileValue) throws Exception
 {
     DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
     DocumentBuilder db = dbf.newDocumentBuilder();
     Document doc = db.parse(fileValue);
     doc.getDocumentElement().normalize();
     System.out.println("fileValue : "+fileValue.getName());
     return doc;
 }
}