Skip to Content
Technical Articles

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.

5 Comments
You must be Logged on to comment or reply to a post.
  • 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.

    • 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,
      Fatih

    • 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.