Clarity

Hi  All,

I'm trying to push the Query output to the excel Sheet after running the code it is showing Error at the Class which im trying to Load .

I got to know that class is supported for Excel 2003

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

    <sql:setDataSource url="jdbc:oracle:thin:@hostname:xxxx:niku"

driver="oracle.jdbc.driver.OracleDriver"

user="aaaa"

password="aaaa"

     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>

It is showing error Here:<core:new> org.apache.poi.hssf.usermodel.HSSFWorkbook

Can any one help me out from this problem

Thanks All.

Any particular reason you're not exporting it as a CSV file ? Because that would be much much easier.

Reason I'm Exporting is there are few situations where we would be using that Excel in loading data to DB.

Okay Even I dont know how to export to CSV File ,could you or any one please show me that how to do it.

Thanks.

Using things like core:new will quickly get you into trouble/difficulty.

Not all classes/methods are thread-safe.

You lack the ability (in a scripted languages like GEL) to wrap algorithms and methods with a sync in order to lock and serialize access to those objects as would be needed in raw java.

You build a dependency on something that may be replaced or updated in future causing your GEL script to break.

Regarding reading/writing to CSV files, you might consider the following links although I think the preferred methods seem to be moving the execution into Excel itself and use VB code and macros to extract data out of Clarity and then manipulate it:

Gel Script: How to Generate a CSV

Gel script -Reading csv file and passing the o/p  to sql script

(There may be more too, these ones just jumped out first).