locked
SharePoint 2013 List to Excel file RRS feed

  • Question

  • Hi,

    We have a requirement to pull SharePoint list contents to an excel file. 

    Can anyone help me with this? Is it possible to write a timer job or custom code or is there any plugin to load contents to excel file?

    Thanks,

    Shruthi

    Thursday, June 27, 2019 8:16 PM

All replies

  • Try this:


    Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue 

    #Get the Web 
    $web = Get-SPWeb -identity "Web site url" 
    #Get the Target List 
    $list = $web.Lists["List Name"]
     ListItemCollection = @() 
     $list.Items |  foreach { 
     $ExportItem = New-Object PSObject 
     $ExportItem | Add-Member -MemberType NoteProperty -name "Title" -value $_["Column1"] 
     $ExportItem | Add-Member -MemberType NoteProperty -Name "Department" -value $_["Column2"] 
     $ExportItem | Add-Member -MemberType NoteProperty -name "Status" -value $_["Column3"] 
     $ExportItem | Add-Member -MemberType NoteProperty -name "Priority" -value $_["Column4"] 

     $ListItemCollection += $ExportItem } 
     #Export the result Array to CSV file 
     $ListItemCollection | Export-CSV "c:\ListData.csv" -NoTypeInformation 
     $web.Dispose()

    The above script should generate .CSV file, which you could change to .xlsx. 

    You could also schedule this in the windows task scheduler.

    Regards,

    Balajee 




    Thursday, June 27, 2019 9:13 PM
  • If you just want to export it to excel for just one time purpose then just go to list from top ribbon click on export to excel that will do the stuff but if you want the report for every week or so then what Balajee mentioned is true you can simple keep the above script in the Task Scheduler.

    https://sharepointmaven.com/export-sharepoint-excel/

    Thanks & Regards,


    sharath aluri

    Friday, June 28, 2019 2:35 AM
  • Hi Shruthi,

    As Balajee's reply, we can easy export the list items to a csv file using PowerShell.

    If you want to create a timer job with C# code to achieve it, the following example code for your reference.

    using Microsoft.SharePoint;
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.IO;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Web.UI;
    
    namespace ConsoleApplication1
    {
        class Program
        {
            private static DataTable dataTable;
            private static SPList list;
            static void Main(string[] args)
            {
                try
                {
                    string _siteUrl = "http://sp2013/sites/team";
                    string _listName = "CustomList";
                    if (!string.IsNullOrEmpty(_siteUrl))
                    {
                        using (SPSite site = new SPSite(_siteUrl))
                        {
                            if (site != null)
                            {
                                SPWeb web = site.RootWeb;
                                if (web != null)
                                {
                                    #region Export List                               
                                    if (!string.IsNullOrEmpty(_listName))
                                    {
                                        list = web.Lists[_listName];
                                        if (list != null)
                                        {
                                            dataTable = new DataTable();
                                            //Adds Columns to SpreadSheet
                                            InitializeExcel(list, dataTable);
                                            string _schemaXML = list.DefaultView.ViewFields.SchemaXml;
                                            if (list.Items != null && list.ItemCount > 0)
                                            {
                                                foreach (SPListItem _item in list.Items)
                                                {
                                                    DataRow dr = dataTable.NewRow();
                                                    foreach (DataColumn _column in dataTable.Columns)
                                                    {
                                                        if (dataTable.Columns[_column.ColumnName] != null && _item[_column.ColumnName] != null)
                                                        {
                                                            dr[_column.ColumnName] = _item[_column.ColumnName].ToString();
                                                        }
                                                    }
                                                    dataTable.Rows.Add(dr);
                                                }
                                            }
                                        }
                                    }
                                    System.Web.UI.WebControls.DataGrid grid = new System.Web.UI.WebControls.DataGrid();
                                    grid.HeaderStyle.Font.Bold = true;
                                    grid.DataSource = dataTable;
                                    grid.DataBind();
                                    using (StreamWriter streamWriter = new StreamWriter("C:\\temp\\" + list.Title + ".xls", false, Encoding.UTF8))
                                    {
                                        using (HtmlTextWriter htmlTextWriter = new HtmlTextWriter(streamWriter))
                                        {
                                            grid.RenderControl(htmlTextWriter);
                                        }
                                    }
                                    Console.WriteLine("File Created");
                                    #endregion
                                }
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Error: " + ex.Message);
                }
                //Console.ReadLine();
            }
            public static void InitializeExcel(SPList list, DataTable _datatable)
            {
                if (list != null)
                {
                    string _schemaXML = list.DefaultView.ViewFields.SchemaXml;
                    if (list.Items != null && list.ItemCount > 0)
                    {
                        foreach (SPListItem _item in list.Items)
                        {
                            foreach (SPField _itemField in _item.Fields)
                            {
                                if (_schemaXML.Contains(_itemField.InternalName))
                                {
                                    if (_item[_itemField.InternalName] != null)
                                    {
                                        if (!_datatable.Columns.Contains(_itemField.InternalName))
                                        {
                                            _datatable.Columns.Add(new DataColumn(_itemField.StaticName, Type.GetType("System.String")));
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
    }

    Best Regards,

    Dennis


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    SharePoint Server 2019 has been released, you can click here to download it.
    Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.


    Friday, June 28, 2019 2:36 AM
  • Hi Shruthi,

    Any update?

    If the replies help you, please mark the reply as answer, it will make others who stuck with the similar issue easier to search for valid solutions in this forum.

    Best regards,
    Dennis


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    SharePoint Server 2019 has been released, you can click here to download it.
    Click here to learn new features. Visit the dedicated forum to share, explore and talk to experts about SharePoint Server 2019.

    Wednesday, July 3, 2019 8:21 AM