public void PivotTableRefreshDataFieldSumFunctionMultipleRowDataFields()
{
var file = new FileInfo("PivotTableDataFieldFunctionTypes.xlsx");
Assert.IsTrue(file.Exists);
using (var newFile = new TempTestFile())
{
using (var package = new ExcelPackage(file))
{
var worksheet = package.Workbook.Worksheets["Sheet1"];
this.PivotTable = worksheet.PivotTables["PivotTable3"];
var cacheDefinition = package.Workbook.PivotCacheDefinitions.Single();
this.ConfigurePivotTableMultipleRowDataFieldsFunction();
cacheDefinition.UpdateData();
this.CheckPivotTableAddress(new ExcelAddress("A36:K49"), this.PivotTable.Address);
Assert.AreEqual(7, this.PivotTable.Fields.Count);
Assert.AreEqual(0, this.PivotTable.Fields[0].Items.Count);
Assert.AreEqual(4, this.PivotTable.Fields[1].Items.Count);
Assert.AreEqual(4, this.PivotTable.Fields[2].Items.Count);
Assert.AreEqual(5, this.PivotTable.Fields[3].Items.Count);
Assert.AreEqual(0, this.PivotTable.Fields[4].Items.Count);
Assert.AreEqual(0, this.PivotTable.Fields[5].Items.Count);
Assert.AreEqual(0, this.PivotTable.Fields[6].Items.Count);
package.SaveAs(newFile.File);
}
string sheetName = "Sheet1";
this.ValidatePivotTableRefreshMultipleRowDataFields(newFile.File, sheetName);
}
}
public void DisableDefaultSubtotalRemovesDefaultItem()
{
var file = new FileInfo("PivotTableBackedByExcelTable.xlsx");
Assert.IsTrue(file.Exists);
using (var newFile = new TempTestFile())
{
using (var package = new ExcelPackage(file))
{
var worksheet = package.Workbook.Worksheets.First();
var pivotTable = worksheet.PivotTables.First();
Assert.IsTrue(pivotTable.Fields.All(f => f.DefaultSubtotal));
Assert.AreEqual(0, pivotTable.Fields[0].Items.Count);
Assert.AreEqual(0, pivotTable.Fields[1].Items.Count);
var field = pivotTable.Fields[2];
Assert.AreEqual(4, field.Items.Count);
Assert.AreEqual("default", field.Items[3].T);
field = pivotTable.Fields[3];
Assert.AreEqual(5, field.Items.Count);
Assert.AreEqual("default", field.Items[4].T);
foreach (var ptField in pivotTable.Fields)
{
ptField.SubTotalFunctions = OfficeOpenXml.Table.PivotTable.eSubTotalFunctions.None;
}
Assert.IsFalse(pivotTable.Fields.All(f => f.DefaultSubtotal));
Assert.AreEqual(0, pivotTable.Fields[0].Items.Count);
Assert.AreEqual(0, pivotTable.Fields[1].Items.Count);
field = pivotTable.Fields[2];
Assert.AreEqual(3, field.Items.Count);
Assert.IsTrue(string.IsNullOrEmpty(field.Items[2].T));
field = pivotTable.Fields[3];
Assert.AreEqual(4, field.Items.Count);
Assert.IsTrue(string.IsNullOrEmpty(field.Items[3].T));
package.SaveAs(newFile.File);
}
using (var package = new ExcelPackage(newFile.File))
{
var worksheet = package.Workbook.Worksheets.First();
var pivotTable = worksheet.PivotTables.First();
Assert.IsFalse(pivotTable.Fields.All(f => f.DefaultSubtotal));
Assert.AreEqual(0, pivotTable.Fields[0].Items.Count);
Assert.AreEqual(0, pivotTable.Fields[1].Items.Count);
var field = pivotTable.Fields[2];
Assert.AreEqual(3, field.Items.Count);
Assert.IsTrue(string.IsNullOrEmpty(field.Items[2].T));
field = pivotTable.Fields[3];
Assert.AreEqual(4, field.Items.Count);
Assert.IsTrue(string.IsNullOrEmpty(field.Items[3].T));
package.SaveAs(newFile.File);
}
}
}
public void SetSourceRangeAddress()
{
var file = new FileInfo("PivotTableBackedByExcelTable.xlsx");
Assert.IsTrue(file.Exists);
using (var newFile = new TempTestFile())
{
using (var package = new ExcelPackage(file))
{
var cacheDefinition = package.Workbook.PivotCacheDefinitions.First();
Assert.AreEqual("Table1", cacheDefinition.TopNode.SelectSingleNode("d:cacheSource/d:worksheetSource/@name", cacheDefinition.NameSpaceManager).Value);
Assert.AreEqual("Table1", cacheDefinition.GetSourceRangeAddress().Address);
cacheDefinition.SetSourceRangeAddress(package.Workbook.Worksheets.First(), "A1:G7");
Assert.AreEqual("A1:G7", cacheDefinition.GetSourceRangeAddress());
Assert.IsNull(cacheDefinition.TopNode.SelectSingleNode("d:cacheSource/d:worksheetSource/@name", cacheDefinition.NameSpaceManager));
}
}
}
public void GetPivotDataFilterConflictsWithPivotTablePageFieldMonthFilter()
{
var file = new FileInfo("PivotTableWithPageFields.xlsx");
Assert.IsTrue(file.Exists);
using (var newFile = new TempTestFile())
{
using (var package = new ExcelPackage(file))
{
var sheet1 = package.Workbook.Worksheets["Sheet1"];
var pivotTable = sheet1.PivotTables["PivotTable1"];
pivotTable.PageFields[0].Item = 1;
pivotTable.CacheDefinition.UpdateData();
sheet1.Cells[2, 10].Formula = @"GETPIVOTDATA(""Sum of Units Sold"", K12, ""Month"", ""March"")";
sheet1.Cells[2, 10].Calculate();
Assert.AreEqual(eErrorType.Ref, ((ExcelErrorValue)sheet1.Cells[2, 10].Value).Type);
}
}
}
public void GetPivotDataFilterOnPivotTableWithMultiSelectPageField()
{
var file = new FileInfo("PivotTableWithPageFields.xlsx");
Assert.IsTrue(file.Exists);
using (var newFile = new TempTestFile())
{
using (var package = new ExcelPackage(file))
{
var sheet1 = package.Workbook.Worksheets["Sheet1"];
var pivotTable = sheet1.PivotTables["PivotTable1"];
pivotTable.Fields[2].Items[1].Hidden = true;
var cacheDefinition = package.Workbook.PivotCacheDefinitions.Single();
cacheDefinition.UpdateData();
sheet1.Cells[2, 10].Formula = @"GETPIVOTDATA(""Sum of Wholesale Price"", K12, ""Item"", ""Headlamp"")";
sheet1.Cells[2, 10].Calculate();
Assert.AreEqual(24.99, (double)sheet1.Cells[2, 10].Value);
}
}
}
public void GetSetShowDataAs()
{
var file = new FileInfo("PivotTableShowDataAs.xlsx");
Assert.IsTrue(file.Exists);
using (var newFile = new TempTestFile())
{
string sheetName = "PivotTables";
using (var package = new ExcelPackage(file))
{
var worksheet = package.Workbook.Worksheets[sheetName];
var pivotTable = worksheet.PivotTables["PivotTable1"];
var dataField = pivotTable.DataFields.First(f => f.Index == 4);
Assert.AreEqual(ShowDataAs.PercentOfTotal, dataField.ShowDataAs);
dataField.ShowDataAs = ShowDataAs.PercentOfRow;
Assert.AreEqual(ShowDataAs.PercentOfRow, dataField.ShowDataAs);
// NoCalculation deletes the extLst.
dataField.ShowDataAs = ShowDataAs.NoCalculation;
Assert.AreEqual(ShowDataAs.NoCalculation, dataField.ShowDataAs);
Assert.IsNull(dataField.TopNode.Attributes["showDataAs"]);
Assert.IsNull(dataField.TopNode.SelectSingleNode("d:extLst/d:ext/x14:dataField", dataField.NameSpaceManager));
// PercentOfParentRow is stored in the extLst.
dataField.ShowDataAs = ShowDataAs.PercentOfParentRow;
Assert.AreEqual(ShowDataAs.PercentOfParentRow, dataField.ShowDataAs);
var extDataFieldNode = dataField.TopNode.SelectSingleNode("d:extLst/d:ext/x14:dataField/@pivotShowAs", dataField.NameSpaceManager);
Assert.IsNotNull(extDataFieldNode);
// PercentOfCol deletes d:extLst/d:ext/x14:dataField/@pivotShowAs.
dataField.ShowDataAs = ShowDataAs.PercentOfCol;
Assert.AreEqual(ShowDataAs.PercentOfCol, dataField.ShowDataAs);
Assert.IsNotNull(dataField.TopNode.Attributes["showDataAs"]);
Assert.IsNull(dataField.TopNode.SelectSingleNode("d:extLst/d:ext/x14:dataField/@pivotShowAs", dataField.NameSpaceManager));
// PercentOfParentCol is stored in d:extLst/d:ext/x14:dataField/@pivotShowAs.
dataField.ShowDataAs = ShowDataAs.PercentOfParentCol;
Assert.AreEqual(ShowDataAs.PercentOfParentCol, dataField.ShowDataAs);
extDataFieldNode = dataField.TopNode.SelectSingleNode("d:extLst/d:ext/x14:dataField/@pivotShowAs", dataField.NameSpaceManager);
Assert.IsNotNull(extDataFieldNode);
// Difference deletes d:extLst/d:ext/x14:dataField/@pivotShowAs.
dataField.ShowDataAs = ShowDataAs.Difference;
Assert.AreEqual(ShowDataAs.Difference, dataField.ShowDataAs);
Assert.IsNotNull(dataField.TopNode.Attributes["showDataAs"]);
Assert.IsNull(dataField.TopNode.SelectSingleNode("d:extLst/d:ext/x14:dataField/@pivotShowAs", dataField.NameSpaceManager));
// RankDescending is stored in d:extLst/d:ext/x14:dataField/@pivotShowAs.
dataField.ShowDataAs = ShowDataAs.RankDescending;
Assert.AreEqual(ShowDataAs.RankDescending, dataField.ShowDataAs);
extDataFieldNode = dataField.TopNode.SelectSingleNode("d:extLst/d:ext/x14:dataField/@pivotShowAs", dataField.NameSpaceManager);
Assert.IsNotNull(extDataFieldNode);
// PercentDiff deletes d:extLst/d:ext/x14:dataField/@pivotShowAs.
dataField.ShowDataAs = ShowDataAs.PercentDiff;
Assert.AreEqual(ShowDataAs.PercentDiff, dataField.ShowDataAs);
Assert.IsNotNull(dataField.TopNode.Attributes["showDataAs"]);
Assert.IsNull(dataField.TopNode.SelectSingleNode("d:extLst/d:ext/x14:dataField/@pivotShowAs", dataField.NameSpaceManager));
// PercentOfRunningTotal is stored in d:extLst/d:ext/x14:dataField/@pivotShowAs.
dataField.ShowDataAs = ShowDataAs.PercentOfRunningTotal;
Assert.AreEqual(ShowDataAs.PercentOfRunningTotal, dataField.ShowDataAs);
extDataFieldNode = dataField.TopNode.SelectSingleNode("d:extLst/d:ext/x14:dataField/@pivotShowAs", dataField.NameSpaceManager);
Assert.IsNotNull(extDataFieldNode);
// RunTotal deletes d:extLst/d:ext/x14:dataField/@pivotShowAs.
dataField.ShowDataAs = ShowDataAs.RunTotal;
Assert.AreEqual(ShowDataAs.RunTotal, dataField.ShowDataAs);
Assert.IsNotNull(dataField.TopNode.Attributes["showDataAs"]);
Assert.IsNull(dataField.TopNode.SelectSingleNode("d:extLst/d:ext/x14:dataField/@pivotShowAs", dataField.NameSpaceManager));
// RankAscending is stored in d:extLst/d:ext/x14:dataField/@pivotShowAs.
dataField.ShowDataAs = ShowDataAs.RankAscending;
Assert.AreEqual(ShowDataAs.RankAscending, dataField.ShowDataAs);
extDataFieldNode = dataField.TopNode.SelectSingleNode("d:extLst/d:ext/x14:dataField/@pivotShowAs", dataField.NameSpaceManager);
Assert.IsNotNull(extDataFieldNode);
// NoCalculation deletes d:extLst/d:ext/x14:dataField/@pivotShowAs.
dataField.ShowDataAs = ShowDataAs.NoCalculation;
Assert.AreEqual(ShowDataAs.NoCalculation, dataField.ShowDataAs);
Assert.IsNull(dataField.TopNode.Attributes["showDataAs"]);
Assert.IsNull(dataField.TopNode.SelectSingleNode("d:extLst/d:ext/x14:dataField/@pivotShowAs", dataField.NameSpaceManager));
// Index deletes d:extLst/d:ext/x14:dataField/@pivotShowAs.
dataField.ShowDataAs = ShowDataAs.Index;
Assert.AreEqual(ShowDataAs.Index, dataField.ShowDataAs);
Assert.IsNotNull(dataField.TopNode.Attributes["showDataAs"]);
Assert.IsNull(dataField.TopNode.SelectSingleNode("d:extLst/d:ext/x14:dataField/@pivotShowAs", dataField.NameSpaceManager));
// Percent deletes d:extLst/d:ext/x14:dataField/@pivotShowAs.
dataField.ShowDataAs = ShowDataAs.Percent;
Assert.AreEqual(ShowDataAs.Percent, dataField.ShowDataAs);
Assert.IsNotNull(dataField.TopNode.Attributes["showDataAs"]);
Assert.IsNull(dataField.TopNode.SelectSingleNode("d:extLst/d:ext/x14:dataField/@pivotShowAs", dataField.NameSpaceManager));
}
}
}
public void PivotTableRefreshSummarizeValuesByProductShowDataAsPercentOfParentRow()
{
var file = new FileInfo("SummarizeValuesByTestWorkbook.xlsx");
Assert.IsTrue(file.Exists);
using (var newFile = new TempTestFile())
{
string sheetName = "PivotTables";
using (var package = new ExcelPackage(file))
{
var worksheet = package.Workbook.Worksheets[sheetName];
var pivotTable = worksheet.PivotTables["PivotTable1"];
// Show 'Wholesale Price' data as the percentage of its parent row.
var balanceAtEndDateDataField = pivotTable.DataFields.First(f => f.Name == "Sum of Balance at End Date");
balanceAtEndDateDataField.Function = DataFieldFunctions.Product;
var cacheDefinition = package.Workbook.PivotCacheDefinitions.Single();
cacheDefinition.UpdateData();
ExcelPivotTableTest.CheckPivotTableAddress(new ExcelAddress("A1:F12"), pivotTable.Address);
package.SaveAs(newFile.File);
}
TestHelperUtility.ValidateWorksheet(newFile.File, sheetName, new[]
{
new ExpectedCellValue(sheetName, 1, 1, "Sum of Balance at End Date"),
new ExpectedCellValue(sheetName, 2, 1, "Quarters"),
new ExpectedCellValue(sheetName, 3, 1, "Qtr1"),
new ExpectedCellValue(sheetName, 4, 1, null),
new ExpectedCellValue(sheetName, 5, 1, "Qtr2"),
new ExpectedCellValue(sheetName, 6, 1, null),
new ExpectedCellValue(sheetName, 7, 1, "Qtr3"),
new ExpectedCellValue(sheetName, 8, 1, null),
new ExpectedCellValue(sheetName, 9, 1, "Qtr4"),
new ExpectedCellValue(sheetName, 10, 1, null),
new ExpectedCellValue(sheetName, 11, 1, null),
new ExpectedCellValue(sheetName, 12, 1, "Grand Total"),
new ExpectedCellValue(sheetName, 1, 2, null),
new ExpectedCellValue(sheetName, 2, 2, "Debit/Credit"),
new ExpectedCellValue(sheetName, 3, 2, "Both"),
new ExpectedCellValue(sheetName, 4, 2, "Debit"),
new ExpectedCellValue(sheetName, 5, 2, "Both"),
new ExpectedCellValue(sheetName, 6, 2, "Debit"),
new ExpectedCellValue(sheetName, 7, 2, "Both"),
new ExpectedCellValue(sheetName, 8, 2, "Credit"),
new ExpectedCellValue(sheetName, 9, 2, "Both"),
new ExpectedCellValue(sheetName, 10, 2, "Credit"),
new ExpectedCellValue(sheetName, 11, 2, "Debit"),
new ExpectedCellValue(sheetName, 12, 2, null),
new ExpectedCellValue(sheetName, 1, 3, "Account Type"),
new ExpectedCellValue(sheetName, 2, 3, "Begin-Total"),
new ExpectedCellValue(sheetName, 3, 3, null),
new ExpectedCellValue(sheetName, 4, 3, null),
new ExpectedCellValue(sheetName, 5, 3, null),
new ExpectedCellValue(sheetName, 6, 3, null),
new ExpectedCellValue(sheetName, 7, 3, null),
new ExpectedCellValue(sheetName, 8, 3, null),
new ExpectedCellValue(sheetName, 9, 3, null),
new ExpectedCellValue(sheetName, 10, 3, null),
new ExpectedCellValue(sheetName, 11, 3, null),
new ExpectedCellValue(sheetName, 12, 3, null),
new ExpectedCellValue(sheetName, 1, 4, null),
new ExpectedCellValue(sheetName, 2, 4, "End-Total"),
new ExpectedCellValue(sheetName, 3, 4, null),
new ExpectedCellValue(sheetName, 4, 4, null),
new ExpectedCellValue(sheetName, 5, 4, 0),
new ExpectedCellValue(sheetName, 6, 4, 0),
new ExpectedCellValue(sheetName, 7, 4, 1d),
new ExpectedCellValue(sheetName, 8, 4, 0),
new ExpectedCellValue(sheetName, 9, 4, 0),
new ExpectedCellValue(sheetName, 10, 4, 0),
new ExpectedCellValue(sheetName, 11, 4, 0),
new ExpectedCellValue(sheetName, 12, 4, null),
new ExpectedCellValue(sheetName, 1, 5, null),
new ExpectedCellValue(sheetName, 2, 5, "Posting"),
new ExpectedCellValue(sheetName, 3, 5, 0),
new ExpectedCellValue(sheetName, 4, 5, 1d),
new ExpectedCellValue(sheetName, 5, 5, 0),
new ExpectedCellValue(sheetName, 6, 5, 1d),
new ExpectedCellValue(sheetName, 7, 5, null),
new ExpectedCellValue(sheetName, 8, 5, null),
new ExpectedCellValue(sheetName, 9, 5, null),
new ExpectedCellValue(sheetName, 10, 5, null),
new ExpectedCellValue(sheetName, 11, 5, null),
new ExpectedCellValue(sheetName, 12, 5, null),
new ExpectedCellValue(sheetName, 1, 6, null),
new ExpectedCellValue(sheetName, 2, 6, "Grand Total"),
new ExpectedCellValue(sheetName, 3, 6, null),
new ExpectedCellValue(sheetName, 4, 6, null),
new ExpectedCellValue(sheetName, 5, 6, null),
new ExpectedCellValue(sheetName, 6, 6, null),
new ExpectedCellValue(sheetName, 7, 6, null),
new ExpectedCellValue(sheetName, 8, 6, null),
new ExpectedCellValue(sheetName, 9, 6, null),
new ExpectedCellValue(sheetName, 10, 6, null),
new ExpectedCellValue(sheetName, 11, 6, null),
new ExpectedCellValue(sheetName, 12, 6, null)
});
}
}
public void PivotTableRefreshSummarizeValuesBySumShowDataAsPercentOfParentRow()
{
var file = new FileInfo("SummarizeValuesByTestWorkbook.xlsx");
Assert.IsTrue(file.Exists);
using (var newFile = new TempTestFile())
{
string sheetName = "PivotTables";
using (var package = new ExcelPackage(file))
{
var worksheet = package.Workbook.Worksheets[sheetName];
var pivotTable = worksheet.PivotTables["PivotTable1"];
var cacheDefinition = package.Workbook.PivotCacheDefinitions.Single();
cacheDefinition.UpdateData();
ExcelPivotTableTest.CheckPivotTableAddress(new ExcelAddress("A1:F12"), pivotTable.Address);
package.SaveAs(newFile.File);
}
TestHelperUtility.ValidateWorksheet(newFile.File, sheetName, new[]
{
new ExpectedCellValue(sheetName, 1, 1, "Sum of Balance at End Date"),
new ExpectedCellValue(sheetName, 2, 1, "Quarters"),
new ExpectedCellValue(sheetName, 3, 1, "Qtr1"),
new ExpectedCellValue(sheetName, 4, 1, null),
new ExpectedCellValue(sheetName, 5, 1, "Qtr2"),
new ExpectedCellValue(sheetName, 6, 1, null),
new ExpectedCellValue(sheetName, 7, 1, "Qtr3"),
new ExpectedCellValue(sheetName, 8, 1, null),
new ExpectedCellValue(sheetName, 9, 1, "Qtr4"),
new ExpectedCellValue(sheetName, 10, 1, null),
new ExpectedCellValue(sheetName, 11, 1, null),
new ExpectedCellValue(sheetName, 12, 1, "Grand Total"),
new ExpectedCellValue(sheetName, 1, 2, null),
new ExpectedCellValue(sheetName, 2, 2, "Debit/Credit"),
new ExpectedCellValue(sheetName, 3, 2, "Both"),
new ExpectedCellValue(sheetName, 4, 2, "Debit"),
new ExpectedCellValue(sheetName, 5, 2, "Both"),
new ExpectedCellValue(sheetName, 6, 2, "Debit"),
new ExpectedCellValue(sheetName, 7, 2, "Both"),
new ExpectedCellValue(sheetName, 8, 2, "Credit"),
new ExpectedCellValue(sheetName, 9, 2, "Both"),
new ExpectedCellValue(sheetName, 10, 2, "Credit"),
new ExpectedCellValue(sheetName, 11, 2, "Debit"),
new ExpectedCellValue(sheetName, 12, 2, null),
new ExpectedCellValue(sheetName, 1, 3, "Account Type"),
new ExpectedCellValue(sheetName, 2, 3, "Begin-Total"),
new ExpectedCellValue(sheetName, 3, 3, null),
new ExpectedCellValue(sheetName, 4, 3, null),
new ExpectedCellValue(sheetName, 5, 3, null),
new ExpectedCellValue(sheetName, 6, 3, null),
new ExpectedCellValue(sheetName, 7, 3, null),
new ExpectedCellValue(sheetName, 8, 3, null),
new ExpectedCellValue(sheetName, 9, 3, null),
new ExpectedCellValue(sheetName, 10, 3, null),
new ExpectedCellValue(sheetName, 11, 3, null),
new ExpectedCellValue(sheetName, 12, 3, null),
new ExpectedCellValue(sheetName, 1, 4, null),
new ExpectedCellValue(sheetName, 2, 4, "End-Total"),
new ExpectedCellValue(sheetName, 3, 4, null),
new ExpectedCellValue(sheetName, 4, 4, null),
new ExpectedCellValue(sheetName, 5, 4, -6.3252),
new ExpectedCellValue(sheetName, 6, 4, 7.3252),
new ExpectedCellValue(sheetName, 7, 4, 1d),
new ExpectedCellValue(sheetName, 8, 4, 0),
new ExpectedCellValue(sheetName, 9, 4, 0.8047),
new ExpectedCellValue(sheetName, 10, 4, .1953),
new ExpectedCellValue(sheetName, 11, 4, 0),
new ExpectedCellValue(sheetName, 12, 4, 1d),
new ExpectedCellValue(sheetName, 1, 5, null),
new ExpectedCellValue(sheetName, 2, 5, "Posting"),
new ExpectedCellValue(sheetName, 3, 5, 0),
new ExpectedCellValue(sheetName, 4, 5, 1d),
new ExpectedCellValue(sheetName, 5, 5, 0),
new ExpectedCellValue(sheetName, 6, 5, 1d),
new ExpectedCellValue(sheetName, 7, 5, null),
new ExpectedCellValue(sheetName, 8, 5, null),
new ExpectedCellValue(sheetName, 9, 5, -1.4201),
new ExpectedCellValue(sheetName, 10, 5, 0),
new ExpectedCellValue(sheetName, 11, 5, 2.4201),
new ExpectedCellValue(sheetName, 12, 5, 1d),
new ExpectedCellValue(sheetName, 1, 6, null),
new ExpectedCellValue(sheetName, 2, 6, "Grand Total"),
new ExpectedCellValue(sheetName, 3, 6, 0),
new ExpectedCellValue(sheetName, 4, 6, 1d),
new ExpectedCellValue(sheetName, 5, 6, -1.3251),
new ExpectedCellValue(sheetName, 6, 6, 2.3251),
new ExpectedCellValue(sheetName, 7, 6, 1d),
new ExpectedCellValue(sheetName, 8, 6, 0),
new ExpectedCellValue(sheetName, 9, 6, 1.1587),
new ExpectedCellValue(sheetName, 10, 6, 0.2263),
new ExpectedCellValue(sheetName, 11, 6, -0.3851),
new ExpectedCellValue(sheetName, 12, 6, 1d)
});
}
}
public void PivotTableRefreshCountFunctionTypeOneRowFieldOneColumnField()
{
var file = new FileInfo("PivotTableDataCalculationCountType.xlsx");
Assert.IsTrue(file.Exists);
using (var newFile = new TempTestFile())
{
string sheetName = "PivotTables";
using (var package = new ExcelPackage(file))
{
var worksheet = package.Workbook.Worksheets[sheetName];
var pivotTable = worksheet.PivotTables["PivotTable1"];
var cacheDefinition = package.Workbook.PivotCacheDefinitions.Single();
cacheDefinition.UpdateData();
ExcelPivotTableTest.CheckPivotTableAddress(new ExcelAddress("A1:D11"), pivotTable.Address);
Assert.AreEqual(9, pivotTable.Fields.Count);
package.SaveAs(newFile.File);
}
TestHelperUtility.ValidateWorksheet(newFile.File, sheetName, new[]
{
new ExpectedCellValue(sheetName, 1, 1, "Count of Currency Code"),
new ExpectedCellValue(sheetName, 2, 1, "Row Labels"),
new ExpectedCellValue(sheetName, 3, 1, "Autohaus Mielberg KG 2018"),
new ExpectedCellValue(sheetName, 4, 1, "Beef House 2018"),
new ExpectedCellValue(sheetName, 5, 1, "Credit Memo 104001"),
new ExpectedCellValue(sheetName, 6, 1, "Opening Entries, Customers"),
new ExpectedCellValue(sheetName, 7, 1, "Order 101008"),
new ExpectedCellValue(sheetName, 8, 1, "Order 101014"),
new ExpectedCellValue(sheetName, 9, 1, "Order 101021"),
new ExpectedCellValue(sheetName, 10, 1, "Payment 2018"),
new ExpectedCellValue(sheetName, 11, 1, "Grand Total"),
new ExpectedCellValue(sheetName, 1, 2, "Column Labels"),
new ExpectedCellValue(sheetName, 2, 2, "Jan"),
new ExpectedCellValue(sheetName, 3, 2, 4d),
new ExpectedCellValue(sheetName, 4, 2, 1d),
new ExpectedCellValue(sheetName, 5, 2, 2d),
new ExpectedCellValue(sheetName, 6, 2, null),
new ExpectedCellValue(sheetName, 7, 2, 1d),
new ExpectedCellValue(sheetName, 8, 2, 1d),
new ExpectedCellValue(sheetName, 9, 2, 1d),
new ExpectedCellValue(sheetName, 10, 2, 2d),
new ExpectedCellValue(sheetName, 11, 2, 12d),
new ExpectedCellValue(sheetName, 1, 3, null),
new ExpectedCellValue(sheetName, 2, 3, "Dec"),
new ExpectedCellValue(sheetName, 3, 3, null),
new ExpectedCellValue(sheetName, 4, 3, null),
new ExpectedCellValue(sheetName, 5, 3, null),
new ExpectedCellValue(sheetName, 6, 3, 3d),
new ExpectedCellValue(sheetName, 7, 3, null),
new ExpectedCellValue(sheetName, 8, 3, null),
new ExpectedCellValue(sheetName, 9, 3, null),
new ExpectedCellValue(sheetName, 10, 3, null),
new ExpectedCellValue(sheetName, 11, 3, 3d),
new ExpectedCellValue(sheetName, 1, 4, null),
new ExpectedCellValue(sheetName, 2, 4, "Grand Total"),
new ExpectedCellValue(sheetName, 3, 4, 4d),
new ExpectedCellValue(sheetName, 4, 4, 1d),
new ExpectedCellValue(sheetName, 5, 4, 2d),
new ExpectedCellValue(sheetName, 6, 4, 3d),
new ExpectedCellValue(sheetName, 7, 4, 1d),
new ExpectedCellValue(sheetName, 8, 4, 1d),
new ExpectedCellValue(sheetName, 9, 4, 1d),
new ExpectedCellValue(sheetName, 10, 4, 2d),
new ExpectedCellValue(sheetName, 11, 4, 15d)
});
}
}
public void PivotTableRefreshCountFunctionTypeTwoRowFieldsTwoColumnFields()
{
var file = new FileInfo("PivotTableDataCalculationCountType.xlsx");
Assert.IsTrue(file.Exists);
using (var newFile = new TempTestFile())
{
string sheetName = "PivotTables";
using (var package = new ExcelPackage(file))
{
var worksheet = package.Workbook.Worksheets[sheetName];
var pivotTable = worksheet.PivotTables["PivotTable3"];
var cacheDefinition = package.Workbook.PivotCacheDefinitions.Single();
cacheDefinition.UpdateData();
ExcelPivotTableTest.CheckPivotTableAddress(new ExcelAddress("A31:E51"), pivotTable.Address);
Assert.AreEqual(9, pivotTable.Fields.Count);
package.SaveAs(newFile.File);
}
TestHelperUtility.ValidateWorksheet(newFile.File, sheetName, new[]
{
new ExpectedCellValue(sheetName, 31, 1, "Count of Currency Code"),
new ExpectedCellValue(sheetName, 32, 1, null),
new ExpectedCellValue(sheetName, 33, 1, "Description"),
new ExpectedCellValue(sheetName, 34, 1, "Autohaus Mielberg KG 2018"),
new ExpectedCellValue(sheetName, 35, 1, null),
new ExpectedCellValue(sheetName, 36, 1, "Beef House 2018"),
new ExpectedCellValue(sheetName, 37, 1, null),
new ExpectedCellValue(sheetName, 38, 1, "Credit Memo 104001"),
new ExpectedCellValue(sheetName, 39, 1, null),
new ExpectedCellValue(sheetName, 40, 1, "Opening Entries, Customers"),
new ExpectedCellValue(sheetName, 41, 1, null),
new ExpectedCellValue(sheetName, 42, 1, null),
new ExpectedCellValue(sheetName, 43, 1, "Order 101008"),
new ExpectedCellValue(sheetName, 44, 1, null),
new ExpectedCellValue(sheetName, 45, 1, "Order 101014"),
new ExpectedCellValue(sheetName, 46, 1, null),
new ExpectedCellValue(sheetName, 47, 1, "Order 101021"),
new ExpectedCellValue(sheetName, 48, 1, null),
new ExpectedCellValue(sheetName, 49, 1, "Payment 2018"),
new ExpectedCellValue(sheetName, 50, 1, null),
new ExpectedCellValue(sheetName, 51, 1, "Grand Total"),
new ExpectedCellValue(sheetName, 31, 2, null),
new ExpectedCellValue(sheetName, 32, 2, null),
new ExpectedCellValue(sheetName, 33, 2, "Customer No."),
new ExpectedCellValue(sheetName, 34, 2, null),
new ExpectedCellValue(sheetName, 35, 2, 49633663),
new ExpectedCellValue(sheetName, 36, 2, null),
new ExpectedCellValue(sheetName, 37, 2, 49525252),
new ExpectedCellValue(sheetName, 38, 2, null),
new ExpectedCellValue(sheetName, 39, 2, 10000),
new ExpectedCellValue(sheetName, 40, 2, null),
new ExpectedCellValue(sheetName, 41, 2, 10000),
new ExpectedCellValue(sheetName, 42, 2, 30000),
new ExpectedCellValue(sheetName, 43, 2, null),
new ExpectedCellValue(sheetName, 44, 2, 35451236),
new ExpectedCellValue(sheetName, 45, 2, null),
new ExpectedCellValue(sheetName, 46, 2, 47563218),
new ExpectedCellValue(sheetName, 47, 2, null),
new ExpectedCellValue(sheetName, 48, 2, 35963852),
new ExpectedCellValue(sheetName, 49, 2, null),
new ExpectedCellValue(sheetName, 50, 2, 10000),
new ExpectedCellValue(sheetName, 51, 2, null),
new ExpectedCellValue(sheetName, 31, 3, "Years"),
new ExpectedCellValue(sheetName, 32, 3, 2017),
new ExpectedCellValue(sheetName, 33, 3, "Dec"),
new ExpectedCellValue(sheetName, 34, 3, null),
new ExpectedCellValue(sheetName, 35, 3, null),
new ExpectedCellValue(sheetName, 36, 3, null),
new ExpectedCellValue(sheetName, 37, 3, null),
new ExpectedCellValue(sheetName, 38, 3, null),
new ExpectedCellValue(sheetName, 39, 3, null),
new ExpectedCellValue(sheetName, 40, 3, 3d),
new ExpectedCellValue(sheetName, 41, 3, 2d),
new ExpectedCellValue(sheetName, 42, 3, 1d),
new ExpectedCellValue(sheetName, 43, 3, null),
new ExpectedCellValue(sheetName, 44, 3, null),
new ExpectedCellValue(sheetName, 45, 3, null),
new ExpectedCellValue(sheetName, 46, 3, null),
new ExpectedCellValue(sheetName, 47, 3, null),
new ExpectedCellValue(sheetName, 48, 3, null),
new ExpectedCellValue(sheetName, 49, 3, null),
new ExpectedCellValue(sheetName, 50, 3, null),
new ExpectedCellValue(sheetName, 51, 3, 3d),
new ExpectedCellValue(sheetName, 31, 4, "Posting Date"),
new ExpectedCellValue(sheetName, 32, 4, 2018),
new ExpectedCellValue(sheetName, 33, 4, "Jan"),
new ExpectedCellValue(sheetName, 34, 4, 4d),
new ExpectedCellValue(sheetName, 35, 4, 4d),
new ExpectedCellValue(sheetName, 36, 4, 1d),
new ExpectedCellValue(sheetName, 37, 4, 1d),
new ExpectedCellValue(sheetName, 38, 4, 2d),
new ExpectedCellValue(sheetName, 39, 4, 2d),
new ExpectedCellValue(sheetName, 40, 4, null),
new ExpectedCellValue(sheetName, 41, 4, null),
new ExpectedCellValue(sheetName, 42, 4, null),
new ExpectedCellValue(sheetName, 43, 4, 1d),
new ExpectedCellValue(sheetName, 44, 4, 1d),
new ExpectedCellValue(sheetName, 45, 4, 1d),
new ExpectedCellValue(sheetName, 46, 4, 1d),
new ExpectedCellValue(sheetName, 47, 4, 1d),
new ExpectedCellValue(sheetName, 48, 4, 1d),
new ExpectedCellValue(sheetName, 49, 4, 2d),
new ExpectedCellValue(sheetName, 50, 4, 2d),
new ExpectedCellValue(sheetName, 51, 4, 12d),
new ExpectedCellValue(sheetName, 31, 5, null),
new ExpectedCellValue(sheetName, 32, 5, "Grand Total"),
new ExpectedCellValue(sheetName, 33, 5, null),
new ExpectedCellValue(sheetName, 34, 5, 4d),
new ExpectedCellValue(sheetName, 35, 5, 4d),
new ExpectedCellValue(sheetName, 36, 5, 1d),
new ExpectedCellValue(sheetName, 37, 5, 1d),
new ExpectedCellValue(sheetName, 38, 5, 2d),
new ExpectedCellValue(sheetName, 39, 5, 2d),
new ExpectedCellValue(sheetName, 40, 5, 3d),
new ExpectedCellValue(sheetName, 41, 5, 2d),
new ExpectedCellValue(sheetName, 42, 5, 1d),
new ExpectedCellValue(sheetName, 43, 5, 1d),
new ExpectedCellValue(sheetName, 44, 5, 1d),
new ExpectedCellValue(sheetName, 45, 5, 1d),
new ExpectedCellValue(sheetName, 46, 5, 1d),
new ExpectedCellValue(sheetName, 47, 5, 1d),
new ExpectedCellValue(sheetName, 48, 5, 1d),
new ExpectedCellValue(sheetName, 49, 5, 2d),
new ExpectedCellValue(sheetName, 50, 5, 2d),
new ExpectedCellValue(sheetName, 51, 5, 15d)
});
}
}
public void PivotTableRefreshCountFunctionTypeTwoRowFieldsTwoColumnFieldsLeafDataField()
{
var file = new FileInfo("PivotTableDataCalculationCountType.xlsx");
Assert.IsTrue(file.Exists);
using (var newFile = new TempTestFile())
{
string sheetName = "PivotTables";
using (var package = new ExcelPackage(file))
{
var worksheet = package.Workbook.Worksheets[sheetName];
var pivotTable = worksheet.PivotTables["PivotTable2"];
var cacheDefinition = package.Workbook.PivotCacheDefinitions.Single();
cacheDefinition.UpdateData();
ExcelPivotTableTest.CheckPivotTableAddress(new ExcelAddress("A15:H27"), pivotTable.Address);
Assert.AreEqual(9, pivotTable.Fields.Count);
package.SaveAs(newFile.File);
}
TestHelperUtility.ValidateWorksheet(newFile.File, sheetName, new[]
{
new ExpectedCellValue(sheetName, 15, 1, null),
new ExpectedCellValue(sheetName, 16, 1, null),
new ExpectedCellValue(sheetName, 17, 1, "Description"),
new ExpectedCellValue(sheetName, 18, 1, "Autohaus Mielberg KG 2018"),
new ExpectedCellValue(sheetName, 19, 1, "Beef House 2018"),
new ExpectedCellValue(sheetName, 20, 1, "Credit Memo 104001"),
new ExpectedCellValue(sheetName, 21, 1, "Opening Entries, Customers"),
new ExpectedCellValue(sheetName, 22, 1, null),
new ExpectedCellValue(sheetName, 23, 1, "Order 101008"),
new ExpectedCellValue(sheetName, 24, 1, "Order 101014"),
new ExpectedCellValue(sheetName, 25, 1, "Order 101021"),
new ExpectedCellValue(sheetName, 26, 1, "Payment 2018"),
new ExpectedCellValue(sheetName, 27, 1, "Grand Total"),
new ExpectedCellValue(sheetName, 15, 2, null),
new ExpectedCellValue(sheetName, 16, 2, null),
new ExpectedCellValue(sheetName, 17, 2, "Customer No."),
new ExpectedCellValue(sheetName, 18, 2, 49633663),
new ExpectedCellValue(sheetName, 19, 2, 49525252),
new ExpectedCellValue(sheetName, 20, 2, 10000),
new ExpectedCellValue(sheetName, 21, 2, 10000),
new ExpectedCellValue(sheetName, 22, 2, 30000),
new ExpectedCellValue(sheetName, 23, 2, 35451236),
new ExpectedCellValue(sheetName, 24, 2, 47563218),
new ExpectedCellValue(sheetName, 25, 2, 35963852),
new ExpectedCellValue(sheetName, 26, 2, 10000),
new ExpectedCellValue(sheetName, 27, 2, null),
new ExpectedCellValue(sheetName, 15, 3, "Posting Date"),
new ExpectedCellValue(sheetName, 16, 3, "Jan"),
new ExpectedCellValue(sheetName, 17, 3, "Count of Currency Code"),
new ExpectedCellValue(sheetName, 18, 3, 4d),
new ExpectedCellValue(sheetName, 19, 3, 1d),
new ExpectedCellValue(sheetName, 20, 3, 2d),
new ExpectedCellValue(sheetName, 21, 3, null),
new ExpectedCellValue(sheetName, 22, 3, null),
new ExpectedCellValue(sheetName, 23, 3, 1d),
new ExpectedCellValue(sheetName, 24, 3, 1d),
new ExpectedCellValue(sheetName, 25, 3, 1d),
new ExpectedCellValue(sheetName, 26, 3, 2d),
new ExpectedCellValue(sheetName, 27, 3, 12d),
new ExpectedCellValue(sheetName, 15, 4, "Values"),
new ExpectedCellValue(sheetName, 16, 4, null),
new ExpectedCellValue(sheetName, 17, 4, "Sum of Profit ($)"),
new ExpectedCellValue(sheetName, 18, 4, 0),
new ExpectedCellValue(sheetName, 19, 4, 0),
new ExpectedCellValue(sheetName, 20, 4, -129.98),
new ExpectedCellValue(sheetName, 21, 4, null),
new ExpectedCellValue(sheetName, 22, 4, null),
new ExpectedCellValue(sheetName, 23, 4, 259.97),
new ExpectedCellValue(sheetName, 24, 4, 6349.7),
new ExpectedCellValue(sheetName, 25, 4, 521.17),
new ExpectedCellValue(sheetName, 26, 4, 0),
new ExpectedCellValue(sheetName, 27, 4, 7000.86),
new ExpectedCellValue(sheetName, 15, 5, null),
new ExpectedCellValue(sheetName, 16, 5, "Dec"),
new ExpectedCellValue(sheetName, 17, 5, "Count of Currency Code"),
new ExpectedCellValue(sheetName, 18, 5, null),
new ExpectedCellValue(sheetName, 19, 5, null),
new ExpectedCellValue(sheetName, 20, 5, null),
new ExpectedCellValue(sheetName, 21, 5, 2d),
new ExpectedCellValue(sheetName, 22, 5, 1d),
new ExpectedCellValue(sheetName, 23, 5, null),
new ExpectedCellValue(sheetName, 24, 5, null),
new ExpectedCellValue(sheetName, 25, 5, null),
new ExpectedCellValue(sheetName, 26, 5, null),
new ExpectedCellValue(sheetName, 27, 5, 3d),
new ExpectedCellValue(sheetName, 15, 6, null),
new ExpectedCellValue(sheetName, 16, 6, null),
new ExpectedCellValue(sheetName, 17, 6, "Sum of Profit ($)"),
new ExpectedCellValue(sheetName, 18, 6, null),
new ExpectedCellValue(sheetName, 19, 6, null),
new ExpectedCellValue(sheetName, 20, 6, null),
new ExpectedCellValue(sheetName, 21, 6, 0),
new ExpectedCellValue(sheetName, 22, 6, 0),
new ExpectedCellValue(sheetName, 23, 6, null),
new ExpectedCellValue(sheetName, 24, 6, null),
new ExpectedCellValue(sheetName, 25, 6, null),
new ExpectedCellValue(sheetName, 26, 6, null),
new ExpectedCellValue(sheetName, 27, 6, 0),
new ExpectedCellValue(sheetName, 15, 7, null),
new ExpectedCellValue(sheetName, 16, 7, "Total Count of Currency Code"),
new ExpectedCellValue(sheetName, 17, 7, null),
new ExpectedCellValue(sheetName, 18, 7, 4d),
new ExpectedCellValue(sheetName, 19, 7, 1d),
new ExpectedCellValue(sheetName, 20, 7, 2d),
new ExpectedCellValue(sheetName, 21, 7, 2d),
new ExpectedCellValue(sheetName, 22, 7, 1d),
new ExpectedCellValue(sheetName, 23, 7, 1d),
new ExpectedCellValue(sheetName, 24, 7, 1d),
new ExpectedCellValue(sheetName, 25, 7, 1d),
new ExpectedCellValue(sheetName, 26, 7, 2d),
new ExpectedCellValue(sheetName, 27, 7, 15d),
new ExpectedCellValue(sheetName, 15, 8, null),
new ExpectedCellValue(sheetName, 16, 8, "Total Sum of Profit ($)"),
new ExpectedCellValue(sheetName, 17, 8, null),
new ExpectedCellValue(sheetName, 18, 8, 0),
new ExpectedCellValue(sheetName, 19, 8, 0),
new ExpectedCellValue(sheetName, 20, 8, -129.98),
new ExpectedCellValue(sheetName, 21, 8, 0),
new ExpectedCellValue(sheetName, 22, 8, 0),
new ExpectedCellValue(sheetName, 23, 8, 259.97),
new ExpectedCellValue(sheetName, 24, 8, 6349.7),
new ExpectedCellValue(sheetName, 25, 8, 521.17),
new ExpectedCellValue(sheetName, 26, 8, 0),
new ExpectedCellValue(sheetName, 27, 8, 7000.86)
});
}
}
public void RefreshSlicersWithStaticData()
{
var file = new FileInfo("Slicers.xlsx");
Assert.IsTrue(file.Exists);
using (var newFile = new TempTestFile())
{
using (var package = new ExcelPackage(file))
{
package.Workbook.PivotCacheDefinitions.First().UpdateData();
package.SaveAs(newFile.File);
}
var postingDateSlicerValues = new List <SlicerCacheValue>
{
new SlicerCacheValue("Jan"),
new SlicerCacheValue("Feb"),
new SlicerCacheValue("Mar"),
new SlicerCacheValue("Apr"),
new SlicerCacheValue("May"),
new SlicerCacheValue("Jun"),
new SlicerCacheValue("Jul"),
new SlicerCacheValue("Aug"),
new SlicerCacheValue("Sep"),
new SlicerCacheValue("Oct"),
new SlicerCacheValue("Nov"),
new SlicerCacheValue("Dec"),
new SlicerCacheValue("<12/31/2012"),
new SlicerCacheValue(">1/2/2013"),
};
var sourceCodeSlicerValues = new List <SlicerCacheValue>
{
new SlicerCacheValue("END"),
new SlicerCacheValue("MIDDLE"),
new SlicerCacheValue("PURCHASES"),
new SlicerCacheValue("SALES"),
new SlicerCacheValue("START")
};
var descriptionSlicerValues = new List <SlicerCacheValue>
{
new SlicerCacheValue("Entries, January 2013"),
new SlicerCacheValue("Opening Entry"),
new SlicerCacheValue("Order 106015"),
new SlicerCacheValue("Order 106018")
};
var amountSlicerValues = new List <SlicerCacheValue>
{
new SlicerCacheValue("-752562.89"),
new SlicerCacheValue("-558283.32"),
new SlicerCacheValue("-244909.87"),
new SlicerCacheValue("-53800.14"),
new SlicerCacheValue("82.28"),
new SlicerCacheValue("122.09"),
new SlicerCacheValue("183.13"),
new SlicerCacheValue("232.02"),
new SlicerCacheValue("286.31"),
new SlicerCacheValue("305.22"),
new SlicerCacheValue("828.97"),
new SlicerCacheValue("1243.45"),
new SlicerCacheValue("2072.43"),
new SlicerCacheValue("3734.73"),
new SlicerCacheValue("5602.1"),
new SlicerCacheValue("5662.08"),
new SlicerCacheValue("8277.85"),
new SlicerCacheValue("9336.83"),
new SlicerCacheValue("11836.03"),
new SlicerCacheValue("53800.14"),
};
using (var package = new ExcelPackage(newFile.File))
{
var cacheDefinition = package.Workbook.PivotCacheDefinitions.First();
var postingDateSlicerCache = package.Workbook.SlicerCaches.First(c => c.Name == "Slicer_Posting_Date");
var descriptionSlicerCache = package.Workbook.SlicerCaches.First(c => c.Name == "Slicer_Description");
var amountSlicerCache = package.Workbook.SlicerCaches.First(c => c.Name == "Slicer_Amount");
var sourceCodeSlicerCache = package.Workbook.SlicerCaches.First(c => c.Name == "Slicer_Source_Code");
this.ValidateSlicer(postingDateSlicerValues, postingDateSlicerCache, cacheDefinition);
this.ValidateSlicer(descriptionSlicerValues, descriptionSlicerCache, cacheDefinition);
this.ValidateSlicer(sourceCodeSlicerValues, sourceCodeSlicerCache, cacheDefinition);
this.ValidateSlicer(amountSlicerValues, amountSlicerCache, cacheDefinition);
}
}
}
public void PivotTableRefreshIndentationCompactFormDisabledAndTabularFormEnabled()
{
var file = new FileInfo("PivotTableCompactAndTabularFormMultipleDataFields.xlsx");
Assert.IsTrue(file.Exists);
using (var newFile = new TempTestFile())
{
string sheetName = "ThreeRowFields";
using (var package = new ExcelPackage(file))
{
var sheet = package.Workbook.Worksheets[sheetName];
var cacheDefinition = package.Workbook.PivotCacheDefinitions.Single();
cacheDefinition.UpdateData();
package.SaveAs(newFile.File);
}
var pt1expected = new[]
{
// Pivot Table 7
new ExpectedCellValue(sheetName, 116, 3, 0),
new ExpectedCellValue(sheetName, 125, 3, 0),
new ExpectedCellValue(sheetName, 137, 3, 0),
new ExpectedCellValue(sheetName, 145, 3, 0),
new ExpectedCellValue(sheetName, 146, 3, 0),
new ExpectedCellValue(sheetName, 116, 4, 0),
new ExpectedCellValue(sheetName, 117, 4, 0),
new ExpectedCellValue(sheetName, 121, 4, 0),
new ExpectedCellValue(sheetName, 125, 4, 0),
new ExpectedCellValue(sheetName, 129, 4, 0),
new ExpectedCellValue(sheetName, 133, 4, 0),
new ExpectedCellValue(sheetName, 137, 4, 0),
new ExpectedCellValue(sheetName, 141, 4, 0),
new ExpectedCellValue(sheetName, 116, 5, 0),
new ExpectedCellValue(sheetName, 117, 5, 0),
new ExpectedCellValue(sheetName, 118, 5, 1),
new ExpectedCellValue(sheetName, 119, 5, 0),
new ExpectedCellValue(sheetName, 120, 5, 1),
new ExpectedCellValue(sheetName, 121, 5, 0),
new ExpectedCellValue(sheetName, 122, 5, 1),
new ExpectedCellValue(sheetName, 123, 5, 0),
new ExpectedCellValue(sheetName, 124, 5, 1),
new ExpectedCellValue(sheetName, 125, 5, 0),
new ExpectedCellValue(sheetName, 126, 5, 1),
new ExpectedCellValue(sheetName, 127, 5, 0),
new ExpectedCellValue(sheetName, 128, 5, 1),
new ExpectedCellValue(sheetName, 129, 5, 0),
new ExpectedCellValue(sheetName, 130, 5, 1),
new ExpectedCellValue(sheetName, 131, 5, 0),
new ExpectedCellValue(sheetName, 132, 5, 1),
new ExpectedCellValue(sheetName, 133, 5, 0),
new ExpectedCellValue(sheetName, 134, 5, 1),
new ExpectedCellValue(sheetName, 135, 5, 0),
new ExpectedCellValue(sheetName, 136, 5, 1),
new ExpectedCellValue(sheetName, 137, 5, 0),
new ExpectedCellValue(sheetName, 138, 5, 1),
new ExpectedCellValue(sheetName, 139, 5, 0),
new ExpectedCellValue(sheetName, 140, 5, 1),
new ExpectedCellValue(sheetName, 141, 5, 0),
new ExpectedCellValue(sheetName, 142, 5, 1),
new ExpectedCellValue(sheetName, 143, 5, 0),
new ExpectedCellValue(sheetName, 144, 5, 1),
};
this.ValidateIndentation(newFile.File, pt1expected);
}
}
public void PivotTableRefreshIndentationCompactFormDisabled()
{
var file = new FileInfo("PivotTableCompactForm.xlsx");
Assert.IsTrue(file.Exists);
using (var newFile = new TempTestFile())
{
string sheetName = "FourRowFields";
using (var package = new ExcelPackage(file))
{
var sheet = package.Workbook.Worksheets[sheetName];
var cacheDefinition = package.Workbook.PivotCacheDefinitions.Single();
cacheDefinition.UpdateData();
package.SaveAs(newFile.File);
}
var pt1expected = new[]
{
// Pivot Table 5
new ExpectedCellValue(sheetName, 60, 1, 0),
new ExpectedCellValue(sheetName, 61, 1, 0),
new ExpectedCellValue(sheetName, 65, 1, 0),
new ExpectedCellValue(sheetName, 69, 1, 0),
new ExpectedCellValue(sheetName, 73, 1, 0),
new ExpectedCellValue(sheetName, 77, 1, 0),
new ExpectedCellValue(sheetName, 81, 1, 0),
new ExpectedCellValue(sheetName, 85, 1, 0),
new ExpectedCellValue(sheetName, 89, 1, 0),
new ExpectedCellValue(sheetName, 60, 2, 0),
new ExpectedCellValue(sheetName, 62, 2, 0),
new ExpectedCellValue(sheetName, 63, 2, 1),
new ExpectedCellValue(sheetName, 66, 2, 0),
new ExpectedCellValue(sheetName, 67, 2, 1),
new ExpectedCellValue(sheetName, 70, 2, 0),
new ExpectedCellValue(sheetName, 71, 2, 1),
new ExpectedCellValue(sheetName, 74, 2, 0),
new ExpectedCellValue(sheetName, 75, 2, 1),
new ExpectedCellValue(sheetName, 78, 2, 0),
new ExpectedCellValue(sheetName, 79, 2, 1),
new ExpectedCellValue(sheetName, 82, 2, 0),
new ExpectedCellValue(sheetName, 83, 2, 1),
new ExpectedCellValue(sheetName, 86, 2, 0),
new ExpectedCellValue(sheetName, 87, 2, 1),
new ExpectedCellValue(sheetName, 60, 3, 0),
new ExpectedCellValue(sheetName, 64, 3, 0),
new ExpectedCellValue(sheetName, 72, 3, 0),
new ExpectedCellValue(sheetName, 76, 3, 0),
new ExpectedCellValue(sheetName, 80, 3, 0),
new ExpectedCellValue(sheetName, 84, 3, 0),
new ExpectedCellValue(sheetName, 88, 3, 0),
};
var pt2expected = new[]
{
// Pivot Table 7
new ExpectedCellValue(sheetName, 60, 11, 0),
new ExpectedCellValue(sheetName, 61, 11, 0),
new ExpectedCellValue(sheetName, 65, 11, 0),
new ExpectedCellValue(sheetName, 69, 11, 0),
new ExpectedCellValue(sheetName, 73, 11, 0),
new ExpectedCellValue(sheetName, 77, 11, 0),
new ExpectedCellValue(sheetName, 81, 11, 0),
new ExpectedCellValue(sheetName, 85, 11, 0),
new ExpectedCellValue(sheetName, 89, 11, 0),
new ExpectedCellValue(sheetName, 60, 12, 0),
new ExpectedCellValue(sheetName, 62, 12, 0),
new ExpectedCellValue(sheetName, 63, 12, 1),
new ExpectedCellValue(sheetName, 64, 12, 2),
new ExpectedCellValue(sheetName, 66, 12, 0),
new ExpectedCellValue(sheetName, 67, 12, 1),
new ExpectedCellValue(sheetName, 68, 12, 2),
new ExpectedCellValue(sheetName, 70, 12, 0),
new ExpectedCellValue(sheetName, 71, 12, 1),
new ExpectedCellValue(sheetName, 72, 12, 2),
new ExpectedCellValue(sheetName, 74, 12, 0),
new ExpectedCellValue(sheetName, 75, 12, 1),
new ExpectedCellValue(sheetName, 76, 12, 2),
new ExpectedCellValue(sheetName, 78, 12, 0),
new ExpectedCellValue(sheetName, 79, 12, 1),
new ExpectedCellValue(sheetName, 80, 12, 2),
new ExpectedCellValue(sheetName, 82, 12, 0),
new ExpectedCellValue(sheetName, 83, 12, 1),
new ExpectedCellValue(sheetName, 84, 12, 2),
new ExpectedCellValue(sheetName, 86, 12, 0),
new ExpectedCellValue(sheetName, 87, 12, 1),
new ExpectedCellValue(sheetName, 88, 12, 2),
};
this.ValidateIndentation(newFile.File, pt1expected);
this.ValidateIndentation(newFile.File, pt2expected);
}
}
public void PivotTableRefreshIndentationTabularEnabled()
{
var file = new FileInfo("PivotTableTabularSettingsEnabled.xlsx");
Assert.IsTrue(file.Exists);
using (var newFile = new TempTestFile())
{
string sheetName = "FourTabularFields";
using (var package = new ExcelPackage(file))
{
var sheet = package.Workbook.Worksheets[sheetName];
var cacheDefinition = package.Workbook.PivotCacheDefinitions.Single();
cacheDefinition.UpdateData();
package.SaveAs(newFile.File);
}
var pt1expected = new[]
{
new ExpectedCellValue(sheetName, 3, 1, 0),
new ExpectedCellValue(sheetName, 6, 1, 0),
new ExpectedCellValue(sheetName, 10, 1, 0),
new ExpectedCellValue(sheetName, 11, 1, 0),
new ExpectedCellValue(sheetName, 14, 1, 0),
new ExpectedCellValue(sheetName, 15, 1, 0),
new ExpectedCellValue(sheetName, 18, 1, 0),
new ExpectedCellValue(sheetName, 19, 1, 0),
new ExpectedCellValue(sheetName, 22, 1, 0),
new ExpectedCellValue(sheetName, 23, 1, 0),
new ExpectedCellValue(sheetName, 26, 1, 0),
new ExpectedCellValue(sheetName, 27, 1, 0),
new ExpectedCellValue(sheetName, 30, 1, 0),
new ExpectedCellValue(sheetName, 31, 1, 0),
new ExpectedCellValue(sheetName, 3, 2, 0),
new ExpectedCellValue(sheetName, 5, 2, 0),
new ExpectedCellValue(sheetName, 7, 2, 0),
new ExpectedCellValue(sheetName, 9, 2, 0),
new ExpectedCellValue(sheetName, 11, 2, 0),
new ExpectedCellValue(sheetName, 13, 2, 0),
new ExpectedCellValue(sheetName, 15, 2, 0),
new ExpectedCellValue(sheetName, 17, 2, 0),
new ExpectedCellValue(sheetName, 19, 2, 0),
new ExpectedCellValue(sheetName, 21, 2, 0),
new ExpectedCellValue(sheetName, 23, 2, 0),
new ExpectedCellValue(sheetName, 25, 2, 0),
new ExpectedCellValue(sheetName, 27, 2, 0),
new ExpectedCellValue(sheetName, 29, 2, 0),
new ExpectedCellValue(sheetName, 3, 3, 0),
new ExpectedCellValue(sheetName, 4, 3, 1),
new ExpectedCellValue(sheetName, 7, 3, 0),
new ExpectedCellValue(sheetName, 8, 3, 1),
new ExpectedCellValue(sheetName, 11, 3, 0),
new ExpectedCellValue(sheetName, 12, 3, 1),
new ExpectedCellValue(sheetName, 15, 3, 0),
new ExpectedCellValue(sheetName, 16, 3, 1),
new ExpectedCellValue(sheetName, 19, 3, 0),
new ExpectedCellValue(sheetName, 20, 3, 1),
new ExpectedCellValue(sheetName, 23, 3, 0),
new ExpectedCellValue(sheetName, 24, 3, 1),
new ExpectedCellValue(sheetName, 27, 3, 0),
new ExpectedCellValue(sheetName, 28, 3, 1),
};
this.ValidateIndentation(newFile.File, pt1expected);
}
}
public void PivotTableRefreshAllFieldsCompactIndentationWith0CharacterIndent()
{
var file = new FileInfo("PivotTableWithMultipleRowDataFieldsNoColumnField.xlsx");
Assert.IsTrue(file.Exists);
int indentMultiplier = 127; // Excel stores zero indent as 127.
using (var newFile = new TempTestFile())
{
string sheetName = "RowDataFields";
using (var package = new ExcelPackage(file))
{
var sheet = package.Workbook.Worksheets[sheetName];
var cacheDefinition = package.Workbook.PivotCacheDefinitions.Single();
foreach (var pivotTable in sheet.PivotTables)
{
pivotTable.Indent = indentMultiplier;
}
cacheDefinition.UpdateData();
package.SaveAs(newFile.File);
}
var pt1expected = new[]
{
new ExpectedCellValue(sheetName, 1, 1, 0),
new ExpectedCellValue(sheetName, 2, 1, 0),
new ExpectedCellValue(sheetName, 3, 1, 0),
new ExpectedCellValue(sheetName, 4, 1, 0),
new ExpectedCellValue(sheetName, 5, 1, 0),
new ExpectedCellValue(sheetName, 6, 1, 0),
new ExpectedCellValue(sheetName, 7, 1, 0),
new ExpectedCellValue(sheetName, 8, 1, 0),
new ExpectedCellValue(sheetName, 9, 1, 0),
new ExpectedCellValue(sheetName, 10, 1, 0),
new ExpectedCellValue(sheetName, 11, 1, 0),
new ExpectedCellValue(sheetName, 12, 1, 0)
};
var pt2expected = new[]
{
new ExpectedCellValue(sheetName, 1, 6, 0),
new ExpectedCellValue(sheetName, 2, 6, 0),
new ExpectedCellValue(sheetName, 3, 6, 0),
new ExpectedCellValue(sheetName, 4, 6, 0),
new ExpectedCellValue(sheetName, 5, 6, 0),
new ExpectedCellValue(sheetName, 6, 6, 0),
new ExpectedCellValue(sheetName, 7, 6, 0),
new ExpectedCellValue(sheetName, 8, 6, 0),
new ExpectedCellValue(sheetName, 9, 6, 0),
new ExpectedCellValue(sheetName, 10, 6, 0),
new ExpectedCellValue(sheetName, 11, 6, 0),
new ExpectedCellValue(sheetName, 12, 6, 0),
new ExpectedCellValue(sheetName, 13, 6, 0),
new ExpectedCellValue(sheetName, 14, 6, 0),
new ExpectedCellValue(sheetName, 15, 6, 0),
new ExpectedCellValue(sheetName, 16, 6, 0),
new ExpectedCellValue(sheetName, 17, 6, 0),
new ExpectedCellValue(sheetName, 18, 6, 0),
new ExpectedCellValue(sheetName, 19, 6, 0),
new ExpectedCellValue(sheetName, 20, 6, 0),
new ExpectedCellValue(sheetName, 21, 6, 0),
new ExpectedCellValue(sheetName, 22, 6, 0),
new ExpectedCellValue(sheetName, 23, 6, 0),
new ExpectedCellValue(sheetName, 24, 6, 0),
new ExpectedCellValue(sheetName, 25, 6, 0),
new ExpectedCellValue(sheetName, 26, 6, 0),
new ExpectedCellValue(sheetName, 27, 6, 0),
new ExpectedCellValue(sheetName, 28, 6, 0),
new ExpectedCellValue(sheetName, 29, 6, 0),
new ExpectedCellValue(sheetName, 30, 6, 0),
new ExpectedCellValue(sheetName, 31, 6, 0),
new ExpectedCellValue(sheetName, 32, 6, 0)
};
var pt3expected = new[]
{
new ExpectedCellValue(sheetName, 1, 11, 0),
new ExpectedCellValue(sheetName, 2, 11, 0),
new ExpectedCellValue(sheetName, 3, 11, 0),
new ExpectedCellValue(sheetName, 4, 11, 0),
new ExpectedCellValue(sheetName, 5, 11, 0),
new ExpectedCellValue(sheetName, 6, 11, 0),
new ExpectedCellValue(sheetName, 7, 11, 0),
new ExpectedCellValue(sheetName, 8, 11, 0),
new ExpectedCellValue(sheetName, 9, 11, 0),
new ExpectedCellValue(sheetName, 10, 11, 0),
new ExpectedCellValue(sheetName, 11, 11, 0),
new ExpectedCellValue(sheetName, 12, 11, 0),
new ExpectedCellValue(sheetName, 13, 11, 0),
new ExpectedCellValue(sheetName, 14, 11, 0),
new ExpectedCellValue(sheetName, 15, 11, 0),
new ExpectedCellValue(sheetName, 16, 11, 0),
new ExpectedCellValue(sheetName, 17, 11, 0),
new ExpectedCellValue(sheetName, 18, 11, 0),
new ExpectedCellValue(sheetName, 19, 11, 0),
new ExpectedCellValue(sheetName, 20, 11, 0),
new ExpectedCellValue(sheetName, 21, 11, 0),
new ExpectedCellValue(sheetName, 22, 11, 0),
new ExpectedCellValue(sheetName, 23, 11, 0),
new ExpectedCellValue(sheetName, 24, 11, 0),
new ExpectedCellValue(sheetName, 25, 11, 0),
new ExpectedCellValue(sheetName, 26, 11, 0),
new ExpectedCellValue(sheetName, 27, 11, 0),
new ExpectedCellValue(sheetName, 28, 11, 0),
new ExpectedCellValue(sheetName, 29, 11, 0),
new ExpectedCellValue(sheetName, 30, 11, 0),
new ExpectedCellValue(sheetName, 31, 11, 0),
new ExpectedCellValue(sheetName, 32, 11, 0),
new ExpectedCellValue(sheetName, 33, 11, 0)
};
this.ValidateIndentation(newFile.File, pt1expected, indentMultiplier);
this.ValidateIndentation(newFile.File, pt2expected, indentMultiplier);
this.ValidateIndentation(newFile.File, pt3expected, indentMultiplier);
}
}
