CODEWITHSUNDEEP
exceljs-xlsx
Pallavi Prasad
Pallavi Prasad

Reputation: 587

unable to read empty cells reading excel file using js-xlsx

I have an excel file I am reading using js-xlsx. The code is working fine except where the cells are empty. These cells are ignored. How do I get these cells also when creating my JSON object?

I went through some of the question on SO as well as some other forums for the same problem but nothing satisfactory.

Any help would be welcome. My code is:

 reader.addEventListener('load', function(){
        var data = this.result;
        var wb = XLSX.read(data, {type: 'binary', sheetStubs:true});
        //  console.log(headers);

        wb.SheetNames.forEach(function(sheetName){
            //pulling out column headers for tablecreation
            var headers = get_header_row(wb.Sheets[sheetName]);
            createTableInDB(headers);
            // Here is your object
            var XL_row_object = XLSX.utils.sheet_to_json(wb.Sheets[sheetName]);
            //console.log(XL_row_object);

            for(var i=0; i<XL_row_object.length; i++){
                var json_object = XL_row_object[i];

                if(json_object !== null){
                    var dataobject = {
                        "tablename": tname,
                        "dbname": dbname,
                        "info": json_object,
                        "uname": uname
                    }

                    dataobject = $.toJSON(dataobject);

                    $.ajax({
                        type: "POST",
                        url: "insertIntoTable.php",
                        async: false,
                        data:"pInsertData=" + dataobject, 
                        success: function(msg){

                            console.log(msg);

                        }
                    });
                    //console.log(json_object);

                }

            }
        });
    });

    reader.readAsBinaryString(document.querySelector('input').files[0]);

The file is uploaded through an input in HTML. Thanks in Advance

Upvotes: 5

Views: 11745

Answers (3)

roxhens
roxhens

Reputation: 552

The library has an option for that. In your code below:

...
// Here is your object
var XL_row_object = XLSX.utils.sheet_to_json(wb.Sheets[sheetName]);
//console.log(XL_row_object);
...

You should provide the following option, to the options argument {defval: null} as follows:

...
// Here is your object
var XL_row_object = XLSX.utils.sheet_to_json(wb.Sheets[sheetName], {defval: null});
//console.log(XL_row_object);
...

Then, it should work.

Upvotes: 3

sachin kuravattigowdar
sachin kuravattigowdar

Reputation: 166

Just pass default value in sheet_to_json method:

var jsonObj = XLS.utils.sheet_to_json(data.Sheets[data.SheetNames[0]], {
                header: 0,
                defval: ""
              });

Upvotes: 15

Himanshu mishra
Himanshu mishra

Reputation: 33

Solution 1 .Condition "if(h===undefined)continue;" in "xlsx.core.min.js" comment it out.

or do it properly...

Solution 2 . By passing Condition extra param while running this XLSX.utils.sheet_to_json(wb.Sheets[name] , {blankCell : false}). add a condition on line no. 19150 "if(defval === undefined && blankCell) continue;" in file xlsx.js etc..

Upvotes: 0

Related Questions