Convert incoming XML to Excel Sheet Part 2 – Adapter Module way

[aashish_sinha]

As I described one approach to convert incoming XML file into Excel XML, this part will convert it to excel sheet using Adapter module in SAP PI.

Link to Part 1 XSLT Ways - Convert incoming XML to Excel or Excel XML – Part 1 - XSLT Way

I am not describing here –

1. How to develop adapter module

In this article, I will restrict us to the development approach using NWDS for adapter module.Pre-Requisite and Assumptions of the development:

1. Apache POI open Standard for excel Conversion
2. SAP PI specific Development libraries

                        com.sap.aii.af.cpa.svc.api.jar

                        com.sap.aii.af.lib.mod.jar

                        com.sap.aii.af.ms.ifc_api.jar

                        com.sap.aii.af.svc_api.jar
         3.    All ESR Activities are done in PI.
4.    All developments are taken place in PI 7.3

Source Structure

<?xml version="1.0" encoding="UTF-8"?> <ns0:MT_XMLToXLSTest_Out xmlns:ns0="http://Aviall.com/XLSTest"> <RECORD>  <TRANSACTION_TYPE>123456767</TRANSACTION_TYPE> <ACCOUNT_NUMBER>645768374</ACCOUNT_NUMBER>  <CREDIT_AMOUNT>1000</CREDIT_AMOUNT>  <CONTRIBUTION_TYPE>CONT</CONTRIBUTION_TYPE> <SSN>9123456</SSN> </RECORD> <RECORD>  <TRANSACTION_TYPE>1239876</TRANSACTION_TYPE> <ACCOUNT_NUMBER>1000849748</ACCOUNT_NUMBER>  <CREDIT_AMOUNT>5000</CREDIT_AMOUNT>  <CONTRIBUTION_TYPE>CONT</CONTRIBUTION_TYPE> <SSN>91111167</SSN> </RECORD> </ns0:MT_XMLToXLSTest_Out> 

Conversion Code from NWDS

Below Java code is a Sample java code for the conversion of incoming XML to excel sheet.

WriteXMLToXLS. Java

/**  *   */ package com.sap.adaptermodule;   import java.io.InputStream; import java.io.*; import java.rmi.RemoteException;   import javax.ejb.EJBException; import javax.ejb.SessionBean; import javax.ejb.SessionContext; import javax.ejb.TimedObject; import javax.ejb.Timer; import javax.xml.parsers.DocumentBuilder; import javax.xml.parsers.DocumentBuilderFactory;   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; import org.w3c.dom.Document; import org.w3c.dom.Element; import org.w3c.dom.Node; import org.w3c.dom.NodeList; import org.w3c.dom.*;   import com.sap.aii.af.lib.mp.module.ModuleContext; import com.sap.aii.af.lib.mp.module.ModuleData; import com.sap.aii.af.lib.mp.module.ModuleException; import com.sap.engine.interfaces.messaging.api.Message; import com.sap.engine.interfaces.messaging.api.MessageKey; import com.sap.engine.interfaces.messaging.api.PublicAPIAccessFactory; import com.sap.engine.interfaces.messaging.api.XMLPayload; import com.sap.engine.interfaces.messaging.api.auditlog.AuditAccess; import com.sap.engine.interfaces.messaging.api.auditlog.AuditLogStatus; import com.sap.tc.logging.Location;   /**  * @author Aashish Sinha  *  */ public class WriteXMLToXLSBean implements SessionBean, TimedObject {   /* (non-Javadoc)              * @see javax.ejb.SessionBean#ejbActivate()              */ @Override public void ejbActivate() throws EJBException, RemoteException { // TODO Auto-generated method stub   }   /* (non-Javadoc)              * @see javax.ejb.SessionBean#ejbPassivate()              */ @Override public void ejbPassivate() throws EJBException, RemoteException { // TODO Auto-generated method stub   }   /* (non-Javadoc)              * @see javax.ejb.SessionBean#ejbRemove()              */ @Override public void ejbRemove() throws EJBException, RemoteException { // TODO Auto-generated method stub   }   /* (non-Javadoc)              * @see javax.ejb.SessionBean#setSessionContext(javax.ejb.SessionContext)              */ @Override public void setSessionContext(SessionContext arg0) throws EJBException, RemoteException { // TODO Auto-generated method stub   }   /* (non-Javadoc)              * @see javax.ejb.TimedObject#ejbTimeout(javax.ejb.Timer)              */ @Override public void ejbTimeout(Timer arg0) { // TODO Auto-generated method stub   }   public void ejbCreate() throws javax.ejb.CreateException {   } @SuppressWarnings("deprecation") public ModuleData process(ModuleContext moduleContext, ModuleData inputModuleData) throws ModuleException { String SIGNATURE = "process(ModuleContext moduleContext, ModuleData inputModuleData)"; Location location = null; AuditAccess audit = null; // Create the location always new to avoid serialization/transient of // location try { location = Location.getLocation(this.getClass().getName()); } catch (Exception t) { t.printStackTrace(); ModuleException me = new ModuleException( "Unable to create trace location", t); throw me; }   Object obj = null; Message msg = null; MessageKey key = null; String msgType = null; String nameSpace = null; String key1 = null; String value1 = null; String Result = ""; // int counter = 0; try { obj = inputModuleData.getPrincipalData(); msg = (Message) obj; msgType = (String) moduleContext.getContextData("msgType"); nameSpace = (String) moduleContext.getContextData("nameSpace"); key1 = (String) moduleContext.getContextData("key"); value1 = (String) moduleContext.getContextData("value");   key = new MessageKey(msg.getMessageId(), msg.getMessageDirection()); audit = PublicAPIAccessFactory.getPublicAPIAccess() .getAuditAccess(); audit.addAuditLogEntry(key, AuditLogStatus.SUCCESS, "Write XMLToXLS: Module called"); } catch (Exception e) { ModuleException me = new ModuleException(e); throw me; }   try { XMLPayload xmlpayload = msg.getDocument(); InputStream isXML = (InputStream) xmlpayload.getInputStream(); DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance(); DocumentBuilder db = dbf.newDocumentBuilder(); Document doc = db.parse(isXML); doc.getDocumentElement().normalize();   // Reading Details node(having multiple occurrences) NodeList nodeLst = doc.getElementsByTagName("RECORD"); String Header[] = new String[nodeLst.getLength()]; for (int x = 0; x < nodeLst.getLength(); x++) { Node fstNode = nodeLst.item(x); if (fstNode.getNodeType() == Node.ELEMENT_NODE) { Element fstElmnt = (Element) fstNode; // Reading first element "Transaction Type" NodeList transc_type_lst = fstElmnt .getElementsByTagName("TRANSACTION_TYPE"); Element transc_type = (Element) transc_type_lst.item(0); NodeList trans_type = transc_type.getChildNodes(); String Transaction_Type = ((Node) trans_type.item(0)) .getNodeValue();   // Reading first element "ACCOUNT_NIMBER" NodeList accnt_num_lst = fstElmnt .getElementsByTagName("ACCOUNT_NIMBER"); Element accnt_num = (Element) accnt_num_lst.item(0); NodeList acc_num = accnt_num.getChildNodes(); String Account_Number = ((Node) acc_num.item(0)) .getNodeValue();   // Reading first element "CREDIT_AMOUNT" NodeList credit_amt_lst = fstElmnt .getElementsByTagName("CREDIT_AMOUNT"); Element credit_amt = (Element) credit_amt_lst.item(0); NodeList crd_amt = credit_amt.getChildNodes(); String Credit_Amount = ((Node) crd_amt.item(0)) .getNodeValue();   // Reading first element "CONTRIBUTION_TYPE" NodeList contrib_type_lst = fstElmnt .getElementsByTagName("CONTRIBUTION_TYPE"); Element contrib_type = (Element) contrib_type_lst.item(0); NodeList contri_typ = contrib_type.getChildNodes(); String Contribution_Type = ((Node) contri_typ.item(0)) .getNodeValue();   // Reading first element "SSN" NodeList SS_num_lst = fstElmnt.getElementsByTagName("SSN"); Element SS_num = (Element) SS_num_lst.item(0); NodeList SS_number = SS_num.getChildNodes(); String SSN = ((Node) SS_number.item(0)).getNodeValue();   Result = Result + Transaction_Type + "," + Account_Number + "," + Credit_Amount + "," + Contribution_Type + "," + SSN + "\n"; } }   int counter = 0; HSSFWorkbook hwb = new HSSFWorkbook(); HSSFSheet sheet = hwb.createSheet("Page1");   String inputArray[] = Result.split("\n");   for (int i = 0; i < inputArray.length; i++) { counter++; audit = PublicAPIAccessFactory.getPublicAPIAccess() .getAuditAccess(); audit.addAuditLogEntry(key, AuditLogStatus.SUCCESS, "Write XMLToXLS: Conversion Started"); String fieldValuesArray[] = inputArray[i].split(","); HSSFRow row = sheet.createRow((short)0+i); for (int j = 0; j < fieldValuesArray.length; j++) { HSSFCell cell = row.createCell((short) j);  cell.setCellValue(fieldValuesArray[j].toString()); } }   FileOutputStream output = new FileOutputStream(new File(value1)); hwb.write(output); output.flush(); output.close(); audit = PublicAPIAccessFactory.getPublicAPIAccess() .getAuditAccess(); audit.addAuditLogEntry(key, AuditLogStatus.SUCCESS, "Write XMLToXLS: Conversion Completed");   xmlpayload.setContent(Result.getBytes()); inputModuleData.setPrincipalData(msg);   } catch (Exception e) { ModuleException me = new ModuleException(e); throw me; } return inputModuleData; } } 

After the completion of code, deploy this code on server. Go to JNDI browser in NWA->Troubleshoot->JNDI Browser. From the drop down list select context name and provide localejbs in find textbox. Below screenshot shows the deployed adapter module.

Provide Module Parameter in Receiver Channel as per below screenshot -

Channel configuration as below.

Output file