roa.length is coming 0 while XLSX file having data #776

New Issue

2017-08-11T14:57:42Z

ujjwalkolkata commented

2017-08-11 14:57:42 +00:00

(Migrated from github.com)

Hi Expert, I am trying to do client side validation for excel sheet. I am using MVC .net project . I am using Jquery 3.1.1 is my client side validation.

Step 1. Under document.ready
$('#flWarrantyProduct').change(handleFile);
Step 2.

function handleFile(e) {
            //Get the files from Upload control
            var files = e.target.files;
            var i, f;
            //Loop through files
            debugger;
            for (i = 0, f = files[i]; i != files.length; ++i) {
                var reader = new FileReader();
                var name = f.name;
                var file = e.target.files[0];
                
                debugger;
                reader.onload = function (e) {
                    var data = e.target.result;
                    debugger;
                    var result;
                    var workbook = XLSX.read(data, { type: 'binary' });

                    var sheet_name_list = workbook.SheetNames;
                        sheet_name_list.forEach(function (y) { /* iterate through sheets */
                        //Convert the cell value to Json
                        debugger;
                        var roa = XLSX.utils.sheet_to_json(workbook.Sheets[y]);
                       
                        if (roa.length > 0) {
                            debugger;
                            result = roa;
                        }
                   });
                    //Get the first column first cell value
                    alert(result[0].Column1);
                };
                reader.readAsDataURL(e.target.files[0]);
            }
        }

Now, It is not going under if (roa.length > 0) beacuse roa.length is coming 0.

I am stuck in here.

Can you please help me.

Hi Expert, I am trying to do client side validation for excel sheet. I am using MVC .net project . I am using Jquery 3.1.1 is my client side validation. Step 1. Under document.ready ` $('#flWarrantyProduct').change(handleFile);` Step 2. ``` function handleFile(e) { //Get the files from Upload control var files = e.target.files; var i, f; //Loop through files debugger; for (i = 0, f = files[i]; i != files.length; ++i) { var reader = new FileReader(); var name = f.name; var file = e.target.files[0]; debugger; reader.onload = function (e) { var data = e.target.result; debugger; var result; var workbook = XLSX.read(data, { type: 'binary' }); var sheet_name_list = workbook.SheetNames; sheet_name_list.forEach(function (y) { /* iterate through sheets */ //Convert the cell value to Json debugger; var roa = XLSX.utils.sheet_to_json(workbook.Sheets[y]); if (roa.length > 0) { debugger; result = roa; } }); //Get the first column first cell value alert(result[0].Column1); }; reader.readAsDataURL(e.target.files[0]); } } ``` Now, It is not going under if (roa.length > 0) beacuse roa.length is coming 0. I am stuck in here. Can you please help me.

ujjwalkolkata commented

2017-08-11 16:05:26 +00:00

(Migrated from github.com)

If I am using 'base64' in this below line
var workbook = XLSX.read(data, { type: 'binary' });
I am getting data but not human readable.

If I am using '**base64**' in this below line ` var workbook = XLSX.read(data, { type: 'binary' }); ` I am getting data but not human readable.

reviewher commented

2017-08-11 16:13:38 +00:00

(Migrated from github.com)

First: You are using readAsDataURL. See https://github.com/SheetJS/js-xlsx/issues/535#issuecomment-281454900 for a comment on how to handle it, but you are better off switching to readAsBinaryString or readAsArrayBuffer.

Second: Assuming the file is properly parsed, if your worksheet starts with an empty row or a weird title cell, the sheet_to_json utility may not find your data table. Try generating an array of arrays:

var roa = XLSX.utils.sheet_to_json(workbook.Sheets[y], {header:1});

First: You are using `readAsDataURL`. See https://github.com/SheetJS/js-xlsx/issues/535#issuecomment-281454900 for a comment on how to handle it, but you are better off switching to `readAsBinaryString` or `readAsArrayBuffer`. Second: Assuming the file is properly parsed, if your worksheet starts with an empty row or a weird title cell, the `sheet_to_json` utility may not find your data table. Try generating an array of arrays: ```js var roa = XLSX.utils.sheet_to_json(workbook.Sheets[y], {header:1}); ```

ujjwalkolkata commented

2017-08-11 18:13:55 +00:00

(Migrated from github.com)

Thanks reviewher,
I have changed my code but not able to get column wise data. ( Like A2, A3, B2, B3). How can we track data is coming from which column? I have changed my code in following way.

 function handleFile(e) {
            var files = e.target.files, file;
            debugger;
            if (!files || files.length == 0) return;
            file = files[0];
            var fileReader = new FileReader();
            fileReader.onload = function (e) {
                var filename = file.name;
                // pre-process data
                var binary = "";
                var bytes = new Uint8Array(e.target.result);
                var length = bytes.byteLength;
                for (var i = 0; i < length; i++) {
                    binary += String.fromCharCode(bytes[i]);
                }
                debugger;
                // call 'xlsx' to read the file
                var length = XLSX.read(binary, { type: 'binary', cellDates: true, cellStyles: true }).Strings.length;
                if (length > 0) {
                    var header = XLSX.read(binary, { type: 'binary', cellDates: true, cellStyles: true }).Strings[0].h;
                }
                if (length > 1) {                    
                    var dataFirstCell = XLSX.read(binary, { type: 'binary', cellDates: true, cellStyles: true }).Strings[1].h;
                }
                if (length == 0)
                {
                    alert("Column A1 doesnot have any data in spreadsheet. Please check you attached Spreadsheet.");
                    return false;
                }
                if (length == 1) {
                    alert("Column A1 Style should have data to add under respective Warranaty Type. Please check you attached Spreadsheet.");
                    return false;
                }
                if(header!="Style")
                {
                    alert("Column A1 Style name should not be chaned");
                    return false;
                }
                return true;
            };
            fileReader.readAsArrayBuffer(file);
        }

Thanks reviewher, I have changed my code but not able to get column wise data. ( Like A2, A3, B2, B3). How can we track data is coming from which column? I have changed my code in following way. ``` function handleFile(e) { var files = e.target.files, file; debugger; if (!files || files.length == 0) return; file = files[0]; var fileReader = new FileReader(); fileReader.onload = function (e) { var filename = file.name; // pre-process data var binary = ""; var bytes = new Uint8Array(e.target.result); var length = bytes.byteLength; for (var i = 0; i < length; i++) { binary += String.fromCharCode(bytes[i]); } debugger; // call 'xlsx' to read the file var length = XLSX.read(binary, { type: 'binary', cellDates: true, cellStyles: true }).Strings.length; if (length > 0) { var header = XLSX.read(binary, { type: 'binary', cellDates: true, cellStyles: true }).Strings[0].h; } if (length > 1) { var dataFirstCell = XLSX.read(binary, { type: 'binary', cellDates: true, cellStyles: true }).Strings[1].h; } if (length == 0) { alert("Column A1 doesnot have any data in spreadsheet. Please check you attached Spreadsheet."); return false; } if (length == 1) { alert("Column A1 Style should have data to add under respective Warranaty Type. Please check you attached Spreadsheet."); return false; } if(header!="Style") { alert("Column A1 Style name should not be chaned"); return false; } return true; }; fileReader.readAsArrayBuffer(file); } ```

reviewher commented

2021-09-19 00:37:04 +00:00

(Migrated from github.com)

Pass the option header: 1 to get the raw values from the worksheet. If the cells in the range are empty, use update_sheet_range to recalculate worksheet range.

If the worksheet is still empty, pass the option WTF: 1 to see if there are errors.

Please reopen the issue if this persists.

Pass the option `header: 1` to get the raw values from the worksheet. If the cells in the range are empty, use [`update_sheet_range`](https://github.com/SheetJS/sheetjs/wiki/General-Utility-Functions#updating-worksheet-range) to recalculate worksheet range. If the worksheet is still empty, pass the option `WTF: 1` to see if there are errors. Please reopen the issue if this persists.

Sign in to join this conversation.