Friday, June 1, 2012

Asp.net export to Word document from SQL Server Table


In this port “Asp.net export to Word document from SQL Server Table”, we shall see how to export content from a database table to a Word document.

Exporting content to Word 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 Word by setting the Content-Type. 

This method of exporting is very handy since it allows to do any kind of formatting to the Word document 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 cmdExportWord_Click(object sender, EventArgs e)
{
    GenerateWordDocument();
}
//
public static void GenerateWordDocument()
{
    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/msword");
        HttpContext.Current.Response.AppendHeader("Content-disposition", "attachment; filename=EmployeeDetails.doc");
        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 Word document looks like this.


That’s it we have seen a simple technique to export content of a Table into a Word Document.





Search Flipkart Products:
Flipkart.com

No comments: