In this article I will explain how to export ASP.Net GridView data to Excel file and then sending it as file attachment in email without saving the exporting Excel file on disk.
The GridView data will be first fetched into a DataTable and then the DataTable will be exported into an Excel Sheet using OpenXml and ClosedXml Excel libraries.
Download DocumentFormat.OpenXml and ClosedXml Libraries
You can download the libraries using the following download locations.
Note: You will need to install the OpenXml SDK 2.0 in your Windows Operating System.
Database
I have made use of the following table Customers with the schema as follows.
![Export GridView data to Excel and send Excel File as email attachment in ASP.Net]()
I have already inserted few records in the table.
![Export GridView data to Excel and send Excel File as email attachment in ASP.Net]()
Note: You can download the database table SQL by clicking the download link below.
HTML Markup
The HTML Markup consists of an ASP.Net GridView with three columns and a Button to export GridView to Excel and send as email attachment.
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false">
<Columns>
<asp:BoundField DataField="CustomerId" HeaderText="Customer Id" ItemStyle-Width="80" />
<asp:BoundField DataField="Name" HeaderText="Name" ItemStyle-Width="120" />
<asp:BoundField DataField="Country" HeaderText="Country" ItemStyle-Width="120" />
</Columns>
</asp:GridView>
<br />
<asp:Button Text="Export" OnClick="ExportExcel" runat="server" />
Namespaces
You will need to import the following namespaces.
C#
using System.IO;
using System.Data;
using ClosedXML.Excel;
using System.Net.Mail;
using System.Configuration;
using System.Data.SqlClient;
VB.Net
Imports System.IO
Imports System.Data
Imports ClosedXML.Excel
Imports System.Net.Mail
Imports System.Configuration
Imports System.Data.SqlClient
Binding the GridView
The GridView is populated with the records from the Customers table inside the Page Load event of the page.
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
GridView1.DataSource = this.GetData();
GridView1.DataBind();
}
}
private DataTable GetData()
{
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand("SELECT * FROM Customers"))
{
using (SqlDataAdapter sda = new SqlDataAdapter())
{
cmd.Connection = con;
sda.SelectCommand = cmd;
using (DataTable dt = new DataTable())
{
sda.Fill(dt);
return dt;
}
}
}
}
}
VB.Net
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
If Not Me.IsPostBack Then
GridView1.DataSource = Me.GetData()
GridView1.DataBind()
End If
End Sub
Private Function GetData() As DataTable
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As New SqlConnection(constr)
Using cmd As New SqlCommand("SELECT * FROM Customers")
Using sda As New SqlDataAdapter()
cmd.Connection = con
sda.SelectCommand = cmd
Using dt As New DataTable()
sda.Fill(dt)
Return dt
End Using
End Using
End Using
End Using
End Function
![Export GridView data to Excel and send Excel File as email attachment in ASP.Net]()
Exporting GridView to Excel and sending as File Attachment in Email
When the Export button is clicked, the following event handler is executed. A DataTable is populated with records from the Customers table.
Then a Workbook object is created to which the DataTable is added as Worksheet using the Add method which accepts DataTable and the name of the Sheet as parameters.
Once the DataTable is added as a Worksheet to the Workbook, the WorkBook is saved to a MemoryStream which ultimately is converted to a Byte array.
Finally the Byte Array is added as attachment to the MailMessage object and the Email is sent.
Note: I am using Gmail Mail settings for this example, but you can use settings of any Mail Server of your choice.
C#
protected void ExportExcel(object sender, EventArgs e)
{
//Get the GridView Data from database.
DataTable dt = GetData();
//Set DataTable Name which will be the name of Excel Sheet.
dt.TableName = "GridView_Data";
//Create a New Workbook.
using (XLWorkbook wb = new XLWorkbook())
{
//Add the DataTable as Excel Worksheet.
wb.Worksheets.Add(dt);
using (MemoryStream memoryStream = new MemoryStream())
{
//Save the Excel Workbook to MemoryStream.
wb.SaveAs(memoryStream);
//Convert MemoryStream to Byte array.
byte[] bytes = memoryStream.ToArray();
memoryStream.Close();
//Send Email with Excel attachment.
using (MailMessage mm = new MailMessage("sender@gmail.com", "recipient@gmail.com"))
{
mm.Subject = "GridView Exported Excel";
mm.Body = "GridView Exported Excel Attachment";
//Add Byte array as Attachment.
mm.Attachments.Add(new Attachment(new MemoryStream(bytes), "GridView.xlsx"));
mm.IsBodyHtml = true;
SmtpClient smtp = new SmtpClient();
smtp.Host = "smtp.gmail.com";
smtp.EnableSsl = true;
System.Net.NetworkCredential credentials = new System.Net.NetworkCredential();
credentials.UserName = "sender@gmail.com";
credentials.Password = "<password>";
smtp.UseDefaultCredentials = true;
smtp.Credentials = credentials;
smtp.Port = 587;
smtp.Send(mm);
}
}
}
}
VB.Net
Protected Sub ExportExcel(sender As Object, e As EventArgs)
'Get the GridView Data from database.
Dim dt As DataTable = GetData()
'Set DataTable Name which will be the name of Excel Sheet.
dt.TableName = "GridView_Data"
'Create a New Workbook.
Using wb As New XLWorkbook()
'Add the DataTable as Excel Worksheet.
wb.Worksheets.Add(dt)
Using memoryStream As New MemoryStream()
'Save the Excel Workbook to MemoryStream.
wb.SaveAs(memoryStream)
'Convert MemoryStream to Byte array.
Dim bytes As Byte() = memoryStream.ToArray()
memoryStream.Close()
'Send Email with Excel attachment.
Using mm As New MailMessage("sender@gmail.com", "recipient@gmail.com")
mm.Subject = "GridView Exported Excel"
mm.Body = "GridView Exported Excel Attachment"
'Add Byte array as Attachment.
mm.Attachments.Add(New Attachment(New MemoryStream(bytes), "GridView.xlsx"))
mm.IsBodyHtml = True
Dim smtp As New SmtpClient()
smtp.Host = "smtp.gmail.com"
smtp.EnableSsl = True
Dim credentials As New System.Net.NetworkCredential()
credentials.UserName = "sender@gmail.com"
credentials.Password = "<password>"
smtp.UseDefaultCredentials = True
smtp.Credentials = credentials
smtp.Port = 587
smtp.Send(mm)
End Using
End Using
End Using
End Sub
Exported GridView Excel as attachment in Gmail Mailbox
![Export GridView data to Excel and send Excel File as email attachment in ASP.Net]()
Exported GridView Excel opened up in Google Document Viewer
![Export GridView data to Excel and send Excel File as email attachment in ASP.Net]()
Downloads
