n
nIf you try to search on internet on this topic, there are many posts available and each one uses different technique to achieve this
n
nlast 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?
n
nI 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
n
nMajor 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
n
nI am keen to see this in SharePoint 2010 environment because we can use Client Object Model to achieve this
n
nBut for now here is code I created
n
nI 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
n
n
n
nclass Program
n
n{
n
n
n
n private static DataTable dataTable;
n
n private static SPList list;
n
n
n
n static void Main(string[] args)
n
n {
n
n try
n
n {
n
n Console.WriteLine(“Site Url: “);
n
n string _siteUrl = Console.ReadLine();
n
n if (!string.IsNullOrEmpty(_siteUrl))
n
n {
n
n SPSecurity.RunWithElevatedPrivileges(delegate()
n
n {
n
n using (SPSite site = new SPSite(_siteUrl))
n
n {
n
n if (site != null)
n
n {
n
n SPWeb web = site.RootWeb;
n
n
n
n if (web != null)
n
n {
n
n
n
n #region Export List
n
n
n
n Console.WriteLine(“List Name:”);
n
n string _listName = Console.ReadLine();
n
n if (!string.IsNullOrEmpty(_listName))
n
n {
n
n list = web.Lists[_listName];
n
n
n
n if (list != null)
n
n {
n
n dataTable = new DataTable();
n
n
n
n //Adds Columns to SpreadSheet
n
n InitializeExcel(list, dataTable);
n
n
n
n string _schemaXML = list.DefaultView.ViewFields.SchemaXml;
n
n
n
n if (list.Items != null && list.ItemCount > 0)
n
n {
n
n foreach (SPListItem _item in list.Items)
n
n {
n
n DataRow dr = dataTable.NewRow();
n
n foreach (DataColumn _column in dataTable.Columns)
n
n {
n
nif (dataTable.Columns[_column.ColumnName] != null && _item[_column.ColumnName] != null)
n
n {
n
ndr[_column.ColumnName] = _item[_column.ColumnName].ToString();
n
n }
n
n }
n
n dataTable.Rows.Add(dr);
n
n
n
n }
n
n }
n
n
n
n }
n
n }
n
n
n
nSystem.Web.UI.WebControls.DataGrid grid = new System.Web.UI.WebControls.DataGrid();
n
n
n
n grid.HeaderStyle.Font.Bold = true;
n
n grid.DataSource = dataTable;
n
n grid.DataBind();
n
n
n
n using (StreamWriter streamWriter = new StreamWriter(“C:\\” + list.Title + “.xls”, false, Encoding.UTF8))
n
n {
n
n using (HtmlTextWriter htmlTextWriter = new HtmlTextWriter(streamWriter))
n
n {
n
n grid.RenderControl(htmlTextWriter);
n
n }
n
n }
n
n
n
n Console.WriteLine(“File Created”);
n
n
n
n #endregion
n
n }
n
n }
n
n }
n
n });
n
n }
n
n }
n
n catch (Exception ex)
n
n {
n
n Console.WriteLine(“Error: “ + ex.Message);
n
n }
n
n
n
n Console.ReadLine();
n
n }
n
n
n
n public static void InitializeExcel(SPList list, DataTable _datatable)
n
n {
n
n if (list != null)
n
n {
n
n string _schemaXML = list.DefaultView.ViewFields.SchemaXml;
n
n if (list.Items != null && list.ItemCount > 0)
n
n {
n
n foreach (SPListItem _item in list.Items)
n
n {
n
n foreach (SPField _itemField in _item.Fields)
n
n {
n
n if (_schemaXML.Contains(_itemField.InternalName))
n
n {
n
n if (_item[_itemField.InternalName] != null)
n
n {
n
n if (!_datatable.Columns.Contains(_itemField.InternalName))
n
n {
n
n _datatable.Columns.Add(new DataColumn(_itemField.StaticName, Type.GetType(“System.String”)));
n
n }
n
n }
n
n }
n
n }
n
n }
n
n }
n
n }
n
n }
n
n }
n