public override void ValidatePivotTableRefreshDataField(FileInfo file, string sheetName)
{
TestHelperUtility.ValidateWorksheet(file, sheetName, new[]
{
new ExpectedCellValue(sheetName, 14, 1, "January"),
new ExpectedCellValue(sheetName, 15, 1, "February"),
new ExpectedCellValue(sheetName, 16, 1, "March"),
new ExpectedCellValue(sheetName, 17, 1, "Grand Total"),
new ExpectedCellValue(sheetName, 13, 2, "Car Rack"),
new ExpectedCellValue(sheetName, 14, 2, 57616.02),
new ExpectedCellValue(sheetName, 16, 2, "#DIV/0!"),
new ExpectedCellValue(sheetName, 17, 2, 43212.02),
new ExpectedCellValue(sheetName, 13, 3, "Headlamp"),
new ExpectedCellValue(sheetName, 16, 3, "#DIV/0!"),
new ExpectedCellValue(sheetName, 17, 3, "#DIV/0!"),
new ExpectedCellValue(sheetName, 13, 4, "Sleeping Bag"),
new ExpectedCellValue(sheetName, 15, 4, "#DIV/0!"),
new ExpectedCellValue(sheetName, 17, 4, "#DIV/0!"),
new ExpectedCellValue(sheetName, 13, 5, "Tent"),
new ExpectedCellValue(sheetName, 15, 5, "#DIV/0!"),
new ExpectedCellValue(sheetName, 17, 5, "#DIV/0!"),
new ExpectedCellValue(sheetName, 13, 6, "Grand Total"),
new ExpectedCellValue(sheetName, 14, 6, 57616.02),
new ExpectedCellValue(sheetName, 15, 6, 599512.5),
new ExpectedCellValue(sheetName, 16, 6, 325229.19),
new ExpectedCellValue(sheetName, 17, 6, 188180.73)
});
}
public override void ValidatePivotTableRefreshDataField(FileInfo file, string sheetName)
{
TestHelperUtility.ValidateWorksheet(file, sheetName, new[]
{
new ExpectedCellValue(sheetName, 14, 1, "January"),
new ExpectedCellValue(sheetName, 15, 1, "February"),
new ExpectedCellValue(sheetName, 16, 1, "March"),
new ExpectedCellValue(sheetName, 17, 1, "Grand Total"),
new ExpectedCellValue(sheetName, 13, 2, "Car Rack"),
new ExpectedCellValue(sheetName, 14, 2, 240.03),
new ExpectedCellValue(sheetName, 16, 2, "#DIV/0!"),
new ExpectedCellValue(sheetName, 17, 2, 207.875),
new ExpectedCellValue(sheetName, 13, 3, "Headlamp"),
new ExpectedCellValue(sheetName, 16, 3, "#DIV/0!"),
new ExpectedCellValue(sheetName, 17, 3, "#DIV/0!"),
new ExpectedCellValue(sheetName, 13, 4, "Sleeping Bag"),
new ExpectedCellValue(sheetName, 15, 4, "#DIV/0!"),
new ExpectedCellValue(sheetName, 17, 4, "#DIV/0!"),
new ExpectedCellValue(sheetName, 13, 5, "Tent"),
new ExpectedCellValue(sheetName, 15, 5, "#DIV/0!"),
new ExpectedCellValue(sheetName, 17, 5, "#DIV/0!"),
new ExpectedCellValue(sheetName, 13, 6, "Grand Total"),
new ExpectedCellValue(sheetName, 14, 6, 240.03),
new ExpectedCellValue(sheetName, 15, 6, 774.28),
new ExpectedCellValue(sheetName, 16, 6, 570.29),
new ExpectedCellValue(sheetName, 17, 6, 433.80)
});
}
public override void ValidatePivotTableRefreshDataField(FileInfo file, string sheetName)
{
TestHelperUtility.ValidateWorksheet(file, sheetName, new[]
{
new ExpectedCellValue(sheetName, 14, 1, "January"),
new ExpectedCellValue(sheetName, 15, 1, "February"),
new ExpectedCellValue(sheetName, 16, 1, "March"),
new ExpectedCellValue(sheetName, 17, 1, "Grand Total"),
new ExpectedCellValue(sheetName, 13, 2, "Car Rack"),
new ExpectedCellValue(sheetName, 14, 2, 287446327.94),
new ExpectedCellValue(sheetName, 16, 2, 831.5),
new ExpectedCellValue(sheetName, 17, 2, 239011621680.03),
new ExpectedCellValue(sheetName, 13, 3, "Headlamp"),
new ExpectedCellValue(sheetName, 16, 3, 24.99),
new ExpectedCellValue(sheetName, 17, 3, 24.99),
new ExpectedCellValue(sheetName, 13, 4, "Sleeping Bag"),
new ExpectedCellValue(sheetName, 15, 4, 99d),
new ExpectedCellValue(sheetName, 17, 4, 99d),
new ExpectedCellValue(sheetName, 13, 5, "Tent"),
new ExpectedCellValue(sheetName, 15, 5, 1194d),
new ExpectedCellValue(sheetName, 17, 5, 1194d),
new ExpectedCellValue(sheetName, 13, 6, "Grand Total"),
new ExpectedCellValue(sheetName, 14, 6, 287446327.94),
new ExpectedCellValue(sheetName, 15, 6, 118206),
new ExpectedCellValue(sheetName, 16, 6, 20779.18),
new ExpectedCellValue(sheetName, 17, 6, 7.0603266773022118E+17)
});
}
public override void ValidatePivotTableRefreshDataField(FileInfo file, string sheetName)
{
TestHelperUtility.ValidateWorksheet(file, sheetName, new[]
{
new ExpectedCellValue(sheetName, 14, 1, "January"),
new ExpectedCellValue(sheetName, 15, 1, "February"),
new ExpectedCellValue(sheetName, 16, 1, "March"),
new ExpectedCellValue(sheetName, 17, 1, "Grand Total"),
new ExpectedCellValue(sheetName, 13, 2, "Car Rack"),
new ExpectedCellValue(sheetName, 14, 2, 415.75),
new ExpectedCellValue(sheetName, 16, 2, 831.5),
new ExpectedCellValue(sheetName, 17, 2, 415.75),
new ExpectedCellValue(sheetName, 13, 3, "Headlamp"),
new ExpectedCellValue(sheetName, 16, 3, 24.99),
new ExpectedCellValue(sheetName, 17, 3, 24.99),
new ExpectedCellValue(sheetName, 13, 4, "Sleeping Bag"),
new ExpectedCellValue(sheetName, 15, 4, 99d),
new ExpectedCellValue(sheetName, 17, 4, 99d),
new ExpectedCellValue(sheetName, 13, 5, "Tent"),
new ExpectedCellValue(sheetName, 15, 5, 1194d),
new ExpectedCellValue(sheetName, 17, 5, 1194d),
new ExpectedCellValue(sheetName, 13, 6, "Grand Total"),
new ExpectedCellValue(sheetName, 14, 6, 415.75),
new ExpectedCellValue(sheetName, 15, 6, 99d),
new ExpectedCellValue(sheetName, 16, 6, 24.99),
new ExpectedCellValue(sheetName, 17, 6, 24.99)
});
}
public override void ValidatePivotTableRefreshMultipleColumnDataFields(FileInfo file, string sheetName)
{
TestHelperUtility.ValidateWorksheet(file, sheetName, new[]
{
new ExpectedCellValue(sheetName, 24, 1, "January"),
new ExpectedCellValue(sheetName, 25, 1, "Car Rack"),
new ExpectedCellValue(sheetName, 26, 1, "February"),
new ExpectedCellValue(sheetName, 27, 1, "Sleeping Bag"),
new ExpectedCellValue(sheetName, 28, 1, "Tent"),
new ExpectedCellValue(sheetName, 29, 1, "March"),
new ExpectedCellValue(sheetName, 30, 1, "Car Rack"),
new ExpectedCellValue(sheetName, 31, 1, "Headlamp"),
new ExpectedCellValue(sheetName, 32, 1, "Grand Total"),
new ExpectedCellValue(sheetName, 22, 2, "Chicago"),
new ExpectedCellValue(sheetName, 23, 2, "Var of Total"),
new ExpectedCellValue(sheetName, 24, 2, "#DIV/0!"),
new ExpectedCellValue(sheetName, 25, 2, "#DIV/0!"),
new ExpectedCellValue(sheetName, 29, 2, "#DIV/0!"),
new ExpectedCellValue(sheetName, 31, 2, "#DIV/0!"),
new ExpectedCellValue(sheetName, 32, 2, 325229.19),
new ExpectedCellValue(sheetName, 23, 3, "Sum of Units Sold"),
new ExpectedCellValue(sheetName, 24, 3, 2d),
new ExpectedCellValue(sheetName, 25, 3, 2d),
new ExpectedCellValue(sheetName, 29, 3, 1d),
new ExpectedCellValue(sheetName, 31, 3, 1d),
new ExpectedCellValue(sheetName, 32, 3, 3d),
new ExpectedCellValue(sheetName, 22, 4, "Nashville"),
new ExpectedCellValue(sheetName, 23, 4, "Var of Total"),
new ExpectedCellValue(sheetName, 24, 4, "#DIV/0!"),
new ExpectedCellValue(sheetName, 25, 4, "#DIV/0!"),
new ExpectedCellValue(sheetName, 26, 4, "#DIV/0!"),
new ExpectedCellValue(sheetName, 28, 4, "#DIV/0!"),
new ExpectedCellValue(sheetName, 29, 4, "#DIV/0!"),
new ExpectedCellValue(sheetName, 30, 4, "#DIV/0!"),
new ExpectedCellValue(sheetName, 32, 4, 43802.08),
new ExpectedCellValue(sheetName, 23, 5, "Sum of Units Sold"),
new ExpectedCellValue(sheetName, 24, 5, 2d),
new ExpectedCellValue(sheetName, 25, 5, 2d),
new ExpectedCellValue(sheetName, 26, 5, 6d),
new ExpectedCellValue(sheetName, 28, 5, 6d),
new ExpectedCellValue(sheetName, 29, 5, 2d),
new ExpectedCellValue(sheetName, 30, 5, 2d),
new ExpectedCellValue(sheetName, 32, 5, 10d),
new ExpectedCellValue(sheetName, 22, 6, "San Francisco"),
new ExpectedCellValue(sheetName, 23, 6, "Var of Total"),
new ExpectedCellValue(sheetName, 24, 6, "#DIV/0!"),
new ExpectedCellValue(sheetName, 25, 6, "#DIV/0!"),
new ExpectedCellValue(sheetName, 26, 6, "#DIV/0!"),
new ExpectedCellValue(sheetName, 26, 6, "#DIV/0!"),
new ExpectedCellValue(sheetName, 32, 6, 50165.28),
new ExpectedCellValue(sheetName, 23, 7, "Sum of Units Sold"),
new ExpectedCellValue(sheetName, 24, 7, 1d),
new ExpectedCellValue(sheetName, 25, 7, 1d),
new ExpectedCellValue(sheetName, 26, 7, 1d),
new ExpectedCellValue(sheetName, 27, 7, 1d),
new ExpectedCellValue(sheetName, 32, 7, 2d),
new ExpectedCellValue(sheetName, 22, 8, "Total Var of Total"),
new ExpectedCellValue(sheetName, 24, 8, 57616.02),
new ExpectedCellValue(sheetName, 25, 8, 57616.02),
new ExpectedCellValue(sheetName, 26, 8, 599512.5),
new ExpectedCellValue(sheetName, 27, 8, "#DIV/0!"),
new ExpectedCellValue(sheetName, 28, 8, "#DIV/0!"),
new ExpectedCellValue(sheetName, 29, 8, 325229.19),
new ExpectedCellValue(sheetName, 30, 8, "#DIV/0!"),
new ExpectedCellValue(sheetName, 31, 8, "#DIV/0!"),
new ExpectedCellValue(sheetName, 32, 8, 188180.73),
new ExpectedCellValue(sheetName, 22, 9, "Total Sum of Units Sold"),
new ExpectedCellValue(sheetName, 24, 9, 5d),
new ExpectedCellValue(sheetName, 25, 9, 5d),
new ExpectedCellValue(sheetName, 26, 9, 7d),
new ExpectedCellValue(sheetName, 27, 9, 1d),
new ExpectedCellValue(sheetName, 28, 9, 6d),
new ExpectedCellValue(sheetName, 29, 9, 3d),
new ExpectedCellValue(sheetName, 30, 9, 2d),
new ExpectedCellValue(sheetName, 31, 9, 1d),
new ExpectedCellValue(sheetName, 32, 9, 15d)
});
}
public override void ValidatePivotTableRefreshMultipleRowDataFields(FileInfo file, string sheetName)
{
TestHelperUtility.ValidateWorksheet(file, sheetName, new[]
{
new ExpectedCellValue(sheetName, 39, 1, "January"),
new ExpectedCellValue(sheetName, 40, 1, "Var of Total"),
new ExpectedCellValue(sheetName, 41, 1, "Sum of Units Sold"),
new ExpectedCellValue(sheetName, 42, 1, "February"),
new ExpectedCellValue(sheetName, 43, 1, "Var of Total"),
new ExpectedCellValue(sheetName, 44, 1, "Sum of Units Sold"),
new ExpectedCellValue(sheetName, 45, 1, "March"),
new ExpectedCellValue(sheetName, 46, 1, "Var of Total"),
new ExpectedCellValue(sheetName, 47, 1, "Sum of Units Sold"),
new ExpectedCellValue(sheetName, 48, 1, "Total Var of Total"),
new ExpectedCellValue(sheetName, 49, 1, "Total Sum of Units Sold"),
new ExpectedCellValue(sheetName, 37, 2, "Chicago"),
new ExpectedCellValue(sheetName, 38, 2, "Car Rack"),
new ExpectedCellValue(sheetName, 40, 2, "#DIV/0!"),
new ExpectedCellValue(sheetName, 41, 2, 2d),
new ExpectedCellValue(sheetName, 48, 2, "#DIV/0!"),
new ExpectedCellValue(sheetName, 49, 2, 2d),
new ExpectedCellValue(sheetName, 38, 3, "Headlamp"),
new ExpectedCellValue(sheetName, 46, 3, "#DIV/0!"),
new ExpectedCellValue(sheetName, 47, 3, 1d),
new ExpectedCellValue(sheetName, 48, 3, "#DIV/0!"),
new ExpectedCellValue(sheetName, 49, 3, 1d),
new ExpectedCellValue(sheetName, 37, 4, "Chicago Total"),
new ExpectedCellValue(sheetName, 40, 4, "#DIV/0!"),
new ExpectedCellValue(sheetName, 41, 4, 2d),
new ExpectedCellValue(sheetName, 46, 4, "#DIV/0!"),
new ExpectedCellValue(sheetName, 47, 4, 1d),
new ExpectedCellValue(sheetName, 48, 4, 325229.19),
new ExpectedCellValue(sheetName, 49, 4, 3d),
new ExpectedCellValue(sheetName, 37, 5, "Nashville"),
new ExpectedCellValue(sheetName, 38, 5, "Car Rack"),
new ExpectedCellValue(sheetName, 40, 5, "#DIV/0!"),
new ExpectedCellValue(sheetName, 41, 5, 2d),
new ExpectedCellValue(sheetName, 46, 5, "#DIV/0!"),
new ExpectedCellValue(sheetName, 47, 5, 2d),
new ExpectedCellValue(sheetName, 48, 5, 0),
new ExpectedCellValue(sheetName, 49, 5, 4d),
new ExpectedCellValue(sheetName, 38, 6, "Tent"),
new ExpectedCellValue(sheetName, 43, 6, "#DIV/0!"),
new ExpectedCellValue(sheetName, 44, 6, 6d),
new ExpectedCellValue(sheetName, 48, 6, "#DIV/0!"),
new ExpectedCellValue(sheetName, 49, 6, 6d),
new ExpectedCellValue(sheetName, 37, 7, "Nashville Total"),
new ExpectedCellValue(sheetName, 40, 7, "#DIV/0!"),
new ExpectedCellValue(sheetName, 41, 7, 2d),
new ExpectedCellValue(sheetName, 43, 7, "#DIV/0!"),
new ExpectedCellValue(sheetName, 44, 7, 6d),
new ExpectedCellValue(sheetName, 46, 7, "#DIV/0!"),
new ExpectedCellValue(sheetName, 47, 7, 2d),
new ExpectedCellValue(sheetName, 48, 7, 43802.08),
new ExpectedCellValue(sheetName, 49, 7, 10d),
new ExpectedCellValue(sheetName, 37, 8, "San Francisco"),
new ExpectedCellValue(sheetName, 38, 8, "Car Rack"),
new ExpectedCellValue(sheetName, 40, 8, "#DIV/0!"),
new ExpectedCellValue(sheetName, 41, 8, 1d),
new ExpectedCellValue(sheetName, 48, 8, "#DIV/0!"),
new ExpectedCellValue(sheetName, 49, 8, 1d),
new ExpectedCellValue(sheetName, 38, 9, "Sleeping Bag"),
new ExpectedCellValue(sheetName, 43, 9, "#DIV/0!"),
new ExpectedCellValue(sheetName, 44, 9, 1d),
new ExpectedCellValue(sheetName, 48, 9, "#DIV/0!"),
new ExpectedCellValue(sheetName, 49, 9, 1d),
new ExpectedCellValue(sheetName, 37, 10, "San Francisco Total"),
new ExpectedCellValue(sheetName, 40, 10, "#DIV/0!"),
new ExpectedCellValue(sheetName, 41, 10, 1d),
new ExpectedCellValue(sheetName, 43, 10, "#DIV/0!"),
new ExpectedCellValue(sheetName, 44, 10, 1d),
new ExpectedCellValue(sheetName, 48, 10, 50165.28),
new ExpectedCellValue(sheetName, 49, 10, 2d),
new ExpectedCellValue(sheetName, 37, 11, "Grand Total"),
new ExpectedCellValue(sheetName, 40, 11, 57616.02),
new ExpectedCellValue(sheetName, 41, 11, 5d),
new ExpectedCellValue(sheetName, 43, 11, 599512.5),
new ExpectedCellValue(sheetName, 44, 11, 7d),
new ExpectedCellValue(sheetName, 46, 11, 325229.19),
new ExpectedCellValue(sheetName, 47, 11, 3d),
new ExpectedCellValue(sheetName, 48, 11, 188180.73),
new ExpectedCellValue(sheetName, 49, 11, 15d)
});
}
public override void ValidatePivotTableRefreshMultipleColumnDataFields(FileInfo file, string sheetName)
{
TestHelperUtility.ValidateWorksheet(file, sheetName, new[]
{
new ExpectedCellValue(sheetName, 24, 1, "January"),
new ExpectedCellValue(sheetName, 25, 1, "Car Rack"),
new ExpectedCellValue(sheetName, 26, 1, "February"),
new ExpectedCellValue(sheetName, 27, 1, "Sleeping Bag"),
new ExpectedCellValue(sheetName, 28, 1, "Tent"),
new ExpectedCellValue(sheetName, 29, 1, "March"),
new ExpectedCellValue(sheetName, 30, 1, "Car Rack"),
new ExpectedCellValue(sheetName, 31, 1, "Headlamp"),
new ExpectedCellValue(sheetName, 32, 1, "Grand Total"),
new ExpectedCellValue(sheetName, 22, 2, "Chicago"),
new ExpectedCellValue(sheetName, 23, 2, "Min of Total"),
new ExpectedCellValue(sheetName, 24, 2, 831.5),
new ExpectedCellValue(sheetName, 25, 2, 831.5),
new ExpectedCellValue(sheetName, 29, 2, 24.99),
new ExpectedCellValue(sheetName, 31, 2, 24.99),
new ExpectedCellValue(sheetName, 32, 2, 24.99),
new ExpectedCellValue(sheetName, 23, 3, "Product of Units Sold"),
new ExpectedCellValue(sheetName, 24, 3, 2d),
new ExpectedCellValue(sheetName, 25, 3, 2d),
new ExpectedCellValue(sheetName, 29, 3, 1d),
new ExpectedCellValue(sheetName, 31, 3, 1d),
new ExpectedCellValue(sheetName, 32, 3, 2d),
new ExpectedCellValue(sheetName, 22, 4, "Nashville"),
new ExpectedCellValue(sheetName, 23, 4, "Min of Total"),
new ExpectedCellValue(sheetName, 24, 4, 831.5),
new ExpectedCellValue(sheetName, 25, 4, 831.5),
new ExpectedCellValue(sheetName, 26, 4, 1194d),
new ExpectedCellValue(sheetName, 28, 4, 1194d),
new ExpectedCellValue(sheetName, 29, 4, 831.5),
new ExpectedCellValue(sheetName, 30, 4, 831.5),
new ExpectedCellValue(sheetName, 32, 4, 831.5),
new ExpectedCellValue(sheetName, 23, 5, "Product of Units Sold"),
new ExpectedCellValue(sheetName, 24, 5, 2d),
new ExpectedCellValue(sheetName, 25, 5, 2d),
new ExpectedCellValue(sheetName, 26, 5, 6d),
new ExpectedCellValue(sheetName, 28, 5, 6d),
new ExpectedCellValue(sheetName, 29, 5, 2d),
new ExpectedCellValue(sheetName, 30, 5, 2d),
new ExpectedCellValue(sheetName, 32, 5, 24d),
new ExpectedCellValue(sheetName, 22, 6, "San Francisco"),
new ExpectedCellValue(sheetName, 23, 6, "Min of Total"),
new ExpectedCellValue(sheetName, 24, 6, 415.75),
new ExpectedCellValue(sheetName, 25, 6, 415.75),
new ExpectedCellValue(sheetName, 26, 6, 99d),
new ExpectedCellValue(sheetName, 26, 6, 99d),
new ExpectedCellValue(sheetName, 32, 6, 99d),
new ExpectedCellValue(sheetName, 23, 7, "Product of Units Sold"),
new ExpectedCellValue(sheetName, 24, 7, 1d),
new ExpectedCellValue(sheetName, 25, 7, 1d),
new ExpectedCellValue(sheetName, 26, 7, 1d),
new ExpectedCellValue(sheetName, 27, 7, 1d),
new ExpectedCellValue(sheetName, 32, 7, 1d),
new ExpectedCellValue(sheetName, 22, 8, "Total Min of Total"),
new ExpectedCellValue(sheetName, 24, 8, 415.75),
new ExpectedCellValue(sheetName, 25, 8, 415.75),
new ExpectedCellValue(sheetName, 26, 8, 99d),
new ExpectedCellValue(sheetName, 27, 8, 99d),
new ExpectedCellValue(sheetName, 28, 8, 1194d),
new ExpectedCellValue(sheetName, 29, 8, 24.99),
new ExpectedCellValue(sheetName, 30, 8, 831.5),
new ExpectedCellValue(sheetName, 31, 8, 24.99),
new ExpectedCellValue(sheetName, 32, 8, 24.99),
new ExpectedCellValue(sheetName, 22, 9, "Total Product of Units Sold"),
new ExpectedCellValue(sheetName, 24, 9, 4d),
new ExpectedCellValue(sheetName, 25, 9, 4d),
new ExpectedCellValue(sheetName, 26, 9, 6d),
new ExpectedCellValue(sheetName, 27, 9, 1d),
new ExpectedCellValue(sheetName, 28, 9, 6d),
new ExpectedCellValue(sheetName, 29, 9, 2d),
new ExpectedCellValue(sheetName, 30, 9, 2d),
new ExpectedCellValue(sheetName, 31, 9, 1d),
new ExpectedCellValue(sheetName, 32, 9, 48d)
});
}
public override void ValidatePivotTableRefreshMultipleRowDataFields(FileInfo file, string sheetName)
{
TestHelperUtility.ValidateWorksheet(file, sheetName, new[]
{
new ExpectedCellValue(sheetName, 39, 1, "January"),
new ExpectedCellValue(sheetName, 40, 1, "Max of Total"),
new ExpectedCellValue(sheetName, 41, 1, "Product of Units Sold"),
new ExpectedCellValue(sheetName, 42, 1, "February"),
new ExpectedCellValue(sheetName, 43, 1, "Max of Total"),
new ExpectedCellValue(sheetName, 44, 1, "Product of Units Sold"),
new ExpectedCellValue(sheetName, 45, 1, "March"),
new ExpectedCellValue(sheetName, 46, 1, "Max of Total"),
new ExpectedCellValue(sheetName, 47, 1, "Product of Units Sold"),
new ExpectedCellValue(sheetName, 48, 1, "Total Max of Total"),
new ExpectedCellValue(sheetName, 49, 1, "Total Product of Units Sold"),
new ExpectedCellValue(sheetName, 37, 2, "Chicago"),
new ExpectedCellValue(sheetName, 38, 2, "Car Rack"),
new ExpectedCellValue(sheetName, 40, 2, 831.5),
new ExpectedCellValue(sheetName, 41, 2, 2d),
new ExpectedCellValue(sheetName, 48, 2, 831.5),
new ExpectedCellValue(sheetName, 49, 2, 2d),
new ExpectedCellValue(sheetName, 38, 3, "Headlamp"),
new ExpectedCellValue(sheetName, 46, 3, 24.99),
new ExpectedCellValue(sheetName, 47, 3, 1d),
new ExpectedCellValue(sheetName, 48, 3, 24.99),
new ExpectedCellValue(sheetName, 49, 3, 1d),
new ExpectedCellValue(sheetName, 37, 4, "Chicago Total"),
new ExpectedCellValue(sheetName, 40, 4, 831.5),
new ExpectedCellValue(sheetName, 41, 4, 2d),
new ExpectedCellValue(sheetName, 46, 4, 24.99),
new ExpectedCellValue(sheetName, 47, 4, 1d),
new ExpectedCellValue(sheetName, 48, 4, 831.5),
new ExpectedCellValue(sheetName, 49, 4, 2d),
new ExpectedCellValue(sheetName, 37, 5, "Nashville"),
new ExpectedCellValue(sheetName, 38, 5, "Car Rack"),
new ExpectedCellValue(sheetName, 40, 5, 831.5),
new ExpectedCellValue(sheetName, 41, 5, 2d),
new ExpectedCellValue(sheetName, 46, 5, 831.5),
new ExpectedCellValue(sheetName, 47, 5, 2d),
new ExpectedCellValue(sheetName, 48, 5, 831.5),
new ExpectedCellValue(sheetName, 49, 5, 4d),
new ExpectedCellValue(sheetName, 38, 6, "Tent"),
new ExpectedCellValue(sheetName, 43, 6, 1194d),
new ExpectedCellValue(sheetName, 44, 6, 6d),
new ExpectedCellValue(sheetName, 48, 6, 1194d),
new ExpectedCellValue(sheetName, 49, 6, 6d),
new ExpectedCellValue(sheetName, 37, 7, "Nashville Total"),
new ExpectedCellValue(sheetName, 40, 7, 831.5),
new ExpectedCellValue(sheetName, 41, 7, 2d),
new ExpectedCellValue(sheetName, 43, 7, 1194d),
new ExpectedCellValue(sheetName, 44, 7, 6d),
new ExpectedCellValue(sheetName, 46, 7, 831.5),
new ExpectedCellValue(sheetName, 47, 7, 2d),
new ExpectedCellValue(sheetName, 48, 7, 1194d),
new ExpectedCellValue(sheetName, 49, 7, 24d),
new ExpectedCellValue(sheetName, 37, 8, "San Francisco"),
new ExpectedCellValue(sheetName, 38, 8, "Car Rack"),
new ExpectedCellValue(sheetName, 40, 8, 415.75),
new ExpectedCellValue(sheetName, 41, 8, 1d),
new ExpectedCellValue(sheetName, 48, 8, 415.75),
new ExpectedCellValue(sheetName, 49, 8, 1d),
new ExpectedCellValue(sheetName, 38, 9, "Sleeping Bag"),
new ExpectedCellValue(sheetName, 43, 9, 99d),
new ExpectedCellValue(sheetName, 44, 9, 1d),
new ExpectedCellValue(sheetName, 48, 9, 99d),
new ExpectedCellValue(sheetName, 49, 9, 1d),
new ExpectedCellValue(sheetName, 37, 10, "San Francisco Total"),
new ExpectedCellValue(sheetName, 40, 10, 415.75),
new ExpectedCellValue(sheetName, 41, 10, 1d),
new ExpectedCellValue(sheetName, 43, 10, 99d),
new ExpectedCellValue(sheetName, 44, 10, 1d),
new ExpectedCellValue(sheetName, 48, 10, 415.75),
new ExpectedCellValue(sheetName, 49, 10, 1d),
new ExpectedCellValue(sheetName, 37, 11, "Grand Total"),
new ExpectedCellValue(sheetName, 40, 11, 831.5),
new ExpectedCellValue(sheetName, 41, 11, 4d),
new ExpectedCellValue(sheetName, 43, 11, 1194d),
new ExpectedCellValue(sheetName, 44, 11, 6d),
new ExpectedCellValue(sheetName, 46, 11, 831.5),
new ExpectedCellValue(sheetName, 47, 11, 2d),
new ExpectedCellValue(sheetName, 48, 11, 1194d),
new ExpectedCellValue(sheetName, 49, 11, 48d)
});
}
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)
});
}
}