Excel to XML component

Excel to XML component

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:

 

NG2ExcelToXML0001

 

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:

 

NG2ExcelToXML0002

 

Then the output XML file will look like below:

(Not the complete file is shown)

NG2ExcelToXML0003

 

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:

 

1. Read from file

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).

 

4. From variable to 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.

 

7. Database SQL Update

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.

 

8. Set multiple variables

The last step is to increase the Index variable for each iteration in the repeat loop as below:

​

 

 

    • Related Articles

    • EXCEL

      EXCEL is one of the file types, that can be handled as input in the workflow. Only input excel files in the format, .xlsx is supported. The excel files can e.g. be converted into xml with the component, Excel to XML.

    • XML to Excel

      The advanced, create document workflow component, XML to Excel is described below. This is a workflow component, that accepts a special formatted input XML file (that defines the Excel layout) and converts this into an Excel file in the payload. ...

    • Excel designer

      Excel Support It is possible to generate Excel output in InterFormNG2, and it is also possible to convert Excel files into XML. You need however to have a license for InterFormNG2, that includes the Excel functionality, which can be support for ...

    • XML

      XML is used widely in InterFormNG2 as both input and output payload in the workflow (and of course in the designer). Please notice, that some workflow components are listed as accepting only XML as input, where they actually also accept an IBM i ...

    • Create Excel document

      InterFormNG2 can generate an output excel file in the format .xlsx based on a special formatted XML file. Such an XML file can be generated by the related workflow component, Excel to XML. The advanced, Create Excel document component has these ...