Monday, 29 January 2018

SharePoint list data export to excel webjob

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\" />
      <!--exportLocation example: D:\Export\... Please use the same struture -->
   
    </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-----------------------------------

Wednesday, 17 January 2018

SharePoint CSR in List and Forms:

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 "<span><font style='background-color:green;display:block;'>" + ctx.CurrentItem.ProjectStatus + "</font></span>";
    }
    else if (choiceValue == 'InProgress') {
        return "<span><font style='background-color:yellow;display:block;'>" + ctx.CurrentItem.ProjectStatus + "</font></span>";
    }
    else
    {
        return "<span><font style='background-color:red;display:block;'>" + ctx.CurrentItem.ProjectStatus + "</font></span>";
    }
}
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;"><span style="color: #fff; position:absolute; text-align: center; width: 100px;">'
        + ctx.CurrentItem.Rating + '</span><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 "<span style='color :#f00'>" + priority + "</span>";
            break;
        case "(2) Normal":
            return "<span style='color :#ff6a00'>" + priority + "</span>";
            break;
        case "(3) Low":
            return "<span style='color :#cab023'>" + priority + "</span>";
    }
}

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 "<span title='" + bodyValue + "'>" + newBodyValue + "</span>";        

}