Import values from Excel into GEL

[Anon Anon]

Hi,

I need to import values from two columns in an Excel sheet stored in a local folder (not in a server) and xog them in.

Could someone suggest the best way of doing this?

Any kind of information on this will be greatly appreciated!

Regards,

Pavithra :)

[Anon Anon]

Write a VBA macro (which runs in Excel locally) - the macro reads data from the XLS, constructs an XML message and XOGs it into Clarity.

(no GEL involved)

 

[Anon Anon]

Thanks, Dave. I will try that one.

Just to give you an overview, why we're doing a GEL import:

There is a custom object/module that Deactivates resources in Clarity.

Basically, you key in the resource name and termination date and hit submit. This kicks off a Process that starts the deactivation.

There are a lot of resources who need to be deactivated. And key-ing these names one by one in the module is a tedious task.

The final objective is to create instances of the process mentioned above, for each resource, by making a GEL input these values through a XOG body.

Regards,

Pavithra

 

[Eugene Greiff]

If you want to used Gel then your best bet is to save the excel as csv and and process it like this script.

https://communities.ca.com/web/ca-clarity-global-user-community/message-board/-/message_boards/view_message/10440580

For native Excel format you would need to create a custom tag that wrap the Apache POI -(Java API for Microsoft Documents) to read the sheet / columns.

V/r,

Gene

[L.Elias]

Hi Pavithra,

     I've recently been thinking of creating a process to do somethig similar to what you mentioned, entering a userid and having a script go on and process the deactivation of the resource. Would you be able to share some info on how you went about it? Ideally I would like the process to replace a resource on any active investments with their role, possible send an action item to the PM to that project to be aware, and set the approriate fields to their resource record.

[Legacy User]

I have been trying to do this also.  I have also been trying to write a formatted Excel file from gel.

The only solution I have found is converting the Excel file to a csv file and then read it in a gel scriot.  Save thing for writing.

[Eugene Greiff]

Well I just wanted to see if this was possible via GEL (I really have a love/hate relationship with this programming language)!

So to write an Excel file via GEL (Java) one needs the Apache POI - the Java API for Microsoft Documents - http://poi.apache.org

So I downloaded the latest version:

http://www.apache.org/dyn/closer.cgi/poi/release/bin/poi-bin-3.10-FINAL-20140208.zip

And it’s dropped the jar and it dependencies

http://poi.apache.org/overview.html#components

Into the Clarity lib folder.

Now for a GEL script that creates an Excel file (reading a file would use the same jar).

<?xml version="1.0" encoding="utf-8"?>
<gel:script xmlns:core="jelly:core"
    xmlns:file="jelly:com.niku.union.gel.FileTagLibrary"
    xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
    xmlns:sql="jelly:sql"
    xmlns:util="jelly:util"
    xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

    <!-- Connecting to Database -->
    <gel:setDataSource dbId="niku" var="clarityDS" />
    <sql:query dataSource="${clarityDS}" var="queryResults">
        <![CDATA[    
select id, table_name, column_name
from cmn_attributes
where ui_type = 'text'
and REFERENCED_OBJECT_ID is not null
]]>
    </sql:query>

    <!-- Row and Column counters -->
    <core:set var="rowNum" value="0" />
    <core:set var="colNum" value="0" />

    <!-- Create a new workbook with a single sheet -->
    <core:new className="org.apache.poi.hssf.usermodel.HSSFWorkbook" var="workBook" />
    <core:invoke var="workSheet" method="createSheet" on="${workBook}">
        <core:arg type="java.lang.String" value="TestOne" />
    </core:invoke>

    <!-- Write out out column names -->
    <core:set var="columnNames" value="${queryResults.getColumnNames()}" />
    <core:invoke var="row" method="createRow" on="${workSheet}">
        <core:arg type="java.lang.Integer" value="${rowNum}" />
    </core:invoke>
    <core:forEach trim="true" items="${columnNames}" var="column" indexVar="i">
        <core:invoke var="cell" method="createCell" on="${row}">
            <core:arg type="java.lang.Integer" value="${i}" />
        </core:invoke>
        <core:invoke var="cell" method="setCellValue" on="${cell}">
            <core:arg type="java.lang.String" value="${column}" />
        </core:invoke>
    </core:forEach>

    <!-- Write out out values -->
    <core:forEach trim="true" items="${queryResults.rowsByIndex}" var="resultRow" indexVar="j">
        <core:invoke var="row" method="createRow" on="${workSheet}">
            <core:arg type="java.lang.Integer" value="${j+1}" />
        </core:invoke>
        <core:forEach trim="true" items="${columnNames}" var="column" indexVar="i">
            <core:invoke var="cell" method="createCell" on="${row}">
                <core:arg type="java.lang.Integer" value="${i}" />
            </core:invoke>
            <core:invoke var="cell" method="setCellValue" on="${cell}">
                <core:arg type="java.lang.String" value="${resultRow[i].toString()}" />
            </core:invoke>
        </core:forEach>
    </core:forEach>

    <!-- Save our Excel File -->
    <core:new className="java.io.FileOutputStream" var="excelFile">
        <core:arg type="java.lang.String" value="TestPoiWrite.xls" />
    </core:new>
    <core:invoke method="write" on="${workBook}">
        <core:arg type="java.io.FileOutputStream" value="${excelFile}" />
    </core:invoke>
    <core:invoke method="close" on="${excelFile}" />
    <core:expr value="${excelFile.close()}" />
</gel:script>

V/r,
Gene