HiRitwikSaha,
For this use ClosedXml library.
Download DocumentFormat.OpenXml and ClosedXml Libraries
You can download the libraries using the following download locations.
Download OpenXml SDK 2.0
Download ClosedXml Library
HTML
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false">
<Columns>
<asp:BoundField DataField="Payee" HeaderText="Payee" />
<asp:BoundField DataField="ACNo" HeaderText="A/c No." />
<asp:BoundField DataField="IFSC" HeaderText="IFSC" />
</Columns>
</asp:GridView>
<br />
<asp:Button Text="Export" OnClick="Export" runat="server" />
Namespaces
C#
using System.Data;
using System.IO;
using ClosedXML.Excel;
VB.Net
Imports System.Data
Imports System.IO
Imports ClosedXML.Excel
Code
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
DataTable dt = GetData();
GridView1.DataSource = dt;
GridView1.DataBind();
}
}
private DataTable GetData()
{
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[3] { new DataColumn("Payee", typeof(string)),
new DataColumn("ACNo", typeof(string)),
new DataColumn("IFSC",typeof(string)) });
dt.Rows.Add("ABC", "98765432198765432198", "SBIN0008074");
dt.Rows.Add("DEF", "98765432198765432199", "UTBI0008074");
return dt;
}
protected void Export(object sender, EventArgs e)
{
DataTable dt = GetData();
MemoryStream stream = new MemoryStream();
using (XLWorkbook wb = new XLWorkbook())
{
wb.Worksheets.Add(dt, "Bank");
wb.SaveAs(stream);
}
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment; filename=" + "Payout Till.xls");
Response.ContentType = "application/vnd.ms-excel";
Response.BinaryWrite(stream.ToArray());
Response.End();
}
VB.Net
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
If Not Me.IsPostBack Then
Dim dt As DataTable = GetData()
GridView1.DataSource = dt
GridView1.DataBind()
End If
End Sub
Private Function GetData() As DataTable
Dim dt As DataTable = New DataTable()
dt.Columns.AddRange(New DataColumn(2) {New DataColumn("Payee", GetType(String)), New DataColumn("ACNo", GetType(String)), New DataColumn("IFSC", GetType(String))})
dt.Rows.Add("ABC", "98765432198765432198", "SBIN0008074")
dt.Rows.Add("DEF", "98765432198765432199", "UTBI0008074")
Return dt
End Function
Protected Sub Export(ByVal sender As Object, ByVal e As EventArgs)
Dim dt As DataTable = GetData()
Dim stream As MemoryStream = New MemoryStream()
Using wb As XLWorkbook = New XLWorkbook()
wb.Worksheets.Add(dt, "Bank")
wb.SaveAs(stream)
End Using
Response.Clear()
Response.Buffer = True
Response.AddHeader("content-disposition", "attachment; filename=" & "Payout Till.xls")
Response.ContentType = "application/vnd.ms-excel"
Response.BinaryWrite(stream.ToArray())
Response.End()
End Sub
Screenshot
