Asp.net : Export Datatable data to Excel using CloseXml | ASPMANTRA | Asp.Net,MVC,AngularJs,Jquery,Javascript,Sql Server AND WCF 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





Post a Comment

[blogger]

MKRdezign

Contact Form

Name

Email *

Message *

Powered by Blogger.
Javascript DisablePlease Enable Javascript To See All Widget