public static void Run()
{
// ExStart:1
// directories
string sourceDir = RunExamples.Get_SourceDirectory();
string outputDir = RunExamples.Get_OutputDirectory();
// Load a template file
Workbook workbook = new Workbook(sourceDir + "PivotTableSample.xlsx");
// Get the first worksheet
Worksheet worksheet = workbook.Worksheets[0];
int pivotIndex = 0;
// Accessing the PivotTable
PivotTable pivotTable = worksheet.PivotTables[pivotIndex];
// Accessing the data fields.
PivotFieldCollection pivotFields = pivotTable.DataFields;
// Accessing the first data field in the data fields.
PivotField pivotField = pivotFields[0];
// Setting data display format
pivotField.DataDisplayFormat = PivotFieldDataDisplayFormat.RankLargestToSmallest;
pivotTable.CalculateData();
// Saving the Excel file
workbook.Save(outputDir + "PivotTableDataDisplayFormatRanking_out.xlsx");
// ExEnd:1
Console.WriteLine("PivotTableDataDisplayFormatRanking executed successfully.");
}
static void Main(string[] args)
{
var connection = @"OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Data Source=MYDATASOURCE;Initial Catalog=AdventureWorks2017";
var command = "SELECT * FROM [Sales].[vSalesPersonSalesByFiscalYears]";
var xl = new Application
{
Visible = true
};
xl.Workbooks.Add();
var pivotCache = xl.ActiveWorkbook.PivotCaches().Add(XlPivotTableSourceType.xlExternal, null);
pivotCache.Connection = connection;
pivotCache.MaintainConnection = true;
pivotCache.CommandText = command;
pivotCache.CommandType = XlCmdType.xlCmdSql;
var sheet = (Worksheet)xl.ActiveSheet;
var pivotTables = (PivotTables)sheet.PivotTables();
var pivotTable = pivotTables.Add(pivotCache, xl.ActiveCell, "PivotTable1");
pivotTable.SmallGrid = false;
pivotTable.ShowTableStyleRowStripes = true;
pivotTable.TableStyle2 = "PivotStyleLight1";
PivotField pageField = (PivotField)pivotTable.PivotFields("SalesTerritory");
pageField.Orientation = XlPivotFieldOrientation.xlPageField;
PivotField rowField = (PivotField)pivotTable.PivotFields("FullName");
rowField.Orientation = XlPivotFieldOrientation.xlRowField;
pivotTable.AddDataField(pivotTable.PivotFields("2004"), "Sum of 2004", XlConsolidationFunction.xlSum);
}
public PivotFilterSetter(FilterProto filter) : base(filter)
{
filter.CanFilter = false;
int i = 0;
var fields = Current.CurRegion.ActiveRow.PivotFields;
for (; i < fields.Length; i++)
{
if (!string.Equals(fields[i].Name, filter.Name, StringComparison.OrdinalIgnoreCase))
{
continue;
}
/*if (filter.GetType() == typeof(DateFilter) || filter.GetType() == typeof(DateFilter))
* if (fields[i].DataType != XlPivotFieldDataType.xlDate) continue;*/
if (filter.GetType() == typeof(NumericFilter))
{
if (fields[i].DataType != XlPivotFieldDataType.xlNumber)
{
continue;
}
}
filter.CanFilter = true;
_pivField = fields[i];
break;
}
}
static void GroupFieldItems(IWorkbook workbook)
{
#region #GroupFieldItems
Worksheet worksheet = workbook.Worksheets["Report11"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Access the pivot table by its name in the collection.
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
// Access the "State" field by its name in the collection.
PivotField field = pivotTable.Fields["State"];
// Add the "State" field to the column axis area.
pivotTable.ColumnFields.Add(field);
// Group the first three items in the field.
IEnumerable <int> items = new List <int>()
{
0, 1, 2
};
field.GroupItems(items);
// Access the created grouped field by its index in the field collection.
int groupedFieldIndex = pivotTable.Fields.Count - 1;
PivotField groupedField = pivotTable.Fields[groupedFieldIndex];
// Set the grouped item caption to "West".
groupedField.Items[0].Caption = "West";
#endregion #GroupFieldItems
}
public static void CreatePivot(string tableSource, int[] pageFields, int[] rowFields, int[] dataFields, string pivotTableName = "Pivot Table", string[] slicerColumns = null)
{
Microsoft.Office.Interop.Excel.Worksheet worksheet = new Microsoft.Office.Interop.Excel.Worksheet();
Workbook workbook = Globals.ThisAddIn.Application.ActiveWorkbook;
worksheet = workbook.Worksheets.Add(After: workbook.ActiveSheet);
worksheet.Name = "Pivot";
ListObject table = GetTable(tableSource);
Range rng = table.Range;
worksheet.PivotTableWizard(
XlPivotTableSourceType.xlDatabase,
rng,
worksheet.Range["A1"],
pivotTableName
);
PivotTable pivot = (PivotTable)worksheet.PivotTables(pivotTableName);
//pivot.HasAutoFormat = true;
pivot.ColumnGrand = true;
pivot.RowGrand = true;
for (int i = 0; i < pageFields.Length; i++)
{
PivotField field1 = pivot.PivotFields(pageFields[i]);
field1.Orientation = XlPivotFieldOrientation.xlPageField;
field1.Position = i + 1;
field1.CurrentPage = "(All)";
}
for (int i = 0; i < rowFields.Length; i++)
{
PivotField field1 = pivot.PivotFields(rowFields[i]);
field1.Orientation = XlPivotFieldOrientation.xlRowField;
field1.Position = i + 1;
}
PivotField columnField = pivot.PivotFields();
columnField.Orientation = XlPivotFieldOrientation.xlColumnField;
columnField.Position = 1;
for (int i = 0; i < dataFields.Length; i++)
{
PivotField field1 = pivot.PivotFields(dataFields[i]);
field1.Orientation = XlPivotFieldOrientation.xlDataField;
field1.Position = 1 + i;
field1.Function = XlConsolidationFunction.xlSum;
}
//Add Slicers
SlicerCaches caches = workbook.SlicerCaches;
int counter = 1;
if (slicerColumns != null)
{
foreach (string s in slicerColumns)
{
SlicerCache cache = caches.Add(pivot, s, s);
Slicers slicers = cache.Slicers;
Slicer slicer = slicers.Add(worksheet, Type.Missing, s, s, 160 * counter, 10, 144, 200);
counter++;
}
}
}
private void btnRun_Click(object sender, System.EventArgs e)
{
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
// Setting the value to the cells
sheet.Range["A1"].Value = "Product";
sheet.Range["B1"].Value = "Month";
sheet.Range["C1"].Value = "Count";
sheet.Range["A2"].Value = "SpireDoc";
sheet.Range["A3"].Value = "SpireDoc";
sheet.Range["A4"].Value = "SpireXls";
sheet.Range["A5"].Value = "SpireDoc";
sheet.Range["A6"].Value = "SpireXls";
sheet.Range["A7"].Value = "SpireXls";
sheet.Range["B2"].Value = "January";
sheet.Range["B3"].Value = "February";
sheet.Range["B4"].Value = "January";
sheet.Range["B5"].Value = "January";
sheet.Range["B6"].Value = "February";
sheet.Range["B7"].Value = "February";
sheet.Range["C2"].Value = "10";
sheet.Range["C3"].Value = "15";
sheet.Range["C4"].Value = "9";
sheet.Range["C5"].Value = "7";
sheet.Range["C6"].Value = "8";
sheet.Range["C7"].Value = "10";
// Adding a PivotTable to the worksheet
CellRange dataRange = sheet.Range["A1:C7"];
PivotCache cache = workbook.PivotCaches.Add(dataRange);
PivotTable pt = sheet.PivotTables.Add("Pivot Table", sheet.Range["C10"], cache);
// Draging the fields to the row area.
PivotField pf = pt.PivotFields["Product"] as PivotField;
pf.Axis = AxisTypes.Row;
PivotField pf2 = pt.PivotFields["Month"] as PivotField;
pf2.Axis = AxisTypes.Row;
//Draging the field to the data area.
pt.DataFields.Add(pt.PivotFields["Count"], "SUM of Count", SubtotalTypes.Sum);
//set PivotTable style
pt.BuiltInStyle = PivotBuiltInStyles.PivotStyleMedium12;
//autofit columns generated by the pivot table
pt.CalculateData();
sheet.AutoFitColumn(3);
sheet.AutoFitColumn(4);
workbook.SaveToFile("Sample.xlsx", ExcelVersion.Version2010);
ExcelDocViewer(workbook.FileName);
}
static void SetDateFilter(IWorkbook workbook)
{
#region #Date Filter
Worksheet worksheet = workbook.Worksheets["Report6"];
workbook.Worksheets.ActiveWorksheet = worksheet;
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
PivotField field = pivotTable.Fields[0];
pivotTable.Filters.Add(field, PivotFilterType.SecondQuarter);
#endregion #Date Filter
}
static void SetValueFilter(IWorkbook workbook)
{
#region #Value Filter
Worksheet worksheet = workbook.Worksheets["Report4"];
workbook.Worksheets.ActiveWorksheet = worksheet;
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
PivotField field = pivotTable.Fields[1];
pivotTable.Filters.Add(field, pivotTable.DataFields[0], PivotFilterType.ValueBetween, 6000, 13000);
#endregion #Value Filter
}
static void SetLabelFilter(IWorkbook workbook)
{
#region #Label Filter
Worksheet worksheet = workbook.Worksheets["Report4"];
workbook.Worksheets.ActiveWorksheet = worksheet;
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
PivotField field = pivotTable.Fields[0];
pivotTable.Filters.Add(field, PivotFilterType.CaptionEqual, "South");
#endregion #Label Filter
}
static void SetTop10Filter(IWorkbook workbook)
{
#region #Top10 Filter
Worksheet worksheet = workbook.Worksheets["Report4"];
workbook.Worksheets.ActiveWorksheet = worksheet;
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
PivotField field = pivotTable.Fields[1];
PivotFilter filter = pivotTable.Filters.Add(field, pivotTable.DataFields[0], PivotFilterType.Count, 2);
filter.Top10Type = PivotFilterTop10Type.Bottom;
#endregion #Top10 Filter
}
static void GroupFieldByDates(IWorkbook workbook)
{
#region #GroupFieldByDates
Worksheet worksheet = workbook.Worksheets["Report8"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Access the pivot table by its name in the collection.
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
// Access the "DATE" field by its name in the collection.
PivotField field = pivotTable.Fields["DATE"];
// Group field items by quarters and months.
field.GroupItems(PivotFieldGroupByType.Quarters | PivotFieldGroupByType.Months);
#endregion #GroupFieldByDates
}
static void GroupFieldByNumericRanges(IWorkbook workbook)
{
#region #GroupFieldByNumericRanges
Worksheet worksheet = workbook.Worksheets["Report12"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Access the pivot table by its name in the collection.
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
// Access the "Usual Hours Worked" field by its name in the collection.
PivotField field = pivotTable.Fields["Sales"];
// Group field items from 1000 to 4000 by 1000.
field.GroupItems(1000, 4000, 1000, PivotFieldGroupByType.NumericRanges);
#endregion #GroupFieldByNumericRanges
}
static void SortFieldItemsByDataField(IWorkbook workbook)
{
#region #SortFieldItemsByDataField
Worksheet worksheet = workbook.Worksheets["Report1"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Access the pivot table by its name in the collection.
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
// Access the pivot field by its name in the collection.
PivotField field = pivotTable.Fields["Product"];
// Sort items in the "Product" field by values of the "Sum of Sales" data field.
field.SortItems(PivotFieldSortType.Ascending, 0);
#endregion #SortFieldItemsByDataField
}
static void SetLabelFilter(IWorkbook workbook)
{
#region #LabelFilter
Worksheet worksheet = workbook.Worksheets["Report4"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Access the pivot table by its name in the collection.
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
// Access the "Region" field.
PivotField field = pivotTable.Fields[0];
// Filter the "Region" field by text to display sales data for the "South" region.
pivotTable.Filters.Add(field, PivotFilterType.CaptionEqual, "South");
#endregion #LabelFilter
}
static void SetMultipleFilter(IWorkbook workbook)
{
#region #Multiple Filters
Worksheet worksheet = workbook.Worksheets["Report6"];
workbook.Worksheets.ActiveWorksheet = worksheet;
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
pivotTable.Behavior.AllowMultipleFieldFilters = true;
PivotField field1 = pivotTable.Fields[0];
pivotTable.Filters.Add(field1, PivotFilterType.SecondQuarter);
PivotFilter filter = pivotTable.Filters.Add(field1, pivotTable.DataFields[0], PivotFilterType.Count, 2);
filter.Top10Type = PivotFilterTop10Type.Bottom;
#endregion #Multiple Filters
}
static void SetValueFilter(IWorkbook workbook)
{
#region #ValueFilter
Worksheet worksheet = workbook.Worksheets["Report4"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Access the pivot table by its name in the collection.
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
// Access the "Product" field.
PivotField field = pivotTable.Fields[1];
// Filter the "Product" field to display products with grand total sales between $6000 and $13000.
pivotTable.Filters.Add(field, pivotTable.DataFields[0], PivotFilterType.ValueBetween, 6000, 13000);
#endregion #ValueFilter
}
static void SortFieldItems(IWorkbook workbook)
{
#region #SortFieldItems
Worksheet worksheet = workbook.Worksheets["Report1"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Access the pivot table by its name in the collection.
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
// Access the pivot field by its name in the collection.
PivotField field = pivotTable.Fields["Product"];
// Sort items in the "Product" field.
field.SortType = PivotFieldSortType.Ascending;
#endregion #SortFieldItems
}
static void SetDateFilter(IWorkbook workbook)
{
#region #DateFilter
Worksheet worksheet = workbook.Worksheets["Report6"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Access the pivot table by its name in the collection.
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
// Access the "Date" field.
PivotField field = pivotTable.Fields[0];
// Filter the "Date" field to display sales for the second quarter.
pivotTable.Filters.Add(field, PivotFilterType.SecondQuarter);
#endregion #DateFilter
}
static void MultipleSubtotals(IWorkbook workbook)
{
#region #MultipleSubtotals
Worksheet worksheet = workbook.Worksheets["Report1"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Access the pivot table by its name in the collection.
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
// Access the pivot field by its name in the collection.
PivotField field = pivotTable.Fields["Category"];
// Display multiple subtotals for the field.
field.SetSubtotal(PivotSubtotalFunctions.Sum | PivotSubtotalFunctions.Average);
#endregion #MultipleSubtotals
}
static void SetTop10Filter(IWorkbook workbook)
{
#region #Top10Filter
Worksheet worksheet = workbook.Worksheets["Report4"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Access the pivot table by its name in the collection.
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
// Access the "Product" field.
PivotField field = pivotTable.Fields[1];
// Filter the "Product" field to display two products with the lowest sales.
PivotFilter filter = pivotTable.Filters.Add(field, pivotTable.DataFields[0], PivotFilterType.Count, 2);
filter.Top10Type = PivotFilterTop10Type.Bottom;
#endregion #Top10Filter
}
static void AddCalculatedField(IWorkbook workbook)
{
#region #AddCalculatedField
Worksheet worksheet = workbook.Worksheets["Report1"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Access the pivot table by its name in the collection.
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
// Create a calculated field based on data in the "Sales" field.
PivotField field = pivotTable.CalculatedFields.Add("=Sales*10%", "Sales Tax");
// Add the calculated field to the data area and specify the custom field name.
PivotDataField dataField = pivotTable.DataFields.Add(field, "Total Tax");
// Specify the number format for the data field.
dataField.NumberFormat = @"_([$$-409]* #,##0.00_);_([$$-409]* (#,##0.00);_([$$-409]* "" - ""??_);_(@_)";
#endregion #AddCalculatedField
}
static void UngroupFieldItems(IWorkbook workbook)
{
#region #UngroupFieldItems
Worksheet worksheet = workbook.Worksheets["Report8"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Access the pivot table by its name in the collection.
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
// Access the "DATE" field by its name in the collection.
PivotField field = pivotTable.Fields["DATE"];
// Group field items by days.
field.GroupItems(field.GroupingInfo.DefaultStartValue, field.GroupingInfo.DefaultEndValue, 50, PivotFieldGroupByType.Days);
// Ungroup field items.
field.UngroupItems();
#endregion #UngroupFieldItems
}
public static void Run()
{
// ExStart:1
// directories
string sourceDir = RunExamples.Get_SourceDirectory();
string outputDir = RunExamples.Get_OutputDirectory();
Workbook workbook = new Workbook(sourceDir + "PivotTableHideAndSortSample.xlsx");
Worksheet worksheet = workbook.Worksheets[0];
var pivotTable = worksheet.PivotTables[0];
var dataBodyRange = pivotTable.DataBodyRange;
int currentRow = 3;
int rowsUsed = dataBodyRange.EndRow;
// Sorting score in descending
PivotField field = pivotTable.RowFields[0];
field.IsAutoSort = true;
field.IsAscendSort = false;
field.AutoSortField = 0;
pivotTable.RefreshData();
pivotTable.CalculateData();
// Hiding rows with score less than 60
while (currentRow < rowsUsed)
{
Cell cell = worksheet.Cells[currentRow, 1];
double score = Convert.ToDouble(cell.Value);
if (score < 60)
{
worksheet.Cells.HideRow(currentRow);
}
currentRow++;
}
pivotTable.RefreshData();
pivotTable.CalculateData();
// Saving the Excel file
workbook.Save(outputDir + "PivotTableHideAndSort_out.xlsx");
// ExEnd:1
Console.WriteLine("PivotTableSortAndHide executed successfully.");
}
static void AddCalculatedItem(IWorkbook workbook)
{
#region #AddCalculatedItem
Worksheet worksheet = workbook.Worksheets["Report10"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Access the pivot table by its name in the collection.
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
// Access the pivot field by its name in the collection.
PivotField field = pivotTable.Fields["State"];
// Add calculated items to the "State" field.
field.CalculatedItems.Add("=Arizona+California+Colorado", "West Total");
field.CalculatedItems.Add("=Illinois+Kansas+Wisconsin", "Midwest Total");
#endregion #AddCalculatedItem
}
static void RemoveCalculatedField(IWorkbook workbook)
{
#region #RemoveCalculatedField
Worksheet worksheet = workbook.Worksheets["Report1"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Access the pivot table by its name in the collection.
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
// Create a calculated field based on data in the "Sales" field.
pivotTable.CalculatedFields.Add("=Sales*10%", "Sales Tax");
// Access the calculated field by its name in the collection.
PivotField field = pivotTable.CalculatedFields["Sales Tax"];
// Add the calculated field to the data area.
PivotDataField dataField = pivotTable.DataFields.Add(field);
//Remove the calculated field.
pivotTable.CalculatedFields.RemoveAt(0);
#endregion #RemoveCalculatedField
}
static void ModifyCalculatedItem(IWorkbook workbook)
{
#region #ModifyCalculatedItem
Worksheet worksheet = workbook.Worksheets["Report7"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Access the pivot table by its name in the collection.
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
// Access the pivot field by its name in the collection.
PivotField field = pivotTable.Fields["Customer"];
// Add a calculated item to the "Customer" field.
PivotItem item = field.CalculatedItems.Add("='Big Foods'*110%", "Big Foods Sales Plan");
//Change the formula for the calculated item.
item.Formula = "='Big Foods'*115%";
#endregion #ModifyCalculatedItem
}
static void RemoveCalculatedItem(IWorkbook workbook)
{
#region #RemoveCalculatedItem
Worksheet worksheet = workbook.Worksheets["Report7"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Access the pivot table by its name in the collection.
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
// Access the pivot field by its name in the collection.
PivotField field = pivotTable.Fields["Customer"];
// Add a calculated item to the "Customer" field.
field.CalculatedItems.Add("='Big Foods'*110%", "Big Foods Sales Plan");
//Remove the calculated item by its index from the collection.
field.CalculatedItems.RemoveAt(0);
#endregion #RemoveCalculatedItem
}
/// <summary>
/// Crear una tabla pivot.
/// <param name="hoja_tabla">Número de la hoja a la que se agregará la tabla pivot</param>
/// <param name="nombre_campo_fila">Nombre del campo de fila</param>
/// <param name="nombre_campo_datos">Nombre del campo de datos</param>
/// </summary>
public override void crearTablaPivot(string hoja_tabla, string nombre_campo_fila, string nombre_campo_datos)
{
XlPivotTableSourceType fuente = XlPivotTableSourceType.xlDatabase;
XlPivotFieldOrientation orientacion_fila = XlPivotFieldOrientation.xlRowField;
XlPivotFieldOrientation orientacion_datos = XlPivotFieldOrientation.xlDataField;
// Crear la tabla pivot
_hoja = (Worksheet)_libro.Worksheets[hoja_tabla];
_hoja.PivotTableWizard(fuente, _seleccionado, _missing, "PivTab1");
PivotTable tabla = (PivotTable)_hoja.PivotTables("PivTab1");
PivotField campo_fila = ((PivotField)tabla.PivotFields(nombre_campo_fila));
PivotField campo_datos = ((PivotField)tabla.PivotFields(nombre_campo_datos));
campo_fila.Orientation = orientacion_fila;
campo_datos.Orientation = orientacion_datos;
}
static void PercentOf(IWorkbook workbook)
{
#region #PercentOf
Worksheet worksheet = workbook.Worksheets["Report14"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Access the pivot table by its name in the collection
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
// Access the data field by its index in the collection.
PivotDataField dataField = pivotTable.DataFields[0];
// Select the base field ("Quarter").
PivotField baseField = pivotTable.Fields["Quarter"];
// Select the base item ("Q1").
PivotItem baseItem = baseField.Items[0];
// Show values as the percentage of the value of the base item in the base field.
dataField.ShowValuesWithCalculation(PivotShowValuesAsType.Percent, baseField, baseItem);
#endregion #PercentOf
}
static void SetMultipleFilter(IWorkbook workbook)
{
#region #MultipleFilters
Worksheet worksheet = workbook.Worksheets["Report6"];
workbook.Worksheets.ActiveWorksheet = worksheet;
// Access the pivot table by its name in the collection.
PivotTable pivotTable = worksheet.PivotTables["PivotTable1"];
// Allow multiple filters for a field.
pivotTable.Behavior.AllowMultipleFieldFilters = true;
// Filter the "Date" field to display sales for the second quarter.
PivotField field1 = pivotTable.Fields[0];
pivotTable.Filters.Add(field1, PivotFilterType.SecondQuarter);
// Add the second filter to the "Date" field to display two days with the lowest sales.
PivotFilter filter = pivotTable.Filters.Add(field1, pivotTable.DataFields[0], PivotFilterType.Count, 2);
filter.Top10Type = PivotFilterTop10Type.Bottom;
#endregion #MultipleFilters
}
// 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;
}
// 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 PivotField ToPivotField()
{
PivotField pf = new PivotField();
if (this.Name != null && this.Name.Length > 0) pf.Name = this.Name;
if (this.Axis != null) pf.Axis = this.Axis.Value;
if (this.DataField != false) pf.DataField = this.DataField;
if (this.SubtotalCaption != null && this.SubtotalCaption.Length > 0) pf.SubtotalCaption = this.SubtotalCaption;
if (this.ShowDropDowns != true) pf.ShowDropDowns = this.ShowDropDowns;
if (this.HiddenLevel != false) pf.HiddenLevel = this.HiddenLevel;
if (this.UniqueMemberProperty != null && this.UniqueMemberProperty.Length > 0) pf.UniqueMemberProperty = this.UniqueMemberProperty;
if (this.Compact != true) pf.Compact = this.Compact;
if (this.AllDrilled != false) pf.AllDrilled = this.AllDrilled;
if (this.NumberFormatId != null) pf.NumberFormatId = this.NumberFormatId.Value;
if (this.Outline != true) pf.Outline = this.Outline;
if (this.SubtotalTop != true) pf.SubtotalTop = this.SubtotalTop;
if (this.DragToRow != true) pf.DragToRow = this.DragToRow;
if (this.DragToColumn != true) pf.DragToColumn = this.DragToColumn;
if (this.MultipleItemSelectionAllowed != false) pf.MultipleItemSelectionAllowed = this.MultipleItemSelectionAllowed;
if (this.DragToPage != true) pf.DragToPage = this.DragToPage;
if (this.DragToData != true) pf.DragToData = this.DragToData;
if (this.DragOff != true) pf.DragOff = this.DragOff;
if (this.ShowAll != true) pf.ShowAll = this.ShowAll;
if (this.InsertBlankRow != false) pf.InsertBlankRow = this.InsertBlankRow;
if (this.ServerField != false) pf.ServerField = this.ServerField;
if (this.InsertPageBreak != false) pf.InsertPageBreak = this.InsertPageBreak;
if (this.AutoShow != false) pf.AutoShow = this.AutoShow;
if (this.TopAutoShow != true) pf.TopAutoShow = this.TopAutoShow;
if (this.HideNewItems != false) pf.HideNewItems = this.HideNewItems;
if (this.MeasureFilter != false) pf.MeasureFilter = this.MeasureFilter;
if (this.IncludeNewItemsInFilter != false) pf.IncludeNewItemsInFilter = this.IncludeNewItemsInFilter;
if (this.ItemPageCount != 10) pf.ItemPageCount = this.ItemPageCount;
if (this.SortType != FieldSortValues.Manual) pf.SortType = this.SortType;
if (this.DataSourceSort != null) pf.DataSourceSort = this.DataSourceSort.Value;
if (this.NonAutoSortDefault != false) pf.NonAutoSortDefault = this.NonAutoSortDefault;
if (this.RankBy != null) pf.RankBy = this.RankBy.Value;
if (this.DefaultSubtotal != true) pf.DefaultSubtotal = this.DefaultSubtotal;
if (this.SumSubtotal != false) pf.SumSubtotal = this.SumSubtotal;
if (this.CountASubtotal != false) pf.CountASubtotal = this.CountASubtotal;
if (this.AverageSubTotal != false) pf.AverageSubTotal = this.AverageSubTotal;
if (this.MaxSubtotal != false) pf.MaxSubtotal = this.MaxSubtotal;
if (this.MinSubtotal != false) pf.MinSubtotal = this.MinSubtotal;
if (this.ApplyProductInSubtotal != false) pf.ApplyProductInSubtotal = this.ApplyProductInSubtotal;
if (this.CountSubtotal != false) pf.CountSubtotal = this.CountSubtotal;
if (this.ApplyStandardDeviationInSubtotal != false) pf.ApplyStandardDeviationInSubtotal = this.ApplyStandardDeviationInSubtotal;
if (this.ApplyStandardDeviationPInSubtotal != false) pf.ApplyStandardDeviationPInSubtotal = this.ApplyStandardDeviationPInSubtotal;
if (this.ApplyVarianceInSubtotal != false) pf.ApplyVarianceInSubtotal = this.ApplyVarianceInSubtotal;
if (this.ApplyVariancePInSubtotal != false) pf.ApplyVariancePInSubtotal = this.ApplyVariancePInSubtotal;
if (this.ShowPropCell != false) pf.ShowPropCell = this.ShowPropCell;
if (this.ShowPropertyTooltip != false) pf.ShowPropertyTooltip = this.ShowPropertyTooltip;
if (this.ShowPropAsCaption != false) pf.ShowPropAsCaption = this.ShowPropAsCaption;
if (this.DefaultAttributeDrillState != false) pf.DefaultAttributeDrillState = this.DefaultAttributeDrillState;
if (this.Items.Count > 0)
{
pf.Items = new Items();
foreach (SLItem it in this.Items)
{
pf.Items.Append(it.ToItem());
}
}
if (this.HasAutoSortScope)
{
pf.AutoSortScope = this.AutoSortScope.ToAutoSortScope();
}
return pf;
}
internal void FromPivotField(PivotField pf)
{
this.SetAllNull();
if (pf.Name != null) this.Name = pf.Name.Value;
if (pf.Axis != null) this.Axis = pf.Axis.Value;
if (pf.DataField != null) this.DataField = pf.DataField.Value;
if (pf.SubtotalCaption != null) this.SubtotalCaption = pf.SubtotalCaption.Value;
if (pf.ShowDropDowns != null) this.ShowDropDowns = pf.ShowDropDowns.Value;
if (pf.HiddenLevel != null) this.HiddenLevel = pf.HiddenLevel.Value;
if (pf.UniqueMemberProperty != null) this.UniqueMemberProperty = pf.UniqueMemberProperty.Value;
if (pf.Compact != null) this.Compact = pf.Compact.Value;
if (pf.AllDrilled != null) this.AllDrilled = pf.AllDrilled.Value;
if (pf.NumberFormatId != null) this.NumberFormatId = pf.NumberFormatId.Value;
if (pf.Outline != null) this.Outline = pf.Outline.Value;
if (pf.SubtotalTop != null) this.SubtotalTop = pf.SubtotalTop.Value;
if (pf.DragToRow != null) this.DragToRow = pf.DragToRow.Value;
if (pf.DragToColumn != null) this.DragToColumn = pf.DragToColumn.Value;
if (pf.MultipleItemSelectionAllowed != null) this.MultipleItemSelectionAllowed = pf.MultipleItemSelectionAllowed.Value;
if (pf.DragToPage != null) this.DragToPage = pf.DragToPage.Value;
if (pf.DragToData != null) this.DragToData = pf.DragToData.Value;
if (pf.DragOff != null) this.DragOff = pf.DragOff.Value;
if (pf.ShowAll != null) this.ShowAll = pf.ShowAll.Value;
if (pf.InsertBlankRow != null) this.InsertBlankRow = pf.InsertBlankRow.Value;
if (pf.ServerField != null) this.ServerField = pf.ServerField.Value;
if (pf.InsertPageBreak != null) this.InsertPageBreak = pf.InsertPageBreak.Value;
if (pf.AutoShow != null) this.AutoShow = pf.AutoShow.Value;
if (pf.TopAutoShow != null) this.TopAutoShow = pf.TopAutoShow.Value;
if (pf.HideNewItems != null) this.HideNewItems = pf.HideNewItems.Value;
if (pf.MeasureFilter != null) this.MeasureFilter = pf.MeasureFilter.Value;
if (pf.IncludeNewItemsInFilter != null) this.IncludeNewItemsInFilter = pf.IncludeNewItemsInFilter.Value;
if (pf.ItemPageCount != null) this.ItemPageCount = pf.ItemPageCount.Value;
if (pf.SortType != null) this.SortType = pf.SortType.Value;
if (pf.DataSourceSort != null) this.DataSourceSort = pf.DataSourceSort.Value;
if (pf.NonAutoSortDefault != null) this.NonAutoSortDefault = pf.NonAutoSortDefault.Value;
if (pf.RankBy != null) this.RankBy = pf.RankBy.Value;
if (pf.DefaultSubtotal != null) this.DefaultSubtotal = pf.DefaultSubtotal.Value;
if (pf.SumSubtotal != null) this.SumSubtotal = pf.SumSubtotal.Value;
if (pf.CountASubtotal != null) this.CountASubtotal = pf.CountASubtotal.Value;
if (pf.AverageSubTotal != null) this.AverageSubTotal = pf.AverageSubTotal.Value;
if (pf.MaxSubtotal != null) this.MaxSubtotal = pf.MaxSubtotal.Value;
if (pf.MinSubtotal != null) this.MinSubtotal = pf.MinSubtotal.Value;
if (pf.ApplyProductInSubtotal != null) this.ApplyProductInSubtotal = pf.ApplyProductInSubtotal.Value;
if (pf.CountSubtotal != null) this.CountSubtotal = pf.CountSubtotal.Value;
if (pf.ApplyStandardDeviationInSubtotal != null) this.ApplyStandardDeviationInSubtotal = pf.ApplyStandardDeviationInSubtotal.Value;
if (pf.ApplyStandardDeviationPInSubtotal != null) this.ApplyStandardDeviationPInSubtotal = pf.ApplyStandardDeviationPInSubtotal.Value;
if (pf.ApplyVarianceInSubtotal != null) this.ApplyVarianceInSubtotal = pf.ApplyVarianceInSubtotal.Value;
if (pf.ApplyVariancePInSubtotal != null) this.ApplyVariancePInSubtotal = pf.ApplyVariancePInSubtotal.Value;
if (pf.ShowPropCell != null) this.ShowPropCell = pf.ShowPropCell.Value;
if (pf.ShowPropertyTooltip != null) this.ShowPropertyTooltip = pf.ShowPropertyTooltip.Value;
if (pf.ShowPropAsCaption != null) this.ShowPropAsCaption = pf.ShowPropAsCaption.Value;
if (pf.DefaultAttributeDrillState != null) this.DefaultAttributeDrillState = pf.DefaultAttributeDrillState.Value;
SLItem it;
using (OpenXmlReader oxr = OpenXmlReader.Create(pf))
{
while (oxr.Read())
{
if (oxr.ElementType == typeof(Item))
{
it = new SLItem();
it.FromItem((Item)oxr.LoadCurrentElement());
this.Items.Add(it);
}
else if (oxr.ElementType == typeof(AutoSortScope))
{
this.AutoSortScope.FromAutoSortScope((AutoSortScope)oxr.LoadCurrentElement());
this.HasAutoSortScope = true;
}
}
}
}
