Share via

SharePoint 2013: Export to Excel using REST API

  • Article

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,

https://howtodowithsharepoint.files.wordpress.com/2015/05/11.png?w=450&h=225

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:

https://howtodowithsharepoint.files.wordpress.com/2015/05/21.png?w=450&h=90

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”.

https://howtodowithsharepoint.files.wordpress.com/2015/05/31.png?w=450&h=236

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

https://howtodowithsharepoint.files.wordpress.com/2015/05/41.png?w=450&h=167

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:

https://howtodowithsharepoint.files.wordpress.com/2015/05/51.png?w=450&h=125

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

https://howtodowithsharepoint.files.wordpress.com/2015/05/61.png?w=450&h=254

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-

https://howtodowithsharepoint.files.wordpress.com/2015/05/71.png?w=450&h=233

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

https://howtodowithsharepoint.files.wordpress.com/2015/05/81.png?w=450&h=255

https://howtodowithsharepoint.files.wordpress.com/2015/05/91.png?w=450&h=89

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

https://howtodowithsharepoint.files.wordpress.com/2015/05/10.png?w=450&h=255

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 step 2 of getViewAsHTML() function above:

https://howtodowithsharepoint.files.wordpress.com/2015/05/111.png?w=450&h=98

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

https://howtodowithsharepoint.files.wordpress.com/2015/05/12.png?w=450&h=236

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:

https://howtodowithsharepoint.files.wordpress.com/2015/05/13.png?w=450&h=89

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

https://howtodowithsharepoint.files.wordpress.com/2015/05/14.png?w=450&h=212

Save the Query File

https://howtodowithsharepoint.files.wordpress.com/2015/05/15.png?w=450&h=210

Open the Query File and Enable the Data Connection with SharePoint

https://howtodowithsharepoint.files.wordpress.com/2015/05/16.png?w=450&h=177

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

https://howtodowithsharepoint.files.wordpress.com/2015/05/17.png?w=437&h=450

https://howtodowithsharepoint.files.wordpress.com/2015/05/18.png?w=450&h=371

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

Hope this will help someone in need.