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.
