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 :
Desire result :
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 ?