Processing Excel Files using Java Mapping/Groovy

In this blog I will write about how you can create your simple java map to process the excel file. In one of my project, I had to use java map due to complex requirement which can not be realized easily using existing open source adapter module. Also deploying module was not much fun as we need to ask basis by raising ticket and it takes lot of time.

Please dont jump into writing java map for handling excel file. When you get a requirement where you need to deal with excel, first you have to check if you can use this adapter module. This covers the most of the common use cases.

ExcelTransformBean Part 1: Convert various Excel formats to simple XML easily | SAP Blogs

FormatConversionBean arrives in CPI! | SAP Blogs

Standalone Code

Before we write java map, let us set up standalone java code to check if we can process the excel files. For this create maven project and add latest poi dependencies. One good thing starting with maven project helps to avoid searching for list of required jar dependencies. With this you can quickly write java code and test it.

https://mvnrepository.com/artifact/org.apache.poi/poi/5.0.0

https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml/5.0.0

For the sake of simplicity, I will use simple excel to csv conversion using java code. You can extend further based on your requirement.

package com.javamap.test;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.Iterator;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;


public class SimpleReadExcel {

	public static void main(String[] args) throws EncryptedDocumentException, IOException {


		try {
			Workbook workbook = WorkbookFactory.
		
					create(new FileInputStream("./data/data.xlsx"));
		//	System.out.println("Workbook has " + workbook.getNumberOfSheets() + " Sheets : ");

			Sheet sheet = workbook.getSheetAt(0);
		
		//	System.out.println("\n\nIterating over Rows and Columns using Iterator\n");
			Iterator<Row> rowIterator = sheet.iterator();

			while (rowIterator.hasNext()) {
				Row row = rowIterator.next();

				// Now let's iterate over the columns of the current row
				
				int len = row.getLastCellNum();

				for ( int i = 0; len > i ; i++) {
					
					System.out.print(row.getCell(i).toString());
					
					if(len-1 == i)
					{
						// print nothing
					}
					else
					{
						System.out.print(",");
					}
					
				}
				System.out.println();

			}

		} catch (Exception ex) {

			ex.printStackTrace();
		}

	}
}

input

output

SAP PO – Java Map

Now let us do the same in java map. But before we proceed, first we need to find out list of required  jars. I tried to collect all jars from latest poi version 5, unfortunately, this comes more than 20 MB, which I don’t want to upload into ESR. So I went with lower version which is 3.17. Still with version 3.17, ESR was freezing when I tried to upload around 6 MB size jar into imported archive.

You would need minimum these jars. Create java mapping project and add libraries and poi jars.

Download this zip file from below link and unzip it and collect required jars.

Index of /dist/poi/release/bin (apache.org)

Add it to nwds external jars

Java map

package com.test.javamap;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.Iterator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import com.sap.aii.mapping.api.AbstractTrace;
import com.sap.aii.mapping.api.AbstractTransformation;
import com.sap.aii.mapping.api.StreamTransformationException;
import com.sap.aii.mapping.api.TransformationInput;
import com.sap.aii.mapping.api.TransformationOutput;

public class JavaMapExcelToCSV extends AbstractTransformation {

	private final static String LINE_FEED = "\r\n";

	private boolean isRunningInEclipse = false;
	private String fileInput = null;

	@Override

	public void transform(TransformationInput in, TransformationOutput out) throws StreamTransformationException {

		AbstractTrace trace = this.getTrace();

		InputStream is = null;
		OutputStream os = null;

		if (in != null) {
			is = in.getInputPayload().getInputStream();
			os = out.getOutputPayload().getOutputStream();
		} else {
			try {
				is = new FileInputStream(this.fileInput);
				this.isRunningInEclipse = true;
			} catch (FileNotFoundException e) {
				e.printStackTrace();
				// throw your exception 

			}
		}
		try {

			Workbook workbook = WorkbookFactory.create(is);

			Sheet sheet = workbook.getSheetAt(0);
			Iterator<Row> rowIterator = sheet.iterator();
			StringBuilder sb = new StringBuilder();

			while (rowIterator.hasNext()) {
				Row row = rowIterator.next();

				// Now let's iterate over the columns of the current row

				int len = row.getLastCellNum();

				for (int i = 0; len > i; i++) {

					// System.out.print(row.getCell(i).toString());

					sb.append(row.getCell(i).toString());

					if (len - 1 == i) {
						// print nothing
					} else {
						sb.append(",");
					}

				}
				sb.append(LINE_FEED);

			}

			System.out.println(sb.toString());

			os.write(sb.toString().getBytes());

		} catch (Exception exception) {

			trace.addDebugMessage(exception.getMessage());

			throw new StreamTransformationException(exception.toString());

		}

	}

	public static void main(String[] args) throws StreamTransformationException {

		JavaMapExcelToCSV excelToCSV = new JavaMapExcelToCSV();

		excelToCSV.fileInput = "./data/data.xlsx";

		excelToCSV.transform((TransformationInput) null, (TransformationOutput) null);
	}

}

upload the jars into imported archive

Test in operation mapping

output

CPI – Groovy Script

I tried same code with little modification in groovy script and this also works fine in cpi.

import com.sap.gateway.ip.core.customdev.util.Message;
import java.util.HashMap;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;


/*

// use this for local testing groovy script
def is =  new FileInputStream("Your File name here")

def output = convertExcelToCSV(is, null)

// print output here

*/


def Message processData(Message message) {
// logging

    def messageLog = messageLogFactory.getMessageLog(message);
    if (messageLog != null) {
        messageLog.setStringProperty("log1", "starting")
        ;
    }

    // def body = message.getBody(java.lang.String)
    def body = message.getBody(java.io.InputStream)
    def output = convertExcelToCSV(body, messageLog)
    message.setBody(output)
    return message

}

def String convertExcelToCSV(def is, def messageLog) throws Exception {

    if (messageLog != null) {
        // Add your logging here

        messageLog.setStringProperty("Log2", "in Method convertExcelToCSV, starting conversion")
    }

    StringBuilder sb = new StringBuilder()

    def LINE_FEED = "\r\n"
    try {

        Workbook workbook = WorkbookFactory.create(is)
        Sheet sheet = workbook.getSheetAt(0)
        Iterator<Row> rowIterator = sheet.iterator()

        while (rowIterator.hasNext()) {
            Row row = rowIterator.next()

            // Now let's iterate over the columns of the current row

            int len = row.getLastCellNum();

            for (int i = 0; len > i; i++) {

                // System.out.print(row.getCell(i).toString());

                sb.append(row.getCell(i).toString());

                if (len - 1 == i) {
                    // print nothing
                } else {
                    sb.append(",");
                }

            }
            sb.append(LINE_FEED);

        }

        //System.out.println(sb.toString());

        if (messageLog != null) {
            // Add your logging here

            messageLog.setStringProperty("Log3", "in Method convertExcelToCSV, finished covnersion")
            messageLog.addAttachmentAsString("CSV Payload", sb.toString(), "text/csv");
        }


    } catch (Exception exception) {

        throw new RuntimeException(exception.toString());

    }
    return sb.toString()
}

jars uploaded into resources

I have created simple SFTP scenario which picks the excel file from sftp folder and process it.

output

Before you write a code for your project, please take a look at this code sample written by Eng Swee Yeoh | SAP People . It can help you to get an idea. The example shown in this blog is not a clean one.

equalize-cpi-converter/ConversionExcelInput.java at master · engswee/equalize-cpi-converter (github.com)