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>");
            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.






Search Flipkart Products:
Flipkart.com

No comments: