Step-by-step guide to develop Adapter Module to read Excel file in PI/XI

By Jyothi Anagani, Yash Technologies

Before proceeding with the actual scenario, we would discuss about the importance of Adapter module.

Module acts as a special function that is used by an Adapter to provide the additional functionality to the Adapter.

By using File Adapter we can only read .xml files and .txt files (using File Content Conversion).  If we want to read an excel sheet, it is not possible by using the normal File Adapter. For this purpose we are developing a module & Deploying it into the sap j2ee server and we will specify the module name in module tab of Communication channel. Then this module will read excel sheet and convert that into an xml format which XI can understand.

Here is a simple example to read data from excel sheet and pass it into the Adapter Framework

The Step By Step Solution to Develop a Simple Adapter Module:

For the module development you have to extract following libraries from your PCK/AF installation:

Steps in SAP Net Weaver Developer Studio  

Open SAP Net Weaver Developer Studio.

Create a new Project as J2EE à EJB Module Project.  

Choose Project à Properties à Java Build Path à LibrariesàAdd External Jars to assign the libraries to your project.

We have to add one more jar file here. That is jxl.jar  à to read excel sheet content     You will find the jxl.jar at this location. http://vonage.otabi.com/activity/lib/jxl.jar and the corresponding API will be found at http://jexcelapi.sourceforge.net/resources/javadocs/current/docs/

Under EJB Module Project select ejbModule folder and Right Click to create a Package.

Right Click on Package à New à Java Class

That Class has to implement SessionBean and Module interfaces                          

When you create the adapter module your Java class has to implement Module Interface of adapter Module API (com.sap.aii.af.mp.module.Module).

This Module Interface has only one Method process

Write your Business Logic inside this process method ( ie Conversion of Exceel sheet content to xml structure ).

·         You get the error message: Bean problem: No Interface classes found. This occurs because there is no Java source available for the EJB interface classes.

• Choose the Package Explorer view in the Developer Studio.

• Choose your project, and in the context menu, choose Close Project.

• Choose Open Project again.

• Finally, switch back to the J2EE Explorer view

·         Your Class will Look Like this. 

/*
 * Created on Jun 12, 2009
 *
 * To change the template for this generated file go to
 * Window>Preferences>Java>Code Generation>Code and Comments
 */
package excelFileRead;

import java.io.ByteArrayInputStream;

import javax.ejb.CreateException;
import javax.ejb.SessionBean;
import javax.ejb.SessionContext;

import jxl.Cell;
import jxl.Workbook;

import com.sap.aii.af.mp.module.Module;
import com.sap.aii.af.mp.module.ModuleContext;
import com.sap.aii.af.mp.module.ModuleData;
import com.sap.aii.af.mp.module.ModuleException;
import com.sap.aii.af.ra.ms.api.Message;
import com.sap.aii.af.ra.ms.api.MessageDirection;
import com.sap.aii.af.ra.ms.api.XMLPayload;
import com.sap.aii.af.service.auditlog.Audit;
import com.sap.aii.af.service.auditlog.AuditDirection;
import com.sap.aii.af.service.auditlog.AuditLogStatus;
import com.sap.aii.af.service.auditlog.AuditMessageKey;
/**
* @ejbHome <{com.sap.aii.af.mp.module.ModuleHome}>
* @ejbLocal <{com.sap.aii.af.mp.module.ModuleLocal}>
* @ejbLocalHome <{com.sap.aii.af.mp.module.ModuleLocalHome}>
* @ejbRemote <{com.sap.aii.af.mp.module.ModuleRemote}>
* @stateless
*/
public class ExcelFileRead implements SessionBean, Module
{
	private SessionContext myContext;
	AuditMessageKey amk;
	public void ejbRemove() 
	{
	}
	public void ejbActivate() 
	{
	}
	public void ejbPassivate() 
	{
	}
	public void setSessionContext(SessionContext context) 
	{
		myContext = context;
	}
	public void ejbCreate() throws CreateException 
	{
	}
	public ModuleData process(
			ModuleContext mc,
			ModuleData imd)
			throws ModuleException {
			Object obj = null;
			Message msg = null;
			String msgType = null;
			String nameSpace = null;
			try {
				  obj = imd.getPrincipalData();
				  msg = (Message) obj;
				  msgType = (String) mc.getContextData("msgType");
				  nameSpace = (String) mc.getContextData("nameSpace");
				  

				  if (msg.getMessageDirection()== MessageDirection.INBOUND)
					amk = new AuditMessageKey(msg.getMessageId(),AuditDirection.INBOUND);
				  else
					amk = new AuditMessageKey(msg.getMessageId(),AuditDirection.OUTBOUND);
			  	

				  XMLPayload xp = msg.getDocument();
				  Audit.addAuditLogEntry(amk, AuditLogStatus.SUCCESS,"AO: Now got the xml payload object.");
				  if (xp != null) 
				  {
						Audit.addAuditLogEntry(amk, AuditLogStatus.SUCCESS,"AO: Now calling the Convert Method to convert Excel to XML.");
						byte by[] = convert(xp.getContent(),msgType,nameSpace);
						Audit.addAuditLogEntry(amk, AuditLogStatus.SUCCESS,"AO: Conversion Done Successfully.");
						xp.setContent(by);
				  }
				  imd.setPrincipalData(msg);
				  Audit.addAuditLogEntry(amk, AuditLogStatus.SUCCESS,"AO: Principle data is set successfully.");
			} catch (Exception e) 
			{
				Audit.addAuditLogEntry(amk, AuditLogStatus.SUCCESS,"AO: Module Exception Caught .");
				ModuleException me = new ModuleException(e);
				throw me;
			}
			return imd;
	  }

	public byte[] convert(byte src[],String msgType,String nameSpace) throws Exception 
	{
	String xmldata = "";
	try 
	{
		  ByteArrayInputStream byteArr= new ByteArrayInputStream(src);
		  Workbook wb = Workbook.getWorkbook(byteArr);
		  Audit.addAuditLogEntry(amk, AuditLogStatus.SUCCESS,"AO: Conversion Started.");
		  xmldata ="<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n\n"+ "<ns0:"+msgType+" "+"xmlns:ns0=\""+nameSpace+"\">";
		  Cell[] cells ;
		  Cell[] cellNames ;
		  cellNames = wb.getSheet(0).getRow(0);
		  for(int j=1;j<wb.getSheet(0).getRows();j++)
		  {
				xmldata = xmldata+"<Record>";
				cells = wb.getSheet(0).getRow(j);
				for(int i=0;i<wb.getSheet(0).getColumns();i++)
				{
					xmldata = xmldata+"<"+cellNames[i].getContents()+">"+cells[i].getContents()+"</"+cellNames[i].getContents()+">";
				}
				xmldata = xmldata+"</Record>";
		  }
		  xmldata = xmldata+"</ns0:"+msgType+">";	  
		  wb.close();
	 }
	 catch (Exception e)
	 {
		Audit.addAuditLogEntry(amk, AuditLogStatus.SUCCESS,"AO: Exception in Convert Method.");
	 }
	 return xmldata.getBytes();
	}
} 

Click here to continue....