Clarity

  • Private Community
Back to discussions
Expand all | Collapse all

Excel Tag Library

  • 1.  Excel Tag Library

    Posted Mar 10, 2014 04:46 PM

    This post got me thinking about a simple Excel tag library.  
    https://communities.ca.com/web/ca-clarity-global-user-community/message-board/-/message_boards/message/109990261?&#p_19

    We seem to use Excel formatted document in our business processes and I am writing custom Excel formatted reports outside the system for most of these requirements.

    So a coded up a simple library that has the following tags (more work to be done – just a proof of concept at the moment) which would allow anyone to Gel an Excel file:

            registerTag("open", XlsOpenTag.class);
            registerTag("sheet", XlsSheetTag.class);
            registerTag("cell", XlsCellTag.class);
            registerTag("save", XlsSaveTag.class);

    So to use the tags it goes something like this:

    <gel:script
            xmlns:core="jelly:core"
            xmlns:debug="jelly:com.ggreiff.gel.DebugTagLibrary"
            xmlns:xls="jelly:com.ggreiff.gel.XlsTagLibrary"
            xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
            xmlns:soap="jelly:com.niku.union.gel.SOAPTagLibrary"
            xmlns:util="jelly:util"
            xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
            xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

        <!-- simple core set -->
        <core:set value="TestOne" var="TestOne" />
        <gel:log>${testOne}</gel:log>

        <!-- write a simple excel file -->
        <xls:open var="xlsWorkBook" />
        <xls:sheet workBook="${xlsWorkBook}" sheetName="xlsSheet" var="xlsSheet" />
        <xls:cell sheet="${xlsSheet}" row="10" column="4" value="This is a test"/>
        <xls:save fileName="test.xls" overWrite="yes" workBook="${xlsWorkBook}"/>

        <!-- ide break -->
        <debug:break />
    </gel:script>

    This script produced:



    <xls:open> either opens a new workbook or and existing if given the fileName attribute.
    <xls:sheet> either creates a new worksheet or opens and existing worksheet based on  the sheetName attribute.
    <xls:cell> writes or reads the value of a cell based on it row / column idex.  If the value attribute is supply then it writes .
    <xls:save> saves the file.  If it exists then the overwrite attribute determines if the file is overwritten.

    I am going to add some feature sets around the cell:

    format  / merge cells
    enter a correct data type
    enter a formula instead of data
    …

    If there is any interest in this type of TagLib, I will post it to the document section after I clean it up.

    V/r,
    Gene



  • 2.  RE: Excel Tag Library

    Posted Mar 11, 2014 03:24 PM

    You have successfully intrigued my interest!
    I was a java major in college and since Clarity in java based I nerd out when somebody does stuff like you are!
    Your way will be a great way to create some excel documents quickly.

    This is when I began to think of another way somebody could do this.  Since we also have on demand clients, I like to try to make things cross compatible for onDemand/onPremise.

    Clarity uses Apache POI, which is Java's api for Microsoft Documents.  This is how they create all exports to microsoft doc's.

    Using only what Clarity gives you on initial install (works for onDemand) I have figured out a way to create excel docs just as Clarity does.

    This example will end up looking like this:

    This is the EXACT output that the script below sends you.

    Things to note: A & B, C & D are merged.  Total and Average fields are formulas and will come over as such.

    NOTICE: This is just a proof of concept, nothing more.  I have not optimized it completely yet.  You will notice i do things one way for some things, and another for others.  This is because I try different things to see what works best.

    This is my first attempt at using this library so sorry if its hard to understand.

    <gel:script xmlns:core="jelly:core"
                xmlns:util="jelly:util"
                xmlns:x="jelly:xml"
                xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
                xmlns:sql="jelly:sql"
                xmlns:soap="jelly:com.niku.union.gel.SOAPTagLibrary"
                xmlns:xog="http://www.niku.com/xog"
                xmlns:soap-env="http://schemas.xmlsoap.org/soap/envelope/"
                xmlns:email="jelly:email"
                xmlns:file="jelly:com.niku.union.gel.FileTagLibrary">
        <!--Create Workbook-->
        <core:new className="org.apache.poi.hssf.usermodel.HSSFWorkbook" var="wb"/>
        
        <!--Create Sheet-->
        <core:invoke method="createSheet" on="${wb}" var="sheet">
            <core:arg type="java.lang.String" value="First Sheet"/>
        </core:invoke>
        
        <!--Create 7 Rows-->
        <core:set value="0" var="rowNum"/>
        <core:while test="${rowNum!=7}">
            <core:invoke method="createRow" on="${sheet}" var="row${rowNum+1}">
                <core:arg type="java.lang.Integer" value="${rowNum}"/>
            </core:invoke>
            <core:set value="${rowNum+1}" var="rowNum"/>
        </core:while>
        
        <!--Create Bold Style-->
        <core:invoke method="createCellStyle" on="${wb}" var="style"/>
        <core:invoke method="createFont" on="${wb}" var="font"/>
        
        <core:invoke method="setBoldweight" on="${font}">
            <core:arg type="java.lang.Short" value="700"/>
        </core:invoke>
        
        <core:invoke method="setFont" on="${style}">
            <core:arg type="org.apache.poi.hssf.usermodel.HSSFFont" value="${font}"/>
        </core:invoke>    

        <!--Create Row1 Col1-->
        <core:invoke method="createCell" on="${row1}" var="r1c1">
            <core:arg type="java.lang.Integer" value="0"/>
        </core:invoke>
        <core:invoke method="setCellValue" on="${r1c1}">
            <core:arg type="java.lang.String" value="Resource Name"/>
        </core:invoke>
        <!--Bold Row1 Col1-->
        <core:invoke method="setCellStyle" on="${r1c1}">
            <core:arg type="org.apache.poi.hssf.usermodel.HSSFCellStyle" value="${style}"/>
        </core:invoke>
        
        <!--Merge A and B in Row 1-->
        <!--rowFrom,rowTo,colFrom,colTo-->
        <core:new className="org.apache.poi.hssf.util.CellRangeAddress" var="cellRangeAddress1">
            <core:arg type="java.lang.Integer" value="0"/>
            <core:arg type="java.lang.Integer" value="0"/>
            <core:arg type="java.lang.Integer" value="0"/>
            <core:arg type="java.lang.Integer" value="1"/>
        </core:new>
        
        <core:invoke method="addMergedRegion" on="${sheet}">
            <core:arg type="org.apache.poi.hssf.util.CellRangeAddress" value="${cellRangeAddress1}"/>
        </core:invoke>
        
        <!--Create Row1 Col3-->
        <core:invoke method="createCell" on="${row1}" var="r1c3">
            <core:arg type="java.lang.Integer" value="2"/>
        </core:invoke>
        <core:invoke method="setCellValue" on="${r1c3}">
            <core:arg type="java.lang.String" value="Salary"/>
        </core:invoke>
        <!--Bold Row1 Col3-->
        <core:invoke method="setCellStyle" on="${r1c3}">
            <core:arg type="org.apache.poi.hssf.usermodel.HSSFCellStyle" value="${style}"/>
        </core:invoke>
        
        <!--Merge C and D in Row 1-->
        <!--rowFrom,rowTo,colFrom,colTo-->
        <core:new className="org.apache.poi.hssf.util.CellRangeAddress" var="cellRangeAddress2">
            <core:arg type="java.lang.Integer" value="0"/>
            <core:arg type="java.lang.Integer" value="0"/>
            <core:arg type="java.lang.Integer" value="2"/>
            <core:arg type="java.lang.Integer" value="3"/>
        </core:new>
        
        <core:invoke method="addMergedRegion" on="${sheet}">
            <core:arg type="org.apache.poi.hssf.util.CellRangeAddress" value="${cellRangeAddress2}"/>
        </core:invoke>
        
        <!--Create Second Row: First, Last, 2013,2014-->
        <core:invoke method="createCell" on="${row2}" var="r2c1">
            <core:arg type="java.lang.Integer" value="0"/>
        </core:invoke>
        <core:invoke method="setCellValue" on="${r2c1}">
            <core:arg type="java.lang.String" value="First"/>
        </core:invoke>
        <core:invoke method="setCellStyle" on="${r2c1}">
            <core:arg type="org.apache.poi.hssf.usermodel.HSSFCellStyle" value="${style}"/>
        </core:invoke>
        <core:invoke method="createCell" on="${row2}" var="r2c2">
            <core:arg type="java.lang.Integer" value="1"/>
        </core:invoke>
        <core:invoke method="setCellValue" on="${r2c2}">
            <core:arg type="java.lang.String" value="Last"/>
        </core:invoke>
        <core:invoke method="setCellStyle" on="${r2c2}">
            <core:arg type="org.apache.poi.hssf.usermodel.HSSFCellStyle" value="${style}"/>
        </core:invoke>
        <core:invoke method="createCell" on="${row2}" var="r2c3">
            <core:arg type="java.lang.Integer" value="2"/>
        </core:invoke>
        <core:invoke method="setCellValue" on="${r2c3}">
            <core:arg type="java.lang.String" value="2013"/>
        </core:invoke>
        <core:invoke method="setCellStyle" on="${r2c3}">
            <core:arg type="org.apache.poi.hssf.usermodel.HSSFCellStyle" value="${style}"/>
        </core:invoke>
        <core:invoke method="createCell" on="${row2}" var="r2c4">
            <core:arg type="java.lang.Integer" value="3"/>
        </core:invoke>
        <core:invoke method="setCellValue" on="${r2c4}">
            <core:arg type="java.lang.String" value="2014"/>
        </core:invoke>
        <core:invoke method="setCellStyle" on="${r2c4}">
            <core:arg type="org.apache.poi.hssf.usermodel.HSSFCellStyle" value="${style}"/>
        </core:invoke>
        
        <!--Peter Griffin Row-->
        <core:invoke method="split" on="Peter,Griffin,87000,90000" var="peter">
            <core:arg type="java.lang.String" value=","/>
        </core:invoke>
        <core:set value="0" var="peterRow"/>
        <core:forEach items="${peter}" var="peterPart">    
            <core:invoke method="createCell" on="${row3}" var="r">
                <core:arg type="java.lang.Integer" value="${peterRow}"/>
            </core:invoke>
            <core:if test="${peterPart.matches(&#34;^[0-9]+$&#34;) == true}">
                <core:invokeStatic className="java.lang.Integer" method="parseInt" var="cellNumeric">
                    <core:arg type="java.lang.String" value="${peterPart}"/>
                </core:invokeStatic>
                <core:set value="${r.setCellValue(cellNumeric)}" var="setCell"/>
            </core:if>
            <core:if test="${peterPart.matches(&#34;^[0-9]+$&#34;) != true}">
                <core:set value="${r.setCellValue(peterPart)}" var="setCell"/>
            </core:if>
            <core:set value="${peterRow+1}" var="peterRow"/>
        </core:forEach>
        
        <!--Lois Griffin Row-->
        <core:invoke method="split" on="Lois,Griffin,60000,65000" var="lois">
            <core:arg type="java.lang.String" value=","/>
        </core:invoke>
        <core:set value="0" var="loisRow"/>
        <core:forEach items="${lois}" var="loisPart">    
            <core:invoke method="createCell" on="${row4}" var="r">
                <core:arg type="java.lang.Integer" value="${loisRow}"/>
            </core:invoke>
            
            <core:if test="${loisPart.matches(&#34;^[0-9]+$&#34;) == true}">
                <core:invokeStatic className="java.lang.Integer" method="parseInt" var="cellNumeric">
                    <core:arg type="java.lang.String" value="${loisPart}"/>
                </core:invokeStatic>
                <core:set value="${r.setCellValue(cellNumeric)}" var="setCell"/>
            </core:if>
            <core:if test="${loisPart.matches(&#34;^[0-9]+$&#34;) != true}">
                <core:set value="${r.setCellValue(loisPart)}" var="setCell"/>
            </core:if>
            <core:set value="${loisRow+1}" var="loisRow"/>
        </core:forEach>
        
        <!--Chris Griffin Row-->
        <core:invoke method="split" on="Chris,Griffin,5000,5250" var="chris">
            <core:arg type="java.lang.String" value=","/>
        </core:invoke>
        <core:set value="0" var="chrisRow"/>
        <core:forEach items="${chris}" var="chrisPart">    
            <core:invoke method="createCell" on="${row5}" var="r">
                <core:arg type="java.lang.Integer" value="${chrisRow}"/>
            </core:invoke>
            <core:if test="${chrisPart.matches(&#34;^[0-9]+$&#34;) == true}">
                <core:invokeStatic className="java.lang.Integer" method="parseInt" var="cellNumeric">
                    <core:arg type="java.lang.String" value="${chrisPart}"/>
                </core:invokeStatic>
                <core:set value="${r.setCellValue(cellNumeric)}" var="setCell"/>
            </core:if>
            <core:if test="${chrisPart.matches(&#34;^[0-9]+$&#34;) != true}">
                <core:set value="${r.setCellValue(chrisPart)}" var="setCell"/>
            </core:if>
            <core:set value="${chrisRow+1}" var="chrisRow"/>
        </core:forEach>
        
        <!--Create Total Label-->
        <core:invoke method="createCell" on="${row6}" var="r1c1">
            <core:arg type="java.lang.Integer" value="1"/>
        </core:invoke>
        <core:invoke method="setCellValue" on="${r1c1}">
            <core:arg type="java.lang.String" value="Total"/>
        </core:invoke>
        <!--Bold Total Label-->
        <core:invoke method="setCellStyle" on="${r1c1}">
            <core:arg type="org.apache.poi.hssf.usermodel.HSSFCellStyle" value="${style}"/>
        </core:invoke>
        <!-- Create Total Formula Cell 2013-->
        <core:invoke method="createCell" on="${row6}" var="r6c2">
            <core:arg type="java.lang.Integer" value="2"/>
        </core:invoke>
        <core:invoke method="setCellType" on="${r6c2}">
            <core:arg type="java.lang.Integer" value="2"/>
        </core:invoke>        
        <core:invoke method="setCellFormula" on="${r6c2}">
            <core:arg type="java.lang.String" value="SUM(C3:C5)"/>
        </core:invoke>    
        <!-- Create Total Formula Cell 2014-->
        <core:invoke method="createCell" on="${row6}" var="r6c3">
            <core:arg type="java.lang.Integer" value="3"/>
        </core:invoke>
        <core:invoke method="setCellType" on="${r6c3}">
            <core:arg type="java.lang.Integer" value="2"/>
        </core:invoke>        
        <core:invoke method="setCellFormula" on="${r6c3}">
            <core:arg type="java.lang.String" value="SUM(D3:D5)"/>
        </core:invoke>
        
        <!--Create Average Label-->
        <core:invoke method="createCell" on="${row7}" var="r1c1">
            <core:arg type="java.lang.Integer" value="1"/>
        </core:invoke>
        <core:invoke method="setCellValue" on="${r1c1}">
            <core:arg type="java.lang.String" value="Average"/>
        </core:invoke>
        <!--Bold Total Label-->
        <core:invoke method="setCellStyle" on="${r1c1}">
            <core:arg type="org.apache.poi.hssf.usermodel.HSSFCellStyle" value="${style}"/>
        </core:invoke>
        <!-- Create Average Formula Cell 2013-->
        <core:invoke method="createCell" on="${row7}" var="r7c2">
            <core:arg type="java.lang.Integer" value="2"/>
        </core:invoke>
        <core:invoke method="setCellType" on="${r7c2}">
            <core:arg type="java.lang.Integer" value="2"/>
        </core:invoke>        
        <core:invoke method="setCellFormula" on="${r7c2}">
            <core:arg type="java.lang.String" value="AVERAGE(C3:C5)"/>
        </core:invoke>
        <!-- Create Average Formula Cell 2014-->
        <core:invoke method="createCell" on="${row7}" var="r7c3">
            <core:arg type="java.lang.Integer" value="3"/>
        </core:invoke>
        <core:invoke method="setCellType" on="${r7c3}">
            <core:arg type="java.lang.Integer" value="2"/>
        </core:invoke>        
        <core:invoke method="setCellFormula" on="${r7c3}">
            <core:arg type="java.lang.String" value="AVERAGE(D3:D5)"/>
        </core:invoke>
        
        
        <!--Find Niku Home Location-->
        <core:invokeStatic className="java.lang.System" method="getenv" var="NIKU_HOME">
            <core:arg value="NIKU_HOME"/>
        </core:invokeStatic>    
        
        <!--Create Output Stream To File-->
        <core:new className="java.io.FileOutputStream" var="fileOut">
            <core:arg type="java.lang.String" value="${NIKU_HOME}/Files/createExcel.xls"/>
        </core:new>
        <!--Write Workbook To File Using Output Stream-->
        <core:invoke method="write" on="${wb}">
            <core:arg type="java.io.FileOutputStream" value="${fileOut}"/>
        </core:invoke>
        <!--Close File Output Stream-->
        <core:invoke method="close" on="${fileOut}"/>
        
    <!--Change Save Folder to one on your server that your allowed to use...-->
    <email:email to="email@email.com" from="clarity@emil.com" subject="Excel File Attached." server="PUT_SMTP_SERVER_HERE" attach="${NIKU_HOME}/Files/createExcel.xls">
    Excel Document Attached.        
    </email:email>
    </gel:script>

     

    Hope you find this as fun/interesting as I do!

    ~Chris



  • 3.  RE: Excel Tag Library

    Posted Mar 11, 2014 03:48 PM

     

    My thoughts are to create enough tags to produce output like you have generated but limit the complexity to the Gel Script developer.

    For tags I think:

    A tag will handle both new and update within the same definition.

    A tag will handle API requirements i.e. <xls:Cell> handles creating rows if needed.

    A tag will return the underlining object so a developer has access to other methods not exposed via the tag e.g.

    <xls:cell sheet="${xlsSheet}" row="10" column="4" value="This is a test" var="xlsCell" />
<core:expr value="${xlsCell.setCellFormula("AVERAGE(D3:D5)"}/>

     

    Just thoughts,

    Gene

     



  • 4.  RE: Excel Tag Library

    Posted Mar 17, 2014 09:30 AM

    Hi,

    You both seems to be genius smiley

    i am also interested in such kind of thing where i need to build a excel report based on the data from clarity.

    As this is new, i am just starting with the creating of excel file first but getting error in the library files.

    Do i need to include anything in jar or lib folder?



  • 5.  RE: Excel Tag Library

    Posted Mar 17, 2014 05:08 PM

    Which way are you trying to do it, Gene's or My way?

    Gene's way will need external library files once it is completed.  Will be a really cool way to do it.  Gene is one smart cookie. :)

    If you are doing it my way, i did it on a 13.2 environment.  I assume most(if not all) Clarity versions have Apache POI included OOTB though.



  • 6.  RE: Excel Tag Library

    Posted Mar 18, 2014 03:03 AM

    Hi,

    I am doing it in the 13.3 and using your way for the excel tag.

    But script is unable to identiy the library files which i have included in the script.



  • 7.  Re: Excel Tag Library

    Posted Jun 01, 2017 03:37 AM

    Hi Chris,

     

    I am trying to add border to the cell. I am facing unknown error. Can you help me with that



  • 8.  Re: Excel Tag Library

    Posted Jun 05, 2017 11:06 AM

    This works for me.

     

        <!-- Create Bold Style
              With POI version 3.17 one will need to use the BorderStyle instead of the short
              <core:getStatic var="mediumBorderStyle" className="org.apache.poi.ss.usermodel.BorderStyle" field="MEDIUM"/>
         -->
        <core:invoke method="createCellStyle" on="${wb}" var="style"/>
         <core:invoke method="createCellStyle" on="${wb}" var="styleBoxed"/>
        
         <core:invoke method="setBorderTop" on="${styleBoxed}">
              <core:arg type="java.lang.Short" value="2"/>
        </core:invoke>
              <core:invoke method="setBorderBottom" on="${styleBoxed}">
              <core:arg type="java.lang.Short" value="2"/>
        </core:invoke>
              <core:invoke method="setBorderRight" on="${styleBoxed}">
              <core:arg type="java.lang.Short" value="2"/>
        </core:invoke>
              <core:invoke method="setBorderLeft" on="${styleBoxed}">
              <core:arg type="java.lang.Short" value="2"/>
        </core:invoke>
        
         <!-- Apply the style to the cell -->
         <core:invoke method="setCellStyle" on="${r7c3}">
            <core:arg type="org.apache.poi.hssf.usermodel.HSSFCellStyle" value="${styleBoxed}"/>
        </core:invoke>

     

     

    You can find the boarder styles here: Constant Field Values (POI API Documentation) 

     

     

    V/r,

    Gene



  • 9.  Re: Excel Tag Library

    Posted Jun 05, 2017 12:20 PM

    Well done to all of you, I think I am going to be doing some playing tonight.

     

    BTW: You made me feel old. When I was in college I majored in Software Development in Pascal, C, ADA, and 8088 assembly! Actually I do not feel that old.



  • 10.  Re: Excel Tag Library

    Posted Jun 05, 2017 12:39 PM

    I started with Fortran with on a CDC 6600 with cards. 

     

    I also worked on a PDP 8 with 4K of actual core memory.

     

    Showing my age,

    Gene



  • 11.  Re: Excel Tag Library

    Posted Jun 09, 2017 03:12 AM

    Thanks Gene,

     

    I have used another constructor of that class to do so. But i can try this as well (As it seems to be bit easier)



  • 12.  Re: Excel Tag Library

    Posted Apr 29, 2019 05:44 AM

    Hello All,

     

    Just a snippet to create a date style in the Workbook :

    <!-- Date Style -->
    <j:invoke method="createCellStyle" on="${v_workbook}" var="v_style_date"/>
    <j:invoke method="getCreationHelper" on="${v_workbook}" var="v_CreationHelper"/>
    <j:invoke method="setDataFormat" on="${v_style_date}">
     <j:arg type="java.lang.Short" value="${v_CreationHelper.createDataFormat().getFormat('dd/mm/yyyy hh:mm')}"/>
    </j:invoke>

    And to apply the style to a cell :

    <j:invoke method="setCellValue" on="${v_cell}">
     <j:arg type="java.util.Date" value="${v_sql_exec_row[j]}"/>
    </j:invoke>
    <j:invoke method="setCellStyle" on="${v_cell}">
     <j:arg type="org.apache.poi.ss.usermodel.CellStyle" value="${v_style_date}"/>
    </j:invoke>

    Works fine for me.

     

    BTW, does anybody know how to determine the data type of a column in a dataset returned by sql:query ?

    Right now, I'm testing the name of the column and writing the value accordingly (Date, Number or String) :

    <!-- SQL -->
    <s:query escapeText="false" var="v_sql_exec">
     <![CDATA[...]]>
    </s:query>
    <!-- Parse Rows -->
    <j:forEach items="${v_sql_exec.rowsByIndex}" indexVar="i" var="v_sql_exec_row">
     <!-- New Row -->
     <j:invoke method="createRow" on="${v_sheet}" var="v_row">
     <j:arg type="java.lang.Integer" value="${v_row_number}"/>
     </j:invoke>
     <j:set value="${v_row_number+1}" var="v_row_number" />
     <!-- Parse Columns -->
     <j:forEach items="${v_sql_exec.columnNames}" indexVar="j" var="v_sql_exec_column">
     <j:if test="${v_sql_exec_row[j] != null}">
     <j:invoke method="createCell" on="${v_row}" var="v_cell">
     <j:arg type="java.lang.Integer" value="${j}"/>
     </j:invoke>
     <j:choose>
     <!-- Date -->
     <j:when test="${v_sql_exec_column == 'COLUMN_TYPE_DATE'}">
     <j:invoke method="setCellValue" on="${v_cell}">
     <j:arg type="java.util.Date" value="${v_sql_exec_row[j]}"/>
     </j:invoke>
     <j:invoke method="setCellStyle" on="${v_cell}">
     <j:arg type="org.apache.poi.ss.usermodel.CellStyle" value="${v_style_date}"/>
     </j:invoke>
     </j:when>
     <!-- Number -->
     <j:when test="${v_sql_exec_column == 'COLUMN_TYPE_NUMBER'}">
     <j:invoke method="setCellValue" on="${v_cell}">
     <j:arg type="java.lang.Double" value="${v_sql_exec_row[j]}"/>
     </j:invoke>
     </j:when>
     <!-- String, Default -->
     <j:otherwise>
     <j:invoke method="setCellValue" on="${v_cell}">
     <j:arg type="java.lang.String" value="${v_sql_exec_row[j].toString()}"/>
     </j:invoke>
     </j:otherwise>
     </j:choose>
     </j:if>
     </j:forEach>
    </j:forEach>

     

    In Oracle, I could use the package DBMS_SQL but is there a simpler way to analyze the query ?

     

    Thx,

    David

     

    PS : Thx gcubed for your post, just love it !



  • 13.  Re: Excel Tag Library

    Posted Apr 29, 2019 11:14 AM

    Try 

         <j:set value="${v_sql_exec_row[j].getClass()" var="ClassName" />
         <j:set value="${v_sql_exec_row[j].getType()"  var="TypeName"  />

     

    I suspect that getClass will return java.lang.Object as the column values are an Object[],  but getType() might return the jdbc data type.


    V/r,

    Gene



  • 14.  Re: Excel Tag Library

    Posted Apr 29, 2019 12:05 PM

    Many Thanks !

     

    getType returns null.

    getClass returns :

    • class java.math.BigDecimal or class java.lang.Double for numbers
    • class java.sql.Timestamp for dates
    • class java.lang.String for strings

     

    And the script to parse columns

    Tip : Do not use core:switch, it does not work correctly when there is inner space in strings.

    <!-- Parse Columns -->
    <j:forEach items="${v_sql_exec.columnNames}" indexVar="j" var="v_sql_exec_column">
     <j:if test="${v_sql_exec_row[j] != null}">
     <j:invoke method="createCell" on="${v_row}" var="v_cell">
     <j:arg type="java.lang.Integer" value="${j}"/>
     </j:invoke>
     <!-- Data Type -->
     <j:choose>
     <!-- Date -->
     <j:when test="${v_sql_exec_row[j].getClass() == 'class java.sql.Timestamp'}">
     <j:invoke method="setCellValue" on="${v_cell}">
     <j:arg type="java.util.Date" value="${v_sql_exec_row[j]}"/>
     </j:invoke>
     <j:invoke method="setCellStyle" on="${v_cell}">
     <j:arg type="org.apache.poi.ss.usermodel.CellStyle" value="${v_style_date}"/>
     </j:invoke>
     </j:when>
     <!-- Number -->
     <j:when test="${v_sql_exec_row[j].getClass() == 'class java.math.BigDecimal' or v_sql_exec_row[j].getClass() == 'class java.lang.Double'}">
     <j:invoke method="setCellValue" on="${v_cell}">
     <j:arg type="java.lang.Double" value="${v_sql_exec_row[j]}"/>
     </j:invoke>
     <j:invoke method="setCellStyle" on="${v_cell}">
     <j:arg type="org.apache.poi.ss.usermodel.CellStyle" value="${v_style_number}"/>
     </j:invoke>
     </j:when>
     <!-- String, Default -->
     <j:otherwise>
     <j:invoke method="setCellValue" on="${v_cell}">
     <j:arg type="java.lang.String" value="${v_sql_exec_row[j].toString()}"/>
     </j:invoke>
     </j:otherwise>
     </j:choose>
     </j:if>
    </j:forEach>

     

    Regards,

    David



  • 15.  Re: Excel Tag Library

    Posted May 02, 2019 01:09 PM

    To pull off just the class name -- removing the namespace.

    <?xml version="1.0" encoding="utf-8"?>
    <gel:script xmlns:core="jelly:core"
         xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary">
         
         <core:set value='class java.math.BigDecimal' var="testFive" />
         <gel:log>testFive   = ${testFive}</gel:log>
         
         <core:set value='${testFive.split("\.")}' var="testSix" />
         <gel:log>testSix   = ${testSix[testSix.size()-1]}</gel:log>
              
    </gel:script>

     

     

    Would allow you to use the switch tag.

     

    Just playing around,

    Gene