Spread ASP.NET and Sorting

  • 0 Comments

Spread ASP.NET supports several different methods of sorting. Sorting occurs on the server side. You can sort the data displayed in the sheet either by column or by row. Sorting data makes it easier to find information. Spread has the following sort methods:

  • Sort
  • SortColumns
  • SortRows

The order of the sort can be in ascending order (A to Z, 0 to 9) or descending order (Z to A, 9 to 0). You can customize the comparison method and you can select which values to use as a comparison key when sorting. Use the SortInfo object in the Sort method to customize sorting. You can sort entire rows or columns in a sheet and you can specify which column or row to use as a key for sorting. Use the SortColumns (or SortRows) method to sort the columns (or rows) in a sheet using one or more rows (or columns) as the key. This does not affect the data model, only how the data is displayed. Several overloads provide different ways to sort the columns (or rows). To further customize sorting, use the SortInfo object with these methods. You can double-click the column header to sort if you set the AllowSort property to true. The sort indicator (an arrow by default) is displayed in the column header after sorting. The cell type does not matter for sorting. The sorting is based on the data type of the values in the cells. If you sort cells with the DateTime type, then it sorts those cells by date, and if you sort cells with the string type, it sorts those cells alphabetically. Sorting executed by clicking column headers sorts only the displayed data and does not affect the order of actual data in the data model. So you can reset the sorted data being displayed to the order of actual data using either the ResetViewRowIndexes method or the ResetViewColumnIndexes method. You can use the GetModelRowFromViewRow and GetModelColumnFromViewColumn methods to get the model coordinates after sorting. The following example sets the AllowSort property. Double-click the column header to sort. SpreadASPAllowSort AllowSort C# 

// Add code to Page Load event  
if (this.IsPostBack) return;  
 //Add sample data  
string conStr = "Provider=Microsoft.JET.OLEDB.4.0;data source= C:\\\Program Files (x86)\\\GrapeCity\\\Spread Studio 9\\\Common\\\nwind.mdb";  
string sqlStr = "Select CompanyName, ContactName, ContactTitle, Country from Customers";  
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(conStr);  
System.Data.DataSet ds = new System.Data.DataSet();  
System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(sqlStr, conn);  
da.Fill(ds);  
FpSpread1.Sheets[0].DataMember = "Patients";  
FpSpread1.Sheets[0].DataSource = ds;  
FpSpread1.Sheets[0].DefaultStyle.Font.Name = "Calibri";  
FpSpread1.Sheets[0].DefaultStyle.Font.Size = 11;  
FpSpread1.Sheets[0].PageSize = 100;  
FpSpread1.Sheets[0].AllowSort = true;  
FpSpread1.Sheets[0].SetColumnWidth(0, 150);  
FpSpread1.Sheets[0].SetColumnWidth(1, 150);  
FpSpread1.Sheets[0].SetColumnWidth(2, 150);

VB 

‘ Add code to Page Load event  
If (Me.IsPostBack) Then Return  
Dim conStr As String = "Provider=Microsoft.JET.OLEDB.4.0;data source= C:\\Program Files (x86)\\GrapeCity\\Spread Studio 9\\Common\\nwind.mdb"  
Dim sqlStr As String = "Select CompanyName, ContactName, ContactTitle, Country from Customers"  
Dim conn As New System.Data.OleDb.OleDbConnection(conStr)  
Dim ds As DataSet = New DataSet()  
Dim da As New System.Data.OleDb.OleDbDataAdapter(sqlStr, conn)  
da.Fill(ds)  
FpSpread1.Sheets(0).DataMember = "Patients"  
FpSpread1.Sheets(0).DataSource = ds  
FpSpread1.Sheets(0).DefaultStyle.Font.Name = "Calibri"  
FpSpread1.Sheets(0).DefaultStyle.Font.Size = 11  
FpSpread1.Sheets(0).PageSize = 100  
FpSpread1.Sheets(0).AllowSort = True  
FpSpread1.Sheets(0).SetColumnWidth(0, 150)  
FpSpread1.Sheets(0).SetColumnWidth(1, 150)  
FpSpread1.Sheets(0).SetColumnWidth(2, 150)

This example creates a Column object for the sheet, sets text in the first three rows of the column, and sorts the column data using a button click event. The sort indicator for the column is displayed after the column is sorted. C# 

private void SpreadPage_Load(object sender,System.EventArgs e)  
{  
if (this.IsPostBack) return;  
FarPoint.Web.Spread.Column mycol;  
mycol = FpSpread1.ActiveSheetView.Columns[1];  
FpSpread1.ActiveSheetView.SetValue(0, 1, "Alignment");  
FpSpread1.ActiveSheetView.SetValue(1, 1, "CarbAdjust");  
FpSpread1.ActiveSheetView.SetValue(2, 1, "Brakes");  
mycol.SortIndicator = FarPoint.Web.Spread.Model.SortIndicator.Descending;  
FpSpread1.ActiveSheetView.SortRows(1, false, true);  
FpSpread1.Sheets[0].AllowSort = true;  
}  

protected void Button1_Click(object sender, EventArgs e)  
{  
FpSpread1.ActiveSheetView.SortRows(1, true, true);  
}

VB 

Protected Sub SpreadPage_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load  
        If (Me.IsPostBack) Then Return  
        Dim mycol As FarPoint.Web.Spread.Column  
        mycol = FpSpread1.ActiveSheetView.Columns(1)  
        FpSpread1.ActiveSheetView.SetValue(0, 1, "Alignment")  
        FpSpread1.ActiveSheetView.SetValue(1, 1, "CarbAdjust")  
        FpSpread1.ActiveSheetView.SetValue(2, 1, "Brakes")  
        mycol.SortIndicator = FarPoint.Web.Spread.Model.SortIndicator.Descending  
        FpSpread1.ActiveSheetView.SortRows(1, False, True)  
        FpSpread1.Sheets(0).AllowSort = True  
    End Sub  

    Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click  
        FpSpread1.Sheets(0).SetColumnSortIndicator(1, FarPoint.Web.Spread.Model.SortIndicator.Ascending)  
    End Sub

This example sorts a range of columns and specifies a sort key. Use this method if you want to specify more than one row by which to sort (have more than one key for sorting). The SortInfo array can contain multiple keys (multiple rows) and the first specified row is the primary key. C# 

FpSpread1.Sheets[0].Cells[0, 0].Text = "Sales Representative";  
FpSpread1.Sheets[0].Cells[1, 0].Text = "Owner";  
FpSpread1.Sheets[0].Cells[2, 0].Text = "Order Administrator";  
FpSpread1.Sheets[0].Cells[0, 1].Text = "UK";  
FpSpread1.Sheets[0].Cells[1, 1].Text = "Sweden";  
FpSpread1.Sheets[0].Cells[2, 1].Text = "Germany";  
FpSpread1.Sheets[0].Cells[0, 2].Text = "Antonio";  
FpSpread1.Sheets[0].Cells[1, 2].Text = "Hardy";  
FpSpread1.Sheets[0].Cells[2, 2].Text = "Elizabeth";  
FpSpread1.Sheets[0].Cells[0, 3].Text = "Books";  
FpSpread1.Sheets[0].Cells[1, 3].Text = "Candles";  
FpSpread1.Sheets[0].Cells[2, 3].Text = "Furniture";  

FarPoint.Web.Spread.SheetView sv;  
FarPoint.Web.Spread.SortInfo[] s = new FarPoint.Web.Spread.SortInfo[1];  
s[0] = new FarPoint.Web.Spread.SortInfo(0, false);  
sv = FpSpread1.ActiveSheetView;  
sv.SortColumns(0, 3, s);

VB 

FpSpread1.Sheets(0).Cells(0, 0).Text = "Sales Representative"  
FpSpread1.Sheets(0).Cells(1, 0).Text = "Owner"  
FpSpread1.Sheets(0).Cells(2, 0).Text = "Order Administrator"  
FpSpread1.Sheets(0).Cells(0, 1).Text = "UK"  
FpSpread1.Sheets(0).Cells(1, 1).Text = "Sweden"  
FpSpread1.Sheets(0).Cells(2, 1).Text = "Germany"  
FpSpread1.Sheets(0).Cells(0, 2).Text = "Antonio"  
FpSpread1.Sheets(0).Cells(1, 2).Text = "Hardy"  
FpSpread1.Sheets(0).Cells(2, 2).Text = "Elizabeth"  
FpSpread1.Sheets(0).Cells(0, 3).Text = "Books"  
FpSpread1.Sheets(0).Cells(1, 3).Text = "Candles"  
FpSpread1.Sheets(0).Cells(2, 3).Text = "Furniture"  

Dim sv As FarPoint.Web.Spread.SheetView  
Dim s(1) As FarPoint.Web.Spread.SortInfo  
s(0) = New FarPoint.Web.Spread.SortInfo(0, False)  
sv = FpSpread1.ActiveSheetView  
sv.SortColumns(0, 3, s)

You can also sort on the client-side with the SortColumn method. This method causes a postback to occur. The AllowSort property must be set to True for the sheet. This example sorts the column. JavaScript 

<script lang ="javascript">  
        function Test(event) {  
            FpSpread1.SortColumn(1);  
        }  
    </script>

C# 

protected void SpreadPage_Load(object sender, EventArgs e)  
        {  
            if (this.IsPostBack) return;  

        FpSpread1.Sheets[0].Cells[0, 0].Text = "Sales Representative";  
        FpSpread1.Sheets[0].Cells[1, 0].Text = "Owner";  
        FpSpread1.Sheets[0].Cells[2, 0].Text = "Order Administrator";  
        FpSpread1.Sheets[0].Cells[0, 1].Text = "UK";  
        FpSpread1.Sheets(0).Cells(1, 1).Text = "Germany"  
        FpSpread1.Sheets(0).Cells(2, 1).Text = "Sweden"  
        FpSpread1.Sheets[0].Cells[0, 2].Text = "Antonio";  
        FpSpread1.Sheets[0].Cells[1, 2].Text = "Hardy";  
        FpSpread1.Sheets[0].Cells[2, 2].Text = "Elizabeth";  
        FpSpread1.Sheets[0].Cells[0, 3].Text = "Books";  
        FpSpread1.Sheets[0].Cells[1, 3].Text = "Candles";  
        FpSpread1.Sheets[0].Cells[2, 3].Text = "Furniture";  

        FpSpread1.Sheets[0].AllowSort = true;  
        FpSpread1.Attributes.Add("onActiveCellChanged", "Test(event)");  
        }

VB 

Protected Sub SpreadPage_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load  
        If (Me.IsPostBack) Then Return  
        FpSpread1.Sheets(0).Cells(0, 0).Text = "Sales Representative"  
        FpSpread1.Sheets(0).Cells(1, 0).Text = "Owner"  
        FpSpread1.Sheets(0).Cells(2, 0).Text = "Order Administrator"  
        FpSpread1.Sheets(0).Cells(0, 1).Text = "UK"  
        FpSpread1.Sheets(0).Cells(1, 1).Text = "Germany"  
        FpSpread1.Sheets(0).Cells(2, 1).Text = "Sweden"  
        FpSpread1.Sheets(0).Cells(0, 2).Text = "Antonio"  
        FpSpread1.Sheets(0).Cells(1, 2).Text = "Hardy"  
        FpSpread1.Sheets(0).Cells(2, 2).Text = "Elizabeth"  
        FpSpread1.Sheets(0).Cells(0, 3).Text = "Books"  
        FpSpread1.Sheets(0).Cells(1, 3).Text = "Candles"  
        FpSpread1.Sheets(0).Cells(2, 3).Text = "Furniture"  

        FpSpread1.Sheets(0).AllowSort = True  
        FpSpread1.Attributes.Add("onActiveCellChanged", "Test(event)")  
    End Sub

The SortColumnCommand event occurs when AllowSort is True. This example uses the SortColumnCommand event and returns the sorted column. C# 

FarPoint.Web.Spread.Column col;  
col = FpSpread1.ActiveSheetView.Columns[1];  
FpSpread1.ActiveSheetView.Cells[0, 1].Text = "Alignment";  
FpSpread1.ActiveSheetView.Cells[1, 1].Text = "CarbAdjust";  
FpSpread1.ActiveSheetView.Cells[2, 1].Text = "Brakes";              
FpSpread1.ActiveSheetView.AllowSort = true;  
col.SortIndicator = FarPoint.Web.Spread.Model.SortIndicator.Descending;  

protected void FpSpread1_SortColumnCommand(object sender, FarPoint.Web.Spread.SpreadCommandEventArgs e)  
        {  
            FpSpread1.ActiveSheetView.Cells[0, 0].Text = "The sorted column was column #" + e.CommandArgument;  
        }

VB 

Dim col As FarPoint.Web.Spread.Column  
col = FpSpread1.ActiveSheetView.Columns(1)  
FpSpread1.ActiveSheetView.Cells(0, 1).Text = "Alignment"  
FpSpread1.ActiveSheetView.Cells(1, 1).Text = "CarbAdjust"  
FpSpread1.ActiveSheetView.Cells(2, 1).Text = "Brakes"  
col.SortIndicator = FarPoint.Web.Spread.Model.SortIndicator.Descending  
FpSpread1.ActiveSheetView.AllowSort = True  

Protected Sub FpSpread1_SortColumnCommand(sender As Object, e As FarPoint.Web.Spread.SpreadCommandEventArgs) Handles   
FpSpread1.SortColumnCommand  
        FpSpread1.ActiveSheetView.Cells(0, 0).Text = "The sorted column was column #" & e.CommandArgument  
    End Sub

MESCIUS inc.