Email: Password:       Forgot Password 
    .netCodeSG
A Saarsha Group Online Community for dot net codes group like C#, Asp.NET, VB.NET, Sharepoint, JavaScript, JQuery, Ajax, SQL, WCF, WPF.
 
TECHNOLOGIES:
 

Export and send as an Attachment Grid View Data in ASP.NET

Article:
Viewed:  2531 
Posted On:  28/01/2015 09:56:28 
How we can export Grid View Data to an excel file? How we can send an email exported excel file in asp.net c#? 

In this article I am going to show how we can export our Grid View data to an excel  file and how we can send this excel file as an attachment by mail.

Below is my Data Table from which I am fetching records.

Data Table in Design Mode:

1.png

Image 1.

Data in Table:

2.png

Image 2.

Create a new Project in Visual Studio 2010 and add a reference of ClosedXML by using Manage NuGet Packages.

3.png

Image 3.

Now My aspx code is:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="ExportAndSendEmail.aspx.cs" Inherits="ExportAndSendEmailData.ExportAndSendEmail" %><!DOCTYPE html>
 
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <table style="width: 70%; border: solid 2px red; vertical-align: central;">
                <tr style="background-color: green; color: white; font-size: 15pt; font-weight: bold; height: 30px; text-align: center;">
                    <td>Export To Excel and Send as an Email
                    </td>
                </tr>
                <tr>
                    <td>
                        <asp:GridView ID="GridViewAllDataFromDB" runat="server" AutoGenerateColumns="False" EmptyDataRowStyle-ForeColor="Red"
                            EmptyDataText="There is no record." CellPadding="4" ForeColor="#333333" GridLines="None" Width="100%">
                            <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
                            <Columns>
                                <asp:BoundField DataField="StudentID" HeaderText="Student ID" HeaderStyle-HorizontalAlign="Left" />
                                <asp:BoundField DataField="Name" HeaderText="Name" HeaderStyle-HorizontalAlign="Left" />
                                <asp:BoundField DataField="Email" HeaderText="Email" HeaderStyle-HorizontalAlign="Left" />
                                <asp:BoundField DataField="Class" HeaderText="Class" HeaderStyle-HorizontalAlign="Left" />
                                <asp:BoundField DataField="EnrollYear" HeaderText="EnrollYear" HeaderStyle-HorizontalAlign="Left" />
                                <asp:BoundField DataField="City" HeaderText="City" HeaderStyle-HorizontalAlign="Left" />
                                <asp:BoundField DataField="Country" HeaderText="Country" HeaderStyle-HorizontalAlign="Left" />
                            </Columns>
                            <EditRowStyle BackColor="#999999" />
                            <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                            <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                            <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
                            <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
                            <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />

                            <SortedAscendingCellStyle BackColor="#E9E7E2" />
                            <SortedAscendingHeaderStyle BackColor="#506C8C" />
                            <SortedDescendingCellStyle BackColor="#FFFDF8" />
                            <SortedDescendingHeaderStyle BackColor="#6F8DAE" />
                        </asp:GridView>
                    </td>
                </tr>
            </table>
            <table style="width: 70%; border: solid 2px red; vertical-align: central;">
                <tr>
                    <td align="right">
                        <asp:Button ID="btnExportGridView" Text="Export & Send As Email" runat="server" OnClick="btnExportGridView_Click" /></td>
                </tr>
            </table>
        </div>
    </form>
</body>
</html>

 

My aspx.cs code:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using ClosedXML.Excel;
using System.IO;
using System.Net.Mail;
 
namespace ExportAndSendEmailData
{
    public partial class ExportAndSendEmail : System.Web.UI.Page
    {
        SqlDataAdapter da;
        DataSet ds = new DataSet();
        DataTable dt = new DataTable();
 
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                GetAllRecordFromDB();
            }
        }
 
        public void GetAllRecordFromDB()
        {
            SqlConnection con = new SqlConnection();
            con.ConnectionString = @"Data Source=MyPC\SqlServer2k8; Initial Catalog=SchoolManagement; Integrated Security=true;";
            SqlCommand cmd = new SqlCommand("SELECT *  FROM Student ORDER BY StudentID", con);
 
            da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
 
            if (!object.Equals(ds.Tables[0], null))
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    GridViewAllDataFromDB.DataSource = ds;
                    GridViewAllDataFromDB.DataBind();
 
                    dt = ds.Tables[0];
                }
                else
                {
                    GridViewAllDataFromDB.DataSource = null;
                    GridViewAllDataFromDB.DataBind();
                }
            }
        }
 
        protected void btnExportGridView_Click(object sender, EventArgs e)
        {            
            XLWorkbook wb = new XLWorkbook();
            dt.TableName = "MyGridViewData";
            wb.Worksheets.Add(dt);
            MemoryStream memoryStream = new MemoryStream();
            wb.SaveAs(memoryStream);
            byte[] bytes = memoryStream.ToArray();
            memoryStream.Close();
 
            //Sending Mail
            MailMessage mailMSG = new MailMessage("FROM@gmail.com", "TO@gmail.com");
            mailMSG.Subject = "Excel Sheet Exported From DB";
            mailMSG.Body = "Excel Sheet As an Attachment";
            
            mailMSG.Attachments.Add(new Attachment(new MemoryStream(bytes), "MyGridView.xlsx"));
            mailMSG.IsBodyHtml = true;
            SmtpClient smtp = new SmtpClient();
            smtp.Host = "smtp.gmail.com";
            smtp.EnableSsl = true;
            System.Net.NetworkCredential credentials = new System.Net.NetworkCredential();
            credentials.UserName = "my@gmail.com";
            credentials.Password = "GmailIDPassword";
            smtp.UseDefaultCredentials = true;
            smtp.Credentials = credentials;
            smtp.Port = 587;
            smtp.Send(mailMSG);
        }
    }
}


Now run the application and click on button:

4.png

Image 4.

Now check your mail.  J

  Comment:
 
By dotnet  On  06/04/2016 01:37:56
         HOME   |   Submit Article   |   Contact Us   |   About Us   |   Terms & Condition   |   Advertise With us