XML file to Excel file creation through Java Mapping

[former_member205051]

Hi Experts,

I have a requirement where PI will read a xml file and will create an excel file.

The xml file is like given below:

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

<incmstmts>

<stmt>

<year>2005</year>

<revenue>11837</revenue>

<costofrevenue>2239</costofrevenue>

<researchdevelopment>1591</researchdevelopment>

<salesmarketing>2689</salesmarketing>

<generaladmin>661</generaladmin>

<totaloperexpenses>7180</totaloperexpenses>

<operincome>4657</operincome>

<invincome>480</invincome>

<incbeforetaxes>5137</incbeforetaxes>

<taxes>1484</taxes>

<netincome>3653</netincome>

</stmt>

<stmt>

<year>2004</year>

<revenue>10818</revenue>

<costofrevenue>1875</costofrevenue>

<researchdevelopment>1421</researchdevelopment>

<salesmarketing>2122</salesmarketing>

<generaladmin>651</generaladmin>

<totaloperexpenses>6069</totaloperexpenses>

<operincome>4749</operincome>

<invincome>420</invincome>

<incbeforetaxes>5169</incbeforetaxes>

<taxes>1706</taxes>

<netincome>3463</netincome>

</stmt>

</incmstmts>

When I am executing my Java map in PI 7.3 version,I am observing that excel file is getting created without any data inside it

but when I am testing my Java Map locally through Main function then it is creating the excel file correctly with data.

My Java code that I am executing in PI is as given below:

//import java.io.File;
//import java.io.FileOutputStream;
import java.io.IOException;
//import java.io.InputStream;
//import java.io.OutputStream;

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

//import jxl.Workbook;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
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;
import org.xml.sax.SAXException;

import com.sap.aii.mapping.api.AbstractTransformation;
import com.sap.aii.mapping.api.StreamTransformationException;
import com.sap.aii.mapping.api.TransformationInput;
import com.sap.aii.mapping.api.TransformationOutput;

public class generateExcel extends AbstractTransformation
{
public void transform(TransformationInput arg0, TransformationOutput arg1) throws StreamTransformationException
{
   try
   {
    getTrace().addInfo("JAVA Mapping Called");
    //Workbook w = Workbook.getWorkbook(arg0.getInputPayload().getInputStream());
   
    HSSFWorkbook wb= new HSSFWorkbook();
    HSSFSheet spreadSheet=wb.createSheet("spreadSheet");
   
    spreadSheet.setColumnWidth((short) 0,(short) (256*25));
    spreadSheet.setColumnWidth((short) 1,(short) (256*25));
   
    DocumentBuilderFactory factory=DocumentBuilderFactory.newInstance();
    DocumentBuilder builder=factory.newDocumentBuilder();
   
    //InputStream xmlDocuments = null;
  Document document= builder.parse(arg0.getInputPayload().getInputStream());
    NodeList nodelist=document.getElementsByTagName("stmt");
   
    HSSFRow row=spreadSheet.createRow(0);
   
    HSSFCell cell=row.createCell((short)1);
    cell.setCellValue("Year 2005");
    cell=row.createCell((short)2);
    cell.setCellValue("Year 2004");
   
    HSSFRow row1 = spreadSheet.createRow(1);
    HSSFRow row2 = spreadSheet.createRow(2);
    HSSFRow row3 = spreadSheet.createRow(3);
    HSSFRow row4 = spreadSheet.createRow(4);
    HSSFRow row5 = spreadSheet.createRow(5);
    HSSFRow row6 = spreadSheet.createRow(6);
    HSSFRow row7 = spreadSheet.createRow(7);
    HSSFRow row8 = spreadSheet.createRow(8);
    HSSFRow row9 = spreadSheet.createRow(9);
    HSSFRow row10 = spreadSheet.createRow(10);
    HSSFRow row11 = spreadSheet.createRow(11);
   
    for(int i=0;i<nodelist.getLength(); i++)
    {
     HSSFCellStyle cellStyle=wb.createCellStyle();
     cellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
     cellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
     cellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
     cellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
    
     switch(i)
     {
     case 0:
     
      cell = row1.createCell((short) 0);
      cell.setCellValue("Revenue ($)");
     
      cell=row1.createCell((short)1);
      cell.setCellValue(((Element) (nodelist.item(0)))
                    .getElementsByTagName("revenue").item(0)
                    .getFirstChild().getNodeValue());
     
      cell = row2.createCell((short) 0);
      cell.setCellValue("Cost of Revenue ($)");

     
      cell = row2.createCell((short) 1);
      cell.setCellValue(((Element) (nodelist.item(0)))
                  .getElementsByTagName("costofrevenue").item(0)
                  .getFirstChild().getNodeValue());

     
      cell = row3.createCell((short) 0);
                  cell.setCellValue("Research and Development ($)");
                 
                  cell = row3.createCell((short) 1);
                  cell.setCellValue(((Element) (nodelist.item(0)))
                     .getElementsByTagName("researchdevelopment")
                     .item(0).getFirstChild().getNodeValue());
                 
                 
                  cell = row4.createCell((short) 0);
                  cell.setCellValue("Sales and Marketing ($)");

                 
                  cell = row4.createCell((short) 1);
                  cell.setCellValue(((Element) (nodelist.item(0)))
                     .getElementsByTagName("salesmarketing").item(0)
                     .getFirstChild().getNodeValue());
                 
                 
                  cell = row5.createCell((short) 0);
                  cell.setCellValue("General and Administrative ($)");

                 
                  cell = row5.createCell((short) 1);
                  cell.setCellValue(((Element) (nodelist.item(0)))
                     .getElementsByTagName("generaladmin").item(0)
                     .getFirstChild().getNodeValue());
                 
                 
                  cell = row6.createCell((short) 0);
                  cell.setCellValue("Total Operating Expenses ($)");
                  cell.setCellStyle(cellStyle);
                  cell = row6.createCell((short) 1);
                  cell.setCellValue(((Element) (nodelist.item(0)))
                     .getElementsByTagName("totaloperexpenses").item(0)
                     .getFirstChild().getNodeValue());
                 
                 
                  cell.setCellStyle(cellStyle);

                  cell = row7.createCell((short) 0);
                  cell.setCellValue("Operating Income ($)");

                  cell = row7.createCell((short) 1);
                  cell.setCellValue(((Element) (nodelist.item(0)))
                     .getElementsByTagName("operincome").item(0)
                     .getFirstChild().getNodeValue());

                  cell = row8.createCell((short) 0);
                  cell.setCellValue("Investment Income ($)");

                 
                  cell = row8.createCell((short) 1);
                  cell.setCellValue(((Element) (nodelist.item(0)))
                     .getElementsByTagName("invincome").item(0)
                     .getFirstChild().getNodeValue());

                  cell = row9.createCell((short) 0);
                  cell.setCellValue("Income Before Taxes ($)");
                  cell.setCellStyle(cellStyle);

                  cell = row9.createCell((short) 1);
                  cell.setCellValue(((Element) (nodelist.item(0)))
                     .getElementsByTagName("incbeforetaxes").item(0)
                     .getFirstChild().getNodeValue());

                 
                  cell.setCellStyle(cellStyle);

                  cell = row10.createCell((short) 0);
                  cell.setCellValue("Taxes ($)");

                  cell = row10.createCell((short) 1);
                  cell.setCellValue(((Element) (nodelist.item(0)))
                     .getElementsByTagName("taxes").item(0)
                     .getFirstChild().getNodeValue());

                  cell = row11.createCell((short) 0);
                  cell.setCellValue("Net Income ($)");
                  cell.setCellStyle(cellStyle);

                  cell = row11.createCell((short) 1);
                  cell.setCellValue(((Element) (nodelist.item(0)))
                     .getElementsByTagName("netincome").item(0)
                     .getFirstChild().getNodeValue());
                 
                  cell.setCellStyle(cellStyle);

                  break;
                 
     case 1:
     
     
      cell = row1.createCell((short) 2);
              cell.setCellValue(((Element) (nodelist.item(1)))
                 .getElementsByTagName("revenue").item(0)
                 .getFirstChild().getNodeValue());

     
              cell = row2.createCell((short) 2);
              cell.setCellValue(((Element) (nodelist.item(1)))
                 .getElementsByTagName("costofrevenue").item(0)
                 .getFirstChild().getNodeValue());
             
             
              cell = row3.createCell((short) 2);
              cell.setCellValue(((Element) (nodelist.item(1)))
                 .getElementsByTagName("researchdevelopment")
                 .item(0).getFirstChild().getNodeValue());

             
              cell = row4.createCell((short) 2);
              cell.setCellValue(((Element) (nodelist.item(1)))
                 .getElementsByTagName("salesmarketing").item(0)
                 .getFirstChild().getNodeValue());

              cell = row5.createCell((short) 2);
              cell.setCellValue(((Element) (nodelist.item(1)))
                 .getElementsByTagName("generaladmin").item(0)
                 .getFirstChild().getNodeValue());

              cell = row6.createCell((short) 2);
              cell.setCellValue(((Element) (nodelist.item(1)))
                 .getElementsByTagName("totaloperexpenses").item(0)
                 .getFirstChild().getNodeValue());

              cell.setCellStyle(cellStyle);

              cell = row7.createCell((short) 2);
              cell.setCellValue(((Element) (nodelist.item(1)))
                 .getElementsByTagName("operincome").item(0)
                 .getFirstChild().getNodeValue());

              cell = row8.createCell((short) 2);
              cell.setCellValue(((Element) (nodelist.item(1)))
                 .getElementsByTagName("invincome").item(0)
                 .getFirstChild().getNodeValue());

              cell = row9.createCell((short) 2);
              cell.setCellValue(((Element) (nodelist.item(1)))
                 .getElementsByTagName("incbeforetaxes").item(0)
                 .getFirstChild().getNodeValue());

              cell.setCellStyle(cellStyle);

              cell = row10.createCell((short) 2);
              cell.setCellValue(((Element) (nodelist.item(1)))
                 .getElementsByTagName("taxes").item(0)
                 .getFirstChild().getNodeValue());

              cell = row11.createCell((short) 2);
              cell.setCellValue(((Element) (nodelist.item(1)))
                 .getElementsByTagName("netincome").item(0)
                 .getFirstChild().getNodeValue());
              cell.setCellStyle(cellStyle);
              break;

           default:
              break;
           }

     }
   
   
    arg1.getOutputPayload().getOutputStream().write(spreadSheet.toString().getBytes("UTF-8"));
   
         
   
   // FileOutputStream output = new FileOutputStream(new File("IncomeStatements.xls"));
      //    wb.write(output);
       //   output.flush();
        //  output.close();
       } catch (IOException e)
       {
       
       } catch (ParserConfigurationException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } catch (SAXException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
      
    
    }
    
   
   
  
}

I want to know that when I am executing this Java code in PI excel file is getting created but no data is there,What is the changes neccessary in this code?

Regards

Joy

[former_member181985 · Accepted solution]

Hello,

Seems almost everything correct, except below statement

>>arg1.getOutputPayload().getOutputStream().write(spreadSheet.toString().getBytes("UTF-8"));

Try with below statement, it should work

wb.write(arg1.getOutputPayload().getOutputStream());

Also, check my other blog which gives more insight into java mapping covering external standalone, pi standalone and pi end to end java template codes for easy porting of code

http://scn.sap.com/community/pi-and-soa-middleware/blog/2012/08/05/coding-java-mapping-points-to-pon...

Message was edited by: Praveen Gujjeti

[Former Member]

Java Code for SAP PI 7.0 mapping:

import java.io.FileInputStream;

import java.io.FileOutputStream;

import java.io.IOException;

import java.io.OutputStream;

import java.io.InputStream;

import java.util.Map;

import com.sap.aii.mapping.api.StreamTransformation;

import com.sap.aii.mapping.api.AbstractTrace;

import java.util.HashMap;

import javax.xml.parsers.DocumentBuilder;

import javax.xml.parsers.DocumentBuilderFactory;

import javax.xml.parsers.ParserConfigurationException;

import org.apache.poi.hssf.usermodel.HSSFCell;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;

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;

import org.xml.sax.SAXException;

/*

* Created on Jun 16, 2014

*

* To change the template for this generated file go to

* Window&gt;Preferences&gt;Java&gt;Code Generation&gt;Code and Comments

*/

/**

* @author ashutosh.a.upadhyay

*

* To change the template for this generated type comment go to

* Window&gt;Preferences&gt;Java&gt;Code Generation&gt;Code and Comments

*/

public class XMLtoXLS implements StreamTransformation{

   private Map map = null;

   private AbstractTrace trace = null;

   public void setParameter(Map arg0) {

  map = arg0;   // Store reference to the mapping parameters

  if (map == null) {

  this.map = new HashMap();

  }

   }

  public static void main(String args[]) {  //FOR EXTERNAL STANDALONE TESTING

  try

  {

  FileInputStream fin = new FileInputStream ("C:/Users/ashutosh.a.upadhyay/Desktop/input.xml"); //INPUT FILE (PAYLOAD)

  FileOutputStream fout = new FileOutputStream ("C:/Users/ashutosh.a.upadhyay/Desktop/output.xls"); //OUTPUT FILE (PAYLOAD)

  XMLtoXLS mapping = new XMLtoXLS ();

  mapping.execute(fin, fout);

  }

  catch (Exception e1)  {

  e1.printStackTrace();

  }

  }

  public void execute(InputStream inputstream, OutputStream outputstream)

  {

  try {

  HSSFWorkbook wb= new HSSFWorkbook();

  HSSFSheet spreadSheet=wb.createSheet("spreadSheet");

    spreadSheet.setColumnWidth((short) 0,(short) (256*25));

  spreadSheet.setColumnWidth((short) 1,(short) (256*25));

  DocumentBuilderFactory factory=DocumentBuilderFactory.newInstance();

  DocumentBuilder builder=factory.newDocumentBuilder();

  //InputStream xmlDocuments = null;

  Document document= builder.parse(inputstream);

  NodeList nodelist=document.getElementsByTagName("stmt");

  HSSFRow row=spreadSheet.createRow(0);

  HSSFCell cell=row.createCell((short)1);

  cell.setCellValue("Year 2005");

  cell=row.createCell((short)2);

  cell.setCellValue("Year 2004");

  HSSFRow row1 = spreadSheet.createRow(1);

  HSSFRow row2 = spreadSheet.createRow(2);

  HSSFRow row3 = spreadSheet.createRow(3);

  HSSFRow row4 = spreadSheet.createRow(4);

  HSSFRow row5 = spreadSheet.createRow(5);

  HSSFRow row6 = spreadSheet.createRow(6);

  HSSFRow row7 = spreadSheet.createRow(7);

  HSSFRow row8 = spreadSheet.createRow(8);

  HSSFRow row9 = spreadSheet.createRow(9);

  HSSFRow row10 = spreadSheet.createRow(10);

  HSSFRow row11 = spreadSheet.createRow(11);

  for(int i=0;i<nodelist.getLength(); i++){

  HSSFCellStyle cellStyle=wb.createCellStyle();

  cellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);

  cellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);

  cellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);

  cellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);

  switch(i){

  case 0:

  cell = row1.createCell((short) 0);

  cell.setCellValue("Revenue ($)");

  cell=row1.createCell((short)1);

  cell.setCellValue(((Element) (nodelist.item(0))).getElementsByTagName("revenue").item(0).getFirstChild().getNodeValue());

  cell = row2.createCell((short) 0);

  cell.setCellValue("Cost of Revenue ($)");

  cell = row2.createCell((short) 1);

  cell.setCellValue(((Element) (nodelist.item(0))).getElementsByTagName("costofrevenue").item(0).getFirstChild().getNodeValue());

  cell = row3.createCell((short) 0);

  cell.setCellValue("Research and Development ($)");

  cell = row3.createCell((short) 1);

  cell.setCellValue(((Element) (nodelist.item(0))).getElementsByTagName("researchdevelopment").item(0).getFirstChild().getNodeValue());

  cell = row4.createCell((short) 0);

  cell.setCellValue("Sales and Marketing ($)");

  cell = row4.createCell((short) 1);

  cell.setCellValue(((Element) (nodelist.item(0))).getElementsByTagName("salesmarketing").item(0).getFirstChild().getNodeValue());

  cell = row5.createCell((short) 0);

  cell.setCellValue("General and Administrative ($)");

  cell = row5.createCell((short) 1);

  cell.setCellValue(((Element) (nodelist.item(0))).getElementsByTagName("generaladmin").item(0).getFirstChild().getNodeValue());

  cell = row6.createCell((short) 0);

  cell.setCellValue("Total Operating Expenses ($)");

  cell.setCellStyle(cellStyle);

  cell = row6.createCell((short) 1);

  cell.setCellValue(((Element) (nodelist.item(0))).getElementsByTagName("totaloperexpenses").item(0).getFirstChild().getNodeValue());

  cell.setCellStyle(cellStyle);

  cell = row7.createCell((short) 0);

  cell.setCellValue("Operating Income ($)");

  cell = row7.createCell((short) 1);

  cell.setCellValue(((Element) (nodelist.item(0))).getElementsByTagName("operincome").item(0).getFirstChild().getNodeValue());

  cell = row8.createCell((short) 0);

  cell.setCellValue("Investment Income ($)");

  cell = row8.createCell((short) 1);

  cell.setCellValue(((Element) (nodelist.item(0))).getElementsByTagName("invincome").item(0).getFirstChild().getNodeValue());

  cell = row9.createCell((short) 0);

  cell.setCellValue("Income Before Taxes ($)");

  cell.setCellStyle(cellStyle);

  cell = row9.createCell((short) 1);

  cell.setCellValue(((Element) (nodelist.item(0))).getElementsByTagName("incbeforetaxes").item(0).getFirstChild().getNodeValue());

  cell.setCellStyle(cellStyle);

  cell = row10.createCell((short) 0);

  cell.setCellValue("Taxes ($)");

  cell = row10.createCell((short) 1);

  cell.setCellValue(((Element) (nodelist.item(0))).getElementsByTagName("taxes").item(0).getFirstChild().getNodeValue());

  cell = row11.createCell((short) 0);

  cell.setCellValue("Net Income ($)");

  cell.setCellStyle(cellStyle);

  cell = row11.createCell((short) 1);

  cell.setCellValue(((Element) (nodelist.item(0))).getElementsByTagName("netincome").item(0).getFirstChild().getNodeValue());

  cell.setCellStyle(cellStyle);

  break;

  case 1:

  cell = row1.createCell((short) 2);

  cell.setCellValue(((Element) (nodelist.item(1))).getElementsByTagName("revenue").item(0).getFirstChild().getNodeValue());

  cell = row2.createCell((short) 2);

  cell.setCellValue(((Element) (nodelist.item(1))).getElementsByTagName("costofrevenue").item(0).getFirstChild().getNodeValue());

  cell = row3.createCell((short) 2);

  cell.setCellValue(((Element) (nodelist.item(1))).getElementsByTagName("researchdevelopment").item(0).getFirstChild().getNodeValue());

  cell = row4.createCell((short) 2);

  cell.setCellValue(((Element) (nodelist.item(1))).getElementsByTagName("salesmarketing").item(0).getFirstChild().getNodeValue());

  cell = row5.createCell((short) 2);

  cell.setCellValue(((Element) (nodelist.item(1))).getElementsByTagName("generaladmin").item(0).getFirstChild().getNodeValue());

  cell = row6.createCell((short) 2);

  cell.setCellValue(((Element) (nodelist.item(1))).getElementsByTagName("totaloperexpenses").item(0).getFirstChild().getNodeValue());

  cell.setCellStyle(cellStyle);

  cell = row7.createCell((short) 2);

  cell.setCellValue(((Element) (nodelist.item(1))).getElementsByTagName("operincome").item(0).getFirstChild().getNodeValue());

  cell = row8.createCell((short) 2);

  cell.setCellValue(((Element) (nodelist.item(1))).getElementsByTagName("invincome").item(0).getFirstChild().getNodeValue());

  cell = row9.createCell((short) 2);

  cell.setCellValue(((Element) (nodelist.item(1))).getElementsByTagName("incbeforetaxes").item(0).getFirstChild().getNodeValue());

  cell.setCellStyle(cellStyle);

  cell = row10.createCell((short) 2);

  cell.setCellValue(((Element) (nodelist.item(1))).getElementsByTagName("taxes").item(0).getFirstChild().getNodeValue());

  cell = row11.createCell((short) 2);

  cell.setCellValue(((Element) (nodelist.item(1))).getElementsByTagName("netincome").item(0).getFirstChild().getNodeValue());

  cell.setCellStyle(cellStyle);

  break;

  default:

  break;

  }

  }

  wb.write(outputstream);

  System.out.println(spreadSheet.toString());

  outputstream.flush();

  outputstream.close();

  }

  catch (IOException e) {

  e.printStackTrace();

  }

  catch (ParserConfigurationException e) {

  e.printStackTrace();

  }

  catch (SAXException e) {

  e.printStackTrace();

  }

  }

}

[former_member205051]

Thanks a lot Praveen for the help

[Former Member]

you can refer below blog it's might be helpful for step by step and java code also:

http://www.saptechnical.com/Tutorials/XI/Adapter/Index.htm

[Former Member]

Hi. Atanu

You can test your JavaMapping in PI.

Check this blog please.

http://scn.sap.com/community/pi-and-soa-middleware/blog/2012/07/08/can-we-test-binary-files-in-inter...

Regards

Lucho.