Prashank Singh

GEL Scripting: Reading xlsx document

Blog Post created by Prashank Singh on Aug 4, 2017

Sometimes, client wants to push data feed for CA PPM through .xls/.xlsx file, which is generated from any other system. However, we don't have any specific gel tag for reading such files and that becomes a hurdle while achieving this. Only solution for these scenarios is to invoke java code in gel for reading and writing or modifying xlsx documents.

 

I have tried to read xlsx document via gel so that we can understand some basic logic related to these, Please follow comments and logs in script for learning gel as below.

 

Sample GEL Script for reading xlsx as below:

<?xml version="1.0" encoding="UTF-8"?>  
<gel:script xmlns:core="jelly:core" 
            xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary" 
            xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
            xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:util="jelly:util">    
    <!-- *****************************************************************  -->
    <!-- Program:       Read xlsx File                                      -->
    <!-- Object:        NA                                                  -->
    <!-- Step:          Start Step                                          -->
    <!-- Action:        GEL                                                 -->
    <!-- Author:        Prashank Singh,(Pemari Technology Consultant)       -->
    <!-- Version:       1.0.00                                              -->
    <!-- Created On:    02th August 2017                                    -->
    <!-- Modified On:   02th August 2017, Modified By: Prashank Singh       -->
    <!--                                                                    -->
    <!-- Modification History:   Version 1.0.00                             -->
    <!-- Dependencies:   NA
                                                                           -->
    <!-- ****************************************************************** -->
 <core:catch var="SuperException">
    <gel:log> File Location ("G:\Apps\Clarity\Clarity2211\TestExcel Data\TransactionData.xlsx") </gel:log> 
    <core:new className="java.io.File" var="myFile"> 
        <core:arg value="G:\Apps\Clarity\Clarity2211\TestExcel Data\CODA Extract - Random Generated.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 first sheet from the 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 iterator to all the rows in current sheet </gel:log> 
     <core:invoke method="getSheetAt" on="${myWorkBook}" var="mySheet">
        <core:arg  value="${0}"/>
     </core:invoke>
    <gel:log> Traversing over each row of XLSX file  </gel:log> 
    <core:invoke method="iterator" on="${mySheet}" var="rowIterator"/>
    <gel:log>____________________________________________________________</gel:log>
    <core:while test="${rowIterator.hasNext()}">
        <gel:log> For each row, iterate through each columns </gel:log>
        <core:invoke method="next" on="${rowIterator}" var="row"/> 
        <core:invoke method="cellIterator" on="${row}" var="cellIterator"/>    
        <core:while test="${cellIterator.hasNext()}">
            <gel:log>Get Cell Type ${cell.getCellType()} </gel:log>
            <core:invoke method="next" on="${cellIterator}" var="cell"/>       
            <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:while>
    </core:while>  
</core:catch>
    <core:if test="${SuperException!=null}">
      <gel:log>Super Exception -- ${SuperException}</gel:log>
   </core:if>  
</gel:script>

Similary you can look for implementation of any document write/modifaction via apache poi jar, Do let me know for you feedback or any comment. 

Outcomes