SharePoint List Export to Excel in Java Script Client Object Model using RPC (Owssvr.dll)

Hello guys,

Here I am explaining how to implement the OOB 'Export to Excel' feature of SharePoint list using RPC (Remote Procedure Calls) with Javascript CSOM code.

What is RPC :

This is a protocol from Windows SharePoint Services(WSS) , helps in exchanging the data between client and the server which runs with WSS. As all of you know the foundation for Sharepoint Server is came from WSS.

So for this kind of functionalities we can make use of this RPC. Here more about RPC from msdn.

Here I am using this below rpc url to generate the excel report of a list

 "/_vti_bin/owssvr.dll?CS=109&Using=_layouts/query.iqy&List={" + listId + "}" + "&View={" + viewId + "}&CacheControl=1";           

The parameters are List GUID and the View GUID.

Get List GUID :

Go to your list settings page: List Settingsà in Url you will find

http:// purnaexperiments:1234/_layouts/15/listedit.aspx?List=%7BCEB4F6D3%2D06AC%2D43FF%2DA95E%2DA158144585A3%7D

Replace  %7B with “{“,

             %2D with “–“ and

             %7D with “}”

After this the you get the GUID like ={CEB4F6D3-06AC-43FF-A95E-A158144585A3}.

Get View Guid :

Go to List Viewà Click Modify this ViewàYour url will look like this follow url

http://purnaexperiments:1234/_layouts/15/ViewEdit.aspx?List=%7BCEB4F6D3%2D06AC%2D43FF%2DA95E%2DA158144585A3%7D&View=%7BE42BBCD5%2D8A0F%2D4092%2D8B3D%2D52D9EF805AAB%7D

Copy the value after the string ‘View’ in above url and

Replace  %7B with “{“,

             %2D with “–“ and

             %7D with “}”

After this the you get the View GUID like ={ E42BBCD5-8A0F-4092-8B3D-52D9EF805AAB}.

In below code I am getting the LIST GUID and View GUID from their names. 

You can use this function directly to generate the report with List Title and View Title

Code :

1.   <input id="btnGenerate" onclick=" GeneratePCMReport('MyCustomList', 'All Items’);" value="Generate Excel Report" /> 
2. // Here the list name is 'MyCustomList' and the view name is ‘All Items’ 
3. 
   
4.     <script type="text/javascript">    
5.         var exporturl = null;
6.         var listPages = null;
7.         var view = null;
8.         var web = null;
9.         function GeneratePCMReport(listName, viewTitle)         
10.         { 
11.             if (navigator.appName == "Microsoft Internet Explorer") 
12.                {
13.                 var context = new SP.ClientContext.get_current();
14.                 web = context.get_web();
15.                 context.load(web);
16.                 listPages = context.get_web().get_lists().getByTitle(listName);
17.                 context.load(listPages);
18.                 view = listPages.get_views().getByTitle(viewTitle);
19.                 context.load(view);
20.                 context.executeQueryAsync(getlistInfoSuccess, getlistInfoFailed);
21.                 }
22.             else {
23.                 alert("Please use Internet Explorer to generate the Excel Report");
24.             }
25.         } 
26.         function getlistInfoSuccess(sender, args)
27.         {
28.             var listId = listPages.get_id();
29.             var viewId = view.get_id();
30.             exporturl = web.get_url() + "/_vti_bin/owssvr.dll?CS=109&Using=_layouts/query.iqy&List={" + listId + "}" + "&View={" + viewId + "}&CacheControl=1";          
31.             window.location.href = exporturl;
32.         }
33.         function getlistInfoFailed(sender, args) 
34.          {
35.             alert('Issue in getting the Report, Please try later')
36.          } 
37.     </script>

Note : As you know the oob  'Export to Excel' works only in IE, the above code also works only in IE.

Since we are calling the same oob function through csom code. That is the reason i have included a condition (navigator.appName == "Microsoft Internet Explorer") in above code to check the browser type.

Thanks,

Purna

Getting Data from Managed Metadata Column using JavaScript Client Side Object Model

Hello,

In this post I am explaining a small topic, how to get data from managed metadata filed using JavaScript client object model.

The reason to write this post is, generally if it is multi data column we check the count of the items and loop through it to get the values of it. But here with meta data columns the syntax is little different, we need to use child item count and loop through it and get the label property of it.

Generally we contain two types of settings for Manged Metadata column

Single valued Metadata Column (setting: Allow Multiple Values – False) : This contains single value

Multivalued Metadata Column (setting:    Allow Multiple Values – True)  : This contains single/multiple values.

Code to get the data from Singlevalued Metadata Column

Here the column name is 'ProductName'

//Single Valued Metadata column

var resultProductName = "";

        var _ProductName =  listItem.get_item('ProductName')

        if (typeof _ProductName !== 'undefined' && _ProductName !== null) 

        {

            resultProductName = listItem.get_item('ProductName').Label;

        }

  alert("Product Name " + resultProductName);

Code to get the data from Multivalued Metadata Column

  //Multi Valued Metadata column with single/multiple values

Here the column name is 'ProductColors'

 var resultColorString = "";

      

        var _ProdColors = listItem.get_item('ProductColors');    

        if (typeof _ProdColors !== "undefined" && _ProdColors !== null) {

            if (_ProdColors._Child_Items_.length > 0)

            {

                if (_ProdColors._Child_Items_.length == 1)// If single value

                {

                    var color = _ProdColors._Child_Items_[0].Label;

                    resultColorString = color;                  

                }

                else// If multi value

                {

                    for (var i = 0; i < _ProdColors._Child_Items_.length ; i++)

                    {

                        var multicolor = _ProdColors._Child_Items_[i].Label;

                        if (multicolor != null) {

                            resultColorString = resultColorString + "," + multicolor;

                        }

                    }

                    if (resultColorString.length > 0) {

                        resultColorString = resultColorString.substring(1, resultColorString.length);

                    }                   

                }

            }

        }    

       alert('color(s) ' + resultColorString)

Hope it saves some one's time :)

Thanks,

Purna