Converting Excel sheet into JSON and sending data to SharePoint list---getting an empty object instead

user_10654347 0
Viewed 1073

I'm working on a project that is supposed to take an uploaded excel file, convert it to JSON, and POST that parsed info into a SharePoint list. The POST request is working---when I reload the SharePoint list the uploaded item is there, but it's coming back as blank.

Long story short, the data that's supposed to be parsed is shown to be an empty object.

I also noticed that in the excel parsing function, the majority of the code is getting skipped over and I'm not sure why. In the code below I've listed where it happens.

What parameters do I need to pass into xl2String (see below)?

HTML:

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
<script lang="javascript" src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.15.5/xlsx.full.min.js"></script>

<input class="getFile" id="getFile" type="file" name="files[]" multiple="multiple"/><br />

JS:

<script>
    const ExcelToJSON = function(files) {

        this.parseExcel = function(files) {
            let reader = new FileReader();

            reader.onload = function(e) { 
                let data = e.target.result; // this code is NOT reached
                    console.log("data: ", data)
                let workbook = XLSX.read(data, { type: 'binary' });

                workbook.SheetNames.forEach((sheetName) => {
                    let XL_row_object = XLSX.utils.sheet_to_json(workbook.Sheets[sheetName]);
                    let json_object = JSON.stringify(XL_row_object);

                    console.log(JSON.parse(json_object))

                    $('#jsonData').val(json_object);
                })

            } // reader.onload // debugger skips over

            reader.onerror = function(err) {
                console.log(err);
            }

            // reader.readAsBinaryString(files); // tells JS to start reading the file
        } // this.parseExcel

    } // ExcelToJSON

    function handleFileSelect(evt) {
        let files = evt.target.files; // FileList object

            let xl2json = new ExcelToJSON(files);
                    xl2json.parseExcel(files[0]);
                    let xl2String = JSON.stringify(xl2json);

            let addlistItem = new AddItemToSPList();
                AddItemToSPList(xl2String[0].Title, xl2String[0].Location);
                console.log(xl2String) // empty obj...

//      let filteredByLocation = xl2json.map(e => e.Location == "New York");

        console.log(xl2String.Title); // empty obj

    }

    const AddItemToSPList = function(title, location) {
        let holidaysColumns = {
            "__metadata":{"type": "SP.Data.holidaysListItem"},
      "Title": title,
            "Location": location
        };

        let listName = "Test%20Holidays";
        return fetch("www.url.com/gc/_api/web/lists/getbytitle('" + listName + "')/items", {
            method: "POST",
            credentials: 'same-origin',
            body: JSON.stringify(holidaysColumns),
            headers: { 
                // etc            
            }
        }).then((response) => {
            $(".uploadSuccessFail").html("File was upload successfully.")
            console.log(response)
        }).catch((err) => {
            $(".uploadSuccessFail").html("Error: File upload unsuccessful.")
            console.log("Error: " + err);
        })
    }

</script>


<script>
    document.getElementById('getFile').addEventListener('change', handleFileSelect, false);
</script>
xlsx sharepoint rest jquery javascript
1 Answers

I am a little confused by the syntax you are using, you have defined ExcelToJSON as an object constructor, but it only contains a single method that doesn't even use the parameter that was passed to either the constructor or the method. The onLoad is never getting executed, because the reader.readAsBinaryString is commented out, which is the method that triggers the file loading. You are trying to capture xl2json from parseExcel but the file is parsed asynchronously, so you will need to use the json within the onLoad and save your item to SharePoint from within that callback. Then you are treating AddItemToSPList as if it is also a constructor, but it is just a function, so likely won't do anything.

I don't have your XLSX utility, so I cannot execute the code, but this should work:

<script>
    function parseAndUploadFile(file) {
        let reader = new FileReader();

        reader.onload = function(e) { 
            let data = e.target.result; // this code is NOT reached
                console.log("data: ", data)
            let workbook = XLSX.read(data, { type: 'binary' });

            workbook.SheetNames.forEach((sheetName) => {
                let XL_row_object = XLSX.utils.sheet_to_json(workbook.Sheets[sheetName]);
                let json_object = JSON.stringify(XL_row_object);

                //console.log(JSON.parse(json_object))

                //$('#jsonData').val(json_object);

                if (XL_row_object && XL_row_object.Title && XL_row_object.Location) {
                    addItemToSPList(XL_row_object.Title, XL_row_object.Location);
                } else {
                    console.log('parsed json does not appear to have necessary values: ')
                    console.log(XL_row_object);
                }
            })

        } // reader.onload

        reader.onerror = function(err) {
            console.log(err);
        }

        reader.readAsBinaryString(files); // tells JS to start reading the file
    } // ExcelToJSON

    function addItemToSPList(title, location) {
        let holidaysColumns = {
            "__metadata":{"type": "SP.Data.holidaysListItem"},
            "Title": title,
            "Location": location
        };

        let listName = "Test%20Holidays";
        return fetch("www.url.com/gc/_api/web/lists/getbytitle('" + listName + "')/items", {
            method: "POST",
            credentials: 'same-origin',
            body: JSON.stringify(holidaysColumns),
            headers: { 
                // etc            
            }
        }).then((response) => {
            $(".uploadSuccessFail").html("File was upload successfully.")
            console.log(response)
        }).catch((err) => {
            $(".uploadSuccessFail").html("Error: File upload unsuccessful.")
            console.log("Error: " + err);
        })
    }

    function handleFileSelect(evt) {
        let files = evt.target.files; // FileList object

        if (files.length > 0) {
            parseAndUploadFile(files[0]);
        }
    }
</script>


<script>
    document.getElementById('getFile').addEventListener('change', handleFileSelect, false);
</script>
user_28249140
Related