sheet_to_json<t> not working #2832

New Issue

billmom · 2022-11-30T05:32:41Z

billmom commented

2022-11-30 05:32:41 +00:00

How do I get sheet_to_json to be a typed model?

I tried the following, but the return type is still of object[]. I would expect it to be DataRow[].

const rows: DataRow[] = XLSX.utils.sheet_to_json(ws);

It will work and it knows all the properties in DataRow as typescript is loosely coupled so as long as I tell it that it should be DataRow[] and it actually is DataRow[], it will work with intellisense and everything is great with the world.

The issue becomes when I want to use the resulting rows in the class-validator. It doesn't validate it because it is not actually a DataRow type. When I loop through the rows, I have to actually do an DataRow ndr = new DataRow(); rows.forEach(r => {Object.assign(ndr, r}); and then use the ndr to validate since it now assigned to the correct model.

How do I get sheet_to_json to be a typed model? I tried the following, but the return type is still of object[]. I would expect it to be DataRow[]. const rows: DataRow[] = XLSX.utils.sheet_to_json<DataRow>(ws); It will work and it knows all the properties in DataRow as typescript is loosely coupled so as long as I tell it that it should be DataRow[] and it actually is DataRow[], it will work with intellisense and everything is great with the world. The issue becomes when I want to use the resulting rows in the class-validator. It doesn't validate it because it is not actually a DataRow type. When I loop through the rows, I have to actually do an DataRow ndr = new DataRow(); rows.forEach(r => {Object.assign(ndr, r}); and then use the ndr to validate since it now assigned to the correct model.

sheetjs commented

2022-11-30 05:42:23 +00:00

There is a typed variant for sheet_to_json:

    /** Converts a worksheet object to an array of JSON objects */
    sheet_to_json<T>(worksheet: WorkSheet, opts?: Sheet2JSONOpts): T[];

To use it, you need to specify the type in the function call itself:

const rows: DataRow[] = XLSX.utils.sheet_to_json<DataRow>(ws);
// ---------------------------------------------^^^^^^^^^

This example is from the VueJS page in the documentation:

import { read, utils } from 'xlsx';

interface President {
  Name: string;
  Index: number;
}

const f = await (await fetch("https://sheetjs.com/pres.xlsx")).arrayBuffer();
const wb = read(f);
const data = utils.sheet_to_json<President>(wb.Sheets[wb.SheetNames[0]]);

Please let us know how to present this info in the documentation.

PS: the types are strictly informational, for TypeScript to verify usage. It does not guarantee that the rows contain the specified fields.

There is a typed variant for `sheet_to_json`: ```ts /** Converts a worksheet object to an array of JSON objects */ sheet_to_json<T>(worksheet: WorkSheet, opts?: Sheet2JSONOpts): T[]; ``` To use it, you need to specify the type in the function call itself: ```ts const rows: DataRow[] = XLSX.utils.sheet_to_json<DataRow>(ws); // ---------------------------------------------^^^^^^^^^ ``` This example is from [the VueJS page in the documentation](https://docs.sheetjs.com/docs/demos/vue#array-of-objects): ```ts import { read, utils } from 'xlsx'; interface President { Name: string; Index: number; } const f = await (await fetch("https://sheetjs.com/pres.xlsx")).arrayBuffer(); const wb = read(f); const data = utils.sheet_to_json<President>(wb.Sheets[wb.SheetNames[0]]); ``` Please let us know how to present this info in the documentation. PS: the types are strictly informational, for TypeScript to verify usage. It does not guarantee that the rows contain the specified fields.

sheetjs closed this issue

2022-11-30 05:42:23 +00:00

billmom commented

2022-11-30 06:15:51 +00:00

Thank you for the quick reply, but I don't think you understood my question or issue. The data is not an instanceof the Type you are passing in.

To illustrate my point, I took your President example as below.

export interface IPresident {
Name: string;
Index: number;
}
export class President implements IPresident {
Name: string;
Index: number;
}

const f = await (await fetch("https://sheetjs.com/pres.xlsx")).arrayBuffer();
const wb = XLSX.read(f);
const data = XLSX.utils.sheet_to_json< IPresident>(wb.Sheets[wb.SheetNames[0]]);
data instanceof President returns false. I would expect it to return true since I told it to be of type President

When I creat a new instance of President and assign the data to it as below
const typedData = new President();
Object.assign(typedData, data);

typedData instanceof President is now true. I would expect it to be true using the generic implementation, but it is not.

Thank you for the quick reply, but I don't think you understood my question or issue. The data is not an instanceof the Type you are passing in. To illustrate my point, I took your President example as below. export interface IPresident { Name: string; Index: number; } export class President implements IPresident { Name: string; Index: number; } const f = await (await fetch("https://sheetjs.com/pres.xlsx")).arrayBuffer(); const wb = XLSX.read(f); const data = XLSX.utils.sheet_to_json< IPresident>(wb.Sheets[wb.SheetNames[0]]); data instanceof President returns false. I would expect it to return true since I told it to be of type President When I creat a new instance of President and assign the data to it as below const typedData = new President(); Object.assign(typedData, data); typedData instanceof President is now true. I would expect it to be true using the generic implementation, but it is not.

sheetjs commented

2022-11-30 06:30:43 +00:00

sheet_to_json returns an array of plain JS objects. The type is informational:

const data = XLSX.utils.sheet_to_json<IPresident>(wb.Sheets[wb.SheetNames[0]]); // IPresident[]
const first_row = data[0]; // IPresident
console.log(first_row.Name); // this passes strict type check

What you are now asking for is for the ability to pass a constructor or base object when creating each row object. That is not supported.

You can map using your assign workaround to create the array of objects:

const raw_data = XLSX.utils.sheet_to_json<IPresident>(wb.Sheets[wb.SheetNames[0]]);
const data = raw_data.map(data => Object.assign(new President(), data));
console.log(data[0] == null || (data[0] instanceof President)); // true

`sheet_to_json` returns an array of plain JS objects. The type is informational: ```ts const data = XLSX.utils.sheet_to_json<IPresident>(wb.Sheets[wb.SheetNames[0]]); // IPresident[] const first_row = data[0]; // IPresident console.log(first_row.Name); // this passes strict type check ``` What you are now asking for is for the ability to pass a constructor or base object when creating each row object. That is not supported. You can map using your `assign` workaround to create the array of objects: ```ts const raw_data = XLSX.utils.sheet_to_json<IPresident>(wb.Sheets[wb.SheetNames[0]]); const data = raw_data.map(data => Object.assign(new President(), data)); console.log(data[0] == null || (data[0] instanceof President)); // true ```

Sign in to join this conversation.