Wednesday 5 July 2017

Updating Multiline textbox RTF (with Enhanced rich text enabled) in SharePoint 2016

I had to update the multiline textbox with rich text format enabled with JQuery. Below code worked for me. It might help someone else also:

 <script type="text/javascript" src="/jquery-1.9.1.min.js"></script>
<script type="text/javascript">

$(document).ready(function() {

 var defaultText = "Hi";

 $('nobr:contains("Comments")').closest('tr').find('div.ms-rtestate-write').html(defaultText);

//"Comments" is my field title or column name . Replace this with your column name.
});

</script>

Wednesday 14 January 2015

Retrieve all documents from folders and sub-folders in a SharePoint document library using C#

I had some requirements from client, for fulfilling that I had to write a code for getting list of all documents from folders/sub-folders from a SharePoint library.

Below is the code which I used:



===========================MAIN METHOD================================
private void OCR_Document()
        {
            try
            {


                SPWebApplication webApp = SPWebApplication.Lookup(new Uri("webapplication URL"));
//iterates in each site and subsites
                foreach (SPSite site in webApp.Sites)
                {
                    foreach (SPWeb web in site.AllWebs)
                    {
     //iterates in each lists/libraries
                        foreach (SPList list in web.Lists)
                        {
      //selects only document libraries
                            if (Convert.ToString(list.BaseType) == "DocumentLibrary")
                            {        
       //check if document library contains folder
                                    if (list.Folders.Count > 0)
                                    {
                                                 
                                        SPQuery query = new SPQuery();
                                        query.Folder = list.RootFolder;
                                        
                                        SPListItemCollection itemCollection = web.Lists[list.RootFolder.ParentListId].GetItems(query);

                                        foreach (SPListItem subitem in itemCollection)
                                        {
          //iterate in each folder-subfolder, all folders will come into this loop
                                            
                                            if (subitem.ContentType.Name == "Folder")
                                            {
                                               iterateFolder(subitem.Folder);
                                            }
                                            else
                                            {
                                                //here you will get only files in subitem varialbe
            //write your code here for files
                                            }

                                        }
                                    }
                                    else
                                    {
                                        foreach (SPFile file in list.RootFolder.Files)
                                        {

                                            //here you will get only files
            //write your code here for files


                                        }

                                    }
                                
                            }
                        }
                    }
                }



            }
            catch
            {

                throw;
            }


        }
===========================ITERATE FOLDER METHOD====================
private void iterateFolder(SPFolder folder)
        {
            if (folder.SubFolders != null)
            {
   //check for subfolders
                foreach (SPFolder item in folder.SubFolders)
                {
                    //scroll through each subfolder and each child folder
                    iterateFolder(item.SubFolders.Folder);
                }
            }


            foreach (SPFile file in folder.Files)
            {
                
                 //here you will get only files
       //write your code here for files

            }


        }

Friday 2 January 2015

Migrate excel sheet data and documents from local folder to SharePoint 2013 document library C# CSOM

I had a requirements from client to develop a migration tool which will migrate the excel sheet data and documents from local folder to SharePoint 2013 document library.

The excel sheet contains the location of documents from local folder plus the metadata in its columns.

I have developed a console application which:
1) Checks the access of user on SharePoint site and document library
2) Gives the row number in which there is some wrong data which cannot be added in library
3) Pick the document from location specified in excelsheet
4) Adds the data from other excel columns as the meta data of the file.

Below is the sample excel file

1) The column DOCUMENTLOCATION shown below is mandatory
2) The columns (except DocumentLocation) from excel file should be present in document library
3) The case of column names in excel and in document library should be same.







------------------------------------------------------------------------------------------------------------------------------------------------------------MAIN PROGRAM----------(refer at bottom for config file)----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
using System;  
 using System.Collections.Generic;  
 using System.Linq;  
 using System.Text;  
 using System.Threading.Tasks;  
 using Microsoft.SharePoint.Client;  
 using System.IO;  
 using System.Web;  
 using System.Security;  
 using System.Configuration;  
 using System.Data;  
 using System.Data.OleDb;  
 using System.Net;  
 using System.Diagnostics;  
 #endregion  
 namespace MigrateExcel  
 {  
   class Program  
   {  
     #region Constants  
 //All the data is read from config file  
 //Full path of excel sheet from local folder in which the metadata is saved  
     private const string EXCELFILEPATHWITHFILENAME = "ExcelFilePathWithFileName";  
 //Sheet name usually it is sheet1  
     private const string EXCELSHEETNAME = "ExcelSheetName";  
 //Column name where the location of document is specified in excel sheet  
     private const string COLNAMEDOCUMENTLOCATION = "ColNameDocumentLocation";  
 //Library name  
     private const string SPLIBRARYNAME = "SPLibraryName";  
 //site URL  
     private const string SPSITEURL = "SPSiteURL";  
 //Message to be shown when user do not have access on library or library do not exist  
     private const string MSG_NOLIBRARY = "Msg_NoLibrary";  
 //Message to be shown if columns from excel sheet do not exist in library (case of columns will also //be checked  
     private const string MSG_UNMATCHEDCOLUMNS = "Msg_UnmatchedColumns";  
     private const string MSG_CORRECTURLLIBRARY = "Msg_CorrectURLLibrary";  
 //Path of notepad file to log exceptions  
     private const string ERRORFILEPATH = "ErrorFilePath";  
     static string _excelName;  
     #endregion  
     static void Main(string[] args)  
     {  
       try  
       {  
         _excelName = Path.GetFileName(ConfigurationManager.AppSettings[EXCELFILEPATHWITHFILENAME]).ToString();  
         ClientContext context = getContext(); //to check the access of user  
         context.Load(context.Web, w =&gt; w.Title);  
         context.ExecuteQuery();  
         if (context != null)  
         {  
           Web currentWeb = context.Web;  
           context.Load(currentWeb);  
           context.ExecuteQuery();  
           List docs = getLibrary(context);  
           if (docs != null)  
           {  
             if (getConfirmation(docs.Title, context.Web.Title))  
             {  
               if (!excelFileOpen())  
               {  
                 context.Load(docs.Fields);  
                 context.ExecuteQuery();  
                 if (checkColumns(getColumnNames(docs), getExcelColumns()))  
                 {  
                   AddDocument(context, docs);  
                 }  
                 else  
                 {  
                   Console.WriteLine(ConfigurationManager.AppSettings[MSG_UNMATCHEDCOLUMNS]);  
                 }  
               }  
               else  
               {  
                 //do nothing  
               }  
             }  
             else  
             {  
               //do nothing  
             }  
           }  
         }  
         Console.ReadKey();  
       }  
       catch (Exception ex)  
       {  
         WriteError(ex);  
       }  
     }  
     #region Methods  
     private static bool excelFileOpen()  
     {  
       string filePath = ConfigurationManager.AppSettings[EXCELFILEPATHWITHFILENAME];  
       bool open = false;  
       try  
       {  
         using (System.IO.File.Open(filePath, FileMode.Open)) { }  
       }  
       catch  
       {  
         Console.WriteLine("The file " + _excelName + " is already open or is unavailable.");  
         open = true;  
       }  
       return open;  
     }  
     private static bool getConfirmation(string LibTitle, string SiteTitle)  
     {  
       bool proceed = false;  
       try  
       {  
         Console.WriteLine(ConfigurationManager.AppSettings[MSG_CORRECTURLLIBRARY] + "\n");  
         Console.WriteLine("To proceed with migration of excel file " + _excelName + " to document libray: "  
           + LibTitle + " in site: " + SiteTitle + "\n Press Y or y then press Enter key. \n If you do not"  
           + " wish to proceed \n Press N or n then press Enter key.");  
         string answer = Console.ReadLine();  
         if (answer.ToLower().Equals("y"))  
         {  
           proceed = true;  
         }  
         else  
         {  
           //do nothing  
         }  
       }  
       catch (Exception)  
       {  
         throw;  
       }  
       return proceed;  
     }  
     private static void AddDocument(ClientContext context, List docs)  
     {  
       try  
       {  
         DataTable dt = ConvertExcelInToDataTable(ConfigurationManager.AppSettings[EXCELFILEPATHWITHFILENAME], ConfigurationManager.AppSettings[EXCELSHEETNAME], false, false);  
         List&lt;string&gt; excelColumn = getExcelColumns();  
         int i = 1;  
         foreach (DataRow row in dt.Rows)  
         {  
           FileCreationInformation newFile = new FileCreationInformation();  
           i += 1;  
           try  
           {  
             newFile.Content = System.IO.File.ReadAllBytes(Convert.ToString(row[ConfigurationManager.AppSettings[COLNAMEDOCUMENTLOCATION]]));  
             newFile.Url = Path.GetFileName(Convert.ToString(row[ConfigurationManager.AppSettings[COLNAMEDOCUMENTLOCATION]]));  
             newFile.Overwrite = true;  
             context.Load(docs.RootFolder.Files);  
             context.ExecuteQuery();  
             Microsoft.SharePoint.Client.File uploadFile = docs.RootFolder.Files.Add(newFile);  
             context.Load(uploadFile);  
             context.ExecuteQuery();  
             foreach (string col in excelColumn)  
             {  
               uploadFile.ListItemAllFields[col] = Convert.ToString(row[col]);  
             }  
             uploadFile.ListItemAllFields.Update();  
             context.ExecuteQuery();  
           }  
           catch (Exception e)  
           {  
             WriteError(e);  
             Console.WriteLine("There was a problem in copying the row :" + i);  
             deleteUploadedDoc(docs, newFile.Url, context);  
           }  
         }  
       }  
       catch (Exception)  
       {  
         throw;  
       }  
     }  
     private static DataTable ConvertExcelInToDataTable(string excelFilePath, string SheetName, bool addHeader, bool oneRow)  
     {  
       DataTable excelData = new DataTable();  
       try  
       {  
         string con = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFilePath + ";Extended Properties=Excel 12.0;";  
         using (OleDbConnection connection = new OleDbConnection(con))  
         {  
           connection.Open();  
           if (!oneRow)  
           {  
             OleDbCommand command = new OleDbCommand("select * from [" + SheetName + "$]", connection);  
             using (OleDbDataReader dr = command.ExecuteReader())  
             {  
               excelData.Load(dr);  
             }  
           }  
           else  
           {  
             OleDbCommand command = new OleDbCommand("select top 1 * from [" + SheetName + "$]", connection);  
             using (OleDbDataReader dr = command.ExecuteReader())  
             {  
               excelData.Load(dr);  
             }  
           }  
           if (addHeader)  
           {  
             // in some excel header column is missing, to creater header with the first record of excel sheet, so that the row is considered in Foreach loop  
             DataRow row = excelData.NewRow();  
             for (int i = 0; i &lt; excelData.Columns.Count; i++)  
             {  
               row[i] = excelData.Columns[i].ColumnName;  
             }  
             excelData.Rows.InsertAt(row, 0);  
           }  
           else  
           {  
             //do nothing  
           }  
         }  
       }  
       catch (Exception ex)  
       {  
         throw;  
       }  
       return excelData;  
     }  
     private static void deleteUploadedDoc(List docs, string url, ClientContext context)  
     {  
       try  
       {  
         Web web = context.Web;  
         Microsoft.SharePoint.Client.File f = docs.RootFolder.Files.GetByUrl(url);  
         context.Load(f);  
         f.DeleteObject();  
         context.ExecuteQuery(); // Delete file here but throw Exception         
         //Console.Write("deleted");  
       }  
       catch  
       {  
         throw;  
       }  
     }  
     private static ClientContext getContext()  
     {  
       ClientContext context = null;  
       try  
       {  
         context = new ClientContext(ConfigurationManager.AppSettings[SPSITEURL]);  
       }  
       catch (System.Exception)  
       {  
         throw;  
       }  
       return context;  
     }  
     private static List getLibrary(ClientContext context)  
     {  
       List docLib = null;  
       try  
       {  
         docLib = context.Web.Lists.GetByTitle(ConfigurationManager.AppSettings[SPLIBRARYNAME]);  
         context.Load(docLib);  
         context.ExecuteQuery();  
       }  
       catch (System.Exception)  
       {  
         Console.WriteLine(ConfigurationManager.AppSettings[MSG_NOLIBRARY]);  
         docLib = null;  
       }  
       return docLib;  
     }  
     private static List&lt;string&gt; getColumnNames(List docs)  
     {  
       List&lt;string&gt; list = new System.Collections.Generic.List&lt;string&gt;();  
       try  
       {  
         foreach (Field field in docs.Fields)  
         {  
           list.Add(Convert.ToString(field.Title));  
         }  
       }  
       catch (System.Exception)  
       {  
         throw;  
       }  
       return list;  
     }  
     private static List&lt;string&gt; getExcelColumns()  
     {  
       List&lt;string&gt; list = new System.Collections.Generic.List&lt;string&gt;();  
       try  
       {  
         DataTable dt = ConvertExcelInToDataTable(ConfigurationManager.AppSettings[EXCELFILEPATHWITHFILENAME], ConfigurationManager.AppSettings[EXCELSHEETNAME], false, true);  
         foreach (DataColumn col in dt.Columns)  
         {  
           if (Convert.ToString(col.ColumnName) != ConfigurationManager.AppSettings[COLNAMEDOCUMENTLOCATION])  
           {  
             list.Add(Convert.ToString(col.ColumnName));  
           }  
         }  
       }  
       catch (System.Exception)  
       {  
         throw;  
       }  
       return list;  
     }  
     private static bool checkColumns(List&lt;string&gt; getLibColNames, List&lt;string&gt; getExcelColNames)  
     {  
       bool equal = true;  
       try  
       {  
         foreach (string Excelitem in getExcelColNames)  
         {  
           if (!getLibColNames.Contains(Excelitem))  
           {  
             equal = false;  
             Console.WriteLine("The column name " + Excelitem + " do not exist in Document Library or the case do not match");  
           }  
           else  
           {  
             //do nothing  
           }  
         }  
       }  
       catch (System.Exception)  
       {  
         throw;  
       }  
       return equal;  
     }  
     #endregion  
     #region Exception  
     private static string GetExceptionMessage(Exception e)  
     {  
       StringBuilder message = new StringBuilder();  
       bool flag = false;  
       string strInnerExeption;  
       while (e != null)  
       {  
         if (flag)  
         {  
           strInnerExeption = "Inner Exception";  
         }  
         else  
         {  
           strInnerExeption = string.Empty;  
         }  
         // Get stack trace for the exception with source file information  
         var st = new StackTrace(e, true);  
         // Get the top stack frame  
         var frame = st.GetFrame(st.FrameCount - 1);  
         // Get the line number from the stack frame  
         var line = frame.GetFileLineNumber();  
         string method = frame.GetMethod().ToString();  
         int line1 = frame.GetFileLineNumber();  
         message.Append(Environment.NewLine);  
         message.Append("----------" + strInnerExeption + " Exception----------");  
         message.Append(Environment.NewLine);  
         message.Append("\t\r\nType : " + e.GetType().FullName);  
         message.Append(Environment.NewLine);  
         message.Append("\t\r\nMessage : " + e.Message);  
         message.Append(Environment.NewLine);  
         message.Append("\t\r\nSource : " + e.Source);  
         message.Append(Environment.NewLine);  
         message.Append("\t\r\nTargetSite : " + e.TargetSite);  
         message.Append(Environment.NewLine);  
         message.Append("\t\r\nLine Number : " + line + " : " + method);  
         message.Append(Environment.NewLine);  
         message.Append("\t\r\nStack Trace : \n" + e.StackTrace);  
         message.Append(Environment.NewLine);  
         message.Append("\t\r\n----------" + strInnerExeption + " Exception----------");  
         message.Append(Environment.NewLine);  
         flag = true;  
         e = e.InnerException;  
       }  
       return message.ToString();  
     }  
     protected static void WriteError(Exception e)  
     {  
       try  
       {  
         string errorMessage = GetExceptionMessage(e);  
         addToFile(errorMessage);  
       }  
       catch (Exception)  
       {  
       }  
     }  
     protected static void addToFile(string errorMessage)  
     {  
       try  
       {  
         string path = ConfigurationManager.AppSettings[ERRORFILEPATH] + DateTime.Today.ToString("yyyy_MM_dd") + ".txt";  
         string subPath = ConfigurationManager.AppSettings[ERRORFILEPATH];  
         bool isExists = System.IO.Directory.Exists(subPath);  
         if (!isExists)  
           System.IO.Directory.CreateDirectory(subPath);  
         if (!System.IO.File.Exists(path))  
         {  
           System.IO.File.Create(path).Close();  
         }  
         using (StreamWriter w = System.IO.File.AppendText(path))  
         {  
           //w.WriteLine("\r\nLog Entry : ");  
           //w.WriteLine("{0}", DateTime.Now.ToString(CultureInfo.InvariantCulture));  
           w.WriteLine("\r\nLog Entry : {0}", DateTime.Now.ToString("yyyy/MM/dd hh:mm:ss"));  
           string err = "Error in: " + "" +  
              ". Error Message:" + errorMessage;  
           w.WriteLine(err);  
           w.WriteLine("__________________________");  
           w.Flush();  
           w.Close();  
         }  
       }  
       catch (Exception)  
       {  
         //WriteError(ex.Message);  
       }  
     }  
     #endregion  
   }  
   //  }  
 }  
---------------------------------------------------------------------------------------------------------------------------------------------------------------------APP.CONFIG FILE-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
  </startup>
  <appSettings>
<add key="ErrorFilePath" value="D:\\Projects\\Tamu\\Codes\\Errors\\MigrateExcel"/>

    <add key="ExcelFilePathWithFileName" value="C:\\Users\\Downloads\\MigrationToolSampleData.xlsx" />
    <add key="ExcelSheetName" value="Sheet1" />
    <add key="ColNameDocumentLocation" value="DocumentLocation" />
    
    <add key="SPLibraryName" value="Documents" />    
    <add key="SPSiteURL" value="http://tamouh_srv01:8086/" />

    <add key="Msg_NoLibrary" value="Library does not exist or you do not have permissions on the library." />
    <add key="Msg_UnmatchedColumns" value="Documents" />
  <add key="Msg_CorrectURLLibrary" value="The site URL and document library provided are correct" />
  </appSettings>
</configuration>
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------




Friday 15 June 2012

To increase the width of "Multiselect" column using Jquery

We can easily increase the size of "Multi-Select" control is SharePoint 2010 document library (Edit Form.aspx)
There are many way to do that:
You can find your solutions here:
a) http://spservices.codeplex.com/wikipage?title=$%28%29.SPServices.SPSetMultiSelectSizes

Unfortunately none of them came to my rescue. (May be i did not know how to implement it)

But i found just another way to do that...

I used following Jquery

$(document).ready(function() {

  $("#FirstBoxIDTakenfromFF").attr('style','width:370px;height:125px');
  $("#SecondBoxIDTakenfromFF").attr('style','width:370px;height:125px');

});
</script>

where FirstBoxIDTakenfromFF and SecondBoxIDTakenfromFF are the ids which you get from your browser