-
Notifications
You must be signed in to change notification settings - Fork 1
/
PivotTableFilterActions.cs
124 lines (103 loc) · 5.23 KB
/
PivotTableFilterActions.cs
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
using DevExpress.Spreadsheet;
namespace SpreadsheetPivotTableExamples
{
public static class PivotTableFilterActions
{
static void SetItemFilter(IWorkbook workbook)
{
#region #ItemFilter
Worksheet worksheet = workbook.Worksheets["Report4"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Access the pivot table by its name in the collection.
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
// Show the first item in the "Product" field.
pivotTable.Fields[1].ShowSingleItem(0);
//Show all items in the "Product" field (the default option).
//pivotTable.Fields[1].ShowAllItems();
#endregion #ItemFilter
}
static void SetItemVisibilityFilter(IWorkbook workbook)
{
#region #ItemVisibility
Worksheet worksheet = workbook.Worksheets["Report4"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Access the pivot table by its name in the collection.
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
// Access items of the "Product" field.
PivotItemCollection pivotFieldItems = pivotTable.Fields[1].Items;
// Hide the first item in the "Product" field.
pivotFieldItems[0].Visible = false;
#endregion #ItemVisibility
}
static void SetLabelFilter(IWorkbook workbook)
{
#region #LabelFilter
Worksheet worksheet = workbook.Worksheets["Report4"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Access the pivot table by its name in the collection.
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
// Access the "Region" field.
PivotField field = pivotTable.Fields[0];
// Filter the "Region" field by text to display sales data for the "South" region.
pivotTable.Filters.Add(field, PivotFilterType.CaptionEqual, "South");
#endregion #LabelFilter
}
static void SetValueFilter(IWorkbook workbook)
{
#region #ValueFilter
Worksheet worksheet = workbook.Worksheets["Report4"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Access the pivot table by its name in the collection.
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
// Access the "Product" field.
PivotField field = pivotTable.Fields[1];
// Filter the "Product" field to display products with grand total sales between $6000 and $13000.
pivotTable.Filters.Add(field, pivotTable.DataFields[0], PivotFilterType.ValueBetween, 6000, 13000);
#endregion #ValueFilter
}
static void SetTop10Filter(IWorkbook workbook)
{
#region #Top10Filter
Worksheet worksheet = workbook.Worksheets["Report4"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Access the pivot table by its name in the collection.
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
// Access the "Product" field.
PivotField field = pivotTable.Fields[1];
// Filter the "Product" field to display two products with the lowest sales.
PivotFilter filter = pivotTable.Filters.Add(field, pivotTable.DataFields[0], PivotFilterType.Count, 2);
filter.Top10Type = PivotFilterTop10Type.Bottom;
#endregion #Top10Filter
}
static void SetDateFilter(IWorkbook workbook)
{
#region #DateFilter
Worksheet worksheet = workbook.Worksheets["Report6"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Access the pivot table by its name in the collection.
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
// Access the "Date" field.
PivotField field = pivotTable.Fields[0];
// Filter the "Date" field to display sales for the second quarter.
pivotTable.Filters.Add(field, PivotFilterType.SecondQuarter);
#endregion #DateFilter
}
static void SetMultipleFilter(IWorkbook workbook)
{
#region #MultipleFilters
Worksheet worksheet = workbook.Worksheets["Report6"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Access the pivot table by its name in the collection.
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
// Allow multiple filters for a field.
pivotTable.Behavior.AllowMultipleFieldFilters = true;
// Filter the "Date" field to display sales for the second quarter.
PivotField field1 = pivotTable.Fields[0];
pivotTable.Filters.Add(field1, PivotFilterType.SecondQuarter);
// Add the second filter to the "Date" field to display two days with the lowest sales.
PivotFilter filter = pivotTable.Filters.Add(field1, pivotTable.DataFields[0], PivotFilterType.Count, 2);
filter.Top10Type = PivotFilterTop10Type.Bottom;
#endregion #MultipleFilters
}
}
}