Nrthugu

I have a spreadsheet based on which I have to generate a xml file. the file should respect a particular XSD structure and I have to do from Java.

The XML mapping is not working in excel because of "list of lists " issue.
what would be an ideal approach in java to create , domparser or jaxb ?
Any reference is helpful

Here is my code

package exceltoXML;



import java.io.BufferedReader;

import java.io.BufferedWriter;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.FileWriter;

import java.io.IOException;

import java.io.InputStream;

import java.io.InputStreamReader;

import java.io.Writer;

import java.nio.file.Files;

import java.nio.file.Path;

import java.nio.file.Paths;

import java.text.SimpleDateFormat;

import java.util.ArrayList;

import java.util.Date;

import java.util.Enumeration;

import java.util.Iterator;

import java.util.List;

import java.util.Map;

import java.util.Properties;

import java.util.TreeMap;



import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.Row;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.eclipse.jgit.*;

import org.eclipse.jgit.api.CommitCommand;

import org.eclipse.jgit.api.Git;

import org.eclipse.jgit.api.PushCommand;

import org.eclipse.jgit.api.errors.GitAPIException;

import org.eclipse.jgit.transport.UsernamePasswordCredentialsProvider;



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.TransformerException;

import javax.xml.transform.TransformerFactory;

import javax.xml.transform.dom.DOMSource;

import javax.xml.transform.stream.StreamResult;



import org.w3c.dom.Document;

import org.w3c.dom.Element;

import org.w3c.dom.Node;





public class readExcel_class1 {



    public static Properties properties;

    public static Enumeration<?> en;

    public static String SourcePath;

    public static String targetPath;

    public static Map<String, String> propertiesCaseInsensitiveMap;          

    public static List<foldermap> foldermaps;





    public static void main(String args) throws IOException, ParserConfigurationException, TransformerException {

        // TODO Auto-generated method stub

         Writer writer = null;



        try {

            foldermaps = new ArrayList<foldermap>();

            properties=loadProperties(args[0]);



            en=properties.propertyNames();

            propertiesCaseInsensitiveMap=new TreeMap<String,String>(String.CASE_INSENSITIVE_ORDER);

            while(en.hasMoreElements()){

                String key=(String)en.nextElement();

                propertiesCaseInsensitiveMap.put(key, properties.getProperty(key));

            }

            SourcePath=propertiesCaseInsensitiveMap.get("sourcepath");

            targetPath=propertiesCaseInsensitiveMap.get("targetPath");



            System.out.println("Source Path is "+ SourcePath + " and Target path is " + targetPath);



            String fileName = "export_"+new Date().getTime() + ".json";

            System.out.println("Output filename is " + fileName);



         //System.out.println("Hi");

        File myFile = new File("C:\Users\abcdra\Desktop\cicd\git_push.xlsm");

        FileInputStream fis = new FileInputStream(myFile);

        String sourceFileName = SourcePath+fileName;

        String targetFileName = targetPath+fileName;

        System.out.println("Source FileName : " + sourceFileName);

        System.out.println("Target FileName : " + targetFileName);

        File file = new File(sourceFileName);  



        DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();

        DocumentBuilder builder1 = factory.newDocumentBuilder();

        Document document = builder1.newDocument();



        Element rootElement = document.createElement("importParams");

        document.appendChild(rootElement);



        rootElement.setAttribute("xmlns", "http://www.abc.io/oie/importControl/9" );



        writer = new BufferedWriter(new FileWriter(file));



        // Finds the workbook instance for XLSX file

        XSSFWorkbook myWorkBook = new XSSFWorkbook (fis);



        // Return first sheet from the XLSX workbook

        XSSFSheet mySheet = myWorkBook.getSheetAt(1);



        Iterator<Row> rowIterator = mySheet.iterator();



        ArrayList<ArrayList<String>> data = new ArrayList<ArrayList<String>>();



        foldermap foldermap = new foldermap();

        while (rowIterator.hasNext()) {



            //System.out.println("Current Row Number : " + rowIterator.next().getRowNum());

            Row row = rowIterator.next();

            if(row.getRowNum()==0 ){

                continue; //just skip the rows if row number is 0

               }

            // For each row, iterate through each columns

            Iterator<Cell> cellIterator = row.cellIterator();

            ArrayList<String> rowData = new ArrayList<String>();





            while (cellIterator.hasNext()) {



                Cell cell = cellIterator.next();



                switch (cell.getCellType()) {

                case STRING:

                    //System.out.println ("String: " + cell.getRichStringCellValue ());

                    rowData.add(cell.getRichStringCellValue() + "");

                    //System.out.println("Inside cells");



                    if (cell.getColumnIndex() ==0 )

                    {

                    foldermap.setSourceProject(cell.getStringCellValue()); 

                    System.out.println("source project " + foldermap.getSourceProject());

                    }

                    if (cell.getColumnIndex() ==1 )

                    {

                    foldermap.setSourceFolderPath (cell.getStringCellValue());                                   

                    }

                    if (cell.getColumnIndex() ==2 )

                    {

                    foldermap.setTargetProject(cell.getStringCellValue());                                   

                    }

                    if (cell.getColumnIndex() ==3 )

                    {

                    foldermap.setTargetFolderPath( cell.getStringCellValue());                                   

                    }

                    if (cell.getColumnIndex() ==4 )

                    {

                    foldermap.setRecursive(cell.getStringCellValue());  

                    System.out.println("Recursive value " + foldermap.getRecursive());

                    }

                    if (cell.getColumnIndex() ==5 )

                    {

                    foldermap.setRecursive(cell.getStringCellValue());  

                    System.out.println("Recursive value " + foldermap.getRecursive());

                    }



                    break;

                case NUMERIC:

                    System.out.println ("String: " + cell.getNumericCellValue ());



                    break;



                case BOOLEAN:

                    System.out.print(cell.getBooleanCellValue() + "t");



                    break;

                default :



                }



            }

          //  System.out.println("n");  

            writer.write("n "); 



            foldermaps.add(foldermap);   

        }



System.out.println("Writing to text file is completed...");

if (writer != null) {      

     writer.close();      

     }    



TransformerFactory transformerFactory=TransformerFactory.newInstance();

Transformer transformer = transformerFactory.newTransformer();

DOMSource source = new DOMSource(document);

//create target path if it does not exist



Element foldermaps1 = document.createElement("folderMaps");

rootElement.appendChild(foldermaps1);

System.out.println("Number of foldermap are " + foldermaps.size());

for (foldermap fm : foldermaps)

{

    System.out.println("Source Project is " + fm.getSourceProject());

    Element fmap = document.createElement("folderMap");

    foldermaps1.appendChild(fmap);

    fmap.setAttribute("sourceProject", fm.getSourceProject());

    fmap.setAttribute("sourceFolderPath", fm.getSourceFolderPath());

    fmap.setAttribute("targetProject", fm.getTargetProject());

    fmap.setAttribute("targetFolderPath", fm.getTargetFolderPath());

    fmap.setAttribute("recursive", fm.getRecursive());

}







StreamResult result = new StreamResult(new File("C:\Users\abcdra\Desktop\eclipse_workspace_64\abc.xml"));

transformer.setOutputProperty

(OutputKeys.INDENT, "yes");

transformer.transform(source, result);



System.out.println("Parameter file successfully created");



    }

    catch(IOException ex )

    {

          ex.printStackTrace();

    }





}



    public static Properties loadProperties(String filename){

        Properties props = new Properties();        

        InputStream input = null;

        try {

            input = new FileInputStream(filename);

            props.load(input);

            System.out.println("Loaded properties from: " + filename);

        } catch (FileNotFoundException e) {

            e.printStackTrace();

        } catch (IOException e) {

            e.printStackTrace();

        }

        return props;

    }



     public static void fileCopy (File sourceDirectory , File targetDirectory)

     {



         try {

             Files.copy(sourceDirectory.toPath(),targetDirectory.toPath());

             }

             catch(Exception e)

             {

                System.out.println("Error" + e);

             }



     }



}

The input is like

I am getting the output like

<?xml version="1.0" encoding="UTF-8" standalone="no"?>

<importParams xmlns="http://www.informatica.com/oie/importControl/9">

<folderMaps>

<folderMap recursive="truth" sourceFolderPath="Hive" sourceProject="Unknown1" targetFolderPath="import" targetProject="Unknown"/>

<folderMap recursive="truth" sourceFolderPath="Hive" sourceProject="Unknown1" targetFolderPath="import" targetProject="Unknown"/>

</folderMaps>

</importParams>

Both elements have value unknown1 , so the last element is repeating ,any idea how to avoid that. the output I am expecting is

<?xml version="1.0" encoding="UTF-8" standalone="no"?>

<importParams xmlns="http://www.informatica.com/oie/importControl/9">

<folderMaps>

<folderMap recursive="truth" sourceFolderPath="Hive" sourceProject="Unknown" targetFolderPath="import" targetProject="Unknown"/>

<folderMap recursive="truth" sourceFolderPath="Hive" sourceProject="Unknown1" targetFolderPath="import" targetProject="Unknown"/>

</folderMaps>

</importParams>

There is quite a bit of code here, but I think the issue is that the foldermap is not being created new each time inside the row loop.

 foldermap foldermap = new foldermap();

 while (rowIterator.hasNext()) {

   ...



   foldermaps.add(foldermap);

 }

So the foldermap is being updated, but added multiple times.

Move the new foldermap(); inside the while loop so that on each row, there is a new entry.

I think, if I read the code correctly, you can do:

while (rowIterator.hasNext()) {

  foldermap foldermap = new foldermap();

  ...

As the foldermap does not need scope outside of the while loop for the rowIterator.