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


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



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