wpf-spreadsheet-pivot-table-api-examples/CS/SpreadsheetWPFPivotTableExamples/CodeExamples/PivotFieldActions.cs at 24.2.1+ · DevExpress-Examples/wpf-spreadsheet-pivot-table-api-examples · GitHub

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
124
125
126
127
128
129
130
131
using DevExpress.Spreadsheet;

namespace SpreadsheetWPFPivotTableExamples
{
    public static class PivotFieldActions
    {
        static void AddFieldToAxis(IWorkbook workbook)
        {
            #region #AddToAxis
            Worksheet sourceWorksheet = workbook.Worksheets["Data1"];
            Worksheet worksheet = workbook.Worksheets.Add();
            workbook.Worksheets.ActiveWorksheet = worksheet;

            // Create a pivot table.
            PivotTable pivotTable = worksheet.PivotTables.Add(sourceWorksheet["A1:D41"], worksheet["B2"]);

            // Add the "Product" field to the row axis area.
            pivotTable.RowFields.Add(pivotTable.Fields["Product"]);
            // Add the "Category" field to the column axis area.
            pivotTable.ColumnFields.Add(pivotTable.Fields["Category"]);
            // Add the "Sales" field to the data area and specify the custom field name.
            pivotTable.DataFields.Add(pivotTable.Fields["Sales"], "Sales(Sum)");
            // Add the "Region" field to the filter area.
            pivotTable.PageFields.Add(pivotTable.Fields["Region"]);

            // Specify the number format for the "Sales" field.
            pivotTable.Fields["Sales"].NumberFormat = @"_([$$-409]* #,##0.00_);_([$$-409]* (#,##0.00);_([$$-409]* "" - ""??_);_(@_)";
            #endregion #AddToAxis
        }

        static void InsertFieldToAxis(IWorkbook workbook)
        {
            #region #InsertAtTop
            Worksheet worksheet = workbook.Worksheets["Report1"];
            workbook.Worksheets.ActiveWorksheet = worksheet;

            // Access the pivot table by its name in the collection.
            PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];

            // Insert the "Region" field at the top of the row axis area.
            pivotTable.RowFields.Insert(0, pivotTable.Fields["Region"]);
            #endregion #InsertAtTop
        }

        static void MoveFieldToAxis(IWorkbook workbook)
        {
            #region #MoveToAxis
            Worksheet worksheet = workbook.Worksheets["Report1"];
            workbook.Worksheets.ActiveWorksheet = worksheet;

            // Access the pivot table by its name in the collection.
            PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];

            // Move the "Region" field to the column axis area.
            pivotTable.ColumnFields.Add(pivotTable.Fields["Region"]);
            #endregion #MoveToAxis
        }

        static void MoveFieldUp(IWorkbook workbook)
        {
            #region #MoveUp
            Worksheet worksheet = workbook.Worksheets["Report3"];
            workbook.Worksheets.ActiveWorksheet = worksheet;

            // Access the pivot table by its name in the collection.
            PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];

            // Move the "Category" field one position up in the row area.
            pivotTable.RowFields["Category"].MoveUp();
            #endregion #MoveUp
        }

        static void MoveFieldDown(IWorkbook workbook)
        {
            #region #MoveDown
            Worksheet worksheet = workbook.Worksheets["Report3"];
            workbook.Worksheets.ActiveWorksheet = worksheet;

            // Access the pivot table by its name in the collection.
            PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];

            // Move the "Region" field one position down in the row area.
            pivotTable.RowFields["Region"].MoveDown();
            #endregion #MoveDown
        }

        static void RemoveFieldFromAxis(IWorkbook workbook)
        {
            #region #RemoveFromAxis
            Worksheet worksheet = workbook.Worksheets["Report1"];
            workbook.Worksheets.ActiveWorksheet = worksheet;

            // Access the pivot table by its name in the collection.
            PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];

            // Remove the "Product" field from the row axis area.
            pivotTable.RowFields.Remove(pivotTable.RowFields["Product"]);
            #endregion #RemoveFromAxis
        }

        static void SortFieldItems(IWorkbook workbook)
        {
            #region #SortFieldItems
            Worksheet worksheet = workbook.Worksheets["Report1"];
            workbook.Worksheets.ActiveWorksheet = worksheet;

            // Access the pivot table by its name in the collection.
            PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
            // Access the pivot field by its name in the collection.
            PivotField field = pivotTable.Fields["Product"];
            // Sort items in the "Product" field.
            field.SortType = PivotFieldSortType.Ascending;
            #endregion #SortFieldItems
        }

        static void SortFieldItemsByDataField(IWorkbook workbook)
        {
            #region #SortFieldItemsByDataField
            Worksheet worksheet = workbook.Worksheets["Report1"];
            workbook.Worksheets.ActiveWorksheet = worksheet;

            // Access the pivot table by its name in the collection.
            PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
            // Access the pivot field by its name in the collection.
            PivotField field = pivotTable.Fields["Product"];
            // Sort items in the "Product" field by values of the "Sum of Sales" data field.
            field.SortItems(PivotFieldSortType.Ascending, 0);
            #endregion #SortFieldItemsByDataField
        }
    }
}