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.

1

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:

2

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

3

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

4

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:

5

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

6

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-

7

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

8

9

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

10

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:

11

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

12

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:

13

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

14

Save the Query File

15

Open the Query File and Enable the Data Connection with SharePoint

16

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:

17

18

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…

Advertisements

16 thoughts on “SharePoint 2013: Export To Excel Using REST API

  1. Indeed helpful article and nice approach. However, we need to make sure that either user who is running the code or App in which we implement this logic, should require contribution permission on the respective list…

  2. Gaurav,

    Thanks for your feedback.

    The contribute permission will be needed only if you need to create List Views on the fly as it is shown in the above post (Which is again a optional choice), else just to query the data using export will need only Read Permission on the respective list.

    • Harsh,

      Currently the source is not hosted,but you can share your Email id with me and will share the code with you.

      Else code chunks are displayed in the post can help you to write on your own.

  3. Can you do this with the attachments included in the excel document? So I am looking to do the same thing, but under each item that I get, I also want to look to see if an attachment was uploaded with item, and if it was, then I need to get the contents of that excel document and add it to the exported excel document that I just created.

  4. I try your code, but when open excel file I get threshold error. My column where apply the filter is indexed. so i dont understand why i get this error.

  5. Hey, thank you for this tutorial! I think it’s helped me as a starting base for a requirement. Would you be able to point me in the right direction for how to make a REST call to ALL Document Libraries in a site collection and place all of those files into a view to export to excel? Thanks!

  6. Hi I AM GETTING FORBBIDDON ERROR 403
    I have also tried with request digest control value, but no use.
    any help would be great.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s