0

I have a node.js file whose function is to insert and delete columns in an excel file,Im working with the 'xslx' module.

Both insert and delete rows have their own function, so when I call one without calling the other, everything works fine, but if I call them at the same time they don't work as they should, the rows are repeated and not added as they should.

This is the data I want to put in the excel sheet :

    let datos = [
    {
        "Dato 1" : 1,
        "Dato 2" : 2,
        "Dato 3" : 3
    },
    {
        "Dato 1" : 4,
        "Dato 2" : 5,
        "Dato 3" : 6
    },
    {
        "Dato 1" : 7,
        "Dato 2" : 8,
        "Dato 3" : 9
    }
 ]

When I only call the function to put data, this happens Works fine it pull all the data in the correct way:

    function caller() {
        
        // console.log('Deleting previous data...')
        // deleteAllExcelRows(woorksheet)
        // console.log('Data eliminated\n')
    
    
        console.log("Put new data...\n");
        for (let i = 0; i < datos.length; i++) {
    
            putDataExcel(woorksheet,datos,i);
        }
    
    }
caller()

When I only call the function to delete the data also works fine,deletes all the data except the headers:

    function caller() {
        
        console.log('Deleting previous data...')
        deleteAllExcelRows(woorksheet)
        console.log('Data eliminated\n')
    
    
        // console.log("Put new data...\n");
        // for (let i = 0; i < datos.length; i++) {
    
        //     putDataExcel(woorksheet,datos,i);
        // }
    
    }
caller()

Now if I called both something very weird happens, the last elements of the data is repeated:

function caller() {
    
    console.log('Deleting previous data...')
    deleteAllExcelRows(woorksheet)
    console.log('Data eliminated\n')


    console.log("Put new data...\n");
    for (let i = 0; i < datos.length; i++) {

        putDataExcel(woorksheet,datos,i);
    }

}

caller()

INCORRECT RESULT :

enter image description here

Desire result :

enter image description here

Complete code :

const xlsx = require("xlsx");

const FILENAME = "C:\\Users\\harol\\Documents\\prueba-excel.xlsx"

let wb = xlsx.readFile(FILENAME);
let woorksheet = wb.Sheets[wb.SheetNames[0]];

let datos = [
    {
        "Dato 1" : 1,
        "Dato 2" : 2,
        "Dato 3" : 3
    },
    {
        "Dato 1" : 4,
        "Dato 2" : 5,
        "Dato 3" : 6
    },
    {
        "Dato 1" : 7,
        "Dato 2" : 8,
        "Dato 3" : 9
    }
]

function putDataExcel(ws,data,i) {

    let hoja = xlsx.utils.sheet_to_json(ws);
    console.log(xlsx.utils.decode_range(ws["!ref"]))

    hoja.push(data[i]);
    xlsx.utils.sheet_add_json(ws, hoja);
    xlsx.writeFile(wb, FILENAME);
}

function ec(row, column) {
  return xlsx.utils.encode_cell({ r: row, c: column });
}

function deleteExcelRow(ws, row_index) {

    let hoja = xlsx.utils.sheet_to_json(ws);
    console.log(xlsx.utils.decode_range(ws["!ref"]))

    let range = xlsx.utils.decode_range(ws["!ref"]);
    for (var R = row_index; R < range.e.r; ++R) {
        for (var C = range.s.c; C <= range.e.c; ++C) {
        ws[ec(R, C)] = ws[ec(R + 1, C)];
    }
  }
  range.e.r--;
  ws["!ref"] = xlsx.utils.encode_range(range.s, range.e);
  xlsx.writeFile(wb, FILENAME);
  console.log(xlsx.utils.decode_range(ws["!ref"]))
  console.log("\n")
}

function deleteAllExcelRows(ws)
{

    let numeroFilas = parseInt(xlsx.utils.decode_range(ws['!ref']).e.r)

    for(let i = 1;i<=numeroFilas;i++)
    {
        deleteExcelRow(ws,i);
        console.log("Dato " + i + "Eliminado ")
    }


}

function caller() {
    
    console.log('Deleting previous data...')
    deleteAllExcelRows(woorksheet)
    console.log('Data eliminated\n')


    console.log("Put new data...\n");
    for (let i = 0; i < datos.length; i++) {

        putDataExcel(woorksheet,datos,i);
    }

}

caller()

Why this happens and how I can solve it ?

Harold Burbano
  • 113
  • 2
  • 8
  • 1
    What is `deleteAllExcelRows(woorksheet)`? – Konrad Jan 03 '23 at 19:16
  • 1
    You should call `deleteAllExcelRows(woorksheet)` and use a callback to let your script know that function has completed, and to THEN run your loop. – Zak Jan 03 '23 at 19:18
  • But why I callback? as far as I know this code execute in a synchronous way – Harold Burbano Jan 03 '23 at 19:25
  • deleteAllExcelRows(woorksheet) is a function that deletes al rows except the rows calling in loop the function deleteExcelRow(ws, row_index) – Harold Burbano Jan 03 '23 at 19:26
  • Wrong. NodeJS is an asynchronous event-driven JavaScript runtime environment -- The for loop will try to run at the same time as the delete function. – Zak Jan 03 '23 at 19:33
  • [More Information Here](https://stackoverflow.com/questions/17607280/why-is-node-js-asynchronous) – Zak Jan 03 '23 at 19:35
  • So, Inside a function I put the function deleteAllExcelRows(woorksheet) ? – Harold Burbano Jan 03 '23 at 19:38

0 Answers0