Asp.net : Export multiple Datatables data to Excel file using CloseXml

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


Description:
I want to export multiple tables data to excel file. 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:
I have created 2 table Tb_Population and Employees, insert some dummy records into it.
After that 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
        {
            //datatable One
            SqlDataAdapter adp = new SqlDataAdapter("Select * from Tb_Population", con);
            DataTable dt = new DataTable();
            adp.Fill(dt);

            //datatable two
            SqlDataAdapter adpemp = new SqlDataAdapter("Select * from Employees", con);
            DataTable dtemp = new DataTable();
            adpemp.Fill(dtemp);

            XLWorkbook wbook = new XLWorkbook();
                wbook.Worksheets.Add(dt, "Tb_Population");
                wbook.Worksheets.Add(dtemp, "Employees");
        
                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 adpemp As New SqlDataAdapter("Select * from Employees", con)
            Dim dtemp As New DataTable()
            adpemp.Fill(dtemp)

            Dim wbook As New XLWorkbook()
            wbook.Worksheets.Add(dt, "Tb_Population")
            wbook.Worksheets.Add(dtemp, "Employees")
            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