Informatics People - SharePoint developer: Console app - Custom List Data Export to Excel -Send Email to Report

using ExportListToExcel.Properties;

using SP = Microsoft.SharePoint.Client;

using System;

using System.Collections.Generic;

using System.IO;

using System.Linq;

using System.Net;

using System.Security;

using System.Text;

using System.Threading.Tasks;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Drawing;

using System.Data;

using System.Reflection;

using Microsoft.SharePoint.Client;

using Microsoft.SharePoint.Client.UserProfiles;

using System.Net.Mail;

namespace ExportListToExcel

{

class Program

{

static void Main(string[] args)

{

using (SP.ClientContext clientContext = new SP.ClientContext(Settings.Default.SiteUrl))

{

try

{

GetListReport(clientContext, Settings.Default.ListName, Settings.Default.PathName, DateTime.Now.ToString("dd MMM yyyy")+"HitsReport");

GetListReport(clientContext, Settings.Default.NewsListName, Settings.Default.PathName, DateTime.Now.ToString("dd MMM yyyy") + "NewsPages");

GetListReport(clientContext, "HomePageHits", Settings.Default.PathName, DateTime.Now.ToString("dd MMM yyyy") + "HomePageHits");

}

catch (Exception ex)

{

Console.WriteLine();

Console.ForegroundColor = ConsoleColor.Red;

Console.WriteLine("Authentication failed." + ex.Message);

Console.ForegroundColor = ConsoleColor.Gray;

}

public static void GetListReport(SP.ClientContext clientContext,string listName,string PathName,string fileName)

{

DirectoryInfo dir = new DirectoryInfo(@""+PathName+"");

dir.Create();

string excelFileName = string.Format(@"" + PathName + "\\" + fileName + "_{0}.xls", DateTime.Now.Ticks.ToString());

string attachFileName = string.Format(fileName + "_{0}.xls", DateTime.Now.Ticks.ToString());

FileInfo file = new FileInfo(excelFileName);

StreamWriter streamWriter = file.CreateText();

StringWriter stringWriter = new StringWriter();

HtmlTextWriter htmlTextWriter = new HtmlTextWriter(stringWriter);

SP.List myList = clientContext.Web.Lists.GetByTitle(listName);

SP.View listView = myList.Views.GetByTitle("All Items");

Table tblListView = new Table();

tblListView.ID = "_tblListView";

tblListView.BorderStyle = BorderStyle.Solid;

tblListView.BorderWidth = Unit.Pixel(1);

tblListView.BorderColor = Color.Silver;

listView.RowLimit = 2147483647;

clientContext.Load(listView);

clientContext.ExecuteQuery();

SP.CamlQuery query = new SP.CamlQuery();

query.ViewXml = "<View Scope='RecursiveAll'><Query>" + listView.ListViewXml + "</Query></View>";

SP.ListItemCollection items = myList.GetItems(query);

clientContext.Load(myList);

clientContext.Load(myList.Fields);

clientContext.Load(listView.ViewFields);

clientContext.Load(items);

clientContext.ExecuteQuery();

if (items != null && items.Count > 0)

{

DataTable dt = new DataTable();

foreach (var field in items[0].FieldValues.Keys)

{

dt.Columns.Add(field);

}

var count = 1;

foreach (var item in items)

{

DataRow dr = dt.NewRow();

foreach (var obj in item.FieldValues)

{

if (obj.Value != null)

{

string type = obj.Value.GetType().FullName;

if (type == "Microsoft.SharePoint.Client.FieldLookupValue")

{

dr[obj.Key] = ((SP.FieldLookupValue)obj.Value).LookupValue;

}

else if (type == "Microsoft.SharePoint.Client.FieldUserValue")

{

dr[obj.Key] = ((SP.FieldUserValue)obj.Value).LookupValue;

}

else

{

if (obj.Key == "User")

{

dr[obj.Key] = GetUserName(obj.Value.ToString());

}

else if (obj.Key == "Time")

{

dr[obj.Key] = Convert.ToDateTime(obj.Value).ToString("dd/MMM/yyyy");

}

else

{

dr[obj.Key] = obj.Value;

}

else

{

dr[obj.Key] = null;

}

if (count == 10) { break; }

Console.WriteLine("item count." + count++);

dt.Rows.Add(dr);

}

DataView dvListViewData = dt.DefaultView;

dvListViewData.RowFilter = "Isnull(Counter,'') <> ''";

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 < listView.ViewFields.Count; i++)

{

tblListView.Rows[0].Cells.Add(new TableCell());

tblListView.Rows[0].Cells[i].Text = listView.ViewFields[i].ToString().Replace("LinkTitle", "Title");

}

for (int i = 0; i < dvListViewData.Count; i++)

{

tblListView.Rows.Add(new TableRow());

for (int j = 0; j < listView.ViewFields.Count; j++)

{

tblListView.Rows[i + 1].Cells.Add(new TableCell());

if (dt.Columns.Contains(listView.ViewFields[j].ToString().Replace("LinkTitle", "Title")))

{

tblListView.Rows[i + 1].BorderStyle = BorderStyle.Dotted;

tblListView.Rows[i + 1].Cells[j].Text = dvListViewData[i][listView.ViewFields[j].ToString().Replace("LinkTitle", "Title")].ToString();

}

tblListView.RenderControl(htmlTextWriter);

streamWriter.Write(stringWriter.ToString());

SendEMail("from@from.com", "jaisonarumugam@gmail.com", "Report-News/Links/HomePage", "Hi<br> Please find the attached report for your reference.", stringWriter, attachFileName);

htmlTextWriter.Close();

streamWriter.Close();

stringWriter.Close();

}

private static string GetUserName(string accountName)

{

if (accountName != "SHAREPOINT\\system")

{

SP.ClientContext clientContext = new SP.ClientContext(Settings.Default.SiteUrl);

// Get the people manager instance for tenant context

PeopleManager peopleManager = new PeopleManager(clientContext);

var managerData = peopleManager.GetUserProfilePropertyFor(accountName, "PreferredName");

clientContext.ExecuteQuery();

if (managerData != null && !String.IsNullOrEmpty(managerData.Value))

{

accountName = managerData.Value;

}

return accountName;

}

public static bool SendEMail(string toList, string ccList, string subject, string body, System.IO.StringWriter stringWrite,string attachfileName)

{

bool isSentMail = false;

// Get the mail server details

string smtpServerAddress = "smptaddress";

string fromAddress = "from@from.com";

var email = new MailMessage();

email.From = new MailAddress(fromAddress);

var a = System.Net.Mail.Attachment.CreateAttachmentFromString(stringWrite.ToString(), attachfileName);

email.Attachments.Add(a);

email.Priority = MailPriority.Normal;

email.To.Add(toList);

// email.CC.Add(ccList);

email.Bcc.Add(ccList);

// email.AlternateViews.Add(Mail_Body(dt));

email.Subject = subject;

email.IsBodyHtml = true;

email.Body = body;

// Set up the mail server and sent the email

SmtpClient mailServer = new SmtpClient(smtpServerAddress);

mailServer.Credentials = CredentialCache.DefaultNetworkCredentials;

mailServer.Send(email);

isSentMail = true;

return isSentMail;

}

<?xml version="1.0" encoding="utf-8" ?>

</sectionGroup>

</sectionGroup>

</configSections>

</startup>

<ExportListToExcel.Properties.Settings>

<value>http://sitename</value>

</setting>

<value>NewsList</value>

</setting>

<value>NewsList1</value>

</setting>

<value>D:\ReportListData</value>

</setting>

</ExportListToExcel.Properties.Settings>

</applicationSettings>

</configuration>

Informatics People - SharePoint developer

Search This Blog

Wednesday, January 3, 2018

Console app - Custom List Data Export to Excel -Send Email to Report

No comments:

Post a Comment