Is it possible to attach a gridview as excel file automatically in email while ciicking email button in mvc?

Question

Hi currently i have one view called Exceptions. In that i have one button(Email button) and Gridview. Now what i want is, i want to send mail by clicking that Email button. While sending email that gridview have to attach as excel file automatically in email attachment. Is it possible in mvc. If it is possible means any one give some suggesstions and some links me to do this task.

Actually i know to how to download grid view as excel file separetly and attach that excel file manually. But that is not my requiement . I want to attach grid view as excel file automatically while clicking email button.

My model

public class MailModel
{
    public string From { get; set; }
    public string To { get; set; }
    public string Subject { get; set; }
    public string Body { get; set; }
    public string Password { get; set; }

}

My controller code

//gridView code

public ActionResult UserMasterDetails()
    {
        var userregistrationlist = db.UserMasters.ToList();
        return View(userregistrationlist);
    }

//Mail code

public ActionResult EmailWithAttachment()
     {
         return View();
     }
    [HttpPost]
    public ActionResult MailwithAttachment(MSSCOSEC.Models.MailModel objModelMail, HttpPostedFileBase fileUploader)
    {
        if (ModelState.IsValid)
        {
            string from = objModelMail.From; //example:- sourabh9303@gmail.com
            using (MailMessage mail = new MailMessage(from, objModelMail.To))
            {
                mail.Subject = objModelMail.Subject;
                mail.Body = objModelMail.Body;
                if (fileUploader != null)
                {
                    string fileName = Path.GetFileName(fileUploader.FileName);
                    mail.Attachments.Add(new Attachment(fileUploader.InputStream, fileName));
                }
                mail.IsBodyHtml = false;
                SmtpClient smtp = new SmtpClient();
                smtp.Host = "smtp.gmail.com";
                smtp.EnableSsl = true;
                NetworkCredential networkCredential = new NetworkCredential(from, "xxxxxx13332");
                smtp.UseDefaultCredentials = true;
                smtp.Credentials = networkCredential;
                smtp.Port = 587;
                smtp.Send(mail);
                ViewBag.Message = "Sent";
                return View("EmailWithAttachment", objModelMail);
            }
        }
        else
        {
            return View();
        }
    }

The above code is manually attach the exported gridview as excel file to mail. Buti need to attach the gridview as excel file automatocally while clicking email button. I tried level best to explain the issue. Anyone understand my issue and give some solution for my problem.

Advance thanks..

Answer 1

In the comments you mentioned you get the data from a DataTable and populate the GridView from it. Then I am sure you present that GridView to a user in a view. The user then can decide to email the GridView to someone. But the emailing code is in your controller, so when the user clicks 'Email` button, the browser will call your controller action.

You have 2 options:

1. Get the data from the database again and then create an Excel file from it.
2. When you get the data the first time, send it to client (browser), then the browser can send the data back to you. This will be necessary if the user changes the data in the gridview, filters certain rows. Then you can take that and convert it to Excel.

For both options I would use Closed XML NuGet package because it will do a lot of heavy lifting for you.

Option 1

Note we are not asking the browser for the data of the gridview, because we will simply get this from the db ourselves.

[HttpPost]
public ActionResult MailwithAttachment(MSSCOSEC.Models.MailModel objModelMail)
{
    if (ModelState.IsValid)
    {
        XLWorkbook wb = new XLWorkbook();
        DataTable dt = GetDataTableOrWhatever();
        wb.Worksheets.Add(dt,"WorksheetName");
        wb.SaveAs("WhateverName.xlsx");
       // Your code for attaching the file and emailing it
    }
}

To take an EF query result and throw it into a DataTable, you can do this in GetDataTableOrWhatever() method:

var query = db.UserMasters;
query.CopyToDataTable<UserMasters>();

Option 2

Your controller will need to accept a list of UserMasters, this will be the filtered or modified items in the gridview so you can email it:

[HttpPost]
public ActionResult MailwithAttachment(MSSCOSEC.Models.MailModel objModelMail, IList<UserMasters> gridContents)
{
    if (ModelState.IsValid)
    {
        XLWorkbook wb = new XLWorkbook();
        DataTable dt = gridContents.ToDataTable();
        wb.Worksheets.Add(dt,"WorksheetName");
        wb.SaveAs("WhateverName.xlsx");
       // Your code for attaching the file and emailing it
    }
}

And here is the extension method for converting a list to a DataTable which I got from this answer:

public static DataTable ToDataTable<T>(this IList<T> data)
{
    PropertyDescriptorCollection props =
        TypeDescriptor.GetProperties(typeof(T));
    DataTable table = new DataTable();
    for(int i = 0 ; i < props.Count ; i++)
    {
        PropertyDescriptor prop = props[i];
        table.Columns.Add(prop.Name, prop.PropertyType);
    }
    object[] values = new object[props.Count];
    foreach (T item in data)
    {
        for (int i = 0; i < values.Length; i++)
        {
            values[i] = props[i].GetValue(item);
        }
        table.Rows.Add(values);
    }
    return table;        
}