Java utility to create/populate a DVM from XL sheet -SOA 11g

Today I am posting the java code which reads in a input XL with the dvm data and creates a dvm file. I had created a utility in 10g , now just modified it for 11g.This is very useful in scenarios where there is huge amount of data to be inserted into DVM before the interface can start working. Even though we have soa composer and JDev to enter the recods into DVM. manual entry of large number of DVM data is not a feasible solution For running the java program pass the 2 arguments
for eg: -inputXL=D:\Projects\DVMTest\Sites.xls
-outputDVM=D:\Projects\DVMTest\SiteTest.dvm
This will work for any number of columns.The first row in the XL sheet represents the column name that needs to appear in the DVM. If decimal points are not required modify the code to replace the decimal value. Hope this helps.

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;


/**
 * This class has methods which will read the excel file and convert it into a
 * a DVM file.
 *
 *@author George Thomas
 *@version 1.0
 *
 */
public class DVMCreator {
    private final static Logger logger =
        Logger.getLogger(DVMCreator.class.getName());

    public DVMCreator() {
        super();
    }


    private String inputFile;

    /**
     * @param inputFile
     */
    public void setInputFile(String inputFile) {
        this.inputFile = inputFile;
    }


    /**
     * This method listens for incoming records and handles them as required.
     *
     * @return sheetData
     * @throws Exception
     */
    public List getSheetData() throws Exception {
        //
        // Create an ArrayList to store the data read from excel sheet.
        //
        List sheetData = new ArrayList();

        FileInputStream fis = null;
        try {
            //
            // Create a FileInputStream that will be use to read the
            // excel file.
            //
            fis = new FileInputStream(inputFile);

            //
            // Create an excel workbook from the file system.
            //
            HSSFWorkbook workbook = new HSSFWorkbook(fis);
            //
            // Get the first sheet on the workbook.
            //
            HSSFSheet sheet = workbook.getSheetAt(0);

            //
            // When we have a sheet object in hand we can iterator on
            // each sheet's rows and on each row's cells. We store the
            // data read on an ArrayList so that we can printed the
            // content of the excel to the console.
            //
            Iterator rows = sheet.rowIterator();
            while (rows.hasNext()) {
                HSSFRow row = (HSSFRow)rows.next();
                Iterator cells = row.cellIterator();
                List data = new ArrayList();
                while (cells.hasNext()) {
                    HSSFCell cell = (HSSFCell)cells.next();
                    data.add(cell);
                }

                sheetData.add(data);
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (fis != null) {
                fis.close();
            }
            showExcelData(sheetData);
            // processExelData(sheetData);
            return sheetData;
        }
    }


     private static String convertExcelToDVMData(List sheetData,String dvmName ) {
        //
        // Iterates the data and print it out to the console.
        //
        StringBuffer xmlData = new StringBuffer();
      
      dvmName = dvmName.replaceAll(".dvm", "");

        
        Map map = new HashMap();
        xmlData.append("<?xml version=\"1.0\" encoding=\"UTF-8\" ?>");
        xmlData.append("<dvm name=\"");
        xmlData.append(dvmName);
        xmlData.append("\"");
        xmlData.append(" xmlns=\"http://xmlns.oracle.com/dvm\">");
        
        xmlData.append("<description></description>");
        xmlData.append("<columns>");


        List columnNamelist = (List)sheetData.get(0);
        for (int j = 0; j < columnNamelist.size(); j++) {
            xmlData.append("<column name=\"");
            HSSFCell cell = (HSSFCell)columnNamelist.get(j);
            if (cell.getCellType() == 0) {
                logger.log(Level.INFO, " " + cell.getNumericCellValue());
                xmlData.append(cell.getNumericCellValue());

            } else if (cell.getCellType() == 1) {
                logger.log(Level.INFO,
                           "S " + cell.getRichStringCellValue().getString());
                xmlData.append(cell.getRichStringCellValue().getString());
            }
            xmlData.append("\"/>");

        }
        logger.log(Level.INFO, "");
        xmlData.append("</columns>");


        xmlData.append("<rows>");

        for (int i = 1; i < sheetData.size(); i++) {
            xmlData.append("<row>");
            List list = (List)sheetData.get(i);
            for (int j = 0; j < list.size(); j++) {
                xmlData.append("<cell>");
                HSSFCell cell = (HSSFCell)list.get(j);
                if (cell.getCellType() == 0) {
                    logger.log(Level.INFO, " " + cell.getNumericCellValue());
                    xmlData.append(cell.getNumericCellValue());

                } else if (cell.getCellType() == 1) {
                    logger.log(Level.INFO,
                               "S " + cell.getRichStringCellValue().getString());
                    xmlData.append(cell.getRichStringCellValue().getString());
                }
                xmlData.append("</cell>");
                if (j < list.size() - 1) {
                    logger.log(Level.INFO, ", ");
                }
            }
            logger.log(Level.INFO, "");
            xmlData.append("</row>");
        }
        xmlData.append("</rows>");
        xmlData.append("</dvm>");

        return xmlData.toString();
    }

  



    

  public static boolean writeDVM(String xmlData, File file) throws Exception {

       
      try {
          FileOutputStream fOut = new FileOutputStream(file);
          fOut.write(xmlData.getBytes());
          fOut.flush();
          fOut.close();
          logger.log(Level.INFO, "File Created .::" + file.getName());

      } catch (Exception e) {
          logger.log(Level.INFO,
                     "!!BOOOM!! xlCreate() failed : " + e.getMessage());
          throw e;
      }
      return true;
  }
  
  
    /**
     * main method
     *
     * @param args      Expect one argument that is the file to read.
     * @throws IOException  When there is an error processing the file.
     */
    public static void main(String[] args) throws IOException {
        DVMCreator reader = new DVMCreator ();
        String spliter[] = null;
        String inputXL = null;
        String outputDVM = null;
        if (args.length == 0 || args.length == 1) {
            logger.log(Level.INFO, " USAGE:- Enter the input excel file \n" +
                    "     -inputXL  and enter the output DVM file location and name -outputDVM \n");
        } else if (args[0].equals("-help")) {
            logger.log(Level.INFO, "USAGE:- Enter the input excel file \n" +
                    "     -inputXL  and enter the output DVM file location and name -outputDVM \n");
        } else {
            if (args[0].contains("-inputXL") &&
                args[1].contains("-outputDVM")) {
                spliter = args[0].split("=");
                inputXL = spliter[1];
                reader.setInputFile(inputXL);
                spliter = args[1].split("=");
                outputDVM = spliter[1];
                File file = null;
              try {
                   file = new File(outputDVM);
                  logger.log(Level.INFO, "File Created .::" + outputDVM);

              } catch (Exception e) {
                  logger.log(Level.INFO,
                             "!!BOOOM!! xlCreate() failed : " + e.getMessage());
                
              }

                try {
                    List data = reader.getSheetData();
                    
                    String xmlData = convertExcelToDVMData(data,file.getName());
                    boolean flag = writeDVM(xmlData, file);
                    logger.log(Level.INFO, "XML data ::" + xmlData);


                } catch (Exception e) {
                    logger.log(Level.INFO, "Error occured" + e.getMessage());
                    e.printStackTrace();
                }
                logger.log(Level.INFO, "DVM  write done.");

            }


        }
    }
}