Pivot Table not refreshing programmatically for larger set of data using .NET

Aspose.Cells Product Family
1

Hi Team,
I have written a piece of code that creates 14 excel sheets . In one of the sheet there are 1,64,000 rows and 8 columns where the formulas are getting calculated and following which 4 pivot tables get created. But the pivot tables are not getting refreshed through the code for such a large set of data and when I open the code trigger the manual refresh, it populates the pivot table with data. I tested the code for 5 rows of data and it refreshed programmatically.

I have used the following code in the function that creates the pivot table to refresh data

                    try
                    {
                       Workbook.CalculateFormula();
                        pivotTable.RefreshDataFlag = true;
                        pivotTable.RefreshDataOnOpeningFile = true;
                        pivotTable.RefreshData();
                        pivotTable.CalculateData();
                        //pivotTable.CalculateRange();
                    }
                    catch (Exception e)
                    {
                        Console.WriteLine(e.Message);
                    }

and also used
workbook.Settings.FormulaSettings.EnableCalculationChain = true;
and workbook.CalculateFormula() for calculation of the formulas in the non pivot sheets. Following is the piece of code

           for (int r = 3; r <= destsheet.Cells.MaxDataRow + 1; r++)
            {
                try { 

              formula_Act = "=IFERROR(VLOOKUP(H" + r + ", 'formula'!A:" + GetExcelColumnName(col_format) + "," + i + ", 0), 0)";
                Columnname_2 = ColumnIndexToColumnLetter(t);
                int bh = t - 9;

                startcell_deal = Columnname_2 + r;

                destsheet.Cells[startcell_deal].Formula = formula_Act;
                   workbook.Settings.FormulaSettings.EnableCalculationChain = true;
              }
                catch (Exception e)
                {
                    Console.WriteLine(e);
                }

}
workbook.CalculateFormula();
But nothing is working. Kindly guide how can I refresh programmatically the pivot tables for such large set of data.
Thanks and Regards
Donna

2

@Donnamitra,

Could you please create a sample VS.NET console application (source code without compilation errors) to reproduce the issue you have mentioned. Also, provide sample files (input file and output file,
etc.) used in your scenario/case. We will evaluate your issue and assist you accordingly.

PS. please zip the sample project and resource files prior attaching.

3

Hi Amjad.

I added the following piece of code and it successfully refreshed all the pivot tables .
Workbook.Worksheets.RefreshPivotTables();

As I understand the code refreshes all the pivot tables in the entire workbook.
Is it meant to handle large amounts of data?

Regards
Donna Mitra

4

@Donnamitra,

Yes, RefreshPivotTables will refresh all the pivot tables (whether these are based on large data) in the spreadsheet/Excel file.

5

Thank you

Best Regards
Donna Mitra

6

Hi Amjad,

One more question. Even after using pt.refreshdata() after each pivot table I had to use Workbook.Worksheets.RefreshPivotTables() at the end.
What according to you went wrong pt.refreshdata() ? Why did this function not refresh the pivot tables?

Best Regards
Donna Mitra

7

@Donnamitra,

Could you try to calculate pivot data after refreshing each pivot table. See the sample lines of code:
e.g.
Sample code:

.....
pt.RefreshData()
pt.CalculateData();

If you still find any issue, kindly create a sample VS.NET console application (source code without compilation errors) to reproduce the issue. Also, provide sample files (input file and output file,
etc.) used in your scenario/case. We will evaluate your issue and assist you accordingly.