Pivot Chart contains old column data after dynamic data update and refresh

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]–>

Hi Ravi,


Thank you for contacting Aspose support.

The presented problem is probably caused due to the reason that Chart.refreshPivotData method is not behaving correctly with recent revision of Aspose.Cells for Java 8.3.1.2. We have already logged this problem as CELLSJAVA-41156 and currently waiting for the fix to be available for re-evaluation of the presented scenario.

We will shortly get back to you with estimated releases schedule of the upcoming release of Aspose.Cells for Java.

Hi again,


This is to inform you that we have logged a separate ticket CELLSJAVA-41165 to thoroughly investigate this scenario on our end. Please spare us little time for proper analysis. In the meanwhile we will keep you posted with updates in this regard.

The issues you have found earlier (filed as CELLSJAVA-41165) have been fixed in this update.


This message was posted using Notification2Forum from Downloads module by Aspose Notifier.