diff --git a/ClosedXML/Excel/PivotTables/XLPivotTable.cs b/ClosedXML/Excel/PivotTables/XLPivotTable.cs index 6832af3..1d7895d 100644 --- a/ClosedXML/Excel/PivotTables/XLPivotTable.cs +++ b/ClosedXML/Excel/PivotTables/XLPivotTable.cs @@ -295,19 +295,35 @@ private void SetExcelDefaults() { EmptyCellReplacement = String.Empty; - AutofitColumns = true; - PreserveCellFormatting = true; - ShowGrandTotalsColumns = true; - ShowGrandTotalsRows = true; - UseCustomListsForSorting = true; - ShowExpandCollapseButtons = true; - ShowContextualTooltips = true; - DisplayCaptionsAndDropdowns = true; - RepeatRowLabels = true; SaveSourceData = true; - EnableShowDetails = true; ShowColumnHeaders = true; ShowRowHeaders = true; + + // source http://www.datypic.com/sc/ooxml/e-ssml_pivotTableDefinition.html + DisplayItemLabels = true; // Show Item Names + ShowExpandCollapseButtons = true; // Show Expand Collapse + PrintExpandCollapsedButtons = false; // Print Drill Indicators + ShowPropertiesInTooltips = true; // Show Member Property ToolTips + ShowContextualTooltips = true; // Show ToolTips on Data + EnableShowDetails = true; // Enable Drill Down + PreserveCellFormatting = true; // Preserve Formatting + AutofitColumns = false; // Auto Formatting + FilterAreaOrder = XLFilterAreaOrder.DownThenOver; // Page Over Then Down + FilteredItemsInSubtotals = false; // Subtotal Hidden Items + ShowGrandTotalsRows = true; // Row Grand Totals + ShowGrandTotalsColumns = true; // Grand Totals On Columns + PrintTitles = false; // Field Print Titles + RepeatRowLabels = false; // Item Print Titles + MergeAndCenterWithLabels = false; // Merge Titles + RowLabelIndent = 1; // Indentation for Compact Axis + ShowEmptyItemsOnRows = false; // Show Empty Row + ShowEmptyItemsOnColumns = false; // Show Empty Column + DisplayCaptionsAndDropdowns = true; // Show Field Headers + ClassicPivotTableLayout = false; // Enable Drop Zones + AllowMultipleFilters = true; // Multiple Field Filters + SortFieldsAtoZ = false; // Default Sort Order + UseCustomListsForSorting = true; // Custom List AutoSort + } } } diff --git a/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/Excel/XLWorkbook_Load.cs index 7641a16..5124abd 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 30c3287..8451e51 100644 --- a/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/Excel/XLWorkbook_Save.cs @@ -2149,16 +2149,16 @@ Name = pt.Name, CacheId = cacheId, DataCaption = "Values", - MergeItem = OpenXmlHelper.GetBooleanValue(pt.MergeAndCenterWithLabels, true), + MergeItem = OpenXmlHelper.GetBooleanValue(pt.MergeAndCenterWithLabels, false), Indent = Convert.ToUInt32(pt.RowLabelIndent), PageOverThenDown = (pt.FilterAreaOrder == XLFilterAreaOrder.OverThenDown), PageWrap = Convert.ToUInt32(pt.FilterFieldsPageWrap), ShowError = String.IsNullOrEmpty(pt.ErrorValueReplacement), - UseAutoFormatting = OpenXmlHelper.GetBooleanValue(pt.AutofitColumns, true), + UseAutoFormatting = OpenXmlHelper.GetBooleanValue(pt.AutofitColumns, false), PreserveFormatting = OpenXmlHelper.GetBooleanValue(pt.PreserveCellFormatting, true), RowGrandTotals = OpenXmlHelper.GetBooleanValue(pt.ShowGrandTotalsRows, true), ColumnGrandTotals = OpenXmlHelper.GetBooleanValue(pt.ShowGrandTotalsColumns, true), - SubtotalHiddenItems = OpenXmlHelper.GetBooleanValue(pt.FilteredItemsInSubtotals, true), + SubtotalHiddenItems = OpenXmlHelper.GetBooleanValue(pt.FilteredItemsInSubtotals, false), MultipleFieldFilters = OpenXmlHelper.GetBooleanValue(pt.AllowMultipleFilters, true), CustomListSort = OpenXmlHelper.GetBooleanValue(pt.UseCustomListsForSorting, true), ShowDrill = OpenXmlHelper.GetBooleanValue(pt.ShowExpandCollapseButtons, true), @@ -2166,13 +2166,13 @@ ShowMemberPropertyTips = OpenXmlHelper.GetBooleanValue(pt.ShowPropertiesInTooltips, true), ShowHeaders = OpenXmlHelper.GetBooleanValue(pt.DisplayCaptionsAndDropdowns, true), GridDropZones = OpenXmlHelper.GetBooleanValue(pt.ClassicPivotTableLayout, false), - ShowEmptyRow = OpenXmlHelper.GetBooleanValue(pt.ShowEmptyItemsOnRows, true), - ShowEmptyColumn = OpenXmlHelper.GetBooleanValue(pt.ShowEmptyItemsOnColumns, true), + ShowEmptyRow = OpenXmlHelper.GetBooleanValue(pt.ShowEmptyItemsOnRows, false), + ShowEmptyColumn = OpenXmlHelper.GetBooleanValue(pt.ShowEmptyItemsOnColumns, false), ShowItems = OpenXmlHelper.GetBooleanValue(pt.DisplayItemLabels, true), - FieldListSortAscending = OpenXmlHelper.GetBooleanValue(pt.SortFieldsAtoZ, true), - PrintDrill = OpenXmlHelper.GetBooleanValue(pt.PrintExpandCollapsedButtons, true), - ItemPrintTitles = OpenXmlHelper.GetBooleanValue(pt.RepeatRowLabels, true), - FieldPrintTitles = OpenXmlHelper.GetBooleanValue(pt.PrintTitles, true), + FieldListSortAscending = OpenXmlHelper.GetBooleanValue(pt.SortFieldsAtoZ, false), + PrintDrill = OpenXmlHelper.GetBooleanValue(pt.PrintExpandCollapsedButtons, false), + ItemPrintTitles = OpenXmlHelper.GetBooleanValue(pt.RepeatRowLabels, false), + 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..8ee4606 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 = true; + pt.PreserveCellFormatting = false; + pt.ShowGrandTotalsColumns = true; + pt.ShowGrandTotalsRows = true; + pt.UseCustomListsForSorting = false; + pt.ShowExpandCollapseButtons = false; + pt.ShowContextualTooltips = false; + pt.DisplayCaptionsAndDropdowns = false; + pt.RepeatRowLabels = true; + pt.SaveSourceData = false; + pt.EnableShowDetails = false; + pt.ShowColumnHeaders = false; + pt.ShowRowHeaders = false; + + pt.MergeAndCenterWithLabels = true; // MergeItem + pt.RowLabelIndent = 12; // Indent + pt.FilterAreaOrder = XLFilterAreaOrder.OverThenDown; // PageOverThenDown + pt.FilterFieldsPageWrap = 14; // PageWrap + pt.ErrorValueReplacement = "error test"; // ErrorCaption + pt.EmptyCellReplacement = "empty test"; // MissingCaption + + pt.FilteredItemsInSubtotals = true; // Subtotal filtered page items + pt.AllowMultipleFilters = false; // MultipleFieldFilters + + pt.ShowPropertiesInTooltips = false; + pt.ClassicPivotTableLayout = true; + pt.ShowEmptyItemsOnRows = true; + pt.ShowEmptyItemsOnColumns = true; + pt.DisplayItemLabels = false; + 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.OverThenDown, 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(true, ptassert.AutofitColumns, "AutofitColumns save failure"); + Assert.AreEqual(false, ptassert.PreserveCellFormatting, "PreserveCellFormatting save failure"); + Assert.AreEqual(true, ptassert.ShowGrandTotalsRows, "ShowGrandTotalsRows save failure"); + Assert.AreEqual(true, ptassert.ShowGrandTotalsColumns, "ShowGrandTotalsColumns save failure"); + Assert.AreEqual(true, ptassert.FilteredItemsInSubtotals, "FilteredItemsInSubtotals save failure"); + Assert.AreEqual(false, 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(false, 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(false, ptassert.DisplayItemLabels, "DisplayItemLabels save failure"); + Assert.AreEqual(true, ptassert.SortFieldsAtoZ, "SortFieldsAtoZ save failure"); + Assert.AreEqual(true, ptassert.PrintExpandCollapsedButtons, "PrintExpandCollapsedButtons save failure"); + Assert.AreEqual(true, 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..c0f266b 100644 --- a/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx +++ b/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx Binary files differ