SharePoint Journey: Creation of SPGridView Webpart with Sorting, Filtering and Pagination OOTB

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:

2. Screen when filter:

3. Item per page:

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

1. CSS in user control

.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

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;
}
}