Convert Excel to JSON & JSON to Excel

Jump to bottom

Manujaya edited this page Jul 21, 2021 · 1 revision

Guide Video Here

Convert Excel to JSON

1st need to install xlsx pacakge

"npm install xlsx"

Task 1 - Read data from Excel and store into JSON file

Step 1 - Read Excel file

const wb = xlsx.readfile(./<file_name>.xlsx);

Step 2 - Read Sheet from the WorkBook

const wb = wb.Sheets['<name_of_the_sheet>'];

Step 3 - Read Sheet Data and Covert into JSON

const data = xlsx.utils.sheet_to_json(ws);

Step 4 - Write JSON Data into JSON file by Stringfying data

fs.writeFileSync("./datajson.json", JSON.Stringfy(newData, null, 2)); //datajson.json is the file name

Convert JSON to Excel

Task 2 - Read data from JSON and store into Excel File

Step 1 - Read JSON File Content and parse it into JSON

let content = JSON.parse (fs.readFileSync ('./datajson.json', 'utf8'));

Step 2 - Create a WorkBook

let newWB = xlsx.utils.book_new();

Step 3 - Create a worksheet from json data read into Step 1

let newWS = xlsx.utils.json_to_sheet(content);

Step 4 - Append worksheet to WorkBook

xlsx.utils.book_append_sheet (newWB, newWS, 'new data'); // new data is the name of the sheet

Step 5 - Write Data to Excel

xlsx.writeFile(newWB, "newExcel.xlsx");