I have an Excel workbook that I use as a template. This workbook contains a data source range, a pivot table off of the data source range, and a pivot chart off of the pivot table. I have attached a screenshot that shows the pivot table and pivot chart. I have also attached the Excel workbook.
I have an application that dynamically updates the data source data then calls Aspose to refresh the pivot table and then refresh the pivot chart. This process works fine for generating a new workbook from Aspose. However when I output a PDF instead the Pivot chart still contains old data. The source data and the pivot table both contain the correct data, just the pivot chart has the old data. Attached is a screenshot.
Notice that the pivot chart still has a reference to the series GL0001 when there is no such data in the source data or pivot table.
I’m not sure if this is related to the other recent issue I reported:
<!–[if gte mso 9]>
<w:WordDocument>
<w:View>Normal</w:View>
<w:Zoom>0</w:Zoom>
<w:TrackMoves/>
<w:TrackFormatting/>
<w:PunctuationKerning/>
<w:ValidateAgainstSchemas/>
<w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid>
<w:IgnoreMixedContent>false</w:IgnoreMixedContent>
<w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText>
<w:DoNotPromoteQF/>
<w:LidThemeOther>EN-US</w:LidThemeOther>
<w:LidThemeAsian>X-NONE</w:LidThemeAsian>
<w:LidThemeComplexScript>X-NONE</w:LidThemeComplexScript>
<w:Compatibility>
<w:BreakWrappedTables/>
<w:SnapToGridInCell/>
<w:WrapTextWithPunct/>
<w:UseAsianBreakRules/>
<w:DontGrowAutofit/>
<w:SplitPgBreakAndParaMark/>
<w:EnableOpenTypeKerning/>
<w:DontFlipMirrorIndents/>
<w:OverrideTableStyleHps/>
</w:Compatibility>
<w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel>
<m:mathPr>
<m:mathFont m:val=“Cambria Math”/>
<m:brkBin m:val=“before”/>
<m:brkBinSub m:val="–"/>
<m:smallFrac m:val=“off”/>
<m:dispDef/>
<m:lMargin m:val=“0”/>
<m:rMargin m:val=“0”/>
<m:defJc m:val=“centerGroup”/>
<m:wrapIndent m:val=“1440”/>
<m:intLim m:val=“subSup”/>
<m:naryLim m:val=“undOvr”/>
</m:mathPr></w:WordDocument>
<![endif]–><span style=“font-size:12.0pt;font-family:“Times New Roman”,“serif”;
mso-fareast-font-family:Calibri;mso-fareast-theme-font:minor-latin;mso-ansi-language:
EN-US;mso-fareast-language:EN-US;mso-bidi-language:AR-SA”><a href="
.
Here is the java program that reproduces this issue:
package bentest;
/* A class that implements LightCellsDataProvider interface
* Copyright © 2001-2012 Aspose Pty Ltd. All Rights Reserved.
<em>/
/</em> Main Program
* Copyright © 2001-2012 Aspose Pty Ltd. All Rights Reserved.
*/
import com.aspose.cells.Chart;
import com.aspose.cells.OoxmlSaveOptions;
import com.aspose.cells.PivotTable;
import com.aspose.cells.SaveFormat;
import com.aspose.cells.Workbook;
import com.aspose.cells.Worksheet;
import java.io.File;
import java.util.Iterator;
public class testpivotchart
{
private static final String OUTPUT_FILE_PATH = “output/”;
public static void main(String[] args) throws Exception
{
System.out.println(“Staring Pivot chart test”);
//Load up workbook with pivot chart
final Workbook wb = new Workbook(“c:\temp\aspose-pivotchart-with-dates.xlsx”);
updateSourceData(wb);
wb.getSettings().setCreateCalcChain(false);
wb.calculateFormula();
@SuppressWarnings(“unchecked”)
final Iterator wsIterator = wb.getWorksheets().iterator();
while ( wsIterator.hasNext() )
{
final Worksheet ws = wsIterator.next();
ws.autoFitRows(); //This is necessary to get the PDF version to display the full row height.
refreshPivotTables(ws);
}
OoxmlSaveOptions finalSaveOptions = new OoxmlSaveOptions(SaveFormat.PDF);
wb.save(OUTPUT_FILE_PATH + “/bentest.pdf”, finalSaveOptions);
System.out.println(“Ending Pivot chart test”);
}
private static void updateSourceData(final Workbook wb)
{
final Worksheet ws = wb.getWorksheets().get(“ExposureTrendPerTag”);
ws.getCells().get(1, 2).setValue(1);
ws.getCells().get(2, 2).setValue(1);
ws.getCells().get(3, 2).setValue(1);
ws.getCells().get(4, 2).setValue(1);
ws.getCells().get(1, 1).setValue(“GL_01”);
ws.getCells().get(2, 1).setValue(“GL_01”);
ws.getCells().get(3, 1).setValue(“GL_01”);
ws.getCells().get(4, 1).setValue(“GL_01”);
}
private static void refreshPivotTables(final Worksheet ws) {
@SuppressWarnings(“unchecked”)
final Iterator ptIterator = ws.getPivotTables().iterator();
while (ptIterator.hasNext())
{
final PivotTable pt = ptIterator.next();
pt.refreshData();
try {
pt.calculateData();
} catch (Exception e) {
//Ignoring the error on specific pivot table.
}
}
//Now update any charts associated with a pivot table.
@SuppressWarnings(“unchecked”)
final Iterator chartIterator = ws.getCharts().iterator();
while ( chartIterator.hasNext())
{
final Chart chart = chartIterator.next();
//chart.refreshPivotData();
try {
chart.calculate();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
<!–[if gte mso 10]>
/* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman","serif";}
<![endif]–>