Search This Blog

Saturday, October 1, 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.