CODEWITHSUNDEEP
javascriptjsonexcelpromisecypress
Rutvik
Rutvik

Reputation: 137

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

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

Upvotes: 2

Views: 15572

Answers (3)

Nasser X
Nasser X

Reputation: 305

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

Upvotes: 0

ts achu
ts achu

Reputation: 1

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.

Upvotes: -2

Δ O
Δ O

Reputation: 3710

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.

Upvotes: 3

Related Questions