diff --git a/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/Excel/XLWorkbook_Load.cs index 75f9cee..aa9fbb3 100644 --- a/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/Excel/XLWorkbook_Load.cs @@ -513,6 +513,15 @@ if (pivotTableDefinition.ItemPrintTitles != null) pt.RepeatRowLabels = pivotTableDefinition.ItemPrintTitles.Value; if (pivotTableDefinition.FieldPrintTitles != null) pt.PrintTitles = pivotTableDefinition.FieldPrintTitles.Value; if (pivotTableDefinition.EnableDrill != null) pt.EnableShowDetails = pivotTableDefinition.EnableDrill.Value; + if (pivotTableCacheDefinitionPart.PivotCacheDefinition.SaveData != null) pt.SaveSourceData = pivotTableCacheDefinitionPart.PivotCacheDefinition.SaveData.Value; + + if (pivotTableCacheDefinitionPart.PivotCacheDefinition.MissingItemsLimit != null) + { + if (pivotTableCacheDefinitionPart.PivotCacheDefinition.MissingItemsLimit == 0U) + pt.ItemsToRetainPerField = XLItemsToRetain.None; + else if (pivotTableCacheDefinitionPart.PivotCacheDefinition.MissingItemsLimit == XLHelper.MaxRowNumber) + pt.ItemsToRetainPerField = XLItemsToRetain.Max; + } if (pivotTableDefinition.ShowMissing != null && pivotTableDefinition.MissingCaption != null) pt.EmptyCellReplacement = pivotTableDefinition.MissingCaption.Value; @@ -520,6 +529,25 @@ if (pivotTableDefinition.ShowError != null && pivotTableDefinition.ErrorCaption != null) pt.ErrorValueReplacement = pivotTableDefinition.ErrorCaption.Value; + var pivotTableDefinitionExtensionList = pivotTableDefinition.GetFirstChild(); + var pivotTableDefinitionExtension = pivotTableDefinitionExtensionList?.GetFirstChild(); + var pivotTableDefinition2 = pivotTableDefinitionExtension?.GetFirstChild(); + if (pivotTableDefinition2 != null) + { + if (pivotTableDefinition2.EnableEdit != null) pt.EnableCellEditing = pivotTableDefinition2.EnableEdit.Value; + if (pivotTableDefinition2.HideValuesRow != null) pt.ShowValuesRow = !pivotTableDefinition2.HideValuesRow.Value; + } + + var pivotTableStyle = pivotTableDefinition.GetFirstChild(); + if (pivotTableStyle != null) + { + pt.Theme = (XLPivotTableTheme) Enum.Parse(typeof(XLPivotTableTheme), pivotTableStyle.Name); + pt.ShowRowHeaders = pivotTableStyle.ShowRowHeaders; + pt.ShowColumnHeaders = pivotTableStyle.ShowColumnHeaders; + pt.ShowRowStripes = pivotTableStyle.ShowRowStripes; + pt.ShowColumnStripes = pivotTableStyle.ShowColumnStripes; + } + // Subtotal configuration if (pivotTableDefinition.PivotFields.Cast().All(pf => pf.SubtotalTop != null && pf.SubtotalTop.HasValue && pf.SubtotalTop.Value)) pt.SetSubtotals(XLPivotSubtotals.AtTop); diff --git a/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/Excel/XLWorkbook_Save.cs index 0fda27a..b4c58f1 100644 --- a/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/Excel/XLWorkbook_Save.cs @@ -2158,21 +2158,21 @@ PreserveFormatting = OpenXmlHelper.GetBooleanValue(pt.PreserveCellFormatting, true), RowGrandTotals = OpenXmlHelper.GetBooleanValue(pt.ShowGrandTotalsRows, true), ColumnGrandTotals = OpenXmlHelper.GetBooleanValue(pt.ShowGrandTotalsColumns, true), - SubtotalHiddenItems = OpenXmlHelper.GetBooleanValue(pt.FilteredItemsInSubtotals, true), - MultipleFieldFilters = OpenXmlHelper.GetBooleanValue(pt.AllowMultipleFilters, true), + SubtotalHiddenItems = OpenXmlHelper.GetBooleanValue(pt.FilteredItemsInSubtotals, false), + MultipleFieldFilters = OpenXmlHelper.GetBooleanValue(pt.AllowMultipleFilters, false), CustomListSort = OpenXmlHelper.GetBooleanValue(pt.UseCustomListsForSorting, true), ShowDrill = OpenXmlHelper.GetBooleanValue(pt.ShowExpandCollapseButtons, true), ShowDataTips = OpenXmlHelper.GetBooleanValue(pt.ShowContextualTooltips, true), - ShowMemberPropertyTips = OpenXmlHelper.GetBooleanValue(pt.ShowPropertiesInTooltips, true), + ShowMemberPropertyTips = OpenXmlHelper.GetBooleanValue(pt.ShowPropertiesInTooltips, false), ShowHeaders = OpenXmlHelper.GetBooleanValue(pt.DisplayCaptionsAndDropdowns, true), GridDropZones = OpenXmlHelper.GetBooleanValue(pt.ClassicPivotTableLayout, false), - ShowEmptyRow = OpenXmlHelper.GetBooleanValue(pt.ShowEmptyItemsOnRows, true), - ShowEmptyColumn = OpenXmlHelper.GetBooleanValue(pt.ShowEmptyItemsOnColumns, true), - ShowItems = OpenXmlHelper.GetBooleanValue(pt.DisplayItemLabels, true), - FieldListSortAscending = OpenXmlHelper.GetBooleanValue(pt.SortFieldsAtoZ, true), - PrintDrill = OpenXmlHelper.GetBooleanValue(pt.PrintExpandCollapsedButtons, true), + ShowEmptyRow = OpenXmlHelper.GetBooleanValue(pt.ShowEmptyItemsOnRows, false), + ShowEmptyColumn = OpenXmlHelper.GetBooleanValue(pt.ShowEmptyItemsOnColumns, false), + ShowItems = OpenXmlHelper.GetBooleanValue(pt.DisplayItemLabels, false), + FieldListSortAscending = OpenXmlHelper.GetBooleanValue(pt.SortFieldsAtoZ, false), + PrintDrill = OpenXmlHelper.GetBooleanValue(pt.PrintExpandCollapsedButtons, false), ItemPrintTitles = OpenXmlHelper.GetBooleanValue(pt.RepeatRowLabels, true), - FieldPrintTitles = OpenXmlHelper.GetBooleanValue(pt.PrintTitles, true), + FieldPrintTitles = OpenXmlHelper.GetBooleanValue(pt.PrintTitles, false), EnableDrill = OpenXmlHelper.GetBooleanValue(pt.EnableShowDetails, true) }; diff --git a/ClosedXML_Tests/Excel/PivotTables/XLPivotTableTests.cs b/ClosedXML_Tests/Excel/PivotTables/XLPivotTableTests.cs index f61b84b..0066287 100644 --- a/ClosedXML_Tests/Excel/PivotTables/XLPivotTableTests.cs +++ b/ClosedXML_Tests/Excel/PivotTables/XLPivotTableTests.cs @@ -29,5 +29,120 @@ } } } + + [Test] + public void PivotTableOptionsSaveTest() + { + using (var stream = TestHelper.GetStreamFromResource(TestHelper.GetResourcePath(@"Examples\PivotTables\PivotTables.xlsx"))) + using (var wb = new XLWorkbook(stream)) + { + var ws = wb.Worksheet("PastrySalesData"); + var table = ws.Table("PastrySalesData"); + + var range = table.DataRange; + var header = ws.Range(1, 1, 1, 3); + var dataRange = ws.Range(header.FirstCell(), range.LastCell()); + + var ptSheet = wb.Worksheets.Add("BlankPivotTable"); + var pt = ptSheet.PivotTables.AddNew("pvtOptionsTest", ptSheet.Cell(1, 1), dataRange); + + pt.ColumnHeaderCaption = "clmn header"; + pt.RowHeaderCaption = "row header"; + + pt.AutofitColumns = false; + pt.PreserveCellFormatting = false; + pt.ShowGrandTotalsColumns = false; + pt.ShowGrandTotalsRows = false; + pt.UseCustomListsForSorting = false; + pt.ShowExpandCollapseButtons = false; + pt.ShowContextualTooltips = false; + pt.DisplayCaptionsAndDropdowns = false; + pt.RepeatRowLabels = false; + pt.SaveSourceData = false; + pt.EnableShowDetails = false; + pt.ShowColumnHeaders = false; + pt.ShowRowHeaders = false; + + pt.MergeAndCenterWithLabels = true; // MergeItem + pt.RowLabelIndent = 12; // Indent + pt.FilterAreaOrder = XLFilterAreaOrder.DownThenOver; // PageOverThenDown + pt.FilterFieldsPageWrap = 14; // PageWrap + pt.ErrorValueReplacement = "error test"; // ErrorCaption + pt.EmptyCellReplacement = "empty test"; // MissingCaption + + pt.FilteredItemsInSubtotals = true; // Subtotal filtered page items + pt.AllowMultipleFilters = true; // MultipleFieldFilters + + pt.ShowPropertiesInTooltips = true; + pt.ClassicPivotTableLayout = true; + pt.ShowEmptyItemsOnRows = true; + pt.ShowEmptyItemsOnColumns = true; + pt.DisplayItemLabels = true; + pt.SortFieldsAtoZ = true; + + pt.PrintExpandCollapsedButtons = true; + pt.PrintTitles = true; + + // TODO pt.RefreshDataOnOpen = false; + pt.ItemsToRetainPerField = XLItemsToRetain.Max; + pt.EnableCellEditing = true; + pt.ShowValuesRow = true; + pt.ShowRowStripes = true; + pt.ShowColumnStripes = true; + pt.Theme = XLPivotTableTheme.PivotStyleDark13; + + using (var ms = new MemoryStream()) + { + wb.SaveAs(ms, true); + + ms.Position = 0; + + using (var wbassert = new XLWorkbook(ms)) + { + var wsassert = wbassert.Worksheet("BlankPivotTable"); + var ptassert = wsassert.PivotTable("pvtOptionsTest"); + Assert.AreNotEqual(null, ptassert, "name save failure"); + Assert.AreEqual("clmn header", ptassert.ColumnHeaderCaption, "ColumnHeaderCaption save failure"); + Assert.AreEqual("row header", ptassert.RowHeaderCaption, "RowHeaderCaption save failure"); + Assert.AreEqual(true, ptassert.MergeAndCenterWithLabels, "MergeAndCenterWithLabels save failure"); + Assert.AreEqual(12, ptassert.RowLabelIndent, "RowLabelIndent save failure"); + Assert.AreEqual(XLFilterAreaOrder.DownThenOver, ptassert.FilterAreaOrder, "FilterAreaOrder save failure"); + Assert.AreEqual(14, ptassert.FilterFieldsPageWrap, "FilterFieldsPageWrap save failure"); + Assert.AreEqual("error test", ptassert.ErrorValueReplacement, "ErrorValueReplacement save failure"); + Assert.AreEqual("empty test", ptassert.EmptyCellReplacement, "EmptyCellReplacement save failure"); + Assert.AreEqual(false, ptassert.AutofitColumns, "AutofitColumns save failure"); + Assert.AreEqual(false, ptassert.PreserveCellFormatting, "PreserveCellFormatting save failure"); + Assert.AreEqual(false, ptassert.ShowGrandTotalsRows, "ShowGrandTotalsRows save failure"); + Assert.AreEqual(false, ptassert.ShowGrandTotalsColumns, "ShowGrandTotalsColumns save failure"); + Assert.AreEqual(true, ptassert.FilteredItemsInSubtotals, "FilteredItemsInSubtotals save failure"); + Assert.AreEqual(true, ptassert.AllowMultipleFilters, "AllowMultipleFilters save failure"); + Assert.AreEqual(false, ptassert.UseCustomListsForSorting, "UseCustomListsForSorting save failure"); + Assert.AreEqual(false, ptassert.ShowExpandCollapseButtons, "ShowExpandCollapseButtons save failure"); + Assert.AreEqual(false, ptassert.ShowContextualTooltips, "ShowContextualTooltips save failure"); + Assert.AreEqual(true, ptassert.ShowPropertiesInTooltips, "ShowPropertiesInTooltips save failure"); + Assert.AreEqual(false, ptassert.DisplayCaptionsAndDropdowns, "DisplayCaptionsAndDropdowns save failure"); + Assert.AreEqual(true, ptassert.ClassicPivotTableLayout, "ClassicPivotTableLayout save failure"); + Assert.AreEqual(true, ptassert.ShowEmptyItemsOnRows, "ShowEmptyItemsOnRows save failure"); + Assert.AreEqual(true, ptassert.ShowEmptyItemsOnColumns, "ShowEmptyItemsOnColumns save failure"); + Assert.AreEqual(true, ptassert.DisplayItemLabels, "DisplayItemLabels save failure"); + Assert.AreEqual(true, ptassert.SortFieldsAtoZ, "SortFieldsAtoZ save failure"); + Assert.AreEqual(true, ptassert.PrintExpandCollapsedButtons, "PrintExpandCollapsedButtons save failure"); + Assert.AreEqual(false, ptassert.RepeatRowLabels, "RepeatRowLabels save failure"); + Assert.AreEqual(true, ptassert.PrintTitles, "PrintTitles save failure"); + Assert.AreEqual(false, ptassert.SaveSourceData, "SaveSourceData save failure"); + Assert.AreEqual(false, ptassert.EnableShowDetails, "EnableShowDetails save failure"); + // TODO Assert.AreEqual(false, ptassert.RefreshDataOnOpen, "RefreshDataOnOpen save failure"); + Assert.AreEqual(XLItemsToRetain.Max, ptassert.ItemsToRetainPerField, "ItemsToRetainPerField save failure"); + Assert.AreEqual(true, ptassert.EnableCellEditing, "EnableCellEditing save failure"); + Assert.AreEqual(XLPivotTableTheme.PivotStyleDark13, ptassert.Theme, "Theme save failure"); + Assert.AreEqual(true, ptassert.ShowValuesRow, "ShowValuesRow save failure"); + Assert.AreEqual(false, ptassert.ShowRowHeaders, "ShowRowHeaders save failure"); + Assert.AreEqual(false, ptassert.ShowColumnHeaders, "ShowColumnHeaders save failure"); + Assert.AreEqual(true, ptassert.ShowRowStripes, "ShowRowStripes save failure"); + Assert.AreEqual(true, ptassert.ShowColumnStripes, "ShowColumnStripes save failure"); + } + } + } + } } } diff --git a/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx b/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx index d6f65c7..3137b74 100644 --- a/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx +++ b/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx Binary files differ