The advanced, converter workflow component, Excel to XML converts an input excel file in the .xlsx format into the special XML format.
The converter handles rows, columns and the most important styles.
A related workflow component can create an output Excel file from an input XML file: XML to Excel.
The XML result can afterwards be used as data input for templates (or rendered back into an Excel document).
The component has no parameters:
An example
In order to make a workflow accept an excel file as input you need to specify Excel as the input file type.
If we e.g. consider an input excel file looking like this:
Then the output XML file will look like below:
(Not the complete file is shown)
This is the internal format, that is used by InterFormNG2, when output Excel files are to be generated based on an XML specification.
Example: Convert an Excel file into XML and save it into a file/table on the IBM i
The task is here to save the data found in this input excel file:
Into an IBM i file (table) with the similar fields:
PERIODE CHAR 6
PERSONALNUMMMER CHAR 10
NAME CHAR 30
ABTEILUNG CHAR 50
ANZAHL BINARY 4 0
The file is called NVV010PF and is stored in the library, SILPDTA.
Setup a connection to the database
First we need to setup a connection to the output database e.g. like below:
Here we use this as the connection URL:
jdbc:as400:192.168.151.250;naming=system;translate binary=true;transaction isolation=none
To implement the requested we can setup a workflow like below:
Part 1:
Part 2:
Each of the workflow components are covered below:
This is the input type for this workflow. Here we monitor a folder for files with the extenstion .xlsx:
We expect to work with the xml file, that is generated from the Excel to XML component, so that has previously been run and the output has been saved with To filesystem and then loaded in the documents library, so that is could be referenced here as the sample file. Please notice, the filter for the file name and the input type which match the input file.
2. Excel to XML
This converts the excel file, which was the input into an XML file and saves that into the payload of the workflow:
3. Payload to workflow variable
In future versions this might not be necessary, but in the current version (3.4.2) we need to update an internal XML with the XML file, that has just been generated by the Excel to XML component. A way to do that is to save the payload into a workflow variable and restore the payload from the variable again. This is done with this and the next component:
(with the component above we save the payload into a dummy variable named payload).
With this component we update the internal XML from the variable saved in the previous step:
5. Set multiple workflow variables
Here we prepare for the repeat, which we will use to iterate over all the detail lines of the XML file:
The input Excel file contains a header line which is the first row node, which has the node y=0, so we need to skip that.
So the number of records to write is the total number of row nodes, where we subtract 1.
The Index variable is set to 1 - again to skip the first row node, where y=0. We will use this variable as an index for XML node references.
6. Repeat
Here we prepare to repeat over the row nodes in the XML file. The number of repetitions is already know and stored in the NbrOfRecords variable:
It is very important that the option, Keep variable values and payload repeats is enabled, so we can update the Index variable for each iteration.
Inside the repeat we can now write the current record (identified by the current value of the index variable) as below:
Please notice, that we use the Index variable for comparing with the value of the y-node to gradually iterate over the input nodes.
The last step is to increase the Index variable for each iteration in the repeat loop as below:
