InterFormNG2 is able to convert generic CSV files into Excel. In this section I will describe how that can be done.
As an example we consider a CSV file with this content:
Seq,Product No,Description
1,4234,aaaaa
2,23423,bbbbbb
3,3423,ccccccc
4,345,dddd
5,453,ee
6,45345,fff
7,4534,gggg
8,3311,hhh
9,34236,iiii
10,334534,jjj
11,5345,kkkk
12,123,lll
13,435,mmm
14,664,nnnn
15,543,oooo
16,745,ppp
17,34356,qqqq
18,64,rr
19,564,sss
20,5,tttt
21,54,uuuu
22,456,vvvvv
23,456,www
24,454,xxxxx
25,56456,yyyyy
26,5689,zzzzzz
The first line contains headers and the rest of the lines contains the detail lines. Each field is separated with a comma. The first thing we need to do is to convert the CSV file into XML. We can do that in a workflow like so:
This converts the CSV file into an XML file with the CSV to XML workflow component. We save it as a file with the To filesystem component.
In order to proceed we first need to load the XML file as a document resource in the Library, which makes it available in the Excel designer.
In the Excel designer we first load the sample xml file.
The sample XML file looks like this:
Then we can build up a generic Excel template like below:
The Excel template has been built up of these elements:
1.The first repeat element repeats the rest of the elements for each row found in the input XML file.
2.For each of these rows we insert a new row.
3.Inside of each row we want to convert each node into an column in the output excel file. By inserting a repeat with * as the node selection we are adding a repeat, that iterates through all nodes of the current row.
4.Inside the last repeat we insert a cell. The value of this cell is set to be the value of the current node. That can be selected with a dot (.) or the function, text() in an XPath expression:
If you want a more advanced Excel output, where the header is printed in bold, then you can set it up like below:
In the above setup we have inserted two conditions with the if element to determine if the current row is a header or not (i.e. does the first cell contain the text 'Seq' or not).
Inside each condition we have the same setup as above with the only difference, that the cell inside the first repeat refers to a header style (to create a bold text):
And the second cell (for the detail lines) does not:
With the additions added above the output Excel file looks like this:
