NilenduGhosal

Read EXCEL using GEL SCRIPT

Blog Post created by NilenduGhosal on Jun 13, 2018

I was working on a project where i was required to read EXCEL .

The excel consisted of Numeric Data, String Data and Data evaluated by formulas

 

 

Below is the reference code

 

<gel:script
xmlns:core="jelly:core"
xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
xmlns:sql="jelly:sql"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">


<gel:log>SCRIPT STARTED!!!!!</gel:log>


<core:catch var="SuperException">

<gel:log> File Location ("PATH_TO_FILE") </gel:log>

<core:new className="java.io.File" var="myFile">
<core:arg value="/fs0/clarity1/share/test.xlsx"/>
</core:new>
<gel:log> File Size ${myFile.length()} </gel:log>

<core:new className="java.io.FileInputStream" var="fis">
<core:arg type="java.io.File" value="${myFile}"/>
</core:new>
<gel:log> Return Required XLSX workbook </gel:log>

<core:new className="org.apache.poi.xssf.usermodel.XSSFWorkbook" var="myWorkBook">
<core:arg type="java.io.FileInputStream" value="${fis}"/>
</core:new>
<gel:log> Get FInancial Summary sheet</gel:log>

<core:invoke method="getSheetAt" on="${myWorkBook}" var="mySheet">
<core:arg value="${3}"/>
</core:invoke>

<gel:log> Get single row from sheet</gel:log>

<core:invoke method="getRow" on="${mySheet}" var="getHeader">
<core:arg value="${6}"/>
</core:invoke>

<!--workbook.getCreationHelper().createFormulaEvaluator().evaluateInCell(cell).getCellType()-->

<gel:log>CHK1</gel:log>

<gel:log> Get iterator to the cell in current sheet</gel:log>

<core:invoke method="cellIterator" on="${getHeader}" var="cellIterator"/>

<gel:log> Traversing over one row of XLSX file </gel:log>
<gel:log>____________________________________________________________</gel:log>

<core:while test="${cellIterator.hasNext()}">
<!--gel:log>Get Cell Type ${cell.getCellType()} </gel:log-->
<!--gel:log>CHK2</gel:log-->
<core:invoke method="next" on="${cellIterator}" var="cell"/>

<core:catch var="SuperException2">
<core:switch on="${cell.getCachedFormulaResultType()}">
<core:case value="${1}">
<gel:log>${cell.getStringCellValue()} </gel:log>
</core:case>

<core:case value="${0}">
<gel:log>${cell.getNumericCellValue()} </gel:log>
</core:case>

<core:default/>

</core:switch>
</core:catch>
<core:catch var="SuperException2">
<core:switch on="${cell.getCellType()}">
<core:case value="${1}">
<gel:log>${cell.getStringCellValue()} </gel:log>
</core:case>

<core:case value="${0}">
<gel:log>${cell.getNumericCellValue()} </gel:log>
</core:case>

<core:default/>

</core:switch>
</core:catch>
</core:while>

</core:catch>

<core:if test="${SuperException!=null}">
<gel:log>Super Exception -- ${SuperException}</gel:log>
</core:if>

 


</gel:script>

Outcomes