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