Friday, June 24, 2016

Get ListItem Attachments using Iterating Large SharePoint Lists with PowerShell

Get ListItem Attachments using Iterating Large SharePoint Lists with PowerShell.

$SPAssignment = Start-SPAssignment
$webUrl = "http://sharepointurl:1234/"   
$listname = "ListDisplayName"  
$destinationfolder = "c:\backupfiles"    
$site = new-object Microsoft.SharePoint.SPSite($webUrl)   
$web = $site.OpenWeb()        
$list = $web.Lists[$listname] 
$spQuery = New-Object Microsoft.SharePoint.SPQuery
$query = '<Where><IsNotNull><FieldRef Name="ID" /></IsNotNull></Where>'
$spQuery.ViewAttributes = "Scope = 'Recursive'"
$spQuery.Query = $query
$spQuery.RowLimit = 2000
Write-Host "    ItemCount: " $list.ItemCount
do
{
    $spListItemCol = $list.GetItems($spQuery)
    $spQuery.ListItemCollectionPosition = $spListItemCol.ListItemCollectionPosition        
       foreach ($listItem in $spListItemCol)
       {
              Write-Host "    Item ID: " $listItem.ID      
              if (!(Test-Path -path $destinationfolder))       
              {           
                     $dest = New-Item $destinationfolder -type directory         
              }
              foreach ($attachment in $listItem.Attachments)   
              {       
                     $file = $web.GetFile($listItem.Attachments.UrlPrefix + $attachment)       
                     $bytes = $file.OpenBinary()               
                     $path = $destinationfolder + "\" + $listItem.ID + "_" + $attachment
                     Write "Saving $path"
                     $fs = new-object System.IO.FileStream($path, "OpenOrCreate")
                     $fs.Write($bytes, 0 , $bytes.Length)   
                     $fs.Close()   
              }
       }
}
while ($spQuery.ListItemCollectionPosition -ne $null)
Stop-SPAssignment $SPAssignment

Friday, June 17, 2016

Export SharePoint List to Excel

Export SharePoint List to Excel
go to ExportToExcel.ascx add below code.
<%@ Assembly Name="$SharePoint.Project.AssemblyFullName$" %>
<%@ Assembly Name="Microsoft.Web.CommandUI, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
<%@ Register Tagprefix="SharePoint" Namespace="Microsoft.SharePoint.WebControls" Assembly="Microsoft.SharePoint, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
<%@ Register Tagprefix="Utilities" Namespace="Microsoft.SharePoint.Utilities" Assembly="Microsoft.SharePoint, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
<%@ Register Tagprefix="asp" Namespace="System.Web.UI" Assembly="System.Web.Extensions, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" %>
<%@ Import Namespace="Microsoft.SharePoint" %>
<%@ Register Tagprefix="WebPartPages" Namespace="Microsoft.SharePoint.WebPartPages" Assembly="Microsoft.SharePoint, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
<%@ Control Language="C#" AutoEventWireup="true" CodeBehind="ExportToExcel.ascx.cs" Inherits="ExportToExcel.ExportToExcel.ExportToExcel" %>
<table>
    <tr id="trExportToExcel" runat="server">
        <td>
            <asp:Button ID="btnExcel" runat="server" Text="Export to Excel" OnClick="btnExcel_Click" />
        </td>
    </tr>
    <tr>
        <td>
            <asp:Label ID="lblError" runat="server"></asp:Label>
        </td>
    </tr>
</table>

go to ExportToExcel.ascx.cs add below code.
using Microsoft.SharePoint;
using System;
using System.ComponentModel;
using System.Data;
using System.IO;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts; 
namespace ExportToExcel.ExportToExcel
{
    [ToolboxItemAttribute(false)]
    public partial class ExportToExcel : WebPart
    {
        public ExportToExcel()
        {
        } 
        protected override void OnInit(EventArgs e)
        {
            base.OnInit(e);
            InitializeControl();
        } 
        protected void Page_Load(object sender, EventArgs e)
        {
        } 
        protected void btnExcel_Click(object sender, EventArgs e)
        {
            try
            {
                trExportToExcel.Visible = true;
                SPWeb oWeb = SPContext.Current.Web;
                SPBasePermissions perms = SPBasePermissions.ViewListItems;
                if (oWeb.DoesUserHavePermissions(SPBasePermissions.EnumeratePermissions))
                {
                    SPList oList = oWeb.Lists["test"];
                    SPUser oCurrentUser = oWeb.CurrentUser;
                    bool hasPermission = oList.DoesUserHavePermissions(oCurrentUser, perms);
                    if (hasPermission)
                    {
                        if (oList != null)
                        {
                            SPQuery qrylegalcases = new SPQuery();
                            qrylegalcases.Query = "<Where><IsNotNull><FieldRef Name='ID'/></IsNotNull></Where>";
                            SPListItemCollection itemslegalcases = oList.GetItems(qrylegalcases);
                            try
                            {
                                DataTable dt = new DataTable();
                                if (itemslegalcases.Count > 0)
                                {
                                    dt = itemslegalcases.GetDataTable();
                                }
                                ExportToExce(dt);
                            }
                            catch (Exception ex)
                            {
                                lblError.Text += " ## " + ex.Message + " # " + ex.StackTrace;
                            }
                        }
                        else
                        {
                            lblError.Text += "List deos not exist.";
                        }
                    }
                    else
                    {
                        lblError.Text += "You don't have permission to list.";
                    }
                }
                else
                {
                    lblError.Text += "You don't have permission to site.";
                }
            }
            catch (Exception ex)
            {
                lblError.Text += " ## " + ex.Message + " # " + ex.StackTrace;
            }
        } 
        private void ExportToExce(DataTable dt)
        {
            GridView GridView1 = new GridView();
            GridView1.AllowPaging = false;
            GridView1.DataSource = dt;
            GridView1.DataBind(); 
            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.Buffer = true;
            HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=ExportToExcel.xls");
            HttpContext.Current.Response.Charset = "";
            HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
            StringWriter sw = new StringWriter();
            HtmlTextWriter hw = new HtmlTextWriter(sw);
            for (int i = 0; i < GridView1.Rows.Count; i++)
            {
                GridView1.Rows[i].Attributes.Add("class", "textmode");
            }
            GridView1.RenderControl(hw);
            string style = @"<style> .textmode { mso-number-format:\@; } </style>";
            HttpContext.Current.Response.Write(style);
            HttpContext.Current.Response.Output.Write(sw.ToString());
            HttpContext.Current.Response.Flush();
            HttpContext.Current.Response.End();
        } 
    }
}
deploy code and check.

Thursday, December 17, 2015

Authenticating .NET Client Object Model CSOM in Office 365 in SharePoint 2013

Authenticating .NET Client Object Model CSOM in Office 365 in SharePoint 2013
--------------------------------------------------------
using Microsoft.SharePoint.Client;
using SP = Microsoft.SharePoint.Client;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Security;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace WebApplication1
{
    public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            const string webUrl = "https://sreekanth2.sharepoint.com/";
            const string USER = "sreekanth@sreekanth2.onmicrosoft.com";
            const string PWD = "password";
            var listInfo = ""; 
            using (ClientContext clientContext = new ClientContext(webUrl))
            {
                SecureString passWord = new SecureString();
                foreach (char c in PWD.ToCharArray())
                {
                    passWord.AppendChar(c);
                }
                clientContext.Credentials = new SharePointOnlineCredentials(USER, passWord);
                Microsoft.SharePoint.Client.List spList = clientContext.Web.Lists.GetByTitle("Temperature");
                clientContext.Load(spList);
                clientContext.ExecuteQuery();
                if (spList != null && spList.ItemCount > 0)
                {
                    Microsoft.SharePoint.Client.CamlQuery camlQuery = new CamlQuery();
                    camlQuery.ViewXml = @"<View><Query><Where><IsNotNull><FieldRef Name='ID' /></IsNotNull></Where><OrderBy><FieldRef Name='ID' /></OrderBy></Query><ViewFields><FieldRef Name='ID' /><FieldRef Name='City' /><FieldRef Name='Month' /><FieldRef Name='Temperature' /></ViewFields></View>";
                    SP.ListItemCollection listItems = spList.GetItems(camlQuery);
                    clientContext.Load(listItems);
                    clientContext.ExecuteQuery();
                    listInfo += "<table border='1'><tr><td>ID</td><td>City</td><td>Month</td><td>Temperature</td></tr>";
                    foreach (SP.ListItem oListItem in listItems)
                    {
                        listInfo += "<tr><td>" + oListItem.Id + "</td><td>" + oListItem["City"] + "</td><td>" + oListItem["Month"] + "</td><td>" + oListItem["Temperature"] + "</td></tr>";
                    }
                    listInfo += "</tr></table>";
                    div1.InnerHtml = "List Items found:<br/>" + listInfo;
                }
            }
        }
    }
}
--------------------------------------------------------
--------------------------------------------------------