Pivot Chart with Dates on X-Axis displayed as numbers on PDF output

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,


Thank you for contacting Aspose support.

We have evaluated your presented scenario while using the latest version of Aspose.Cells for Java 8.3.1.2. We have noticed the the Chart.refreshPivotData method is causing the conversion of dates to numbers in the chart’s axis. If you comment out this statement from your code you will get the correct result as attached. Moreover, please use the PdfSaveOptions class instead of OoxmlSaveOptions because the first one is the specialized class for PDF rendering.

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,


Thank you for elaborating your scenario further.

We can understand that refreshing the the pivot data is essential if you are dynamically updating the data source. We have logged the problem in our bug tracking system under the ticket CELLSJAVA-41156 for further investigation & correction purposes. Please spare us little time to properly analyze the scenario, and to provide the fix at earliest possible. In the meanwhile, we will keep you posted with updates in this regard.

We are glad that you are able to reproduce the problem at your end and thanks for submitting a ticket with Development team.


Could you please us know when can expect an update on this issue? This is very important for us. With out this fix, we can’t effectively use Aspose.Cells in our application.

Best regards,
Ravi

Hi Ravi,


I am afraid, the ticket logged earlier as CELLSJAVA-41156 is currently pending for analysis, and we cannot share an ETA for the fix unless we have properly analyze the problem cause. We have recorded a note to the ticket for the concerned development team member to schedule the ticket for thorough analysis. As soon as we receive any updates in this regard, we will post here for your kind reference.

Thank you for your patience with us.

Hi Ravi,


This is to inform you that we have received the ETA for the ticket CELLSJAVA-41156. We will try our best to provide the fix during the 3rd quarter of January 2015.

Babar,


Thanks for providing us ETA. Jan 3rd week is fits in our project schedule.

regards,
Ravi

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,


I would appreciate if you could provide us quick ETA. From our point of view, original issues hasn’t been fixed as upgrading to newer version is causing even bigger problems.

thanks a lot,
Ravi

Hi Ravi,


We haven’t yet received the estimated release schedule for the ticket logged earlier as CELLSJAVA-41167. However, we have raised it’s priority and requested the concerned development team member to share an insight of the problem, and most preferably an estimated release schedule for the fix. We will shortly get back to you with more updates in this regard.

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.