public void ConstructEmptyExcelPivotCacheDefinition()
{
using (var package = new ExcelPackage())
{
var sheet1 = package.Workbook.Worksheets.Add("sheet1");
sheet1.Cells[1, 1].Value = 1;
sheet1.Cells[2, 1].Value = 2;
sheet1.Cells[3, 1].Value = 3;
sheet1.Cells[4, 1].Value = 4;
sheet1.Cells[1, 2].Value = "a";
sheet1.Cells[2, 2].Value = "b";
sheet1.Cells[3, 2].Value = "c";
sheet1.Cells[4, 2].Value = "d";
sheet1.Cells[1, 3].Value = true;
sheet1.Cells[2, 3].Value = true;
sheet1.Cells[3, 3].Value = true;
sheet1.Cells[4, 3].Value = false;
var sourceAddress = sheet1.Cells["A1:C4"];
var pivotTable = new ExcelPivotTable(sheet1, sheet1.Cells[10, 10], sourceAddress, "pivotTable1", 1);
var cacheDefinition = new ExcelPivotCacheDefinition(TestUtility.CreateDefaultNSM(), pivotTable, sourceAddress, 10);
Assert.IsNotNull(cacheDefinition);
Assert.AreEqual(0, cacheDefinition.CacheFields.Count);
Assert.AreEqual(0, cacheDefinition.CacheRecords.Count);
}
}
private List <ExcelPivotTable> GetRelatedPivotTables(ExcelPivotCacheDefinition cacheDefinition)
{
var relatedPivotTables = cacheDefinition.GetRelatedPivotTables();
var pivotTableNames = this.PivotTables.Select(p => p.PivotTableName);
return(relatedPivotTables.Where(p => pivotTableNames.Any(n => n.IsEquivalentTo(p.Name))).ToList());
}
/// <summary>
/// Refreshes the slicer cache's values.
/// </summary>
internal void Refresh(ExcelPivotCacheDefinition cacheDefinition, List <CacheItem> previouslySelectedItems)
{
// If all are selected and a new value is added, it is selected.
// Otherwise new values are added as deselected.
bool isFiltered = this.TabularDataNode.Items.Any(i => !i.IsSelected);
var cacheFieldIndex = cacheDefinition.GetCacheFieldIndex(this.SourceName);
var cacheField = cacheDefinition.CacheFields[cacheFieldIndex];
var cacheItems = cacheDefinition.GetCacheItemsForSlicer(cacheField);
this.TabularDataNode.Items.Clear();
if (isFiltered)
{
for (int i = 0; i < cacheItems.Count; i++)
{
var sharedItem = cacheItems[i];
bool isSelected = previouslySelectedItems.Any(si => si.Value == sharedItem.Value && si.Type == sharedItem.Type);
this.TabularDataNode.Items.Add(i, isSelected);
}
}
else
{
cacheItems.ForEach((c, i) => this.TabularDataNode.Items.Add(i, true));
}
}
private CacheItem ResolveXCacheItem(CacheItem item, int fieldIndex, ExcelPivotCacheDefinition cacheDefinition)
{
if (item.Type != PivotCacheRecordType.x)
{
throw new InvalidOperationException("The cache item was not a reference item.");
}
int sharedItemIndex = int.Parse(item.Value);
return(cacheDefinition.CacheFields[fieldIndex].SharedItems[sharedItemIndex]);
}
/// <summary>
/// Applies the sort and hide settings to the slice values. This must be called after
/// pivot tables are refreshed in order to work properly.
/// </summary>
/// <param name="cacheDefinition">The backing cache definition.</param>
internal void ApplySettings(ExcelPivotCacheDefinition cacheDefinition)
{
var cacheFieldIndex = cacheDefinition.GetCacheFieldIndex(this.SourceName);
var cacheField = cacheDefinition.CacheFields[cacheFieldIndex];
var cacheItems = cacheDefinition.GetCacheItemsForSlicer(cacheField);
var sortedItems = this.Sort(cacheField, cacheItems);
sortedItems = this.ApplyNoDataSettings(sortedItems, cacheDefinition, cacheFieldIndex, cacheField, cacheItems);
this.TabularDataNode.Items.Clear();
this.TabularDataNode.Items.AddRange(sortedItems);
}
public void ConstructExistingExcelPivotCacheDefinition()
{
var file = new FileInfo("PivotTableDataSourceTypeWorksheet.xlsx");
Assert.IsTrue(file.Exists);
using (var package = new ExcelPackage(file))
{
var uri = new Uri("xl/pivotCache/pivotCacheDefinition1.xml", UriKind.Relative);
var possiblePart = package.GetXmlFromUri(uri);
var cacheDefinition = new ExcelPivotCacheDefinition(TestUtility.CreateDefaultNSM(), package, possiblePart, uri);
Assert.IsNotNull(cacheDefinition);
Assert.AreEqual(4, cacheDefinition.CacheFields.Count);
Assert.AreEqual("C3:F6", cacheDefinition.GetSourceRangeAddress().Address);
}
}
public void ExcelPivotCacheDefinitionNullSourceAddress()
{
using (var package = new ExcelPackage())
{
var sheet1 = package.Workbook.Worksheets.Add("sheet1");
sheet1.Cells[1, 1].Value = 1;
sheet1.Cells[2, 1].Value = 2;
sheet1.Cells[3, 1].Value = 3;
sheet1.Cells[4, 1].Value = 4;
sheet1.Cells[1, 2].Value = "a";
sheet1.Cells[2, 2].Value = "b";
sheet1.Cells[3, 2].Value = "c";
sheet1.Cells[4, 2].Value = "d";
sheet1.Cells[1, 3].Value = true;
sheet1.Cells[2, 3].Value = true;
sheet1.Cells[3, 3].Value = true;
sheet1.Cells[4, 3].Value = false;
var pivotTable = new ExcelPivotTable(sheet1, sheet1.Cells[10, 10], sheet1.Cells["A1:C4"], "pivotTable1", 1);
var cacheDefinition = new ExcelPivotCacheDefinition(TestUtility.CreateDefaultNSM(), pivotTable, null, 10);
}
}
private static ExcelPivotTable CreatePivotTableWithASlicer(ExcelPackage pck, ExcelPivotCacheDefinition pivotCache)
{
//This method connects a slicer to the pivot table. Also see sample 24 for more detailed samples on slicers.
var wsPivot4 = pck.Workbook.Worksheets.Add("PivotWithSlicer");
//Create a new pivot table using the same cache as pivot table 2.
var pivotTable4 = wsPivot4.PivotTables.Add(wsPivot4.Cells["A3"], pivotCache, "PerEmploeeSelectedCompSlicer");
pivotTable4.RowFields.Add(pivotTable4.Fields["Name"]);
//Add a rowfield
pivotTable4.RowFields.Add(pivotTable4.Fields["OrderDate"]);
//Add slicer
var companyNameField = pivotTable4.Fields["CompanyName"];
var slicer = companyNameField.AddSlicer();
slicer.SetPosition(3, 0, 5, 0); //Set top left to row 4, column F
companyNameField.Items.Refresh(); //Refresh the items from the source range.
companyNameField.Items[1].Hidden = true; //Hide item with index 1 in the items collection
companyNameField.Items.GetByValue("Walsh LLC").Hidden = true; //Hide the item with supplied the value .
//Add the data fields and format them
ExcelPivotTableDataField dataField;
dataField = pivotTable4.DataFields.Add(pivotTable4.Fields["OrderValue"]);
dataField.Format = "#,##0";
dataField = pivotTable4.DataFields.Add(pivotTable4.Fields["Tax"]);
dataField.Format = "#,##0";
dataField = pivotTable4.DataFields.Add(pivotTable4.Fields["Freight"]);
dataField.Format = "#,##0";
//We want the data fields to appear in columns
pivotTable4.DataOnRows = false;
return(pivotTable4);
}
private List <Tuple <int, int> > ResolveFieldValuePairs(List <FunctionArgument> fieldValueArguments, ExcelPivotCacheDefinition cacheDefinition)
{
var indices = new List <Tuple <int, int> >();
for (int i = 0; i + 1 < fieldValueArguments.Count; i += 2)
{
string fieldName = fieldValueArguments[i].Value.ToString();
string value = fieldValueArguments[i + 1].Value.ToString();
int fieldIndex = -1;
CacheFieldNode cacheField = null;
for (int j = 0; j < cacheDefinition.CacheFields.Count; j++)
{
var currentCacheField = cacheDefinition.CacheFields[j];
if (currentCacheField.Name.IsEquivalentTo(fieldName))
{
fieldIndex = j;
cacheField = currentCacheField;
break;
}
}
if (fieldIndex == -1)
{
return(null);
}
int valueIndex = -1;
for (int j = 0; j < cacheField.SharedItems.Count; j++)
{
if (cacheField.SharedItems[j].Value.IsEquivalentTo(value))
{
valueIndex = j;
break;
}
}
if (valueIndex == -1)
{
return(null);
}
var indexPair = new Tuple <int, int>(fieldIndex, valueIndex);
indices.Add(indexPair);
}
return(indices);
}
private static ExcelPivotTable CreatePivotTableWithACalculatedField(ExcelPackage pck, ExcelPivotCacheDefinition pivotCache)
{
//This method connects a slicer to the pivot table. Also see sample 24 for more detailed samples on slicers.
var wsPivot4 = pck.Workbook.Worksheets.Add("PivotWithCalculatedField");
//Create a new pivot table using the same cache as pivot table 2.
var pivotTable4 = wsPivot4.PivotTables.Add(wsPivot4.Cells["A3"], pivotCache, "PerWithCalculatedField");
pivotTable4.RowFields.Add(pivotTable4.Fields["CompanyName"]);
pivotTable4.RowFields.Add(pivotTable4.Fields["OrderDate"]);
//Be careful with formulas as they can cause the pivot table to become corrupt if they are entered invalidly.
var calcField = pivotTable4.Fields.AddCalculatedField("Total", "'OrderValue'+'Tax'+'Freight'");
calcField.Format = "#,##0";
//Add the data fields and format them
ExcelPivotTableDataField dataField;
dataField = pivotTable4.DataFields.Add(pivotTable4.Fields["OrderValue"]);
dataField.Format = "#,##0";
dataField = pivotTable4.DataFields.Add(pivotTable4.Fields["Tax"]);
dataField.Format = "#,##0";
dataField = pivotTable4.DataFields.Add(pivotTable4.Fields["Freight"]);
dataField.Format = "#,##0";
dataField = pivotTable4.DataFields.Add(pivotTable4.Fields["Total"]);
dataField.Format = "#,##0";
//We want the data fields to appear in columns
pivotTable4.DataOnRows = false;
return(pivotTable4);
}
private static ExcelPivotTable CreatePivotTableWithPageFilter(ExcelPackage pck, ExcelPivotCacheDefinition pivotCache)
{
var wsPivot3 = pck.Workbook.Worksheets.Add("PivotWithPageField");
//Create a new pivot table using the same cache as pivot table 2.
var pivotTable3 = wsPivot3.PivotTables.Add(wsPivot3.Cells["A3"], pivotCache, "PerEmploeeSelectedCompanies");
pivotTable3.RowFields.Add(pivotTable3.Fields["Name"]);
//Add a rowfield
var rowField = pivotTable3.RowFields.Add(pivotTable3.Fields["OrderDate"]);
//Add a pagefield
var pageField = pivotTable3.PageFields.Add(pivotTable3.Fields["CompanyName"]);
pageField.Items.Refresh(); //Refresh the items from the source range.
pageField.Items[1].Hidden = true; //Hide item with index 1 in the items collection
pageField.Items.GetByValue("Walsh LLC").Hidden = true; //Hide the item with supplied the value .
//pageField.Items.SelectSingleItem(3); //You can also select a single item with this method
//Add the data fields and format them
ExcelPivotTableDataField dataField;
dataField = pivotTable3.DataFields.Add(pivotTable3.Fields["OrderValue"]);
dataField.Format = "#,##0";
dataField = pivotTable3.DataFields.Add(pivotTable3.Fields["Tax"]);
dataField.Format = "#,##0";
dataField = pivotTable3.DataFields.Add(pivotTable3.Fields["Freight"]);
dataField.Format = "#,##0";
//We want the datafields to appear in columns
pivotTable3.DataOnRows = false;
return(pivotTable3);
}
private List <TabularItemNode> ApplyNoDataSettings(List <TabularItemNode> sortedItems, ExcelPivotCacheDefinition cacheDefinition,
int cacheFieldIndex, CacheFieldNode cacheField, SharedItemsCollection cacheItems)
{
var pivotTables = this.GetRelatedPivotTables(cacheDefinition);
if (this.HideItemsWithNoData)
{
foreach (var item in sortedItems)
{
// TODO: Task #13685 - Implement hide items with no data settings.
if (!this.PivotTablesContainItem(item, pivotTables, cacheFieldIndex, cacheItems))
{
item.NoData = true;
}
}
}
else
{
if (this.TabularDataNode.CrossFilter == CrossFilter.Both)
{
var usedItems = new List <TabularItemNode>();
var unusedItems = new List <TabularItemNode>();
foreach (var item in sortedItems)
{
bool hasData = this.PivotTablesContainItem(item, pivotTables, cacheFieldIndex, cacheItems);
if (hasData)
{
usedItems.Add(item);
}
else
{
unusedItems.Add(item);
}
}
sortedItems = usedItems.Concat(unusedItems).ToList();
}
if (!this.TabularDataNode.ShowMissing)
{
foreach (var item in sortedItems)
{
if (cacheItems[item.AtomIndex].Unused)
{
item.NoData = true;
}
}
}
}
return(sortedItems);
}
private void ValidateSlicer(List <SlicerCacheValue> expectedValues, ExcelSlicerCache slicerCache, ExcelPivotCacheDefinition cacheDefinition)
{
Assert.AreEqual(expectedValues.Count, slicerCache.TabularDataNode.Items.Count);
var cacheField = cacheDefinition.CacheFields.First(c => c.Name.IsEquivalentTo(slicerCache.SourceName));
var items = cacheField.IsGroupField ? cacheField.FieldGroup.GroupItems : cacheField.SharedItems;
for (int i = 0; i < expectedValues.Count; i++)
{
var expected = expectedValues[i];
var cacheItem = slicerCache.TabularDataNode.Items[i];
Assert.AreEqual(expected.IsSelected, cacheItem.IsSelected);
Assert.AreEqual(expected.NoData, cacheItem.NoData);
var actual = items[cacheItem.AtomIndex];
this.CompareValue(expected.Value, actual);
}
}
