We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Pivot table refresh is not working properly

Hi Team,

I am getting an issue with PivotTable refresh when using the aspose.

When using the default excel functionality and refresh the pivot manually it works fine.

Below is an image describing the issue -

PivotIssueExplained.png (45.0 KB)

SourceCode - PivotIssue.zip (56.8 KB)

class Program
{
    static void Main(string[] args)
    {
        FileStream fs;
        fs = File.Open(@"Book1.xlsx", FileMode.Open);
        Workbook wb = new Workbook(fs);
        var sheet = wb.Worksheets["Pivot"];
        sheet.PivotTables.FirstOrDefault().RefreshData();
        sheet.PivotTables.FirstOrDefault().CalculateData();
        fs.Close();
        wb.Save(@"Book1.xlsx");
    }
}

Steps to reproduce :-

1.) execute the source code attached above.
2.) the excel sheet book1.xlsx which is attached originally does not have any extra line.
3.) after the execution of the code go to the file and open it —> /bin/debug/book1.xlsx
4.) you will see an extra line in the pivot table. when do a refreshAll manually from the sheet the extra line goes away.

Please advise a solution to this.

Thanks much.
Shobhit Bansal

@hi.shobhit76,
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-48475 - Pivot table refresh is not working properly

hi… Any updated on this please ?

@hi.shobhit82,

Your issue is not resolved yet. Once we figure it out or have some other updates, we will let you know.

@hi.shobhit82

In the attachment, we didn’t find your test file. But we simulated a test file named “a.xlsx” according to your picture, but we can’t reproduce your issue. Can you provide your test file? a.zip (110.7 KB)

Please use this sheet
PivotIssueExcel.zip (13.3 KB)

@hi.shobhit82,

Thanks for the file.
We have logged it with the ticket. We will be looking into it soon.

@hi.shobhit82,

This is to inform you that your issue has been resolved now. We will provide you the fixed version within the next few days after performing QA and incorporating other enhancements and fixes.

@hi.shobhit82,
Please try our latest version/fix: Aspose.Cells for .NET v21.8.4 (attached)
Aspose.Cells21.8.4 For .Net2_AuthenticodeSigned.Zip (5.6 MB)
Aspose.Cells21.8.4 For .Net4.0.Zip (5.6 MB)
Aspose.Cells21.8.4 For .NetStandard20.Zip (5.6 MB)

Your issue should be fixed in it.
Let us know your feedback.

It seems to be working with the sample sheet provided, but I need more time to do a thorough testing before I confirm it is working.

Also, Could I please also know the root cause here for better understanding of the issue ?

@hi.shobhit82,

Please take your time to thoroughly evaluate the new fix. Hopefully it will pass your tests.

We will give you root cause of the issue soon.

@hi.shobhit82,

The issue was due to following reasons and findings which are fixed in the new fix.

  1. When the number of PivotField in the data area is greater than or equal to 2.
  2. “Display field captions and filter drop downs” and “Classic PivotTable layout” are not checked in the PivotTable display setting.
  3. There is a problem in column area calculation.

The issues you have found earlier (filed as CELLSNET-48475) have been fixed in this update. This message was posted using Bugs notification tool by simon.zhao