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; } } } }