Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

How to Create and Download Excel File in Asp.net C#, VB.NET

Nov 11, 2014
Introduction

Here I will explain how to create and download excel file in
asp.net using c#, vb.net or create & download excel file from datatable in asp.net using c#, vb.net.


To create and download excel file in asp.net using c#, vb.net we need to write the code like as shown below in aspx page


<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>Create Excel File and Download to Excel in Asp.net</title>
</head>
<body>
<form id="form1" runat="server">
<asp:Button ID="btnCreateExcel" runat="server" Text="Create & Download Excel"
onclick="btnCreateExcel_Click" />
</form>
</body>
</html>
Now open code behind file and write the following code

C# Code


using System;
using System.Data;

public partial class Default2 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
    }
    protected DataTable BindDatatable()
    {
        DataTable dt = new DataTable();
        dt.Columns.Add("UserId", typeof(Int32));
        dt.Columns.Add("UserName", typeof(string));
        dt.Columns.Add("Education", typeof(string));
        dt.Columns.Add("Location", typeof(string));
        dt.Rows.Add(1, "SureshDasari", "B.Tech", "Chennai");
        dt.Rows.Add(2, "MadhavSai", "MBA", "Nagpur");
        dt.Rows.Add(3, "MaheshDasari", "B.Tech", "Nuzividu");
        dt.Rows.Add(4, "Rohini", "MSC", "Chennai");
        dt.Rows.Add(5, "Mahendra", "CA", "Guntur");
        dt.Rows.Add(6, "Honey", "B.Tech", "Nagpur");
        return dt;
    }
    //Export data to excel from datatable
    protected void btnCreateExcel_Click(object sender, EventArgs e)
    {
        Response.ClearContent();
        Response.Buffer = true;
        Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "Customers.xls"));
        Response.ContentType = "application/ms-excel";
        DataTable dt = BindDatatable();
        string str = string.Empty;
        foreach (DataColumn dtcol in dt.Columns)
        {
            Response.Write(str + dtcol.ColumnName);
            str = "\t";
        }
        Response.Write("\n");
        foreach (DataRow dr in dt.Rows)
        {
            str = "";
            for (int j = 0; j < dt.Columns.Count; j++)
            {
                Response.Write(str + Convert.ToString(dr[j]));
                str = "\t";
            }
            Response.Write("\n");
        }
        Response.End();
    }
}
VB.NET


Imports System.Data
Imports System.IO

Partial Class _Default
    Inherits System.Web.UI.Page
    Protected Function BindDatatable() As DataTable
        Dim dt As New DataTable()
        dt.Columns.Add("UserId", GetType(Int32))
        dt.Columns.Add("UserName", GetType(String))
        dt.Columns.Add("Education", GetType(String))
        dt.Columns.Add("Location", GetType(String))
        dt.Rows.Add(1, "SureshDasari", "B.Tech", "Chennai")
        dt.Rows.Add(2, "MadhavSai", "MBA", "Nagpur")
        dt.Rows.Add(3, "MaheshDasari", "B.Tech", "Nuzividu")
        dt.Rows.Add(4, "Rohini", "MSC", "Chennai")
        dt.Rows.Add(5, "Mahendra", "CA", "Guntur")
        dt.Rows.Add(6, "Honey", "B.Tech", "Nagpur")
        Return dt
    End Function
    'Export data to excel from datatable
    Protected Sub btnCreateExcel_Click(ByVal sender As Object, ByVal e As EventArgs)
        Response.ClearContent()
        Response.Buffer = True
        Response.AddHeader("content-disposition", String.Format("attachment; filename={0}", "Customers.xls"))
        Response.ContentType = "application/ms-excel"
        Dim dt As DataTable = BindDatatable()
        Dim str As String = String.Empty
        For Each dtcol As DataColumn In dt.Columns
            Response.Write(str + dtcol.ColumnName)
            str = vbTab
        Next
        Response.Write(vbLf)
        For Each dr As DataRow In dt.Rows
            str = ""
            For j As Integer = 0 To dt.Columns.Count - 1
                Response.Write(str & Convert.ToString(dr(j)))
                str = vbTab
            Next
            Response.Write(vbLf)
        Next
        Response.[End]()
    End Sub
End Class
Demo



If you enjoyed this post, please support the blog below. It's FREE!

Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

subscribe by rss Subscribe by RSS subscribe by email Subscribe by Email

3 comments :

Unknown said...

Hi Suresh,

If i want to format the data created in the excel sheet using c#, how can i do it? Can you please post it here?
Suppose i want the column names to be bold and cell bgcolor to be yellow, how can i achieve that?

Thanks in Advance,
Vishu

Unknown said...

thanks a lot, i want with space in last and no other code contain space in last but your does thanks again

Anonymous said...

How can I change column style properties?

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.