Skip to Content
Technical Articles
Author's profile photo Yusuf Baysal

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 :

2.) commons-math3-3.6.1.jar :

This script can be also found on CPI Groovy Examples project shared by Fatih Pense:

Example page and GitHub directory



import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
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 org.apache.poi.hssf.usermodel.HSSFWorkbook;
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 = "";
       def body = message.getBody(String.class);
        byte[] data = java.util.Base64.getDecoder().decode(body)
       Document doc = newDocument();
		Element rootEl = doc.createElementNS(NSURL,

		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;
			// }

			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();
				case CellType.NUMERIC:
					// System.out.println(cell.getNumericCellValue());
					cellValue = String.valueOf(cell.getNumericCellValue());
				// case BOOLEAN: ... break;
				// case FORMULA: ... break;
					System.out.println("CELL TYPE NOT USED!");

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


       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.


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.

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Ramazan Sedat Göç
      Ramazan Sedat Göç

      Hi Yusuf,

      Very helpful blog. Thank you for sharing!


      Author's profile photo Yusuf Baysal
      Yusuf Baysal
      Blog Post Author

      Hi Sedat,

      Thank you!

      Author's profile photo Kishore Kannan Mohandas
      Kishore Kannan Mohandas

      If I'm not wrong, we can save the XLS file in CSV format and then we can easily convert the data into XML format right? In this case client has to place file in CSV format, which we need to request them.

      Author's profile photo Fatih Pense
      Fatih Pense

      Hello Kishore, I agree with you on avoiding XLS files for integration. I would also try to design an integration with proper data formats like XML, CSV, JSON... This blog by Yusuf is useful when you have a requirement that you can't change.

      Best regards,

      Author's profile photo Yusuf Baysal
      Yusuf Baysal
      Blog Post Author

      Hi Kishore,

      As Fatih Pense stated that some customers don't change Excel(xls) format to another formats. Bank integrations can be good case which data comes with excel and you have to convert it XML. Therefore, it can be necessary when we do not have another choice.

      Author's profile photo Karimulla Shaik
      Karimulla Shaik

      Hi Yusuf Baysal,


      While Exporting JAR poi-4.1.2.jar  file in CPI, I am getting

      Can you please let me know if any thing we need to check?
      Author's profile photo Karimulla Shaik
      Karimulla Shaik

      Hi Yusuf Baysal,


      I added JAR files in sequence then error gone.


      however i am getting the error.

      javax.script.ScriptException: java.lang.Exception: java.lang.IllegalArgumentException: Illegal base64 character d@ line 61 in script1.groovy, cause: java.lang.IllegalArgumentException: Illegal base64 character d


      Error coming at line:

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


      do you have any clue for this?






      Author's profile photo Tiyasa Biswas
      Tiyasa Biswas

      Hi Karimulla Shaik, were you able to resolve this?

      I am also getting the same error on executing the script.

      Author's profile photo ramakrishna b
      ramakrishna b

      I am trying to execute, but getting an error