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
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
Comments
Post a Comment