AnsweredAssumed Answered

Excel Tag Library

Question asked by gcubed Champion on Mar 10, 2014
Latest reply on Jun 9, 2017 by Viraj Khara

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

Outcomes