Sahdev Patro: 2018

1. Update the Program.config as below.

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<startup>
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.2" />
</startup>
<appSettings>
<add key="siteUrl" value="https://ABC.sharepoint.com/teams/SiteName" />
<add key="username" value="XYZ@Domain.com" />
<add key="password" value="********" />
<add key="spListName" value="testList" />
<add key="viewName" value="All Items" />
<add key="excelName" value="ExportExcel" />
<add key="exportLocation" value="D:\Export\" />


</appSettings>
</configuration>

2. Copy the below code and paste in Program.cs file.

using Microsoft.SharePoint.Client;
using System;
using System.Configuration;
using System.Data;
using System.IO;
using System.Security;

namespace TestExport
{
class Program
{
public static string siteUrl = ConfigurationManager.AppSettings["siteUrl"];
public static string username = ConfigurationManager.AppSettings["username"];
public static string password = ConfigurationManager.AppSettings["password"];

public static string spListName = ConfigurationManager.AppSettings["spListName"];
public static string viewName = ConfigurationManager.AppSettings["viewName"];

public static string excelName = ConfigurationManager.AppSettings["excelName"];
public static string exportLocation = ConfigurationManager.AppSettings["exportLocation"];

public static bool isSuccess = true;

static void Main(string[] args)
{
try
{
DataTable table = new DataTable();
Program p = new Program();
table = p.GetDataTableFromListItemCollection();

#region Export to excel
p.WriteDataTableToExcel(table, spListName, exportLocation + excelName + ".xlsx", "Details");
Console.WriteLine();
Console.WriteLine();
if (isSuccess)
{
Console.ForegroundColor = ConsoleColor.Green;
Console.WriteLine("List data exported to excel completed successfully. Location: " + exportLocation);
}
else
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("Alert: Please ensure that the details entered in the config file are correct.");
}
Console.Read();
#endregion
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
Console.Read();
}
}
private DataTable GetDataTableFromListItemCollection()
{
string strWhere = string.Empty;
string filePath = string.Empty;

DataTable dtGetReqForm = new DataTable();
using (var clientContext = new ClientContext(siteUrl))
{
try
{
SecureString passWord = new SecureString();
foreach (char c in password.ToCharArray()) passWord.AppendChar(c);
clientContext.Credentials = new SharePointOnlineCredentials(username, passWord);
Console.WriteLine("Connecting \"" + siteUrl + "\"");
Console.WriteLine("Loading...");
Web Oweb = clientContext.Web;
clientContext.Load(Oweb);
clientContext.ExecuteQuery();
List spList = clientContext.Web.Lists.GetByTitle(spListName);
clientContext.Load(spList);
clientContext.Load(spList.Views);
clientContext.ExecuteQuery();
Console.WriteLine("Getting List: " + spListName);
Console.WriteLine("Loading...");

if (spList != null && spList.ItemCount > 0)
{
View view = spList.Views.GetByTitle(viewName);
clientContext.Load(view);
clientContext.ExecuteQuery();
ViewFieldCollection viewFields = view.ViewFields;
clientContext.Load(viewFields);
clientContext.ExecuteQuery();

CamlQuery query = new CamlQuery();
query.ViewXml = "<View><Query>" + view.ViewQuery + "</Query></View>";
ListItemCollection listItems = spList.GetItems(query);

clientContext.Load(listItems);
clientContext.ExecuteQuery();

if (listItems != null && listItems.Count > 0)
{
foreach (var field in viewFields)
{
dtGetReqForm.Columns.Add(field);
}

foreach (var item in listItems)
{
DataRow dr = dtGetReqForm.NewRow();

#region download attachments
if (Convert.ToBoolean(item["Attachments"].ToString()))
{
Folder folder = Oweb.GetFolderByServerRelativeUrl(Oweb.Url + "/Lists/" + spListName + "/Attachments/" + item["ID"]);
clientContext.Load(folder);
clientContext.ExecuteQuery();

FileCollection attachments = folder.Files;
clientContext.Load(attachments);
clientContext.ExecuteQuery();
foreach (Microsoft.SharePoint.Client.File oFile in attachments)
{
ClientResult<Stream> data = oFile.OpenBinaryStream();
clientContext.ExecuteQuery();
filePath = exportLocation;

string subPath = item["ID"].ToString();
bool exists1 = System.IO.Directory.Exists(filePath + "\\" + subPath);

if (!exists1)
System.IO.Directory.CreateDirectory(filePath + "\\" + subPath);

Console.WriteLine("Downloading file: " + oFile.Name);
var fileName = Path.Combine(filePath + "\\" + subPath, oFile.Name);
using (FileStream fileStream = System.IO.File.OpenWrite(fileName))
{
if (data != null)
{
using (var memory = new MemoryStream())
{
byte[] buffer = new byte[1024 * 64];
int nread = 0;

while ((nread = data.Value.Read(buffer, 0, buffer.Length)) > 0)
{
memory.Write(buffer, 0, nread);
}
memory.Seek(0, SeekOrigin.Begin);
memory.CopyTo(fileStream);
}
}
}
}
}
#endregion
for (int i = 0; i < viewFields.Count; i++)
{
string key = viewFields[i];
string value = string.Empty;
//string type = item.FieldValues[i].GetType().ToString();
if (item[key] != null)
{
if (i == viewFields.Count - 1)
{

}
if (item.FieldValues[key].GetType().Name == "FieldLookupValue" || item.FieldValues[key].GetType().Name == "FieldUserValue")
{
value = ((Microsoft.SharePoint.Client.FieldLookupValue)(item[key])).LookupValue;
}
else if (item.FieldValues[key].GetType().Name == "FieldUserValue[]")
{
if (((Microsoft.SharePoint.Client.FieldUserValue[])(item.FieldValues[key]))[0].GetType().Name == "FieldLookupValue" || ((Microsoft.SharePoint.Client.FieldUserValue[])(item.FieldValues[key]))[0].GetType().Name == "FieldUserValue")
{
value = (((Microsoft.SharePoint.Client.FieldUserValue[])(item.FieldValues[key]))[0]).LookupValue;
}
}
else if (item.FieldValues[key].GetType().Name == "FieldUrlValue")
{
value = ((Microsoft.SharePoint.Client.FieldUrlValue)(item[key])).Description;
}
else
{
value = item[key].ToString();
}
}

dr[key] = value;
}

dtGetReqForm.Rows.Add(dr);
}
}
}
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
isSuccess = false;
}
finally
{
if (clientContext != null)
clientContext.Dispose();
}
}
return dtGetReqForm;

}

public bool WriteDataTableToExcel(System.Data.DataTable dataTable, string worksheetName, string saveAsLocation, string ReporType)
{
Microsoft.Office.Interop.Excel.Application excel;
Microsoft.Office.Interop.Excel.Workbook excelworkBook;
Microsoft.Office.Interop.Excel.Worksheet excelSheet;
Microsoft.Office.Interop.Excel.Range excelCellrange;

try
{
// Delete the existing files
Array.ForEach(Directory.GetFiles(exportLocation),
delegate (string path) { System.IO.File.Delete(path); });

// Start Excel and get Application object.
excel = new Microsoft.Office.Interop.Excel.Application();

// for making Excel visible
excel.Visible = false;
excel.DisplayAlerts = false;

// Creation a new Workbook
excelworkBook = excel.Workbooks.Add(Type.Missing);

// Work sheet
excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelworkBook.ActiveSheet;
excelSheet.Name = worksheetName;

// loop through each row and add values to our sheet
int rowcount = 1;
int finalColumn = 1;
foreach (DataRow datarow in dataTable.Rows)
{
int exclColumn = 1;
rowcount += 1;
for (int i = 1; i <= dataTable.Columns.Count; i++)
{
if (dataTable.Columns[i - 1].ColumnName != "Attachments" && !dataTable.Columns[i - 1].ColumnName.StartsWith("_") && dataTable.Columns[i - 1].ColumnName != "MetaInfo" && dataTable.Columns[i - 1].ColumnName != "owshiddenversion" && dataTable.Columns[i - 1].ColumnName != "FileRef" && dataTable.Columns[i - 1].ColumnName != "UniqueId" && dataTable.Columns[i - 1].ColumnName != "FSObjType" && dataTable.Columns[i - 1].ColumnName != "ContentTypeId" && dataTable.Columns[i - 1].ColumnName != "File_x0020_Type" && dataTable.Columns[i - 1].ColumnName != "SMLastModifiedDate" && dataTable.Columns[i - 1].ColumnName != "SMTotalSize" && dataTable.Columns[i - 1].ColumnName != "ItemChildCount" && dataTable.Columns[i - 1].ColumnName != "FolderChildCount" && dataTable.Columns[i - 1].ColumnName != "ScopeId" && dataTable.Columns[i - 1].ColumnName != "GUID" && dataTable.Columns[i - 1].ColumnName != "WorkflowInstanceID" && dataTable.Columns[i - 1].ColumnName != "FileDirRef" && dataTable.Columns[i - 1].ColumnName != "SortBehavior" && dataTable.Columns[i - 1].ColumnName != "FileLeafRef" && dataTable.Columns[i - 1].ColumnName != "SyncClientId" && dataTable.Columns[i - 1].ColumnName != "ProgId" && dataTable.Columns[i - 1].ColumnName != "AppEditor" && dataTable.Columns[i - 1].ColumnName != "WorkflowVersion" && dataTable.Columns[i - 1].ColumnName != "InstanceID" && dataTable.Columns[i - 1].ColumnName != "Order" && dataTable.Columns[i - 1].ColumnName != "Restricted" && dataTable.Columns[i - 1].ColumnName != "OriginatorId" && dataTable.Columns[i - 1].ColumnName != "AppAuthor")
{
// on the first iteration we add the column headers
if (rowcount == 2)
{
excelSheet.Cells[1, exclColumn] = dataTable.Columns[i - 1].ColumnName;
excelSheet.Cells.Font.Color = System.Drawing.Color.Black;
}

if (datarow[i - 1].ToString() != "")
excelSheet.Cells[rowcount, exclColumn] = datarow[i - 1].ToString();
else
excelSheet.Cells[rowcount, exclColumn] = null;

//for alternate rows
if (rowcount > 2)
{
if (i == dataTable.Columns.Count)
{
if (rowcount % 2 == 0)
{
excelCellrange = excelSheet.Range[excelSheet.Cells[rowcount, 1], excelSheet.Cells[rowcount, exclColumn]];
FormattingExcelCells(excelCellrange, "#CCCCFF", System.Drawing.Color.Black, false);
}

}
}

exclColumn += 1;
finalColumn = exclColumn - 1;
}
}
}

// now we resize the columns
excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[rowcount, finalColumn]];
excelCellrange.EntireColumn.AutoFit();
Microsoft.Office.Interop.Excel.Borders border = excelCellrange.Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;

excelCellrange = excelSheet.Range[excelSheet.Cells[1, 1], excelSheet.Cells[1, finalColumn]];
FormattingExcelCells(excelCellrange, "#000099", System.Drawing.Color.White, true);

//now save the workbook and exit Excel
excelworkBook.SaveAs(saveAsLocation);
excelworkBook.Close();
excel.Quit();
return true;
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
isSuccess = false;
return false;
}
finally
{
excelSheet = null;
excelCellrange = null;
excelworkBook = null;
}

}

public void FormattingExcelCells(Microsoft.Office.Interop.Excel.Range range, string HTMLcolorCode, System.Drawing.Color fontColor, bool IsFontbool)
{
range.Interior.Color = System.Drawing.ColorTranslator.FromHtml(HTMLcolorCode);
range.Font.Color = System.Drawing.ColorTranslator.ToOle(fontColor);
if (IsFontbool == true)
{
range.Font.Bold = IsFontbool;
}
}
}
}

----------------------------End-----------------------------------

Referred the below links:

1. Change Footer

-------------

SPClientTemplates.TemplateManager.RegisterTemplateOverrides({

Templates: {

Footer: function (ctx) {

return String.format("Hello world from {0}!", ctx.ListTitle);

}

});

// ******* Color coding based on status column- Colour complete row **********

SPClientTemplates.TemplateManager.RegisterTemplateOverrides({

OnPostRender: function (ctx) {

var rows = ctx.ListData.Row;

for (var i = 0; i < rows.length; i++) {

var selectedVal = rows[i].Choice;

var color = (selectedVal == "Good") ? "#228B22" : (selectedVal == "Ok") ? "#FFE4B5" : "#DC143C";

var myIid = GenerateIIDForListItem(ctx, rows[i]);

var tr = document.getElementById(myIid);

tr.style.backgroundColor = color;

}

});

2. Color coding based on status column- Colour only status column

--------------------------------------------------------------

(function () {

var condFieldCtx = {};

// Define template variable

condFieldCtx.Templates = {};

// Define your required fields and functions to call in each case.

// In our case the field is Progress

// Override Function is PatientProgressViewTemplate

condFieldCtx.Templates.Fields = {

"ProjectStatus": { "View": PriorityFormat } // Choice is the field name

};

//conditional formatting for a complete row comes here

condFieldCtx.OnPostRender = [HighlightRowOverride];

// Register the template override with SP2013

SPClientTemplates.TemplateManager.RegisterTemplateOverrides(condFieldCtx);

})();

function PriorityFormat(ctx) {

var choiceValue = ctx.CurrentItem.ProjectStatus;

if (choiceValue == 'Open') {

return "" + ctx.CurrentItem.ProjectStatus + "";

}

else if (choiceValue == 'InProgress') {

return "" + ctx.CurrentItem.ProjectStatus + "";

}

else

{

return "" + ctx.CurrentItem.ProjectStatus + "";

}

function HighlightRowOverride(ctx) {

var rows = ctx.ListData.Row;

for (var i = 0; i < rows.length; i++) {

var myIid = GenerateIIDForListItem(ctx, rows[i]);

var tr = document.getElementById(myIid);

ctx.skipNextAnimation = true;

}

3. Show percentage bar in list

---------------------------

(function () {

var condFieldCtx = {};

// Define template variable

condFieldCtx.Templates = {};

// Define your required fields and functions to call in each case.

// In our case the field is Progress

// Override Function is PatientProgressViewTemplate

condFieldCtx.Templates.Fields = {

"Rating": { "View": PercentCompleteFormat } // Choice is the field name

};

//conditional formatting for a complete row comes here

condFieldCtx.OnPostRender = [HighlightRowOverride];

// Register the template override with SP2013

SPClientTemplates.TemplateManager.RegisterTemplateOverrides(condFieldCtx);

})();

function PercentCompleteFormat(ctx) {

var returnvalue = '<div style="background: #BCBCBC; display:block; height: 20px; width: 100px;">'

+ ctx.CurrentItem.Rating + '<div style="background: #D2421F; height: 100%; width: '

+ ctx.CurrentItem.Rating.replace(" %", "") + '%"></div></div>';

return returnvalue;

}

function HighlightRowOverride(ctx) {

var rows = ctx.ListData.Row;

for (var i = 0; i < rows.length; i++) {

var myIid = GenerateIIDForListItem(ctx, rows[i]);

var tr = document.getElementById(myIid);

ctx.skipNextAnimation = true;

}

4. Colour only the text based on status

------------------------------------

(function () {

// Create object that have the context information about the field that we want to change it's output render

var priorityFiledContext = {};

priorityFiledContext.Templates = {};

priorityFiledContext.Templates.Fields = {

// Apply the new rendering for Priority field on List View

"Priority": { "View": priorityFiledTemplate }

};

SPClientTemplates.TemplateManager.RegisterTemplateOverrides(priorityFiledContext);

})();

// This function provides the rendering logic for list view

function priorityFiledTemplate(ctx) {

var priority = ctx.CurrentItem[ctx.CurrentFieldSchema.Name];

// Return html element with appropriate color based on priority value

switch (priority) {

case "(1) High":

return "" + priority + "";

break;

case "(2) Normal":

return "" + priority + "";

break;

case "(3) Low":

return "" + priority + "";

}

5. See multi line text content in tool tip in list

-----------------------------------------------

(function () {

// Create object that have the context information about the field that we want to change it's output render

var bodyFiledContext = {};

bodyFiledContext.Templates = {};

bodyFiledContext.Templates.Fields = {

// Apply the new rendering for Body field on list view

"Body": { "View": bodyFiledTemplate }

};

SPClientTemplates.TemplateManager.RegisterTemplateOverrides(bodyFiledContext);

})();

// This function provides the rendering logic

function bodyFiledTemplate(ctx) {

var bodyValue = ctx.CurrentItem[ctx.CurrentFieldSchema.Name];

//This regex expression use to delete html tags from the Body field

var regex = /(<([^>]+)>)/ig;

bodyValue = bodyValue.replace(regex, "");

var newBodyValue = bodyValue;

if (bodyValue && bodyValue.length >= 100)

{

newBodyValue = bodyValue.substring(0, 100) + " ...";

}

return "" + newBodyValue + "";

}

Sahdev Patro

Monday 29 January 2018

SharePoint list data export to excel webjob

Wednesday 17 January 2018

SharePoint CSR in List and Forms:

1. Change Footer