diff --git a/ClosedXML/Excel/PivotTables/IXLPivotField.cs b/ClosedXML/Excel/PivotTables/IXLPivotField.cs index 8ce227b..20b3ef4 100644 --- a/ClosedXML/Excel/PivotTables/IXLPivotField.cs +++ b/ClosedXML/Excel/PivotTables/IXLPivotField.cs @@ -1,7 +1,5 @@ using System; using System.Collections.Generic; -using System.Linq; -using System.Text; namespace ClosedXML.Excel { @@ -51,6 +49,7 @@ IXLPivotField SetInsertPageBreaks(); IXLPivotField SetInsertPageBreaks(Boolean value); IXLPivotField SetCollapsed(); IXLPivotField SetCollapsed(Boolean value); - List SharedStrings { get; set; } + IList SelectedValues { get; } + IXLPivotField AddSelectedValue(Object value); } } diff --git a/ClosedXML/Excel/PivotTables/XLPivotField.cs b/ClosedXML/Excel/PivotTables/XLPivotField.cs index 9bd7ccb..acf0b1c 100644 --- a/ClosedXML/Excel/PivotTables/XLPivotField.cs +++ b/ClosedXML/Excel/PivotTables/XLPivotField.cs @@ -1,15 +1,17 @@ using System; using System.Collections.Generic; +using System.Diagnostics; namespace ClosedXML.Excel { + [DebuggerDisplay("{SourceName}")] public class XLPivotField : IXLPivotField { public XLPivotField(string sourceName) { SourceName = sourceName; - SharedStrings = new List(); Subtotals = new List(); + SelectedValues = new List(); } public String SourceName { get; private set; } @@ -67,6 +69,11 @@ public IXLPivotField SetCollapsed(Boolean value) { Collapsed = value; return this; } - public List SharedStrings { get; set; } + public IList SelectedValues { get; private set; } + public IXLPivotField AddSelectedValue(Object value) + { + SelectedValues.Add(value); + return this; + } } } diff --git a/ClosedXML/Excel/PivotTables/XLPivotTable.cs b/ClosedXML/Excel/PivotTables/XLPivotTable.cs index 6e7c1f0..6832af3 100644 --- a/ClosedXML/Excel/PivotTables/XLPivotTable.cs +++ b/ClosedXML/Excel/PivotTables/XLPivotTable.cs @@ -1,18 +1,22 @@ using System; using System.Collections.Generic; +using System.Diagnostics; using System.Linq; -using System.Text; namespace ClosedXML.Excel { - internal class XLPivotTable: IXLPivotTable + [DebuggerDisplay("{Name}")] + internal class XLPivotTable : IXLPivotTable { + public Guid Guid { get; private set; } public XLPivotTable() { + this.Guid = Guid.NewGuid(); + Fields = new XLPivotFields(this); ReportFilters = new XLPivotFields(this); - ColumnLabels=new XLPivotFields(this); + ColumnLabels = new XLPivotFields(this); RowLabels = new XLPivotFields(this); Values = new XLPivotValues(this); Theme = XLPivotTableTheme.PivotStyleLight16; @@ -22,6 +26,7 @@ public IXLCell TargetCell { get; set; } public IXLRange SourceRange { get; set; } + public IEnumerable SourceRangeFieldsAvailable { get { return this.SourceRange.FirstRow().Cells().Select(c => c.GetString()); } @@ -33,12 +38,24 @@ public IXLPivotFields RowLabels { get; private set; } public IXLPivotValues Values { get; private set; } - public XLPivotTableTheme Theme { get; set; } public IXLPivotTable SetTheme(XLPivotTableTheme value) { Theme = value; return this; } - public String Name { get; set; } public IXLPivotTable SetName(String value) { Name = value; return this; } - public String Title { get; set; } public IXLPivotTable SetTitle(String value) { Title = value; return this; } - public String Description { get; set; } public IXLPivotTable SetDescription(String value) { Description = value; return this; } + public XLPivotTableTheme Theme { get; set; } + + public IXLPivotTable SetTheme(XLPivotTableTheme value) { Theme = value; return this; } + + public String Name { get; set; } + + public IXLPivotTable SetName(String value) { Name = value; return this; } + + public String Title { get; set; } + + public IXLPivotTable SetTitle(String value) { Title = value; return this; } + + public String Description { get; set; } + + public IXLPivotTable SetDescription(String value) { Description = value; return this; } public String ColumnHeaderCaption { get; set; } + public IXLPivotTable SetColumnHeaderCaption(String value) { ColumnHeaderCaption = value; @@ -46,68 +63,230 @@ } public String RowHeaderCaption { get; set; } + public IXLPivotTable SetRowHeaderCaption(String value) { RowHeaderCaption = value; return this; } - public Boolean MergeAndCenterWithLabels { get; set; } public IXLPivotTable SetMergeAndCenterWithLabels() { MergeAndCenterWithLabels = true; return this; } public IXLPivotTable SetMergeAndCenterWithLabels(Boolean value) { MergeAndCenterWithLabels = value; return this; } - public Int32 RowLabelIndent { get; set; } public IXLPivotTable SetRowLabelIndent(Int32 value) { RowLabelIndent = value; return this; } - public XLFilterAreaOrder FilterAreaOrder { get; set; } public IXLPivotTable SetFilterAreaOrder(XLFilterAreaOrder value) { FilterAreaOrder = value; return this; } - public Int32 FilterFieldsPageWrap { get; set; } public IXLPivotTable SetFilterFieldsPageWrap(Int32 value) { FilterFieldsPageWrap = value; return this; } - public String ErrorValueReplacement { get; set; } public IXLPivotTable SetErrorValueReplacement(String value) { ErrorValueReplacement = value; return this; } - public String EmptyCellReplacement { get; set; } public IXLPivotTable SetEmptyCellReplacement(String value) { EmptyCellReplacement = value; return this; } - public Boolean AutofitColumns { get; set; } public IXLPivotTable SetAutofitColumns() { AutofitColumns = true; return this; } public IXLPivotTable SetAutofitColumns(Boolean value) { AutofitColumns = value; return this; } - public Boolean PreserveCellFormatting { get; set; } public IXLPivotTable SetPreserveCellFormatting() { PreserveCellFormatting = true; return this; } public IXLPivotTable SetPreserveCellFormatting(Boolean value) { PreserveCellFormatting = value; return this; } + public Boolean MergeAndCenterWithLabels { get; set; } - public Boolean ShowGrandTotalsRows { get; set; } public IXLPivotTable SetShowGrandTotalsRows() { ShowGrandTotalsRows = true; return this; } public IXLPivotTable SetShowGrandTotalsRows(Boolean value) { ShowGrandTotalsRows = value; return this; } - public Boolean ShowGrandTotalsColumns { get; set; } public IXLPivotTable SetShowGrandTotalsColumns() { ShowGrandTotalsColumns = true; return this; } public IXLPivotTable SetShowGrandTotalsColumns(Boolean value) { ShowGrandTotalsColumns = value; return this; } - public Boolean FilteredItemsInSubtotals { get; set; } public IXLPivotTable SetFilteredItemsInSubtotals() { FilteredItemsInSubtotals = true; return this; } public IXLPivotTable SetFilteredItemsInSubtotals(Boolean value) { FilteredItemsInSubtotals = value; return this; } - public Boolean AllowMultipleFilters { get; set; } public IXLPivotTable SetAllowMultipleFilters() { AllowMultipleFilters = true; return this; } public IXLPivotTable SetAllowMultipleFilters(Boolean value) { AllowMultipleFilters = value; return this; } - public Boolean UseCustomListsForSorting { get; set; } public IXLPivotTable SetUseCustomListsForSorting() { UseCustomListsForSorting = true; return this; } public IXLPivotTable SetUseCustomListsForSorting(Boolean value) { UseCustomListsForSorting = value; return this; } + public IXLPivotTable SetMergeAndCenterWithLabels() { MergeAndCenterWithLabels = true; return this; } - public Boolean ShowExpandCollapseButtons { get; set; } public IXLPivotTable SetShowExpandCollapseButtons() { ShowExpandCollapseButtons = true; return this; } public IXLPivotTable SetShowExpandCollapseButtons(Boolean value) { ShowExpandCollapseButtons = value; return this; } - public Boolean ShowContextualTooltips { get; set; } public IXLPivotTable SetShowContextualTooltips() { ShowContextualTooltips = true; return this; } public IXLPivotTable SetShowContextualTooltips(Boolean value) { ShowContextualTooltips = value; return this; } - public Boolean ShowPropertiesInTooltips { get; set; } public IXLPivotTable SetShowPropertiesInTooltips() { ShowPropertiesInTooltips = true; return this; } public IXLPivotTable SetShowPropertiesInTooltips(Boolean value) { ShowPropertiesInTooltips = value; return this; } - public Boolean DisplayCaptionsAndDropdowns { get; set; } public IXLPivotTable SetDisplayCaptionsAndDropdowns() { DisplayCaptionsAndDropdowns = true; return this; } public IXLPivotTable SetDisplayCaptionsAndDropdowns(Boolean value) { DisplayCaptionsAndDropdowns = value; return this; } - public Boolean ClassicPivotTableLayout { get; set; } public IXLPivotTable SetClassicPivotTableLayout() { ClassicPivotTableLayout = true; return this; } public IXLPivotTable SetClassicPivotTableLayout(Boolean value) { ClassicPivotTableLayout = value; return this; } - public Boolean ShowValuesRow { get; set; } public IXLPivotTable SetShowValuesRow() { ShowValuesRow = true; return this; } public IXLPivotTable SetShowValuesRow(Boolean value) { ShowValuesRow = value; return this; } - public Boolean ShowEmptyItemsOnRows { get; set; } public IXLPivotTable SetShowEmptyItemsOnRows() { ShowEmptyItemsOnRows = true; return this; } public IXLPivotTable SetShowEmptyItemsOnRows(Boolean value) { ShowEmptyItemsOnRows = value; return this; } - public Boolean ShowEmptyItemsOnColumns { get; set; } public IXLPivotTable SetShowEmptyItemsOnColumns() { ShowEmptyItemsOnColumns = true; return this; } public IXLPivotTable SetShowEmptyItemsOnColumns(Boolean value) { ShowEmptyItemsOnColumns = value; return this; } - public Boolean DisplayItemLabels { get; set; } public IXLPivotTable SetDisplayItemLabels() { DisplayItemLabels = true; return this; } public IXLPivotTable SetDisplayItemLabels(Boolean value) { DisplayItemLabels = value; return this; } - public Boolean SortFieldsAtoZ { get; set; } public IXLPivotTable SetSortFieldsAtoZ() { SortFieldsAtoZ = true; return this; } public IXLPivotTable SetSortFieldsAtoZ(Boolean value) { SortFieldsAtoZ = value; return this; } + public IXLPivotTable SetMergeAndCenterWithLabels(Boolean value) { MergeAndCenterWithLabels = value; return this; } - public Boolean PrintExpandCollapsedButtons { get; set; } public IXLPivotTable SetPrintExpandCollapsedButtons() { PrintExpandCollapsedButtons = true; return this; } public IXLPivotTable SetPrintExpandCollapsedButtons(Boolean value) { PrintExpandCollapsedButtons = value; return this; } - public Boolean RepeatRowLabels { get; set; } public IXLPivotTable SetRepeatRowLabels() { RepeatRowLabels = true; return this; } public IXLPivotTable SetRepeatRowLabels(Boolean value) { RepeatRowLabels = value; return this; } - public Boolean PrintTitles { get; set; } public IXLPivotTable SetPrintTitles() { PrintTitles = true; return this; } public IXLPivotTable SetPrintTitles(Boolean value) { PrintTitles = value; return this; } + public Int32 RowLabelIndent { get; set; } - public Boolean SaveSourceData { get; set; } public IXLPivotTable SetSaveSourceData() { SaveSourceData = true; return this; } public IXLPivotTable SetSaveSourceData(Boolean value) { SaveSourceData = value; return this; } - public Boolean EnableShowDetails { get; set; } public IXLPivotTable SetEnableShowDetails() { EnableShowDetails = true; return this; } public IXLPivotTable SetEnableShowDetails(Boolean value) { EnableShowDetails = value; return this; } - public Boolean RefreshDataOnOpen { get; set; } public IXLPivotTable SetRefreshDataOnOpen() { RefreshDataOnOpen = true; return this; } public IXLPivotTable SetRefreshDataOnOpen(Boolean value) { RefreshDataOnOpen = value; return this; } - public XLItemsToRetain ItemsToRetainPerField { get; set; } public IXLPivotTable SetItemsToRetainPerField(XLItemsToRetain value) { ItemsToRetainPerField = value; return this; } - public Boolean EnableCellEditing { get; set; } public IXLPivotTable SetEnableCellEditing() { EnableCellEditing = true; return this; } public IXLPivotTable SetEnableCellEditing(Boolean value) { EnableCellEditing = value; return this; } + public IXLPivotTable SetRowLabelIndent(Int32 value) { RowLabelIndent = value; return this; } + public XLFilterAreaOrder FilterAreaOrder { get; set; } - public Boolean ShowRowHeaders { get; set; } public IXLPivotTable SetShowRowHeaders() { ShowRowHeaders = true; return this; } public IXLPivotTable SetShowRowHeaders(Boolean value) { ShowRowHeaders = value; return this; } - public Boolean ShowColumnHeaders { get; set; } public IXLPivotTable SetShowColumnHeaders() { ShowColumnHeaders = true; return this; } public IXLPivotTable SetShowColumnHeaders(Boolean value) { ShowColumnHeaders = value; return this; } - public Boolean ShowRowStripes { get; set; } public IXLPivotTable SetShowRowStripes() { ShowRowStripes = true; return this; } public IXLPivotTable SetShowRowStripes(Boolean value) { ShowRowStripes = value; return this; } - public Boolean ShowColumnStripes { get; set; } public IXLPivotTable SetShowColumnStripes() { ShowColumnStripes = true; return this; } public IXLPivotTable SetShowColumnStripes(Boolean value) { ShowColumnStripes = value; return this; } - public XLPivotSubtotals Subtotals { get; set; } public IXLPivotTable SetSubtotals(XLPivotSubtotals value) { Subtotals = value; return this; } + public IXLPivotTable SetFilterAreaOrder(XLFilterAreaOrder value) { FilterAreaOrder = value; return this; } + + public Int32 FilterFieldsPageWrap { get; set; } + + public IXLPivotTable SetFilterFieldsPageWrap(Int32 value) { FilterFieldsPageWrap = value; return this; } + + public String ErrorValueReplacement { get; set; } + + public IXLPivotTable SetErrorValueReplacement(String value) { ErrorValueReplacement = value; return this; } + + public String EmptyCellReplacement { get; set; } + + public IXLPivotTable SetEmptyCellReplacement(String value) { EmptyCellReplacement = value; return this; } + + public Boolean AutofitColumns { get; set; } + + public IXLPivotTable SetAutofitColumns() { AutofitColumns = true; return this; } + + public IXLPivotTable SetAutofitColumns(Boolean value) { AutofitColumns = value; return this; } + + public Boolean PreserveCellFormatting { get; set; } + + public IXLPivotTable SetPreserveCellFormatting() { PreserveCellFormatting = true; return this; } + + public IXLPivotTable SetPreserveCellFormatting(Boolean value) { PreserveCellFormatting = value; return this; } + + public Boolean ShowGrandTotalsRows { get; set; } + + public IXLPivotTable SetShowGrandTotalsRows() { ShowGrandTotalsRows = true; return this; } + + public IXLPivotTable SetShowGrandTotalsRows(Boolean value) { ShowGrandTotalsRows = value; return this; } + + public Boolean ShowGrandTotalsColumns { get; set; } + + public IXLPivotTable SetShowGrandTotalsColumns() { ShowGrandTotalsColumns = true; return this; } + + public IXLPivotTable SetShowGrandTotalsColumns(Boolean value) { ShowGrandTotalsColumns = value; return this; } + + public Boolean FilteredItemsInSubtotals { get; set; } + + public IXLPivotTable SetFilteredItemsInSubtotals() { FilteredItemsInSubtotals = true; return this; } + + public IXLPivotTable SetFilteredItemsInSubtotals(Boolean value) { FilteredItemsInSubtotals = value; return this; } + + public Boolean AllowMultipleFilters { get; set; } + + public IXLPivotTable SetAllowMultipleFilters() { AllowMultipleFilters = true; return this; } + + public IXLPivotTable SetAllowMultipleFilters(Boolean value) { AllowMultipleFilters = value; return this; } + + public Boolean UseCustomListsForSorting { get; set; } + + public IXLPivotTable SetUseCustomListsForSorting() { UseCustomListsForSorting = true; return this; } + + public IXLPivotTable SetUseCustomListsForSorting(Boolean value) { UseCustomListsForSorting = value; return this; } + + public Boolean ShowExpandCollapseButtons { get; set; } + + public IXLPivotTable SetShowExpandCollapseButtons() { ShowExpandCollapseButtons = true; return this; } + + public IXLPivotTable SetShowExpandCollapseButtons(Boolean value) { ShowExpandCollapseButtons = value; return this; } + + public Boolean ShowContextualTooltips { get; set; } + + public IXLPivotTable SetShowContextualTooltips() { ShowContextualTooltips = true; return this; } + + public IXLPivotTable SetShowContextualTooltips(Boolean value) { ShowContextualTooltips = value; return this; } + + public Boolean ShowPropertiesInTooltips { get; set; } + + public IXLPivotTable SetShowPropertiesInTooltips() { ShowPropertiesInTooltips = true; return this; } + + public IXLPivotTable SetShowPropertiesInTooltips(Boolean value) { ShowPropertiesInTooltips = value; return this; } + + public Boolean DisplayCaptionsAndDropdowns { get; set; } + + public IXLPivotTable SetDisplayCaptionsAndDropdowns() { DisplayCaptionsAndDropdowns = true; return this; } + + public IXLPivotTable SetDisplayCaptionsAndDropdowns(Boolean value) { DisplayCaptionsAndDropdowns = value; return this; } + + public Boolean ClassicPivotTableLayout { get; set; } + + public IXLPivotTable SetClassicPivotTableLayout() { ClassicPivotTableLayout = true; return this; } + + public IXLPivotTable SetClassicPivotTableLayout(Boolean value) { ClassicPivotTableLayout = value; return this; } + + public Boolean ShowValuesRow { get; set; } + + public IXLPivotTable SetShowValuesRow() { ShowValuesRow = true; return this; } + + public IXLPivotTable SetShowValuesRow(Boolean value) { ShowValuesRow = value; return this; } + + public Boolean ShowEmptyItemsOnRows { get; set; } + + public IXLPivotTable SetShowEmptyItemsOnRows() { ShowEmptyItemsOnRows = true; return this; } + + public IXLPivotTable SetShowEmptyItemsOnRows(Boolean value) { ShowEmptyItemsOnRows = value; return this; } + + public Boolean ShowEmptyItemsOnColumns { get; set; } + + public IXLPivotTable SetShowEmptyItemsOnColumns() { ShowEmptyItemsOnColumns = true; return this; } + + public IXLPivotTable SetShowEmptyItemsOnColumns(Boolean value) { ShowEmptyItemsOnColumns = value; return this; } + + public Boolean DisplayItemLabels { get; set; } + + public IXLPivotTable SetDisplayItemLabels() { DisplayItemLabels = true; return this; } + + public IXLPivotTable SetDisplayItemLabels(Boolean value) { DisplayItemLabels = value; return this; } + + public Boolean SortFieldsAtoZ { get; set; } + + public IXLPivotTable SetSortFieldsAtoZ() { SortFieldsAtoZ = true; return this; } + + public IXLPivotTable SetSortFieldsAtoZ(Boolean value) { SortFieldsAtoZ = value; return this; } + + public Boolean PrintExpandCollapsedButtons { get; set; } + + public IXLPivotTable SetPrintExpandCollapsedButtons() { PrintExpandCollapsedButtons = true; return this; } + + public IXLPivotTable SetPrintExpandCollapsedButtons(Boolean value) { PrintExpandCollapsedButtons = value; return this; } + + public Boolean RepeatRowLabels { get; set; } + + public IXLPivotTable SetRepeatRowLabels() { RepeatRowLabels = true; return this; } + + public IXLPivotTable SetRepeatRowLabels(Boolean value) { RepeatRowLabels = value; return this; } + + public Boolean PrintTitles { get; set; } + + public IXLPivotTable SetPrintTitles() { PrintTitles = true; return this; } + + public IXLPivotTable SetPrintTitles(Boolean value) { PrintTitles = value; return this; } + + public Boolean SaveSourceData { get; set; } + + public IXLPivotTable SetSaveSourceData() { SaveSourceData = true; return this; } + + public IXLPivotTable SetSaveSourceData(Boolean value) { SaveSourceData = value; return this; } + + public Boolean EnableShowDetails { get; set; } + + public IXLPivotTable SetEnableShowDetails() { EnableShowDetails = true; return this; } + + public IXLPivotTable SetEnableShowDetails(Boolean value) { EnableShowDetails = value; return this; } + + public Boolean RefreshDataOnOpen { get; set; } + + public IXLPivotTable SetRefreshDataOnOpen() { RefreshDataOnOpen = true; return this; } + + public IXLPivotTable SetRefreshDataOnOpen(Boolean value) { RefreshDataOnOpen = value; return this; } + + public XLItemsToRetain ItemsToRetainPerField { get; set; } + + public IXLPivotTable SetItemsToRetainPerField(XLItemsToRetain value) { ItemsToRetainPerField = value; return this; } + + public Boolean EnableCellEditing { get; set; } + + public IXLPivotTable SetEnableCellEditing() { EnableCellEditing = true; return this; } + + public IXLPivotTable SetEnableCellEditing(Boolean value) { EnableCellEditing = value; return this; } + + public Boolean ShowRowHeaders { get; set; } + + public IXLPivotTable SetShowRowHeaders() { ShowRowHeaders = true; return this; } + + public IXLPivotTable SetShowRowHeaders(Boolean value) { ShowRowHeaders = value; return this; } + + public Boolean ShowColumnHeaders { get; set; } + + public IXLPivotTable SetShowColumnHeaders() { ShowColumnHeaders = true; return this; } + + public IXLPivotTable SetShowColumnHeaders(Boolean value) { ShowColumnHeaders = value; return this; } + + public Boolean ShowRowStripes { get; set; } + + public IXLPivotTable SetShowRowStripes() { ShowRowStripes = true; return this; } + + public IXLPivotTable SetShowRowStripes(Boolean value) { ShowRowStripes = value; return this; } + + public Boolean ShowColumnStripes { get; set; } + + public IXLPivotTable SetShowColumnStripes() { ShowColumnStripes = true; return this; } + + public IXLPivotTable SetShowColumnStripes(Boolean value) { ShowColumnStripes = value; return this; } + + public XLPivotSubtotals Subtotals { get; set; } + + public IXLPivotTable SetSubtotals(XLPivotSubtotals value) { Subtotals = value; return this; } public XLPivotLayout Layout { - set { Fields.ForEach(f=>f.SetLayout(value)); } + set { Fields.ForEach(f => f.SetLayout(value)); } } public IXLPivotTable SetLayout(XLPivotLayout value) { Layout = value; return this; } public Boolean InsertBlankLines { - set { Fields.ForEach(f=>f.SetInsertBlankLines(value)); } + set { Fields.ForEach(f => f.SetInsertBlankLines(value)); } } - public IXLPivotTable SetInsertBlankLines() { InsertBlankLines = true; return this; } public IXLPivotTable SetInsertBlankLines(Boolean value) { InsertBlankLines = value; return this; } + public IXLPivotTable SetInsertBlankLines() { InsertBlankLines = true; return this; } + + public IXLPivotTable SetInsertBlankLines(Boolean value) { InsertBlankLines = value; return this; } internal String RelId { get; set; } internal String CacheDefinitionRelId { get; set; } @@ -130,6 +309,5 @@ ShowColumnHeaders = true; ShowRowHeaders = true; } - } } diff --git a/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/Excel/XLWorkbook_Load.cs index 3af1993..2b7fa10 100644 --- a/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/Excel/XLWorkbook_Load.cs @@ -642,6 +642,73 @@ } } } + + // Filters + if (pivotTableDefinition.PageFields != null) + { + foreach (var pageField in pivotTableDefinition.PageFields.Cast()) + { + var pf = pivotTableDefinition.PivotFields.ElementAt((int)pageField.Field.Value) as PivotField; + if (pf == null) + continue; + + var cacheField = pivotTableCacheDefinitionPart.PivotCacheDefinition.CacheFields.ElementAt((int)pageField.Field.Value) as CacheField; + + var filterName = pf.Name?.Value ?? cacheField.Name?.Value; + + IXLPivotField rf; + if (pageField.Name?.Value != null) + rf = pt.ReportFilters.Add(filterName, pageField.Name.Value); + else + rf = pt.ReportFilters.Add(filterName); + + if ((pageField.Item?.HasValue ?? false) + && pf.Items.Any() && cacheField.SharedItems.Any()) + { + var item = pf.Items.ElementAt(Convert.ToInt32(pageField.Item.Value)) as Item; + if (item == null) + continue; + + var sharedItem = cacheField.SharedItems.ElementAt(Convert.ToInt32((uint)item.Index)); + var numberItem = sharedItem as NumberItem; + var stringItem = sharedItem as StringItem; + var dateTimeItem = sharedItem as DateTimeItem; + + if (numberItem != null) + rf.AddSelectedValue(Convert.ToDouble(numberItem.Val.Value)); + else if (dateTimeItem != null) + rf.AddSelectedValue(Convert.ToDateTime(dateTimeItem.Val.Value)); + else if (stringItem != null) + rf.AddSelectedValue(stringItem.Val.Value); + else + throw new NotImplementedException(); + } + else if (BooleanValue.ToBoolean(pf.MultipleItemSelectionAllowed)) + { + foreach (var item in pf.Items.Cast()) + { + if (item.Hidden == null || !BooleanValue.ToBoolean(item.Hidden)) + { + var sharedItem = cacheField.SharedItems.ElementAt(Convert.ToInt32((uint)item.Index)); + var numberItem = sharedItem as NumberItem; + var stringItem = sharedItem as StringItem; + var dateTimeItem = sharedItem as DateTimeItem; + + if (numberItem != null) + rf.AddSelectedValue(Convert.ToDouble(numberItem.Val.Value)); + else if (dateTimeItem != null) + rf.AddSelectedValue(Convert.ToDateTime(dateTimeItem.Val.Value)); + else if (stringItem != null) + rf.AddSelectedValue(stringItem.Val.Value); + else + throw new NotImplementedException(); + } + } + } + } + + pt.TargetCell = pt.TargetCell.CellAbove(pt.ReportFilters.Count() + 1); + } } } } diff --git a/ClosedXML/Excel/XLWorkbook_Save.NestedTypes.cs b/ClosedXML/Excel/XLWorkbook_Save.NestedTypes.cs index 491b37c..c4a9961 100644 --- a/ClosedXML/Excel/XLWorkbook_Save.NestedTypes.cs +++ b/ClosedXML/Excel/XLWorkbook_Save.NestedTypes.cs @@ -1,6 +1,5 @@ using System; using System.Collections.Generic; -using System.Diagnostics; using System.Linq; namespace ClosedXML.Excel @@ -8,81 +7,57 @@ public partial class XLWorkbook { #region Nested type: SaveContext + internal sealed class SaveContext { - #region Private fields - [DebuggerBrowsable(DebuggerBrowsableState.Never)] - private readonly RelIdGenerator _relIdGenerator; - [DebuggerBrowsable(DebuggerBrowsableState.Never)] - private readonly Dictionary _sharedStyles; - [DebuggerBrowsable(DebuggerBrowsableState.Never)] - private readonly Dictionary _sharedNumberFormats; - [DebuggerBrowsable(DebuggerBrowsableState.Never)] - private readonly Dictionary _sharedFonts; - [DebuggerBrowsable(DebuggerBrowsableState.Never)] - private readonly HashSet _tableNames; - [DebuggerBrowsable(DebuggerBrowsableState.Never)] - private uint _tableId; - #endregion - #region Constructor public SaveContext() { - _relIdGenerator = new RelIdGenerator(); - _sharedStyles = new Dictionary(); - _sharedNumberFormats = new Dictionary(); - _sharedFonts = new Dictionary(); - _tableNames = new HashSet(); - _tableId = 0; + DifferentialFormats = new Dictionary(); + PivotTables = new Dictionary(); + RelIdGenerator = new RelIdGenerator(); + SharedFonts = new Dictionary(); + SharedNumberFormats = new Dictionary(); + SharedStyles = new Dictionary(); + TableId = 0; + TableNames = new HashSet(); } - #endregion - #region Public properties - public RelIdGenerator RelIdGenerator - { - [DebuggerStepThrough] - get { return _relIdGenerator; } - } - public Dictionary SharedStyles - { - [DebuggerStepThrough] - get { return _sharedStyles; } - } - public Dictionary SharedNumberFormats - { - [DebuggerStepThrough] - get { return _sharedNumberFormats; } - } - public Dictionary SharedFonts - { - [DebuggerStepThrough] - get { return _sharedFonts; } - } - public HashSet TableNames - { - [DebuggerStepThrough] - get { return _tableNames; } - } - public uint TableId - { - [DebuggerStepThrough] - get { return _tableId; } - [DebuggerStepThrough] - set { _tableId = value; } - } - public Dictionary DifferentialFormats = new Dictionary(); - #endregion + + public Dictionary DifferentialFormats { get; private set; } + public IDictionary PivotTables { get; private set; } + public RelIdGenerator RelIdGenerator { get; private set; } + public Dictionary SharedFonts { get; private set; } + public Dictionary SharedNumberFormats { get; private set; } + public Dictionary SharedStyles { get; private set; } + public uint TableId { get; set; } + public HashSet TableNames { get; private set; } } - #endregion + + #endregion Nested type: SaveContext + #region Nested type: RelType + internal enum RelType { Workbook//, Worksheet } - #endregion + + #endregion Nested type: RelType + #region Nested type: RelIdGenerator + internal sealed class RelIdGenerator { private readonly Dictionary> _relIds = new Dictionary>(); + public void AddValues(IEnumerable values, RelType relType) + { + if (!_relIds.ContainsKey(relType)) + { + _relIds.Add(relType, new List()); + } + _relIds[relType].AddRange(values.Where(v => !_relIds[relType].Contains(v))); + } + public String GetNext() { return GetNext(RelType.Workbook); @@ -107,59 +82,85 @@ id++; } } - public void AddValues(IEnumerable values, RelType relType) - { - if (!_relIds.ContainsKey(relType)) - { - _relIds.Add(relType, new List()); - } - _relIds[relType].AddRange(values.Where(v => !_relIds[relType].Contains(v))); - } + public void Reset(RelType relType) { if (_relIds.ContainsKey(relType)) _relIds.Remove(relType); } } - #endregion + + #endregion Nested type: RelIdGenerator + #region Nested type: FontInfo + internal struct FontInfo { - public UInt32 FontId; public XLFont Font; + public UInt32 FontId; }; - #endregion + + #endregion Nested type: FontInfo + #region Nested type: FillInfo + internal struct FillInfo { - public UInt32 FillId; public XLFill Fill; + public UInt32 FillId; } - #endregion + + #endregion Nested type: FillInfo + #region Nested type: BorderInfo + internal struct BorderInfo { - public UInt32 BorderId; public XLBorder Border; + public UInt32 BorderId; } - #endregion + + #endregion Nested type: BorderInfo + #region Nested type: NumberFormatInfo + internal struct NumberFormatInfo { - public Int32 NumberFormatId; public IXLNumberFormatBase NumberFormat; + public Int32 NumberFormatId; } - #endregion + + #endregion Nested type: NumberFormatInfo + #region Nested type: StyleInfo + internal struct StyleInfo { - public UInt32 StyleId; - public UInt32 FontId; - public UInt32 FillId; public UInt32 BorderId; + public UInt32 FillId; + public UInt32 FontId; public Int32 NumberFormatId; public IXLStyle Style; + public UInt32 StyleId; } - #endregion + + #endregion Nested type: StyleInfo + + #region Nested type: Pivot tables + + internal struct PivotTableFieldInfo + { + public XLCellValues DataType; + public Boolean MixedDataType; + public IEnumerable DistinctValues; + } + + internal struct PivotTableInfo + { + public IDictionary Fields; + public Guid Guid; + } + + #endregion Nested type: Pivot tables } } diff --git a/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/Excel/XLWorkbook_Save.cs index a60076a..05e831d 100644 --- a/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/Excel/XLWorkbook_Save.cs @@ -53,7 +53,6 @@ { private const Double ColumnWidthOffset = 0.710625; - //private Dictionary sharedStrings; //private Dictionary context.SharedStyles; private static readonly EnumValue CvSharedString = new EnumValue(CellValues.SharedString); @@ -1898,6 +1897,8 @@ foreach (var pt in xlWorksheet.PivotTables.Cast()) { + context.PivotTables.Clear(); + // TODO: Avoid duplicate pivot caches of same source range var workbookCacheRelId = pt.WorkbookCacheRelId; @@ -1915,7 +1916,7 @@ pivotTableCacheDefinitionPart = workbookPart.AddNewPart(workbookCacheRelId); } - GeneratePivotTableCacheDefinitionPartContent(pivotTableCacheDefinitionPart, pt); + GeneratePivotTableCacheDefinitionPartContent(pivotTableCacheDefinitionPart, pt, context); if (String.IsNullOrWhiteSpace(pt.WorkbookCacheRelId)) pivotCaches.AppendChild(pivotCache); @@ -1935,10 +1936,16 @@ // Generates content of pivotTableCacheDefinitionPart private static void GeneratePivotTableCacheDefinitionPartContent( - PivotTableCacheDefinitionPart pivotTableCacheDefinitionPart, IXLPivotTable pt) + PivotTableCacheDefinitionPart pivotTableCacheDefinitionPart, IXLPivotTable pt, + SaveContext context) { - var source = pt.SourceRange; + var pti = new PivotTableInfo + { + Guid = (pt as XLPivotTable).Guid, + Fields = new Dictionary() + }; + var source = pt.SourceRange; var pivotCacheDefinition = new PivotCacheDefinition { Id = "rId1", @@ -1962,14 +1969,18 @@ { var columnNumber = c.ColumnNumber(); var columnName = c.FirstCell().Value.ToString(); - IXLPivotField xlpf; - if (pt.Fields.Contains(columnName)) - xlpf = pt.Fields.Get(columnName); - else - xlpf = pt.Fields.Add(columnName); - var field = - pt.RowLabels.Union(pt.ColumnLabels).Union(pt.ReportFilters).FirstOrDefault(f => f.SourceName == columnName); + XLPivotField xlpf; + if (pt.Fields.Contains(columnName)) + xlpf = pt.Fields.Get(columnName) as XLPivotField; + else + xlpf = pt.Fields.Add(columnName) as XLPivotField; + + var field = pt.RowLabels + .Union(pt.ColumnLabels) + .Union(pt.ReportFilters) + .FirstOrDefault(f => f.SourceName == columnName); + if (field != null) { xlpf.CustomName = field.CustomName; @@ -1978,32 +1989,96 @@ var sharedItems = new SharedItems(); - var onlyNumbers = - !source.Cells().Any( - cell => - cell.Address.ColumnNumber == columnNumber && - cell.Address.RowNumber > source.FirstRow().RowNumber() && cell.DataType != XLCellValues.Number); - if (onlyNumbers) + var ptfi = new PivotTableFieldInfo(); + + var fieldValueCells = source.Cells(cell => cell.Address.ColumnNumber == columnNumber + && cell.Address.RowNumber > source.FirstRow().RowNumber()); + var types = fieldValueCells.Select(cell => cell.DataType).Distinct(); + + + if (types.Count() == 1 && types.Single() == XLCellValues.Number) { - sharedItems = new SharedItems - { ContainsSemiMixedTypes = false, ContainsString = false, ContainsNumber = true }; + sharedItems.ContainsSemiMixedTypes = false; + sharedItems.ContainsString = false; + sharedItems.ContainsNumber = true; + + ptfi.DataType = XLCellValues.Number; + ptfi.MixedDataType = false; + ptfi.DistinctValues = fieldValueCells + .Select(cell => cell.GetDouble()) + .Distinct() + .Cast(); + + pti.Fields.Add(xlpf.SourceName, ptfi); + + // Output items only for row / column / filter fields + if (pt.RowLabels.Any(p => p.SourceName == xlpf.SourceName) + || pt.ColumnLabels.Any(p => p.SourceName == xlpf.SourceName) + || pt.ReportFilters.Any(p => p.SourceName == xlpf.SourceName)) + { + foreach (var value in ptfi.DistinctValues) + sharedItems.AppendChild(new NumberItem { Val = (double)value }); + } + + sharedItems.MinValue = (double)ptfi.DistinctValues.Min(); + sharedItems.MaxValue = (double)ptfi.DistinctValues.Max(); + } + else if (types.Count() == 1 && types.Single() == XLCellValues.DateTime) + { + sharedItems.ContainsSemiMixedTypes = false; + sharedItems.ContainsString = false; + sharedItems.ContainsNumber = false; + sharedItems.ContainsDate = true; + + ptfi.DataType = XLCellValues.DateTime; + ptfi.MixedDataType = false; + ptfi.DistinctValues = fieldValueCells + .Select(cell => cell.GetDateTime()) + .Distinct() + .Cast(); + + pti.Fields.Add(xlpf.SourceName, ptfi); + + // Output items only for row / column / filter fields + if (pt.RowLabels.Any(p => p.SourceName == xlpf.SourceName) + || pt.ColumnLabels.Any(p => p.SourceName == xlpf.SourceName) + || pt.ReportFilters.Any(p => p.SourceName == xlpf.SourceName)) + { + foreach (var value in ptfi.DistinctValues) + sharedItems.AppendChild(new DateTimeItem { Val = (DateTime)value }); + } + + sharedItems.MinDate = (DateTime)ptfi.DistinctValues.Min(); + sharedItems.MaxDate = (DateTime)ptfi.DistinctValues.Max(); } else { - foreach (var cellValue in source.Cells() - .Where(cell => cell.Address.ColumnNumber == columnNumber && cell.Address.RowNumber > source.FirstRow().RowNumber()) - .Select(cell => cell.Value.ToString()) - .Where(cellValue => !xlpf.SharedStrings.Select(ss => ss.ToLower()).Contains(cellValue.ToLower()))) + if (types.Any()) { - xlpf.SharedStrings.Add(cellValue); - } + ptfi.DataType = types.First(); + ptfi.MixedDataType = types.Count() > 1; - foreach (var li in xlpf.SharedStrings) - { - sharedItems.AppendChild(new StringItem { Val = li }); + if (!ptfi.MixedDataType && ptfi.DataType == XLCellValues.Text) + ptfi.DistinctValues = fieldValueCells + .Select(cell => cell.Value) + .Cast() + .Distinct(StringComparer.OrdinalIgnoreCase); + else + ptfi.DistinctValues = fieldValueCells + .Select(cell => cell.GetString()) + .Cast() + .Distinct(StringComparer.OrdinalIgnoreCase); + + pti.Fields.Add(xlpf.SourceName, ptfi); + + foreach (var value in ptfi.DistinctValues) + sharedItems.AppendChild(new StringItem { Val = (string)value }); } } + if (ptfi.DistinctValues.Any()) + sharedItems.Count = Convert.ToUInt32(ptfi.DistinctValues.Count()); + var cacheField = new CacheField { Name = xlpf.SourceName }; cacheField.AppendChild(sharedItems); cacheFields.AppendChild(cacheField); @@ -2020,13 +2095,19 @@ var pivotCacheRecords = new PivotCacheRecords(); pivotCacheRecords.AddNamespaceDeclaration("r", - "http://schemas.openxmlformats.org/officeDocument/2006/relationships"); + "http://schemas.openxmlformats.org/officeDocument/2006/relationships"); pivotTableCacheRecordsPart.PivotCacheRecords = pivotCacheRecords; + + context.PivotTables.Add(pti.Guid, pti); } // Generates content of pivotTablePart - private static void GeneratePivotTablePartContent(PivotTablePart pivotTablePart, IXLPivotTable pt, uint cacheId, SaveContext context) + private static void GeneratePivotTablePartContent( + PivotTablePart pivotTablePart, IXLPivotTable pt, + uint cacheId, SaveContext context) { + var pti = context.PivotTables[(pt as XLPivotTable).Guid]; + var pivotTableDefinition = new PivotTableDefinition { Name = pt.Name, @@ -2093,12 +2174,17 @@ var location = new Location { - Reference = pt.TargetCell.Address.ToString(), FirstHeaderRow = 1U, FirstDataRow = 1U, FirstDataColumn = 1U }; + if (pt.ReportFilters.Any()) + // Reference cell is the part BELOW the report filters + location.Reference = pt.TargetCell.CellBelow(pt.ReportFilters.Count() + 1).Address.ToString(); + else + location.Reference = pt.TargetCell.Address.ToString(); + var rowFields = new RowFields(); var columnFields = new ColumnFields(); var rowItems = new RowItems(); @@ -2106,14 +2192,21 @@ var pageFields = new PageFields { Count = (uint)pt.ReportFilters.Count() }; var pivotFields = new PivotFields { Count = Convert.ToUInt32(pt.SourceRange.ColumnCount()) }; - foreach (var xlpf in pt.Fields.OrderBy(f => pt.RowLabels.Any(p => p.SourceName == f.SourceName) ? pt.RowLabels.IndexOf(f) : Int32.MaxValue)) + foreach (var xlpf in pt + .Fields + .Cast() + .OrderBy(f => pt.RowLabels.Any(p => p.SourceName == f.SourceName) + ? pt.RowLabels.IndexOf(f) + : Int32.MaxValue)) { + var ptfi = pti.Fields[xlpf.SourceName]; + if (pt.RowLabels.Any(p => p.SourceName == xlpf.SourceName)) { var f = new Field { Index = pt.Fields.IndexOf(xlpf) }; rowFields.AppendChild(f); - for (var i = 0; i < xlpf.SharedStrings.Count; i++) + for (var i = 0; i < ptfi.DistinctValues.Count(); i++) { var rowItem = new RowItem(); rowItem.AppendChild(new MemberPropertyIndex { Val = i }); @@ -2129,7 +2222,7 @@ var f = new Field { Index = pt.Fields.IndexOf(xlpf) }; columnFields.AppendChild(f); - for (var i = 0; i < xlpf.SharedStrings.Count; i++) + for (var i = 0; i < ptfi.DistinctValues.Count(); i++) { var rowItem = new RowItem(); rowItem.AppendChild(new MemberPropertyIndex { Val = i }); @@ -2154,9 +2247,10 @@ } } - foreach (var xlpf in pt.Fields) + foreach (var xlpf in pt.Fields.Cast()) { - IXLPivotField labelField = null; + var ptfi = pti.Fields[xlpf.SourceName]; + IXLPivotField labelOrFilterField = null; var pf = new PivotField { ShowAll = false, Name = xlpf.CustomName }; if (pt.ClassicPivotTableLayout) @@ -2182,37 +2276,95 @@ break; } - if (pt.RowLabels.Any(p => p.SourceName == xlpf.SourceName)) + if (pt.RowLabels.Contains(xlpf.SourceName)) { - labelField = pt.RowLabels.Single(p => p.SourceName == xlpf.SourceName); + labelOrFilterField = pt.RowLabels.Get(xlpf.SourceName); pf.Axis = PivotTableAxisValues.AxisRow; } - else if (pt.ColumnLabels.Any(p => p.SourceName == xlpf.SourceName)) + else if (pt.ColumnLabels.Contains(xlpf.SourceName)) { - labelField = pt.ColumnLabels.Single(p => p.SourceName == xlpf.SourceName); + labelOrFilterField = pt.ColumnLabels.Get(xlpf.SourceName); pf.Axis = PivotTableAxisValues.AxisColumn; } - else if (pt.ReportFilters.Any(p => p.SourceName == xlpf.SourceName)) + else if (pt.ReportFilters.Contains(xlpf.SourceName)) { + labelOrFilterField = pt.ReportFilters.Get(xlpf.SourceName); + location.ColumnsPerPage = 1; location.RowPageCount = 1; pf.Axis = PivotTableAxisValues.AxisPage; - pageFields.AppendChild(new PageField { Hierarchy = -1, Field = pt.Fields.IndexOf(xlpf) }); + + var pageField = new PageField + { + Hierarchy = -1, + Field = pt.Fields.IndexOf(xlpf) + }; + + if (labelOrFilterField.SelectedValues.Count == 1) + { + if (ptfi.MixedDataType || ptfi.DataType == XLCellValues.Text) + { + var values = ptfi.DistinctValues + .Select(v => v.ToString().ToLower()) + .ToList(); + var selectedValue = labelOrFilterField.SelectedValues.Single().ToString().ToLower(); + if (values.Contains(selectedValue)) + pageField.Item = Convert.ToUInt32(values.IndexOf(selectedValue)); + } + else if (ptfi.DataType == XLCellValues.DateTime) + { + var values = ptfi.DistinctValues + .Select(v => Convert.ToDateTime(v)) + .ToList(); + var selectedValue = Convert.ToDateTime(labelOrFilterField.SelectedValues.Single()); + if (values.Contains(selectedValue)) + pageField.Item = Convert.ToUInt32(values.IndexOf(selectedValue)); + } + else if (ptfi.DataType == XLCellValues.Number) + { + var values = ptfi.DistinctValues + .Select(v => Convert.ToDouble(v)) + .ToList(); + var selectedValue = Convert.ToDouble(labelOrFilterField.SelectedValues.Single()); + if (values.Contains(selectedValue)) + pageField.Item = Convert.ToUInt32(values.IndexOf(selectedValue)); + } + else + throw new NotImplementedException(); + } + + pageFields.AppendChild(pageField); } + if ((labelOrFilterField?.SelectedValues?.Count ?? 0) > 1) + pf.MultipleItemSelectionAllowed = true; + if (pt.Values.Any(p => p.SourceName == xlpf.SourceName)) pf.DataField = true; var fieldItems = new Items(); - if (xlpf.SharedStrings.Any()) + // Output items only for row / column / filter fields + if (ptfi.DistinctValues.Any() + && (pt.RowLabels.Contains(xlpf.SourceName) + || pt.ColumnLabels.Contains(xlpf.SourceName) + || pt.ReportFilters.Contains(xlpf.SourceName))) { - for (uint i = 0; i < xlpf.SharedStrings.Count; i++) + uint i = 0; + foreach (var value in ptfi.DistinctValues) { var item = new Item { Index = i }; - if (labelField != null && labelField.Collapsed) + + if (labelOrFilterField != null && labelOrFilterField.Collapsed) item.HideDetails = BooleanValue.FromBoolean(false); + + if (labelOrFilterField.SelectedValues.Count > 1 + && !labelOrFilterField.SelectedValues.Contains(value)) + item.Hidden = BooleanValue.FromBoolean(true); + fieldItems.AppendChild(item); + + i++; } } @@ -4527,7 +4679,6 @@ } } - var exlst = from c in xlWorksheet.ConditionalFormats where c.ConditionalFormatType == XLConditionalFormatType.DataBar && c.Colors.Count > 1 && typeof(IXLConditionalFormat).IsAssignableFrom(c.GetType()) select c; if (exlst != null && exlst.Count() > 0) { @@ -4580,7 +4731,6 @@ } } - #endregion Conditional Formatting #region DataValidations diff --git a/ClosedXML_Examples/PivotTables/PivotTables.cs b/ClosedXML_Examples/PivotTables/PivotTables.cs index a94bb10..b8f6023 100644 --- a/ClosedXML_Examples/PivotTables/PivotTables.cs +++ b/ClosedXML_Examples/PivotTables/PivotTables.cs @@ -8,41 +8,43 @@ { private class Pastry { - public Pastry(string name, int numberOfOrders, double quality, string month) + public Pastry(string name, int numberOfOrders, double quality, string month, DateTime bakeDate) { Name = name; NumberOfOrders = numberOfOrders; Quality = quality; Month = month; + BakeDate = bakeDate; } public string Name { get; set; } public int NumberOfOrders { get; set; } public double Quality { get; set; } public string Month { get; set; } + public DateTime BakeDate { get; set; } } public void Create(String filePath) { var pastries = new List { - new Pastry("Croissant", 150, 60.2, "Apr"), - new Pastry("Croissant", 250, 50.42, "May"), - new Pastry("Croissant", 134, 22.12, "June"), - new Pastry("Doughnut", 250, 89.99, "Apr"), - new Pastry("Doughnut", 225, 70, "May"), - new Pastry("Doughnut", 210, 75.33, "June"), - new Pastry("Bearclaw", 134, 10.24, "Apr"), - new Pastry("Bearclaw", 184, 33.33, "May"), - new Pastry("Bearclaw", 124, 25, "June"), - new Pastry("Danish", 394, -20.24, "Apr"), - new Pastry("Danish", 190, 60, "May"), - new Pastry("Danish", 221, 24.76, "June"), + new Pastry("Croissant", 150, 60.2, "Apr", new DateTime(2016, 04, 21)), + new Pastry("Croissant", 250, 50.42, "May", new DateTime(2016, 05, 03)), + new Pastry("Croissant", 134, 22.12, "Jun", new DateTime(2016, 06, 24)), + new Pastry("Doughnut", 250, 89.99, "Apr", new DateTime(2017, 04, 23)), + new Pastry("Doughnut", 225, 70, "May", new DateTime(2016, 05, 24)), + new Pastry("Doughnut", 210, 75.33, "Jun", new DateTime(2016, 06, 02)), + new Pastry("Bearclaw", 134, 10.24, "Apr", new DateTime(2016, 04, 27)), + new Pastry("Bearclaw", 184, 33.33, "May", new DateTime(2016, 05, 20)), + new Pastry("Bearclaw", 124, 25, "Jun", new DateTime(2017, 06, 05)), + new Pastry("Danish", 394, -20.24, "Apr", new DateTime(2017, 04, 24)), + new Pastry("Danish", 190, 60, "May", new DateTime(2017, 05, 08)), + new Pastry("Danish", 221, 24.76, "Jun", new DateTime(2016, 06, 21)), // Deliberately add different casings of same string to ensure pivot table doesn't duplicate it. - new Pastry("Scone", 135, 0, "Apr"), - new Pastry("SconE", 122, 5.19, "May"), - new Pastry("SCONE", 243, 44.2, "June") + new Pastry("Scone", 135, 0, "Apr", new DateTime(2017, 04, 22)), + new Pastry("SconE", 122, 5.19, "May", new DateTime(2017, 05, 03)), + new Pastry("SCONE", 243, 44.2, "Jun", new DateTime(2017, 06, 14)), }; using (var wb = new XLWorkbook()) @@ -170,6 +172,28 @@ #endregion Pivot table with subtotals disabled + #region Pivot Table with filter + + ptSheet = wb.Worksheets.Add("pvtFilter"); + + pt = ptSheet.PivotTables.AddNew("pvtFilter", ptSheet.Cell(1, 1), dataRange); + + pt.RowLabels.Add("Month"); + + pt.Values.Add("NumberOfOrders").SetSummaryFormula(XLPivotSummary.Sum); + + pt.ReportFilters.Add("Name") + .AddSelectedValue("Scone") + .AddSelectedValue("Doughnut"); + + pt.ReportFilters.Add("Quality") + .AddSelectedValue(5.19); + + pt.ReportFilters.Add("BakeDate") + .AddSelectedValue(new DateTime(2017, 05, 03)); + + #endregion Pivot Table with filter + wb.SaveAs(filePath); } } diff --git a/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx b/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx index 247daba..61f9aee 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/TestHelper.cs b/ClosedXML_Tests/TestHelper.cs index bf85115..646d9d0 100644 --- a/ClosedXML_Tests/TestHelper.cs +++ b/ClosedXML_Tests/TestHelper.cs @@ -75,36 +75,23 @@ using (var wb = new XLWorkbook(filePath1)) wb.SaveAs(filePath2, true, evaluateFormulae); - bool success = true; -#pragma warning disable 162 - try + if (CompareWithResources) + { - //Compare - // ReSharper disable ConditionIsAlwaysTrueOrFalse - if (CompareWithResources) - // ReSharper restore ConditionIsAlwaysTrueOrFalse - + string resourcePath = filePartName.Replace('\\', '.').TrimStart('.'); + using (var streamExpected = _extractor.ReadFileFromResToStream(resourcePath)) + using (var streamActual = File.OpenRead(filePath2)) { - string resourcePath = filePartName.Replace('\\', '.').TrimStart('.'); - using (var streamExpected = _extractor.ReadFileFromResToStream(resourcePath)) - using (var streamActual = File.OpenRead(filePath2)) - { - string message; - success = ExcelDocsComparer.Compare(streamActual, streamExpected, TestHelper.IsRunningOnUnix, out message); - var formattedMessage = - String.Format( - "Actual file '{0}' is different than the expected file '{1}'. The difference is: '{2}'", - filePath2, resourcePath, message); + string message; + var success = ExcelDocsComparer.Compare(streamActual, streamExpected, TestHelper.IsRunningOnUnix, out message); + var formattedMessage = + String.Format( + "Actual file '{0}' is different than the expected file '{1}'. The difference is: '{2}'", + filePath2, resourcePath, message); - Assert.IsTrue(success, formattedMessage); - } + Assert.IsTrue(success, formattedMessage); } } - finally - { - //if (success && File.Exists(filePath)) File.Delete(filePath); - } -#pragma warning restore 162 } public static string GetResourcePath(string filePartName)