Tuesday, 28 April 2015

Export Excel using DataTable

DataTable dt = (DataTable)ViewState["dt"];
            HttpContext curContext = HttpContext.Current;
            curContext.Response.Clear();
            string FileName = "PPODetailsList" + DateTime.Now.Day.ToString() + "_" + DateTime.Now.Month.ToString() + "_" + DateTime.Now.Year.ToString() + "_" + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString() + ".xls";
            curContext.Response.AddHeader("content-disposition", "attachment;filename=" + FileName);
            curContext.Response.Charset = "";
            curContext.Response.Cache.SetCacheability(HttpCacheability.NoCache);
            curContext.Response.ContentType = "application/vnd.ms-excel";
            StringBuilder sb = new StringBuilder();
            sb.Append("<table border=1>");
            sb.Append("<tr style='background-color: silver;'>");
            //foreach (DataColumn c in dtExcel.Columns)
            //{
            //sb.Append("<td>");
            //sb.Append(c.ColumnName.ToString());
            //sb.Append("Domain Name");

            //sb.Append("</td>");
            //sb.Append("<td>Registry Date</td>");
            //sb.Append("<td>Expiry Date</td>");
            //sb.Append("<td>Organization Name</td>");
            //}

            sb.Append("<th>S.No.</th><th>Treasury Code</th><th>Type of Pension</th><th >Sole ID</th><th>Region Name</th><th >PPO No.</th>");
            sb.Append("</tr>");
            int Srno = 1;
            foreach (DataRow r in dt.Rows)
            {
                sb.Append("<tr >");
                sb.Append("<td>");
                sb.Append((Srno++).ToString());
                sb.Append("</td>");

                sb.Append("<td>");
                sb.Append(r[2].ToString());
                sb.Append("</td>");
                sb.Append("<td>");
                sb.Append(r[7].ToString());
                sb.Append("</td>");
                sb.Append("<td>");
                sb.Append(r[4].ToString());
                sb.Append("</td>");
                sb.Append("<td>");
                sb.Append(r[5].ToString());
                sb.Append("</td>");

                sb.Append("<td>");
                sb.Append(r[9].ToString());
                sb.Append("</td>");
                sb.Append("<td>");
                sb.Append(r[14].ToString());
                sb.Append("</td>");
                sb.Append("</tr>");
            }
            sb.Append("</table>");
            byte[] byteArray = Encoding.ASCII.GetBytes(sb.ToString());
            MemoryStream s = new MemoryStream(byteArray);
            StreamReader sr = new StreamReader(s, Encoding.ASCII);
            curContext.Response.Write(sr.ReadToEnd());

            curContext.Response.End();

No comments:

Post a Comment