I did this a while back. Not sure it will still work but should point you in the right direction. I normally do this in either Java or C# as it is easier that Jelly.
V/r,
Gene
<gel:script
xmlns:core="jelly:core"
xmlns:file="jelly:com.niku.union.gel.FileTagLibrary"
xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
xmlns:soap="jelly:com.niku.union.gel.SOAPTagLibrary"
xmlns:soap-env="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
xmlns:sql="jelly:sql"
xmlns:util="jelly:util"
xmlns:xog="http://www.niku.com/xog"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<core:catch var = "fileExp">
<core:new className = "java.io.File" var = "varFileInstance">
<core:arg type = "java.lang.String" value = "c:/temp/test.xls"/>
</core:new>
</core:catch>
<core:if test = "${fileExp!=null}">
<gel:log>Encountered File exception: ${fileExp}</gel:log>
</core:if>
<core:catch var = "FISExp">
<core:new className = "java.io.FileInputStream" var = "varFileisInstance">
<core:arg type = "java.io.File" value = "${varFileInstance}"/>
</core:new>
</core:catch>
<core:if test = "${FISExp!=null}">
<gel:log>Encountered exception in Inputstream: ${FISExp}</gel:log>
</core:if>
<core:catch var = "xssfexp">
<core:new className = "org.apache.poi.hssf.usermodel.HSSFWorkbook" var = "varxssfwb">
<core:arg type = "java.io.FileInputStream" value = "${varFileisInstance}"/>
</core:new>
</core:catch>
<core:if test = "${xssfexp!=null}">
<gel:log>Encountered exception in workbook: ${xssfexp}</gel:log>
</core:if>
<core:invoke method = "getSheetAt" on = "${varxssfwb}" var = "varSheet">
<core:arg type = "java.lang.Integer" value = "0"/>
</core:invoke>
<core:set value = "${varSheet.getPhysicalNumberOfRows()}" var = "rowCount"/>
<gel:log>Number of Rows in Sheet: ${rowCount}</gel:log>
<file:writeFile embedded="false" fileName="c:/temp/load_incidents_insert.csv" delimiter="|">
<file:line>
<file:column value="incident_no"/>
<file:column value="status"/>
<file:column value="creation_dt"/>
</file:line>
<core:set var="j" value="1" />
<core:while test = "${j < rowCount}">
<core:invoke method = "getRow" on = "${varSheet}" var = "cur_row">
<core:arg type = "java.lang.Integer" value = "${j}"/>
</core:invoke>
<core:if test = "${cur_row!=null}">
<core:invoke method = "getCell" on = "${cur_row}" var = "incident_no">
<core:arg type = "java.lang.Integer" value = "0"/>
</core:invoke>
<core:invoke method = "getCell" on = "${cur_row}" var = "status">
<core:arg type = "java.lang.Integer" value = "1"/>
</core:invoke>
<core:invoke method = "getCell" on = "${cur_row}" var = "creation_dt">
<core:arg type = "java.lang.Integer" value = "2"/>
</core:invoke>
<gel:log>Creation Date: ${creation_dt.toString()}</gel:log>
<core:set value="${incident_no.toString().trim().replaceAll('\n', ' ').replaceAll('\r', ' ').replaceAll('\x27', '').replaceAll(',', ' ')}" var="incident_no"/>
<core:set value="${status.toString().trim().replaceAll('\n', ' ').replaceAll('\r', ' ').replaceAll('\x27', '').replaceAll(',', ' ')}" var="status"/>
<core:set var="entries" value="${context.getVariables().entrySet().toArray()}" />
<core:forEach var="entry" items="${entries}">
<core:if test="${!entry.getKey().equalsIgnoreCase('systemScope')}" >
<gel:log> ${entry.getKey()} | ${entry.getValue()} | ${entry.getValue().getClass().getName()}</gel:log>
</core:if>
</core:forEach>
<core:new var="ExcelDateNumeric" className="java.lang.Integer">
<core:arg type="java.lang.String" value="${creation_dt}" />
</core:new>
<gel:log>here</gel:log>
<gel:log>Creation Date: ${creation_dt.toString()}</gel:log>
<core:if test = "${j != 0}">
<gel:log>Row_NUM: ${j}, Incident: ${incident_no}, Creation Date: ${creation_dt}</gel:log>
<file:line>
<core:choose>
<core:when test="${incident_no == null}">
<file:column value=""/>
</core:when>
<core:otherwise>
<file:column value="${incident_no}"/>
</core:otherwise>
</core:choose>
<core:choose>
<core:when test="${status == null}">
<file:column value=""/>
</core:when>
<core:otherwise>
<file:column value="${status}"/>
</core:otherwise>
</core:choose>
<core:choose>
<core:when test="${creation_dt == null}">
<file:column value=""/>
</core:when>
<core:otherwise>
<file:column value="${creation_dt}"/>
</core:otherwise>
</core:choose>
</file:line>
</core:if>
</core:if>
<core:set value="${j+1}" var="j"/>
</core:while>
</file:writeFile>
</gel:script>
Original Message:
Sent: 11-11-2019 09:25 AM
From: Manith Kongala
Subject: Read data which of .XLS Format
Hi,
Need a Script to read data which is in .XLS Format and push data into PPM(Only .XLS Format)
Thank You