If you try to search the internet on this topic, there are many posts available and each one uses different techniques to do this.
Last week, one of my colleagues asked me how they can export the SharePoint list content (they were accessing sites remotely). Well the answer was quite simple; click on the actions tab on the list and then select export to spreadsheet and save file; but what really puzzled me is, what other OOB ways are available by SharePoint to do this other than this option?
I tried searching for this for a few hours and ended up writing a sample console application that exports the list's default view and writing the list contents to a spreadsheet.
The major disadvantage of this is, you cannot run this application when you are not directly working with SharePoint Server, because we are using the Server side object model to do this.
I am keen to see this in a SharePoint 2010 environment since we can use the Client Object Model to achieve this.
But for now here is the code I created.
I know this code is a little heavy (due to those foreach loops) but I thought this is ok as this won't be running continuously on the server. class Program {
private static DataTable dataTable;
private static SPList list;
static void Main(string[] args)
{
try
{
Console.WriteLine("Site Url: ");
string _siteUrl = Console.ReadLine();
if (!string.IsNullOrEmpty(_siteUrl))
{
SPSecurity.RunWithElevatedPrivileges(delegate()
{
using (SPSite site = new SPSite(_siteUrl))
{
if (site != null)
{
SPWeb web = site.RootWeb;
if (web != null)
{
#region Export List
Console.WriteLine("List Name:");
string _listName = Console.ReadLine();
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:\\" + 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")));
}
}
}
}
}
}
}
}
}
subhra ghoshPosted Aug 17, 2019, 3:16 AM
I am working in csom,then how can I do this. If you have any leads how to do it in csom c#.
abhishek singhPosted Mar 21, 2018, 5:12 AM
I do not access to SharePoint server, I have credentials to view lists present on sharepoint, then how can I download/ extract the sharepoint list items.
Rathrola Prem KumarPosted Apr 11, 2017, 9:09 PM
Issue fixed, i just changed my console app from 32-64 in the properties window, Hence solved :)
Rathrola Prem KumarPosted Apr 11, 2017, 9:02 PM
Am getting error: Retrieving the COM class factory for component with CLSID {BDEADF26-C265-11D0-BCED-00A0C90AB50F} failed due to the following error: 80040154 Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)).
Rathrola Prem KumarPosted Apr 11, 2017, 9:02 PM
Am getting this error??
Pallavi GlPosted Nov 25, 2016, 11:53 AM
Hi , This code works but the excel file generated using the code throws an error "The file format and extension of File.xls dont match.The file could be corrupted or unsafe.Unless you trust its source,dont open it..... ". After Clicking on ok it shows the data . How can i avoid that . Please help
Rajkiran SwainPosted Jul 20, 2015, 11:02 AM
nice