diff --git a/ClosedXML/Excel/PivotTables/IXLPivotField.cs b/ClosedXML/Excel/PivotTables/IXLPivotField.cs index cc6fe91..9571eb0 100644 --- a/ClosedXML/Excel/PivotTables/IXLPivotField.cs +++ b/ClosedXML/Excel/PivotTables/IXLPivotField.cs @@ -24,12 +24,14 @@ { String SourceName { get; } String CustomName { get; set; } + String SubtotalCaption { get; set; } List Subtotals { get; } Boolean IncludeNewItemsInFilter { get; set; } - XLPivotLayout Layout { get; set; } - Boolean SubtotalsAtTop { get; set; } + Boolean Outline { get; set; } + Boolean Compact { get; set; } + Boolean? SubtotalsAtTop { get; set; } Boolean RepeatItemLabels { get; set; } Boolean InsertBlankLines { get; set; } Boolean ShowBlankItems { get; set; } @@ -38,6 +40,7 @@ XLPivotSortType SortType { get; set; } IXLPivotField SetCustomName(String value); + IXLPivotField SetSubtotalCaption(String value); IXLPivotField AddSubtotal(XLSubtotalFunction value); IXLPivotField SetIncludeNewItemsInFilter(); IXLPivotField SetIncludeNewItemsInFilter(Boolean value); diff --git a/ClosedXML/Excel/PivotTables/XLPivotField.cs b/ClosedXML/Excel/PivotTables/XLPivotField.cs index 64a0b99..dbdfac9 100644 --- a/ClosedXML/Excel/PivotTables/XLPivotField.cs +++ b/ClosedXML/Excel/PivotTables/XLPivotField.cs @@ -13,6 +13,7 @@ Subtotals = new List(); SelectedValues = new List(); SortType = XLPivotSortType.Default; + SetExcelDefaults(); } public String SourceName { get; private set; } @@ -20,6 +21,10 @@ public IXLPivotField SetCustomName(String value) { CustomName = value; return this; } + public String SubtotalCaption { get; set; } + + public IXLPivotField SetSubtotalCaption(String value) { SubtotalCaption = value; return this; } + public List Subtotals { get; private set; } public IXLPivotField AddSubtotal(XLSubtotalFunction value) { Subtotals.Add(value); return this; } @@ -30,11 +35,22 @@ public IXLPivotField SetIncludeNewItemsInFilter(Boolean value) { IncludeNewItemsInFilter = value; return this; } - public XLPivotLayout Layout { get; set; } + public bool Outline { get; set; } + public bool Compact { get; set; } - public IXLPivotField SetLayout(XLPivotLayout value) { Layout = value; return this; } + public IXLPivotField SetLayout(XLPivotLayout value) + { + Compact = false; + Outline = false; + switch (value) + { + case XLPivotLayout.Compact: Compact = true; break; + case XLPivotLayout.Outline: Outline = true; break; + } + return this; + } - public Boolean SubtotalsAtTop { get; set; } + public Boolean? SubtotalsAtTop { get; set; } public IXLPivotField SetSubtotalsAtTop() { SubtotalsAtTop = true; return this; } @@ -80,5 +96,18 @@ SelectedValues.Add(value); return this; } + + private void SetExcelDefaults() + { + IncludeNewItemsInFilter = false; + Outline = true; + Compact = true; + InsertBlankLines = false; + ShowBlankItems = true; + InsertPageBreaks = false; + RepeatItemLabels = false; + SubtotalsAtTop = true; + Collapsed = false; + } } } diff --git a/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/Excel/XLWorkbook_Load.cs index 0899294..ec7ae2f 100644 --- a/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/Excel/XLWorkbook_Load.cs @@ -577,40 +577,17 @@ continue; var cacheField = pivotTableCacheDefinitionPart.PivotCacheDefinition.CacheFields.ElementAt(rf.Index.Value) as CacheField; - if (pf.Name != null) - pivotField = pt.RowLabels.Add(pf.Name.Value); - else if (cacheField.Name != null) - pivotField = pt.RowLabels.Add(cacheField.Name.Value); + if (cacheField.Name != null) + pivotField = pf.Name != null + ? pt.RowLabels.Add(cacheField.Name, pf.Name.Value) + : pt.RowLabels.Add(cacheField.Name.Value); else continue; if (pivotField != null) { - if (pf.AverageSubTotal != null) - pivotField.AddSubtotal(XLSubtotalFunction.Average); - if (pf.CountASubtotal != null) - pivotField.AddSubtotal(XLSubtotalFunction.Count); - if (pf.CountSubtotal != null) - pivotField.AddSubtotal(XLSubtotalFunction.CountNumbers); - if (pf.MaxSubtotal != null) - pivotField.AddSubtotal(XLSubtotalFunction.Maximum); - if (pf.MinSubtotal != null) - pivotField.AddSubtotal(XLSubtotalFunction.Minimum); - if (pf.ApplyStandardDeviationPInSubtotal != null) - pivotField.AddSubtotal(XLSubtotalFunction.PopulationStandardDeviation); - if (pf.ApplyVariancePInSubtotal != null) - pivotField.AddSubtotal(XLSubtotalFunction.PopulationVariance); - if (pf.ApplyProductInSubtotal != null) - pivotField.AddSubtotal(XLSubtotalFunction.Product); - if (pf.ApplyStandardDeviationInSubtotal != null) - pivotField.AddSubtotal(XLSubtotalFunction.StandardDeviation); - if (pf.SumSubtotal != null) - pivotField.AddSubtotal(XLSubtotalFunction.Sum); - if (pf.ApplyVarianceInSubtotal != null) - pivotField.AddSubtotal(XLSubtotalFunction.Variance); - 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(); + LoadFieldOptions(pf, pivotField); + LoadSubtotals(pf, pivotField); if (pf.SortType != null) { @@ -637,40 +614,17 @@ continue; var cacheField = pivotTableCacheDefinitionPart.PivotCacheDefinition.CacheFields.ElementAt(cf.Index.Value) as CacheField; - if (pf.Name != null) - pivotField = pt.ColumnLabels.Add(pf.Name.Value); - else if (cacheField.Name != null) - pivotField = pt.ColumnLabels.Add(cacheField.Name.Value); + if (cacheField.Name != null) + pivotField = pf.Name != null + ? pt.ColumnLabels.Add(cacheField.Name, pf.Name.Value) + : pt.ColumnLabels.Add(cacheField.Name.Value); else continue; if (pivotField != null) { - if (pf.AverageSubTotal != null) - pivotField.AddSubtotal(XLSubtotalFunction.Average); - if (pf.CountASubtotal != null) - pivotField.AddSubtotal(XLSubtotalFunction.Count); - if (pf.CountSubtotal != null) - pivotField.AddSubtotal(XLSubtotalFunction.CountNumbers); - if (pf.MaxSubtotal != null) - pivotField.AddSubtotal(XLSubtotalFunction.Maximum); - if (pf.MinSubtotal != null) - pivotField.AddSubtotal(XLSubtotalFunction.Minimum); - if (pf.ApplyStandardDeviationPInSubtotal != null) - pivotField.AddSubtotal(XLSubtotalFunction.PopulationStandardDeviation); - if (pf.ApplyVariancePInSubtotal != null) - pivotField.AddSubtotal(XLSubtotalFunction.PopulationVariance); - if (pf.ApplyProductInSubtotal != null) - pivotField.AddSubtotal(XLSubtotalFunction.Product); - if (pf.ApplyStandardDeviationInSubtotal != null) - pivotField.AddSubtotal(XLSubtotalFunction.StandardDeviation); - if (pf.SumSubtotal != null) - pivotField.AddSubtotal(XLSubtotalFunction.Sum); - if (pf.ApplyVarianceInSubtotal != null) - pivotField.AddSubtotal(XLSubtotalFunction.Variance); - 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(); + LoadFieldOptions(pf, pivotField); + LoadSubtotals(pf, pivotField); if (pf.SortType != null) { @@ -803,6 +757,53 @@ #endregion } + private static void LoadFieldOptions(PivotField pf, IXLPivotField pivotField) + { + if (pf.SubtotalCaption != null) pivotField.SubtotalCaption = pf.SubtotalCaption; + if (pf.IncludeNewItemsInFilter != null) pivotField.IncludeNewItemsInFilter = pf.IncludeNewItemsInFilter.Value; + if (pf.Outline != null) pivotField.Outline = pf.Outline.Value; + if (pf.Compact != null) pivotField.Compact = pf.Compact.Value; + if (pf.InsertBlankRow != null) pivotField.InsertBlankLines = pf.InsertBlankRow.Value; + if (pf.ShowAll != null) pivotField.ShowBlankItems = pf.ShowAll.Value; + if (pf.InsertPageBreak != null) pivotField.InsertPageBreaks = pf.InsertPageBreak.Value; + if (pf.SubtotalTop != null) pivotField.SubtotalsAtTop = pf.SubtotalTop.Value; + if (pf.AllDrilled != null) pivotField.Collapsed = !pf.AllDrilled.Value; + + var pivotFieldExtensionList = pf.GetFirstChild(); + var pivotFieldExtension = pivotFieldExtensionList?.GetFirstChild(); + var field2010 = pivotFieldExtension?.GetFirstChild(); + if (field2010?.FillDownLabels != null) pivotField.RepeatItemLabels = field2010.FillDownLabels.Value; + } + + private static void LoadSubtotals(PivotField pf, IXLPivotField pivotField) + { + if (pf.AverageSubTotal != null) + pivotField.AddSubtotal(XLSubtotalFunction.Average); + if (pf.CountASubtotal != null) + pivotField.AddSubtotal(XLSubtotalFunction.Count); + if (pf.CountSubtotal != null) + pivotField.AddSubtotal(XLSubtotalFunction.CountNumbers); + if (pf.MaxSubtotal != null) + pivotField.AddSubtotal(XLSubtotalFunction.Maximum); + if (pf.MinSubtotal != null) + pivotField.AddSubtotal(XLSubtotalFunction.Minimum); + if (pf.ApplyStandardDeviationPInSubtotal != null) + pivotField.AddSubtotal(XLSubtotalFunction.PopulationStandardDeviation); + if (pf.ApplyVariancePInSubtotal != null) + pivotField.AddSubtotal(XLSubtotalFunction.PopulationVariance); + if (pf.ApplyProductInSubtotal != null) + pivotField.AddSubtotal(XLSubtotalFunction.Product); + if (pf.ApplyStandardDeviationInSubtotal != null) + pivotField.AddSubtotal(XLSubtotalFunction.StandardDeviation); + if (pf.SumSubtotal != null) + pivotField.AddSubtotal(XLSubtotalFunction.Sum); + if (pf.ApplyVarianceInSubtotal != null) + pivotField.AddSubtotal(XLSubtotalFunction.Variance); + 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(); + } + private void LoadDrawings(WorksheetPart wsPart, IXLWorksheet ws) { if (wsPart.DrawingsPart != null) diff --git a/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/Excel/XLWorkbook_Save.cs index edde683..434a030 100644 --- a/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/Excel/XLWorkbook_Save.cs @@ -2020,6 +2020,16 @@ { xlpf.CustomName = field.CustomName; xlpf.SortType = field.SortType; + xlpf.SubtotalCaption = field.SubtotalCaption; + xlpf.IncludeNewItemsInFilter = field.IncludeNewItemsInFilter; + xlpf.Outline = field.Outline; + xlpf.Compact = field.Compact; + xlpf.SubtotalsAtTop = field.SubtotalsAtTop; + xlpf.RepeatItemLabels = field.RepeatItemLabels; + xlpf.InsertBlankLines = field.InsertBlankLines; + xlpf.ShowBlankItems = field.ShowBlankItems; + xlpf.InsertPageBreaks = field.InsertPageBreaks; + xlpf.Collapsed = field.Collapsed; xlpf.Subtotals.AddRange(field.Subtotals); } @@ -2303,13 +2313,30 @@ { var ptfi = pti.Fields[xlpf.SourceName]; IXLPivotField labelOrFilterField = null; - var pf = new PivotField { ShowAll = false, Name = xlpf.CustomName }; - + var pf = new PivotField + { + Name = xlpf.CustomName, + IncludeNewItemsInFilter = OpenXmlHelper.GetBooleanValue(xlpf.IncludeNewItemsInFilter, false), + InsertBlankRow = OpenXmlHelper.GetBooleanValue(xlpf.InsertBlankLines, false), + ShowAll = OpenXmlHelper.GetBooleanValue(xlpf.ShowBlankItems, true), + InsertPageBreak = OpenXmlHelper.GetBooleanValue(xlpf.InsertPageBreaks, false), + AllDrilled = OpenXmlHelper.GetBooleanValue(xlpf.Collapsed, false), + }; + if (!string.IsNullOrWhiteSpace(xlpf.SubtotalCaption)) + { + pf.SubtotalCaption = xlpf.SubtotalCaption; + } + if (pt.ClassicPivotTableLayout) { pf.Outline = false; pf.Compact = false; } + else + { + pf.Outline = OpenXmlHelper.GetBooleanValue(xlpf.Outline, true); + pf.Compact = OpenXmlHelper.GetBooleanValue(xlpf.Compact, true); + } if (xlpf.SortType != XLPivotSortType.Default) { @@ -2328,11 +2355,15 @@ break; case XLPivotSubtotals.AtTop: - pf.DefaultSubtotal = true; - pf.SubtotalTop = true; + // at top is by default break; } + if (xlpf.SubtotalsAtTop.HasValue) + { + pf.SubtotalTop = OpenXmlHelper.GetBooleanValue(xlpf.SubtotalsAtTop.Value, true); + } + if (pt.RowLabels.Contains(xlpf.SourceName)) { labelOrFilterField = pt.RowLabels.Get(xlpf.SourceName); @@ -2501,6 +2532,24 @@ fieldItems.Count = Convert.ToUInt32(fieldItems.Count()); pf.AppendChild(fieldItems); } + + #region Excel 2010 Features + if (xlpf.RepeatItemLabels) + { + var pivotFieldExtensionList = new PivotFieldExtensionList(); + pivotFieldExtensionList.RemoveNamespaceDeclaration("x"); + var pivotFieldExtension = new PivotFieldExtension { Uri = "{2946ED86-A175-432a-8AC1-64E0C546D7DE}" }; + pivotFieldExtension.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main"); + + var pivotField2 = new DocumentFormat.OpenXml.Office2010.Excel.PivotField { FillDownLabels = true }; + + pivotFieldExtension.AppendChild(pivotField2); + + pivotFieldExtensionList.AppendChild(pivotFieldExtension); + pf.AppendChild(pivotFieldExtensionList); + } + #endregion Excel 2010 Features + pivotFields.AppendChild(pf); } diff --git a/ClosedXML_Examples/app.config b/ClosedXML_Examples/app.config index 21ac8f3..86fcc8b 100644 --- a/ClosedXML_Examples/app.config +++ b/ClosedXML_Examples/app.config @@ -1,6 +1,6 @@ - - - - - - + + + + + + diff --git a/ClosedXML_Sandbox/app.config b/ClosedXML_Sandbox/app.config index 21ac8f3..86fcc8b 100644 --- a/ClosedXML_Sandbox/app.config +++ b/ClosedXML_Sandbox/app.config @@ -1,6 +1,6 @@ - - - - - - + + + + + + diff --git a/ClosedXML_Tests/Excel/PivotTables/XLPivotTableTests.cs b/ClosedXML_Tests/Excel/PivotTables/XLPivotTableTests.cs index 8ee4606..2cb1332 100644 --- a/ClosedXML_Tests/Excel/PivotTables/XLPivotTableTests.cs +++ b/ClosedXML_Tests/Excel/PivotTables/XLPivotTableTests.cs @@ -144,5 +144,75 @@ } } } + + [TestCase(true)] + [TestCase(false)] + public void PivotFieldOptionsSaveTest(bool withDefaults) + { + 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 dataRange = ws.Range(ws.Range(1, 1, 1, 3).FirstCell(), table.DataRange.LastCell()); + + var ptSheet = wb.Worksheets.Add("pvtFieldOptionsTest"); + var pt = ptSheet.PivotTables.AddNew("pvtFieldOptionsTest", ptSheet.Cell(1, 1), dataRange); + + var field = pt.RowLabels.Add("Name") + .SetSubtotalCaption("Test caption") + .SetCustomName("Test name"); + SetFieldOptions(field, withDefaults); + + pt.ColumnLabels.Add("Month"); + pt.Values.Add("NumberOfOrders").SetSummaryFormula(XLPivotSummary.Sum); + //wb.SaveAs(".\\pivot.xlsx.zip", true); + + using (var ms = new MemoryStream()) + { + wb.SaveAs(ms, true); + + ms.Position = 0; + + using (var wbassert = new XLWorkbook(ms)) + { + var wsassert = wbassert.Worksheet("pvtFieldOptionsTest"); + var ptassert = wsassert.PivotTable("pvtFieldOptionsTest"); + var pfassert = ptassert.RowLabels.Get("Name"); + Assert.AreNotEqual(null, pfassert, "name save failure"); + Assert.AreEqual("Test caption", pfassert.SubtotalCaption, "SubtotalCaption save failure"); + Assert.AreEqual("Test name", pfassert.CustomName, "CustomName save failure"); + AssertFieldOptions(pfassert, withDefaults); + } + } + } + } + + private static void SetFieldOptions(IXLPivotField field, bool withDefaults) + { + field.SubtotalsAtTop = !withDefaults; + field.ShowBlankItems = !withDefaults; + field.Outline = !withDefaults; + field.Compact = !withDefaults; + field.Collapsed = withDefaults; + field.InsertBlankLines = withDefaults; + field.RepeatItemLabels = withDefaults; + field.InsertPageBreaks = withDefaults; + field.IncludeNewItemsInFilter = withDefaults; + } + + private static void AssertFieldOptions(IXLPivotField field, bool withDefaults) + { + Assert.AreEqual(!withDefaults, field.SubtotalsAtTop, "SubtotalsAtTop save failure"); + Assert.AreEqual(!withDefaults, field.ShowBlankItems, "ShowBlankItems save failure"); + Assert.AreEqual(!withDefaults, field.Outline, "Outline save failure"); + Assert.AreEqual(!withDefaults, field.Compact, "Compact save failure"); + Assert.AreEqual(withDefaults, field.Collapsed, "Collapsed save failure"); + Assert.AreEqual(withDefaults, field.InsertBlankLines, "InsertBlankLines save failure"); + Assert.AreEqual(withDefaults, field.RepeatItemLabels, "RepeatItemLabels save failure"); + Assert.AreEqual(withDefaults, field.InsertPageBreaks, "InsertPageBreaks save failure"); + Assert.AreEqual(withDefaults, field.IncludeNewItemsInFilter, "IncludeNewItemsInFilter save failure"); + } } } diff --git a/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx b/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx index 4ad1e7f..7e934f9 100644 --- a/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx +++ b/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx Binary files differ