Sheetjs: sheet_add_json with origin: -1 leaves gap from bottom of worksheet

[dandv]

I'm seeing strange behavior with the following code:

const XLSX = require('xlsx');

const wb = XLSX.utils.book_new();

const ws = XLSX.utils.json_to_sheet([
  { Product: 'p2', Qty: 2 },
]);

XLSX.utils.sheet_add_json(ws,
  [
    { Product: 'p3', Qty: 3 },
    { Product: 'p4', Qty: 4 },
    { Product: 'p5', Qty: 5 },
    { Product: 'p6', Qty: 6 },
    { Product: 'p7', Qty: 7 },
    { Product: 'p8', Qty: 8 },
  ],
  {
    header: ['Product', 'Qty'],
    skipHeader: true,
    origin: -1,  // append to bottom of worksheet starting on first column
  }
);


XLSX.utils.book_append_sheet(wb, ws, 'Bug sheet');

XLSX.writeFile(wb, 'bug.ods');

I expected p3...p8 to be placed on rows 3 ... 8, but they ended up on rows 6 ... 11 (in LibreCalc at least).

I'm on 0.14.1. What might be going on?

[dandv]

Did some more digging. Looks like if the JSON array has N >= 4 elements, the library inserts N - 3 empty rows, if the existing worksheet has 2 rows.

[SheetJSDev]

The logical error is in https://github.com/SheetJS/js-xlsx/blob/master/bits/90_utils.js#L193-L199 : when origin: -1 is specified and the number of rows inserted exceeds the number of rows in the file, it is added twice.

It would probably be simpler to proactively wraparound by checking if _R < 0 in https://github.com/SheetJS/js-xlsx/blob/master/bits/90_utils.js#L186 and just adding the number of rows in the worksheet. This would also handle cases like -2 for the penultimate row.

[dandv]

Thanks for confirming and looking forward to the fix!

[dandv]

[dongido001]

Made a pull request that solves the issue #1463 @dandv

[dandv]

Yay! First time I'm about to award a bounty, after years of using BountySource :)

@SheetJSDev, can you please test and merge if everything checks out?

[dandv]

Hey @SheetJSDev, ping :)

[dandv]

Here's a 2020 ping!

[fabiooshiro]

workaround with the advantage of writing new columns header if your object has new properties

let arrayObjects = XLSX.utils.sheet_to_json(dataSheet);
arrayObjects.push(newObject);
XLSX.utils.sheet_add_json(dataSheet, arrayObjects);