AnsweredAssumed Answered

How to Export Query Data to Excel

Question asked by JayaSantoshKumar on Jan 12, 2016
Latest reply on Jan 14, 2016 by nick_darlington

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.

Outcomes