diff --git a/ClosedXML/Excel/PivotTables/IXLPivotField.cs b/ClosedXML/Excel/PivotTables/IXLPivotField.cs index 8e69e8b..011e222 100644 --- a/ClosedXML/Excel/PivotTables/IXLPivotField.cs +++ b/ClosedXML/Excel/PivotTables/IXLPivotField.cs @@ -36,6 +36,7 @@ Boolean InsertBlankLines { get; set; } Boolean ShowBlankItems { get; set; } Boolean InsertPageBreaks { get; set; } + Boolean Collapsed { get; set; } IXLPivotField SetCustomName(String value); @@ -48,6 +49,7 @@ IXLPivotField SetInsertBlankLines(); IXLPivotField SetInsertBlankLines(Boolean value); IXLPivotField SetShowBlankItems(); IXLPivotField SetShowBlankItems(Boolean value); IXLPivotField SetInsertPageBreaks(); IXLPivotField SetInsertPageBreaks(Boolean value); + IXLPivotField SetCollapsed(); IXLPivotField SetCollapsed(Boolean value); List SharedStrings { get; set; } } diff --git a/ClosedXML/Excel/PivotTables/XLPivotField.cs b/ClosedXML/Excel/PivotTables/XLPivotField.cs index 84291fd..909db34 100644 --- a/ClosedXML/Excel/PivotTables/XLPivotField.cs +++ b/ClosedXML/Excel/PivotTables/XLPivotField.cs @@ -1,12 +1,9 @@ using System; using System.Collections.Generic; -using System.Linq; -using System.Text; namespace ClosedXML.Excel { - - public class XLPivotField: IXLPivotField + public class XLPivotField : IXLPivotField { public XLPivotField(string sourceName) { @@ -15,18 +12,60 @@ Subtotals = new List(); } - public String SourceName { get; private set; } - public String CustomName { get; set; } public IXLPivotField SetCustomName(String value) { CustomName = value; return this; } + public String SourceName { get; private set; } + public String CustomName { get; set; } - public List Subtotals { get; private set; } public IXLPivotField AddSubtotal(XLSubtotalFunction value) { Subtotals.Add(value); return this; } - public Boolean IncludeNewItemsInFilter { get; set; } public IXLPivotField SetIncludeNewItemsInFilter() { IncludeNewItemsInFilter = true; return this; } public IXLPivotField SetIncludeNewItemsInFilter(Boolean value) { IncludeNewItemsInFilter = value; return this; } + public IXLPivotField SetCustomName(String value) { CustomName = value; return this; } - public XLPivotLayout Layout { get; set; } public IXLPivotField SetLayout(XLPivotLayout value) { Layout = value; return this; } - public Boolean SubtotalsAtTop { get; set; } public IXLPivotField SetSubtotalsAtTop() { SubtotalsAtTop = true; return this; } public IXLPivotField SetSubtotalsAtTop(Boolean value) { SubtotalsAtTop = value; return this; } - public Boolean RepeatItemLabels { get; set; } public IXLPivotField SetRepeatItemLabels() { RepeatItemLabels = true; return this; } public IXLPivotField SetRepeatItemLabels(Boolean value) { RepeatItemLabels = value; return this; } - public Boolean InsertBlankLines { get; set; } public IXLPivotField SetInsertBlankLines() { InsertBlankLines = true; return this; } public IXLPivotField SetInsertBlankLines(Boolean value) { InsertBlankLines = value; return this; } - public Boolean ShowBlankItems { get; set; } public IXLPivotField SetShowBlankItems() { ShowBlankItems = true; return this; } public IXLPivotField SetShowBlankItems(Boolean value) { ShowBlankItems = value; return this; } - public Boolean InsertPageBreaks { get; set; } public IXLPivotField SetInsertPageBreaks() { InsertPageBreaks = true; return this; } public IXLPivotField SetInsertPageBreaks(Boolean value) { InsertPageBreaks = value; return this; } + public List Subtotals { get; private set; } + + public IXLPivotField AddSubtotal(XLSubtotalFunction value) { Subtotals.Add(value); return this; } + + public Boolean IncludeNewItemsInFilter { get; set; } + + public IXLPivotField SetIncludeNewItemsInFilter() { IncludeNewItemsInFilter = true; return this; } + + public IXLPivotField SetIncludeNewItemsInFilter(Boolean value) { IncludeNewItemsInFilter = value; return this; } + + public XLPivotLayout Layout { get; set; } + + public IXLPivotField SetLayout(XLPivotLayout value) { Layout = value; return this; } + + public Boolean SubtotalsAtTop { get; set; } + + public IXLPivotField SetSubtotalsAtTop() { SubtotalsAtTop = true; return this; } + + public IXLPivotField SetSubtotalsAtTop(Boolean value) { SubtotalsAtTop = value; return this; } + + public Boolean RepeatItemLabels { get; set; } + + public IXLPivotField SetRepeatItemLabels() { RepeatItemLabels = true; return this; } + + public IXLPivotField SetRepeatItemLabels(Boolean value) { RepeatItemLabels = value; return this; } + + public Boolean InsertBlankLines { get; set; } + + public IXLPivotField SetInsertBlankLines() { InsertBlankLines = true; return this; } + + public IXLPivotField SetInsertBlankLines(Boolean value) { InsertBlankLines = value; return this; } + + public Boolean ShowBlankItems { get; set; } + + public IXLPivotField SetShowBlankItems() { ShowBlankItems = true; return this; } + + public IXLPivotField SetShowBlankItems(Boolean value) { ShowBlankItems = value; return this; } + + public Boolean InsertPageBreaks { get; set; } + + public IXLPivotField SetInsertPageBreaks() { InsertPageBreaks = true; return this; } + + public IXLPivotField SetInsertPageBreaks(Boolean value) { InsertPageBreaks = value; return this; } + + public Boolean Collapsed { get; set; } + + public IXLPivotField SetCollapsed() { Collapsed = true; return this; } + + public IXLPivotField SetCollapsed(Boolean value) { Collapsed = value; return this; } public List SharedStrings { get; set; } } diff --git a/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/Excel/XLWorkbook_Load.cs index 250ce67..d62bb95 100644 --- a/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/Excel/XLWorkbook_Load.cs @@ -438,7 +438,20 @@ && pivotTableCacheDefinitionPart.PivotCacheDefinition.CacheSource.WorksheetSource != null) { // TODO: Implement other sources besides worksheetSource (e.g. Table source?) - source = ws.Workbook.Range(pivotTableCacheDefinitionPart.PivotCacheDefinition.CacheSource.WorksheetSource.Name.Value); + // But for now assume names and references point directly to a range + var wss = pivotTableCacheDefinitionPart.PivotCacheDefinition.CacheSource.WorksheetSource; + string rangeAddress = string.Empty; + if (wss.Name != null) + rangeAddress = wss.Name.Value; + else + { + var sourceSheet = wss.Sheet == null ? ws : this.Worksheet(wss.Sheet.Value); + rangeAddress = sourceSheet.Range(wss.Reference.Value).RangeAddress.ToStringRelative(true); + } + + source = this.Range(rangeAddress); + if (source == null) + continue; } if (target != null && source != null) @@ -484,12 +497,32 @@ { foreach (var rf in pivotTableDefinition.RowFields.Cast()) { - if (rf.Index.Value == -2) - pt.RowLabels.Add(XLConstants.PivotTableValuesSentinalLabel); - else if (rf.Index < pivotTableDefinition.PivotFields.Count) + if (rf.Index < pivotTableDefinition.PivotFields.Count) { - var pf = pivotTableDefinition.PivotFields.ElementAt(rf.Index.Value) as PivotField; - if (pf != null && pf.Name != null) pt.RowLabels.Add(pf.Name.Value); + IXLPivotField pivotField = null; + if (rf.Index.Value == -2) + pivotField = pt.RowLabels.Add(XLConstants.PivotTableValuesSentinalLabel); + else + { + var pf = pivotTableDefinition.PivotFields.ElementAt(rf.Index.Value) as PivotField; + if (pf == null) + 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); + else + continue; + + if (pivotField != null) + { + 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(); + } + } } } } @@ -499,12 +532,29 @@ { foreach (var cf in pivotTableDefinition.ColumnFields.Cast()) { + IXLPivotField pivotField = null; if (cf.Index.Value == -2) - pt.ColumnLabels.Add(XLConstants.PivotTableValuesSentinalLabel); + pivotField = pt.ColumnLabels.Add(XLConstants.PivotTableValuesSentinalLabel); else if (cf.Index < pivotTableDefinition.PivotFields.Count) { var pf = pivotTableDefinition.PivotFields.ElementAt(cf.Index.Value) as PivotField; - if (pf != null && pf.Name != null) pt.ColumnLabels.Add(pf.Name.Value); + if (pf == null) + 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); + else + continue; + + if (pivotField != null) + { + 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(); + } } } } @@ -514,34 +564,44 @@ { foreach (var df in pivotTableDefinition.DataFields.Cast()) { + IXLPivotValue pivotValue = null; if ((int)df.Field.Value == -2) - pt.Values.Add(XLConstants.PivotTableValuesSentinalLabel); + pivotValue = pt.Values.Add(XLConstants.PivotTableValuesSentinalLabel); else if (df.Field.Value < pivotTableDefinition.PivotFields.Count) { var pf = pivotTableDefinition.PivotFields.ElementAt((int)df.Field.Value) as PivotField; - if (pf != null && pf.Name != null) + if (pf == null) + continue; + + var cacheField = pivotTableCacheDefinitionPart.PivotCacheDefinition.CacheFields.ElementAt((int)df.Field.Value) as CacheField; + + if (pf.Name != null) + pivotValue = pt.Values.Add(pf.Name.Value, df.Name.Value); + else if (cacheField.Name != null) + pivotValue = pt.Values.Add(cacheField.Name.Value, df.Name.Value); + else + continue; + + if (df.NumberFormatId != null) pivotValue.NumberFormat.SetNumberFormatId((int)df.NumberFormatId.Value); + if (df.Subtotal != null) pivotValue = pivotValue.SetSummaryFormula(df.Subtotal.Value.ToClosedXml()); + if (df.ShowDataAs != null) { - var pv = pt.Values.Add(pf.Name.Value, df.Name.Value); - if (df.NumberFormatId != null) pv.NumberFormat.SetNumberFormatId((int)df.NumberFormatId.Value); - if (df.Subtotal != null) pv = pv.SetSummaryFormula(df.Subtotal.Value.ToClosedXml()); - if (df.ShowDataAs != null) - { - var calculation = pv.Calculation; - calculation = df.ShowDataAs.Value.ToClosedXml(); - pv = pv.SetCalculation(calculation); - } - if (df.BaseField != null) - { - var col = pt.SourceRange.Column(df.BaseField.Value + 1); + var calculation = pivotValue.Calculation; + calculation = df.ShowDataAs.Value.ToClosedXml(); + pivotValue = pivotValue.SetCalculation(calculation); + } - var items = col.CellsUsed() - .Select(c => c.Value) - .Skip(1) // Skip header column - .Distinct().ToList(); + if (df.BaseField != null) + { + var col = pt.SourceRange.Column(df.BaseField.Value + 1); - pv.BaseField = col.FirstCell().GetValue(); - if (df.BaseItem != null) pv.BaseItem = items[(int)df.BaseItem.Value].ToString(); - } + var items = col.CellsUsed() + .Select(c => c.Value) + .Skip(1) // Skip header column + .Distinct().ToList(); + + pivotValue.BaseField = col.FirstCell().GetValue(); + if (df.BaseItem != null) pivotValue.BaseItem = items[(int)df.BaseItem.Value].ToString(); } } } diff --git a/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/Excel/XLWorkbook_Save.cs index 47e6302..9a99f83 100644 --- a/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/Excel/XLWorkbook_Save.cs @@ -2102,14 +2102,17 @@ foreach (var xlpf in pt.Fields) { + IXLPivotField labelField = null; var pf = new PivotField { ShowAll = false, Name = xlpf.CustomName }; if (pt.RowLabels.Any(p => p.SourceName == xlpf.SourceName)) { + labelField = pt.RowLabels.Single(p => p.SourceName == xlpf.SourceName); pf.Axis = PivotTableAxisValues.AxisRow; } else if (pt.ColumnLabels.Any(p => p.SourceName == xlpf.SourceName)) { + labelField = pt.ColumnLabels.Single(p => p.SourceName == xlpf.SourceName); pf.Axis = PivotTableAxisValues.AxisColumn; } else if (pt.ReportFilters.Any(p => p.SourceName == xlpf.SourceName)) @@ -2129,7 +2132,10 @@ { for (uint i = 0; i < xlpf.SharedStrings.Count; i++) { - fieldItems.AppendChild(new Item { Index = i }); + var item = new Item { Index = i }; + if (labelField != null && labelField.Collapsed) + item.HideDetails = BooleanValue.FromBoolean(false); + fieldItems.AppendChild(item); } } diff --git a/ClosedXML_Examples/PivotTables/PivotTables.cs b/ClosedXML_Examples/PivotTables/PivotTables.cs index 92d28a7..61692b9 100644 --- a/ClosedXML_Examples/PivotTables/PivotTables.cs +++ b/ClosedXML_Examples/PivotTables/PivotTables.cs @@ -102,8 +102,20 @@ pt.RowLabels.Add("Name"); pt.RowLabels.Add("Month"); - pt.Values.Add("NumberOfOrders").SetSummaryFormula(XLPivotSummary.Sum);//.NumberFormat.Format = "#0.00"; - pt.Values.Add("Quality").SetSummaryFormula(XLPivotSummary.Sum);//.NumberFormat.Format = "#0.00"; + pt.Values.Add("NumberOfOrders").SetSummaryFormula(XLPivotSummary.Sum); + pt.Values.Add("Quality").SetSummaryFormula(XLPivotSummary.Sum); + + + // Pivot table with collapsed fields + ptSheet = wb.Worksheets.Add("pvtCollapsedFields"); + pt = ptSheet.PivotTables.AddNew("pvtCollapsedFields", ptSheet.Cell(1, 1), dataRange); + + pt.RowLabels.Add("Name").SetCollapsed(); + pt.RowLabels.Add("Month").SetCollapsed(); + + pt.Values.Add("NumberOfOrders").SetSummaryFormula(XLPivotSummary.Sum); + pt.Values.Add("Quality").SetSummaryFormula(XLPivotSummary.Sum); + // Pivot table with a field both as a value and as a row/column/filter label ptSheet = wb.Worksheets.Add("pvtFieldAsValueAndLabel"); diff --git a/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx b/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx index c402ffc..5bc0db9 100644 --- a/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx +++ b/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx Binary files differ