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