Want a free Ebook & latest Update Subscribe Now
  [email protected]      beta
Donate

Export gridview to Excel in Asp.net

 Gridview To Excel: This article explains how to export gridview to Excel file in Asp.net C#. When I was working on my previous projects, there is a requirement by one of my client that he wants to export data into MS Excel format.

The data could be from Asp.net GridView Control or any other data bound control or directly from code-behind DataTable. I have to export Asp.net GridView / Repeater control / DataTable data to Excel file using C#.

You can also check  Import Excel sheet data to Gridview in Asp.net C#, How to export gridview to pdf in asp.net c#.

Demo:

Export Gridview to Excel in Asp.net C#


Methods for Export Data to Excel in Asp.net C#

  • By creating CSV file –  Have you ever open CSV file using EXCEL, if not then do open it once. What you are going to see it in Excel, is your CSV values are displayed as table-structured data .i.e comma separated value display as a Table cell form data. For creating Excel file in  C#, you can use this approach, only if you don’t want formatting, styles or workbook structure.
  • By creating XML file –  Excel allows you to open XML files with our without a schema. You can also see a table of data in Excel and can have some control on formatting if you use a schema.
  • By creating SYLK file –  Symbolic Link (SYLK) is a Microsoft file format typically used to exchange data between applications, specifically spreadsheets. SYLK files conventionally have a .slk suffix. Composed of only displayable ANSI characters, it can be easily created and processed by other applications, such as databases.

Using above any one method, we can export gridview data to excel file.

Step to Export Gridview to Excel.

  1. Add Gridview and Button control.
  2. Bind Gridview with data.
  3. Code to export data to excel.

# Html Markup: Add Asp.net Gridview control and button.

Open new visual studio project, add new Webpage (.aspx file) and now place an Asp.net `Gridview` control and a `button` control on newly added Webpage. So our page markup looks like as written below.

//*
<asp:GridView ID="GridView1" runat="server" OnSelectedIndexChanged="GridView1_SelectedIndexChanged" CellPadding="4" ForeColor="#333333" GridLines="None">
    <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
    <EditRowStyle BackColor="#999999" />
    <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
    <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
    <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
    <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
    <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
    <SortedAscendingCellStyle BackColor="#E9E7E2" />
    <SortedAscendingHeaderStyle BackColor="#506C8C" />
    <SortedDescendingCellStyle BackColor="#FFFDF8" />
    <SortedDescendingHeaderStyle BackColor="#6F8DAE" />
</asp:GridView><br />
<asp:Button ID="Button1" runat="server" OnClick="Button1_Click1" Text="Grid to CSV" />
//*

 # Code-behind: Bind Gridview control with data.

Here on code behind I have initialized a datatable and added some data to it.

And on page load event I have bind this datatable to my Asp.net Gridview control, you can also bind it with the database. So our code which shows data on gridview control looks like as written below.

//*
protected void Page_Load(object sender, EventArgs e)
{
    if (!Page.IsPostBack)
    {
        GridView1.DataSource = getData();
        GridView1.DataBind();
    }
}

public DataTable getData()
{

    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, "Satinder Singh", "Bsc Com Sci", "Mumbai");
    dt.Rows.Add(2, "Amit Sarna", "Mstr Com Sci", "Mumbai");
    dt.Rows.Add(3, "Andrea Ely", "Bsc Bio-Chemistry", "Queensland");
    dt.Rows.Add(4, "Leslie Mac", "MSC", "Town-ville");
    dt.Rows.Add(5, "Vaibhav Adhyapak", "MBA", "New Delhi");
    dt.Rows.Add(6, "Johny Dave", "MCA", "Texas");

    return dt;
}
//*

# Code to export data to excel.

Yeah, now we are on the main part of this post i.e., code how to export gridview to excel in c#. We can do this by using any one of the above methods. Let’s first check by using Method 1, will update this with other methods later by this week

#Method 1: Create CSV file in Asp.net c#

For the most part, reading and writing CSV files are trivial. As the name suggestions, a `CSV` file is simply a plain-text file that contains one or more values per line, separated by commas.

Each value is a field (or column in a spreadsheet), and each line is a record (or row in a spreadsheet)

CSV files can easily be read and written by many programs, including Microsoft Excel. You can also check CSV-export tool for C#.

#Code: To generate CSV file from the Gridview data.

Here we make a loop over Asp.net Gridview control and get the cell value and then append it to StringBuilder variable. So our code looks like as written below

//*
public void GridviewToExcel()
{

    StringBuilder builder = new StringBuilder();
    string strFileName = "GridviewExcel_" + DateTime.Now.ToShortDateString() + ".csv";
    builder.Append("Name ,Education,Location" + Environment.NewLine);
    foreach (GridViewRow row in GridView1.Rows)
    {
        string name = row.Cells[0].Text;
        string education = row.Cells[1].Text;
        string location = row.Cells[2].Text;
        builder.Append(name + "," + education + "," + location + Environment.NewLine);
    }
    Response.Clear();
    Response.ContentType = "text/csv";
    Response.AddHeader("Content-Disposition", "attachment;filename=" + strFileName);
    Response.Write(builder.ToString());
    Response.End();
}

protected void Button1_Click1(object sender, EventArgs e)
{
    GridviewToExcel();
}
//*

Note: Each comma represents a column.

Download Source Code

Output: Finally we are done with Excel File generation 🙂

Export Gridview to Excel in Asp.net C#

Hope you enjoyed this tutorial. If you have any recommendations, please let us know what you think in the comment section below! See you again next time!

If You Liked It, could you do me a favour and tell your friends !! By sharing it on Facebook, Google+ or Twitter.

Share:    
Satinder Singh

Hi all, am Founder and Author of Codepedia.info, also atypical polyglot programmer from Bombay, India. I have a passion to create, solve, and deploy software applications.
       ForEach ( minute in MyLife ) { myExperience++ ; }


2 comments on “Export gridview to Excel in Asp.net”


Post Comment


Your email address will not be published. Required fields are marked *