Thursday, October 17, 2013

How to Create an Export to Excel Button for Any List & View (Including Surveys)


This tutorial will cover how to create a simple HTML button which will export a particular list & list view to Excel.


Materials Needed:

<input type="button" style="width:180px; height: 75px; 30px;background:gray; color:white;font-size:larger; font-weight:bold;"onclick="window.location.href='SITEURL/_vti_bin/owssvr.dll?CS=109&Using=_layouts/query.iqy&List={YOUR LIST ID}&View={YOUR VIEW ID}&CacheControl=1';" value ="YOUR BUTTON TEXT"/>


The code above is essentially everything that you will need for a simple Excel Export button.  In order to get the code to work, you will need to update the SITEURL, YOUR LIST ID, YOUR VIEW ID, & YOUR BUTTON TEXT.


The hardest part of this exercise is to determine what your list & view ID are, but even that is simple!


To Obtain Your List & View ID:


1.    Navigate To Your List

2.    Select List Settings

3.    Under Views

a.    Edit the view you would like to create an export button for

4.    Copy The URL from your browser

a.    It should resemble: SitePath/_layouts/ViewEdit.aspx?List=%7B58B38FF2%2D9F99%2D4279%2DA22C%2DA2CCA95F4D7E%7D&View=%7B5781F798%2DD539%2D4015%2D87FB%2DA4A32925AACC%7D&Source=%252Fsites%252Fustsm%252F%255Flayouts%252Flistedit%252Easpx%253FList%253D%25257B58B38FF2%25252D9F99%25252D4279%25252DA22C%25252DA2CCA95F4D7E%25257D

b.    You can manually break out the View= & List=, but I personally just use this free translator that is embedded in the page which does it for you.

5.    SurfpointTechnology List ID & View ID Calculator

 

To Implement The Button:


1.    Swap out the necessary snippets in order to update the HTML code (YOUR BUTTON TEXT, YOUR URL, YOUR LIST ID, and YOUR VIEW ID) with the information you obtained in “To Obtain Your List & View ID.”

2.    Place the updated code into a text file and save it out on your site

3.    On a webpage that you would like the button, create a content editor web part and link that web part to the URL of your text file, I would recommend using the relative path (/local/fakesite/library/filename.etc).
 

That’s It Folks!


Dan

 
Posted by
Labels: , , , , , , , , , , ,

36 comments:

  1. PixidiliousJuly 31, 2014 at 1:17 AM

    YOU ARE AMAZING!!!!! THANK YOU!!!!

    ReplyDelete
    Replies
    1. DanNovember 11, 2014 at 7:19 AM

      Thank you for the kind words, I am glad that it worked out for you!

      Best Regards,
      Dan

      Delete
  2. Kevin WhiteNovember 10, 2014 at 7:45 PM

    what kind of text file?

    ReplyDelete
    Replies
    1. DanNovember 11, 2014 at 7:26 AM

      Hi Kevin,

      You should be able to just open NotePad or NotePad++ and paste in the code snippet directly into it (after you have updated the components specific to your SP site). The default file which is saved will be .txt. You can either upload the .txt file to your SharePoint site and link to it from a content editor web part or if you are on SharePoint 2013 you can paste the code snippet directly into a script editor web part.

      Best Regards,
      Dan

      Delete
  3. UnknownJanuary 21, 2015 at 9:51 PM

    HI Dan i've tried this code but its not working for me. While clicking on the button it giving error like,
    Invalid field name. {3881510a-4e4a-4ee8-b102-8ee8e2d0dd4b} /sites/CFO/Lists/summary .
    Please help in solving this.
    Thanks in advance...

    ReplyDelete
    Replies
    1. DanJanuary 22, 2015 at 7:37 AM

      Hi Archana,

      Would you mind posting the code you are using? From what you posted it seems like you might have input the list GUID where the site path should be. The easiest way to get your list GUID and view GUID is to get them from designer or by using that parsing tool I linked to. If you post your code I would be more than happy to try and give you a hand.

      All The Best,
      Dan

      Delete
  4. SwethaJune 9, 2015 at 10:24 PM

    Even I am unable to export my list to excel. Please help. My code below

    input type="button" style="width:180px; height: 75px; 30px;background:gray; color:white;font-size:larger; font-

    weight:bold;"onclick="window.location.href='http://SITEURL/_vti_bin/owssvr.dll?

    CS=109&Using=_layouts/query.iqy&List={0EB86CC6-2172-487D-BC92-057879BF38C5}&View={E5EAC7F2-3FF9-4265-8229-7731F62DC46A}

    &CacheControl=1';" value ="YOUR BUTTON TEXT"/

    ReplyDelete
    Replies
    1. DanJune 10, 2015 at 7:04 AM

      Hi Swetha,

      In order to troubleshoot I would first make sure that your URL that you built is correct before implementing the button. What I mean by this is that I would try to navigate to to your URL below in the browser to see if you are prompted with the dialog to open the connection in Excel.

      http://SITEURL/_vti_bin/owssvr.dll?CS=109&Using=_layouts/query.iqy&List={0EB86CC6-2172-487D-BC92-057879BF38C5}&View={E5EAC7F2-3FF9-4265-8229-7731F62DC46A}&CacheControl=1

      To further troubleshoot I would ensure that your List ID and View ID are correct by verifying them within SharePoint designer. What version of SharePoint are you using and what type of list and view are you trying to export?

      Best Regards,
      Dan

      Delete
  5. Brian's Big and Tall OnlineJune 19, 2015 at 6:36 AM

    Hi Dan,

    I got the URL to work but when excel opens the file I get an unexpected error.

    Below is my code
    input type="button" style="width:180px; height: 75px; 30px;background:gray; color:white;font-size:larger; font-weight:bold;"onclick="window.location.href='http://sharepoint.farmersinsurance.com/sites/business_iao/iao/IAOps/projectmanagement//_vti_bin/owssvr.dll?CS=109&Using=_layouts/query.iqy&List=33250EA2-0E4E-4C3E-A03E-D4D1F05BEEC2&View=084F4E58-9142-4E25-842E-19DCE0E4D0F8&CacheControl=1';" value ="Export to Excel"/>


    any help is appreciated

    ReplyDelete
    Replies
    1. DanJune 19, 2015 at 8:43 AM

      Hey Brian,

      I would try using the code below and see if that works for you. I removed the duplicate slash after project management and added curly braces around your GUIDs.

      input type="button" style="width:180px; height: 75px; 30px;background:gray; color:white;font-size:larger; font-weight:bold;" onclick="window.location.href='http://sharepoint.farmersinsurance.com/sites/business_iao/iao/IAOps/projectmanagement/_vti_bin/owssvr.dll?CS=109&Using=_layouts/query.iqy&List={33250EA2-0E4E-4C3E-A03E-D4D1F05BEEC2}&View={084F4E58-9142-4E25-842E-19DCE0E4D0F8}&CacheControl=1';" value="Export to Excel">

      Best Regards,
      Dan

      Delete
    2. DanJune 19, 2015 at 8:49 AM

      Also another thing to note is that you should be using the same account as the account with the Excel client, otherwise you will run into issues. What I mean by this is if your computer login is myAD\myname , you should be logged into SharePoint as myAD\myname in order to export to Excel correctly. If you are logged in as a farm admin such as myAD\spFarm and exporting to your client which would most likely be myAD\myname there will be a conflict.

      Hope this Helps,
      Dan

      Delete
  6. NickJune 24, 2015 at 2:10 PM

    Hi,

    Is there a way to get this functionality but have a JPG as the button instead of the HTML button?

    Thanks!

    Nick

    ReplyDelete
    Replies
    1. DanJune 25, 2015 at 7:12 AM

      Hey Nick, you can definitely use an image instead of the button. I decided to use a very simple html button since I knew each browser could render it, but you can use an image and wrap that. You could use the out of the box SharePoint functionality and just insert and image and set the URL for that image to the export URL or you could write the markup yourself.

      Example Markup For Image:
      ?a href="SITEURL/_vti_bin/owssvr.dll?CS=109&Using=_layouts/query.iqy&List={YOUR LIST ID}&View={YOUR VIEW ID}&CacheControl=1">?img src="https://tctechcrunch2011.files.wordpress.com/2008/04/linux-penguin-small.png" />?/a>
      You just need to swap out the placeholder URLs and this should work! My blog won't allow me to paste full html markup, so you will need to swap out the question marks with <.

      Best Regards,
      Dan

      Delete
    2. NickJune 25, 2015 at 9:27 AM

      Awesome! Thank you for your help!

      Delete
  7. MarkNovember 27, 2015 at 5:02 AM

    Hi Dan,

    Great blog!

    I am having trouble with this button to export to excel - I get the error message below.

    Sorry, something went wrong
    An unexpected error has occurred.
    Technical Details

    Troubleshoot issues with Microsoft SharePoint Foundation.

    Correlation ID: 00b0459d-ff05-40c0-27d2-94e1fac55c67


    Here is my code:

    input type="button" style="width:180px; height: 75px; 30px;background:gray; color:white;font-size:larger; font-weight:bold;"onclick="window.location.href='https://thehub.group.atlascopco.com/sites/SSF_CCL/Pages/Home.aspx/_vti_bin/owssvr.dll?CS=109&Using=_layouts/query.iqy&List={E8F2D5CA-3669-4A9D-AFF0-AD378E498030}&View={6822C300-318B-4C08-A8EF-CB2694BE980F}&CacheControl=1';" value ="export to EXCEL"/

    Thanks!
    Mark

    ReplyDelete
  8. MarkNovember 27, 2015 at 5:05 AM

    Hi Dan,

    Great site!

    I am having trouble with this code though, I get the following error message.

    Sorry, something went wrong
    An unexpected error has occurred.
    TECHNICAL DETAILS

    Troubleshoot issues with Microsoft SharePoint Foundation.
    Correlation ID: 71b3459d-6fa2-40c0-27d2-964cee483c4c

    Here is the code

    input type="button" style="width:180px; height: 75px; 30px;background:gray; color:white;font-size:larger; font-weight:bold;"onclick="window.location.href='https://thehub.group.atlascopco.com/sites/SSF_CCL/Pages/Home.aspx/_vti_bin/owssvr.dll?CS=109&Using=_layouts/query.iqy&List={E8F2D5CA-3669-4A9D-AFF0-AD378E498030}&View={6822C300-318B-4C08-A8EF-CB2694BE980F}&CacheControl=1';" value ="export to EXCEL"/

    I have a feeling to may have something to do with my work's security or user restrictions on the sharepoint itself, but maybe I've made a right mess of the code!

    Thanks,
    Mark

    ReplyDelete
  9. DanNovember 27, 2015 at 9:46 AM

    Hey Mark!

    This code looks pretty close but I think /pages/home.aspx snuck into your URL. Instead of that URL try:

    https://thehub.group.atlascopco.com/sites/SSF_CCL/_vti_bin/owssvr.dll?CS=109&Using=_layouts/query.iqy&List={E8F2D5CA-3669-4A9D-AFF0-AD378E498030}&View={6822C300-318B-4C08-A8EF-CB2694BE980F}&CacheControl=1

    As long as your id's are correct you should be able to try and hit that URL from your browser to receive the Export to Excel dialog box that you would normally see when exporting from the ribbon. Let me know if this works for you!

    Dan

    ReplyDelete
  10. Sheikh ShahnawazDecember 22, 2015 at 1:15 AM

    Hi Dan,
    I feel i am almost there but when i click on Export to Excel, it is taking me to the url mentioned instead of excel in content editor webpart. i want the view to be printed as is in Excel.

    Can you please help, here is the code

    input type="button" style="width:180px; height: 75px; 30px;background:gray; color:white;font-size:larger; font-weight:bold;"onclick="window.location.href='http://infospace.emirates.com/newsites/BTS-BizSolDesign/_vti_bin/owssvr.dll?CS=109&Using=_layouts/query.iqy&List={16B2B736-AFBE-456F-9268-BEF6F3997FE3}&View={E3D66BA8-9453-4A08-9605-FE104DD4B7AB}&CacheControl=1';" value ="Export to Excel"/>

    ReplyDelete
    Replies
    1. DanDecember 22, 2015 at 7:40 AM

      Hi Sheikh,

      Have you tried hitting that URL from your browser to see if you are prompted with the dialog box for the excel data connection? I would try hitting http://infospace.emirates.com/newsites/BTS-BizSolDesign/_vti_bin/owssvr.dll?CS=109&Using=_layouts/query.iqy&List={16B2B736-AFBE-456F-9268-BEF6F3997FE3}&View={E3D66BA8-9453-4A08-9605-FE104DD4B7AB}&CacheControl=1 to see if you have the right URL. If you do get the dialog box for the Excel data connection we can narrow down the issue. Really this solution is about creating this path, once you have the correct path you could apply it to anything with an anchor tag.

      Best Regards,
      Dan

      Delete
    2. Sheikh ShahnawazDecember 22, 2015 at 9:04 AM

      Hi Dan, i got the solution, it is working in Internet Explorer. i was trying in Chrome. Thank you for your quick response.
      But here one more issue is arising and that is, i am getting all the data in the excel again. it is not grouped as in the sharepoint list view. Actually i have grouped the questions of a survey so that each question will have its own group and will be shown only once. But here in excel the questions are repeated same way it was in normal export to excel view. Can you help me in getting that particular view in excel in the same way as grouped in sharepoint list view?

      Delete
    3. DanDecember 23, 2015 at 9:05 AM

      Hi Sheikh,

      Unfortunately the data will always come into the Excel file in the flat view. By passing the view GUID you will only return the data from that view, but not the grouping within SP. If you had a view with a filter such as CreatedBy = [ME], then the Excel extract would only have your values if the extract was based off of this view. If you need the data grouped in a similar way I would export the data to Excel like you have done and then create a Pivot Table with grouping based off that data. All of that data is linked, so the pivot table derives its data from the table within Excel, and that Excel table is linked to the SP data. You can actually refresh all of your reports (including the pivot table(s) and obtain the latest SP by selecting data from the ribbon and refresh all. If you were to use this approach you could actually just create the workbook and reports once, then simply select the refresh all.

      Best Regards,
      Dan

      Delete
  11. AmitJanuary 29, 2016 at 7:15 AM

    Hi Dan,
    This works form me, but the exported excel has two additional colums "Item type" and "Path". Anyway to get rid of those.

    Regards,
    Amit

    ReplyDelete
    Replies
    1. DanMay 13, 2016 at 8:06 AM

      Hi Amit! Unfortunately I do not know of a way to not include those columns in the export, they are also generated with the default export to excel option as well. After the export has been done it is possible to remove those columns from within Excel, but I don't know of a way to prevent them from being generated during the initial export.

      Delete
  12. sfikasFebruary 5, 2016 at 4:05 AM

    Worked just fine! Thanks!!

    ReplyDelete
  13. NicoleApril 15, 2016 at 1:48 PM

    Hi. I got the button on the page but upon clicking it says "Error cannot complete this action". Help! Here is my code:


    input type="button" style="width:180px; height: 75px; 30px;background:gray; color:white;font-size:larger; font-weight:bold;"onclick="window.location.href='SITEURL/_vti_bin/owssvr.dll?CS=109&Using=_layouts/query.iqy&List={72A79FDC-9D79-407F-89B3-B36C5A42DD15}&View={1042D48D-59C3-42CE-A7E7-608666C15ECE}&CacheControl=1';" value ="EXPORT TO EXCEL"/>

    ReplyDelete
    Replies
    1. DanMay 13, 2016 at 8:14 AM

      Hi Nicole! Did you swap out the SITEURL after the href= with your SITEURL or did you comment with this in for anonymity? That code looks correct if you swapped out the SITEURL placeholder with your URL. If you wanted to test with a very basic button I would try the below. You should just need to swap out the "rest of url" place holder and replace the double quotes at the beginning of the button with < and replace the double quotes at the end of the button with >. Best Regards, Dan

      "button type="button" onclick="window.location.href='https://rest of URL/_vti_bin/owssvr.dll?
      CS=109&Using=_layouts/query.iqy&List={72A79FDC-9D79-407F-89B3-B36C5A42DD15}&View={1042D48D-59C3-42CE-A7E7-608666C15ECE}&CacheControl=1';">Export Button</button"

      Delete
  14. UnknownMay 2, 2016 at 1:24 PM

    Hi Dan, I realize this is a super old post but hoping you're still watching it. This is a great help to me as SP 2013 seems to only allow me to select in my toolbar options one that will allow Export to Excel and one to Edit the list, but not both in the same. I have this all set up, the URL works on it's own but nothing happens when I hit the button. I'm using SP 2013 and IE 11.
    Any ideas as to what's going on?

    input type="button" style="width:150px; height: 50px; 30px;background:orange; color:white;font-size:larger;
    font-weight:bold;"onclick="window.location.href='https://rest of URL/_vti_bin/owssvr.dll?
    CS=109&Using=_layouts/query.iqy&List={BFCB8CF1-32A4-46FA-BDEF-EE375E0B68FD}&View={0C93D9F6-45C1-4CEC-AD1B-3D0756AE07DA}&CacheControl=1';"
    value ="Export To Excel"/>

    Thanks a lot, appreciate it!

    Toni

    ReplyDelete
    Replies
    1. DanMay 13, 2016 at 7:51 AM

      Hi Toni! Is the button rendering on the page for you? If you wanted to try an simple button I would try what I posted below without the quotes. I have seen some browsers require type="button" for the button to work correctly, so I think the button below will work for you. Unfortunetly my blog currently is horrible at responding with code in the comments, so you will need to add the opening and closing tags to the button < and > since my blog will rip it out. Best Regards! Dan


      "button type="button" onclick="window.location.href='https://rest of URL/_vti_bin/owssvr.dll?
      CS=109&Using=_layouts/query.iqy&List={BFCB8CF1-32A4-46FA-BDEF-EE375E0B68FD}&View={0C93D9F6-45C1-4CEC-AD1B-3D0756AE07DA}&CacheControl=1';">Export Button</button"

      Delete
  15. Angel ReverseMay 2, 2016 at 1:43 PM

    HI Dan,
    I tried out the code and i got the button properly but i am unable to get the list exported. When i try just the URL as you mentioned i do not the the Dialog box either. I dont think im inputting the correct URL.
    I used the same URL from which i took my List ID and View ID from, that is, the url from Editing View from the Edit List page.

    window.location.href= http://teamsites.teamworks.wellsfargo.net/sites/TOG-24x7-360/_layouts/_vti_bin/owssvr.dll?CS=109&Using=_layouts/query.iqy&List={71BB21E0-393F-4319-BFF1-A203271925FE}&View={8E680045-F734-4463-973A-9C618C49B803}&CacheControl=1';"

    ReplyDelete
    Replies
    1. DanMay 13, 2016 at 8:25 AM

      Hi Angel! What does the path to your site look like? As an example if I had a path such as https://dansdev/sites/dan/pages/default.aspx and my lists were stored on the /dan site, my URL would be:

      https://dansdev/sites/dan/_vti_bin/owssvr.dll?
      CS=109&Using=_layouts/query.iqy&List={71BB21E0-393F-4319-BFF1-A203271925FE}&View={8E680045-F734-4463-973A-9C618C49B803}&CacheControl=1

      I would first try troubleshooting your URL in IE to see if you get prompted with the export dialog. Once you get the export dialog box to appear you can create a simple button: (just swap out the starting " with < and swap out the last " with >). Let me know if you need any more help! Best Regards, Dan

      "button type="button" onclick="window.location.href='YOUR SITE URL/_vti_bin/owssvr.dll?
      CS=109&Using=_layouts/query.iqy&List={71BB21E0-393F-4319-BFF1-A203271925FE}&View={8E680045-F734-4463-973A-9C618C49B803}&CacheControl=1">Export Button</button"

      Delete
    2. Angel ReverseMay 16, 2016 at 3:55 PM

      That totally worked. Thank you Dan.

      Delete
  16. Nikki May 10, 2016 at 9:43 PM

    Hi Dan, I am working on a project where a page will display several lists based on filter choices.
    I want to export the displayed lists in Excel file, by pressing a button. Is it possible in Sharepoint 2010 using Designer only.
    WIll I require Visual Studio or C#.

    THanks a lot for your response.

    ReplyDelete
  17. Nikki May 11, 2016 at 12:14 AM

    Hello Dan,

    I am also trying this code , as it is very relevant for my project.

    I am facing the same problem. I embedded the code inthe Content editor web part.
    When I select the onclick URL directly, I am able to export the file, but when I try using the web page, it does not work for me.
    I am trying to figure out how to use the code in the Content Editor web part on my ASP page. Thanks for your help!!

    ReplyDelete
    Replies
    1. DanMay 13, 2016 at 7:29 AM

      Hi Amita are you having issues with the button? Luckily you have the URL working which really is the backbone of the post, but it sounds like the HTML button may be throwing you a loop. Did you store the html for the button in a .txt file that you are linking to via a content editor or are you inserting the button into the page via a script editor? If you post your code I can try and help troubleshoot. Best Regards! Dan

      Delete
  18. RogerMarch 31, 2017 at 5:32 AM

    Hi Dan

    I like your solution to this problem, but I have a small twist to it and I am curious if it is possible to solve.

    I have a big list from which I make ad-hoc filtering by clicking the column lable and selecting different combinations in the drop-down list to get the wanted result.
    When I see what I want on my screen I click the button created as above, but the exported data contains all items in my view.
    Is there a way to get rid of the rows that are filtered out from the view so that the export only consists of what I see on my screen.
    Since my selections are so different from time to time, I don't want to make a new view every time I get a question from another department for their piece of the information.

    ReplyDelete

Subscribe to: Post Comments (Atom)