Export SharePoint list to excel using DataGrid

From time to time I learn that the OOTB export to excel capabilities of SharePoint are missing some edge.

For instance - the latest thing my colleague found is that if you do not have excel 2007 but earlier versions, it does not export some column types correctly, or are completely missing.

So, I have created a simple ASP.NET web part that will allow the user to export the list items / list view into excel, that does not require a specific version of excel to be installed on the client.

The concept is simple:
Connect to a list / view,
Get items as DataTable
Connect DataTable to a DataGrid
Export DataGrid to excel.

So, to get the list items / view items into a Data Table, I used one of these code samples:

For list URL + view ID:

using (SPSite site = new SPSite(txtListUrl.Text))
{
  using (SPWeb web = site.OpenWeb())
  {
    SPList list = web.GetListFromUrl(txtListUrl.Text);
    DataTable items = list.GetItems(list.Views[new Guid(cmbListView.SelectedValue)]).GetDataTable();

    ExportToExcel(items);
  }
}

Or, if you plan to place the WP in the list view page - here it is working with the SPContext.Current.List:

SPList list = SPContext.Current.List;
DataTable items = list.Items.GetDataTable();

ExportToExcel(items);

Ok, so now we have the DataTable, all we have to do is implement the code that will export the DataTable to excel using Data Grid:

private void ExportToExcel(DataTable items)
{
  theGrid = new DataGrid();
  theGrid.DataSource = items;
  theGrid.DataBind();

  Page.Response.Clear();
  Page.Response.ContentType = "application/vnd.ms-excel";
  Page.Response.Charset = "utf-8";
  Page.Response.AddHeader("Content-Disposition", "attachment;filename="+DateTime.Now.ToString("yyyyMMdd_hhmmss")+".xls");

  System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
  System.Web.UI.HtmlTextWriter oHtmlTextWriter = new   System.Web.UI.HtmlTextWriter(oStringWriter);
  theGrid.RenderControl(oHtmlTextWriter);

  Page.Response.Write(oStringWriter.ToString());
  Page.Response.End();
}

And guess what? It is that simple!

Enjoy,

Shai Petel
KWizCom VP Professional Services

Catalog: AnchorProject. A master merge was started due to an external request.

Today I was getting this warning in my server event log:
event id 4164 and 4103
Catalog: AnchorProject. A master merge was started due to an external request.

Since it was flodding my event viewer I didnt even see the other issues there... but one of the servers CPU was in max usage all the time (100%, but since we had 2 CPU task manager shows 50% total usage)...

Well, as I was digging up more info here is what I came up with and how I solved this issue:

1: I was getting an error on a certain page from within a site ( a custom list view ) that referenced the wrong master page - /_layouts/application.master. Since the celler page was in the list and not in the /_layouts/ folder this was blocked by ASP.NET
2: The caller user was my search user, lucky it is a unique user so I could be sure it was invoked by the indexer

So, after I fixed / deleted these 2 pages - the indexer stopped trying to index them, and 30 seconds after that the errors were gone and CPU back to normal...

Hope this helps you guys too, I will update if I get more info on this.