Read Excel File using xlsx and fs packages in Javascript/NodeJs

 

In this post, we are going to see how to parse excel file using Javascript,NodeJs packages.

npm Packages required :

1. fs  -- For reading and writing files

2. xlsx - For parsing excel files to different output formats like csv or json

const fs = require("fs");

const xlsx = require("xlsx");

var workbook = xlsx.readFile(

  "C:/Users/xxxx/public/json/SampleExcel.xlsx"

);

// Get the sheets of the Excel

var sheet_name_list = workbook.SheetNames;

// read the first sheet sheet_name_list[0] in the workbook

// use xlx.utils.sheet_to_csv to display the excel records in csv format

var csvData = xlsx.utils.sheet_to_csv(workbook.Sheets[sheet_name_list[0]]);

//The above function will return data like below

/*

Fruit,Retail Price,Discount,Total

Apple,₹ 100.00,5,₹ 95.00

Strawberry,₹ 200.00,2,₹ 196.00

Mango,₹ 100.00,4,₹ 96.00

Kiwi,₹ 120.00,1,₹ 118.80

Lemon,₹ 340.00,5,₹ 323.00

Grapes,₹ 230.00,6,₹ 216.20

Pineapple,₹ 110.00,3,₹ 106.70

Papayya,₹ 50.00,8,₹ 46.00

Orange,₹ 75.00,1,₹ 74.25

*/

//Use xlsx.utils.sheet_to_json to convert the excel sheet data to json format with

 first row as headers

var jsonData = xlsx.utils.sheet_to_json(workbook.Sheets[sheet_name_list[0]]);

/*

Output :

[

  { Fruit: 'Apple', 'Retail Price': 100, Discount: 5, Total: 95 },

  { Fruit: 'Strawberry', 'Retail Price': 200, Discount: 2, Total: 196 },

  { Fruit: 'Mango', 'Retail Price': 100, Discount: 4, Total: 96 },

  { Fruit: 'Kiwi', 'Retail Price': 120, Discount: 1, Total: 118.8 },

  { Fruit: 'Lemon', 'Retail Price': 340, Discount: 5, Total: 323 },

  { Fruit: 'Grapes', 'Retail Price': 230, Discount: 6, Total: 216.2 },

  {

    Fruit: 'Pineapple',

    'Retail Price': 110,

    Discount: 3,

    Total: 106.7

  },

  { Fruit: 'Papayya', 'Retail Price': 50, Discount: 8, Total: 46 },

  { Fruit: 'Orange', 'Retail Price': 75, Discount: 1, Total: 74.25 }

]

*/

console.log(csvData);