Sunday, September 2, 2012

How to create Excel from DataTable DataSet C # dot net

Create an customized Excel file from a DataSource

Sometimes we need to create an Excel file from a DataTable OR DataSet then we think how to do this. Dear you no need to get worry about this task it's so simple. Here I am writing the simple and easy code with detailed description.

Design part[aspx page]

<asp:Button ID="btnExport" runat="server" Text="Export Data" OnClick="btnExport_Click" />


Code behind part[aspx.cs]


 protected void btnExport_Click(object sender, EventArgs e)
        {
            try
            {
                // Here I have a DataTable in session
                DataTable dtExports = (DataTable)Session["dtShadow"];
                Response.Clear();
                Response.ClearContent();
                Response.ClearHeaders();
                Response.Buffer = true;
                Response.AddHeader("content-disposition", "attachment;filename=Member.xls");
                Response.Charset = "";
                Response.ContentType = "application/vnd.ms-excel";

                Table mainTable = new Table();

                TableRow trExport;
                TableCell cellExport;

                if (dtExports.Rows.Count > 0)
                {
                    trExport = new TableRow();
                    for (int col = 0; col < dtExports.Columns.Count; col++)
                    {
                        cellExport = new TableCell();
                        cellExport.Text = dtExports.Columns[col].ToString();
                        cellExport.BorderWidth = 1;
                        cellExport.Style.Add("font-weight", "bold");
                        cellExport.Style.Add("text-align", "Left");
                        cellExport.BackColor = System.Drawing.Color.Silver;
                        trExport.Cells.Add(cellExport);
                        mainTable.Rows.Add(trExport);
                    }

                    for (Int32 ai = 0; ai < dtExports.Rows.Count; ai++)
                    {
                        trExport = new TableRow();
                        for (Int16 cl = 0; cl < dtExports.Columns.Count; cl++)
                        {
                            cellExport = new TableCell();
                            cellExport.Text = Convert.ToString(dtExports.Rows[ai][cl]).Trim();
                            cellExport.BorderWidth = 1;
                            cellExport.Style.Add("text-align", "left");
                            cellExport.Style.Add("vertical-align", "top");
                            trExport.Cells.Add(cellExport);
                        }
                        mainTable.Rows.Add(trExport);
                    }
                }

                StringWriter sw = new StringWriter();
                HtmlTextWriter hw = new HtmlTextWriter(sw);
                mainTable.RenderControl(hw);
                Response.Flush();
                Response.Write(hw.InnerWriter.ToString());
                Response.Flush();
                Response.End();
            }
            catch(Exception ex)
            {
               
            }
        }

In next post I will tell you about the design and formatting of excel with the help of example, means you can download the data of Gridview into an  excel file as it it displaying in GridView.

For any query OR suggestion write back to us.

0 comments:

Twitter Delicious Facebook Digg Stumbleupon Favorites More

 
Powered by Code Imagine