Pivot chart not recalculating for PDF

Aspose.Cells Product Family
1

Hi,

I have an Aspose.Cells issue with getting Pivot charts to recalculate, but only for PDF.

I create a workbook template with raw data in one sheet, a Pivot chart in another and the chart’s pivot table in a third sheet. The pivot table data source is a named range. I then use Aspose.Cells to add some sample data to the raw data sheet, resize the named range, recalculate everything, and save to disk.

This works fine when saving to Excel. When saving to PDF, the pivot tables are updated OK, but the chart never recalculates to reflect the new data.

I am uploading a simple solution that reproduces the issue, as well as the 2 output files that show the difference.

Can you please help me solve this issue?

Thanks,
Rick
2

Hi,


Thanks for your posting and using Aspose.Cells.

It seems, there is some problem when you refresh pivot data of chart inside the foreach loop, but if you do it after foreach loop, it works fine.

Please see the following sample code, the red lines inside it and its output pdf for your reference.

C#
Workbook wb = new Workbook(“sampleIn.xlsx”);

//simulate getting new data set
Worksheet ws = wb.Worksheets[“Raw_Data”];
ws.Cells.Clear();

int currentRow = 0;
Cell clHead = ws.Cells[currentRow++, 0];
clHead.Value = “Color”;

for (int i = 0; i < 20; i++)
{
Cell cl = ws.Cells[currentRow++, 0];
cl.Value = “Blue”;

}
for (int i = 0; i < 30; i++)
{
Cell cl = ws.Cells[currentRow++, 0];
cl.Value = “Red”;

}
for (int i = 0; i < 40; i++)
{
Cell cl = ws.Cells[currentRow++, 0];
cl.Value = “Green”;

}

Cell lastcell = ws.Cells[currentRow - 1, 0];

//Resize the Named range (pivot table data source)
NameCollection names = wb.Worksheets.Names;
Name name = names[“ColorsRange”];
name.RefersTo = “=Raw_Data!$A$1:” + lastcell.Name;

wb.CalculateFormula();
foreach (Worksheet wsheet in wb.Worksheets)
{
wsheet.RefreshPivotTables();
foreach (PivotTable pivot in wsheet.PivotTables)
{
pivot.CalculateData();
}
foreach (Aspose.Cells.Charts.Chart chart in wsheet.Charts)
{
chart.RefreshPivotData();
chart.Calculate();
}
}
Chart ch = wb.Worksheets[“Chart”].Charts[0];
ch.RefreshPivotData();

SaveFormat saveFormat = SaveFormat.Xlsx;
wb.Save(“o.xlsx”, saveFormat);

saveFormat = SaveFormat.Pdf;
wb.Save(“o.pdf”, saveFormat);

3

Thank you for your speedy and correct response! While correct, that solution only works for the demonstration because the sheet upon which the chart resides is known in advance.


Is there any way to get the Chart.RefreshPivotData() to work inside of a for-each loop? Otherwise, we cannot have dynamic input templates.

Rick
4

Hi,


Thanks for your posting and using Aspose.Cells.

Please split your outer foreach loop into two loops like this and it works fine.

foreach (Worksheet wsheet in wb.Worksheets)
{
wsheet.RefreshPivotTables();
foreach (PivotTable pivot in wsheet.PivotTables)
{
<span style=“color: rgb(255, 20, 147); font-family: “Courier New”; font-size: small;”> pivot.CalculateData();
}
}

foreach (Worksheet wsheet in wb.Worksheets)
{
foreach (Aspose.Cells.Charts.Chart chart in wsheet.Charts)
{
chart.RefreshPivotData();
chart.Calculate();
}
}


Here is the complete sample code for your reference.

C#
Workbook wb = new Workbook(“sampleIn1.xlsx”);

//simulate getting new data set
Worksheet ws = wb.Worksheets[“Raw_Data”];
ws.Cells.Clear();

int currentRow = 0;
Cell clHead = ws.Cells[currentRow++, 0];
clHead.Value = “Color”;

for (int i = 0; i < 20; i++)
{
Cell cl = ws.Cells[currentRow++, 0];
cl.Value = “Blue”;

}
for (int i = 0; i < 30; i++)
{
Cell cl = ws.Cells[currentRow++, 0];
cl.Value = “Red”;

}
for (int i = 0; i < 40; i++)
{
Cell cl = ws.Cells[currentRow++, 0];
cl.Value = “Green”;

}

Cell lastcell = ws.Cells[currentRow - 1, 0];

//Resize the Named range (pivot table data source)
NameCollection names = wb.Worksheets.Names;
Name name = names[“ColorsRange”];
name.RefersTo = “=Raw_Data!$A$1:” + lastcell.Name;

wb.CalculateFormula();
foreach (Worksheet wsheet in wb.Worksheets)
{
wsheet.RefreshPivotTables();
foreach (PivotTable pivot in wsheet.PivotTables)
{
<span style=“color: rgb(128, 0, 128); font-family: “Courier New”; font-size: small;”> pivot.CalculateData();
}
<span style=“color: rgb(128, 0, 128); font-family: “Courier New”; font-size: small;”>}

foreach (Worksheet wsheet in wb.Worksheets)
{
foreach (Aspose.Cells.Charts.Chart chart in wsheet.Charts)
{
chart.RefreshPivotData();
chart.Calculate();
}
}

SaveFormat saveFormat = SaveFormat.Xlsx;
wb.Save(“o.xlsx”, saveFormat);

saveFormat = SaveFormat.Pdf;
wb.Save(“output.pdf”, saveFormat);