Skip to main content Skip to footer

Spread Windows Forms and Filtering

You can use filtering to display data that meets specific criteria. This makes it easier to find and view a subset of the data. Spread Windows Forms supports enhanced row filtering. You can use the default filter menu or display a filter bar. The default filter that is displayed depends on the data in the column. The filter can be a number, text, date, or color filter. The following example creates an enhanced filter in the first three columns. The type of filter is based on the type of data in the column. numberfilter

C#

fpSpread1.ActiveSheet.RowCount = 5; fpSpread1.ActiveSheet.ColumnCount = 5; FarPoint.Win.Spread.CellType.ColorPickerCellType cp = new FarPoint.Win.Spread.CellType.ColorPickerCellType(); cp.AllowFullOpen = true; cp.AnyColor = false; cp.CustomColors = new int[] { 255, 190, 50 }; cp.FullOpen = true; cp.Style = FarPoint.Win.Spread.CellType.ColorPickerStyle.Boxed; FarPoint.Win.Spread.CellType.ColorPickerCellType c = new FarPoint.Win.Spread.CellType.ColorPickerCellType(cp); fpSpread1.ActiveSheet.Columns[0].CellType = c; fpSpread1.ActiveSheet.Cells[0, 0].Value = System.Drawing.Color.Aqua; fpSpread1.ActiveSheet.Cells[2, 0].Value = System.Drawing.Color.Purple;FarPoint.Win.Spread.CellType.DateTimeCellType datecell = new FarPoint.Win.Spread.CellType.DateTimeCellType(); datecell.DateTimeFormat = FarPoint.Win.Spread.CellType.DateTimeFormat.ShortDate; fpSpread1.ActiveSheet.Columns[1].Width = 175; fpSpread1.ActiveSheet.Columns[1].CellType = datecell; fpSpread1.ActiveSheet.Cells[1, 1].Value = System.DateTime.Now; fpSpread1.ActiveSheet.Cells[2, 1].Value = "5/10/2014";FarPoint.Win.Spread.CellType.NumberCellType nmbrcell = new FarPoint.Win.Spread.CellType.NumberCellType(); fpSpread1.ActiveSheet.Columns[2].CellType = nmbrcell; fpSpread1.ActiveSheet.Cells[0, 2].Value = 0; fpSpread1.ActiveSheet.Cells[1, 2].Value = 5; fpSpread1.ActiveSheet.Cells[2, 2].Value = 1; fpSpread1.ActiveSheet.Cells[3, 2].Value = 4; fpSpread1.ActiveSheet.Cells[4, 2].Value = 10;fpSpread1.Sheets[0].Columns[0, 2].AllowAutoFilter = true; fpSpread1.Sheets[0].AutoFilterMode = FarPoint.Win.Spread.AutoFilterMode.EnhancedContextMenu;

VB

FpSpread1.ActiveSheet.RowCount = 5 FpSpread1.ActiveSheet.ColumnCount = 5 Dim cp As New FarPoint.Win.Spread.CellType.ColorPickerCellType() cp.AllowFullOpen = True cp.AnyColor = False cp.CustomColors = New Integer() {255, 190, 50} cp.FullOpen = True cp.Style = FarPoint.Win.Spread.CellType.ColorPickerStyle.Boxed Dim c As New FarPoint.Win.Spread.CellType.ColorPickerCellType(cp) FpSpread1.ActiveSheet.Columns(0).CellType = c FpSpread1.ActiveSheet.Cells(0, 0).Value = System.Drawing.Color.Aqua FpSpread1.ActiveSheet.Cells(2, 0).Value = System.Drawing.Color.PurpleDim datecell As New FarPoint.Win.Spread.CellType.DateTimeCellType() datecell.DateTimeFormat = FarPoint.Win.Spread.CellType.DateTimeFormat.ShortDate FpSpread1.ActiveSheet.Columns(1).CellType = datecell FpSpread1.ActiveSheet.Columns(1).Width = 175 FpSpread1.ActiveSheet.Cells(1, 1).CellType = datecell FpSpread1.ActiveSheet.Cells(1, 1).Value = System.DateTime.Now FpSpread1.ActiveSheet.Cells(2, 1).Value = "5/10/2014"Dim nmbrcell As New FarPoint.Win.Spread.CellType.NumberCellType() FpSpread1.ActiveSheet.Columns(2).CellType = nmbrcell FpSpread1.ActiveSheet.Cells(0, 2).Value = 0 FpSpread1.ActiveSheet.Cells(1, 2).Value = 5 FpSpread1.ActiveSheet.Cells(2, 2).Value = 1 FpSpread1.ActiveSheet.Cells(3, 2).Value = 4 FpSpread1.ActiveSheet.Cells(4, 2).Value = 10FpSpread1.ActiveSheet.Columns(0, 2).AllowAutoFilter = True FpSpread1.ActiveSheet.AutoFilterMode = FarPoint.Win.Spread.AutoFilterMode.EnhancedContextMenu

You can add and customize the appearance of the filter bar. You can change the background and text colors and the grid lines and their color. The filter bar also provides a date picker to pick a date to filter by. Certain filter menu choices will display the date picker (before or after, for example). You can also type the value in the edit portion of the filter after you select a filter menu option. Setting the AutoFormat property to True specifies to use the DateTimeFormatInfo, FormatString, and NumberFormatInfo properties to format the value in the filter bar. Set these properties if the format of the data in the cell is different from the format in the filter bar. The Equals filter menu option requires that the cell format and the filter bar format be the same. To customize specific cells in the filter bar, set the FilterBar class's Cells properties. To customize the filter bar overall, set the FilterBar's DefaultStyle, Height, HorizontalGridLine, and VerticalGridLine properties. This example sets one cell in the filter bar to display a custom border and background color, and the entire filter bar to display a custom border. filterbar

C#

FarPoint.Win.Spread.SheetView sheetView = fpSpread1.ActiveSheet; sheetView.AutoFilterMode = FarPoint.Win.Spread.AutoFilterMode.FilterBar; sheetView.FilterBar.Cells[0].Border = new FarPoint.Win.DoubleLineBorder(Color.Red); sheetView.FilterBar.Cells[0].BackColor = Color.GreenYellow; sheetView.FilterBar.DefaultStyle.Border = new FarPoint.Win.DoubleLineBorder(Color.Yellow); sheetView.Columns[0].Width = 125;

VB

Dim sheetView As FarPoint.Win.Spread.SheetView = FpSpread1.ActiveSheet sheetView.AutoFilterMode = FarPoint.Win.Spread.AutoFilterMode.FilterBar sheetView.FilterBar.Cells(0).Border = New FarPoint.Win.DoubleLineBorder(Color.Red) sheetView.FilterBar.Cells(0).BackColor = Color.GreenYellow sheetView.FilterBar.DefaultStyle.Border = New FarPoint.Win.DoubleLineBorder(Color.Yellow) sheetView.Columns(0).Width = 125

This example displays a date filter icon if you pick a filter option such as “After”. datefilterafter

C#

FarPoint.Win.Spread.CellType.FilterBarCellType fbcell = new FarPoint.Win.Spread.CellType.FilterBarCellType(); fbcell.AutoFormat = true; fbcell.ContextMenuType = FarPoint.Win.Spread.CellType.FilterBarContextMenuType.DateTime; fpSpread1.ActiveSheet.AutoFilterMode = FarPoint.Win.Spread.AutoFilterMode.FilterBar; fpSpread1.ActiveSheet.FilterBar.Cells[0].CellType = fbcell;FarPoint.Win.Spread.CellType.DateTimeCellType datecell = new FarPoint.Win.Spread.CellType.DateTimeCellType(); fpSpread1.ActiveSheet.Columns[0].Width = 175; fpSpread1.ActiveSheet.Columns[0].CellType = datecell; fpSpread1.ActiveSheet.Cells[0, 0].Value = System.DateTime.Now;

VB

Dim fbcell As New FarPoint.Win.Spread.CellType.FilterBarCellType() fbcell.AutoFormat = True fbcell.ContextMenuType = FarPoint.Win.Spread.CellType.FilterBarContextMenuType.DateTime FpSpread1.ActiveSheet.AutoFilterMode = FarPoint.Win.Spread.AutoFilterMode.FilterBar FpSpread1.ActiveSheet.FilterBar.Cells(0).CellType = fbcellDim datecell As New FarPoint.Win.Spread.CellType.DateTimeCellType() FpSpread1.ActiveSheet.Columns(0).Width = 175 FpSpread1.ActiveSheet.Columns(0).CellType = datecell FpSpread1.ActiveSheet.Cells(0, 0).Value = System.DateTime.Now

MESCIUS inc.

comments powered by Disqus