// Generates content of pivotTablePart private static void GeneratePivotTablePartContent(PivotTablePart pivotTablePart1, IXLPivotTable pt) { var pivotTableDefinition = new PivotTableDefinition { Name = pt.Name, CacheId = 0U, DataCaption = "Values", MergeItem = GetBooleanValue(pt.MergeAndCenterWithLabels, true), Indent = Convert.ToUInt32(pt.RowLabelIndent), PageOverThenDown = (pt.FilterAreaOrder == XLFilterAreaOrder.OverThenDown), PageWrap = Convert.ToUInt32(pt.FilterFieldsPageWrap), ShowError = String.IsNullOrEmpty(pt.ErrorValueReplacement), UseAutoFormatting = GetBooleanValue(pt.AutofitColumns, true), PreserveFormatting = GetBooleanValue(pt.PreserveCellFormatting, true), RowGrandTotals = GetBooleanValue(pt.ShowGrandTotalsRows, true), ColumnGrandTotals = GetBooleanValue(pt.ShowGrandTotalsColumns, true), SubtotalHiddenItems = GetBooleanValue(pt.FilteredItemsInSubtotals, true), MultipleFieldFilters = GetBooleanValue(pt.AllowMultipleFilters, true), CustomListSort = GetBooleanValue(pt.UseCustomListsForSorting, true), ShowDrill = GetBooleanValue(pt.ShowExpandCollapseButtons, true), ShowDataTips = GetBooleanValue(pt.ShowContextualTooltips, true), ShowMemberPropertyTips = GetBooleanValue(pt.ShowPropertiesInTooltips, true), ShowHeaders = GetBooleanValue(pt.DisplayCaptionsAndDropdowns, true), GridDropZones = GetBooleanValue(pt.ClassicPivotTableLayout, true), ShowEmptyRow = GetBooleanValue(pt.ShowEmptyItemsOnRows, true), ShowEmptyColumn = GetBooleanValue(pt.ShowEmptyItemsOnColumns, true), ShowItems = GetBooleanValue(pt.DisplayItemLabels, true), FieldListSortAscending = GetBooleanValue(pt.SortFieldsAtoZ, true), PrintDrill = GetBooleanValue(pt.PrintExpandCollapsedButtons, true), ItemPrintTitles = GetBooleanValue(pt.RepeatRowLabels, true), FieldPrintTitles = GetBooleanValue(pt.PrintTitles, true), EnableDrill = GetBooleanValue(pt.EnableShowDetails, true) }; if (pt.EmptyCellReplacement != null) { pivotTableDefinition.ShowMissing = true; pivotTableDefinition.MissingCaption = pt.EmptyCellReplacement; } else { pivotTableDefinition.ShowMissing = false; } if (pt.ErrorValueReplacement != null) { pivotTableDefinition.ShowError = true; pivotTableDefinition.ErrorCaption = pt.ErrorValueReplacement; } else { pivotTableDefinition.ShowError = false; } var location = new Location { Reference = pt.TargetCell.Address.ToString(), FirstHeaderRow = 1U, FirstDataRow = 1U, FirstDataColumn = 1U }; var rowFields = new RowFields(); var columnFields = new ColumnFields(); var rowItems = new RowItems(); var columnItems = new ColumnItems(); 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) { var pf = new PivotField {ShowAll = false, Name = xlpf.CustomName}; if (pt.RowLabels.FirstOrDefault(p => p.SourceName == xlpf.SourceName) != null) { pf.Axis = PivotTableAxisValues.AxisRow; var f = new Field {Index = pt.Fields.IndexOf(xlpf)}; rowFields.AppendChild(f); for (var i = 0; i < xlpf.SharedStrings.Count; i++) { var rowItem = new RowItem(); rowItem.AppendChild(new MemberPropertyIndex {Val = i}); rowItems.AppendChild(rowItem); } var rowItemTotal = new RowItem {ItemType = ItemValues.Grand}; rowItemTotal.AppendChild(new MemberPropertyIndex()); rowItems.AppendChild(rowItemTotal); } else if (pt.ColumnLabels.FirstOrDefault(p => p.SourceName == xlpf.SourceName) != null) { pf.Axis = PivotTableAxisValues.AxisColumn; var f = new Field {Index = pt.Fields.IndexOf(xlpf)}; columnFields.AppendChild(f); for (var i = 0; i < xlpf.SharedStrings.Count; i++) { var rowItem = new RowItem(); rowItem.AppendChild(new MemberPropertyIndex {Val = i}); columnItems.AppendChild(rowItem); } var rowItemTotal = new RowItem {ItemType = ItemValues.Grand}; rowItemTotal.AppendChild(new MemberPropertyIndex()); columnItems.AppendChild(rowItemTotal); } else if (pt.ReportFilters.FirstOrDefault(p => p.SourceName == xlpf.SourceName) != null) { location.ColumnsPerPage = 1; location.RowPageCount = 1; pf.Axis = PivotTableAxisValues.AxisPage; pageFields.AppendChild(new PageField {Hierarchy = -1, Field = pt.Fields.IndexOf(xlpf)}); } else if (pt.Values.FirstOrDefault(p => p.CustomName == xlpf.SourceName) != null) { pf.DataField = true; } var fieldItems = new Items(); if (xlpf.SharedStrings.Count > 0) { for (uint i = 0; i < xlpf.SharedStrings.Count; i++) { fieldItems.AppendChild(new Item {Index = i}); } } if (xlpf.Subtotals.Count > 0) { foreach (var subtotal in xlpf.Subtotals) { var itemSubtotal = new Item(); switch (subtotal) { case XLSubtotalFunction.Average: pf.AverageSubTotal = true; itemSubtotal.ItemType = ItemValues.Average; break; case XLSubtotalFunction.Count: pf.CountASubtotal = true; itemSubtotal.ItemType = ItemValues.CountA; break; case XLSubtotalFunction.CountNumbers: pf.CountSubtotal = true; itemSubtotal.ItemType = ItemValues.Count; break; case XLSubtotalFunction.Maximum: pf.MaxSubtotal = true; itemSubtotal.ItemType = ItemValues.Maximum; break; case XLSubtotalFunction.Minimum: pf.MinSubtotal = true; itemSubtotal.ItemType = ItemValues.Minimum; break; case XLSubtotalFunction.PopulationStandardDeviation: pf.ApplyStandardDeviationPInSubtotal = true; itemSubtotal.ItemType = ItemValues.StandardDeviationP; break; case XLSubtotalFunction.PopulationVariance: pf.ApplyVariancePInSubtotal = true; itemSubtotal.ItemType = ItemValues.VarianceP; break; case XLSubtotalFunction.Product: pf.ApplyProductInSubtotal = true; itemSubtotal.ItemType = ItemValues.Product; break; case XLSubtotalFunction.StandardDeviation: pf.ApplyStandardDeviationInSubtotal = true; itemSubtotal.ItemType = ItemValues.StandardDeviation; break; case XLSubtotalFunction.Sum: pf.SumSubtotal = true; itemSubtotal.ItemType = ItemValues.Sum; break; case XLSubtotalFunction.Variance: pf.ApplyVarianceInSubtotal = true; itemSubtotal.ItemType = ItemValues.Variance; break; } fieldItems.AppendChild(itemSubtotal); } } else { fieldItems.AppendChild(new Item {ItemType = ItemValues.Default}); } pf.AppendChild(fieldItems); pivotFields.AppendChild(pf); } pivotTableDefinition.AppendChild(location); pivotTableDefinition.AppendChild(pivotFields); if (pt.RowLabels.Any()) { pivotTableDefinition.AppendChild(rowFields); } else { rowItems.AppendChild(new RowItem()); } pivotTableDefinition.AppendChild(rowItems); if (!pt.ColumnLabels.Any()) { columnItems.AppendChild(new RowItem()); pivotTableDefinition.AppendChild(columnItems); } else { pivotTableDefinition.AppendChild(columnFields); pivotTableDefinition.AppendChild(columnItems); } if (pt.ReportFilters.Any()) { pivotTableDefinition.AppendChild(pageFields); } var dataFields = new DataFields(); foreach (var value in pt.Values) { var sourceColumn = pt.SourceRange.Columns().FirstOrDefault(c => c.Cell(1).Value.ToString() == value.SourceName); if (sourceColumn == null) continue; var df = new DataField { Name = value.SourceName, Field = (UInt32)sourceColumn.ColumnNumber() - 1, Subtotal = value.SummaryFormula.ToOpenXml(), ShowDataAs = value.Calculation.ToOpenXml(), NumberFormatId = (UInt32)value.NumberFormat.NumberFormatId }; if (!String.IsNullOrEmpty(value.BaseField)) { var baseField = pt.SourceRange.Columns().FirstOrDefault(c => c.Cell(1).Value.ToString() == value.BaseField); if (baseField != null) df.BaseField = baseField.ColumnNumber() - 1; } else { df.BaseField = 0; } if (value.CalculationItem == XLPivotCalculationItem.Previous) df.BaseItem = 1048828U; else if (value.CalculationItem == XLPivotCalculationItem.Next) df.BaseItem = 1048829U; else df.BaseItem = 0U; dataFields.AppendChild(df); } pivotTableDefinition.AppendChild(dataFields); pivotTableDefinition.AppendChild(new PivotTableStyle { Name = Enum.GetName(typeof (XLPivotTableTheme), pt.Theme), ShowRowHeaders = pt.ShowRowHeaders, ShowColumnHeaders = pt.ShowColumnHeaders, ShowRowStripes = pt.ShowRowStripes, ShowColumnStripes = pt.ShowColumnStripes }); #region Excel 2010 Features var pivotTableDefinitionExtensionList = new PivotTableDefinitionExtensionList(); var pivotTableDefinitionExtension = new PivotTableDefinitionExtension {Uri = "{962EF5D1-5CA2-4c93-8EF4-DBF5C05439D2}"}; pivotTableDefinitionExtension.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main"); var pivotTableDefinition2 = new DocumentFormat.OpenXml.Office2010.Excel.PivotTableDefinition {EnableEdit = pt.EnableCellEditing, HideValuesRow = !pt.ShowValuesRow}; pivotTableDefinition2.AddNamespaceDeclaration("xm", "http://schemas.microsoft.com/office/excel/2006/main"); pivotTableDefinitionExtension.AppendChild(pivotTableDefinition2); pivotTableDefinitionExtensionList.AppendChild(pivotTableDefinitionExtension); pivotTableDefinition.AppendChild(pivotTableDefinitionExtensionList); #endregion pivotTablePart1.PivotTableDefinition = pivotTableDefinition; }