Hi, I am using the Pivot Refresh using the following code.
public void RefreshPivotTables(Worksheet sheet)
{
foreach (var pt in sheet.PivotTables)
{
pt.RefreshDataFlag = true;
pt.RefreshData();
pt.CalculateData();
pt.RefreshDataFlag = false;
pt.ShowEmptyRow = false;
pt.PreserveFormatting = true;
pt.EnableDrilldown = true;
pt.ShowDrill = true;
}
}
The pivot table I have has 3 Row Fields, that have details. Some rows are collapsed and some are expanded. When the pivot is refreshed using above code, the first collapsed row is blank, somehow the pivot refresh is not showing that row, it happens exactly for the first row that is collapsed after the expanded rows.
I also tried expanding all the rows and collapsing selected PivotItems using the code snippet, the pivot item is hidden instead of collapsing.
PivotField pf = pt.RowFields["Bucket"];
//Hides the item detail for Rates_Carry_Value.
PivotItem pi = pf.PivotItems["Rates_Carry_Value"];
pf.HideItemDetail(pi.Index, true);
@sthetakali,
Thanks for your query.
Please share your sample file and code snippet with us for our testing. We will reproduce the problem and provide our feedback after analysis.This sample code should be complete compilable console application which can be executed without any missing references. Also please send us the source Excel file, the output file generated by Aspose.Cells and an expected output file generated using Excel. Please mention the version of Aspose.Cells as well which is used for testing.
Please find the working console app, with the test xlsx files.
Extract the zip to c:\Dev
It will create the following contents c:\Dev\templates\AsposeBug -> This has the console application.
In C:\Dev\templates, there are 2 sample files, ( Data Tab is data, ) Pivot is Pivot table based on data.
- Bug_SourceData.xlsx
- Bug_SourceData_PivotExpanded.xlsx
I have provided the test results of the console application attached. I have highlighted the rows that are missing after the execution in “Yellow”.
-
Bug_SourceData_AfterRefresh.xlsxtemplates.zip (1.2 MB)
-
Bug_SourceData_PivotExpanded_AfterRefresh.xlsx
If you refresh manually the excel Pivot table, it restores the missing rows.
The expected result after executing the program should be exactly same as manually performing the pivot refresh in Excel application.
Visual Studio : 2015
.Net v 4.5.2
Excel : v2010
Aspose.Cells for .Net : v18.3.0.0
We cannot upgrade Aspose.Cells to newer version at this time as the custom function implementation we did does not work when we upgrade to newer Aspose.Cells version.
@sthetakali,
There seems to be some issue in the code as it is not resembling the description. You have mentioned that you are using two template files as follows:
-
Bug_SourceData.xlsx
-
Bug_SourceData_PivotExpanded.xlsx
Whereas in the code you are using output of first test as input to the second test. Hence you are not using Bug_SourceData_PivotExpanded.xlsx file anywhere. Please review the sample code and let us know if is it same as desired or not. If there is some change, please update the code and send us again.
Preformatted textThere are 2 separate test cases. Both use different files. I put the two test cases to illustrate the problem with APIs.
- TestPivotRefreshBug
Input File : Bug_SourceData.xlsx
Result File : Bug_SourceData_AfterRefresh.xlsx ( This is produced after running the code ). This is the result of my test, which highlights the problem that refreshing pivot table has bug.
- TestPivotCollapseBug
Input File : Bug_SourceData_PivotExpanded.xlsx ( I have a typo in the file variable )
Result File : Bug_SourceData_PivotExpanded_AfterRefresh.xlsx ( This is produced after running the code ). This is the result of my test, which highlights the problem where Hiding a Pivot Item is not working.
namespace AsposeBug
{
class Program
{
static void Main(string[] args)
{
string path = @“C:\dev\templates”;
if ( !Directory.Exists(path) )
{
Directory.CreateDirectory(path);
}
Console.WriteLine(“Test 1, Pivot Refresh Deletes 1 Pivot Item…”);
string template1 = “Bug_SourceData.xlsx”;
string template2 = “Bug_SourceData_PivotExpanded.xlsx”;
string filePath1 = Path.Combine(path, template1);
Console.WriteLine(“Test 2, Pivot Collapse Deletes the Pivot Item…”);
TestPivotRefreshBug(filePath1);
//Same behaviour
//TestPivotRefreshBug2(filePath1);
string filePath2 = Path.Combine(path, template2);
TestPivotCollapseBug(filePath2);
}
public static void TestPivotRefreshBug(string file)
{
var book = new Workbook(file);
string sheetName = "Pivot";
var sheet = book.Worksheets[sheetName];
foreach (var pt in sheet.PivotTables)
{
Console.WriteLine($"Refreshing Pivot table {pt.Name} in {sheet.Name}");
//pt.RefreshDataFlag = true;
//pt.RefreshData();
pt.RefreshDataFlag = false;
pt.RefreshData();
pt.CalculateData();
pt.RefreshDataFlag = true;
pt.PreserveFormatting = true;
pt.EnableDrilldown = true;
pt.ShowDrill = true;
}
book.Save(@"C:\dev\templates\Bug_SourceData_AfterRefresh.xlsx");
}
public static void TestPivotRefreshBug2(string file)
{
var book = new Workbook(file);
string sheetName = "Pivot";
var sheet = book.Worksheets[sheetName];
foreach (var pt in sheet.PivotTables)
{
Console.WriteLine($"Refreshing Pivot table {pt.Name} in {sheet.Name}");
pt.RefreshDataFlag = true;
pt.RefreshData();
pt.RefreshDataFlag = false;
pt.CalculateData();
pt.PreserveFormatting = true;
pt.EnableDrilldown = true;
pt.ShowDrill = true;
}
book.Save(@"C:\dev\templates\Bug_SourceData_AfterRefresh.xlsx");
}
public static void TestPivotCollapseBug(string file)
{
var book = new Workbook(file);
string sheetName = "Pivot";
var sheet = book.Worksheets[sheetName];
foreach (var pt in sheet.PivotTables)
{
Console.WriteLine($"Refreshing Pivot table {pt.Name} in {sheet.Name}");
pt.RefreshDataFlag = false;
pt.RefreshData();
PivotField pf = pt.RowFields["Bucket"];
//Should Hide the item detail for Rates_Carry_Value.
PivotItem pi = pf.PivotItems["Rates_Carry_Value"];
pf.HideItemDetail(pi.Index, true);
pt.CalculateData();
pt.RefreshDataFlag = true;
pt.PreserveFormatting = true;
pt.EnableDrilldown = true;
pt.ShowDrill = true;
}
book.Save(@"C:\dev\templates\Bug_SourceData_PivotExpanded_AfterRefresh.xlsx");
}
}
}
@sthetakali,
We were able to observe the issue but we need to look into it more. We have logged the issue in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.
This issue has been logged as
CELLSNET-46291 - Issues in Pivot tables while Refreshing and hiding pivot items
Is there an ETA or any work around to this issue ?
@sthetakali,
As this issue is logged few days back and also it is little complex too, so it is still not resolved. We will provide you ETA as soon as our analysis is done and feedback is ready for sharing.
@sthetakali,
This is to inform you that your issue would be fixed in the first week of September 2018. Once the issue is fixed, we will share the fixed version.
Keep in touch.
@sthetakali,
Please try our latest version/fix: Aspose.Cells for .NET v18.8.5:
Your issue should be fixed in it.
Let us know your feedback.
Hi Amjad, I cannot access drop box from work, is it possible to provide a link for the download from Aspose.Cell web site or send the zip file
Thank you for the update, I was able to test and confirm it is resolved. Although, I fixed the issue using OpenXml APIs manipulating the xml for hiding the row item. This would be very helpful. I need to do a full regression to upgrade to this version, if my custom functions work.
Thank you
-Srinivas
@sthetakali,
Thank you for the feedback. Feel free to contact us again after thorough testing.
Hi, I am back with a new bug in the Pivot Refresh.
This time it has to do with sub totals.
Please find the complete code.
When some of the row items are collapsed, they will show the sub totals in that row in excel, even though the row field is not sub-totaled. See image for Excel behavior.
excel_behavior.JPG (283.9 KB)
- Create a folder C:\Dev
- Extract the contents and run the program.
Bug_SourceData.xlsx is the input file, when pivot table is refreshed, it is missing the sub-totals as shown in the output file Bug_SourceData_AfterRefresh.xlsx.
The rows are highlighted in red that have issues.
I am using the v18.5.5 which is referenced in the project.
@sthetakali,
It seems that you forgot to attach the project here. Please attach the sample project which can be used to reproduce the issue.