Adapter Module to create Excel Output File

Requirement

There are times, when the business acts odd and asks for an Excel File as the output. Even though we are able to send the CSV file, the requirement would be to create Excel (.xls) file. We are trying to convert a simple XML into Excel Sheet.

Solution

The requirement is handled using a custom adapter module which is created and placed in the Receiver File Channel. The code is given as below. In case you want to check how to write an Adapter Module, this can be easily found in SCN in the following link. Check: SCN: Adapter Module Creation in PI 7.1.

Code

/**
 * 
 */
package com.sap.adaptermodule; 
/**
 * This bean is used to write an excel file in the 2003 (.xls) format
 * from the XML. This is used in the outbound File adapter.
 * 
 * @author anupam.chakraborty
 * @date 23rd October 2012

 * 
 * @ChangedOn     @Defect or CRNumber        @ChangedBy         @ChangeDescription
 * dd/mm/yyyy     xxxxxxxxxxxxxxxxxxx        axxxxxxxxxxx       xxxxxxxxxxxxxxxx
 *
 */

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.util.Locale;

import javax.ejb.CreateException;
import javax.ejb.SessionBean;
import javax.ejb.SessionContext;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;

import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

import com.sap.aii.af.lib.mp.module.Module;
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;

public class ExcelDownloadBean implements SessionBean, Module{
public static final String VERSION_ID = "$Id://tc/aii/30_REL/src/_adapters/_sample/java/user/module/ExcelDownload.java#1 $";
static final long serialVersionUID = 7435850550539048631L;

static DocumentBuilderFactory factory;
static DocumentBuilder parser;
static {
try {
factory = DocumentBuilderFactory.newInstance();
parser = factory.newDocumentBuilder();
} catch (Exception e) {
throw new RuntimeException(":: ABORT MAP :: Error in instantiation of the object");
}
}
AuditAccess audit = null;
MessageKey key = null;

public void ejbRemove() {}
public void ejbActivate() {}
public void ejbPassivate() {}
public void setSessionContext(SessionContext context) { }

public void ejbCreate() throws CreateException{
}
/**
 * This is the main Method being called as a part of the Bean
 */
public ModuleData process(ModuleContext moduleContext, ModuleData inputModuleData) throws ModuleException {
// Create the location always new to avoid serialisation/transient of location 
Object obj = null;
Message msg = null;
try {
obj = inputModuleData.getPrincipalData();
msg = (Message) obj;
XMLPayload xp = msg.getDocument();
key = new MessageKey(msg.getMessageId(), msg.getMessageDirection());
audit = PublicAPIAccessFactory.getPublicAPIAccess().getAuditAccess();

//Start Executing the Excel convertion Module.
if (xp != null)
{
Document domDoc = parser.parse(new ByteArrayInputStream(xp.getContent()));
byte by[] = converttoExcel(domDoc);
audit.addAuditLogEntry(key, AuditLogStatus.SUCCESS, "ExcelDowloadBean: Conversion Done Successfully.");
xp.setContent(by);
}
//Set output data
inputModuleData.setPrincipalData(msg);

//Any exception.
} catch (Exception e)
{
audit.addAuditLogEntry(key, AuditLogStatus.ERROR,"ExcelDowloadBean: Module Exception Caught .");
ModuleException me = new ModuleException(e);
throw me;
}
return inputModuleData;
}
/**
 * 
 * @param sht = Excel Work Sheet 
 * @param domDoc = XML Dom Document
 * @throws Exception
 */
public byte[] converttoExcel(Document domDoc) throws Exception
{
// 
ByteArrayOutputStream byteArr= new ByteArrayOutputStream();
WorkbookSettings ws = new WorkbookSettings();
ws.setLocale(new Locale("en", "EN"));
WritableWorkbook workbook = Workbook.createWorkbook(byteArr);

// Create the Sheet in the Excel
WritableSheet sht = workbook.createSheet("Sheet1", 0);
/* Format the Font 1 for header and 1 for line item */
    WritableFont wfHd = new WritableFont(WritableFont.TAHOMA,10,WritableFont.BOLD);
    WritableFont wfDt = new WritableFont(WritableFont.TAHOMA,10,WritableFont.NO_BOLD);
    WritableCellFormat cfHd = new WritableCellFormat(wfHd);
    WritableCellFormat cfDt = new WritableCellFormat(wfDt);
    Label lblXlArr = null;

//Find the Root Node
Node nodeRoot = domDoc.getDocumentElement();
String strRoot = nodeRoot.getNodeName();
String strNS = nodeRoot.getAttributes().item(0).getNodeValue();
System.out.println ("Input Root Node: " + strRoot + " Root NameSpace: " + strNS);

//Necessary Initialisation
Node nodeRec,nodeFld;
int i,j,ctRec = 0, ctFld;
String strRecName,strFldName,strFldVal = "";
NodeList nlRec, nlFlds;
// Get the records
nlRec = nodeRoot.getChildNodes();
//For each record
for(i = 0; i < nlRec.getLength();i++){
nodeRec = nlRec.item(i);
strRecName = nodeRec.getNodeName();
if (strRecName != "#text"){
nlFlds = nodeRec.getChildNodes();
ctFld = 0;
// For Each Fields 
for(j = 0; j < nlFlds.getLength();j++){
nodeFld = nlFlds.item(j);
strFldName = nodeFld.getNodeName();
//Check if the Record has non null value.
if(nodeFld.hasChildNodes())
strFldVal = nodeFld.getFirstChild().getNodeValue();
else
strFldVal = "";
//Only for the First record Add Header into the first line.
if (ctRec == 0){
lblXlArr =  new Label(ctFld,0,strFldName,cfHd);
sht.addCell(lblXlArr);
}
lblXlArr =  new Label(ctFld,ctRec+1,strFldVal,cfDt);
sht.addCell(lblXlArr);
ctFld++;
}
ctRec++;
}
}
workbook.write();
    workbook.close(); 

byte[] bytes = byteArr.toByteArray();
return bytes;
}
}