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
| TRANSACTION_TYPE | ACCOUNT_NIMBER | CREDIT_AMOUNT | CONTRIBUTION_TYPE | SSN |
<?xml version="1.0" encoding="UTF-8" ?>
-<ns0:MT_Excel xmlns:ns0="POC">
-<RECORD> <TRANSACTION_TYPE>Srikanth</TRANSACTION_TYPE>
<ACCOUNT_NIMBER>00014145</ACCOUNT_NIMBER>
<CREDIT_AMOUNT>5000</CREDIT_AMOUNT>
<CONTRIBUTION_TYPE>LAON</CONTRIBUTION_TYPE>
<SSN>123</SSN>
</RECORD>
-<RECORD> <TRANSACTION_TYPE>Veeru</TRANSACTION_TYPE>
<ACCOUNT_NIMBER>140022244</ACCOUNT_NIMBER>
<CREDIT_AMOUNT>4500</CREDIT_AMOUNT>
<CONTRIBUTION_TYPE>Donation</CONTRIBUTION_TYPE>
<SSN>8123</SSN>
</RECORD>
-<RECORD> <TRANSACTION_TYPE>Mamidi</TRANSACTION_TYPE>
<ACCOUNT_NIMBER>4767501</ACCOUNT_NIMBER>
<CREDIT_AMOUNT>21154</CREDIT_AMOUNT>
<CONTRIBUTION_TYPE>Charity</CONTRIBUTION_TYPE>
<SSN>5678</SSN>
</RECORD>
</ns0:MT_Excel>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
package com.sap.adaptermodule;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
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.cf.CellRange;
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.apache.poi.ss.usermodel.Cell;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
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.aii.af.service.auditlog.Audit;
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.engine.interfaces.messaging.api.exception.MessagingException;
import com.sap.tc.logging.Location;
/**
* @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/ExcelBean.java#1 $";
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) {
t.printStackTrace();
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()
.getAuditAccess();
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);
doc.getDocumentElement().normalize();
// 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
.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;
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);
cell.setCellValue(fieldValuesArray[cellcount].toString());
audit.addAuditLogEntry(key, AuditLogStatus.SUCCESS,
"##### Current Value in the Cell ::##### " + fieldValuesArray[cellcount].toString());
}
}
// Output file Location in the NFS Server
FileOutputStream out = new FileOutputStream(
"/Interface/TEST/AAE/Sucess.xls");
hwb.write(out);
out.close(); //close the excel file
audit = PublicAPIAccessFactory.getPublicAPIAccess()
.getAuditAccess();
audit.addAuditLogEntry(key, AuditLogStatus.SUCCESS,
"WriteXMLToXLS: Conversion Completed");
xmlpayload.setContent(Result.getBytes());
inputModuleData.setPrincipalData(msg);
} 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
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
- SAP Managed Tags:
