I have an Excel workbook with a Pivotchart that contains dates in the X-Axis (see attached screenshot). This displays correctly in Excel. When I use Aspose to render the workbook as PDF the Pivotchart contains numbers in the X-Axis instead of dates (see attached).
Please let me know how I can get the PDF to render with dates on the X-Axis.
Here is the code that I use:
package bentest;
/* A class that implements LightCellsDataProvider interface
* Copyright © 2001-2012 Aspose Pty Ltd. All Rights Reserved.
/
/ 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”);
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 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();
}
}
}
}
Hi Ravi,
That worked. Thanks.
Actually that does not work for my use case. The pivot table data is getting updated dynamically in the application. If I comment out the call to chart.refreshPivotData(); the pivot chart does not get updated with the new data.
Here is a program that demonstrates this issue:
package bentest;
/* A class that implements LightCellsDataProvider interface
* Copyright © 2001-2012 Aspose Pty Ltd. All Rights Reserved.
/
/ 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);
}
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();
}
}
}
}
Use the the initial Excel template I provided.
See the screenshot that shows the pivot table is updated with the new data but the pivot chart has the old data.
Thanks,
Ben
Hi Ben,
We are glad that you are able to reproduce the problem at your end and thanks for submitting a ticket with Development team.
Hi Ravi,
Hi Ravi,
Babar,
Hi,
Thanks for using Aspose.Cells for Java.
We have fixed this issue.
Please download and try this fix: Aspose.Cells for Java v8.3.1.5 and let us know your feedback.
Unfortunately this new library, 8.3.1.5 has broken some basic functionality in Aspose Cells. I have attached a more complicated Excel template that I’m using. When I use the following program with this template I get a broken file messages. See attached screenshots.
This program reproduces the issue.
/* 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.util.Iterator;
public class aspose599219
{
private static final String OUTPUT_FILE_PATH = “”;
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”);
final Workbook wb = new Workbook(“c:\temp\EXECUTIVE_SUMMARY.xlsx”);
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.xlsx”);
///wb.save(OUTPUT_FILE_PATH + “/bentest.pdf”, finalSaveOptions);
System.out.println(“Ending Pivot chart test”);
}
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();
}
}
}
}
This problem does not happen when I use Aspose cells 8.2.1.
Hi Ravi,
Thanks for your posting and using Aspose.Cells.
We were able to observe this issue with the latest version: Aspose.Cells
for Java v8.3.1.5 and it generates corrupt workbook.
We have logged this issue in our database for investigation. We will look into it and fix this issue. Once, the issue is resolved or we have some other update for you, we will let you know asap.
This issue has been logged as
- CELLSJAVA-41167 - Refreshing Pivot Tables generate corrupt workbook
I have attached the output Excel files for a reference.
The issues you have found earlier (filed as CELLSJAVA-41156) have been fixed in this update.
This message was posted using Notification2Forum from Downloads module by Aspose Notifier.
Unfortunately the new library 8.3.2 still has the problem CELLSJAVA-41167. I cannot use it.
Hi Ravi,
Thanks for your posting and using Aspose.Cells.
CELLSJAVA-41167 is not yet fixed. However, we have logged your comment in our database against this issue and requested the development team to fix this issue or provide ETA. Once, there is some news for you, we will let you know asap.
Hi Shakeel,
Hi Ravi,
Hi,
Thanks for using Aspose.Cells.
The issue CELLSJAVA-41167 should be fixed in about Friday, 6-Feb-2015.
Thanks for the update. Looking forward to it.
Hi,
Thanks for using Aspose.Cells for Java.
Please download and try this fix: Aspose.Cells for Java v8.3.2.1 and let us know your feedback.