Spread Windows Forms and Sorting

You may wish to sort data so that certain types of data are displayed first. Spread Windows Forms allows you to sort data by clicking on a column header or with code. You can set the spreadsheet to allow the user to automatically sort the data when a column header is clicked. The first time the column header is clicked (selected) the unsorted icon is displayed. The second click displays the sort icon and sorts the column. If the user clicks successively on the same column, then the direction of the sort is reversed. This does not affect the data model, only how the data is displayed. Use the Column object AllowAutoSort property or the SheetView SetColumnAllowAutoSort method to allow the user to perform automatic sorting when the header cell of a column is clicked. This example allows the user to sort the first column by clicking on the header. autosorting Clicking on a text column header sorts the values alphabetically

C#

fpSpread1.Sheets[0].Columns[0].AllowAutoSort = true; fpSpread1.ActiveSheet.SetClip(0, 0, 8, 1, "one\ntwo\nthree\nfour\r\nfive\nsix\nseven\neight");

VB

FpSpread1.Sheets(0).Columns(0).AllowAutoSort = True FpSpread1.ActiveSheet.SetClip(0, 0, 8, 1, "one" + vbCrLf + "two" + vbCrLf + "three" + vbCrLf + "four" + vbCrLf + "five" + vbCrLf + "six" + vbCrLf + "seven" + vbCrLf + "eight")

The following events occur when sorting automatically:

  • AutoSortedColumn
  • AutoSortingColumn

You can use the following methods to sort the data programatically:

  • SortColumns
  • SortRows
  • SortRange
  • AutoSortColumn

There are two ways to sort data in a range. For bound data, use the SortRows and SortColumns methods. For unbound data, use the SortRange method. Use the SortColumns (or SortRows) method to sort the arrangement of 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 the way sorting is performed, use the SortInfo object with these methods. The SortRange method is for unbound data only. This method sorts the data in a range of cells by moving the data around in the data model and moving the cell-level styles along with it. This method is not intended for bound data, as it moves data (not necessarily by entire row or column) and has the effect of moving the data around in the data source. With the sortInfo array of the SortRange method, you can specify multiple criteria for sorting the data. This method gives you the ability to sort (or arrange) data in a smaller subset than entire rows or columns in a sheet. This example sorts a range of columns using the SortInfo class. sortinfo

C#

FarPoint.Win.Spread.SortInfo[] sort = new FarPoint.Win.Spread.SortInfo[1]; sort[0] = new FarPoint.Win.Spread.SortInfo(0, true, System.Collections.Comparer.Default); fpSpread1.ActiveSheet.SetValue(0, 0, "S"); fpSpread1.ActiveSheet.SetValue(0, 1, "E"); fpSpread1.ActiveSheet.SetValue(0, 2, "A"); fpSpread1.ActiveSheet.SetValue(1, 0, "W"); fpSpread1.ActiveSheet.SetValue(1, 1, "G"); fpSpread1.ActiveSheet.SetValue(1, 2, "P"); fpSpread1.ActiveSheet.SetValue(2, 0, "O"); fpSpread1.ActiveSheet.SetValue(2, 1, "L"); fpSpread1.ActiveSheet.SetValue(2, 2, "Q"); fpSpread1.ActiveSheet.SortColumns(0, 2, sort);

VB

Dim sort(1) As FarPoint.Win.Spread.SortInfo sort(0) = New FarPoint.Win.Spread.SortInfo(0, True, System.Collections.Comparer.Default) FpSpread1.ActiveSheet.SetValue(0, 0, "S") FpSpread1.ActiveSheet.SetValue(0, 1, "E") FpSpread1.ActiveSheet.SetValue(0, 2, "A") FpSpread1.ActiveSheet.SetValue(1, 0, "W") FpSpread1.ActiveSheet.SetValue(1, 1, "G") FpSpread1.ActiveSheet.SetValue(1, 2, "P") FpSpread1.ActiveSheet.SetValue(2, 0, "O") FpSpread1.ActiveSheet.SetValue(2, 1, "L") FpSpread1.ActiveSheet.SetValue(2, 2, "Q") FpSpread1.ActiveSheet.SortColumns(0, 2, sort)

You can customize the sorting indicator image that appears in the column header of columns that allow sorting. One way is to override the PaintSortIndicator method in the CellType ColumnHeaderRenderer class and use your own custom indicator. Another way is to use the GetImage and SetImage methods in the SpreadView class. This example uses the SetImage method to create a custom indicator. sortdatabase

C#

//Add sample data string conStr = "Provider=Microsoft.JET.OLEDB.4.0;data source= C:\\Program Files (x86)\\GrapeCity\\Spread Studio 8\\Common\\nwind.mdb"; string sqlStr = "Select CompanyName, ContactName, ContactTitle, Country from Customers"; System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(conStr); DataSet ds = new DataSet(); System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(sqlStr, conn); da.Fill(ds); fpSpread1.ActiveSheet.DataAutoSizeColumns = true; fpSpread1.ActiveSheet.DataMember = "Patients"; fpSpread1.ActiveSheet.DataSource = ds; fpSpread1.ActiveSheet.Columns[0].AllowAutoSort = true; //Set sort icon image FarPoint.Win.Spread.SpreadView wrkbk = fpSpread1.GetRootWorkbook(); wrkbk.SetImage(FarPoint.Win.Spread.SpreadView.SortAscendingImage, Image.FromFile("C:\\Common\\SD.ico")); wrkbk.SetImage(FarPoint.Win.Spread.SpreadView.SortAscendingImageDisabled, Image.FromFile("C:\\Common\\SD1.ico")); wrkbk.SetImage(FarPoint.Win.Spread.SpreadView.SortDescendingImage, Image.FromFile("C:\\Common\\SD2.ico")); wrkbk.SetImage(FarPoint.Win.Spread.SpreadView.SortDescendingImageDisabled, Image.FromFile("C:\\Common\\SD3.ico")); wrkbk.SetImage(FarPoint.Win.Spread.SpreadView.SortUnsortedImage, Image.FromFile("C:\\Common\\SD4.ico")); wrkbk.SetImage(FarPoint.Win.Spread.SpreadView.SortUnsortedImageDisabled, Image.FromFile("C:\\Common\\SD5.ico"));

VB

‘Add sample data Dim conStr As String = "Provider=Microsoft.JET.OLEDB.4.0;data source= C:\Program Files (x86)\GrapeCity\Spread Studio 8\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.ActiveSheet.DataAutoSizeColumns = True FpSpread1.ActiveSheet.DataMember = "Patients" FpSpread1.ActiveSheet.DataSource = ds FpSpread1.ActiveSheet.Columns(0).AllowAutoSort = True ‘Set sort icon image Dim wrkbk As FarPoint.Win.Spread.SpreadView = FpSpread1.GetRootWorkbook() wrkbk.SetImage(FarPoint.Win.Spread.SpreadView.SortAscendingImage, Image.FromFile("C:\Common\SD.ico")) wrkbk.SetImage(FarPoint.Win.Spread.SpreadView.SortAscendingImageDisabled, Image.FromFile("C:\Common\SD1.ico")) wrkbk.SetImage(FarPoint.Win.Spread.SpreadView.SortDescendingImage, Image.FromFile("C:\Common\SD2.ico")) wrkbk.SetImage(FarPoint.Win.Spread.SpreadView.SortDescendingImageDisabled, Image.FromFile("C:\Common\SD3.ico")) wrkbk.SetImage(FarPoint.Win.Spread.SpreadView.SortUnsortedImage, Image.FromFile("C:\Common\SD4.ico")) wrkbk.SetImage(FarPoint.Win.Spread.SpreadView.SortUnsortedImageDisabled, Image.FromFile("C:\Common\SD5.ico"))

This example overrides the PaintSortIndicator method in the CellType ColumnHeaderRenderer class and creates a custom indicator. sortind

C#

public class myColumnHeaderRenderer : FarPoint.Win.Spread.CellType.ColumnHeaderRenderer {override public void PaintSortIndicator(Graphics g, Rectangle r, FarPoint.Win.Spread.Appearance appearance, float zoomFactor) { g.FillRectangle(new SolidBrush(Color.Red), r); } override public void PaintFilterIndicator(Graphics g, Rectangle r, FarPoint.Win.Spread.Appearance appearance, float zoomFactor) { g.FillRectangle(new SolidBrush(Color.Blue), r); } }private void SpreadSorting_Load(object sender, EventArgs e) { fpSpread1.ActiveSheet.ColumnHeader.DefaultStyle.Renderer = new myColumnHeaderRenderer(); fpSpread1.Sheets[0].Columns[0].AllowAutoSort =true; fpSpread1.Sheets[0].Columns[0].AllowAutoFilter =true;Random r = new Random(); int row, col = 0; for (row = 0; row <= 7; row++) for (col = 0; col<= 7; col++) { fpSpread1.ActiveSheet.SetValue(row, col, r.Next().ToString()); } }

VB

Public Class myColumnHeaderRenderer Inherits FarPoint.Win.Spread.CellType.ColumnHeaderRendererPublic Overrides Sub PaintSortIndicator(ByVal g As Graphics, ByVal r As Rectangle, ByVal appearance As FarPoint.Win.Spread.Appearance, ByVal zoomFactor As Single) g.FillRectangle(New SolidBrush(Color.Red), r) End Sub 'PaintSortIndicatorPublic Overrides Sub PaintFilterIndicator(ByVal g As Graphics, ByVal r As Rectangle, ByVal appearance As FarPoint.Win.Spread.Appearance, ByVal zoomFactor As Single) g.FillRectangle(New SolidBrush(Color.Blue), r) End Sub 'PaintFilterIndicator End Class 'myColumnHeaderRenderer Private Sub SpreadSorting_Load(sender As Object, e As EventArgs) Handles MyBase.Load FpSpread1.ActiveSheet.ColumnHeader.DefaultStyle.Renderer = New myColumnHeaderRenderer FpSpread1.Sheets(0).Columns(0).AllowAutoSort = True FpSpread1.Sheets(0).Columns(0).AllowAutoFilter = True Dim r As New Random Dim row, col As Integer For row = 0 To 7 For col = 0 To 7 FpSpread1.ActiveSheet.SetValue(row, col, r.Next.ToString()) Next col Next row End Sub

MESCIUS inc.

comments powered by Disqus