Excelreader-call

From GreenVulcano Wiki
Revision as of 18:43, 2 October 2011 by Anonymous (talk) ({{VULCON}} / {{GVCONSOLE}} Configuration)
Jump to: navigation, search

Definition

Many IT applications, within their business process, need to read an Excel file. Gvvcl-excelreader plug-in helps IT organizations integrating applications that use Excel format to execute their business. Gvvcl-excelreader plug-in is compatible with all Excel formats up to version 2003, and it is very simple to configure. It receives in input a binary stream containing the Excel file, and returns in output a corresponding XML structure. At the output of Gvvcl-excelreader plug-in is possible to apply a ChangeGvBufferNode operation (with OGNL script or an XSL transformation) to retrieve all interested data.

GreenVulcano® ESB provides two different tools, GV Console® and VulCon®, to configure all supported plug-ins in GV services.

VulCon / GV Console Configuration

excelreader-call is the operation that must be configured into VulCon® or Template:GVConsole System section, to convert an Excel file in GVBuffer.object field, in an XML document. In order to add an operation excelreader-call you must define the following fields:

  • class: it.greenvulcano.gvesb.virtual.excel.reader.GVExcelReaderCallOperation (java class that manage excelReader invocation).
  • name: this field identify the operation name that you will use in service definition.
  • onlyData: is a boolean field, it enabled in output cell formatting informations. If true, insert in the XML document also the cell formatting informations.
 Default to true.
  • type: call


The following example shows the configuration generated from VulCon® or Template:GVConsole when you configure an excelReader operation:

<?xml version="1.0" encoding="UTF-8"?>
<GVSystems name="SYSTEMS" type="module">
    <Systems>
        <Description>This section contains a list of all systems connected to GreenVulcano ESB</Description>
        <System id-system="system-name" system-activation="on">
            <Channel id-channel="CHANNEL_NAME">
                <excelreader-call class="it.greenvulcano.gvesb.virtual.excel.reader.GVExcelReaderCallOperation"
                    name="ReadExcelInput" type="call" onlyData="true"/>
            </Channel>    
        </System>
    </Systems>
</GVSystems>
</syntaxhighlight


To use an ''excelreader-call'' in a {{GVESB}} service, you need to perfom the following steps:
[[File:Service-GVvcl-excelGvConsole.png|right|thumb|{{GVCONSOLE}} service excel-reader configuration ]]
* In {{L_GVCONSOLE}} [[Service section ({{GVCONSOLE}})|Service section]] select the services that you want to modify or, alternatively, you can create a new Services
* On flow tag, click the add operation node button and select "insert before" 
* On the list of GreenVulcano node select ''GvOperationNode''
* On the template that appear, select from drop down list, ''input'' field, ''next-node-id'' and as ''operation-name'' the operation configured in the system. Fill in fields id and output.
* Click ''save button and terminate editing'' 
* [[Reload configuration ({{GVCONSOLE}})|Reload configuration]] from {{L_GVCONSOLE}}

At this point you have configured a service with an ''excelreader'' operation.

==Example==
This example shows an XML document generated by a simple Excel sheet (without cols and rows grouping).
Given an Excel sheet with the following structure:
{| class="gvtable"
|-
! ID1
! ID2
! ID3
! ID4
! ID5
! ID6
! ID7
|-
| 23
| 23232
| 23
| 23333
| 1
| 2
| 3
|-
| 24
| 24444
| 23
| 23332
| 1
| 2
| 3
|}
''excelreader-call'' generates the following XML:

<syntaxhighlight lang="XML"><?xml version="1.0" encoding="UTF-8"?>
<workbook>
    <sheet number="0">
        <name><![CDATA[Foglio1]]></name>
        <row number="0">
            <col number="0">
                <data><![CDATA[ID1]]></data>
            </col>
            <col number="1">
                <data><![CDATA[ID2]]></data>
            </col>
            <col number="2">
                <data><![CDATA[ID3]]></data>
            </col>
            <col number="3">
                <data><![CDATA[ID4]]></data>
            </col>
            <col number="4">
                <data><![CDATA[ID5]]></data>
            </col>
            <col number="5">
                <data><![CDATA[ID6]]></data>
            </col>
            <col number="6">
                <data><![CDATA[ID7]]></data>
            </col>
        </row>
        <row number="1">
            <col number="0">
                <data><![CDATA[23]]></data>
            </col>
            <col number="1">
                <data><![CDATA[23232]]></data>
            </col>
            <col number="2">
                <data><![CDATA[23]]></data>
            </col>
            <col number="3">
                <data><![CDATA[711]]></data>
            </col>
            <col number="4">
                <data><![CDATA[1]]></data>
            </col>
            <col number="5">
                <data><![CDATA[1]]></data>
            </col>
            <col number="6">
                <data><![CDATA[1]]></data>
            </col>
        </row>
        <row number="2">
            <col number="0">
                <data><![CDATA[23]]></data>
            </col>
            <col number="1">
                <data><![CDATA[711]]></data>
            </col>
            <col number="2">
                <data><![CDATA[23]]></data>
            </col>
            <col number="3">
                <data><![CDATA[23232]]></data>
            </col>
            <col number="4">
                <data><![CDATA[1]]></data>
            </col>
            <col number="5">
                <data><![CDATA[1]]></data>
            </col>
            <col number="6">
                <data><![CDATA[1]]></data>
            </col>
        </row>
    </sheet>
</workbook>

With a ChangeGvBufferNode is possible parsing XML and retrieve any tag and value.