Wiki source code of XMLToXML

Version 11.1 by Achraf El Kari on 2021/04/27 13:16
Show last authors
1 {{box cssClass="floatinginfobox" title="**Contents**"}}
2 {{toc/}}
3 {{/box}}
4
5 = I- Introduction =
6
7 (((
8 The "XMLToExcel" connector allows you to transform an XML file into an Excel file following a given format.
9 The connector uses the Apache POI library to perform the transformation.
10 )))
11
12 (((
13
14 Here is what you can do very easily (You can do much better by making the view much prettier 😊):
15 )))
16
17 (((
18
19 )))
20
21 [[image:1619519065381-729.png||height="220" width="664"]]
22
23
24 [[image:1619519086074-417.png]]
25
26 [[image:1619519092034-893.png]]
27
28 Let's get to the heart of the matter.
29
30
31 = II- Parameters & Templates =
32
33
34 == Parameters ==
35
36 {{{You will find the "XMLToExcel" connector in the connectors palette:}}}
37
38 [[image:1619519234897-825.png]]
39
40 {{{Here are the parameters of the "XMLToExcel" connector:
41 - Format: The format that specifies all the visual parameters of the document
42 - Template: A Template file can be specified pre-filled with images / shapes… ==> Parameter not required.
43 - File name: The name of the output file.
44 - Input file: The input must take the form's format.}}}
45
46 == [[image:1619519361984-295.png]] ==
47
48
49 == Templates ==
50
51 {{{The connector will be based on a Template if exist, otherwise it will create a new Excel file.
52 The Template parameter is optional.}}}
53
54
55 = III- 1st Example: File with a template containing datas =
56
57 == Template ==
58
59 Below the following Template: An Excel file with two sheets (Preamble and Jobs)
60
61 - Preamble :
62
63 [[image:1619519878302-168.png||height="282" width="709"]]
64
65
66 - Jobs :
67
68 [[image:1619520073569-407.png||height="291" width="721"]]
69
70
71 {{{The “Jobs” sheet contains a conditional display in the status and duration column:
72 - Status “OK” will be displayed in green
73 - Status “KO” will be displayed in red
74 - status “Warn” will be displayed in orange
75 - Time greater than 500 seconds will be displayed in red.}}}
76
77 == Output File ==
78
79 {{{In this example, we want to generate this Excel file : }}}
80
81 [[image:1619521020905-384.png||height="206" width="725"]]
82
83 And a filled data table :
84
85 [[image:1619521047676-484.png||height="314" width="723"]]
86
87 Datas will be inserted into the Excel file taking into account the initial format of the Template (including conditional display)
88
89
90 == File format ==
91
92 The format file used for this transformation :
93
94 {{code language="xml"}}
95
96 <iXDoc>
97 <styleSheet1 styleSheetName="Preamble" actif="true">
98 <Preambule type="table">
99 <Date beginLine="4">
100 <value column="E"/>
101 </Date>
102 <Description>
103 <value column="E"/>
104 </Description>
105 </Preambule>
106 </styleSheet1>
107 <styleSheet2 styleSheetName="Jobs" >
108 <Jobs type="table">
109 <Job beginLine="3">
110 <JOBID column="B" formatType="numeric" />
111 <DESCRIPTION/>
112 <DURATION formatType="numeric" />
113 <STARTDATE/>
114 <STATUSTYPEID formatType="numeric"/>
115 </Job>
116 </Jobs>
117 </styleSheet2>
118 </iXDoc>
119
120 {{/code}}
121
122
123 == Input File ==
124
125
126 The input file used for this transformation is :
127
128 {{code language="xml"}}
129 <iXDoc>
130 <styleSheet1>
131 <Preambule>
132 <Date>
133 <value>27/04/2021</value>
134 </Date>
135 <Description>
136 <value>Generated dynamically</value>
137 </Description>
138 </Preambule>
139 </styleSheet1>
140 <styleSheet2>
141 <Jobs>
142 <Job>
143 <JOBID>227140</JOBID>
144 <DESCRIPTION>Session 227140 : FTP - R-LIV-076597601-3.XML</DESCRIPTION>
145 <DURATION>164</DURATION>
146 <STARTDATE>2021-01-20 13:13:01.0</STARTDATE>
147 <STATUSTYPEID>0</STATUSTYPEID>
148 </Job>
149 <Job>
150 <JOBID>227141</JOBID>
151 <DESCRIPTION>Received file : /R-LIV-076597601-3.XML' from : 'FML_REPORT</DESCRIPTION>
152 <DURATION>130</DURATION>
153 <STARTDATE>2021-01-20 13:13:01.0</STARTDATE>
154 <STATUSTYPEID>0</STATUSTYPEID>
155 </Job>…
156 </Jobs>…
157
158 {{/code}}
159
160
161 Note that those datas can be retrieved dynamically from a BDDToXML connector for instance.
162
163
164 == Format explanation ==
165
166 The file format and the input file begin with an iXDoc node, followed by "Sheet" type nodes. Each "Sheet" node must contain attribut: type = "Table" or type = "Chart", in the example above, we have the sheet "Preamble" which contains small tables. Here are the properties encountered:
167
168
169 (% style="margin-left:auto; margin-right:auto" %)
170 |=Properties|= Description|=Example
171 |styleSheetName|The name of the Excel document sheet, **mandatory value**. If the sheet does not exist, the connector created it with the name of the property|styleSheetName= " Preamble"
172 |actif|Designates whether the page should be active when opening the document or not.
173 This property only exists for "sheet" type tags (which have the "styleSheetName" attribute)|Actif = "true"
174 |type|The type of the sheet element, either "table" or "chart"|(((
175 Type= "table"
176
177 Type= "chart"
178 )))
179 |beginLine|The start line on the chosen sheet|beginLine="4"
180 |column|The start column (in letters)|column= "B"
181 |formatType|The format type of the file's cell|formatType= "numeric"
182
183
This wiki is hosted and managed by iXPath
Powered by XWiki 13.3 debian