Hi rani,
Check this example. Now please take its reference and correct your code.
Database
For this example I have used of Northwind database that you can download using the link given below.
Download Northwind Database
For configuring entity framework refer below article.
Model
public class Customer
{
public string CustomerID { get; set; }
public string ContactName { get; set; }
public string City { get; set; }
public string Country { get; set; }
}
Namespaces
using System.IO;
using System.Linq;
using System.Net.Mail;
using ClosedXML.Excel;
using Microsoft.AspNetCore.Mvc;
Controller
public class HomeController : Controller
{
private DBCtx Context { get; }
public HomeController(DBCtx _context)
{
this.Context = _context;
}
public IActionResult Index()
{
return View(this.Context.Customers.Take(10).ToList());
}
[HttpPost]
public IActionResult Send()
{
DataTable dt = new DataTable("Customer");
dt.Columns.AddRange(new DataColumn[4] { new DataColumn("CustomerID"),
new DataColumn("ContactName"),
new DataColumn("City"),
new DataColumn("Country") });
foreach (var customer in this.Context.Customers.Take(10).ToList())
{
dt.Rows.Add(customer.CustomerID, customer.ContactName, customer.City, customer.Country);
}
byte[] bytes = null;
using (XLWorkbook wb = new XLWorkbook())
{
wb.Worksheets.Add(dt);
using (MemoryStream stream = new MemoryStream())
{
wb.SaveAs(stream);
bytes = stream.ToArray();
}
}
using (MailMessage mm = new MailMessage("sender@gmail.com", "reciever@gmail.com"))
{
mm.Subject = "HTML Table Exported Excel";
mm.Body = "HTML Table Exported Excel Attachment";
mm.Attachments.Add(new Attachment(new MemoryStream(bytes), "Customers.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);
}
return View("Index", this.Context.Customers.Take(10).ToList());
}
}
View
@using Excel_Email_Core_MVC.Models;
@model IEnumerable<Customer>
@addTagHelper*, Microsoft.AspNetCore.Mvc.TagHelpers
@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Index</title>
</head>
<body>
<h4>Customers</h4>
<hr />
<div id="Grid">
<table cellpadding="5" cellspacing="0" style="border: 1px solid #ccc;font-size: 9pt;">
<tr>
<th style="background-color: #B8DBFD;border: 1px solid #ccc">CustomerID</th>
<th style="background-color: #B8DBFD;border: 1px solid #ccc">ContactName</th>
<th style="background-color: #B8DBFD;border: 1px solid #ccc">City</th>
<th style="background-color: #B8DBFD;border: 1px solid #ccc">Country</th>
</tr>
@foreach (Customer customer in Model)
{
<tr>
<td style="width:120px;border: 1px solid #ccc">@customer.CustomerID</td>
<td style="width:120px;border: 1px solid #ccc">@customer.ContactName</td>
<td style="width:120px;border: 1px solid #ccc">@customer.City</td>
<td style="width:120px;border: 1px solid #ccc">@customer.Country</td>
</tr>
}
</table>
</div>
<br />
<form asp-action="Send" asp-controller="Home" method="post">
<input type="submit" value="Send Email" class="btnSend" />
</form>
</body>
</html>
Screenshots
The HTML Table
Attached Excel
