Error in code: removing duplicate value from xlsx sheet in node.js

Question

I am trying to remove the duplicate values from xlsx sheet for which I have written "removeDuplicate" function which takes xlsx sheet as an argument;

Below is my code for removeDuplicate.I tried this code on console for the array of Json and it is giving correct value butwhen I am trying to put the result in xlsx sheet it is still retaining the duplicate values.

const XLSX = require('xlsx')
var json2xls = require('json2xls');
var removeDuplicate=((worksheet)=>{




var xlsxJson = XLSX.utils.sheet_to_json(worksheet);
var jsonString = JSON.stringify(xlsxJson);
var xlsxJson1 = JSON.parse(jsonString);
console.log(xlsxJson1[0]['Report Status'])
var i;
for (let i = 0; i < xlsxJson1.length; i++) {
{
  for(let j=i+1;j<xlsxJson1.length; j++){
    if(xlsxJson1[i]['TicketName']===xlsxJson1[j]['TicketName'] 
    && xlsxJson1[i]['Date']==xlsxJson1[j]['Date'] 
    && xlsxJson1[i]['In_Input']===xlsxJson1[j]['In_Input']
    && xlsxJson1[i]['In_Result']===xlsxJson1[j]['In_Result']
    &&xlsxJson1[i]['Call_Api']===xlsxJson1[j]['Call_Api']
    && xlsxJson1[i]['Report Status']===xlsxJson1[i]['Report Status']){
      xlsxJson1[j]=" ";
      console.log(xlsxJson1[j])
    }
    else if((xlsxJson1[i]['TicketName']===xlsxJson1[j]['TicketName']
    && xlsxJson1[i]['Date']==xlsxJson1[j]['Date']) 
    && (xlsxJson1[i]['In_Input']===xlsxJson1[j]['In_Input']
    || xlsxJson1[i]['In_Result']===xlsxJson1[j]['In_Result']
    || xlsxJson1[i]['Call_Api']===xlsxJson1[j]['Call_Api']
    || xlsxJson1[i]['Report Status']===xlsxJson1[i]['Report Status'])){
      xlsxJson1[i]=xlsxJson1[j]
      xlsxJson1[j]=" ";
      console.log(xlsxJson1[j])
    }
    else{
      console.log(xlsxJson1[j])
      continue;

    }
  }
}
}
return xlsxJson;
//
return json2xls(xlsxJson1)
})

module.exports=removeDuplicate;

Below is my code where this function is being called in app.js: aap.js

ws = XLSX.utils.sheet_add_json(ws, ticketNameArr,{origin:-1, skipHeader:true});


removeDuplicate(ws)
                    // XLSX.utils.book_append_sheet(wb, ws) // sheetAName is name of Worksheet

                    XLSX.writeFile(wb, 'DailyTicketSatus.xlsx')
                    // XLSX.writeFile(wb, 'DT.xlsx')

[{ Date: 10.09.2019, TicketName: 'ABC', In_Input: 'TRUE', Input_Time: 20:08, In_Result: 'FALSE', Call_Api: 'False', 'Report Status': 'No log files', Comment: 'Ticket Was Not processed', Status: 'Partial Failure' }, { Date:11.10.19, TicketName: 'BCD', In_Input: 'FALSE', In_Result: 'FALSE', Call_Api: 'False', 'Report Status': 'No log files', Comment: 'Ticket Was Not picked', Status: 'Failure' }, { Date:11.10.19 , TicketName: 'BCD', In_Input: 'TRUE', Input_Time: 43743.37598236111, In_Result: 'FALSE', Call_Api: 'False', 'Report Status': 'No log files', Comment: 'Ticket Was Not processed', Status: 'Partial Failure' }]

Now the expected output is : { Date: 10.09.2019, TicketName: 'ABC', In_Input: 'TRUE', Input_Time: 20:08, In_Result: 'FALSE', Call_Api: 'False', 'Report Status': 'No log files', Comment: 'Ticket Was Not processed', Status: 'Partial Failure' }, { Date:11.10.19, TicketName: 'BCD', In_Input: 'FALSE', In_Result: 'FALSE', Call_Api: 'False', 'Report Status': 'No log files', Comment: 'Ticket Was Not picked', Status: 'Failure' }, { "" }. Which I am getting at console.But I am not able to write that in xlsx file.I tried json2xlsx to but it didn;t work.How can I write the modified data into my xlxs sheet.

https://stackoverflow.com/questions/17450412/how-to-create-an-excel-file-with-nodejs take a look — Shubham Tiwari, Oct 18 '19 at 12:10

score 0 · Answer 1 · answered Oct 18 '19 at 13:38

You can remove the duplicates, return the JSON data, then replace the existing worksheet. The code below will do this for the input file, then write to the output file.

If you wish to change the file in place, simply set the output file name to the same as the input file. Ensure the file is not locked though!

var removeDuplicate=((worksheet)=> {

    var xlsxJson = XLSX.utils.sheet_to_json(worksheet);

    for (let i = 0; i < xlsxJson.length; i++) {
        for(let j=i+1;j<xlsxJson.length; j++) {
            if(xlsxJson[i]['TicketName']===xlsxJson[j]['TicketName'] 
                && xlsxJson[i]['Date']==xlsxJson[j]['Date'] 
                && xlsxJson[i]['In_Input']===xlsxJson[j]['In_Input']
                && xlsxJson[i]['In_Result']===xlsxJson[j]['In_Result']
                && xlsxJson[i]['Call_Api']===xlsxJson[j]['Call_Api']
                && xlsxJson[i]['Report Status']===xlsxJson[i]['Report Status']) {
                xlsxJson[j]=" "; // Mark row as duplicate
            }
            else if((xlsxJson[i]['TicketName']===xlsxJson[j]['TicketName']
            && xlsxJson[i]['Date']==xlsxJson[j]['Date']) 
            && (xlsxJson[i]['In_Input']===xlsxJson[j]['In_Input']
            || xlsxJson[i]['In_Result']===xlsxJson[j]['In_Result']
            || xlsxJson[i]['Call_Api']===xlsxJson[j]['Call_Api']
            || xlsxJson[i]['Report Status']===xlsxJson[i]['Report Status'])) {
                xlsxJson[i]=xlsxJson[j]
                xlsxJson[j]=" "; // Mark row as duplicate
            }
            else {
                continue;
            }
        }

    }

    // Filter out duplicate rows.
    xlsxJson = xlsxJson.filter(row => (row + "").trim());
    return xlsxJson;    
})

const inputFile = "tickets.xlsx";
const outputFile = "output.xlsx";
const sheetName = "Sheet1" // <-- Change to the actual sheet name.
const workbook = XLSX.readFile(inputFile);
let sheetJson = removeDuplicate(workbook.Sheets[sheetName]);

// Overwrite worksheet
workbook.Sheets[sheetName] = XLSX.utils.json_to_sheet(sheetJson);

XLSX.writeFile(workbook, outputFile);

Error in code: removing duplicate value from xlsx sheet in node.js

1 Answers1