Clarity

Hi Team

I am trying to read the date column from excel (.xls). But the timestamp has truncated and shows the data "03-Aug-2018".

It would be great, how can read date column from excel in gel script

Data from Input file:

03/08/2018 03:13:21

Code:

<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: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>

<core:invoke method = "getCell" on = "${cur_row}" var = "creation_dt">
<core:arg type = "java.lang.Integer" value = "${0}"/>
</core:invoke>

<core:set value="${creation_dt.toString()}" var="creation_dt"/>

Hi Senthil,

Did you try using getDateCellValue() method, which returns java.util.Date() when you read from excel. Here is snippet of java code to verify if the cell is date formatted and retrieves date cell value. (I did not test this for timestamp)

if(cell.getCellType()==Cell.CELL_TYPE_NUMERIC){
    if (DateUtil.isCellDateFormatted(cell)) {
     System.out.println("date value: "+cell.getDateCellValue());
    }
}

Senthil, you can try getDateValue(cell) function. Additionally you can use the SimpleDateFormat class to change the dateformat of the field that you have read as you need.

In case you have issues with POI and getting the date (I have in the past with early POI Jars), you can convert the numeric value to a Java Calendar - then to a Date if you need that type.

<gel:script
  xmlns:core="jelly:core"
  xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary">

     <core:new var="ExcelDateNumeric" className="java.lang.Integer"> <!-- Excel int for 30-Aug-2018 -->
          <core:arg type="java.lang.String" value="43315" />
     </core:new>

     <core:invokeStatic var="BaseDate" className="java.util.Calendar" method="getInstance" />
     <core:expr value="${BaseDate.set(1899,11,31)}" /> <!-- Excel (by default) uses the 1900 date system -->
     <gel:log>BaseDate = ${BaseDate.getTime()}</gel:log>

     <core:if test="${ExcelDateNumeric > 59}">
          <core:set var="ExcelDateNumeric" value="${ExcelDateNumeric -1}" /> <!-- Leap Year bug -->
     </core:if>

     <core:invoke var="ExcelDate" on="${BaseDate}" method="add">
          <core:arg type="java.lang.Integer" value="5" /> <!-- Calendar.DATE == 5 -->
          <core:arg type="java.lang.Integer" value="${ExcelDateNumeric}" />
     </core:invoke>

     <gel:log>BaseDate = ${BaseDate.getTime()}</gel:log>

</gel:script>

Not as easy as getDateCellValue but just another way,

Gene

Hi Greiff

I am getting an error message "Can't convert Opened to class java.lang.String at org.apache.commons.jelly.tags.core.ArgTag.conver." after implemented the below logic.

<core:new var="ExcelDateNumeric" className="java.lang.Integer"> 
<core:arg type="java.lang.String" value="${creation_dt}" />
</core:new>

Can you please advice how can I fix the problem in the code?

Excel:

Code:

<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">

<gel:setDataSource dbId="niku"/>
<gel:parameter default="C:\Temp" var="P_INCIDENT_TEMP_PATH"/>
<core:set value = "${P_INCIDENT_TEMP_PATH}\test.xls" var = "SourceFile"/>

<!--Input File Start-->

<core:catch var = "fileExp">
<core:new className = "java.io.File" var = "varFileInstance">
<core:arg type = "java.lang.String" value = "${SourceFile}"/>
</core:new>
</core:catch>

<core:if test = "${fileExp!=null}">
<gel:log>Encountered File exception: ${fileExp}</gel:log>
</core:if>

<!--Read Inputstream of the File-->
<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>

<!--Representation of a Input Spreadsheet workbook -->
<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>

<!--Input file read end-->


<!--Output File Start-->


<!--To get First row in Input file-->
<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="${P_INCIDENT_TEMP_PATH}/load_incidents_insert.csv" delimiter="|">

<file:line>
<file:column value="incident_no"/>
<file:column value="status"/>
<file:column value="creation_dt"/>
</file:line>

<!-- FILE DATA LOOP Start -->
<core:while test = "${j &lt; rowCount}">
<!--Read 'j'th row in the input file-->
<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}">

<!--Read 1st Cell-->
<core:invoke method = "getCell" on = "${cur_row}" var = "incident_no">
<core:arg type = "java.lang.Integer" value = "${0}"/>
</core:invoke>

<!--Read 2nd Cell-->
<core:invoke method = "getCell" on = "${cur_row}" var = "status">
<core:arg type = "java.lang.Integer" value = "${1}"/>
</core:invoke>


<!--Read 3rd Cell-->
<core:invoke method = "getCell" on = "${cur_row}" var = "creation_dt">
<core:arg type = "java.lang.Integer" value = "${2}"/>
</core:invoke>

<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 value="${creation_dt.toString().trim().replaceAll('\n', ' ').replaceAll('\r', ' ').replaceAll('\x27', '').replaceAll(',', ' ')}" var="creation_dt"/>
-->


<core:new var="ExcelDateNumeric" className="java.lang.Integer"> <!-- Excel int for 30-Aug-2018 -->
<core:arg type="java.lang.String" value="${creation_dt}" />
</core:new>

<gel:log>Creation Date: ${creation_dt}</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 DATA LOOP END -->
</file:writeFile>

</gel:script>

Thanks

Senthil

lakse01, The error you were getting was because of the header column: "Opened".

I tried using getDateCellValue() as below to read date and writeen same data to a csv file.

    <!--Read 3rd Cell-->
    <core:invoke method="getCell" on="${cur_row}" var="creation_dt">
     <core:arg type="java.lang.Integer" value="${2}"/>
    </core:invoke>
    <core:invoke method="getDateCellValue" on="${creation_dt}" var="crea_dt"/>

Output generated:

incident_no|status|creation_dt
INC_0190526|Resolved|Fri Sep 21 04:02:43 AEST 2018
INC_0190531|Resolved|Fri Sep 21 05:54:14 AEST 2018
INC_0190535|Resolved|Fri Sep 21 07:31:13 AEST 2018
INC_0190540|Closed|Fri Sep 21 07:57:37 AEST 2018

You may have to change the output date format whichever way you want, and I tried testing without the header column*.

<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">
     <gel:setDataSource dbId="niku"/>
     <gel:parameter default="C:\Temp" var="P_INCIDENT_TEMP_PATH"/>
     <core:set value="${P_INCIDENT_TEMP_PATH}/test.xls" var="SourceFile"/>
     <!--Input File Start-->
     <core:catch var="fileExp">
          <core:new className="java.io.File" var="varFileInstance">
               <core:arg type="java.lang.String" value="${SourceFile}"/>
          </core:new>
     </core:catch>
     <core:if test="${fileExp!=null}">
          <gel:log>Encountered File exception: ${fileExp}</gel:log>
     </core:if>
     <gel:log>File: ${SourceFile}</gel:log>
     <!--Read Inputstream of the File-->
     <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>
     <!--Representation of a Input Spreadsheet workbook -->
     <core:new className="org.apache.poi.hssf.usermodel.HSSFWorkbook" var="varxssfwb">
               <core:arg type="java.io.FileInputStream" value="${varFileisInstance}"/>
     </core:new>
     <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="${P_INCIDENT_TEMP_PATH}/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 value="0" var="j"/>
          <core:while test="${j &lt; rowCount}">
               <gel:log>Value: ${j}</gel:log>
               <!--Read 'j'th row in the input file-->
               <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}">
                    <!--Read 1st Cell-->
                    <core:invoke method="getCell" on="${cur_row}" var="incident_no">
                         <core:arg type="java.lang.Integer" value="${0}"/>
                    </core:invoke>
                    <!--Read 2nd Cell-->
                    <core:invoke method="getCell" on="${cur_row}" var="status">
                         <core:arg type="java.lang.Integer" value="${1}"/>
                    </core:invoke>
                    <!--Read 3rd Cell-->
                    <core:invoke method="getCell" on="${cur_row}" var="creation_dt">
                         <core:arg type="java.lang.Integer" value="${2}"/>
                    </core:invoke>
                    <core:invoke method="getDateCellValue" on="${creation_dt}" var="crea_dt"/>
                    
                    <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"/>
                    <gel:log>Creation Date: ${crea_dt}</gel:log>
                    <core:if test="${j != 0}">
                         <gel:log>Row_NUM: ${j}, Incident: ${incident_no}, Creation Date: ${crea_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="${crea_dt == null}">
                                        <file:column value=""/>
                                   </core:when>
                                   <core:otherwise>
                                        <file:column value="${crea_dt}"/>
                                   </core:otherwise>
                              </core:choose>
                         </file:line>
                    </core:if>
               </core:if>
               <core:set value="${j+1}" var="j"/>
          </core:while>
     </file:writeFile>
</gel:script>

In your script creation_dt is a HSSFCell instead of a string which is returned for incident_no and status. 

If you want a java.util.Date from the HSSFCell, Pdesur calling getDateCellValue is the way to go.  Just remember getDateCellValue will return a java.util.Date and not a string.  If you need a string, use SimpleDateFormat to get it into the desired format.

If you want convert it from a numeric, you would just the getNumericCellValue method on the HSSFCell object which should work for the code sample I provided.

V/r,

Gene

Thanks for your help and valuable time. I finally implemented the logic without any issue

Thanks Greiff. I will implement the logic in my process and provide the update