Home > Deveploment and Programming, SharePoint Development > Creation of SPGridView Webpart with Sorting, Filtering and Pagination OOTB

Creation of SPGridView Webpart with Sorting, Filtering and Pagination OOTB

Hi,

This post will simply explain how to use SPGridView in SharePoint 2010 VisualWebpart with sorting, filtering and paging functionality without writing much code, mostly using OOTB features,

This includes below feature

  • SPGridView Webpart
  • Pagination
  • Sorting
  • Filtering
  • Export to Ecel

How it looks:

1. Screen when page load:

Screen when page load

2. Screen when filter:

Screen when filter

3. Item per page:

Item per page

See the below code how to implement the above screen with ootb

1. CSS in user control

<style type=”text/css”>
.pagination
{
font-size: 80%;
}

.pagination a
{
text-decoration: none;
color: #15B;
}

.pagination a, .pagination span
{
font-family: Sans-Serif;
font-size: 11px;
border-bottom: #ccc 1px solid;
border-left: #ccc 1px solid;
padding-bottom: 3px;
margin: 1px;
padding-left: 4px;
padding-right: 4px;
display: inline-block;
border-top: #ccc 1px solid;
font-weight: bold;
border-right: #ccc 1px solid;
padding-top: 3px;
-moz-border-radius: 3px;
-webkit-border-radius: 3px;
}
.pagination .current
{
background: #26B;
color: #fff;
border: solid 1px #AAE;
}
.lblWTHeading
{
font-family: Arial;
font-size: 18px;
font-weight: bold;
color: White;
}
.textAlignLeft
{
text-align: left;
}
</style>

2. SPGridView  and ObjectDataSource in user control

<table>
<tr>
<td align=”right” style=”border: 1px ridge #C0C0C0″>
<table cellpadding=”3″ cellspacing=”3″>
<tr>
<td>
<asp:Label ID=”Label2″ runat=”server” Text=”Export To:” Font-Size=”13px” Font-Names=”Arial”></asp:Label>
</td>
<td>
<asp:ImageButton ID=”imgExcel” runat=”server” ImageUrl=”~/_layouts/images/ExportToExcel.png”
OnClick=”imgExcel_Click” ToolTip=”Export to Excel” Height=”22px” Width=”22px” />
</td>
<td>
<asp:Label ID=”Label1″ runat=”server” Text=”Item per page:” Font-Size=”13px” Font-Names=”Arial”></asp:Label>
</td>
<td>
<asp:DropDownList ID=”DropDownList1″ runat=”server” AutoPostBack=”True” OnSelectedIndexChanged=”DropDownList1_SelectedIndexChanged”>
<asp:ListItem>5</asp:ListItem>
<asp:ListItem>10</asp:ListItem>
<asp:ListItem>20</asp:ListItem>
<asp:ListItem>50</asp:ListItem>
</asp:DropDownList>
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td style=”border: 1px ridge #C0C0C0″>
<SharePoint:SPGridView ID=”oGrid” runat=”server” AutoGenerateColumns=”false” AllowSorting=”True”
DataSourceID=”myDataSource” FilterDataFields=”Title,Status” AllowFiltering=”true”
AllowPaging=”True” FilteredDataSourcePropertyName=”FilterExpression” FilteredDataSourcePropertyFormat=”{1} like ‘{0}'”
Font-Names=”Arial” Font-Size=”13px”>
<Columns>
<SharePoint:SPBoundField DataField=”Title” HeaderText=”Title” SortExpression=”Title”>
<ControlStyle Width=”220px” />
</SharePoint:SPBoundField>
<SharePoint:SPBoundField DataField=”Status” HeaderText=”Status” SortExpression=”Status”>
<ControlStyle Width=”100px” />
</SharePoint:SPBoundField>
<asp:TemplateField HeaderText=”Get Candidates”>
<ItemTemplate>
<asp:LinkButton ID=”lnkBtnGetCandidates” runat=”server” Text=”Get Candidates” CausesValidation=”False”
CommandName=”Select” CommandArgument='<%#Eval(“Description”)%>’></asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
</Columns>
<FooterStyle BackColor=”#5D7B9D” Font-Bold=”True” ForeColor=”White” />
<PagerStyle CssClass=”pagination” HorizontalAlign=”Center” VerticalAlign=”Middle”
Font-Size=”14pt” Wrap=”True” BackColor=”White” />
<RowStyle HorizontalAlign=”Left” />
</SharePoint:SPGridView>
<asp:ObjectDataSource ID=”myDataSource” runat=”server” TypeName=”SPGridview.VisualWebPart1.DataTableWrapper, $SharePoint.Project.AssemblyFullName$”
SelectMethod=”GetTable”></asp:ObjectDataSource>
</td>
</tr>
</table>

3. Code in Visual web part

private DataTable sourceDataTable;
private DataTableWrapper myDataTable;
private string[] _ssep = { “AND” };
private char[] _sep = { ‘,’ };

protected override void CreateChildControls()
{
string js = @”_spSuppressFormOnSubmitWrapper = true;”;
this.Page.ClientScript.RegisterStartupScript(this.GetType(), “js”, js, true);

myDataSource.ObjectCreating += new ObjectDataSourceObjectEventHandler(ds_ObjectCreating);
#region “Pagination”

oGrid.PageSize = Convert.ToInt16(DropDownList1.SelectedValue);
oGrid.RowDataBound += new GridViewRowEventHandler(oGrid_RowDataBound);

//Default Pagination
oGrid.PageIndexChanging += new GridViewPageEventHandler(oGrid_PageIndexChanging);
oGrid.PagerTemplate = null;
#endregion
base.CreateChildControls();

}

void oGrid_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
oGrid.PageIndex = e.NewPageIndex;
oGrid.DataBind();
}

private void oGrid_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (sender == null || e.Row.RowType != DataControlRowType.Header)
{ return; }

SPGridView grid = sender as SPGridView;

// Show icon on filtered and sorted columns
for (int i = 0; i < grid.Columns.Count; i++)
{
DataControlField field = grid.Columns[i];

if (((Microsoft.SharePoint.WebControls.SPGridView)(sender)).FilterFieldName.Contains(field.SortExpression))
{
try
{
PlaceHolder panel = HeaderImages(field, “/_layouts/images/filter.gif”);
e.Row.Cells[i].Controls[0].Controls.Add(panel);
break;
}
catch (Exception)
{ }
}

}
}

void ds_ObjectCreating(object sender, ObjectDataSourceEventArgs e)
{

myDataTable = new DataTableWrapper(sourceDataTable);
e.ObjectInstance = myDataTable;

}

private PlaceHolder HeaderImages(DataControlField field, string imageUrl)
{
Image filterIcon = new Image();
filterIcon.ImageUrl = imageUrl;
filterIcon.Style[HtmlTextWriterStyle.MarginLeft] = “2px”;

Literal headerText = new Literal();
headerText.Text = field.HeaderText;

PlaceHolder panel = new PlaceHolder();
panel.Controls.Add(headerText);

//add the sort icon if needed
if (FilterExpression.Contains(field.SortExpression) &&
SortExpression.Contains(field.SortExpression))
{
string url = sortImage(field);
Image sortIcon = new Image();
sortIcon.ImageUrl = url;
sortIcon.Style[HtmlTextWriterStyle.MarginLeft] = “1px”;
panel.Controls.Add(sortIcon);
//change the left margin to 1
filterIcon.Style[HtmlTextWriterStyle.MarginLeft] = “1px”;
}

panel.Controls.Add(filterIcon);
return panel;
}

private string sortImage(DataControlField field)
{
string url = string.Empty;
string[] fullSortExp = SortExpression.Split(_sep);
List<string> fullSortExpression = new List<string>();
fullSortExpression.AddRange(fullSortExp);

//does the sort expression already exist?
int index = fullSortExpression.FindIndex(s => s.Contains(field.SortExpression));
if (index >= 0)
{
string s = fullSortExpression[index];
if (s.Contains(“ASC”))
{ url = “_layouts/images/sortup.gif”; }
else
{ url = “_layouts/images/sortdown.gif”; }
}
return url;
}

protected override void LoadViewState(object savedState)
{
base.LoadViewState(savedState);

if (Context.Request.Form[“__EVENTARGUMENT”] != null &&
Context.Request.Form[“__EVENTARGUMENT”].Contains(“__ClearFilter__”))
{
// Clear FilterExpression
ViewState.Remove(“FilterExpression”);
}
}

string FilterExpression
{
get
{
if (ViewState[“FilterExpression”] == null)
{ ViewState[“FilterExpression”] = “”; }

return (string)ViewState[“FilterExpression”];
}
set
{
string thisFilterExpression = “(” + value.ToString() + “)”;
List<string> fullFilterExpression = new List<string>();

if (ViewState[“FilterExpression”] != null)
{
string[] fullFilterExp = ViewState[“FilterExpression”].ToString().Split(_ssep, StringSplitOptions.RemoveEmptyEntries);
fullFilterExpression.AddRange(fullFilterExp);

//if the filter is gone expression already exist?
int index = fullFilterExpression.FindIndex(s => s.Contains(thisFilterExpression));
if (index == -1)
{ fullFilterExpression.Add(thisFilterExpression); }
}
else
{
fullFilterExpression.Add(thisFilterExpression);
}
//loop through the list<T> and serialize to string
string filterExp = string.Empty;
fullFilterExpression.ForEach(s => filterExp += s + ” AND “);
filterExp = filterExp.Remove(filterExp.LastIndexOf(” AND “));
if (!filterExp.EndsWith(“))”) && filterExp.Contains(“AND”))
{ filterExp = “(” + filterExp + “)”; }
ViewState[“FilterExpression”] = filterExp;
}
}

string SortExpression
{
get
{
if (ViewState[“SortExpression”] == null)
{ ViewState[“SortExpression”] = “”; }

return (string)ViewState[“SortExpression”];
}
set
{
string[] thisSE = value.ToString().Split(‘ ‘);
string thisSortExpression = thisSE[0];
List<string> fullSortExpression = new List<string>();

if (ViewState[“SortExpression”] != null)
{
string[] fullSortExp = ViewState[“SortExpression”].ToString().Split(_sep);
fullSortExpression.AddRange(fullSortExp);

//does the sort expression already exist?
int index = fullSortExpression.FindIndex(s => s.Contains(thisSortExpression));
if (index >= 0)
{
string s = string.Empty;
if (value.ToString().Contains(“DESC”))
{ s = value.ToString(); }
else
{
s = fullSortExpression[index];
if (s.Contains(“ASC”))
{ s = s.Replace(“ASC”, “DESC”); }
else
{ s = s.Replace(“DESC”, “ASC”); }
}
//reset the sort direction
fullSortExpression[index] = s;
}
else
{
if (value.ToString().Contains(“DESC”))
{ fullSortExpression.Add(value.ToString()); }
else
{ fullSortExpression.Add(thisSortExpression + ” ASC”); }
}
}
else
{
if (value.ToString().Contains(“DESC”))
{ fullSortExpression.Add(value.ToString()); }
else
{ fullSortExpression.Add(thisSortExpression + ” ASC”); }
}
//loop through the list<T> and serialize to string
string sortExp = string.Empty;
fullSortExpression.ForEach(s => sortExp += s);
sortExp = sortExp.Replace(” ASC”, ” ASC,”);
sortExp = sortExp.Replace(” DESC”, ” DESC,”);
ViewState[“SortExpression”] = sortExp.Remove(sortExp.LastIndexOf(‘,’));
}
}

private void ExportToExcel(DataTable dt)
{
Table table = ConvertDatatabletoTable(dt);
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);

table.RenderControl(htw);

HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader(“content-disposition”, string.Format(“attachment; filename={0}”, “CountryState.xls”));
HttpContext.Current.Response.ContentType = “application/ms-excel”;
HttpContext.Current.Response.Cache.SetCacheability(HttpCacheability.NoCache);
HttpContext.Current.Response.Write(sw.ToString());
HttpContext.Current.Response.End();
}

protected Table ConvertDatatabletoTable(DataTable dtSource)
{
Table tbl = new Table();
tbl.CellPadding = 0;
tbl.CellSpacing = 0;
bool AddedColumnName = false;

foreach (DataRow dtRow in dtSource.Rows)
{
TableRow row = new TableRow();
foreach (DataColumn col in dtSource.Columns)
{
if (AddedColumnName == false)
{
TableCell cell = new TableCell();
cell.Text = col.ColumnName;
row.Cells.Add(cell);
}
else
{
TableCell cell = new TableCell();
cell.Text = dtRow[col].ToString();
row.Cells.Add(cell);
}
}
tbl.Rows.Add(row);
AddedColumnName = true;
}
return tbl;
}

protected void imgExcel_Click(object sender, ImageClickEventArgs e)
{

DataTable dt = (DataTable)HttpContext.Current.Session[“Hello”];
ExportToExcel(dt);

}

protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
{
oGrid.PageSize = Convert.ToInt16(DropDownList1.SelectedValue);
}

public class DataTableWrapper
{
private DataTable _dt = new DataTable();

public DataTableWrapper(DataTable dt)
{
_dt = dt;
}

public DataTable GetTable()
{
return _dt;
}
}

Reference:

1. http://www.codeproject.com/Articles/35536/Creation-of-a-SPGridview-Webpart-having-Pagination

2. http://sharethefrustration.blogspot.co.uk/2010/02/spgridview-webpart-with-multiple-filter.html

Download code:

1. http://sdrv.ms/15bXI12

2. https://skydrive.live.com/redir?resid=CDA16F1269A2B92C!809

Advertisements
  1. No comments yet.
  1. 15/08/2014 at 1:25 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: