Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Showing results for 
Search instead for 
Did you mean: 
yusufbaysal
Explorer
Sometimes you encounter file formats that are old and uncommon. While we tend to use new formats for our daily works there may be reasons to keep an old integration working with old formats. We had a requirement to use Excel XLS files. It is a different format from modern Excel ".xlsx" files which contain zipped XML. XLS is a binary format so we have to use a library. We had implemented this for SAP PI/PO and I think it is a good exercise to implement it in CPI and it may be really useful for consultants having the same requirement

In this blog, I will share how to convert XLS file to XML format with Groovy Script in CPI.

We are going to take an example Order sheet from Excel file and convert it to intended XML format.

XLS Example


Note that this file contains 3 sheets. We are going to pick "Order"


 

Groovy Script


Two external Jars need to be imported for this script. You can find them below.

1.) poi-4.1.2.jar : https://mvnrepository.com/artifact/org.apache.poi/poi/4.1.2

2.) commons-math3-3.6.1.jar : https://mvnrepository.com/artifact/org.apache.commons/commons-math3/3.6.1

This script can be also found on CPI Groovy Examples project shared by fatih.pense:

Example page and GitHub directory

 
import com.sap.gateway.ip.core.customdev.util.Message;

import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.io.ByteArrayInputStream
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerException;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.xml.sax.SAXException;




def Message processData(Message message) {

Map<String, String> headerToFieldMap = new HashMap<String, String>() {
{
put("Date", "Date");
put("Order Number", "OrderNumber");
put("Order Item Number", "OrderItemNumber");
put("Order Detail", "OrderDetail");

}
};

Map<Integer, String> indexToFieldMap = new HashMap<Integer, String>();

String NSURL = "https://mdpgroup.com";


//Body
def body = message.getBody(String.class);

byte[] data = java.util.Base64.getDecoder().decode(body)


Document doc = newDocument();

Element rootEl = doc.createElementNS(NSURL,
"ns1:MT_3RD_MDPGROUP_CPI_ORDER");
doc.appendChild(rootEl);

Workbook workbook = new HSSFWorkbook( new ByteArrayInputStream(data));

String sheetName = "Order";

// workbook.getSheetAt(1);
Sheet sheet = workbook.getSheet(sheetName);

for (Row row : sheet) {

// System.out.println(row.getRowNum());
// if(row.getRowNum()==0) {
// continue;
// }

// ADD A NEW ROW
Element itemEl = doc.createElementNS("", "order");

for (Cell cell : row) {
String cellValue = null;
switch (cell.getCellTypeEnum()) {
case CellType.STRING:
// System.out.println(cell.getStringCellValue());
cellValue = cell.getStringCellValue();
break;
case CellType.NUMERIC:
// System.out.println(cell.getNumericCellValue());
cellValue = String.valueOf(cell.getNumericCellValue());
break;
// case BOOLEAN: ... break;
// case FORMULA: ... break;
default:
System.out.println("CELL TYPE NOT USED!");
}

if (cellValue != null) {
// use the first row for header -> fieldname mapping
if (row.getRowNum() == 0) {
indexToFieldMap.put(cell.getColumnIndex(),
headerToFieldMap.get(cellValue));
continue;
}
// add item if it is not header.
rootEl.appendChild(itemEl);
// System.out.println(cellValue);
Element fieldEl = doc.createElementNS("", indexToFieldMap.get(cell.getColumnIndex()));
itemEl.appendChild(fieldEl);
fieldEl.setTextContent(cellValue);
}
}
}

message.setBody(doc)

return message;
}

public static Document newDocument() throws ParserConfigurationException {
DocumentBuilderFactory factory;
DocumentBuilder builder;
try {
factory = DocumentBuilderFactory.newInstance();
builder = factory.newDocumentBuilder();

return builder.newDocument();

} finally {
builder = null;
factory = null;
}
}

 

Create basic Integration Flow which takes HTTP Request.



Testing


In order to test, convert XLS Binary format to Base64 format and post it through Postman.


We have successfully converted XLS file to XML format!

Groovy script comes to help with uncommon requirements. We have implemented a solution that you can use for your own requirement with minimal modifications. I have also shared the script on GitHub where you can contribute & comment.

Thank you for reading.
11 Comments
Labels in this area