Technical Articles
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.
Great blog..Thanks Muni 🙂
Hi Muni,
Appreciate your efforts, Thank you for sharing.
Hi Muni,
Could you please confirm which file formats it will accept for CPI, It works only for XLSX?
Thanks,
Dinesh
Hi Dinesh,
It should work for xls format as well.
Muni
Hi Muni,
Great blog, I will try out details provided in the blog and share my feedback. Looking forward for many more blogs from you.
Thanks,
Nagendra
Am I wrong, or I found that if the CSV has some empty values, the CSV to XML converter is not working properly?