Java to generate XML from excel

user_10629157 0
Viewed 1447

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 The input is this

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>
xml java
1 Answers

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.

user_12772590
Related