If you try to search on internet on this topic, there are many posts available and each one uses different technique to achieve this
last week, one of my colleague asked me how they can export the SharePoint list content (they were accessing sites remotely) , well answer was quite simple , click on actions tab on list and then select export to spread sheet and save file , but what really puzzled me is , what are other OOB ways available by SharePoint to achieve this other than this option?
I tried searching for this for few hours and ended up with writing a sample console application which exports the list’s default view and write list contents to spreadsheet
Major disadvantage of this is, you cannot run this application when you are not directly working with SharePoint Server, because we are using Server side object model to achieve this
I am keen to see this in SharePoint 2010 environment because we can use Client Object Model to achieve this
But for now here is code I created
I know this code is little heavy (due to those foreach loops) but I thought this is ok as this won’t be running continuously on 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")));
}
}
}
}
}
}
}
}
}
