Sunday 20 January 2013

Export List Data to Excel in SharePoint


​​Recently I came across a requirement where I had to export data of a list from inside a custom webpart to excel. After some research I came across many articles stating creating SPGridView and then using HTTPcontext Response object to write the response into an excel file.But out of curiosity I thought of diving into the OOB export functionality of SharePoint list. Firebug came to my support and I observed that when we click on the Export to Excel link in the SharePoint OOB list then it makes an RPC call using owssvr.dll. We can do a lot many things using this and exporting to excel is one of those.

Assuming an export button on the page and you need to export to excel on the click event of the same, you simply need to do the following

jQuery('.export').live('click', function (e) {

window.location = {site url} + "/_vti_bin/owssvr.dll?CS=109&Using=_layouts/query.iqy&List=" + {list guid} + "&View=" + {view guid} + "&CacheControl=1";

});

{site url} -  url of the site
{list guid} - GUID of the list
{view guid} - GUID of the view

You can programmatically fetch the list guid as well as view guid and then you are good to go.
Thats pretty much it and you will be able to export the data to excel :)

Cheers,
Geetanjali

2 comments: