diff --git a/ClosedXML/Excel/PivotTables/IXLPivotTable.cs b/ClosedXML/Excel/PivotTables/IXLPivotTable.cs index c3c6899..2afdd8c 100644 --- a/ClosedXML/Excel/PivotTables/IXLPivotTable.cs +++ b/ClosedXML/Excel/PivotTables/IXLPivotTable.cs @@ -106,8 +106,11 @@ } public enum XLFilterAreaOrder { DownThenOver, OverThenDown } + public enum XLItemsToRetain { Automatic, None, Max } + public enum XLPivotTableSourceType { Range, Table } + public interface IXLPivotTable { XLPivotTableTheme Theme { get; set; } @@ -126,7 +129,10 @@ String RowHeaderCaption { get; set; } IXLCell TargetCell { get; set; } + IXLRange SourceRange { get; set; } + IXLTable SourceTable { get; set; } + XLPivotTableSourceType SourceType { get; } IEnumerable SourceRangeFieldsAvailable { get; } @@ -167,46 +173,75 @@ Boolean EnableCellEditing { get; set; } IXLPivotTable SetName(String value); + IXLPivotTable SetTitle(String value); + IXLPivotTable SetDescription(String value); IXLPivotTable SetMergeAndCenterWithLabels(); IXLPivotTable SetMergeAndCenterWithLabels(Boolean value); + IXLPivotTable SetRowLabelIndent(Int32 value); + IXLPivotTable SetFilterAreaOrder(XLFilterAreaOrder value); + IXLPivotTable SetFilterFieldsPageWrap(Int32 value); + IXLPivotTable SetErrorValueReplacement(String value); + IXLPivotTable SetEmptyCellReplacement(String value); + IXLPivotTable SetAutofitColumns(); IXLPivotTable SetAutofitColumns(Boolean value); + IXLPivotTable SetPreserveCellFormatting(); IXLPivotTable SetPreserveCellFormatting(Boolean value); IXLPivotTable SetShowGrandTotalsRows(); IXLPivotTable SetShowGrandTotalsRows(Boolean value); + IXLPivotTable SetShowGrandTotalsColumns(); IXLPivotTable SetShowGrandTotalsColumns(Boolean value); + IXLPivotTable SetFilteredItemsInSubtotals(); IXLPivotTable SetFilteredItemsInSubtotals(Boolean value); + IXLPivotTable SetAllowMultipleFilters(); IXLPivotTable SetAllowMultipleFilters(Boolean value); + IXLPivotTable SetUseCustomListsForSorting(); IXLPivotTable SetUseCustomListsForSorting(Boolean value); IXLPivotTable SetShowExpandCollapseButtons(); IXLPivotTable SetShowExpandCollapseButtons(Boolean value); + IXLPivotTable SetShowContextualTooltips(); IXLPivotTable SetShowContextualTooltips(Boolean value); + IXLPivotTable SetShowPropertiesInTooltips(); IXLPivotTable SetShowPropertiesInTooltips(Boolean value); + IXLPivotTable SetDisplayCaptionsAndDropdowns(); IXLPivotTable SetDisplayCaptionsAndDropdowns(Boolean value); + IXLPivotTable SetClassicPivotTableLayout(); IXLPivotTable SetClassicPivotTableLayout(Boolean value); + IXLPivotTable SetShowValuesRow(); IXLPivotTable SetShowValuesRow(Boolean value); + IXLPivotTable SetShowEmptyItemsOnRows(); IXLPivotTable SetShowEmptyItemsOnRows(Boolean value); + IXLPivotTable SetShowEmptyItemsOnColumns(); IXLPivotTable SetShowEmptyItemsOnColumns(Boolean value); + IXLPivotTable SetDisplayItemLabels(); IXLPivotTable SetDisplayItemLabels(Boolean value); + IXLPivotTable SetSortFieldsAtoZ(); IXLPivotTable SetSortFieldsAtoZ(Boolean value); IXLPivotTable SetPrintExpandCollapsedButtons(); IXLPivotTable SetPrintExpandCollapsedButtons(Boolean value); + IXLPivotTable SetRepeatRowLabels(); IXLPivotTable SetRepeatRowLabels(Boolean value); + IXLPivotTable SetPrintTitles(); IXLPivotTable SetPrintTitles(Boolean value); IXLPivotTable SetSaveSourceData(); IXLPivotTable SetSaveSourceData(Boolean value); + IXLPivotTable SetEnableShowDetails(); IXLPivotTable SetEnableShowDetails(Boolean value); + IXLPivotTable SetRefreshDataOnOpen(); IXLPivotTable SetRefreshDataOnOpen(Boolean value); + IXLPivotTable SetItemsToRetainPerField(XLItemsToRetain value); + IXLPivotTable SetEnableCellEditing(); IXLPivotTable SetEnableCellEditing(Boolean value); IXLPivotTable SetColumnHeaderCaption(String value); + IXLPivotTable SetRowHeaderCaption(String value); Boolean ShowRowHeaders { get; set; } @@ -218,14 +253,19 @@ Boolean InsertBlankLines { set; } IXLPivotTable SetShowRowHeaders(); IXLPivotTable SetShowRowHeaders(Boolean value); + IXLPivotTable SetShowColumnHeaders(); IXLPivotTable SetShowColumnHeaders(Boolean value); + IXLPivotTable SetShowRowStripes(); IXLPivotTable SetShowRowStripes(Boolean value); + IXLPivotTable SetShowColumnStripes(); IXLPivotTable SetShowColumnStripes(Boolean value); + IXLPivotTable SetSubtotals(XLPivotSubtotals value); + IXLPivotTable SetLayout(XLPivotLayout value); + IXLPivotTable SetInsertBlankLines(); IXLPivotTable SetInsertBlankLines(Boolean value); IXLWorksheet Worksheet { get; } - } } diff --git a/ClosedXML/Excel/PivotTables/XLPivotTable.cs b/ClosedXML/Excel/PivotTables/XLPivotTable.cs index 6cbb361..bd4f8f0 100644 --- a/ClosedXML/Excel/PivotTables/XLPivotTable.cs +++ b/ClosedXML/Excel/PivotTables/XLPivotTable.cs @@ -27,7 +27,30 @@ } public IXLCell TargetCell { get; set; } - public IXLRange SourceRange { get; set; } + + private IXLRange sourceRange; + + public IXLRange SourceRange + { + get { return sourceRange; } + set + { + if (value is IXLTable) + SourceType = XLPivotTableSourceType.Table; + else + SourceType = XLPivotTableSourceType.Range; + + sourceRange = value; + } + } + + public IXLTable SourceTable + { + get { return SourceRange as IXLTable; } + set { SourceRange = value; } + } + + public XLPivotTableSourceType SourceType { get; private set; } public IEnumerable SourceRangeFieldsAvailable { diff --git a/ClosedXML/Excel/PivotTables/XLPivotTables.cs b/ClosedXML/Excel/PivotTables/XLPivotTables.cs index 69a665a..a601d58 100644 --- a/ClosedXML/Excel/PivotTables/XLPivotTables.cs +++ b/ClosedXML/Excel/PivotTables/XLPivotTables.cs @@ -32,7 +32,14 @@ public IXLPivotTable Add(string name, IXLCell targetCell, IXLTable table) { - return Add(name, targetCell, table.AsRange()); + var pivotTable = new XLPivotTable(this.Worksheet) + { + Name = name, + TargetCell = targetCell, + SourceTable = table + }; + _pivotTables.Add(name, pivotTable); + return pivotTable; } public IXLPivotTable AddNew(string name, IXLCell targetCell, IXLRange range) diff --git a/ClosedXML/Excel/Tables/XLTable.cs b/ClosedXML/Excel/Tables/XLTable.cs index e37e473..b7e15f6 100644 --- a/ClosedXML/Excel/Tables/XLTable.cs +++ b/ClosedXML/Excel/Tables/XLTable.cs @@ -1,12 +1,14 @@ using System; using System.Collections.Generic; using System.Data; +using System.Diagnostics; using System.Dynamic; using System.Linq; using System.Text; namespace ClosedXML.Excel { + [DebuggerDisplay("{Name}")] internal class XLTable : XLRange, IXLTable { #region Private fields diff --git a/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/Excel/XLWorkbook_Load.cs index c9c851d..962c37e 100644 --- a/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/Excel/XLWorkbook_Load.cs @@ -456,30 +456,69 @@ } IXLRange source = null; - if (pivotTableCacheDefinitionPart.PivotCacheDefinition != null - && pivotTableCacheDefinitionPart.PivotCacheDefinition.CacheSource != null - && pivotTableCacheDefinitionPart.PivotCacheDefinition.CacheSource.WorksheetSource != null) + XLPivotTableSourceType sourceType = XLPivotTableSourceType.Range; + if (pivotTableCacheDefinitionPart?.PivotCacheDefinition?.CacheSource?.WorksheetSource != null) { - // TODO: Implement other sources besides worksheetSource (e.g. Table source?) + // TODO: Implement other sources besides worksheetSource // 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 + + if (!String.IsNullOrEmpty(wss.Id)) { - var sourceSheet = wss.Sheet == null ? ws : this.Worksheet(wss.Sheet.Value); - rangeAddress = sourceSheet.Range(wss.Reference.Value).RangeAddress.ToStringRelative(true); + var externalRelationship = pivotTableCacheDefinitionPart.ExternalRelationships.FirstOrDefault(er => er.Id.Equals(wss.Id)); + if (externalRelationship?.IsExternal ?? false) + { + // We don't support external sources + continue; + } } - source = this.Range(rangeAddress); + if (wss.Name != null) + { + var table = ws + .Workbook + .Worksheets + .SelectMany(ws1 => ws1.Tables) + .FirstOrDefault(t => t.Name.Equals(wss.Name.Value)); + + if (table != null) + { + sourceType = XLPivotTableSourceType.Table; + source = table; + } + else + { + sourceType = XLPivotTableSourceType.Range; + source = this.Range(wss.Name.Value); + } + } + else + { + sourceType = XLPivotTableSourceType.Range; + var sourceSheet = wss.Sheet == null ? ws : this.Worksheet(wss.Sheet.Value); + source = this.Range(sourceSheet.Range(wss.Reference.Value).RangeAddress.ToStringRelative(includeSheet: true)); + } + if (source == null) continue; } if (target != null && source != null) { - var pt = ws.PivotTables.Add(pivotTableDefinition.Name, target, source) as XLPivotTable; + XLPivotTable pt; + switch (sourceType) + { + case XLPivotTableSourceType.Range: + pt = ws.PivotTables.Add(pivotTableDefinition.Name, target, source) as XLPivotTable; + break; + + case XLPivotTableSourceType.Table: + pt = ws.PivotTables.Add(pivotTableDefinition.Name, target, source as XLTable) as XLPivotTable; + break; + + default: + throw new NotSupportedException($"Pivot table source type {sourceType} is not supported."); + } if (!String.IsNullOrWhiteSpace(StringValue.ToString(pivotTableDefinition?.ColumnHeaderCaption ?? String.Empty))) pt.SetColumnHeaderCaption(StringValue.ToString(pivotTableDefinition.ColumnHeaderCaption)); @@ -1376,12 +1415,15 @@ case CellValues.Boolean: xlCell.SetDataTypeFast(XLDataType.Boolean); break; + case CellValues.Number: xlCell.SetDataTypeFast(XLDataType.Number); break; + case CellValues.Date: xlCell.SetDataTypeFast(XLDataType.DateTime); break; + case CellValues.InlineString: case CellValues.SharedString: xlCell.SetDataTypeFast(XLDataType.Text); @@ -1429,12 +1471,15 @@ case CellValues.Boolean: xlCell.SetDataTypeFast(XLDataType.Boolean); break; + case CellValues.Number: xlCell.SetDataTypeFast(XLDataType.Number); break; + case CellValues.Date: xlCell.SetDataTypeFast(XLDataType.DateTime); break; + case CellValues.InlineString: case CellValues.SharedString: xlCell.SetDataTypeFast(XLDataType.Text); diff --git a/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/Excel/XLWorkbook_Save.cs index 0768579..fd3db5e 100644 --- a/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/Excel/XLWorkbook_Save.cs @@ -153,7 +153,7 @@ { PivotCacheDefinition pvtCacheDef = Item.PivotCacheDefinition; //Check if this CacheSource is linked to SheetToDelete - if (pvtCacheDef.Descendants().Any(s => s.WorksheetSource != null && s.WorksheetSource.Sheet == sheetName)) + if (pvtCacheDef.Descendants().Any(cacheSource => cacheSource.WorksheetSource?.Sheet == sheetName)) { pvtTableCacheDefinitionPart.Add(Item, Item.ToString()); } @@ -2045,7 +2045,27 @@ "http://schemas.openxmlformats.org/officeDocument/2006/relationships"); var cacheSource = new CacheSource { Type = SourceValues.Worksheet }; - cacheSource.AppendChild(new WorksheetSource { Name = source.ToString() }); + var worksheetSource = new WorksheetSource(); + + switch (pt.SourceType) + { + case XLPivotTableSourceType.Range: + worksheetSource.Name = null; + worksheetSource.Reference = source.RangeAddress.ToStringRelative(includeSheet: false); + worksheetSource.Sheet = source.RangeAddress.Worksheet.Name.EscapeSheetName(); + break; + + case XLPivotTableSourceType.Table: + worksheetSource.Name = pt.SourceTable.Name; + worksheetSource.Reference = null; + worksheetSource.Sheet = null; + break; + + default: + throw new NotSupportedException($"Pivot table source type {pt.SourceType} is not supported."); + } + + cacheSource.AppendChild(worksheetSource); var cacheFields = new CacheFields(); diff --git a/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx b/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx index d7e2f79..5264760 100644 --- a/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx +++ b/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Other/PivotTableReferenceFiles/BlankPivotTableField/BlankPivotTableField.xlsx b/ClosedXML_Tests/Resource/Other/PivotTableReferenceFiles/BlankPivotTableField/BlankPivotTableField.xlsx index 6531b99..eb652c2 100644 --- a/ClosedXML_Tests/Resource/Other/PivotTableReferenceFiles/BlankPivotTableField/BlankPivotTableField.xlsx +++ b/ClosedXML_Tests/Resource/Other/PivotTableReferenceFiles/BlankPivotTableField/BlankPivotTableField.xlsx Binary files differ