public void CellsPivotTablesPutWorksheetPivotTableFilterTest()
{
string name = "TestCase.xlsx";
string sheetName = "SHEET4";
int? pivotTableIndex = 0;
PivotFilter pivotFilter = new PivotFilter();
pivotFilter.FieldIndex = 1;
pivotFilter.FilterType = "Count";
AutoFilter autoFilter = new AutoFilter();
autoFilter.FilterColumns = new List <FilterColumn>();
FilterColumn filterColumn = new FilterColumn();
filterColumn.FilterType = "Top10";
filterColumn.FieldIndex = 0;
filterColumn.Top10Filter = new Top10Filter();
filterColumn.Top10Filter.Items = 1;
filterColumn.Top10Filter.IsTop = true;
filterColumn.Top10Filter.IsPercent = false;
autoFilter.FilterColumns.Add(filterColumn);
pivotFilter.AutoFilter = autoFilter;
bool? needReCalculate = true;
string folder = "TEMPFOLDER";
new Config().UpdateDataFile(folder, name);
var response = instance.CellsPivotTablesPutWorksheetPivotTableFilter(name, sheetName, pivotTableIndex, pivotFilter, needReCalculate, folder);
Console.WriteLine(response);
}
public void CellsPivotTablesPutWorksheetPivotTableFilterTest()
{
// TODO uncomment below to test the method and replace null with proper value
string name = PivTestFile;
string sheetName = SHEET4;
int? pivotTableIndex = 0;
PivotFilter pivotFilter = new PivotFilter();
pivotFilter.FieldIndex = 1;
pivotFilter.FilterType = "Count";
AutoFilter autoFilter = new AutoFilter();
autoFilter.FilterColumns = new List <FilterColumn>();
FilterColumn filterColumn = new FilterColumn();
filterColumn.FilterType = "Top10";
filterColumn.FieldIndex = 0;
filterColumn.Top10Filter = new Top10Filter();
filterColumn.Top10Filter.Items = 1;
filterColumn.Top10Filter.IsTop = true;
filterColumn.Top10Filter.IsPercent = false;
autoFilter.FilterColumns.Add(filterColumn);
pivotFilter.AutoFilter = autoFilter;
bool? needReCalculate = true;
string folder = TEMPFOLDER;
UpdateDataFile(instance, folder, name);
var response = instance.CellsPivotTablesPutWorksheetPivotTableFilter(name, sheetName, pivotTableIndex, pivotFilter, needReCalculate, folder);
Assert.IsInstanceOf <CellsCloudResponse>(response, "response is CellsCloudResponse");
Assert.AreEqual(response.Code, 200);
}
internal void FromPivotFilter(PivotFilter pf)
{
SetAllNull();
if (pf.Field != null)
{
Field = pf.Field.Value;
}
if (pf.MemberPropertyFieldId != null)
{
MemberPropertyFieldId = pf.MemberPropertyFieldId.Value;
}
if (pf.Type != null)
{
Type = pf.Type.Value;
}
if (pf.EvaluationOrder != null)
{
EvaluationOrder = pf.EvaluationOrder.Value;
}
if (pf.Id != null)
{
Id = pf.Id.Value;
}
if (pf.MeasureHierarchy != null)
{
MeasureHierarchy = pf.MeasureHierarchy.Value;
}
if (pf.MeasureField != null)
{
MeasureField = pf.MeasureField.Value;
}
if (pf.Name != null)
{
Name = pf.Name.Value;
}
if (pf.Description != null)
{
Description = pf.Description.Value;
}
if (pf.StringValue1 != null)
{
StringValue1 = pf.StringValue1.Value;
}
if (pf.StringValue2 != null)
{
StringValue2 = pf.StringValue2.Value;
}
if (pf.AutoFilter != null)
{
AutoFilter.FromAutoFilter(pf.AutoFilter);
}
}
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 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 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
}
internal PivotFilter ToPivotFilter()
{
var pf = new PivotFilter();
pf.Field = Field;
if (MemberPropertyFieldId != null)
{
pf.MemberPropertyFieldId = MemberPropertyFieldId.Value;
}
pf.Type = Type;
if (EvaluationOrder != 0)
{
pf.EvaluationOrder = EvaluationOrder;
}
pf.Id = Id;
if (MeasureHierarchy != null)
{
pf.MeasureHierarchy = MeasureHierarchy.Value;
}
if (MeasureField != null)
{
pf.MeasureField = MeasureField.Value;
}
if ((Name != null) && (Name.Length > 0))
{
pf.Name = Name;
}
if ((Description != null) && (Description.Length > 0))
{
pf.Description = Description;
}
if ((StringValue1 != null) && (StringValue1.Length > 0))
{
pf.StringValue1 = StringValue1;
}
if ((StringValue2 != null) && (StringValue2.Length > 0))
{
pf.StringValue2 = StringValue2;
}
pf.AutoFilter = AutoFilter.ToAutoFilter();
return(pf);
}
internal PivotFilter ToPivotFilter()
{
PivotFilter pf = new PivotFilter();
pf.Field = this.Field;
if (this.MemberPropertyFieldId != null)
{
pf.MemberPropertyFieldId = this.MemberPropertyFieldId.Value;
}
pf.Type = this.Type;
if (this.EvaluationOrder != 0)
{
pf.EvaluationOrder = this.EvaluationOrder;
}
pf.Id = this.Id;
if (this.MeasureHierarchy != null)
{
pf.MeasureHierarchy = this.MeasureHierarchy.Value;
}
if (this.MeasureField != null)
{
pf.MeasureField = this.MeasureField.Value;
}
if (this.Name != null && this.Name.Length > 0)
{
pf.Name = this.Name;
}
if (this.Description != null && this.Description.Length > 0)
{
pf.Description = this.Description;
}
if (this.StringValue1 != null && this.StringValue1.Length > 0)
{
pf.StringValue1 = this.StringValue1;
}
if (this.StringValue2 != null && this.StringValue2.Length > 0)
{
pf.StringValue2 = this.StringValue2;
}
pf.AutoFilter = this.AutoFilter.ToAutoFilter();
return(pf);
}
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
}
internal PivotFilter ToPivotFilter()
{
PivotFilter pf = new PivotFilter();
pf.Field = this.Field;
if (this.MemberPropertyFieldId != null) pf.MemberPropertyFieldId = this.MemberPropertyFieldId.Value;
pf.Type = this.Type;
if (this.EvaluationOrder != 0) pf.EvaluationOrder = this.EvaluationOrder;
pf.Id = this.Id;
if (this.MeasureHierarchy != null) pf.MeasureHierarchy = this.MeasureHierarchy.Value;
if (this.MeasureField != null) pf.MeasureField = this.MeasureField.Value;
if (this.Name != null && this.Name.Length > 0) pf.Name = this.Name;
if (this.Description != null && this.Description.Length > 0) pf.Description = this.Description;
if (this.StringValue1 != null && this.StringValue1.Length > 0) pf.StringValue1 = this.StringValue1;
if (this.StringValue2 != null && this.StringValue2.Length > 0) pf.StringValue2 = this.StringValue2;
pf.AutoFilter = this.AutoFilter.ToAutoFilter();
return pf;
}
internal void FromPivotFilter(PivotFilter pf)
{
this.SetAllNull();
if (pf.Field != null) this.Field = pf.Field.Value;
if (pf.MemberPropertyFieldId != null) this.MemberPropertyFieldId = pf.MemberPropertyFieldId.Value;
if (pf.Type != null) this.Type = pf.Type.Value;
if (pf.EvaluationOrder != null) this.EvaluationOrder = pf.EvaluationOrder.Value;
if (pf.Id != null) this.Id = pf.Id.Value;
if (pf.MeasureHierarchy != null) this.MeasureHierarchy = pf.MeasureHierarchy.Value;
if (pf.MeasureField != null) this.MeasureField = pf.MeasureField.Value;
if (pf.Name != null) this.Name = pf.Name.Value;
if (pf.Description != null) this.Description = pf.Description.Value;
if (pf.StringValue1 != null) this.StringValue1 = pf.StringValue1.Value;
if (pf.StringValue2 != null) this.StringValue2 = pf.StringValue2.Value;
if (pf.AutoFilter != null) this.AutoFilter.FromAutoFilter(pf.AutoFilter);
}
