XML to Excel Conversion Using Node JS.

In this article I will explain how to convert the xml to excel using node js.

First you have to convert the xml to json and then convert json to excel.

when you have a Web Service, which has the response or output as XML data. Before using it in our UI, we can convert this XML response to JSON data and get the particular data in the form of Excel, so that it will be easy for us to generate the reports.

You would like to parse the XML data to JSON data, from that JSON you have to retrieve the particular fields you want and then generate the report in excel format.

How to convert XML to EXCEL:

In Node JS to convert XML to EXCEL, so many modules are available, but in this article we have to use simple modules i.e xml2js, json2xls module.

Steps to convert XML to JSON:

Step 1: Install xml2js module.

You can install xml2js module by using npm, type the following command in command prompt as follow:

npm install xml2js

Step 2: Create a sample XML file or use the XML file you have.

If you have XML Web Service URL, then generate a XML file and use it in place of sample XML file.

<?xml version=”1.0" encoding=”UTF-8"?>
<EmployeeDetails>
<EmployeeDetail>
<PersonalInformation>
<FirstName>Chandu</FirstName>
<LastName>Mallireddy</LastName>
<Gender>Female</Gender>
</PersonalInformation>
</EmployeeDetail>
<EmployeeDetail>
<PersonalInformation>
<FirstName>TarunTej</FirstName>
<LastName>Mallireddy</LastName>
<Gender>Male</Gender>
</PersonalInformation>
</EmployeeDetail>
</EmployeeDetails>

Step 3: Code to convert XML to JSON .

var fs = require(“fs”); // File System Module

var parseString = require(‘xml2js’).parseString; // XML2JS Module

var xml = ‘<?xml version=”1.0" encoding=”UTF-8"?><EmployeeDetails><EmployeeDetail><PersonalInformation><FirstName>Chandu</FirstName><LastName>Mallireddy</LastName>’

+’<Gender>Female</Gender></PersonalInformation></EmployeeDetail><EmployeeDetail><PersonalInformation><FirstName>TarunTej</FirstName><LastName>Mallireddy</LastName>’

+’<Gender>Male</Gender></PersonalInformation></EmployeeDetail></EmployeeDetails>’;

parseString(xml, function (err, result) {

jsonTextFromXML = JSON.stringify(result)

console.log(“result”,jsonTextFromXML);

});

Save the sample file in .js extension and name as xml2json.js

Step 4 : Run the xml2json.js file.

To run this file, first go the file placed directory in command prompt and type following command:

node xml2json.js

You can see the JSON response or output as follow:

{“EmployeeDetails”:{“EmployeeDetail”:[{“PersonalInformation”:[{“FirstName”:[“Chandu”],”LastName”:[“Mallireddy”],”Gender”:[“Female”]}]},{“PersonalInformation”:[{“FirstName”:[“TarunTej”],”LastName”:[“Mallireddy”],”Gender”:[“Male”]}]}]}}

You have JSON data, now you need to convert the JSON data to Excel.

Steps to convert JSON to EXCEL:

Step 1: Install json2xls module.

You can install json2xls module by using npm, type the following command in command prompt as follow:

npm install json2xls

Step 2 : select the JSON data,which you want to convert it into EXCEL.

I have choosen the JSON which was obtained in the before conversion.

{“EmployeeDetails”:{“EmployeeDetail”:[{“PersonalInformation”:[{“FirstName”:[“Chandu”],”LastName”:[“Mallireddy”],”Gender”:[“Female”]}]},{“PersonalInformation”:[{“FirstName”:[“TarunTej”],”LastName”:[“Mallireddy”],”Gender”:[“Male”]}]}]}}

Step 3: Code to convert JSON to EXCEL.

var json2xls = require(‘json2xls’); // JSON2XLS Module

var fs = require(“fs”); // File Module

var parseString = require(‘xml2js’).parseString;

var xml = ‘<?xml version=”1.0" encoding=”UTF-8"?><EmployeeDetails><EmployeeDetail><PersonalInformation><FirstName>Chandu</FirstName><LastName>Mallireddy</LastName>’

+’<Gender>Female</Gender></PersonalInformation></EmployeeDetail><EmployeeDetail><PersonalInformation><FirstName>TarunTej</FirstName><LastName>Mallireddy</LastName>’

+’<Gender>Male</Gender></PersonalInformation></EmployeeDetail></EmployeeDetails>’;

parseString(xml, function (err, result) {

jsonTextFromXML = JSON.stringify(result)

jsonTextArray=[]

for (i = 0; i < result.EmployeeDetails.EmployeeDetail.length; i++)

{

var tempArray = {

‘FirstName’: result.EmployeeDetails.EmployeeDetail[i].PersonalInformation[0].FirstName[0], //column name and value

‘LastName’ : result.EmployeeDetails.EmployeeDetail[i].PersonalInformation[0].LastName[0], //column name and value

‘Gender’: result.EmployeeDetails.EmployeeDetail[i].PersonalInformation[0].Gender[0] //column name and value

}

jsonTextArray.push(tempArray);

}

;

});

var xls = json2xls(jsonTextArray);

fs.writeFileSync(‘Reportsheet.xlsx’, xls, ‘binary’); // Name of the Excel

Save the filename as json2xls.js

Step 4 : Run the json2xls.js file.

To run this file, first go the file placed directory in command prompt and type following command:

node json2xls.js

You can see the Excel response as follow in the same directory where you have placed your file.