| |
 |
How to: Hide Individual Rows and Columns
The following example demonstrates how to hide particular rows and columns by handling the CustomFieldValueCells event. In this example, the event handler iterates through all row headers and removes rows that correspond to the "Employee B" field value, and that are not Total Rows.
| C#:Data.cs |
using System.Data;
using DevExpress.XtraPivotGrid;
namespace XtraPivotGrid_HidingColumnsAndRows {
public static class PivotHelper {
public const string Employee = "Employee";
public const string Widget = "Widget";
public const string Month = "Month";
public const string RetailPrice = "Retail Price";
public const string WholesalePrice = "Wholesale Price";
public const string Quantity = "Quantity";
public const string Remains = "Remains";
public const string EmployeeA = "Employee A";
public const string EmployeeB = "Employee B";
public const string WidgetA = "Widget A";
public const string WidgetB = "Widget B";
public const string WidgetC = "Widget C";
public static void FillPivot(PivotGridControl pivot) {
pivot.Fields.Add(Employee, PivotArea.RowArea);
pivot.Fields.Add(Widget, PivotArea.RowArea);
pivot.Fields.Add(Month, PivotArea.ColumnArea).AreaIndex = 0;
pivot.Fields.Add(RetailPrice, PivotArea.DataArea);
pivot.Fields.Add(WholesalePrice, PivotArea.DataArea);
pivot.Fields.Add(Quantity, PivotArea.DataArea);
pivot.Fields.Add(Remains, PivotArea.DataArea);
foreach (PivotGridField field in pivot.Fields) {
field.AllowedAreas = GetAllowedArea(field.Area);
}
pivot.OptionsView.RowTotalsLocation = PivotRowTotalsLocation.Near;
pivot.OptionsView.ShowRowGrandTotals = false;
pivot.OptionsView.ColumnTotalsLocation = PivotTotalsLocation.Far;
pivot.OptionsDataField.Area = PivotDataArea.ColumnArea;
pivot.OptionsDataField.AreaIndex = 1;
}
static PivotGridAllowedAreas GetAllowedArea(PivotArea area) {
switch (area) {
case PivotArea.ColumnArea:
return PivotGridAllowedAreas.ColumnArea;
case PivotArea.RowArea:
return PivotGridAllowedAreas.RowArea;
case PivotArea.DataArea:
return PivotGridAllowedAreas.DataArea;
case PivotArea.FilterArea:
return PivotGridAllowedAreas.FilterArea;
default:
return PivotGridAllowedAreas.All;
}
}
public static DataTable GetDataTable() {
DataTable table = new DataTable();
table.Columns.Add(Employee, typeof(string));
table.Columns.Add(Widget, typeof(string));
table.Columns.Add(Month, typeof(int));
table.Columns.Add(RetailPrice, typeof(double));
table.Columns.Add(WholesalePrice, typeof(double));
table.Columns.Add(Quantity, typeof(int));
table.Columns.Add(Remains, typeof(int));
table.Rows.Add(EmployeeA, WidgetA, 6, 45.6, 40, 3, 0);
table.Rows.Add(EmployeeA, WidgetA, 7, 38.9, 30, 6, 1);
table.Rows.Add(EmployeeA, WidgetB, 6, 24.7, 20, 7, 0);
table.Rows.Add(EmployeeA, WidgetB, 7, 8.3, 7.5, 5, 1);
table.Rows.Add(EmployeeA, WidgetC, 6, 10.0, 9, 4, 0);
table.Rows.Add(EmployeeA, WidgetC, 7, 20.0, 18.5, 5, 1);
table.Rows.Add(EmployeeB, WidgetA, 6, 77.8, 70, 2, 0);
table.Rows.Add(EmployeeB, WidgetA, 7, 32.5, 30, 1, 1);
table.Rows.Add(EmployeeB, WidgetB, 6, 12, 11, 10, 0);
table.Rows.Add(EmployeeB, WidgetB, 7, 6.7, 5.5, 4, 1);
table.Rows.Add(EmployeeB, WidgetC, 6, 30.0, 28.7, 6, 0);
table.Rows.Add(EmployeeB, WidgetC, 7, 40.0, 38.3, 7, 1);
return table;
}
}
}
|
| C#:Form1.cs |
using System;
using System.Globalization;
using System.Windows.Forms;
using DevExpress.XtraPivotGrid;
namespace XtraPivotGrid_HidingColumnsAndRows {
public partial class Form1 : Form {
public Form1() {
InitializeComponent();
pivotGridControl1.CustomFieldValueCells +=
new PivotCustomFieldValueCellsEventHandler(pivotGrid_CustomFieldValueCells);
}
void Form1_Load(object sender, EventArgs e) {
PivotHelper.FillPivot(pivotGridControl1);
pivotGridControl1.DataSource = PivotHelper.GetDataTable();
pivotGridControl1.BestFit();
}
protected void pivotGrid_CustomFieldValueCells(object sender,
PivotCustomFieldValueCellsEventArgs e) {
PivotGridControl pivot = sender as PivotGridControl;
if (pivot.DataSource == null) return;
if (radioGroup1.SelectedIndex == 0) return;
for (int i = e.GetCellCount(false) - 1; i >= 0; i--) {
FieldValueCell cell = e.GetCell(false, i);
if (cell == null) continue;
if (object.Equals(cell.Value, "Employee B") &&
cell.ValueType != PivotGridValueType.Total)
e.Remove(cell);
}
}
void pivotGridControl1_FieldValueDisplayText(object sender,
PivotFieldDisplayTextEventArgs e) {
PivotGridControl pivot = sender as PivotGridControl;
if (e.Field == pivot.Fields[PivotHelper.Month])
{
e.DisplayText = CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName((int)e.Value);
}
}
void radioGroup1_SelectedIndexChanged(object sender, EventArgs e) {
this.pivotGridControl1.LayoutChanged();
}
}
}
|
| C#:Program.cs |
using System;
using System.Collections.Generic;
using System.Windows.Forms;
namespace XtraPivotGrid_HidingColumnsAndRows {
static class Program {
[STAThread]
static void Main() {
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
Application.Run(new Form1());
}
}
}
|
| VB:Data.vb |
Imports System.Data
Imports DevExpress.XtraPivotGrid
Namespace XtraPivotGrid_HidingColumnsAndRows
Public NotInheritable Class PivotHelper
Private Sub New()
End Sub
Public Const Employee As String = "Employee"
Public Const Widget As String = "Widget"
Public Const Month As String = "Month"
Public Const RetailPrice As String = "Retail Price"
Public Const WholesalePrice As String = "Wholesale Price"
Public Const Quantity As String = "Quantity"
Public Const Remains As String = "Remains"
Public Const EmployeeA As String = "Employee A"
Public Const EmployeeB As String = "Employee B"
Public Const WidgetA As String = "Widget A"
Public Const WidgetB As String = "Widget B"
Public Const WidgetC As String = "Widget C"
Public Shared Sub FillPivot(ByVal pivot As PivotGridControl)
pivot.Fields.Add(Employee, PivotArea.RowArea)
pivot.Fields.Add(Widget, PivotArea.RowArea)
pivot.Fields.Add(Month, PivotArea.ColumnArea).AreaIndex = 0
pivot.Fields.Add(RetailPrice, PivotArea.DataArea)
pivot.Fields.Add(WholesalePrice, PivotArea.DataArea)
pivot.Fields.Add(Quantity, PivotArea.DataArea)
pivot.Fields.Add(Remains, PivotArea.DataArea)
For Each field As PivotGridField In pivot.Fields
field.AllowedAreas = GetAllowedArea(field.Area)
Next field
pivot.OptionsView.RowTotalsLocation = PivotRowTotalsLocation.Near
pivot.OptionsView.ShowRowGrandTotals = False
pivot.OptionsView.ColumnTotalsLocation = PivotTotalsLocation.Far
pivot.OptionsDataField.Area = PivotDataArea.ColumnArea
pivot.OptionsDataField.AreaIndex = 1
End Sub
Private Shared Function GetAllowedArea(ByVal area As PivotArea) As PivotGridAllowedAreas
Select Case area
Case PivotArea.ColumnArea
Return PivotGridAllowedAreas.ColumnArea
Case PivotArea.RowArea
Return PivotGridAllowedAreas.RowArea
Case PivotArea.DataArea
Return PivotGridAllowedAreas.DataArea
Case PivotArea.FilterArea
Return PivotGridAllowedAreas.FilterArea
Case Else
Return PivotGridAllowedAreas.All
End Select
End Function
Public Shared Function GetDataTable() As DataTable
Dim table As New DataTable()
table.Columns.Add(Employee, GetType(String))
table.Columns.Add(Widget, GetType(String))
table.Columns.Add(Month, GetType(Integer))
table.Columns.Add(RetailPrice, GetType(Double))
table.Columns.Add(WholesalePrice, GetType(Double))
table.Columns.Add(Quantity, GetType(Integer))
table.Columns.Add(Remains, GetType(Integer))
table.Rows.Add(EmployeeA, WidgetA, 6, 45.6, 40, 3, 0)
table.Rows.Add(EmployeeA, WidgetA, 7, 38.9, 30, 6, 1)
table.Rows.Add(EmployeeA, WidgetB, 6, 24.7, 20, 7, 0)
table.Rows.Add(EmployeeA, WidgetB, 7, 8.3, 7.5, 5, 1)
table.Rows.Add(EmployeeA, WidgetC, 6, 10.0, 9, 4, 0)
table.Rows.Add(EmployeeA, WidgetC, 7, 20.0, 18.5, 5, 1)
table.Rows.Add(EmployeeB, WidgetA, 6, 77.8, 70, 2, 0)
table.Rows.Add(EmployeeB, WidgetA, 7, 32.5, 30, 1, 1)
table.Rows.Add(EmployeeB, WidgetB, 6, 12, 11, 10, 0)
table.Rows.Add(EmployeeB, WidgetB, 7, 6.7, 5.5, 4, 1)
table.Rows.Add(EmployeeB, WidgetC, 6, 30.0, 28.7, 6, 0)
table.Rows.Add(EmployeeB, WidgetC, 7, 40.0, 38.3, 7, 1)
Return table
End Function
End Class
End Namespace
|
| VB:Form1.vb |
Imports System
Imports System.Globalization
Imports System.Windows.Forms
Imports DevExpress.XtraPivotGrid
Namespace XtraPivotGrid_HidingColumnsAndRows
Partial Public Class Form1
Inherits Form
Public Sub New()
InitializeComponent()
AddHandler pivotGridControl1.CustomFieldValueCells, AddressOf pivotGrid_CustomFieldValueCells
End Sub
Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs) Handles MyBase.Load
PivotHelper.FillPivot(pivotGridControl1)
pivotGridControl1.DataSource = PivotHelper.GetDataTable()
pivotGridControl1.BestFit()
End Sub
Protected Sub pivotGrid_CustomFieldValueCells(ByVal sender As Object,
ByVal e As PivotCustomFieldValueCellsEventArgs)
Dim pivot As PivotGridControl = TryCast(sender, PivotGridControl)
If pivot.DataSource Is Nothing Then
Return
End If
If radioGroup1.SelectedIndex = 0 Then
Return
End If
For i As Integer = e.GetCellCount(False) - 1 To 0 Step -1
Dim cell As FieldValueCell = e.GetCell(False, i)
If cell Is Nothing Then
Continue For
End If
If Object.Equals(cell.Value, "Employee B") AndAlso cell.ValueType <> PivotGridValueType.Total Then
e.Remove(cell)
End If
Next i
End Sub
Private Sub pivotGridControl1_FieldValueDisplayText(ByVal sender As Object,
ByVal e As PivotFieldDisplayTextEventArgs) Handles pivotGridControl1.FieldValueDisplayText
Dim pivot As PivotGridControl = TryCast(sender, PivotGridControl)
If e.Field Is pivot.Fields(PivotHelper.Month) Then
e.DisplayText = CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(CInt((e.Value)))
End If
End Sub
Private Sub radioGroup1_SelectedIndexChanged(ByVal sender As Object,
ByVal e As EventArgs) Handles radioGroup1.SelectedIndexChanged
Me.pivotGridControl1.LayoutChanged()
pivotGridControl1.BestFit()
End Sub
End Class
End Namespace
|
| VB:Program.vb |
Imports System
Imports System.Collections.Generic
Imports System.Windows.Forms
Namespace XtraPivotGrid_HidingColumnsAndRows
Friend NotInheritable Class Program
Private Sub New()
End Sub
<STAThread> _
Shared Sub Main()
Application.EnableVisualStyles()
Application.SetCompatibleTextRenderingDefault(False)
Application.Run(New Form1())
End Sub
End Class
End Namespace
|
How would you rate this topic?
Additional Feedback
Close
|