Thursday, December 31, 2009

Creating a Export to Excel link in SharePoint

Hi All,

I came across to an interesting query. When we open a document library or a list AllItems.aspx we have an option to export the data into spreadsheet. However there is no direct link given. We do not have any URL for the Export to Excel option that comes under actions menu.

However, when you want to place a link for Export to excel in your web part or your webpage, then here is a way to go for it.

{siteurl}/_vti_bin/owssvr.dll?CS=109&Using=_layouts/query.iqy&List={GUID of list}&View={GUID of view}&CacheControl=1

As you can see all you need to do is replace the respective place holders.

{siteurl} is your site url.

{GUID of list} is the GUID of the list for which you want to export the data to spreadsheet.

{GUID of view} is the GUID of the view for which you want to export the data to spreadsheet.

Where will you get these GUIDs? Well, you will find it when you click on List Settings.



And then click on the view name for which you want the data



You can use the same formula at places where there is no option to export to spreadsheet. For example, if you see on people and group permission page, you will not find this. You the same formula and you will be able to export it to the spreadsheet.

That’s it. Your job is done.

16 comments:

AvibaD said...

Hi,
this seems to be a very great functionality and very beneficial for me if it works. But it’s giving me this problem:

"The following file(s) have been blocked by the administrator: /sites/saybolt/ipteam/demotest/vti_bin/owssvr.dll"
How can I get solution of this? Or there any work around of I can’t get rights to owssvr dll file?

SharePoint Kings said...

Avibad,

Have you tried with admin rights on SharePoint site and your local PC?

we are not sure whether owssvr.dll required any permission or not because it generally. generally this URL is used by sharepoint internally and it does not required any permission.

there might be some network problem or so.

Sebastian Atar said...

Avibad,
I had the same issue then I realized there is a typo (it is missing an underscore before "_vti_bin" in the tutorial.

It should be:

{siteurl}/_vti_bin/owssvr.dll?CS=109&Using=_layouts/query.iqy&List={GUID of list}&View={GUID of view}&CacheControl=1

I used SharePoint Manager to pull the GUIDs of my list and view and it worked.

SharePoint Kings said...

Sebastian is right

that's might be because of typo mistake

thanks Sebastian.

Mike said...

This is very useful.
I also found out the CS=109 varies depends on the type of SharePoint list. For any Tracking list (e.g. tasks, survey) or Communication list (e.g. announcements, contacts) the CS value 65001.

Karma said...

This dont work in SAFARI any know how fix it?

Regards.-

SharePoint Kings said...

Karma,
not tried in safari.

John said...

Awesome! It works great! Just what I needed. Thanks for posting.

John said...

Awesome! It works great! Just what I needed. Thanks for posting.

John said...

Awesome! It works great! Just what I needed. Thanks for posting.

Adam said...

This works great but is there a way where this can handle filters as well? For example; "?FilterField1=ID&FilterValue1=34" (dynamic string)?

Tracy Drew said...

First off, thank you very much for this tip. Ok I tried this on my staging site and got it to work fab. However, an import from production to staging was performed so the staging would be like production and I lost my work.

I found my notes and tried it again. Created the view, found the ListID and View ID and plugged into link, but now I'm getting an error, "Cannot complete this action, Please try again."

Any clue as to why I would get such a nondescript error message?

Tracy said...

I found the answer. I had the code in the wrong order:

I had: /_vti_bin/owssvr.dll?CS=109&Using=_layouts/query.iqy&List=...
But needed it to be in:
/_vti_bin/owssvr.dll?Using=_layouts/query.iqy&CS=109&List=....

I had not saved my 'good' link in my notes, just the bad one.

I would like to be able to export just 1 record of data, but adding an &ID=123 doesn't seem to do it. How would I add a where clause

Thanks for the great information

T

Lakshmi Priya said...

This is super cool...But how do I incorporate the Group By option enabled in the view during the export?

Maggie G said...

Thanks! Is there a way to export SharePoint 2013 search result to Excel? If not, is there a way to convert the search result to a view?

Maggie G said...

Thanks a lot. Is there a way to export the SharePoint 2013 search result to Excel? If not, is there a way to export the search result to a SharePoint list/view first?

-Maggie




Share your SharePoint Experiences with us...
As good as the SharePointKings is, we want to make it even better. One of our most valuable sources of input for our Blog Posts comes from ever enthusiastic Visitors/Readers. We welcome every Visitor/Reader to contribute their experiences with SharePoint. It may be in the form of a code stub, snippet, any tips and trick or any crazy thing you have tried with SharePoint.
Send your Articles to sharepointkings@gmail.com with your Profile Summary. We will Post them. The idea is to act as a bridge between you Readers!!!

If anyone would like to have their advertisement posted on this blog, please send us the requirement details to sharepointkings@gmail.com