Create Excel from SharePoint List Data and Send by email

During my daily task allocation I came across a task in which I was supposed to create excel from SharePoint list and then send the created excel via email. The procedure was to be repeated daily.

I used timer job to repeat task on daily basis. As I was unaware of the column that may exist in SharePoint list so I thought of using SharePoint views (i.e., AllItems) to get the list of fields. Also this facilitated my code to be dynamic enough to be replaces with custom created views.

Add a new class file to project:
Right click project > Add > class
Name class file: ListToExcelGenerator

namespace xxx
{
    /// <summary>
    /// Class to create Excel from Sharepoint List
    /// </summary>
    class ListToExcelGenerator
    {
        /// <summary>
        /// Sets file path as well file name.
        /// </summary>
        /// <param name="url">url of the web application/rootsite</param>
        public void ListToExcelConvertor(string url)
        {
            using (SPSite site = new SPSite(url))
            {
                using (SPWeb web = site.OpenWeb())
                {
                    string filePath = “Path of where to create file”;
                    string fileName = “file name”;
                    DirectoryInfo dir = new DirectoryInfo(filePath);
                    dir.Create();

                    FileInfo file = new FileInfo(filePath + "\\" + fileName + ".xls");
                    StreamWriter streamWriter = file.CreateText();

                    StringWriter stringWriter = new StringWriter();
                    HtmlTextWriter htmlTextWriter = new HtmlTextWriter(stringWriter);

                    string listName = “list name”;

                    Table tblData = GetListTableControl(url + "/Lists/" + listName.Trim(), listName.Trim());
                    tblData.RenderControl(htmlTextWriter);
                    streamWriter.Write(stringWriter.ToString());

                    htmlTextWriter.Close();
                    streamWriter.Close();
                    stringWriter.Close();
                }
            }
        }
        /// <summary>
        /// Creates excel from specified list
        /// </summary>
        /// <param name="strListURL">url to webapplication/rootsite</param>
        /// <param name="strListName">name of list</param>
        /// <returns></returns>
        private static Table GetListTableControl(string strListURL, string strListName)
        {
            //Table class object to get list data to Table
            Table tblListView = new Table();
            tblListView.ID = "_tblListView";
            tblListView.BorderStyle = BorderStyle.Solid;
            tblListView.BorderWidth = Unit.Pixel(1);
            tblListView.BorderColor = Color.Silver;

            using (SPSite site = new SPSite(strListURL.Trim()))
            {
                using (SPWeb web = site.OpenWeb())
                {

                    SPList list = web.Lists.TryGetList(strListName.Trim());
                    if (list != null)
                    {

                        SPView wpView = list.Views["All Items"];
                        wpView.RowLimit = 2147483647;


                        // query to find non deleted items from sharepoint list
                        SPQuery query = new SPQuery(wpView);
                        string softDeleteColumnName = “column for soft delete”;
                        query.Query = "<Where><Eq><FieldRef Name='" + softDeleteColumnName.Trim() + "'/><Value Type='bool'>false</Value></Eq></Where>";


                        SPListItemCollection items = list.GetItems(query);
                        if (items != null && items.Count > 0)
                        {
                            // Code to Create excel data format
                            DataTable tbListViewData = items.GetDataTable();
                            DataView dvListViewData = tbListViewData.DefaultView;
                            if (dvListViewData != null && dvListViewData.Count > 0)
                            {
                                tblListView.Rows.Add(new TableRow());
                                tblListView.Rows[0].BackColor = Color.Gainsboro;
                                tblListView.Rows[0].Font.Bold = true;

                                for (int i = 0; i < wpView.ViewFields.Count; i++)
                                {
                                    tblListView.Rows[0].Cells.Add(new TableCell());
                                    tblListView.Rows[0].Cells[i].Text = list.Fields.GetFieldByInternalName(wpView.ViewFields[i].ToString()).Title;
                                }

                                for (int i = 0; i < dvListViewData.Count; i++)
                                {
                                    tblListView.Rows.Add(new TableRow());

                                    for (int j = 0; j < wpView.ViewFields.Count; j++)
                                    {
                                        tblListView.Rows[i + 1].Cells.Add(new TableCell());

                                        if (tbListViewData.Columns.Contains(wpView.ViewFields[j].ToString()))
                                        {
                                            tblListView.Rows[i + 1].Cells[j].Text = dvListViewData[i][wpView.ViewFields[j].ToString()].ToString();
                                        }
                                    }
                                }
                            }
                        }
                        // excel fomrat code ends
                    }
                }
            }
            return tblListView;
        }
    }
}

Once excel is generated it’s time to send the created excel via email. I am reading the SharePoint outbound email configuration to send email. You can use SMTP if required.

Add a new class file to project:
Right click project > Add > class
Name class file: EmailSender


namespace xxx
{
    class EmailSender
    {
        /// <summary>
        /// prepares Email content
        /// </summary>
        /// <param name="url">web url</param>
        /// <returns>true if mail is send</returns>
        public bool SendEmail(string url)
        {
            string subject = string.Empty;
            string emailBody = string.Empty;
            bool output = false;
            SPSecurity.RunWithElevatedPrivileges(delegate()
            {
                using (SPSite site = new SPSite(url))
                {
                    using (SPWeb web = site.OpenWeb())
                    {

                        string from = string.Empty;
                        string smtpAddress = string.Empty;

                        if (web.Site.WebApplication.OutboundMailSenderAddress != null)
                        {
                            from = web.Site.WebApplication.OutboundMailSenderAddress;
                        }
                        if (site.WebApplication.OutboundMailServiceInstance.Server.Address != null)
                        {
                            smtpAddress = site.WebApplication.OutboundMailServiceInstance.Server.Address;
                        }
                        string filePath = “file path”;
                        string fileName = “file name”;
                        Attachment attachment = new Attachment(filePath + "\\" + fileName + ".xls");

                        string to = string.Empty;

                        to = “send email to address”;

                        subject = “subject of email”
                        emailBody = “email body”;
                        string errtext = string.Empty;
                        output = SendMail(smtpAddress.Trim(), subject.Trim(), from, to.Trim(), null, null, attachment, emailBody.Trim());
                    }
                }
            });
            return output;
        }

        /// <summary>
        /// Sends email
        /// </summary>
        /// <param name="smtpAddress"></param>
        /// <param name="subject"></param>
        /// <param name="from"></param>
        /// <param name="to"></param>
        /// <param name="cc"></param>
        /// <param name="bcc"></param>
        /// <param name="attachements"></param>
        /// <param name="body"></param>
        /// <returns></returns>
        public bool SendMail(string smtpAddress, string subject, string from, string to, string cc, string bcc, Attachment attachements, string body)
        {
            bool mailSent = false;
            SmtpClient smtpClient = null;

            smtpClient = new SmtpClient();
            smtpClient.Host = smtpAddress;


            MailMessage mailMessage = new MailMessage(from, to);//, subject, body);
            mailMessage.Subject = subject;
            if (!String.IsNullOrEmpty(cc))
            {
                MailAddress CCAddress = new MailAddress(cc);
                mailMessage.CC.Add(CCAddress);
            }
            if (!String.IsNullOrEmpty(bcc))
            {
                MailAddress BCCAddress = new MailAddress(bcc);
                mailMessage.Bcc.Add(BCCAddress);
            }
            mailMessage.Attachments.Add(attachements);

            StringBuilder eBody = new StringBuilder(body);

            mailMessage.Body = eBody.ToString();
            smtpClient.Send(mailMessage);
            mailSent = true;



            return mailSent;
        }
    }
}

The above code will return true if email is successfully send. Now it is just as to create a timer job create objects of above classes and invoke appropriate methods.

You can use following links to create a timer job.
Ref:
http://www.codeproject.com/Articles/403323/SharePoint-Create-Custom-Timer-Jobs

Regards,
Keyur Pandya