none
Saving sharepoint list to Excel RRS feed

  • Question

  • HI,

    I am trying to export a sharepoint custom list to excel.

    I have used the below code:

       using (SPSite site = new SPSite(siteurl))
                {
                    using (SPWeb web = site.OpenWeb())
                    {
                        String strExportURL;
                        SPList list = web.Lists[strListName.Trim()];
                        Guid listId = web.Lists[strListName.Trim()].ID;
    
                        SPView wpView = list.Views["All Items"];
                        Guid lstViewId = list.Views["All Items"].ID;
                        wpView.RowLimit = 2147483647;
    string strExcelfilePath = strFile + ".iqy";
                        
    strExportURL = strListURL.ToString() + "_vti_bin/owssvr.dll?CS=109&Using=_layouts/query.iqy&List=%7B" + listId.ToString() + "%7D&View=%7B" + lstViewId.ToString() + "%7D&CacheControl=1";
    
    using (WebClient client = new WebClient())
                        {
    client.Credentials = CredentialCache.DefaultCredentials;
    
    client.DownloadFile(strExportURL, @strExcelfilePath);    
    }

    The following code works, but the data is saved as owssvr.iqy. I want to save the format as xlsx. I am trying the abive code in a timer job.

    How to save list in xlsx format?

    Thanks

    Friday, February 19, 2016 12:01 PM

All replies

  • I suspect that if you change the variable strExcelfilePath to have an XLSX entension you should be fine.

    The .iqy file is already specified in your strExportURL variable.


    w: http://www.the-north.com/sharepoint | t: @JMcAllisterCH | YouTube: http://www.youtube.com/user/JamieMcAllisterMVP

    Friday, February 19, 2016 4:16 PM
  • Hi,

    Thanks for the reply.

    I tried changing the file extension to xls as well as xlsx in the below line:

    //strExcelfilePath contains the "xls" extension
    client.DownloadFile(strExportURL, @strExcelfilePath);  

    However, when the file was downloaded, it just had 1 kb of data, and the data looks like below:

    WEB
    1
    http://site url/_vti_bin/owssvr.dll?XMLDATA=1&List={list id}&View={view id}&RowLimit=0&RootFolder=
    
    Selection={guid}-{guid}
    EditWebPage=
    Formatting=None
    PreFormattedTextToColumns=True
    ConsecutiveDelimitersAsOne=True
    SingleBlockTextImport=False
    DisableDateRecognition=False
    DisableRedirections=False
    SharePointApplication=siteurl/_vti_bin
    SharePointListView={guid}
    SharePointListName={guid}
    RootFolder=
    

    How to fix this?

    Thanks

    Friday, February 19, 2016 5:01 PM
  • If you not trying to automate the export for many lists or export it daily, then you can use the OOB export to excel function under library tab.



    • Edited by ViviSP Friday, February 19, 2016 7:09 PM
    Friday, February 19, 2016 7:09 PM
  • Hi,

    Thanks for the reply. I am trying to do that programmatically.

    Thanks

    Monday, February 22, 2016 12:39 PM