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:
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?
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.
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.
Sebastian is right
that's might be because of typo mistake
thanks Sebastian.
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.
This dont work in SAFARI any know how fix it?
Regards.-
Karma,
not tried in safari.
Awesome! It works great! Just what I needed. Thanks for posting.
Awesome! It works great! Just what I needed. Thanks for posting.
Awesome! It works great! Just what I needed. Thanks for posting.
This works great but is there a way where this can handle filters as well? For example; "?FilterField1=ID&FilterValue1=34" (dynamic string)?
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?
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
This is super cool...But how do I incorporate the Group By option enabled in the view during the export?
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?
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
Post a Comment