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