Difference between revisions of "Excel-call"

From GreenVulcano Wiki
Jump to: navigation, search
(→‎prep-statement)
 
(5 intermediate revisions by 2 users not shown)
Line 38: Line 38:
 
| format || optional || Name of the style of the report. Default ''default''.
 
| format || optional || Name of the style of the report. Default ''default''.
 
|}
 
|}
 +
 +
<div class="version_ge3.4.0.10">
 +
{|class="gvtable"
 +
! 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.
 +
|}
 +
</div>
  
 
Its subelements are:
 
Its subelements are:
Line 59: Line 70:
 
| jdbc-connection || optional || JNDI name of a DataSource. Default to Report connection.
 
| jdbc-connection || optional || JNDI name of a DataSource. Default to Report connection.
 
|}
 
|}
 +
 +
<div class="version_ge3.4.0.10">
 +
{|class="gvtable"
 +
! 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.
 +
|}
 +
</div>
  
 
and its sub-elements:
 
and its sub-elements:
Line 73: Line 92:
 
! Attribute !! Type !! Description
 
! Attribute !! Type !! Description
 
|-
 
|-
−
| type || required || Must be 'Excel Sheet: insert|update|callsp'. The attribute's admitted values are:
+
| type || required ||  
 +
Must be '''Excel Sheet: insert|update|callsp'''. The attribute's admitted values are:
 
* select
 
* select
 
* insert
 
* insert
Line 81: Line 101:
 
| id || optional || prep-statement Id.
 
| id || optional || prep-statement Id.
 
|}
 
|}
 +
 +
==Examples==
 +
The following example shows the configuration generated from {{L_VULCON}} or {{L_GVCONSOLE}} when you configure a [[excel-call]] operation in SQL mode:
 +
 +
<syntaxhighlight lang="XML"><?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>
 +
</syntaxhighlight>
 +
 +
 +
The following example shows the configuration generated from {{L_VULCON}} or {{L_GVCONSOLE}} when you configure a [[excel-call]] operation in XML mode:
 +
 +
<syntaxhighlight lang="XML"><?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>
 +
</syntaxhighlight>
 +
And the following is a sample input XML:
 +
<syntaxhighlight lang="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>
 +
</syntaxhighlight>

Latest revision as of 20:51, 29 October 2015

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:

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>
Retrieved from "https://gvwiki.greenvulcano.com/gvwiki/index.php?title=Excel-call&oldid=4452"