CODEWITHSUNDEEP
javascriptexceloffice-js
j_unknown
j_unknown

Reputation: 55

excel javascript API array handling

I'm working on an add-in for excel 2016 using the javascript API. I can successfully get the range into an array and get the values to show in console.log. I've also been able to get the values into a JSON array using JSON.stringify();

I need to manipulate the array to remove the empty values (""). Can this be accomplished by using regular javascript array methods?

I'm thinking I can display the results back into a different worksheet using a similar approach like i did with var shWk

Here are some snippets of what I'm trying to do:

 (function () {
        "use strict";

        // The initialize function must be run each time a new page is loaded
        Office.initialize = function (reason) {
            $(document).ready(function () {
                app.initialize();
                //document.getElementById("date").innerHTML = Date("MAR 30 2017");
                $('#deleteTab').click(deleteTab);
                $('#preview').click(preview);
                $('#publish').click(publish);

            });
        };

        function preview() {
            Excel.run(function(ctx) {
              //getting the colname from a date range in B2
            var colName = ctx.workbook.worksheets.getItem('preview').getRange("B2");
            colName.load('values');
            return ctx.sync().then(function() {
              //converting colname value to string for column name
                var wkN = (colName.values).toString();
                // displaying on the task pane
                document.getElementById("tst").innerText = wkN;
                // testing to confirm i got the correct colname
                var shWk = ctx.workbook.worksheets.getItem('preview').getRange("B3");
                shWk.values = colName.values;
                //building the column connection by setting the table name located on a different worksheet 
                var tblName = 'PILOT_ZMRP1';
                var tblWK = ctx.workbook.tables.getItem(tblName).columns.getItem(wkN);         
                //loading up tblWK
                tblWK.load('values');
                return ctx.sync().then(function(){
                //this is where my question is:
                    var arry = tblWK.values;
                    for (var i=0; i < tblWK.length; i++){
                        if (tblWK.values !== ""){
                        arry.values[i][0]) = tblWK.values[i][0]
                        };
                    };
                    console.log(arry.length); //returns 185
                    console.log (arry.values);//returns undefined
                    tblWK.values = arry;
                    var tblWeek = tblWK.values;
                    console.log(tblWeek.length);//returns 185
                    console.log(tblWK.values);//returns [object Array]  [Array[1],Array[2]
               })              
            });
}).catch(function (error) {
	console.log(error);
    console.log("debug info: " + JSON.stringify(error.debugInfo));
});
}

What am I missing? Can you point me to some resources for javascript array handling in the specific context of office.js?

Upvotes: 0

Views: 820

Answers (3)

Ishu
Ishu

Reputation: 1

You can just fetch the values form the array that contains null by using for each and can can push the null values into another array.

Upvotes: -1

j_unknown
j_unknown

Reputation: 55

I want to thank everyone for the time spent looking at this question. This is my second question ever posted on Stack Overflow. I see that the question was not written as clear as it could've been. What i was trying to achieve was filtering out the values in a 1D array that had "". The data populating the array was from a column in a separate worksheet that had empty values (hence the "") and numeric values in it. the code below resolved my issue.

//using .filter()
        var itm = tblWK.values;
        function filt(itm){
            return itm != "";
        }
        var arry = [];
        var sht = [];
        var j=0;
        var s=0;
        arry.values = tblWK.values.filter(filt);
//then to  build the display range to show the values:

 for (var i=0; i < itm.length-1; i++) {
        if (tblWK.values[i][0]){
        var arry;    //tblWK.values.splice(i,0); -splice did not work, maybe my syntax was wrong?
        console.log("this printed: "+tblWK.values[i][0]);
        var cl = ('D'+i);          //building the range for display
        j++;                        //increasing the range
s=1;//setting the beignning range
        var cll = cl.toString();//getRange() must be a string
        console.log(cll);//testing the output
        }       
}
//using the variable from the for loop
      var cl = ('D'+s+':D'+j);
      var cll = cl.toString();
      console.log(cll);//testing the build string
      sht = ctx.workbook.worksheets.getItem('Preview').getRange(cll);
      sht.values = arry.values; //displays on the preview tab
      console.log (arry.values); //testing the output

The question was probably easier said by asking what vanilla javascript functions does office.js support. I found a lot help reading Building Office Add-ins using Office.js by Micheal Zlatkovsky and by reading the MDN documentation as well as the suggested answer posted here.

Regards, J

Upvotes: 1

Sudhi Ramamurthy
Sudhi Ramamurthy

Reputation: 2478

I'm not sure what this check is trying to achieve: tblWK.values !== "". .values is a 2D array and won't ever be "".

For Excel, the value "" means that the cell is empty. In other words, if you want to clear a cell, you assign to "". null value assignment results in no-op.

Upvotes: 0

Related Questions