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")));
}
}
}
}
}
}
}
}
}