Friday, June 1, 2012

Asp.net export to Excel spreadsheet from SQL Server Table


In this post “Asp.net export to Excel spreadsheet from SQL Server Table”, we shall see how to export content from a database table 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 formatting the content in a HTML table and exporting the table to Excel by setting the Content-Type. 

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

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 cmdExportExcel_Click(object sender, EventArgs e)
{
    GenerateExcelSpreadSheet();
}
//
public static void GenerateExcelSpreadSheet()
{
    SqlConnection objConn;
    SqlCommand objCmd;
    DataSet dsDocData;
    SqlDataAdapter objDA;
    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;\">");

        // Populate the Employee Details
        string strConn = ConfigurationManager.ConnectionStrings["EmployeesConnectionString"].ToString();
        objConn = new SqlConnection(strConn);
        objCmd = new SqlCommand("SELECT TOP 10 * FROM Employee", objConn);
        objDA = new SqlDataAdapter(objCmd);
        dsDocData = new DataSet();
        objDA.Fill(dsDocData, "dtDocData");
        //
        if ((dsDocData.Tables.Count > 0) && (dsDocData.Tables[0].Rows.Count > 0))
        {
            sbDocBody.Append("<tr><td>");
            // Inner Table for Employee Details
            sbDocBody.Append("<table width=\"500\" cellpadding=\"0\" cellspacing=\"2\"><tr><td>");
            // Header Section
            sbDocBody.Append("<tr><td class=\"Header\" width=\"500\" colspan=\"5\">EMPLOYEE DETAILS</td></tr>");
            sbDocBody.Append("<tr>");
            sbDocBody.Append("<td class=\"SectionHeader\" width=\"50\">ID</td>");
            sbDocBody.Append("<td class=\"SectionHeader\" width=\"100\">NAME</td>");
            sbDocBody.Append("<td class=\"SectionHeader\" width=\"100\">Phone</td>");
            sbDocBody.Append("<td class=\"SectionHeader\" width=\"150\">Email</td>");
            sbDocBody.Append("<td class=\"SectionHeader\" width=\"100\">Salery</td>");
            sbDocBody.Append("</tr>");
            //               
            for (int i = 0; i < dsDocData.Tables[0].Rows.Count; i++)
            {
                sbDocBody.Append("<tr>");
                sbDocBody.Append("<td class=\"Content\">" + dsDocData.Tables["dtDocData"].Rows[i]["ID"].ToString() + "</td>");
                sbDocBody.Append("<td class=\"Content\">" + dsDocData.Tables["dtDocData"].Rows[i]["Name"].ToString() + "</td>");
                sbDocBody.Append("<td class=\"Content\">" + dsDocData.Tables["dtDocData"].Rows[i]["Phone"].ToString() + "</td>");
                sbDocBody.Append("<td class=\"Content\">" + dsDocData.Tables["dtDocData"].Rows[i]["Email"].ToString() + "</td>");
                sbDocBody.Append("<td class=\"Content\">" + dsDocData.Tables["dtDocData"].Rows[i]["Salery"].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 like this.



That’s it we have seen a simple technique to export content of a Table into an Excel Spreadsheet.





Search Flipkart Products:
Flipkart.com

No comments: