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.

Page.ClientScript.RegisterStartupScript (C#) : RegisterStartupScript « Page Lifecycle « ASP.NET Tutorial

<%@ Page Language="C#" %>

<script runat="server">
    protected void Page_Load(object sender, EventArgs e)
    {
       string myScript = @"alert(document.forms[0]['TextBox1'].value);";
       Page.ClientScript.RegisterStartupScript(this.GetType(), 
          "MyScript", myScript, true);
    }
</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:TextBox ID="TextBox1" runat="server" Text="Hello ASP.NET"></asp:TextBox>
    </div>
    </form>
</body>
</html>

Download Particular Excel File From Library on Local server Location

Add-PSSnapin Microsoft.SharePoint.PowerShell -erroraction SilentlyContinue
$fromsite = "http://ngo.com"

$fromfile = "NGODocument/NGO.xlsx"
$tofile   = "C:\NGODoc\NGODocument.xlsx"

$web = Get-SPWeb $fromsite
$file = $web.GetFile($fromfile)
$filebytes = $file.OpenBinary()

$filestream = New-Object System.IO.FileStream($tofile, "Create")
$binarywriter = New-Object System.IO.BinaryWriter($filestream)
$binarywriter.write($filebytes)
$binarywriter.Close()

PowerShell Script to download all files and folders from a SharePoint Library along with its Structure

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

# Function to Download All Files from a SharePoint Library

Function DownloadFiles($SPFolderURL, $LocalFolderPath)

{

        #Get the Source SharePoint Folder

        $SPFolder = $web.GetFolder($SPFolderURL)

        $LocalFolderPath = Join-Path $LocalFolderPath $SPFolder.Name

        #Ensure the destination local folder exists!

        if (!(Test-Path -path $LocalFolderPath))

        {  

             #If it doesn't exists, Create

             $LocalFolder = New-Item $LocalFolderPath -type directory

        }

        #Loop through each file in the folder and download it to Destination

        foreach ($File in $SPFolder.Files)

        {

            #Download the file

            $Data = $File.OpenBinary()

        $FilePath= Join-Path $LocalFolderPath $File.Name

            [System.IO.File]::WriteAllBytes($FilePath, $data)

        }

        #Process the Sub Folders & Recursively call the function

        foreach ($SubFolder in $SPFolder.SubFolders)

        {

           if($SubFolder.Name -ne "Forms") #Leave "Forms" Folder

             {

                  #Call the function Recursively

                  DownloadFiles $SubFolder $LocalFolderPath

             }

        }

    }

#Get the Source Web

$Web = Get-SPWeb "http://ngo:37276/ngo"

#Get the Source SharePoint Library's Root Folder

$SourceLibrary =  $Web.Lists["NGODocuments"].RootFolder

#Local Folder, where the Files to be downloaded

$DestinationPath = "C:\Test"

#Call the Download Files Function

DownloadFiles $SourceLibrary $DestinationPath

SharePoint 2013 Dynamically Adding Dropdown Using REST API and jQuery

REST Services-High Level Overview:

Let's start out with our basic get commands in REST. The following is a list of the basic commands used to get List Items from a SharePoint List using the SharePoint 2013 REST Services.

Imagine

In my example, I'm accessing a Custom list (of countries) and output the result binding it to a dynamic dropdown. I have sorted by a column in ascending only. Using SharePoint's REST API lets us add these filters to our request. The results are given to us as a JSON object that we can then loop through and insert into a dropdown runtime. I also used a modular pattern to structure my code. We can generate our REST request. _spPageContextInfo is a SharePoint object that gives us useful information about the page and site we're on, including the base URL of our site.

After successfully getting our list information, we just need to loop through our data, put it in a dropdown and then inserted into our predefined container element. jQuery helps make this an easy process.

Let's proceed.

Step 1: Navigate to your SharePoint 2013 site.

Step 2: From this page select the Site Actions | Edit Page.

Edit the page, go to the "Insert" tab in the Ribbon and click the "Web Part" option. In the "Web Parts" picker area, go to the "Media and Content" category, select the "Script Editor" Web Part and press the "Add button".

Step 3: Once the Web Part is inserted into the page, you will see an "EDIT SNIPPET" link; click it. You can insert the HTML and/or JavaScript as in the following:

1. <script src="http://ajax.aspnetcdn.com/ajax/jQuery/jquery-1.10.1.min.js"></script>  
2. <script>  
3.   
4.     $(document).ready(function () {  
5.         countriesDrpDownBind();  
6.       });  
7.     function countriesDrpDownBind() {  
8.         var listName = "countries";  
9.         var url = _spPageContextInfo.webAbsoluteUrl;  
10.   
11.         getListItems(listName, url, function (data) {  
12.             var items = data.d.results;  
13.              
14.             var inputElement = '<select id="drpcountries"> <option  value="">Select</option>';  
15.                // Add all the new items  
16.             for (var i = 0; i < items.length; i++) {  
17.                  var itemId = items[i].Title,  
18.                    itemVal = items[i].Title;  
19.                  inputElement += '<option value="' + itemId + '"selected>' + itemId + '</option>';  
20.                 
21.                }  
22.                 inputElement += '</select>';  
23.                 $('#divisiondrp').append(inputElement);  
24.   
25.               $("#drpcountries").each(function () {  
26.                 $('option', this).each(function () {  
27.   
28.                     if ($(this).text() == 'Select') {  
29.                         $(this).attr('selected', 'selected')  
30.                     };  
31.                 });  
32.             });  
33.                // assign the change event to provide an alert of the selected option value  
34.               $('#drpcountries').on('change', function () {  
35.               alert($(this).val());  
36.                   });  
37.              
38.           }, function (data) {  
39.             alert("Ooops, an error occured. Please try again");  
40.         });  
41.     }  
42.     // READ operation  
43.     // listName: The name of the list you want to get items from  
44.     // siteurl: The url of the site that the list is in.  
45.     // success: The function to execute if the call is sucesfull  
46.     // failure: The function to execute if the call fails  
47.     function getListItems(listName, siteurl, success, failure) {  
48.         $.ajax({  
49.             url: siteurl + "/_api/web/lists/getbytitle('" + listName + "')/items?$orderby=Title asc",  
50.             method: "GET",  
51.             headers: { "Accept": "application/json; odata=verbose" },  
52.             success: function (data) {  
53.                 success(data);  
54.             },  
55.             error: function (data) {  
56.                 failure(data);  
57.             }  
58.         });  
59.     }  
60.   
61. </script>  
62. Division  

<div id="divisiondrp"></div>

Finally the result looks as in the following: