SharePoint 2013: Export To Excel Using REST API

In this blog post we will discuss how can we export the SharePoint List Data using a custom solution approach utilizing SharePoint REST API.

Though this feature is also available OOB through SharePoint List/Libraries Ribbon Control, but limited under the scenarios mentioned below.

This solution can be utilized to overcome the OOB limitations under following scenarios:

1. When we need to trigger export process on a custom events
2. When we need to export filtered set of data at runtime
3. When we need to automate the export process by specifying batch size on the fly

In order to demonstrate the solution I have setup the environment as below:

1. Create a Large List with 10,000 Items
2. Create a new WebPart Page with Content Editor WebPart added to it with a text file referenced from Site Assets Library. This page will take care of Presentation part of the solution, which is quite simple in this case.
3. Add a Text File to Site Assets Library to which we will add the necessary code to achieve this solution.

Once the list is created and items are added to it, it will look like as below.

Please note that I intentionally keep the number of items more than the list default threshold limits (5000 items) in order to make sure that this solution remains workable even if the list threshold has been crossed.

Also notice that we only have one View (All Items) which is created already OOB as shown below:

Now first of all we need to analyze the super simple UI that we have created using WebPart Page.

This UI has got one Button “Create & Export List View”.

Now let’s look for the HTML that builds this super simple UI.

1. “viewTitle” Div: Will display the title that we want to show while rendering the List View on this Page.
2. “printView” Div: Will display the List View which will render as HTML
3. “Create & Export List View” Button: Will Trigger the following actions:
   1. Create a new View in SharePoint List
   2. Export View Data to Excel Sheet
   3. Render View as HTML on the Custom UI

Next thing is to talk about the core plumbing that will do all the magic.

We need to add the JQuery reference to our JS File as shown below:

In our JS Code we will be having two functions:

CreateViews: This function will create a new view based on the filter query and row limits.

Let’s analyze the code for this function to understand the different sections as shown below:

1. Request Headers: Specify the needed Request Headers for the REST Call.
2. View Name: Generate Dynamic View Name
3. URL: Prepare URL for REST Call to query the List Views
4. REST Call Body To Created New View in SharePoint List:
   1. Specify Metadata used to create the SPView
   2. Specify View Name
   3. Specify if this is Personal View
   4. Specify the ViewQuery to get the filtered records in the View
   5. Specify the RowLimit to specify the maximum number of records that a view can hold. (I have specified 10,000)
   6. Call Success: Execute another method to Export and Render the List as HTML

We got another function “getViewAsHTML“ which will be executed if the above call gets successful.

In this function we got two noteworthy things:

1. Calling renderAsHTML() Function : This function will take care the rendering of List Views as HTML.
2. Export URL: This is a bit of tricky to get prepared the Export URL but fortunately with a very simple solution as explained below-

The simple solution to this tricky problem is to issue a dummy request to SharePoint to export the list data using following steps:

1. Launch Fiddler
2. Navigate to SharePoint List
3. Click on Export to Excel Ribbon Command Button
4. Analyze the request intercepted by Fiddler

Copy the URL issued to SharePoint by Command Button and analyze it.

On analysis we found that we can build this URL dynamically by providing List ID and View ID at runtime and that’s exactly what we are doing in the step 2 of getViewAsHTML() function above:

And that’s it.

We are all done with the functionality, now it is the time to test all our hard work.

Now launch the WebPart Page with our UI present on it.

Click “Create & Export List View” button

And sure enough once the execution is completed we will see three actions occur as follows:

1. A new View has been created in the list as shown below:

1. List View gets Render as HTML in “printView” Div as we discussed above
2. Export Query File is ready to be downloaded

Save the Query File

Open the Query File and Enable the Data Connection with SharePoint

And we will get all items hold by the view based on the query specified, in this current View I took all 10,000 items as shown below:

Though this is quite a simple approach which can be used to easily cater the custom data export requirements in SharePoint.

Code Snippet:

/sites/dev01/Shared%20Documents/jquery-1.7.1.min.js



function getViewAsHtml(viewId, viewTitle)
 {

var headers = {
 Accept: "application/json;odata=verbose",
 "Content-Type": "application/json;odata=verbose",
 "X-RequestDigest": jQuery("#__REQUESTDIGEST").val()
 };

var call = jQuery.ajax({
 url: _spPageContextInfo.webAbsoluteUrl
 + "/_api/Web/Lists/getbytitle('TestList')/Views('"+viewId+"')/renderAsHtml()",
 type: "GET",
 data: "json",
 headers: headers
 });

call.done(function(data, textStatus, jqXHR)
 {
 $('#viewTitle').html("
"+viewTitle +"
");
 $('#printView').html(data.d.RenderAsHtml);
 window.location=_spPageContextInfo.webAbsoluteUrl + "/_vti_bin/owssvr.dll?CS=65001&Using=_layouts/15/query.iqy&List=%7B00361C78%2D4158%2D4A9B%2DA12D%2D8C55DE4E9079%7D&View=%7B"+viewId +"%7D&RootFolder=%2Fsites%2Fdev01%2FLists%2FTestList&CacheControl=1";
 });

call.fail(failHandler);

function failHandler(jqXHR, textStatus, errorThrown) {
 var response = JSON.parse(jqXHR.responseText);
 var message = response ? response.error.message.value : textStatus;
 alert("Call failed. Error : " + message);
 }
 }

function createViews()
 {

var headers = {
 Accept: "application/json;odata=verbose",
 "Content-Type": "application/json;odata=verbose",
 "X-RequestDigest": jQuery("#__REQUESTDIGEST").val() 
 };

var viewName = "By Status" + Math.floor((Math.random() * 100) + 1);
 var call = jQuery.ajax({
 url: _spPageContextInfo.webAbsoluteUrl
 + "/_api/Web/Lists/getbytitle('TestList')/Views",
 type: "POST",
 data: JSON.stringify({
 "__metadata": { type: "SP.View" },
 Title: viewName,
 'PersonalView': false,
 'ViewQuery':'',
 'RowLimit': 3
 }),
 headers: headers
 });

call.done(function(data, textStatus, jqXHR)
 {
 getViewAsHtml(data.d.Id, data.d.Title);
 });

call.fail(failHandler);

function failHandler(jqXHR, textStatus, errorThrown) {
 var response = JSON.parse(jqXHR.responseText);
 var message = response ? response.error.message.value : textStatus;
 alert("Call failed. Error : " + message);
 }
 }

$(document).ready(function()
 {
 //getViewAsHtml();
 });













 <br/>
 <br/>
 <br/>
 

 
 

 

Hope this will help someone in need…

• Export To Excel
• REST API
• REST Client
• SharePoint 2013 REST API