Thursday 31 March 2016

Check if browser is IE in SharePoint



<script language="javascript">
function GetIEVersion() {
  var sAgent = window.navigator.userAgent;
  var Idx = sAgent.indexOf("MSIE");

  // If IE, return version number.
  if (Idx > 0)
    return parseInt(sAgent.substring(Idx+ 5, sAgent.indexOf(".", Idx)));

  // If IE 11 then look for Updated user agent string.
  else if (!!navigator.userAgent.match(/Trident\/7\./))
    return 11;

  else
    return 0; //It is not IE
}

if (GetIEVersion() > 0)
   alert("This is IE " + GetIEVersion());
else
   alert("This is not IE.");

</script>

Monday 28 March 2016

Exception logging and export datatable to excel using c#

Log exception to Text

Log.ErrorLog(ex, "excel"); to export to text file in your exception handling

Log Datatable to excel

Log.Excel_FromDataTable(datatable);

below class will export to text file and excel.

using System;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;

namespace DataMigrationHDM
{
    internal class Log
    {
        public static void Excel_FromDataTable(System.Data.DataTable dt)
        {
            try
            {


                // Create an Excel object and add workbook...

                ApplicationClass excel = new ApplicationClass();

                Workbook workbook = excel.Application.Workbooks.Add(true); // true for object template???

                // Add column headings...
                int iCol = 0;
                foreach (DataColumn c in dt.Columns)
                {
                    iCol++;
                    excel.Cells[1, iCol] = c.ColumnName;
                }
                // for each row of data...
                int iRow = 0;
                foreach (DataRow r in dt.Rows)
                {
                    iRow++;

                    // add each row's cell data...
                    iCol = 0;
                    foreach (DataColumn c in dt.Columns)
                    {
                        iCol++;
                        excel.Cells[iRow + 1, iCol] = r[c.ColumnName];
                    }
                }

                // Global missing reference for objects we are not defining...
                object missing = System.Reflection.Missing.Value;
                Worksheet worksheet = (Worksheet)excel.ActiveSheet;
               
                string RDir = Directory.GetCurrentDirectory();
                string sYear = DateTime.Now.Year.ToString();
                string sMonth = DateTime.Now.Month.ToString(CultureInfo.InvariantCulture);
                string sDay = DateTime.Now.Day.ToString();
                string dirPath = RDir + @"\ReleasedStandards\" + sMonth + "-" + sDay + "-" + sYear;
                if (!Directory.Exists(dirPath))
                {
                    Directory.CreateDirectory(dirPath);
                }
                //string dateT = DateTime.Now.ToLongDateString();

                string sErrorTime = sDay + "-" + sMonth + "-" + sYear + " " + DateTime.Now.Hour.ToString() + "-" + DateTime.Now.Minute;
                workbook.SaveAs(dirPath + "\\Report_" + sErrorTime + ".xls",
           XlFileFormat.xlXMLSpreadsheet, missing, missing,
            false, false, XlSaveAsAccessMode.xlNoChange,
            missing, missing, missing, missing, missing);

                // If wanting to make Excel visible and activate the worksheet...
                excel.Visible = true;

                ((_Worksheet)worksheet).Activate();

                // //format excel on run time 



                // If wanting excel to shutdown...
                ((_Application)excel).Quit();
                MessageBox.Show("Report Generated", "Maintenance Tool...", MessageBoxButtons.OK, MessageBoxIcon.Information);

            }
            catch (Exception ex)
            {
                //MessageBox.Show("Error in webservice while Saving Excel.", "Maintenance Tool...", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                ErrorLog(ex, "Writng to excel");

            }
        }
        public static void ErrorLog(Exception ex,String info)
        {
            StreamWriter sw = null;

            try
            {
                string sLogFormat = DateTime.Now.ToShortDateString().ToString() + " " +
                                    DateTime.Now.ToLongTimeString().ToString() + " ==> ";

                string sPathName = Directory.GetCurrentDirectory();

                string sYear = DateTime.Now.Year.ToString();
                string sMonth = DateTime.Now.Month.ToString();
                string sDay = DateTime.Now.Day.ToString();

                string sErrorTime = sDay + "-" + sMonth + "-" + sYear;
                string dirPath = sPathName + @"\Logs\";
                if (!Directory.Exists(dirPath))
                {
                    Directory.CreateDirectory(dirPath);
                }
                sw = new StreamWriter(dirPath + "\\DocumentLibrary_ErrorLog_" + sErrorTime + ".txt", true);

                sw.WriteLine(sLogFormat + ex.Message + ex.StackTrace + info);
                sw.Flush();

            }
            catch (Exception ex1)
            {
                ErrorLog(ex1,"Lib");
            }
            finally
            {
                if (sw != null)
                {
                    sw.Dispose();
                    sw.Close();
                }
            }

        }
    }
}

Read excel to datatable using openXML DLL

Need to add openxml DLL reference from codeplex and use below code to read excel to datatable

static void Main(string[] args)
        {
DataTable dtRecords;
            dtRecords = new DataTable();
            dtRecords.Columns.Add("URL");        
            DataTable dt = new DataTable();

            SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open("C:\\Sites.xlsx.", false);
            {
                IEnumerable<Sheet> sheets =
                    spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();
                string relationshipId = sheets.First().Id.Value;
                WorksheetPart worksheetPart =
                    (WorksheetPart)spreadSheetDocument.WorkbookPart.GetPartById(relationshipId);
                Worksheet workSheet = worksheetPart.Worksheet;
                SheetData sheetData = workSheet.GetFirstChild<SheetData>();
                IEnumerable<Row> rows = sheetData.Descendants<Row>();
                foreach (Cell cell in rows.ElementAt(0))
                {
                    dt.Columns.Add(GetCellValue(spreadSheetDocument, cell));
                }
                foreach (Row row in rows)
                {
                    DataRow tempRow = dt.NewRow();
                    for (int i = 0; i < row.Descendants<Cell>().Count(); i++)
                    {
                        tempRow[i] = GetCellValue(spreadSheetDocument, row.Descendants<Cell>().ElementAt(i));
                    }
                    dt.Rows.Add(tempRow);
                }
                dt.Rows.RemoveAt(0);
                //CreateDocumentSetInMeetings(dt);
            }
            foreach (DataRow row in dt.Rows)
            {
           //do your stuff
            }
}
public static string GetCellValue(SpreadsheetDocument document, Cell cell)
        {
            SharedStringTablePart stringTablePart = document.WorkbookPart.SharedStringTablePart;
            if (cell.CellValue == null)
            {
                return "";
            }
            string value = cell.CellValue.InnerXml;
            if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
            {
                return stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText;
            }
            else
            {
                return value;
            }
        }

Tuesday 22 March 2016

Create update list item using .net client object model

Create List Item:

 ClientContext context = new ClientContext("Site Url");
            Web site = context.Web;
List announcementsList = context.Web.Lists.GetByTitle("List Name");
                        ListItemCreationInformation itemCreateInfo = new ListItemCreationInformation();
                        ListItem newItem = announcementsList.AddItem(itemCreateInfo);
                        newItem["Title"] = "test";
                        newItem["Col1"] = "";
                        newItem["Col2"] = "";
                        newItem["Col3"] = "";
                        newItem.Update();
                        context.ExecuteQuery();

Update list Item:

                        List Offboarding = context.Web.Lists.GetByTitle("List Name");
                        CamlQuery queryoff = new CamlQuery();
                        queryoff.ViewXml = "<View><Query><Where><Eq><FieldRef Name='ID' /><Value Type='Text'>" + id + "</Value></Eq></Where></Query></View>";
                        ListItemCollection OffboardingItems = Offboarding.GetItems(queryoff);
                        context.Load(Offboarding);
                        context.Load(OffboardingItems);
                        context.ExecuteQuery();
                        foreach (var itemoff in OffboardingItems)
                        {
                            itemoff["Title"] = "test";                          
                            itemoff.Update();

                        }
                        context.ExecuteQuery();

Access external list using .net client object model (CSOM)

ClientContext context = new ClientContext("Site URL");
            Web site = context.Web;
            var StudentList = site.Lists.GetByTitle(" Ext List Name");
            CamlQuery camlQuery = new CamlQuery();

            IQueryable<ListItem> termEmps = StudentList.GetItems(camlQuery);
            IEnumerable<ListItem> externalList = context.LoadQuery(termEmps);
            context.ExecuteQuery();

            var studentListData = from term in externalList
                                  select new
                                  {
                                      FULLNAME = term.FieldValues.ElementAt(1).Value.ToString(),
                                      Employeeid = term.FieldValues.ElementAt(2).Value.ToString(),
                                      SEPERATIONDATE = term.FieldValues.ElementAt(4).Value.ToString()
                                  };
            foreach (var empterm in studentListData)
            {
//do your stuff
}

Wednesday 9 March 2016

Header style sharepoint list view 2010

Please use webpart ID to use below styles

<style type="text/css">

/* === Title bar CSS === */

/* TR - title bar for web part */
#MSOZoneCell_WebPartWPQ5 .ms-WPHeader
{
  background-color:green;
}

/* H3 - Text in title bar of web part */
#MSOZoneCell_WebPartWPQ5 .ms-WPTitle a   
{
  color:white;
  font-family:"Comic Sans MS";
  font-size:24pt;
}

/* TD - far left and far right (corner) cells of title bar - useful for round corner tricks */
#MSOZoneCell_WebPartWPQ5 .ms-wpTdSpace
{
  /* background-image:url(' someimagepath '); */
  width:30px !important;
  background-color:red;
}

/* web part check box */
#MSOZoneCell_WebPartWPQ5 .ms-WPHeaderCbxHidden 
{
  display:none;   
}


/* === Web part background CSS === */

/* TD - background for all but title bar of web part */
#MSOZoneCell_WebPartWPQ5.s4-wpcell 
{
  background-color:lightgreen;
  /* border-style:dashed; */
  border-style:dashed;
  border-width:5px;
}

/* TD - paging area (i.e. 1 - 5) */ #MSOZoneCell_WebPartWPQ5 .ms-bottompaging td { background-color:yellow !important; } /* hide the gray line above "add new" link */   
#MSOZoneCell_WebPartWPQ5 .ms-partline
{
  display:none;
}

/* selected (clicked) web part background */ #MSOZoneCell_WebPartWPQ5.s4-wpActive { background-color:fuchsia; border-color:red; /* border-style:dotted; */
}   


/* === Column headings === */

/* color for sortable column headings */
#MSOZoneCell_WebPartWPQ5 .ms-vh-div a
{
  color:red !important;
}
/* color for non-sortable column headings */
#MSOZoneCell_WebPartWPQ5 .ms-vh-div
{
  color:red !important;
}


/* === List text CSS === */

/* item description text */
#MSOZoneCell_WebPartWPQ5 .ms-vb2,
#MSOZoneCell_WebPartWPQ5 .ms-vb-user a,
#MSOZoneCell_WebPartWPQ5 .ms-vb-title a
{
  color:yellow !important;
  font-size:12pt;
}

/* TR - alternating (#2,#4,#6...) row of web part */
#MSOZoneCell_WebPartWPQ5 .ms-alternating 
{
  background-color:navy;
}

</style>

Hide ribbon on item selection in sharepoint 2010 list view using jquery

<script src="/SiteAssets/jquery-1.8.3.min.js"></script>
 <script language="javascript">


 $(".s4-wpcell").removeAttr('onkeyup').removeAttr('onmouseup');

 </script>

Style to hide ribbon. title area and left navigation

<style type="text/css">
#s4-titlerow {
 DISPLAY: none !important
}
#RibbonContainer-TabRowLeft {
 DISPLAY: none !important
}
.ms-cui-tts {
 DISPLAY: none !important
}
.s4-ca {
 MARGIN-LEFT: 0px
}
#s4-leftpanel
{
DISPLAY: none
}
</style>