I had written the code to read the excel file into json, but not able to read it as expected. It return the array of each row. Can someone help me read the data and write it to json file correctly. Thanks in advance. Below is my code:

plugins/index.js file

const xlsx = require("node-xlsx").default;
const fs = require("fs");
const path = require("path");
module.exports = (on, config) => {
  on("task", {
    parseXlsx({ filePath }) {
      return new Promise((resolve, reject) => {
        try {
          const jsonData = xlsx.parse(fs.readFileSync(filePath));
          resolve(jsonData);
        } catch (e) {
          reject(e);
        }
      });
    }
  });
}

spec.js file

describe('API', () => {
    it('readf', () => {
        cy.parseXlsx("/Cypress/cypress/fixtures/data.xlsx").then(
            (jsonData) => {
                const rowLength = Cypress.$(jsonData[0].data).length
                for (let index = 0; index < rowLength; index++) {
                    console.log(jsonData[index].data)
                }
            }
        )
    }
)

I want the json output and write to json file as below:

{
    "Sheet1": [
        {
            "Username": "user1",
            "password": "password1"
        },
        {
            "Username": "user2",
            "password": "password2"
        },
        {
            "Username": "user3",
            "password": "password3"
        }
    ]
}

I had written the code to read the excel file into json, but not able to read it as expected. It return the array of each row. Can someone help me read the data and write it to json file correctly. Thanks in advance. Below is my code:

plugins/index.js file

const xlsx = require("node-xlsx").default;
const fs = require("fs");
const path = require("path");
module.exports = (on, config) => {
  on("task", {
    parseXlsx({ filePath }) {
      return new Promise((resolve, reject) => {
        try {
          const jsonData = xlsx.parse(fs.readFileSync(filePath));
          resolve(jsonData);
        } catch (e) {
          reject(e);
        }
      });
    }
  });
}

spec.js file

describe('API', () => {
    it('readf', () => {
        cy.parseXlsx("/Cypress/cypress/fixtures/data.xlsx").then(
            (jsonData) => {
                const rowLength = Cypress.$(jsonData[0].data).length
                for (let index = 0; index < rowLength; index++) {
                    console.log(jsonData[index].data)
                }
            }
        )
    }
)

I want the json output and write to json file as below:

{
    "Sheet1": [
        {
            "Username": "user1",
            "password": "password1"
        },
        {
            "Username": "user2",
            "password": "password2"
        },
        {
            "Username": "user3",
            "password": "password3"
        }
    ]
}

• javascript
• json
• excel
• promise
• cypress

• 1 If you do not have larger set of data in excel and if it is just username and password data, then why can't you put the data in JSON itself and why are you making things plex. I am just trying to understand your use case. – Srinu Kodi Commented Jul 13, 2020 at 16:03
• @SrinuKodi, This is just the example file. But, in real i have excel file which contains more than 7 columns. So, just for example the i need json like: { "Sheet1": [ { "Username": "user1", "password": "password1", "firstname":"test1", "lastname":"gender", "age":"20", "gender":"M/F", "location":"abc" } ] } – Rutvik Commented Jul 14, 2020 at 5:51

3 Answers 3

For converting Excel sheets into JSON (and back), I use SheetJS's xlsx.

const XLSX = require('xlsx');

// read file
let workbook = XLSX.readFile(filename);

// read first sheet (identified by first of SheetNames)
let sheet = workbook.Sheets[workbook.SheetNames[0]];

// convert to JSON
let json = XLSX.utils.sheet_to_json(sheet);

Keep in mind, especially if running a sever app, xlsx uses blocking methods and will cause I/O waits on your process thread. Better run this async in a fork.

$('#ImportItemsInput').change(function (e) {
    var reader = new FileReader();
    reader.readAsArrayBuffer(e.target.files[0]);
    reader.onload = function (e) {
        var data = new Uint8Array(reader.result);
        var workbook = XLSX.read(data, { type: 'array' });
        var sheet = workbook.Sheets[workbook.SheetNames[0]];
        let json = XLSX.utils.sheet_to_json(sheet);
    }
});

You cannot directly convert excel to JSON and read it in your code. As the readFileSync hits the browser and it is a server based function.

To overe this issue please follow the solution provided by me in the below repository. "https://github./Achudan/ExcelToJsonConverterForCypress"

(IT WORKED GOOD FOR OUR TEAM AND IT'S SIMPLE SOLUTION RATHER THAN WRITING A SEPARATE EXCEL TO JSON CONVERTER)

Run the node app in a port and make cy.request in your Cypress mand.

Detailed explanation is given in Readme of above github link.