sheet_to_json empty data #3002

New Issue

VitseA · 2023-10-06T14:09:00Z

VitseA commented

2023-10-06 14:09:00 +00:00

Hi every one,

I try to display an Excel file to HTML Table using SheetJS but i have an issue.

Sometimes and with some files, the convert end up at the end of the header. The datas are not displayed.

After checking the workbook features, the filter is not generated by Excel.

[{"Name":}] --> File without filter

[{"Name":"_xlnm._FilterDatabase","Sheet":0,"Hidden":true,"Ref":"Offre!$A$10:$N$10"}] --> File with filter

I have try the option : ws['!autofilter'] but it didn't works for in my case

`var reader = new FileReader();

        reader.readAsArrayBuffer(event.target.files[0]);

        reader.onload = function(event) {

            var data = new Uint8Array(reader.result);

            const wb = XLSX.read(data, {
                type: "array"
            });

            wb1 = JSON.stringify(wb);

            const wsname = wb.SheetNames[0];

            const ws = wb.Sheets[wsname];

            var range = XLSX.utils.decode_range(ws['!ref']);

            range.s.r = 9; // Ligne d'en tête

            range.s.c = 0; // Colonne de début

            range.e.c = 9; // Colonne de fin

            ws['!ref'] = XLSX.utils.encode_range(range);

            //alert(get_header_row(ws));

            const ws_data = XLSX.utils.sheet_to_json(ws, {
                header: 1
            });`

How can I apply auto filter when I import data ?

Thank in advance

Best regards

Alexandre

Hi every one, I try to display an Excel file to HTML Table using SheetJS but i have an issue. Sometimes and with some files, the convert end up at the end of the header. The datas are not displayed. After checking the workbook features, the filter is not generated by Excel. [{"Name":}] --> File without filter [{"Name":"_xlnm._FilterDatabase","Sheet":0,"Hidden":true,"Ref":"Offre!$A$10:$N$10"}] --> File with filter I have try the option : ws['!autofilter'] but it didn't works for in my case `var reader = new FileReader(); reader.readAsArrayBuffer(event.target.files[0]); reader.onload = function(event) { var data = new Uint8Array(reader.result); const wb = XLSX.read(data, { type: "array" }); wb1 = JSON.stringify(wb); const wsname = wb.SheetNames[0]; const ws = wb.Sheets[wsname]; var range = XLSX.utils.decode_range(ws['!ref']); range.s.r = 9; // Ligne d'en tête range.s.c = 0; // Colonne de début range.e.c = 9; // Colonne de fin ws['!ref'] = XLSX.utils.encode_range(range); //alert(get_header_row(ws)); const ws_data = XLSX.utils.sheet_to_json(ws, { header: 1 });` How can I apply auto filter when I import data ? Thank in advance Best regards Alexandre

sheetjs commented

2023-10-09 11:46:08 +00:00

Can you check the original sheet range and confirm that it covers the entire data range?

Ideally you could share a test file. If you can't do it publicly, send an email.

Can you check the original sheet range and confirm that it covers the entire data range? Ideally you could share a test file. If you can't do it publicly, send an email.

VitseA commented

2023-10-09 12:16:55 +00:00

Hi,

Thanks for your response.

you are right ( 2/3 files) return only header row (dont covers the entire data range)

When it's work :

[
["column1","column2","column3","column4","column5"],
["data-column1","data-column2","data-column3","data-column4","data-column5"],
["data-column1","data-column2","data-column3","data-column4","data-column5"],
]

When it's dont work :

[
["column1","column2","column3","column4","column5"]
]

Have you got any idea? I will try to share with you and example.

Hi, Thanks for your response. you are right ( 2/3 files) return only header row (dont covers the entire data range) When it's work : [ ["column1","column2","column3","column4","column5"], ["data-column1","data-column2","data-column3","data-column4","data-column5"], ["data-column1","data-column2","data-column3","data-column4","data-column5"], ] When it's dont work : [ ["column1","column2","column3","column4","column5"] ] Have you got any idea? I will try to share with you and example.

sheetjs commented

2023-10-09 12:18:17 +00:00

You can pass the option nodim: true OR use the recommendation in https://docs.sheetjs.com/docs/miscellany/errors#worksheet-only-includes-one-row-of-data

You can pass the option `nodim: true` OR use the recommendation in https://docs.sheetjs.com/docs/miscellany/errors#worksheet-only-includes-one-row-of-data

VitseA commented

2023-10-09 12:47:43 +00:00

You can pass the option nodim: true

It's solve my issue

Sincerely thank you

You can pass the option nodim: true It's solve my issue Sincerely thank you

sheetjs closed this issue

2023-10-09 13:29:31 +00:00

sibelius commented

2023-10-31 11:45:43 +00:00

is there any docs about nodim option?

sheetjs commented

2023-11-01 04:14:29 +00:00

The type definition describes the option as follows:

    /** If true, ignore "dimensions" records and guess range using every cell */
    nodim?: boolean;

Currently the option only applies to XLSX. We will go back and add support for the other file formats that allow self-reported ranges.

The [type definition](https://git.sheetjs.com/sheetjs/sheetjs/src/branch/master/types/index.d.ts#L241-L242) describes the option as follows: ```ts /** If true, ignore "dimensions" records and guess range using every cell */ nodim?: boolean; ``` Currently the option only applies to XLSX. We will go back and add support for the other file formats that allow self-reported ranges.

Sign in to join this conversation.