How to Export DataTable to Excel Sheet in ASP.Net

In this tutorial, we have to learn how to export DataTable to Excel sheet in ASP.Net.

In this tutorial, we have to use EPPlus Nuget Package to Export Datatable to ExcelSheet.

So, First add EPPlus dll in your project usomg NuGet Package Manager. You can also get this dll from source code that i have attached at the end of article.

Now use below code to Export DataTable to Excel Sheet.

Default.aspx

 <form id="form1" runat="server">
    <div>
        <asp:Button ID="btn_export" runat="server" Text="Export Data" />
        <br />
        <br />
        <asp:GridView ID="GridView1" runat="server">
        </asp:GridView>
    </div>
    </form>

Default.aspx.cs (VB)

Imports System.Data.SqlClient
Imports System.Data
Imports OfficeOpenXml
Imports System.IO
Imports OfficeOpenXml.Style
Imports System.Drawing
Public Class _Default
    Inherits System.Web.UI.Page
    Dim con As SqlConnection = New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True;MultipleActiveResultSets=True")
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    End Sub
    Protected Sub btn_export_Click(sender As Object, e As EventArgs) Handles btn_export.Click
        con.Open()
        Dim cmd As SqlCommand = New SqlCommand("Select * FROM Student")
        cmd.CommandType = CommandType.Text
        cmd.Connection = con
        Dim dt As DataTable = New DataTable()
        Dim sda As SqlDataAdapter = New SqlDataAdapter(cmd)
        sda.Fill(dt)
        con.Close()
        Using pck As New ExcelPackage()
            'Create the worksheet
            Dim ws As ExcelWorksheet = pck.Workbook.Worksheets.Add("Students")
            'Load the datatable into the sheet, starting from cell A1. Print the column names on row 1
            ws.Cells("A1").LoadFromDataTable(dt, True)
            Dim fileBytes As [Byte]() = pck.GetAsByteArray()
            Response.Clear()
            Response.Buffer = True
            Response.AddHeader("content-disposition", _
                 "attachment;filename=DataTable.xlsx")
            Response.Charset = ""
            Response.ContentType = "application/vnd.ms-excel"
            Dim sw As New StringWriter()
            Response.BinaryWrite(fileBytes)
            Response.[End]()
        End Using
       
    End Sub
End Class

Default.aspx.cs(C#)

using System.Data.SqlClient;
using System.Data;
using OfficeOpenXml;
using System.IO;
using OfficeOpenXml.Style;
using System.Drawing;
public class _Default : System.Web.UI.Page
{
SqlConnection con = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Database.mdf;Integrated Security=True;User Instance=True;MultipleActiveResultSets=True");
protected void Page_Load(object sender, System.EventArgs e)

{
}
protected void  // ERROR: Handles clauses are not supported in C#
btn_export_Click(object sender, EventArgs e)
{
con.Open();
SqlCommand cmd = new SqlCommand("Select * FROM Student");
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
DataTable dt = new DataTable();
SqlDataAdapter sda = new SqlDataAdapter(cmd);
sda.Fill(dt);
con.Close();
using (ExcelPackage pck = new ExcelPackage()) {
//Create the worksheet
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Students");
//Load the datatable into the sheet, starting from cell A1. Print the column names on row 1
ws.Cells("A1").LoadFromDataTable(dt, true);
Byte[] fileBytes = pck.GetAsByteArray();
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment;filename=DataTable.xlsx");
Response.Charset = "";
Response.ContentType = "application/vnd.ms-excel";
StringWriter sw = new StringWriter();
Response.BinaryWrite(fileBytes);
Response.End();
}
}
}

Download Source Code