Excel-call

Description

The GreenVulcano^® ESB plugin excell-call allows you to define the information needed to generate Excel reports from a select on the DB.

VulCon / GV Console Configuration

The excel-call Element is used by Channel and routed-call. It has the following attributes:

Attribute	Type	Description
type	fixed	This attribute must assume the value call.
class	fixed	This attribute must assume the value it.greenvulcano.gvesb.virtual.excel.GVExcelCallOperation.
name	required	Operation name. Used in the Flow section to associate workflow nodes to VCL operations.
group	optional	Reports group name.
report	optional	Report name.

and the subelements:

• Description
• GVExcelReport

GVExcelReport

Defines the configuration elements of an Excel report. It is used by excel-call and excelreader-call and has the following attributes:

Attribute	Type	Description
name	required	Report name
group	optional	Report group. Default Generic.
jdbc-connection	required	JNDI name of a DataSource.
format	optional	Name of the style of the report. Default default.

Attribute	Type	Description
mode	optional	Defines the source of the data to be converted in Excel file: sql: the data are extracted from a DB though an SQL statement xml: the data are extracted from an XML document contained into GVBuffer.object field Default to sql.

Its subelements are:

• Description
• Sheet(s)

Sheet

This element defines the configuration of each sheet of the Report.

The Sheet Element is used by GVExcelReport. Its attributes are:

Attribute	Type	Description
id	required	Sheet id.
name	required	Sheet name.
title	optional	Sheet title.
jdbc-connection	optional	JNDI name of a DataSource. Default to Report connection.

Attribute	Type	Description
fields	optional	Useful only for XML mode, defines the list of (row) element's attributes (and their order) to be shown in the Excel sheet.

and its sub-elements:

• Description
• prep-statement
• statement

prep-statement

Preparation statement for Excel report generation. Are executed after the report statement. Can contains placeholders.

Its attributes are:

Attribute	Type	Description
type	required	Must be Excel Sheet: insert|update|callsp. The attribute's admitted values are: select insert update callsp
id	optional	prep-statement Id.

Examples

The following example shows the configuration generated from VulCon^® or GV Console^® when you configure a excel-call operation in SQL mode:

<?xml version="1.0" encoding="UTF-8"?>
<GVSystems name="SYSTEMS" type="module">
    <Systems>
        <System id-system="system-name" system-activation="on">
            <Channel id-channel="CHANNEL_NAME">
                <excel-call class="it.greenvulcano.gvesb.virtual.excel.GVExcelCallOperation"
                            name="listPerson" type="call">
                    <GVExcelReport format="default" group="CREDIT"
                                   jdbc-connection="ds.gv_test" name="listPerson">
                        <Sheet id="1" name="Person List"
                               title="Person List - timestamp{{dd/MM/yyyy HH:mm:ss}}">
                            <statement type="select"><![CDATA[select p.NAME as "Name", p.BIRTHDATE as "Birth Date", c.NAME as "City"
from PERSON p, CITY c
where p.ID_CITY = c.ID
decode{{@{{NAME}}::NULL::::and p.NAME='@{{NAME}}'}}
order by c.NAME, p.NAME]]></statement>
                        </Sheet>
                        <Sheet id="2" name="Credit Card List"
                               title="Credit Card List - timestamp{{dd/MM/yyyy HH:mm:ss}}">
                            <statement type="select"><![CDATA[select p.NAME as "Name", cc.CNUMBER as "Card Number", cc.CREDIT as "Credit", decode(cc.ENABLED, 'Y', 'Yes', 'No') as "Active"
from PERSON p, CREDIT_CARD cc
where p.ID = cc.ID_OWNER
decode{{@{{NAME}}::NULL::::and p.NAME='@{{NAME}}'}}
order by p.NAME]]></statement>
                        </Sheet>
                    </GVExcelReport>
                </excel-call>
            </Channel>    
        </System>
    </Systems>
</GVSystems>

The following example shows the configuration generated from VulCon^® or GV Console^® when you configure a excel-call operation in XML mode:

<?xml version="1.0" encoding="UTF-8"?>
<GVSystems name="SYSTEMS" type="module">
    <Systems>
        <System id-system="system-name" system-activation="on">
            <Channel id-channel="CHANNEL_NAME">
                <excel-call class="it.greenvulcano.gvesb.virtual.excel.GVExcelCallOperation"
                            name="TestXmlExcel" type="call">
                    <GVExcelReport format="default" group="AAA_GRP" mode="xml"
                                   jdbc-connection="something" name="TestXmlExcel">
                        <Sheet id="1" name="Name Sheet 1 of TestXmlExcel" title="Title Sheet 1 of TestXmlExcel"
                               fields="Name,Surname,BirthDate,City">
                            <statement id="1" type="select"><![CDATA[/RowSet/data[@id="0"]/row]]></statement>
                        </Sheet>
                        <Sheet id="2" name="Name Sheet 2 of TestXmlExcel" title="Title Sheet 2 of TestXmlExcel"
                               fields="Name,Surname,CardNumber">
                            <statement id="2" type="select"><![CDATA[/RowSet/data[@id="1"]/row]]></statement>
                        </Sheet>
                    </GVExcelReport>
                </excel-call>
            </Channel>    
        </System>
    </Systems>
</GVSystems>

And the following is a sample input XML:

<?xml version="1.0" encoding="UTF-8"?>
<RowSet>
    <data id="0">
        <row BirthDate="05/02/1980" City="MILANO" Name="ANTONIO" Surname="ROSSI" blablabla="ccmocmso"/>
        <row BirthDate="15/08/1970" City="MILANO" Name="MARIO" Surname="BIANCHI" blablabla="ccmocmso"/>
        <row BirthDate="02/10/1979" City="ROMA" Name="ANCO" Surname="MARZIO" blablabla="ccmocmso"/>
        <row BirthDate="10/07/1984" City="NAPOLI" Name="CIRO" Surname="ESPOSITO" blablabla="ccmocmso"/>
    </data>
    <data id="1">
        <row CardNumber="1111222233338888" Name="ANTONIO" Surname="ROSSI" blablabla="ccmocmso"/>
        <row CardNumber="1234123415555666" Name="MARIO" Surname="BIANCHI" blablabla="ccmocmso"/>
        <row CardNumber="1111222233335555" Name="ANCO" Surname="MARZIO" blablabla="ccmocmso"/>
        <row CardNumber="1111222233336666" Name="CIRO" Surname="ESPOSITO" blablabla="ccmocmso"/>
    </data>
</RowSet>