// 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;
}
internal PivotTableDefinition ToPivotTableDefinition()
{
PivotTableDefinition ptd = new PivotTableDefinition();
ptd.Name = this.Name;
ptd.CacheId = this.CacheId;
if (this.DataOnRows != false) ptd.DataOnRows = this.DataOnRows;
if (this.DataPosition != null) ptd.DataPosition = this.DataPosition.Value;
if (this.AutoFormatId != null) ptd.AutoFormatId = this.AutoFormatId.Value;
if (this.ApplyNumberFormats != null) ptd.ApplyNumberFormats = this.ApplyNumberFormats.Value;
if (this.ApplyBorderFormats != null) ptd.ApplyBorderFormats = this.ApplyBorderFormats.Value;
if (this.ApplyFontFormats != null) ptd.ApplyFontFormats = this.ApplyFontFormats.Value;
if (this.ApplyPatternFormats != null) ptd.ApplyPatternFormats = this.ApplyPatternFormats.Value;
if (this.ApplyAlignmentFormats != null) ptd.ApplyAlignmentFormats = this.ApplyAlignmentFormats.Value;
if (this.ApplyWidthHeightFormats != null) ptd.ApplyWidthHeightFormats = this.ApplyWidthHeightFormats.Value;
if (this.DataCaption != null && this.DataCaption.Length > 0) ptd.DataCaption = this.DataCaption;
if (this.GrandTotalCaption != null && this.GrandTotalCaption.Length > 0) ptd.GrandTotalCaption = this.GrandTotalCaption;
if (this.ErrorCaption != null && this.ErrorCaption.Length > 0) ptd.ErrorCaption = this.ErrorCaption;
if (this.ShowError != false) ptd.ShowError = this.ShowError;
if (this.MissingCaption != null && this.MissingCaption.Length > 0) ptd.MissingCaption = this.MissingCaption;
if (this.ShowMissing != true) ptd.ShowMissing = this.ShowMissing;
if (this.PageStyle != null && this.PageStyle.Length > 0) ptd.PageStyle = this.PageStyle;
if (this.PivotTableStyleName != null && this.PivotTableStyleName.Length > 0) ptd.PivotTableStyleName = this.PivotTableStyleName;
if (this.VacatedStyle != null && this.VacatedStyle.Length > 0) ptd.VacatedStyle = this.VacatedStyle;
if (this.Tag != null && this.Tag.Length > 0) ptd.Tag = this.Tag;
if (this.UpdatedVersion != 0) ptd.UpdatedVersion = this.UpdatedVersion;
if (this.MinRefreshableVersion != 0) ptd.MinRefreshableVersion = this.MinRefreshableVersion;
if (this.AsteriskTotals != false) ptd.AsteriskTotals = this.AsteriskTotals;
if (this.ShowItems != true) ptd.ShowItems = this.ShowItems;
if (this.EditData != false) ptd.EditData = this.EditData;
if (this.DisableFieldList != false) ptd.DisableFieldList = this.DisableFieldList;
if (this.ShowCalculatedMembers != true) ptd.ShowCalculatedMembers = this.ShowCalculatedMembers;
if (this.VisualTotals != true) ptd.VisualTotals = this.VisualTotals;
if (this.ShowMultipleLabel != true) ptd.ShowMultipleLabel = this.ShowMultipleLabel;
if (this.ShowDataDropDown != true) ptd.ShowDataDropDown = this.ShowDataDropDown;
if (this.ShowDrill != true) ptd.ShowDrill = this.ShowDrill;
if (this.PrintDrill != false) ptd.PrintDrill = this.PrintDrill;
if (this.ShowMemberPropertyTips != true) ptd.ShowMemberPropertyTips = this.ShowMemberPropertyTips;
if (this.ShowDataTips != true) ptd.ShowDataTips = this.ShowDataTips;
if (this.EnableWizard != true) ptd.EnableWizard = this.EnableWizard;
if (this.EnableDrill != true) ptd.EnableDrill = this.EnableDrill;
if (this.EnableFieldProperties != true) ptd.EnableFieldProperties = this.EnableFieldProperties;
if (this.PreserveFormatting != true) ptd.PreserveFormatting = this.PreserveFormatting;
if (this.UseAutoFormatting != false) ptd.UseAutoFormatting = this.UseAutoFormatting;
if (this.PageWrap != 0) ptd.PageWrap = this.PageWrap;
if (this.PageOverThenDown != false) ptd.PageOverThenDown = this.PageOverThenDown;
if (this.SubtotalHiddenItems != false) ptd.SubtotalHiddenItems = this.SubtotalHiddenItems;
if (this.RowGrandTotals != true) ptd.RowGrandTotals = this.RowGrandTotals;
if (this.ColumnGrandTotals != true) ptd.ColumnGrandTotals = this.ColumnGrandTotals;
if (this.FieldPrintTitles != false) ptd.FieldPrintTitles = this.FieldPrintTitles;
if (this.ItemPrintTitles != false) ptd.ItemPrintTitles = this.ItemPrintTitles;
if (this.MergeItem != false) ptd.MergeItem = this.MergeItem;
if (this.ShowDropZones != true) ptd.ShowDropZones = this.ShowDropZones;
if (this.CreatedVersion != 0) ptd.CreatedVersion = this.CreatedVersion;
if (this.Indent != 1) ptd.Indent = this.Indent;
if (this.ShowEmptyRow != false) ptd.ShowEmptyRow = this.ShowEmptyRow;
if (this.ShowEmptyColumn != false) ptd.ShowEmptyColumn = this.ShowEmptyColumn;
if (this.ShowHeaders != true) ptd.ShowHeaders = this.ShowHeaders;
if (this.Compact != true) ptd.Compact = this.Compact;
if (this.Outline != false) ptd.Outline = this.Outline;
if (this.OutlineData != false) ptd.OutlineData = this.OutlineData;
if (this.CompactData != true) ptd.CompactData = this.CompactData;
if (this.Published != false) ptd.Published = this.Published;
if (this.GridDropZones != false) ptd.GridDropZones = this.GridDropZones;
if (this.StopImmersiveUi != true) ptd.StopImmersiveUi = this.StopImmersiveUi;
if (this.MultipleFieldFilters != true) ptd.MultipleFieldFilters = this.MultipleFieldFilters;
if (this.ChartFormat != 0) ptd.ChartFormat = this.ChartFormat;
if (this.RowHeaderCaption != null && this.RowHeaderCaption.Length > 0) ptd.RowHeaderCaption = this.RowHeaderCaption;
if (this.ColumnHeaderCaption != null && this.ColumnHeaderCaption.Length > 0) ptd.ColumnHeaderCaption = this.ColumnHeaderCaption;
if (this.FieldListSortAscending != false) ptd.FieldListSortAscending = this.FieldListSortAscending;
if (this.CustomListSort != true) ptd.CustomListSort = this.CustomListSort;
ptd.Location = this.Location.ToLocation();
if (this.PivotFields.Count > 0)
{
ptd.PivotFields = new PivotFields() { Count = (uint)this.PivotFields.Count };
foreach (SLPivotField pf in this.PivotFields)
{
ptd.PivotFields.Append(pf.ToPivotField());
}
}
if (this.RowFields.Count > 0)
{
ptd.RowFields = new RowFields() { Count = (uint)this.RowFields.Count };
foreach (int i in this.RowFields)
{
ptd.RowFields.Append(new Field() { Index = i });
}
}
if (this.RowItems.Count > 0)
{
ptd.RowItems = new RowItems() { Count = (uint)this.RowItems.Count };
foreach (SLRowItem ri in this.RowItems)
{
ptd.RowItems.Append(ri.ToRowItem());
}
}
if (this.ColumnFields.Count > 0)
{
ptd.ColumnFields = new ColumnFields() { Count = (uint)this.ColumnFields.Count };
foreach (int i in this.ColumnFields)
{
ptd.ColumnFields.Append(new Field() { Index = i });
}
}
if (this.ColumnItems.Count > 0)
{
ptd.ColumnItems = new ColumnItems() { Count = (uint)this.ColumnItems.Count };
foreach (SLRowItem ri in this.ColumnItems)
{
ptd.ColumnItems.Append(ri.ToRowItem());
}
}
if (this.PageFields.Count > 0)
{
ptd.PageFields = new PageFields() { Count = (uint)this.PageFields.Count };
foreach (SLPageField pf in this.PageFields)
{
ptd.PageFields.Append(pf.ToPageField());
}
}
if (this.DataFields.Count > 0)
{
ptd.DataFields = new DataFields() { Count = (uint)this.DataFields.Count };
foreach (SLDataField df in this.DataFields)
{
ptd.DataFields.Append(df.ToDataField());
}
}
if (this.Formats.Count > 0)
{
ptd.Formats = new Formats() { Count = (uint)this.Formats.Count };
foreach (SLFormat f in this.Formats)
{
ptd.Formats.Append(f.ToFormat());
}
}
if (this.ConditionalFormats.Count > 0)
{
ptd.ConditionalFormats = new ConditionalFormats() { Count = (uint)this.ConditionalFormats.Count };
foreach (SLConditionalFormat cf in this.ConditionalFormats)
{
ptd.ConditionalFormats.Append(cf.ToConditionalFormat());
}
}
if (this.ChartFormats.Count > 0)
{
ptd.ChartFormats = new ChartFormats() { Count = (uint)this.ChartFormats.Count };
foreach (SLChartFormat cf in this.ChartFormats)
{
ptd.ChartFormats.Append(cf.ToChartFormat());
}
}
if (this.PivotHierarchies.Count > 0)
{
ptd.PivotHierarchies = new PivotHierarchies() { Count = (uint)this.PivotHierarchies.Count };
foreach (SLPivotHierarchy ph in this.PivotHierarchies)
{
ptd.PivotHierarchies.Append(ph.ToPivotHierarchy());
}
}
ptd.PivotTableStyle = this.PivotTableStyle.ToPivotTableStyle();
if (this.PivotFilters.Count > 0)
{
ptd.PivotFilters = new PivotFilters() { Count = (uint)this.PivotFilters.Count };
foreach (SLPivotFilter pf in this.PivotFilters)
{
ptd.PivotFilters.Append(pf.ToPivotFilter());
}
}
if (this.RowHierarchiesUsage.Count > 0)
{
ptd.RowHierarchiesUsage = new RowHierarchiesUsage() { Count = (uint)this.RowHierarchiesUsage.Count };
foreach (int i in this.RowHierarchiesUsage)
{
ptd.RowHierarchiesUsage.Append(new RowHierarchyUsage() { Value = i });
}
}
if (this.ColumnHierarchiesUsage.Count > 0)
{
ptd.ColumnHierarchiesUsage = new ColumnHierarchiesUsage() { Count = (uint)this.ColumnHierarchiesUsage.Count };
foreach (int i in this.ColumnHierarchiesUsage)
{
ptd.ColumnHierarchiesUsage.Append(new ColumnHierarchyUsage() { Value = i });
}
}
return ptd;
}
internal PivotTableDefinition ToPivotTableDefinition()
{
PivotTableDefinition ptd = new PivotTableDefinition();
ptd.Name = this.Name;
ptd.CacheId = this.CacheId;
if (this.DataOnRows != false)
{
ptd.DataOnRows = this.DataOnRows;
}
if (this.DataPosition != null)
{
ptd.DataPosition = this.DataPosition.Value;
}
if (this.AutoFormatId != null)
{
ptd.AutoFormatId = this.AutoFormatId.Value;
}
if (this.ApplyNumberFormats != null)
{
ptd.ApplyNumberFormats = this.ApplyNumberFormats.Value;
}
if (this.ApplyBorderFormats != null)
{
ptd.ApplyBorderFormats = this.ApplyBorderFormats.Value;
}
if (this.ApplyFontFormats != null)
{
ptd.ApplyFontFormats = this.ApplyFontFormats.Value;
}
if (this.ApplyPatternFormats != null)
{
ptd.ApplyPatternFormats = this.ApplyPatternFormats.Value;
}
if (this.ApplyAlignmentFormats != null)
{
ptd.ApplyAlignmentFormats = this.ApplyAlignmentFormats.Value;
}
if (this.ApplyWidthHeightFormats != null)
{
ptd.ApplyWidthHeightFormats = this.ApplyWidthHeightFormats.Value;
}
if (this.DataCaption != null && this.DataCaption.Length > 0)
{
ptd.DataCaption = this.DataCaption;
}
if (this.GrandTotalCaption != null && this.GrandTotalCaption.Length > 0)
{
ptd.GrandTotalCaption = this.GrandTotalCaption;
}
if (this.ErrorCaption != null && this.ErrorCaption.Length > 0)
{
ptd.ErrorCaption = this.ErrorCaption;
}
if (this.ShowError != false)
{
ptd.ShowError = this.ShowError;
}
if (this.MissingCaption != null && this.MissingCaption.Length > 0)
{
ptd.MissingCaption = this.MissingCaption;
}
if (this.ShowMissing != true)
{
ptd.ShowMissing = this.ShowMissing;
}
if (this.PageStyle != null && this.PageStyle.Length > 0)
{
ptd.PageStyle = this.PageStyle;
}
if (this.PivotTableStyleName != null && this.PivotTableStyleName.Length > 0)
{
ptd.PivotTableStyleName = this.PivotTableStyleName;
}
if (this.VacatedStyle != null && this.VacatedStyle.Length > 0)
{
ptd.VacatedStyle = this.VacatedStyle;
}
if (this.Tag != null && this.Tag.Length > 0)
{
ptd.Tag = this.Tag;
}
if (this.UpdatedVersion != 0)
{
ptd.UpdatedVersion = this.UpdatedVersion;
}
if (this.MinRefreshableVersion != 0)
{
ptd.MinRefreshableVersion = this.MinRefreshableVersion;
}
if (this.AsteriskTotals != false)
{
ptd.AsteriskTotals = this.AsteriskTotals;
}
if (this.ShowItems != true)
{
ptd.ShowItems = this.ShowItems;
}
if (this.EditData != false)
{
ptd.EditData = this.EditData;
}
if (this.DisableFieldList != false)
{
ptd.DisableFieldList = this.DisableFieldList;
}
if (this.ShowCalculatedMembers != true)
{
ptd.ShowCalculatedMembers = this.ShowCalculatedMembers;
}
if (this.VisualTotals != true)
{
ptd.VisualTotals = this.VisualTotals;
}
if (this.ShowMultipleLabel != true)
{
ptd.ShowMultipleLabel = this.ShowMultipleLabel;
}
if (this.ShowDataDropDown != true)
{
ptd.ShowDataDropDown = this.ShowDataDropDown;
}
if (this.ShowDrill != true)
{
ptd.ShowDrill = this.ShowDrill;
}
if (this.PrintDrill != false)
{
ptd.PrintDrill = this.PrintDrill;
}
if (this.ShowMemberPropertyTips != true)
{
ptd.ShowMemberPropertyTips = this.ShowMemberPropertyTips;
}
if (this.ShowDataTips != true)
{
ptd.ShowDataTips = this.ShowDataTips;
}
if (this.EnableWizard != true)
{
ptd.EnableWizard = this.EnableWizard;
}
if (this.EnableDrill != true)
{
ptd.EnableDrill = this.EnableDrill;
}
if (this.EnableFieldProperties != true)
{
ptd.EnableFieldProperties = this.EnableFieldProperties;
}
if (this.PreserveFormatting != true)
{
ptd.PreserveFormatting = this.PreserveFormatting;
}
if (this.UseAutoFormatting != false)
{
ptd.UseAutoFormatting = this.UseAutoFormatting;
}
if (this.PageWrap != 0)
{
ptd.PageWrap = this.PageWrap;
}
if (this.PageOverThenDown != false)
{
ptd.PageOverThenDown = this.PageOverThenDown;
}
if (this.SubtotalHiddenItems != false)
{
ptd.SubtotalHiddenItems = this.SubtotalHiddenItems;
}
if (this.RowGrandTotals != true)
{
ptd.RowGrandTotals = this.RowGrandTotals;
}
if (this.ColumnGrandTotals != true)
{
ptd.ColumnGrandTotals = this.ColumnGrandTotals;
}
if (this.FieldPrintTitles != false)
{
ptd.FieldPrintTitles = this.FieldPrintTitles;
}
if (this.ItemPrintTitles != false)
{
ptd.ItemPrintTitles = this.ItemPrintTitles;
}
if (this.MergeItem != false)
{
ptd.MergeItem = this.MergeItem;
}
if (this.ShowDropZones != true)
{
ptd.ShowDropZones = this.ShowDropZones;
}
if (this.CreatedVersion != 0)
{
ptd.CreatedVersion = this.CreatedVersion;
}
if (this.Indent != 1)
{
ptd.Indent = this.Indent;
}
if (this.ShowEmptyRow != false)
{
ptd.ShowEmptyRow = this.ShowEmptyRow;
}
if (this.ShowEmptyColumn != false)
{
ptd.ShowEmptyColumn = this.ShowEmptyColumn;
}
if (this.ShowHeaders != true)
{
ptd.ShowHeaders = this.ShowHeaders;
}
if (this.Compact != true)
{
ptd.Compact = this.Compact;
}
if (this.Outline != false)
{
ptd.Outline = this.Outline;
}
if (this.OutlineData != false)
{
ptd.OutlineData = this.OutlineData;
}
if (this.CompactData != true)
{
ptd.CompactData = this.CompactData;
}
if (this.Published != false)
{
ptd.Published = this.Published;
}
if (this.GridDropZones != false)
{
ptd.GridDropZones = this.GridDropZones;
}
if (this.StopImmersiveUi != true)
{
ptd.StopImmersiveUi = this.StopImmersiveUi;
}
if (this.MultipleFieldFilters != true)
{
ptd.MultipleFieldFilters = this.MultipleFieldFilters;
}
if (this.ChartFormat != 0)
{
ptd.ChartFormat = this.ChartFormat;
}
if (this.RowHeaderCaption != null && this.RowHeaderCaption.Length > 0)
{
ptd.RowHeaderCaption = this.RowHeaderCaption;
}
if (this.ColumnHeaderCaption != null && this.ColumnHeaderCaption.Length > 0)
{
ptd.ColumnHeaderCaption = this.ColumnHeaderCaption;
}
if (this.FieldListSortAscending != false)
{
ptd.FieldListSortAscending = this.FieldListSortAscending;
}
if (this.CustomListSort != true)
{
ptd.CustomListSort = this.CustomListSort;
}
ptd.Location = this.Location.ToLocation();
if (this.PivotFields.Count > 0)
{
ptd.PivotFields = new PivotFields()
{
Count = (uint)this.PivotFields.Count
};
foreach (SLPivotField pf in this.PivotFields)
{
ptd.PivotFields.Append(pf.ToPivotField());
}
}
if (this.RowFields.Count > 0)
{
ptd.RowFields = new RowFields()
{
Count = (uint)this.RowFields.Count
};
foreach (int i in this.RowFields)
{
ptd.RowFields.Append(new Field()
{
Index = i
});
}
}
if (this.RowItems.Count > 0)
{
ptd.RowItems = new RowItems()
{
Count = (uint)this.RowItems.Count
};
foreach (SLRowItem ri in this.RowItems)
{
ptd.RowItems.Append(ri.ToRowItem());
}
}
if (this.ColumnFields.Count > 0)
{
ptd.ColumnFields = new ColumnFields()
{
Count = (uint)this.ColumnFields.Count
};
foreach (int i in this.ColumnFields)
{
ptd.ColumnFields.Append(new Field()
{
Index = i
});
}
}
if (this.ColumnItems.Count > 0)
{
ptd.ColumnItems = new ColumnItems()
{
Count = (uint)this.ColumnItems.Count
};
foreach (SLRowItem ri in this.ColumnItems)
{
ptd.ColumnItems.Append(ri.ToRowItem());
}
}
if (this.PageFields.Count > 0)
{
ptd.PageFields = new PageFields()
{
Count = (uint)this.PageFields.Count
};
foreach (SLPageField pf in this.PageFields)
{
ptd.PageFields.Append(pf.ToPageField());
}
}
if (this.DataFields.Count > 0)
{
ptd.DataFields = new DataFields()
{
Count = (uint)this.DataFields.Count
};
foreach (SLDataField df in this.DataFields)
{
ptd.DataFields.Append(df.ToDataField());
}
}
if (this.Formats.Count > 0)
{
ptd.Formats = new Formats()
{
Count = (uint)this.Formats.Count
};
foreach (SLFormat f in this.Formats)
{
ptd.Formats.Append(f.ToFormat());
}
}
if (this.ConditionalFormats.Count > 0)
{
ptd.ConditionalFormats = new ConditionalFormats()
{
Count = (uint)this.ConditionalFormats.Count
};
foreach (SLConditionalFormat cf in this.ConditionalFormats)
{
ptd.ConditionalFormats.Append(cf.ToConditionalFormat());
}
}
if (this.ChartFormats.Count > 0)
{
ptd.ChartFormats = new ChartFormats()
{
Count = (uint)this.ChartFormats.Count
};
foreach (SLChartFormat cf in this.ChartFormats)
{
ptd.ChartFormats.Append(cf.ToChartFormat());
}
}
if (this.PivotHierarchies.Count > 0)
{
ptd.PivotHierarchies = new PivotHierarchies()
{
Count = (uint)this.PivotHierarchies.Count
};
foreach (SLPivotHierarchy ph in this.PivotHierarchies)
{
ptd.PivotHierarchies.Append(ph.ToPivotHierarchy());
}
}
ptd.PivotTableStyle = this.PivotTableStyle.ToPivotTableStyle();
if (this.PivotFilters.Count > 0)
{
ptd.PivotFilters = new PivotFilters()
{
Count = (uint)this.PivotFilters.Count
};
foreach (SLPivotFilter pf in this.PivotFilters)
{
ptd.PivotFilters.Append(pf.ToPivotFilter());
}
}
if (this.RowHierarchiesUsage.Count > 0)
{
ptd.RowHierarchiesUsage = new RowHierarchiesUsage()
{
Count = (uint)this.RowHierarchiesUsage.Count
};
foreach (int i in this.RowHierarchiesUsage)
{
ptd.RowHierarchiesUsage.Append(new RowHierarchyUsage()
{
Value = i
});
}
}
if (this.ColumnHierarchiesUsage.Count > 0)
{
ptd.ColumnHierarchiesUsage = new ColumnHierarchiesUsage()
{
Count = (uint)this.ColumnHierarchiesUsage.Count
};
foreach (int i in this.ColumnHierarchiesUsage)
{
ptd.ColumnHierarchiesUsage.Append(new ColumnHierarchyUsage()
{
Value = i
});
}
}
return(ptd);
}
// Generates content of pivotTablePart1.
private void GeneratePivotTablePart1Content(PivotTablePart pivotTablePart1)
{
PivotTableDefinition pivotTableDefinition1 = new PivotTableDefinition() { Name = "PivotTable1", CacheId = (UInt32Value)2U, ApplyNumberFormats = false, ApplyBorderFormats = false, ApplyFontFormats = false, ApplyPatternFormats = false, ApplyAlignmentFormats = false, ApplyWidthHeightFormats = true, DataCaption = "Values", UpdatedVersion = 4, MinRefreshableVersion = 3, UseAutoFormatting = true, ItemPrintTitles = true, CreatedVersion = 4, Indent = (UInt32Value)0U, Outline = true, OutlineData = true, MultipleFieldFilters = false };
Location location1 = new Location() { Reference = "A1:B5", FirstHeaderRow = (UInt32Value)1U, FirstDataRow = (UInt32Value)1U, FirstDataColumn = (UInt32Value)1U };
PivotFields pivotFields1 = new PivotFields() { Count = (UInt32Value)2U };
PivotField pivotField1 = new PivotField() { Axis = PivotTableAxisValues.AxisRow, ShowAll = false };
Items items1 = new Items() { Count = (UInt32Value)4U };
Item item1 = new Item() { Index = (UInt32Value)0U };
Item item2 = new Item() { Index = (UInt32Value)1U };
Item item3 = new Item() { Index = (UInt32Value)2U };
Item item4 = new Item() { ItemType = ItemValues.Default };
items1.Append(item1);
items1.Append(item2);
items1.Append(item3);
items1.Append(item4);
pivotField1.Append(items1);
PivotField pivotField2 = new PivotField() { DataField = true, ShowAll = false };
pivotFields1.Append(pivotField1);
pivotFields1.Append(pivotField2);
RowFields rowFields1 = new RowFields() { Count = (UInt32Value)1U };
Field field1 = new Field() { Index = 0 };
rowFields1.Append(field1);
RowItems rowItems1 = new RowItems() { Count = (UInt32Value)4U };
RowItem rowItem1 = new RowItem();
MemberPropertyIndex memberPropertyIndex1 = new MemberPropertyIndex();
rowItem1.Append(memberPropertyIndex1);
RowItem rowItem2 = new RowItem();
MemberPropertyIndex memberPropertyIndex2 = new MemberPropertyIndex() { Val = 1 };
rowItem2.Append(memberPropertyIndex2);
RowItem rowItem3 = new RowItem();
MemberPropertyIndex memberPropertyIndex3 = new MemberPropertyIndex() { Val = 2 };
rowItem3.Append(memberPropertyIndex3);
RowItem rowItem4 = new RowItem() { ItemType = ItemValues.Grand };
MemberPropertyIndex memberPropertyIndex4 = new MemberPropertyIndex();
rowItem4.Append(memberPropertyIndex4);
rowItems1.Append(rowItem1);
rowItems1.Append(rowItem2);
rowItems1.Append(rowItem3);
rowItems1.Append(rowItem4);
ColumnItems columnItems1 = new ColumnItems() { Count = (UInt32Value)1U };
RowItem rowItem5 = new RowItem();
columnItems1.Append(rowItem5);
DataFields dataFields1 = new DataFields() { Count = (UInt32Value)1U };
DataField dataField1 = new DataField() { Name = "Sum of score", Field = (UInt32Value)1U, BaseField = 0, BaseItem = (UInt32Value)0U };
dataFields1.Append(dataField1);
PivotTableStyle pivotTableStyle1 = new PivotTableStyle() { Name = "PivotStyleMedium9", ShowRowHeaders = true, ShowColumnHeaders = true, ShowRowStripes = false, ShowColumnStripes = false, ShowLastColumn = true };
PivotTableDefinitionExtensionList pivotTableDefinitionExtensionList1 = new PivotTableDefinitionExtensionList();
pivotTableDefinition1.Append(location1);
pivotTableDefinition1.Append(pivotFields1);
pivotTableDefinition1.Append(rowFields1);
pivotTableDefinition1.Append(rowItems1);
pivotTableDefinition1.Append(columnItems1);
pivotTableDefinition1.Append(dataFields1);
pivotTableDefinition1.Append(pivotTableStyle1);
pivotTableDefinition1.Append(pivotTableDefinitionExtensionList1);
pivotTablePart1.PivotTableDefinition = pivotTableDefinition1;
}
internal PivotTableDefinition ToPivotTableDefinition()
{
var ptd = new PivotTableDefinition();
ptd.Name = Name;
ptd.CacheId = CacheId;
if (DataOnRows)
{
ptd.DataOnRows = DataOnRows;
}
if (DataPosition != null)
{
ptd.DataPosition = DataPosition.Value;
}
if (AutoFormatId != null)
{
ptd.AutoFormatId = AutoFormatId.Value;
}
if (ApplyNumberFormats != null)
{
ptd.ApplyNumberFormats = ApplyNumberFormats.Value;
}
if (ApplyBorderFormats != null)
{
ptd.ApplyBorderFormats = ApplyBorderFormats.Value;
}
if (ApplyFontFormats != null)
{
ptd.ApplyFontFormats = ApplyFontFormats.Value;
}
if (ApplyPatternFormats != null)
{
ptd.ApplyPatternFormats = ApplyPatternFormats.Value;
}
if (ApplyAlignmentFormats != null)
{
ptd.ApplyAlignmentFormats = ApplyAlignmentFormats.Value;
}
if (ApplyWidthHeightFormats != null)
{
ptd.ApplyWidthHeightFormats = ApplyWidthHeightFormats.Value;
}
if ((DataCaption != null) && (DataCaption.Length > 0))
{
ptd.DataCaption = DataCaption;
}
if ((GrandTotalCaption != null) && (GrandTotalCaption.Length > 0))
{
ptd.GrandTotalCaption = GrandTotalCaption;
}
if ((ErrorCaption != null) && (ErrorCaption.Length > 0))
{
ptd.ErrorCaption = ErrorCaption;
}
if (ShowError)
{
ptd.ShowError = ShowError;
}
if ((MissingCaption != null) && (MissingCaption.Length > 0))
{
ptd.MissingCaption = MissingCaption;
}
if (ShowMissing != true)
{
ptd.ShowMissing = ShowMissing;
}
if ((PageStyle != null) && (PageStyle.Length > 0))
{
ptd.PageStyle = PageStyle;
}
if ((PivotTableStyleName != null) && (PivotTableStyleName.Length > 0))
{
ptd.PivotTableStyleName = PivotTableStyleName;
}
if ((VacatedStyle != null) && (VacatedStyle.Length > 0))
{
ptd.VacatedStyle = VacatedStyle;
}
if ((Tag != null) && (Tag.Length > 0))
{
ptd.Tag = Tag;
}
if (UpdatedVersion != 0)
{
ptd.UpdatedVersion = UpdatedVersion;
}
if (MinRefreshableVersion != 0)
{
ptd.MinRefreshableVersion = MinRefreshableVersion;
}
if (AsteriskTotals)
{
ptd.AsteriskTotals = AsteriskTotals;
}
if (ShowItems != true)
{
ptd.ShowItems = ShowItems;
}
if (EditData)
{
ptd.EditData = EditData;
}
if (DisableFieldList)
{
ptd.DisableFieldList = DisableFieldList;
}
if (ShowCalculatedMembers != true)
{
ptd.ShowCalculatedMembers = ShowCalculatedMembers;
}
if (VisualTotals != true)
{
ptd.VisualTotals = VisualTotals;
}
if (ShowMultipleLabel != true)
{
ptd.ShowMultipleLabel = ShowMultipleLabel;
}
if (ShowDataDropDown != true)
{
ptd.ShowDataDropDown = ShowDataDropDown;
}
if (ShowDrill != true)
{
ptd.ShowDrill = ShowDrill;
}
if (PrintDrill)
{
ptd.PrintDrill = PrintDrill;
}
if (ShowMemberPropertyTips != true)
{
ptd.ShowMemberPropertyTips = ShowMemberPropertyTips;
}
if (ShowDataTips != true)
{
ptd.ShowDataTips = ShowDataTips;
}
if (EnableWizard != true)
{
ptd.EnableWizard = EnableWizard;
}
if (EnableDrill != true)
{
ptd.EnableDrill = EnableDrill;
}
if (EnableFieldProperties != true)
{
ptd.EnableFieldProperties = EnableFieldProperties;
}
if (PreserveFormatting != true)
{
ptd.PreserveFormatting = PreserveFormatting;
}
if (UseAutoFormatting)
{
ptd.UseAutoFormatting = UseAutoFormatting;
}
if (PageWrap != 0)
{
ptd.PageWrap = PageWrap;
}
if (PageOverThenDown)
{
ptd.PageOverThenDown = PageOverThenDown;
}
if (SubtotalHiddenItems)
{
ptd.SubtotalHiddenItems = SubtotalHiddenItems;
}
if (RowGrandTotals != true)
{
ptd.RowGrandTotals = RowGrandTotals;
}
if (ColumnGrandTotals != true)
{
ptd.ColumnGrandTotals = ColumnGrandTotals;
}
if (FieldPrintTitles)
{
ptd.FieldPrintTitles = FieldPrintTitles;
}
if (ItemPrintTitles)
{
ptd.ItemPrintTitles = ItemPrintTitles;
}
if (MergeItem)
{
ptd.MergeItem = MergeItem;
}
if (ShowDropZones != true)
{
ptd.ShowDropZones = ShowDropZones;
}
if (CreatedVersion != 0)
{
ptd.CreatedVersion = CreatedVersion;
}
if (Indent != 1)
{
ptd.Indent = Indent;
}
if (ShowEmptyRow)
{
ptd.ShowEmptyRow = ShowEmptyRow;
}
if (ShowEmptyColumn)
{
ptd.ShowEmptyColumn = ShowEmptyColumn;
}
if (ShowHeaders != true)
{
ptd.ShowHeaders = ShowHeaders;
}
if (Compact != true)
{
ptd.Compact = Compact;
}
if (Outline)
{
ptd.Outline = Outline;
}
if (OutlineData)
{
ptd.OutlineData = OutlineData;
}
if (CompactData != true)
{
ptd.CompactData = CompactData;
}
if (Published)
{
ptd.Published = Published;
}
if (GridDropZones)
{
ptd.GridDropZones = GridDropZones;
}
if (StopImmersiveUi != true)
{
ptd.StopImmersiveUi = StopImmersiveUi;
}
if (MultipleFieldFilters != true)
{
ptd.MultipleFieldFilters = MultipleFieldFilters;
}
if (ChartFormat != 0)
{
ptd.ChartFormat = ChartFormat;
}
if ((RowHeaderCaption != null) && (RowHeaderCaption.Length > 0))
{
ptd.RowHeaderCaption = RowHeaderCaption;
}
if ((ColumnHeaderCaption != null) && (ColumnHeaderCaption.Length > 0))
{
ptd.ColumnHeaderCaption = ColumnHeaderCaption;
}
if (FieldListSortAscending)
{
ptd.FieldListSortAscending = FieldListSortAscending;
}
if (CustomListSort != true)
{
ptd.CustomListSort = CustomListSort;
}
ptd.Location = Location.ToLocation();
if (PivotFields.Count > 0)
{
ptd.PivotFields = new PivotFields {
Count = (uint)PivotFields.Count
};
foreach (var pf in PivotFields)
{
ptd.PivotFields.Append(pf.ToPivotField());
}
}
if (RowFields.Count > 0)
{
ptd.RowFields = new RowFields {
Count = (uint)RowFields.Count
};
foreach (var i in RowFields)
{
ptd.RowFields.Append(new Field {
Index = i
});
}
}
if (RowItems.Count > 0)
{
ptd.RowItems = new RowItems {
Count = (uint)RowItems.Count
};
foreach (var ri in RowItems)
{
ptd.RowItems.Append(ri.ToRowItem());
}
}
if (ColumnFields.Count > 0)
{
ptd.ColumnFields = new ColumnFields {
Count = (uint)ColumnFields.Count
};
foreach (var i in ColumnFields)
{
ptd.ColumnFields.Append(new Field {
Index = i
});
}
}
if (ColumnItems.Count > 0)
{
ptd.ColumnItems = new ColumnItems {
Count = (uint)ColumnItems.Count
};
foreach (var ri in ColumnItems)
{
ptd.ColumnItems.Append(ri.ToRowItem());
}
}
if (PageFields.Count > 0)
{
ptd.PageFields = new PageFields {
Count = (uint)PageFields.Count
};
foreach (var pf in PageFields)
{
ptd.PageFields.Append(pf.ToPageField());
}
}
if (DataFields.Count > 0)
{
ptd.DataFields = new DataFields {
Count = (uint)DataFields.Count
};
foreach (var df in DataFields)
{
ptd.DataFields.Append(df.ToDataField());
}
}
if (Formats.Count > 0)
{
ptd.Formats = new Formats {
Count = (uint)Formats.Count
};
foreach (var f in Formats)
{
ptd.Formats.Append(f.ToFormat());
}
}
if (ConditionalFormats.Count > 0)
{
ptd.ConditionalFormats = new ConditionalFormats {
Count = (uint)ConditionalFormats.Count
};
foreach (var cf in ConditionalFormats)
{
ptd.ConditionalFormats.Append(cf.ToConditionalFormat());
}
}
if (ChartFormats.Count > 0)
{
ptd.ChartFormats = new ChartFormats {
Count = (uint)ChartFormats.Count
};
foreach (var cf in ChartFormats)
{
ptd.ChartFormats.Append(cf.ToChartFormat());
}
}
if (PivotHierarchies.Count > 0)
{
ptd.PivotHierarchies = new PivotHierarchies {
Count = (uint)PivotHierarchies.Count
};
foreach (var ph in PivotHierarchies)
{
ptd.PivotHierarchies.Append(ph.ToPivotHierarchy());
}
}
ptd.PivotTableStyle = PivotTableStyle.ToPivotTableStyle();
if (PivotFilters.Count > 0)
{
ptd.PivotFilters = new PivotFilters {
Count = (uint)PivotFilters.Count
};
foreach (var pf in PivotFilters)
{
ptd.PivotFilters.Append(pf.ToPivotFilter());
}
}
if (RowHierarchiesUsage.Count > 0)
{
ptd.RowHierarchiesUsage = new RowHierarchiesUsage {
Count = (uint)RowHierarchiesUsage.Count
};
foreach (var i in RowHierarchiesUsage)
{
ptd.RowHierarchiesUsage.Append(new RowHierarchyUsage {
Value = i
});
}
}
if (ColumnHierarchiesUsage.Count > 0)
{
ptd.ColumnHierarchiesUsage = new ColumnHierarchiesUsage {
Count = (uint)ColumnHierarchiesUsage.Count
};
foreach (var i in ColumnHierarchiesUsage)
{
ptd.ColumnHierarchiesUsage.Append(new ColumnHierarchyUsage {
Value = i
});
}
}
return(ptd);
}
