In this article I am going to explain how to export multiple Datatables
data to excel file using CloseXml in asp.net.
In previous article I have explained how to export Datatabledata to excel using CloseXml in asp.net, how to integrate Facebook login inAsp.net MVC website, how to create Facebook app for website and how to insertmultiple tables data into one table in sql server.
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
No comments:
Post a Comment