Export Gridview to CSV in a SharePoint 2010 Webpart

Export Gridview to CSV in a SharePoint 2010 Webpart

I was recently tasked with creating a Gridview in a SharePoint 2010 Webpart. In addition to assigning data to the Gridview, I also needed to create a way to export the data. Typically you would go down the route of exporting to Excel so I looked at some code from another blogger http://mattberseth.com/blog/2007/04/export_gridview_to_excel_1.html.

This worked, however Excel did not recognize the file format as the document is basically a text file. As a result the user would get an ugly prompt saying something like “cannot verify the file format, would you still like to open”. This was not acceptable. So I tried using the code from the blog mentioned above to create a CSV file, which opened with no error, although the document was not formatted with data correctly because a CSV is a comma delimited text file.

So I now know that I can overcome the Excel document open error with a CSV, I just need to create the code to do the export and here it is:

Convert the contents of the GridView Cells from controls to text

private string getGridCellText(TableCell tc)
{
string cellText = “”;
if (tc.HasControls())
{
foreach (Control c in tc.Controls)
{
if (c.GetType() == typeof(LinkButton))
{
LinkButton lb = c as LinkButton;
cellText = lb.Text;
}
else if (c.GetType() == typeof(HyperLink))
{
HyperLink h1 = c as HyperLink;
cellText = h1.Text;
}
}
}
else
{
cellText = tc.Text;
}
return cellText;
}

Process the export request

public void exporttoExcel(GridView grv)
{
StringBuilder sb = new StringBuilder();
GridViewRow grHeader = grv.HeaderRow;
int counter = 0;

foreach (TableCell tc in grHeader.Cells)
{
sb.Append(“\”” + grv.Columns[counter].HeaderText + “\”,”);
counter++;
}
sb.AppendLine();

foreach (GridViewRow gr in grv.Rows)
{
foreach (TableCell tc in gr.Cells)
{
sb.Append(“\”” + getGridCellText(tc) + “\”,”);
}
sb.AppendLine();
}

Response.Clear();
Response.ClearHeaders();
Response.ClearContent();
Response.AddHeader(“content-disposition”, “attachment; filename=Export.csv”);
Response.ContentType = “text/csv”;
Response.AddHeader(“Pragma”, “public”);
Response.Write(sb.ToString());
Response.End();
}

Button click event

public void export(object sender, EventArgs e)
{
exporttoExcel();
}

You now have the code to do your export and it should all work, however something went wrong! It only works once, and everything else on the page seems to be disabled.

Apparantly, SharePoint updates some kind of timestamp hash on the form before it is actually submitted to the server. This is done in order to prevent the form from being submit more than once if the user clicks before the Postback is completed, which is a good thing – except when trying to do what we are trying to do here. To get past this you will need to also add the following JavaScript to the onClientClick event for your button:

function postExport() {

window.WebForm_OnSubmit = function ()
{ return true; };
}

OnClientClick=”postExport()”

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>