Categories
Skip Navigation Links

SharePoint - Export List View to Excel Programmatically

Most of the developers know that we can export SharePoint List view data to Excel using an OOB feature provided by SharePoint. In this article, we will go through the program to export SharePoint data to Excel using JavaScript.


Requirement: You have a custom page where you want to put a hyperlink and when end-user clicks on that link, the data from a specific list view should get exported into the excel file. Let’s see how we can achieve this programmatically.

In this section, we will check what exactly SharePoint requests to the server when we click on Export To Excel OOB Link from a list view.

  1. Open your SharePoint List in SharePoint Classic Mode (Not in Modern Site).
  2. Here, you will see Ribbon and the Export to Excel link.
  3. Now, press F12 or open the developer tools.
  4. Go to Network Tab.
  5. Now, click on the Export to Excel from Ribbon.
  6. This moment you can catch the request URL in the network tab, made by this click.
  7. You can also use the Fiddler tool to check the request.

Now, we need to create a URL like this you see in the network tab.

export to excel in sharepoint

Form an Export To Excel URL

Below is the syntax of the URL that we got from the OOB Export to excel request. We need to form a URL like this using JavaScript.

<<SiteAbsoluteUrl>>/_vti_bin/owssvr.dll?CS=65001&Using=_layouts/15/query.iqy&List=<<ListGUID>>&View=<<ListVIewGUID>>

  • SiteAbsoluteUrl – Absolute URL of the Site. You can use _spPageContextInfo object to get the absolute URL directly.
  • ListGUID – You need List GUID on which you are working. In this tutorial we have got the List GUID using JSOM so you only need to pass List Name in the example given below.
  • ListVIewGUID – You need to pass View GUID that you are working on. In the code below, we haven’t provided the code to get view GUID but we have provided 2 options in the code comment. Also, you can refer to some JSOM Methods for SharePoint List View which will help you here.
  • Put a Content Editor Web part and a Script Editor Web Part on the page.
  • Webpart page content editor and scipt editor
  • Add below Html in Content Editor webpart.
  • <button onclick="ExportToExcel();" type="button">Click mebutton>

  • And below references in Script Editor WebpartScriptFile.js is a JavaScript file where you can keep below function ExportToExcel. i.e. your actual logic.
  • <script src="https://MyServer/sites/SiteCollection/style library/js/ScriptFile.js">script>

  • Once you are done with setting your page, click on the button and your method should get executed.
  • // Function to handle export to excel functionality
  • function ExportToExcel() {
  •  
  •     // Here, we need the List VIEW ID.
  •     // Option 1: If you have the list view added on the same page, you can retrieve the View ID using DOM Manipulation
  •     // Option 2: If you know the view name and list name, you can get the list view id using coding.
  •     var strViewId = 'ViewId';
  •  
  •     //obtaining attributes to build ows query
  •     var url = _spPageContextInfo.siteAbsoluteUrl;
  •     var owasr1 = '/_vti_bin/owssvr.dll?CS=65001&Using=_layouts/15/query.iqy&List={';
  •     var owasr2 = '}&View={';
  •  
  •     var context = new SP.ClientContext.get_current();
  •     var web = context.get_web();
  •  
  •     var mListName = "List Name";
  •     var mList = web.get_lists().getByTitle(mListName);
  •  
  •     //call to get list id param
  •     context.load(mList);
  •  
  •     context.executeQueryAsync(onQuerySucceeded, onQueryFailed);
  •  
  •     function onQuerySucceeded() {
  •         var idList = mList.get_id();
  •  
  •         // SYNTAX: <<SiteAbsoluteUrl>>/_vti_bin/owssvr.dll?CS=65001&Using=_layouts/15/query.iqy&List=<<ListGUID>>&View=<<ListVIewGUID>>
  •         var qstring = url + owasr1 + idList + owasr2 + strViewId + '}';
  •         window.location.replace(qstring);
  •     }
  •  
  •     function onQueryFailed(args) {
  •         var message = args.get_message();
  •         ErrorMessage("Facing Issue: " + message);
  •     }
  • };

Let us know in the comment section whether you were aware of this trick or not.


We value your Feedback:

Page URL:

Name:

 

Email:

 
 

Suggestion:

 

© 2021 Code SharePoint