diff --git a/ClosedXML/Excel/PivotTables/XLPivotTable.cs b/ClosedXML/Excel/PivotTables/XLPivotTable.cs index e30c6e8..1d7895d 100644 --- a/ClosedXML/Excel/PivotTables/XLPivotTable.cs +++ b/ClosedXML/Excel/PivotTables/XLPivotTable.cs @@ -295,17 +295,35 @@ private void SetExcelDefaults() { EmptyCellReplacement = String.Empty; - AutofitColumns = true; - PreserveCellFormatting = 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_Save.cs b/ClosedXML/Excel/XLWorkbook_Save.cs index fac868a..30acfef 100644 --- a/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/Excel/XLWorkbook_Save.cs @@ -2154,24 +2154,24 @@ 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, false), - ColumnGrandTotals = OpenXmlHelper.GetBooleanValue(pt.ShowGrandTotalsColumns, false), + RowGrandTotals = OpenXmlHelper.GetBooleanValue(pt.ShowGrandTotalsRows, true), + ColumnGrandTotals = OpenXmlHelper.GetBooleanValue(pt.ShowGrandTotalsColumns, true), SubtotalHiddenItems = OpenXmlHelper.GetBooleanValue(pt.FilteredItemsInSubtotals, false), - MultipleFieldFilters = OpenXmlHelper.GetBooleanValue(pt.AllowMultipleFilters, false), + MultipleFieldFilters = OpenXmlHelper.GetBooleanValue(pt.AllowMultipleFilters, true), CustomListSort = OpenXmlHelper.GetBooleanValue(pt.UseCustomListsForSorting, true), ShowDrill = OpenXmlHelper.GetBooleanValue(pt.ShowExpandCollapseButtons, true), ShowDataTips = OpenXmlHelper.GetBooleanValue(pt.ShowContextualTooltips, true), - ShowMemberPropertyTips = OpenXmlHelper.GetBooleanValue(pt.ShowPropertiesInTooltips, false), + ShowMemberPropertyTips = OpenXmlHelper.GetBooleanValue(pt.ShowPropertiesInTooltips, true), ShowHeaders = OpenXmlHelper.GetBooleanValue(pt.DisplayCaptionsAndDropdowns, true), GridDropZones = OpenXmlHelper.GetBooleanValue(pt.ClassicPivotTableLayout, false), ShowEmptyRow = OpenXmlHelper.GetBooleanValue(pt.ShowEmptyItemsOnRows, false), ShowEmptyColumn = OpenXmlHelper.GetBooleanValue(pt.ShowEmptyItemsOnColumns, false), - ShowItems = OpenXmlHelper.GetBooleanValue(pt.DisplayItemLabels, false), + ShowItems = OpenXmlHelper.GetBooleanValue(pt.DisplayItemLabels, true), FieldListSortAscending = OpenXmlHelper.GetBooleanValue(pt.SortFieldsAtoZ, false), PrintDrill = OpenXmlHelper.GetBooleanValue(pt.PrintExpandCollapsedButtons, false), - ItemPrintTitles = OpenXmlHelper.GetBooleanValue(pt.RepeatRowLabels, true), + 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 0066287..8ee4606 100644 --- a/ClosedXML_Tests/Excel/PivotTables/XLPivotTableTests.cs +++ b/ClosedXML_Tests/Excel/PivotTables/XLPivotTableTests.cs @@ -49,15 +49,15 @@ pt.ColumnHeaderCaption = "clmn header"; pt.RowHeaderCaption = "row header"; - pt.AutofitColumns = false; + pt.AutofitColumns = true; pt.PreserveCellFormatting = false; - pt.ShowGrandTotalsColumns = false; - pt.ShowGrandTotalsRows = false; + pt.ShowGrandTotalsColumns = true; + pt.ShowGrandTotalsRows = true; pt.UseCustomListsForSorting = false; pt.ShowExpandCollapseButtons = false; pt.ShowContextualTooltips = false; pt.DisplayCaptionsAndDropdowns = false; - pt.RepeatRowLabels = false; + pt.RepeatRowLabels = true; pt.SaveSourceData = false; pt.EnableShowDetails = false; pt.ShowColumnHeaders = false; @@ -65,19 +65,19 @@ pt.MergeAndCenterWithLabels = true; // MergeItem pt.RowLabelIndent = 12; // Indent - pt.FilterAreaOrder = XLFilterAreaOrder.DownThenOver; // PageOverThenDown + 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 = true; // MultipleFieldFilters + pt.AllowMultipleFilters = false; // MultipleFieldFilters - pt.ShowPropertiesInTooltips = true; + pt.ShowPropertiesInTooltips = false; pt.ClassicPivotTableLayout = true; pt.ShowEmptyItemsOnRows = true; pt.ShowEmptyItemsOnColumns = true; - pt.DisplayItemLabels = true; + pt.DisplayItemLabels = false; pt.SortFieldsAtoZ = true; pt.PrintExpandCollapsedButtons = true; @@ -106,28 +106,28 @@ 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(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(false, ptassert.AutofitColumns, "AutofitColumns save failure"); + Assert.AreEqual(true, 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.ShowGrandTotalsRows, "ShowGrandTotalsRows save failure"); + Assert.AreEqual(true, 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.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.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(false, 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.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"); diff --git a/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx b/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx index 3137b74..c0f266b 100644 --- a/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx +++ b/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx Binary files differ