Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • How to export datatable to excel in asp.net?

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 876
    Comment on it

    To export datatable in excel, First create an excel file at any location on my computer. In my example, I have created a blank excel file in D drive named as "DataTableToExcel.xls".

    First, we will add some data in datatable, Either we can have static data or dynamic. We can bind our datatable to database.

    To export datatable in excel we use StreamWriter Class for which "System.IO" namespace is used. This class contains all the methods to write text to file. So StreamWriter helps to implement a Textwriter for writing text to the file in particular file format.

    Implementation: First you have to add an aspx page in your application and then add a button on this page.

    Include the following code in your design part: It will add a button which will export data to excel sheet.

    <!DOCTYPE html>
    
    <html>
    <head runat="server">
        <title></title>
    </head>
    <body>
        <form id="form1" runat="server">
        <div>
        <asp:Button runat="server" ID="Btn_Export" Text="Export" OnClick="Btn_DtToExeclExport_Click" />
        </div>
        </form>
    </body>
    </html>

    Now include the following code at code behind page(aspx.cs)

    protected void Btn_DtToExeclExport_Click(object sender, EventArgs e)
            {
                //DataTable, Filename with full path  
                ExportToExcel(BindDatatable(), "D:\\DataTableToExcel.xls");
            }
    
            protected DataTable BindDatatable()
            {
                DataTable dt = new DataTable();
                dt.Columns.Add("UserId", typeof(Int32));
                dt.Columns.Add("UserName", typeof(string));
                dt.Columns.Add("EmailId", typeof(string));
                dt.Columns.Add("UserMobile", typeof(string));
                dt.Rows.Add(1, "shikha", "shikha.evon@gmail.com", "9876556523");
                dt.Rows.Add(2, "mayuri", "mayuri.evond@gmail.com", "9988774455");
                dt.Rows.Add(3, "deepika", "deepika.evon@gmail.com", "9966563211");
                dt.Rows.Add(4, "mona", "mona.evon@gmail.com", "9988995522");
                return dt;
            }
    
            private void ExportToExcel(DataTable table, string filePath)
            {
                StreamWriter Strwriter = new StreamWriter(filePath, false);
                Strwriter.Write(@"<!DOCTYPE HTML PUBLIC ""-//W3C//DTD HTML 4.0 Transitional//EN"">");
                Strwriter.Write("<font style='font-size:15.0pt; font-family:TimesNewRoman;'>");
                Strwriter.Write("<BR><BR><BR>");
                Strwriter.Write("<Table border='2' bgColor='#ffffff' borderColor='#000000' cellSpacing='0' cellPadding='0' style='font-size:15.0pt; font-family:TimesNewRoman; background:white;'> <TR>");
                int dtcolumncount = table.Columns.Count;
    
                for (int j = 0; j < dtcolumncount; j++)
                {
                    Strwriter.Write("<Td>");
                    Strwriter.Write("<B>");
                    Strwriter.Write(table.Columns[j].ToString());
                    Strwriter.Write("</B>");
                    Strwriter.Write("</Td>");
                }
                Strwriter.Write("</TR>");
                foreach (DataRow row in table.Rows)
                {
                    Strwriter.Write("<TR>");
                    for (int i = 0; i < table.Columns.Count; i++)
                    {
                        Strwriter.Write("<Td>");
                        Strwriter.Write(row[i].ToString());
                        Strwriter.Write("</Td>");
                    }
                    Strwriter.Write("</TR>");
                }
                Strwriter.Write("</Table>");
                Strwriter.Write("</font>");
                Strwriter.Close();
            }

    Debug the code and click on Export Button. When the program will be successfully executed then you can check your excel file at your given location. you will get all data table details in excel.

     

    See Screenshot for reference:

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: