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.