Difference between revisions of "Excel-call"
(→prep-statement) |
|||
(2 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
Contents
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:
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:
|
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>