Converting XLS file to XML format with CPI Groovy Script

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.