Asp.net export DataTable to Excel Spreadsheet

Tuesday, June 12, 2012

Asp.net export DataTable to Excel Spreadsheet

In this port “Asp.net export DataTable to Excel Spreadsheet”, we shall see how to export content from a DataTable to a Excel Spreadsheet.

Exporting content to Excel is a common feature, often seen in reporting modules, there are many ways of exporting details to work, here we shall see a simple technique, of reading the content in a DataTable and exporting it to Excel by setting the Content-Type.

This method of exporting is very handy since it allows doing any kind of formatting to the Excel Spreadsheet using the HTML tags.

Get the data to be exported into a DataTable, add a Button to the Asp.Net page and in the click event of the Button add the following code to export the content.

//

protected void cmdExport_Click(object sender, EventArgs e)

{

// Add logic to get the data to be exported into the DataTable dtReportData
GenerateExcel(dtReportData);

}

public static void GenerateExcel(DataTable dtSource)

{

StringBuilder sbDocBody = new StringBuilder(); ;

try

{

// Declare Styles

sbDocBody.Append("<style>");

sbDocBody.Append(".Header { background-color:Navy; color:#ffffff; font-weight:bold;font-family:Verdana; font-size:12px;}");

sbDocBody.Append(".SectionHeader { background-color:#8080aa; color:#ffffff; font-family:Verdana; font-size:12px;font-weight:bold;}");

sbDocBody.Append(".Content { background-color:#ccccff; color:#000000; font-family:Verdana; font-size:12px;text-align:left}");

sbDocBody.Append(".Label { background-color:#ccccee; color:#000000; font-family:Verdana; font-size:12px; text-align:right;}");

sbDocBody.Append("</style>");

StringBuilder sbContent = new StringBuilder(); ;

sbDocBody.Append("<br><table align=\"center\" cellpadding=1 cellspacing=0 style=\"background-color:#000000;\">");

sbDocBody.Append("<tr><td width=\"500\">");

sbDocBody.Append("<table width=\"100%\" cellpadding=1 cellspacing=2 style=\"background-color:#ffffff;\">");

if (dtSource.Rows.Count > 0)

{

sbDocBody.Append("<tr><td>");

sbDocBody.Append("<table width=\"600\" cellpadding=\"0\" cellspacing=\"2\"><tr><td>");

// Add Column Headers

sbDocBody.Append("<tr><td width=\"25\"> </td></tr>");

sbDocBody.Append("<tr>");

sbDocBody.Append("<td> </td>");

for (int i = 0; i < dtSource.Columns.Count; i++)

{

sbDocBody.Append("<td class=\"Header\" width=\"120\">" + dtSource.Columns[i].ToString().Replace(".", "<br>") + "</td>");

}

sbDocBody.Append("</tr>");

// Add Data Rows

for (int i = 0; i < dtSource.Rows.Count; i++)

{

sbDocBody.Append("<tr>");

sbDocBody.Append("<td> </td>");

for (int j = 0; j < dtSource.Columns.Count; j++)

{

sbDocBody.Append("<td class=\"Content\">" + dtSource.Rows[i][j].ToString() + "</td>");

}

sbDocBody.Append("</tr>");

}

sbDocBody.Append("</table>");

sbDocBody.Append("</td></tr></table>");

}

HttpContext.Current.Response.Clear();

HttpContext.Current.Response.Buffer = true;

HttpContext.Current.Response.AppendHeader("Content-Type", "application/ms-excel");

HttpContext.Current.Response.AppendHeader("Content-disposition", "attachment; filename=EmployeeDetails.xls");

HttpContext.Current.Response.Write(sbDocBody.ToString());

HttpContext.Current.Response.End();

}

catch (Exception ex)

{

// Ignore this error as this is caused due to termination of the Response Stream.

}

}

The Exported Excel Spreadsheet looks as follows.

That’s it we have exported the content of a DataTable into a Excel Spreadsheet, we can use this as a generic function in projects and call it whenever we want to convert the data in a DataTable to a Excel Spreadsheet.