getCellValue delivers "[object Object]" - but why?

[itsmuellergit]

s this phenomenon already known? I don’t understand why getCellValue suddenly returns "[object Object]" as the value in large sheets.

The formula in all cells is similar (just shifted by one column), for example:
=SUM(H36:H47)

Here are some example values:
446 = Object {row: 32, col: "F", value: 5000}
447 = Object {row: 32, col: "G", value: 9000}
448 = Object {row: 32, col: "H", value: 8000}
449 = Object {row: 32, col: "I", value: "[object Object]"}
450 = Object {row: 32, col: "J", value: "[object Object]"}
451 = Object {row: 32, col: "K", value: "[object Object]"}

Do you have any idea why this is happening?

[sequba]

@itsmuellergit thank you for reaching out!

Please share a code that reproduces the issue so we can investigate it. If possible, use https://codesandbox.io/

[itsmuellergit]

Here is a short version of my code:
https://codesandbox.io/p/sandbox/data-cxxstq

run: npx tsx main.ts

I tried to reproduce the behaviour of my NestJs App with this small version.
Also my original Excel Sheet is way bigger, but contains real Business data of my client, therefore this
melt dow version - data3.xlsx

Problem is:
Values in Excel: =DATA!$A$11:$J$60
1’100 2’930 5’500 4’000 5’300 10’600

Output of getCellValue of these cells:
{ sheet: 0, row: 10, col: 4, value: 1100 }
{ sheet: 0, row: 10, col: 5, value: 2930 }
{ sheet: 0, row: 10, col: 6, value: 2930 }
{ sheet: 0, row: 10, col: 7, value: 2930 }
{ sheet: 0, row: 10, col: 8, value: 2930 }
{ sheet: 0, row: 10, col: 9, value: 2930 }

-> Its working for first 2 Cells, but next values are wrong.
The interesting thing is, that the output of my "real" NestJs
App (values in original Excel are different, but formulas are the same than the ones in data3):

446 = Object {row: 32, col: "F", value: 5000}
447 = Object {row: 32, col: "G", value: 9000}
448 = Object {row: 32, col: "H", value: 8000}
449 = Object {row: 32, col: "I", value: "[object Object]"}
450 = Object {row: 32, col: "J", value: "[object Object]"}
451 = Object {row: 32, col: "K", value: "[object Object]"}

Mayby you could figure out what the problem is

[sequba]

The cause of the issue is the incorrect way of reading data from the xlsx file.

As described in our File Import guide, the recommended way to read cell data using ExcelJS looks like this:

const cellData = cell.formula ? `=${cell.formula}` : cell.value;

In the provided example, the calculated results match your expected numbers when you replace the file reading part of your code with the following snippet:

      const data: any[][] = [];
      worksheet.eachRow({ includeEmpty: true }, (row, rowNumber) => {
        data[rowNumber - 1] = [];
        row.eachCell({ includeEmpty: true }, (cell, colNumber) => {
          const cellData = cell.formula ? `=${cell.formula}` : cell.value;
          data[rowNumber - 1][colNumber - 1] = cellData;
        });
      });

I hope, this solves your issue ;)

[itsmuellergit]

Top Support - thank's a lot ...