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.

No comments:

Post a Comment

Featured Post

Protect an API by using OAuth 2.0 with Azure Active Directory and API Management

Protect an API by using OAuth 2.0 with Azure Active Directory and API Management https://docs.microsoft.com/en-us/azure/api-management/api-...

Popular posts