Click here to Skip to main content
15,118,508 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I have to export fetched data from database to excel sheet. How can i do..Here i have displayed data in html .. i have to export data in excel.



public void Function(string fname)
{
strhtmlcontent.Append("<div id='id1'><table align='Center' style='background-color:Silver' BORDER-COLLAPSE:collapse cellSpacing=0 rules=all border=1 width='100%'><th width=8%>FirstName</th><th width=15%>Product Name</th><th width=15%>Client Name</th><th width=10%>Amount</th><th width=15%>Activity Date</th>");

SqlConnection scon = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

scon.Open();
if (fname == "All")
{
scmd = new SqlCommand("SELECT usermaster.firstname, Product_Master.product_name, Product_Master.client_name, Product_Master.amount, Product_Master.activity_date FROM usermaster INNER JOIN Product_Master ON usermaster.product_id = Product_Master.product_id", scon);
}
else
{

scmd = new SqlCommand("SELECT usermaster.firstname, Product_Master.product_name, Product_Master.client_name, Product_Master.amount, Product_Master.activity_date FROM usermaster INNER JOIN Product_Master ON usermaster.product_id = Product_Master.product_id where firstname='"+fname+"'", scon);

}
dr = scmd.ExecuteReader();




string firstname = "", product_name = "", activity_date = "";

string client_name = "", amount = "";

while (dr.Read())
{
firstname = dr["firstname"].ToString();
product_name = dr["product_name"].ToString();
client_name = dr["client_name"].ToString();
amount = dr["amount"].ToString();
activity_date = dr["activity_date"].ToString();


strhtmlcontent.Append("<tr><td align='center'>" + firstname + "&nbsp;</td><td align='center'>" + product_name + "&nbsp;</td><td align='center'>" + client_name + "&nbsp;</td><td align='center'>" + amount + "&nbsp;</td><td align='center'>" + activity_date + "&nbsp;</td></tr>");


}

dr.Close();
strhtmlcontent.Append("</table></div>");
HttpContext.Current.Response.Write(strhtmlcontent);
HttpContext.Current.Response.Flush();
//HttpContext.Current.Response.End();
}
Posted
Updated 20-Feb-12 0:11am
v2
Comments
Nilesh Patil Kolhapur 20-Feb-12 5:08am
   
Each and every Data means what?
[no name] 20-Feb-12 5:10am
   
rows and coloumns of database showed in html..

Datatable to excell
C#
private void ExportDataTable(DataTable dt)
       {
           string attachment = "attachment; filename=a.xls";
           HttpContext.Current.Response.Clear();
           HttpContext.Current.Response.AddHeader("content-disposition", attachment);
           HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
           string sTab = "";
           foreach (DataColumn dc in dt.Columns)
           {
               HttpContext.Current.Response.Write(sTab + dc.ColumnName);
               sTab = "\t";
           }
           HttpContext.Current.Response.Write("\n");

           int i;
           foreach (DataRow dr in dt.Rows)
           {
               sTab = "";
               for (i = 0; i < dt.Columns.Count; i++)
               {
                   HttpContext.Current.Response.Write(sTab + dr[i].ToString());
                   sTab = "\t";
               }
               HttpContext.Current.Response.Write("\n");
           }
           HttpContext.Current.Response.End();
       }
   
Try this....
protected void Button1_Click(object sender, EventArgs e)
{
Response.Clear();
Response.AddHeader("content-disposition", "attachment;filename=GridView1.xls");
Response.Charset = "";
Response.ContentType = "application/vnd.xls";
StringWriter StringWriter = new System.IO.StringWriter();
HtmlTextWriter HtmlTextWriter = new HtmlTextWriter(StringWriter);
GridView1.RenderControl(HtmlTextWriter);
Response.Write(StringWriter.ToString());
Response.End();
}

public override void VerifyRenderingInServerForm(Control control)
{

}
   
Comments
[no name] 20-Feb-12 5:08am
   
Thanks for reply...
i have not used gridview1..its simply showing data in html...i hve to export the html showed data..
1st of all I would request you to post your questions with better code formatting so that it looks more readable.

I can see in your code data retrieval and display mechanism is tried to implement in the same place. Which is not a good practice. You should think like the following:

1. You retrieve your data preferably through a data access class function.
2. You display data as html.
3. You export data as excel.

See, here your data is no way related to html. You have your data in your hand from a separate function. You call for that data and you can take it to any format.

Hope this make sense.
   
Comments
[no name] 20-Feb-12 5:14am
   
suppose i take Button "Export" On the click of that i have to export then..
Mahmud Hasan 20-Feb-12 5:33am
   
On Export Button Click event, you retrieve the data as u did to display in html (again you need separate your data retrieval from the UI) and then export that data to xl.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900