How can I convert excel to json and write to json file programmatically in cypress?

Question

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"
        }
    ]
}

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 complex. I am just trying to understand your use case. — Srinu Kodi
– 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
– Rutvik, Commented Jul 14, 2020 at 5:51

Δ O · Accepted Answer · 2020-07-13 23:04:43Z

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.

answered Jul 13, 2020 at 23:04

Δ O

3,7382 gold badges22 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

Rutvik Over a year ago

Thanks, for the approach but i am not dealing with the server app. So, i am getting the error of readFileSync. So could you suggest some other way.

Δ O Over a year ago

I think my approach should work well with non-server app. What error are you getting?

Δ O Over a year ago

Well, it should be :) Are we using the same library (xlsx, not node-xlsx)? Post more code.

Rutvik Over a year ago

I tried with xlsx and it shows _fs.readFileSync is not a function

Δ O Over a year ago

For parsing JSON object out of a string, use JSON.parse. For parsing a column on each row, I'd recommend mapping the array with a method which parses the column. If you need more help with this, post it as a new question with more specifics :)

|

Nasser X · Accepted Answer · 2022-11-29 09:03:58Z

0

$('#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);
    }
});

answered Nov 29, 2022 at 9:03

Nasser X

3054 silver badges11 bronze badges

Comments

ts achu · Accepted Answer · 2021-01-21 11:41:10Z

-2

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 overcome this issue please follow the solution provided by me in the below repository. "https://github.com/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 command.

Detailed explanation is given in Readme of above github link.

answered Jan 21, 2021 at 11:41

ts achu

1

Collectives™ on Stack Overflow

How can I convert excel to json and write to json file programmatically in cypress?

3 Answers 3

13 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

13 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related