diff --git a/ClosedXML/Excel/PivotTables/IXLPivotField.cs b/ClosedXML/Excel/PivotTables/IXLPivotField.cs index 20b3ef4..cc6fe91 100644 --- a/ClosedXML/Excel/PivotTables/IXLPivotField.cs +++ b/ClosedXML/Excel/PivotTables/IXLPivotField.cs @@ -35,6 +35,7 @@ Boolean ShowBlankItems { get; set; } Boolean InsertPageBreaks { get; set; } Boolean Collapsed { get; set; } + XLPivotSortType SortType { get; set; } IXLPivotField SetCustomName(String value); @@ -48,6 +49,7 @@ IXLPivotField SetShowBlankItems(); IXLPivotField SetShowBlankItems(Boolean value); IXLPivotField SetInsertPageBreaks(); IXLPivotField SetInsertPageBreaks(Boolean value); IXLPivotField SetCollapsed(); IXLPivotField SetCollapsed(Boolean value); + IXLPivotField SetSort(XLPivotSortType value); IList SelectedValues { get; } IXLPivotField AddSelectedValue(Object value); diff --git a/ClosedXML/Excel/PivotTables/IXLPivotTable.cs b/ClosedXML/Excel/PivotTables/IXLPivotTable.cs index 027dd94..bcc4292 100644 --- a/ClosedXML/Excel/PivotTables/IXLPivotTable.cs +++ b/ClosedXML/Excel/PivotTables/IXLPivotTable.cs @@ -91,6 +91,13 @@ PivotStyleMedium9 } + public enum XLPivotSortType + { + Default = 0, + Ascending = 1, + Descending = 2 + } + public enum XLPivotSubtotals { DoNotShow, diff --git a/ClosedXML/Excel/PivotTables/XLPivotField.cs b/ClosedXML/Excel/PivotTables/XLPivotField.cs index acf0b1c..64a0b99 100644 --- a/ClosedXML/Excel/PivotTables/XLPivotField.cs +++ b/ClosedXML/Excel/PivotTables/XLPivotField.cs @@ -12,6 +12,7 @@ SourceName = sourceName; Subtotals = new List(); SelectedValues = new List(); + SortType = XLPivotSortType.Default; } public String SourceName { get; private set; } @@ -69,6 +70,10 @@ public IXLPivotField SetCollapsed(Boolean value) { Collapsed = value; return this; } + public XLPivotSortType SortType { get; set; } + + public IXLPivotField SetSort(XLPivotSortType value) { SortType = value; return this; } + public IList SelectedValues { get; private set; } public IXLPivotField AddSelectedValue(Object value) { diff --git a/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/Excel/XLWorkbook_Load.cs index 15696ad..44d678a 100644 --- a/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/Excel/XLWorkbook_Load.cs @@ -607,6 +607,11 @@ var items = pf.Items.OfType().Where(i => i.Index != null && i.Index.HasValue); if (!items.Any(i => i.HideDetails == null || BooleanValue.ToBoolean(i.HideDetails))) pivotField.SetCollapsed(); + + if (pf.SortType != null) + { + pivotField.SetSort((XLPivotSortType)pf.SortType.Value); + } } } } @@ -662,6 +667,11 @@ var items = pf.Items.OfType().Where(i => i.Index != null && i.Index.HasValue); if (!items.Any(i => i.HideDetails == null || BooleanValue.ToBoolean(i.HideDetails))) pivotField.SetCollapsed(); + + if (pf.SortType != null) + { + pivotField.SetSort((XLPivotSortType)pf.SortType.Value); + } } } } diff --git a/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/Excel/XLWorkbook_Save.cs index 4c0831a..860f680 100644 --- a/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/Excel/XLWorkbook_Save.cs @@ -2020,6 +2020,7 @@ if (field != null) { xlpf.CustomName = field.CustomName; + xlpf.SortType = field.SortType; xlpf.Subtotals.AddRange(field.Subtotals); } @@ -2295,6 +2296,11 @@ pf.Compact = false; } + if (xlpf.SortType != XLPivotSortType.Default) + { + pf.SortType = new EnumValue((FieldSortValues)xlpf.SortType); + } + switch (pt.Subtotals) { case XLPivotSubtotals.DoNotShow: diff --git a/ClosedXML_Examples/PivotTables/PivotTables.cs b/ClosedXML_Examples/PivotTables/PivotTables.cs index b8f6023..0d92978 100644 --- a/ClosedXML_Examples/PivotTables/PivotTables.cs +++ b/ClosedXML_Examples/PivotTables/PivotTables.cs @@ -193,6 +193,21 @@ .AddSelectedValue(new DateTime(2017, 05, 03)); #endregion Pivot Table with filter + + #region Pivot table sorting + + ptSheet = wb.Worksheets.Add("pvtSort"); + pt = ptSheet.PivotTables.AddNew("pvtSort", ptSheet.Cell(1, 1), dataRange); + + pt.RowLabels.Add("Name").SetSort(XLPivotSortType.Ascending); + pt.RowLabels.Add("Month").SetSort(XLPivotSortType.Descending); + + pt.Values.Add("NumberOfOrders").SetSummaryFormula(XLPivotSummary.Sum); + pt.Values.Add("Quality").SetSummaryFormula(XLPivotSummary.Sum); + + pt.SetRowHeaderCaption("Pastry name"); + + #endregion Different kind of pivot wb.SaveAs(filePath); } diff --git a/ClosedXML_Tests/Excel/Loading/LoadingTests.cs b/ClosedXML_Tests/Excel/Loading/LoadingTests.cs index 6efca95..e376449 100644 --- a/ClosedXML_Tests/Excel/Loading/LoadingTests.cs +++ b/ClosedXML_Tests/Excel/Loading/LoadingTests.cs @@ -123,6 +123,20 @@ } [Test] + public void CanLoadOrderedPivotTable() + { + using (var stream = TestHelper.GetStreamFromResource(TestHelper.GetResourcePath(@"Misc\LoadPivotTables.xlsx"))) + using (var wb = new XLWorkbook(stream)) + { + var ws = wb.Worksheet("OrderedPivotTable"); + var pt = ws.PivotTable("OrderedPivotTable"); + + Assert.AreEqual(XLPivotSortType.Ascending, pt.RowLabels.Single().SortType); + Assert.AreEqual(XLPivotSortType.Descending, pt.ColumnLabels.Single().SortType); + } + } + + [Test] public void CanLoadPivotTableSubtotals() { using (var stream = TestHelper.GetStreamFromResource(TestHelper.GetResourcePath(@"Misc\LoadPivotTables.xlsx"))) diff --git a/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx b/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx index c0f266b..e4616db 100644 --- a/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx +++ b/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx Binary files differ