Tuesday, September 30, 2008

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

8 comments:

Anonymous said...

thank you very much,i search this sulotion for a long time.

JG Vimalan said...

Very useful post.
Thanks.

Anonymous said...

HI,
THis is really a very useful post. Thank you very much...

moymike said...

Server Error in '/' Application.
-----------------------------------

Control 'ctl00_m_g_ae1ba58d_e77c_4d53_a356_0fbd0c145f3c_ctl00_ctl02_ctl00' of type 'DataGridLinkButton' must be placed inside a form tag with runat=server.

Outside of sharepoint, this is fixed by adding:

public override void VerifyRenderingInServerForm(Control control) {}

But within sharepoint you get "no suitable method found to override"

I have found a few references to "_spFormOnSubmitCalled", but nothing works when I do it within sharepoint.

piyush said...

its code is fine but big problem occur in web parts.

following are that

export to excel functionality is working fine but after save excel file no other button event fire in web parts,

Harout said...

piyush,
i have the same problem. where you able to solve this?
please let me know because i would really appreciate it.

thanks.

ALI said...

To fix issue in webpart... You need to create a DataGrid and use same source as SPDataGrid.

Shai Petel said...

Hi guys,

Anyone having an issue in web part that this breaks all buttons and such, there is an easy fix:
Please this code in an ASPX page, and from the web part when someone clicks export - open said ASPX page using window.open, it will open it in a new page, give you the export, and your web part page will remain unaffected.

Sorry it took me so long to respond, I see many people asking about that.

Good luck.