Asp.net : Export Datatable data to Excel using CloseXml - Free Asp.Net,MVC,AngularJs,Jquery,Javascript,Sql Server,WCF,Entity framework snippets and tutorial

Asp.net : Export Datatable data to Excel using CloseXml

In this article I am going to explain how to export Datatable data to excel using CloseXml in asp.net.


Description:
I want to export table data to excel. To fulfill this requirement I going to use CloseXml. You can download and add its reference to your application or can install package using Nuget. Run the below command in Nuget package manager:

Install-Package ClosedXML 

Implementation:
Add a webform to project .

HTML of webform:

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <asp:Button ID="btnexport" runat="server" Text="Export To Excel"  />
    </div>
    </form>
</body>
</html>

Add namespaces to code file.

C# code
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using ClosedXML.Excel;
using System.IO;

VB.Net Code
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports ClosedXML.Excel
Imports System.IO

On button click write the below given code.
C# code

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ToString());
    protected void Page_Load(object sender, EventArgs e)
    {
       
    }
    protected void btnexport_Click(object sender, EventArgs e)
    {
        try
        {
            SqlDataAdapter adp = new SqlDataAdapter("Select * from Tb_Population", con);
            DataTable dt = new DataTable();
            adp.Fill(dt);
            XLWorkbook wbook = new XLWorkbook();
            wbook.Worksheets.Add(dt, "Tb_Population");
            Response.Clear();
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.AddHeader("content-disposition", "attachment;filename=\"Excel.xlsx\"");
            using (MemoryStream memoryStream = new MemoryStream())
            {
                wbook.SaveAs(memoryStream);
                memoryStream.WriteTo(Response.OutputStream);
                memoryStream.Close();
            }
            Response.End();
        }
        catch (Exception ex) { }
    }


VB.Net Code
Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("connection").ToString())
    Protected Sub btnexport_Click(sender As Object, e As EventArgs) Handles btnexport.Click
        Try
            Dim adp As New SqlDataAdapter("Select * from Tb_Population", con)
            Dim dt As New DataTable()
            adp.Fill(dt)
            Dim wbook As New XLWorkbook()
            wbook.Worksheets.Add(dt, "Tb_Population")
            Response.Clear()
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
            Response.AddHeader("content-disposition", "attachment;filename="" Excel.xlsx""")
            Using memoryStream As New MemoryStream()
                wbook.SaveAs(memoryStream)
                memoryStream.WriteTo(Response.OutputStream)
                memoryStream.Close()
            End Using
            Response.[End]()
        Catch ex As Exception
        End Try
    End Sub





What do you think about this article?

If you found this article useful, please share and follow on Facebook, Twitter, Google Plus and other social media websites. To get free updates subscribe to newsletter. Please put your thoughts and feedback in comments section.

Share this

Share on FacebookTweet on TwitterPlus on Google+


EmoticonEmoticon