Skip to Content
Author's profile photo srikanth kakani

Inserting the Data into Excel File Using SAP PI

We often get requirements where we have to convert the XML file into an EXcel Sheet. In Our project we got a different requirement where instead of creating a file of excel format it was required to insert the data into an already existing Excel Sheet.

This task was supposed to be carried out by PI

As a reference we have considered following blog where we can convert the XML data into Excel Sheet.


The Document describes how to convert and Insert the XML data into Excel file. It can insert the data in different sheets of the excel file based input payload.

Now here is the data in the XML file which we would like to insert into an Excel sheet which looks like this


<?xml version="1.0" encoding="UTF-8" ?>
-<ns0:MT_Excel xmlns:ns0="POC">

Here we are using Apache POI open Standard for excel Conversion for converting the Xml file into Excel file.

below are Jar file we have used

  • poi-2.5.1.jar
  • poi-3.5-beta1.jar

Module Code to Insert the Data in to Excel File

import java.rmi.RemoteException;
import javax.ejb.CreateException;
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 jxl.Workbook;
import jxl.write.WritableWorkbook;
import org.apache.poi.hssf.record.formula.functions.Row;
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.Node;
import org.w3c.dom.NodeList;
 * @author Srikanth Kakani
public class ExcelBean implements SessionBean, Module {
    public static final String VERSION_ID = "$Id://tc/aii/30_REL/src/_adapters/_sample/java/user/module/ $";
    static final long serialVersionUID = 7435850550539048631L;
    AuditAccess audit = null;
    MessageKey key = null;
    public void ejbRemove() {
    public void ejbActivate() {
    String Result = "";
    public void ejbPassivate() {
    public void setSessionContext(SessionContext context) {
    public void ejbCreate() throws CreateException {
    public ModuleData process(ModuleContext moduleContext,
            ModuleData inputModuleData) throws ModuleException {
        Location location = null;
        // Create the location always new to avoid serialization/transient of location
        try {
            location = Location.getLocation(this.getClass().getName());
        } catch (Exception t) {
            ModuleException me = new ModuleException(
                    "Unable to create trace location", t);
            throw me;
        Object obj = null;
        Message msg = null;
        String msgType = null;
        String nameSpace = null;
        String Result = "";
        try {
            obj = inputModuleData.getPrincipalData();
            msg = (Message) obj;
            // get the message type from Module configuration
            msgType = (String) moduleContext.getContextData("msgType");
            // get the Namespace from Module configuration
            nameSpace = (String) moduleContext.getContextData("nameSpace");
            key = new MessageKey(msg.getMessageId(), msg.getMessageDirection());
            audit = PublicAPIAccessFactory.getPublicAPIAccess()
            audit.addAuditLogEntry(key, AuditLogStatus.SUCCESS,
                    "Write XMLToXLS: Module called");
        } catch (Exception e) {
            ModuleException me = new ModuleException(e);
            throw me;
        try {
        // code for converting the XML data into String and storea in a Array
            XMLPayload xmlpayload = msg.getDocument();
            InputStream isXML = (InputStream) xmlpayload.getInputStream();
            DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
            DocumentBuilder db = dbf.newDocumentBuilder();
            Document doc = db.parse(isXML);
            // ReadingDetails 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
                    Element transc_type = (Element) transc_type_lst.item(0);
                    NodeList trans_type = transc_type.getChildNodes();
                    String Transaction_Type = ((Node) trans_type.item(0))
                    // Reading first element "ACCOUNT_NIMBER"
                    NodeList accnt_num_lst = fstElmnt
                    Element accnt_num = (Element) accnt_num_lst.item(0);
                    NodeList acc_num = accnt_num.getChildNodes();
                    String Account_Number = ((Node) acc_num.item(0))
                    // Reading first element "CREDIT_AMOUNT"
                    NodeList credit_amt_lst = fstElmnt
                    Element credit_amt = (Element) credit_amt_lst.item(0);
                    NodeList crd_amt = credit_amt.getChildNodes();
                    String Credit_Amount = ((Node) crd_amt.item(0))
                    // Reading first element "CONTRIBUTION_TYPE"
                    NodeList contrib_type_lst = fstElmnt
                    Element contrib_type = (Element) contrib_type_lst.item(0);
                    NodeList contri_typ = contrib_type.getChildNodes();
                    String Contribution_Type = ((Node) contri_typ.item(0))
                    // 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;
            String inputArray[] = Result.split("\n"); //input data will store in the Array
            audit.addAuditLogEntry(key, AuditLogStatus.SUCCESS,
            "##### Input Data stored in the Array is ::##### " + inputArray[0].toString());
            //this will check for the Excel File which has already placed in the perticular location NFS File System.
            HSSFWorkbook hwb = new HSSFWorkbook(new FileInputStream("/Interface/TEST/AAE/Sucess.xls"));
            for (int test = 0; test < inputArray.length; test++) {
                String fieldValuesArray[] = inputArray[test].split(","); // split the data stored in the Array
                audit.addAuditLogEntry(key, AuditLogStatus.SUCCESS,
                        "##### Input Data after splitting by comma ::##### " + fieldValuesArray.toString());
                //get the Name of the perticular sheet
                HSSFSheet sheet = hwb.getSheet(fieldValuesArray[0].toString());
                audit.addAuditLogEntry(key, AuditLogStatus.SUCCESS,
                        "##### Sheet Name ::##### " + fieldValuesArray[0].toString());
                int lastRowNumber = sheet.getLastRowNum(); // it will goto the last row of the perticular sheet
                audit.addAuditLogEntry(key, AuditLogStatus.SUCCESS,
                        "##### Last Row Number in the excel sheet is ::##### " + lastRowNumber);
                HSSFRow row = sheet.createRow(lastRowNumber+1);
                // insert the data based on the first value of the array
                for (int cellcount = 0; cellcount < fieldValuesArray.length; cellcount++) {
                    HSSFCell cell = row.createCell((short) cellcount);
                    audit.addAuditLogEntry(key, AuditLogStatus.SUCCESS,
                            "##### Current Value in the Cell ::##### " + fieldValuesArray[cellcount].toString());
        // Output file Location in the NFS Server
            FileOutputStream out = new FileOutputStream(
            out.close(); //close the excel file
                    audit = PublicAPIAccessFactory.getPublicAPIAccess()
            audit.addAuditLogEntry(key, AuditLogStatus.SUCCESS,
                    "WriteXMLToXLS: Conversion Completed");
        } catch (Exception e) {
            ModuleException me = new ModuleException(e);
            throw me;
        return inputModuleData;

Receiver Communication channel Configuration:

Provide Module Parameter in Receiver Channel as per below screenshot

channel cofg.jpg

Once the xml file is picked up from the source directory,the existing Excel file will be update with the latest input data in the target directory which as mentioned in the code.


The data has been updated in the excel sheet as shown below


Please note that the Excel file need to be present in the NFS target directory

Assigned Tags

      Be the first to leave a comment
      You must be Logged on to comment or reply to a post.