Wiki source code of XMLToXML

Version 16.1 by Achraf El Kari on 2021/04/27 13:43
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 Here is what you can do very easily (You can do much better by making the view much prettier 😊):
14 )))
15
16 (((
17
18 )))
19
20 [[image:1619519065381-729.png||height="220" width="664"]]
21
22
23 [[image:1619519086074-417.png]]
24
25 [[image:1619519092034-893.png]]
26
27 Let's get to the heart of the matter.
28
29
30 = II- Parameters & Templates =
31
32
33 == Parameters ==
34
35 {{{You will find the "XMLToExcel" connector in the connectors palette:}}}
36
37 [[image:1619519234897-825.png]]
38
39 {{{Here are the parameters of the "XMLToExcel" connector:
40 - Format: The format that specifies all the visual parameters of the document
41 - Template: A Template file can be specified pre-filled with images / shapes… ==> Parameter not required.
42 - File name: The name of the output file.
43 - Input file: The input must take the form's format.}}}
44
45 == [[image:1619519361984-295.png]] ==
46
47
48 == Templates ==
49
50 {{{The connector will be based on a Template if exist, otherwise it will create a new Excel file.
51 The Template parameter is optional.}}}
52
53
54 = III- 1st Example: File with a template containing datas =
55
56 == Template ==
57
58 Below the following Template: An Excel file with two sheets (Preamble and Jobs)
59
60 - Preamble :
61
62 [[image:1619519878302-168.png||height="282" width="709"]]
63
64
65 - Jobs :
66
67 [[image:1619520073569-407.png||height="291" width="721"]]
68
69
70 {{{The “Jobs” sheet contains a conditional display in the status and duration column:
71 - Status “OK” will be displayed in green
72 - Status “KO” will be displayed in red
73 - status “Warn” will be displayed in orange
74 - Time greater than 500 seconds will be displayed in red.}}}
75
76 == Output File ==
77
78 {{{In this example, we want to generate this Excel file : }}}
79
80 [[image:1619521020905-384.png||height="206" width="725"]]
81
82 And a filled data table :
83
84 [[image:1619521047676-484.png||height="314" width="723"]]
85
86 Datas will be inserted into the Excel file taking into account the initial format of the Template (including conditional display)
87
88
89 == File format ==
90
91 The format file used for this transformation :
92
93 {{code language="xml"}}
94
95 <iXDoc>
96 <styleSheet1 styleSheetName="Preamble" actif="true">
97 <Preambule type="table">
98 <Date beginLine="4">
99 <value column="E"></value>
100 </Date>
101 <Description>
102 <value column="E"></value>
103 </Description>
104 </Preambule>
105 </styleSheet1>
106 <styleSheet2 styleSheetName="Jobs" >
107 <Jobs type="table">
108 <Job beginLine="3">
109 <JOBID column="B" formatType="numeric" ></JOBID>
110 <DESCRIPTION></DESCRIPTION>
111 <DURATION formatType="numeric" ></DURATION>
112 <STARTDATE></STARTDATE>
113 <STATUSTYPEID formatType="numeric"></STATUSTYPEID>
114 </Job>
115 </Jobs>
116 </styleSheet2>
117 </iXDoc>
118
119 {{/code}}
120
121
122 == Input File ==
123
124
125 The input file used for this transformation is :
126
127 {{code language="xml"}}
128 <iXDoc>
129 <styleSheet1>
130 <Preambule>
131 <Date>
132 <value>27/04/2021</value>
133 </Date>
134 <Description>
135 <value>Generated dynamically</value>
136 </Description>
137 </Preambule>
138 </styleSheet1>
139 <styleSheet2>
140 <Jobs>
141 <Job>
142 <JOBID>227140</JOBID>
143 <DESCRIPTION>Session 227140 : FTP - R-LIV-076597601-3.XML</DESCRIPTION>
144 <DURATION>164</DURATION>
145 <STARTDATE>2021-01-20 13:13:01.0</STARTDATE>
146 <STATUSTYPEID>0</STATUSTYPEID>
147 </Job>
148 <Job>
149 <JOBID>227141</JOBID>
150 <DESCRIPTION>Received file : /R-LIV-076597601-3.XML' from : 'FML_REPORT</DESCRIPTION>
151 <DURATION>130</DURATION>
152 <STARTDATE>2021-01-20 13:13:01.0</STARTDATE>
153 <STATUSTYPEID>0</STATUSTYPEID>
154 </Job>…
155 </Jobs>…
156
157 {{/code}}
158
159
160 Note that those datas can be retrieved dynamically from a BDDToXML connector for instance.
161
162
163 == Format explanation ==
164
165 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:
166
167
168 (% style="margin-left:auto; margin-right:auto" %)
169 |=Properties|= Description|=Example
170 |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"
171 |actif|Designates whether the page should be active when opening the document or not.
172 This property only exists for "sheet" type tags (which have the "styleSheetName" attribute)|Actif = "true"
173 |type|The type of the sheet element, either "table" or "chart"|(((
174 Type= "table"
175
176 Type= "chart"
177 )))
178 |beginLine|The start line on the chosen sheet|beginLine="4"
179 |column|The start column (in letters)|column= "B"
180 |formatType|The format type of the file's cell|formatType= "numeric"
181
182
183
184 = IV- Example 2 : File with a template containing data and graphs =
185
186 == Template ==
187
188 In this part, we want to create a simple Excel file with graphs. Here are the Templates we want to have:
189
190 - "charts" sheet: which contains charts created by the iXPath connector.
191
192 - "datas" sheet: which  contains the appropriate datas.
193
194 [[image:1619522412969-991.png]]
195
196 == OutputFile ==
197
198 The "Charts" sheet:
199
200 [[image:1619522453939-314.png]]
201
202
203 The "Datas" sheet :
204
205 [[image:1619522483742-898.png]]
206
207 == ==
208
209 == File format ==
210
211 {{code language="xml"}}
212 <?xml version="1.0" encoding="UTF-8"?>
213 <iXDoc>
214 <styleSheet1 styleSheetName="datas">
215 <Exemples type="table">
216 <Header beginLine="5">
217 <Date1 column="C" bold="true" color="red" />
218 <Date2 column="D" bold="true" color="red" />
219 <Date3 column="E" bold="true" color="red" />
220 <Date4 column="F" bold="true" color="red" />
221 <Date5 column="G" bold="true" color="red" />
222 <Date6 column="H" bold="true" color="red" />
223 </Header>
224
225 <exemple>
226 <Date1 column="C" formatType="numeric" />
227 <Date2 column="D" formatType="numeric" />
228 <Date3 column="E" formatType="numeric" />
229 <Date4 column="F" formatType="numeric" />
230 <Date5 column="G" formatType="numeric" />
231 <Date6 column="H" formatType="numeric" />
232 </exemple>
233
234 </Exemples>
235 <Exemples2 type="table">
236
237 <Header2 beginLine="5">
238 <Date12 column="J" bold="true" color="red" />
239 <Date22 bold="true" color="red" />
240 <Date32 bold="true" color="red" />
241 </Header2>
242 <exemple2>
243 <Date12 column="J" formatType="numeric" />
244 <Date22 formatType="numeric" />
245 <Date32 formatType="numeric" />
246 </exemple2>
247
248 </Exemples2>
249 </styleSheet1>
250
251 <styleSheet2 styleSheetName="charts" actif="true">
252 <chart1 type="chart" AreaRange="3-17-B-I" sheet="datas" chartType="LINE3D" horizontal="true" bottomAxisTitle="Dates" leftAxisTitle="flows type" legend="Number of flows per day (LINE)" />
253
254 <chart2 type="chart" AreaRange="3-17-J-Q" sheet="datas" chartType="BAR3D" horizontal="true" bottomAxisTitle="Dates" stacked="true" leftAxisTitle="flows type" legend="Number of flows per day (BAR)" />
255
256 <chart3 type="chart" AreaRange="3-17-R-Y" sheet="datas" chartType="PIE3D" bottomAxisTitle="" leftAxisTitle="" legend="Number of flows per TYPE (SCATTER)" stacked="true" />
257
258 </styleSheet2>
259 </iXDoc>
260
261
262 {{/code}}
263
264
265 == Input file ==
266
267 {{code language="xml"}}
268 <?xml version="1.0" encoding="UTF-8"?>
269 <iXDoc>
270 <styleSheet1>
271 <Exemples>
272 <Header>
273 <Date1>10/04/2021</Date1>
274 <Date2>11/04/2021</Date2>
275 <Date3>12/04/2021</Date3>
276 <Date4>13/04/2021</Date4>
277 <Date5>14/04/2021</Date5>
278 <Date6>15/04/2021</Date6>
279 </Header>
280 <exemple>
281 <Date1>1033</Date1>
282 <Date2>1042</Date2>
283 <Date3>900</Date3>
284 <Date4>1313</Date4>
285 <Date5>1502</Date5>
286 <Date6>1432</Date6>
287 </exemple>
288 <exemple>
289 <Date1>801</Date1>
290 <Date2>743</Date2>
291 <Date3>1032</Date3>
292 <Date4>802</Date4>
293 <Date5>998</Date5>
294 <Date6>1203</Date6>
295 </exemple>
296 <exemple>
297 <Date1>1203</Date1>
298 <Date2>902</Date2>
299 <Date3>1320</Date3>
300 <Date4>730</Date4>
301 <Date5>1503</Date5>
302 <Date6>1094</Date6>
303 </exemple>
304 </Exemples>
305
306 <Exemples2>
307 <Header2>
308 <Date12>INVOICE</Date12>
309 <Date22>ORDER</Date22>
310 <Date32>DESADV</Date32>
311 </Header2>
312 <exemple2>
313 <Date12>4670</Date12>
314 <Date22>3780</Date22>
315 <Date32>5490</Date32>
316 </exemple2>
317 </Exemples2>
318
319 </styleSheet1>
320 <styleSheet2>
321 <chart1 keysRange="5-5-C-H" valuesRange="6-6-C-H:7-7-C-H:8-8-C-H" aliasValue="INVOICE:ORDER:DESADV" />
322 <chart2 keysRange="5-5-C-H" valuesRange="6-6-C-H:7-7-C-H:8-8-C-H" aliasValue="INVOICE:ORDER:DESADV" />
323 <chart3 keysRange="5-5-C-H" valuesRange="6-6-C-H:7-7-C-H:8-8-C-H" aliasValue="INVOICE:ORDER:DESADV" />
324 </styleSheet2>
325
326 </iXDoc>
327
328
329
330 {{/code}}
331
332
333 == Format explanation ==
334
335 (% style="margin-left:auto; margin-right:auto" %)
336 |**Properties**|**Description **|**Example**
337 |bold|The "bold" property can be applied to a column of data to make the style of the cell bold|
338 |color|Change a color of a cell|
339 |italic|The "italic" property can be applied to a column of data to make the style of the cell in italic|
340 |AreaRange|The range of the graph position.
341 Example: AreaRange = "3-17-J-Q"  Means that the graph must be positioned from the 3rd row to the 17th row, and from column J to column Q|
342 |sheet|The sheet where the chart data is located|
343 |chartType|
344 The types of the graph, here is the exhaustive list:
345 - PIE
346 - PIE3D
347 - LINE
348 - LINE3D
349 - BAR (Stacked, unstacked, horizontal / vertical)
350 - BAR3D (Stacked, unstacked, horizontal / vertical)
351 - AREA
352 - SCATTER
353 - RADAR|
354 |horizontal|Applicable for the Bar & BAR3D type graph, this makes the graph horizontal, by default the graph is vertical|
355 |bottomAxisTitle|The wording of the x-axis|
356 |leftAxisTitle|The label of the y-axis|
357 |legend|The legend of the graph|
358 |Stacked|Application for the BAR & BAR3D type graph, this makes the graph stored on a single column. "False" is the default.|
This wiki is hosted and managed by iXPath
Powered by XWiki 13.3 debian