diff --git a/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/Excel/XLWorkbook_Save.cs index 44dea9e..8f654fe 100644 --- a/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/Excel/XLWorkbook_Save.cs @@ -2405,6 +2405,8 @@ } } + var orderedPageFields = new SortedDictionary(); + foreach (var xlpf in pt.Fields.Cast()) { var ptfi = pti.Fields[xlpf.SourceName]; @@ -2473,6 +2475,7 @@ else if (pt.ReportFilters.Contains(xlpf.SourceName)) { labelOrFilterField = pt.ReportFilters.Get(xlpf.SourceName); + var sortOrderIndex = pt.ReportFilters.IndexOf(labelOrFilterField); location.ColumnsPerPage = 1; location.RowPageCount = 1; @@ -2481,7 +2484,7 @@ var pageField = new PageField { Hierarchy = -1, - Field = pt.Fields.IndexOf(xlpf) + Field = pt.Fields.IndexOf(xlpf), }; if (labelOrFilterField.SelectedValues.Count == 1) @@ -2511,7 +2514,7 @@ .ToList(); var selectedValue = Convert.ToDouble(labelOrFilterField.SelectedValues.Single()); if (values.Contains(selectedValue)) - pageField.Item = Convert.ToUInt32(values.IndexOf(selectedValue)); + pageField.Item = Convert.ToUInt32(values.IndexOf(selectedValue)); } else if (ptfi.DataType == XLDataType.Boolean) { @@ -2535,7 +2538,7 @@ throw new NotImplementedException(); } - pageFields.AppendChild(pageField); + orderedPageFields.Add(sortOrderIndex, pageField); } if ((labelOrFilterField?.SelectedValues?.Count ?? 0) > 1) @@ -2715,6 +2718,7 @@ if (pt.ReportFilters.Any()) { + pageFields.Append(orderedPageFields.Values); pageFields.Count = Convert.ToUInt32(pageFields.Count()); pivotTableDefinition.AppendChild(pageFields); } @@ -2796,7 +2800,7 @@ var pivotTableDefinitionExtensionList = new PivotTableDefinitionExtensionList(); var pivotTableDefinitionExtension = new PivotTableDefinitionExtension { Uri = "{962EF5D1-5CA2-4c93-8EF4-DBF5C05439D2}" }; - pivotTableDefinitionExtension.AddNamespaceDeclaration("x14","http://schemas.microsoft.com/office/spreadsheetml/2009/9/main"); + pivotTableDefinitionExtension.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main"); var pivotTableDefinition2 = new DocumentFormat.OpenXml.Office2010.Excel.PivotTableDefinition { diff --git a/ClosedXML_Tests/Excel/PivotTables/XLPivotTableTests.cs b/ClosedXML_Tests/Excel/PivotTables/XLPivotTableTests.cs index 5187f17..3f868b4 100644 --- a/ClosedXML_Tests/Excel/PivotTables/XLPivotTableTests.cs +++ b/ClosedXML_Tests/Excel/PivotTables/XLPivotTableTests.cs @@ -3,6 +3,7 @@ using System; using System.Collections.Generic; using System.IO; +using System.Linq; namespace ClosedXML_Tests { @@ -292,6 +293,76 @@ } [Test] + public void MaintainPivotTablePageFieldOrder() + { + var pastries = new List + { + new Pastry("Croissant", 101, 150, 60.2, "", new DateTime(2016, 04, 21)), + new Pastry("Croissant", 101, 250, 50.42, "", new DateTime(2016, 05, 03)), + new Pastry("Croissant", 101, 134, 22.12, "", new DateTime(2016, 06, 24)), + new Pastry("Doughnut", 102, 250, 89.99, "", new DateTime(2017, 04, 23)), + new Pastry("Doughnut", 102, 225, 70, "", new DateTime(2016, 05, 24)), + new Pastry("Doughnut", 102, 210, 75.33, "", new DateTime(2016, 06, 02)), + new Pastry("Bearclaw", 103, 134, 10.24, "", new DateTime(2016, 04, 27)), + new Pastry("Bearclaw", 103, 184, 33.33, "", new DateTime(2016, 05, 20)), + new Pastry("Bearclaw", 103, 124, 25, "", new DateTime(2017, 06, 05)), + new Pastry("Danish", 104, 394, -20.24, "", null), + new Pastry("Danish", 104, 190, 60, "", new DateTime(2017, 05, 08)), + new Pastry("Danish", 104, 221, 24.76, "", new DateTime(2016, 06, 21)), + + // Deliberately add different casings of same string to ensure pivot table doesn't duplicate it. + new Pastry("Scone", 105, 135, 0, "", new DateTime(2017, 04, 22)), + new Pastry("SconE", 105, 122, 5.19, "", new DateTime(2017, 05, 03)), + new Pastry("SCONE", 105, 243, 44.2, "", new DateTime(2017, 06, 14)), + + // For ContainsBlank and integer rows/columns test + new Pastry("Scone", null, 255, 18.4, "", null), + }; + + using (var ms = new MemoryStream()) + { + using (var wb = new XLWorkbook()) + { + var sheet = wb.Worksheets.Add("PastrySalesData"); + // Insert our list of pastry data into the "PastrySalesData" sheet at cell 1,1 + var table = sheet.Cell(1, 1).InsertTable(pastries, "PastrySalesData", true); + sheet.Cell("F11").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; + sheet.Columns().AdjustToContents(); + + IXLWorksheet ptSheet; + IXLPivotTable pt; + + // Add a new sheet for our pivot table + ptSheet = wb.Worksheets.Add("pvt"); + + // Create the pivot table, using the data from the "PastrySalesData" table + pt = ptSheet.PivotTables.Add("PastryPivot", ptSheet.Cell(1, 1), table); + + pt.ReportFilters.Add("Month"); + pt.ReportFilters.Add("Name"); + + pt.RowLabels.Add("BakeDate"); + pt.Values.Add("NumberOfOrders").SetSummaryFormula(XLPivotSummary.Sum); + + wb.SaveAs(ms); + } + + ms.Seek(0, SeekOrigin.Begin); + + using (var wb = new XLWorkbook(ms)) + { + var pageFields = wb.Worksheets.SelectMany(ws => ws.PivotTables) + .First() + .ReportFilters + .ToArray(); + + Assert.AreEqual("Month", pageFields[0].SourceName); + Assert.AreEqual("Name", pageFields[1].SourceName); + } + } + } + + [Test] public void ClearPivotTableTenderedTange() { // https://github.com/ClosedXML/ClosedXML/pull/856