Chart refreshed by datadump differs from chart as visable directly n Excel

Aspose.Cells Product Family
1

I created an Excel file with 3 tabs, i.e.: Chart, Data en Pivottable

On the 1st tab (Chart) I created a chart combining two dataseries (from the Pivottable) and two Y-axis.

In Excel all is looking ok (see attachment) but when I create the graph through exporting my data to the file on tab Data and refresh Pivottable and Chart, my Chart is totally different:

  • no second dataset shown as a line

  • no second y-axis

  • labels on the x-axis is incorrect

By code I do this:

worksheet.Cells.ImportDataTable(dataTable, true, 0, 0, false);
pivotTable.RefreshData();
pivotTable.CalculateData();
workbook.CalculateFormula();
chart.RefreshPivotData();
chart.ToImage("filename", ImageFormat.Png);

Can anyone please explain to me why the chart ain’t simply a copy of the one I created in Excel?

2

Hi Edwin,


Thank you for contacting Aspose support.

I am afraid, it is not possible for us to properly investigate the presented scenario on our side unless you share your complete code (most preferably a sample application) along with the input & output spreadsheets. Before you move forward to provide the above requested, we suggest you to give a try to the latest version of Aspose.Cells for .NET (Latest Version) on your end. Moreover, please change the sequence of the refresh statements as follow. In case the problem persists, please share a standalone sample application to replicate the problem. Please note, you have to create the DataTable dynamically in your application so we do not need the database connectivity while investigating the matter.

C#

workbook.CalculateFormula();
pivotTable.CalculateData();
pivotTable.RefreshData();
chart.RefreshPivotData();

3

Thanks for your response. We’re using version 8.5.0 and the sequence of the refresh statement was like proposition.

We have a Visual Studio 2013 demo project to demonstrate the issues we experience. To which e-mail address can I send this demo application (6 Mb and it includes our license file).

If you look in the data directory you will find:
· a. The original Template.xslx

· b. The Result.xslx that’s saved from within the application

· c. A png file that’s saved from the application

The problems we experience:

    1. The original template has a bar and a line the result looks totally different (two bars)
    1. The original pivot table takes the top10 this means the bars are sorted from big to small, this is lost in the result
    1. We use the change palette method to influence the chart colors but this doesn’t seems to work
    1. If you export the chart to a picture the horizontal axis shows strange labels and the right axis is lost

Thanks for your response in advance.

4

Hi Edwin,


Thank you for the details.

Please remove the license file from your demo application and attach it here after creating an archive of it. You may also share the demo application via an email by using the Contact button on the post window, and select to send me an email. If you opt to use the Contact button to send the demo application, please notify us here as well because sometimes the emails land in the junk folder.
5

demo application is sent by e-mail

6

Hi Edwin,

Thank you. I have received your demo application, and currently evaluating the presented scenarios. I will shortly get back to you with updates in this regard.

7

PerfectView:

The problems we experience:

    1. The original template has a bar and a line the result looks totally different (two bars)
    1. The original pivot table takes the top10 this means the bars are sorted from big to small, this is lost in the result
    1. We use the change palette method to influence the chart colors but this doesn’t seems to work
    1. If you export the chart to a picture the horizontal axis shows strange labels and the right axis is lost

Hi,

We have checked the presented scenario while using the latest version of Aspose.Cells for .NET 8.5.0.1, and we are able to replicate the above mentioned problems. Please find the ticket details as follow.

  1. CELLSNET-43750: Chart changes in resultant spreadsheet after refreshing the Chart with RefreshPivotData.
  2. CELLSNET-43751: Row Label sorting is lost in resultant spreadsheet after refreshing the PivotTable.
  3. We need to look further into this matter to establish the presented behavior as a bug on the part of Aspose.Cells APIs. We will keep you posted with updates in this regard.
  4. CELLSNET-43752: Rendering the Chart to image results in different Horizontal axis values and completely lost vertical axis values.

Please allow us some time to properly analyze the aforesaid tickets and get back to you with updates.

8

Hi Babar,


thanks for your quick response. Can you tell me when the identified tickets/problems will be solved? Our strategy on using Apose.Cells for the customers in our CRM cloud is depending on it. We plan a release at the end of July 2015 containing this functionality for Sales dashboards.
9

Hi Edwin,

I am afraid, I cannot say when we can provide the fix unless we have thoroughly analyzed the presented scenarios. We need some time for analysis and some more to provide the fix. Please be patient and let us analyze the tickets attached to this thread. As soon as we receive any news/updates from the product team, we will post here for your kind reference.

Thank you for your understanding.

10

Hi,

can anyone inform me about the status of the following tickets:

1.1. The original template has a bar
and a line the result looks totally different (two bars)
CELLSNET-43750 : Chart changes in resultant spreadsheet after refreshing the Chart with RefreshPivotData.–

2.2. The original pivot table takes the top10 this means the bars are sorted from big to small, this is lost in the result
CELLSNET-43751 : Row Label sorting is lost in resultant spreadsheet after refreshing the PivotTable.

3.3. We use the change palette method to influence the chart colors but this doesn’t seems to work
We need to look further into this matter to establish the presented behavior as a bug on the part of Aspose.Cells APIs. We will keep you posted with updates in this regard.

4.4. If you export the chart to a picture the horizontal axis shows strange labels and the right axis is lost
CELLSNET-43752 : Rendering the Chart to image results in different Horizontal axis values and completely lost vertical axis values.

We are ready to introduce a new version of our CRM application in the coming weeks and we need these functionalities to complete our dashboards.

11

Hi Edwin,


Thank you for writing back.

Please note, we have applied the minor fix for the ticket CELLSNET-43752 whereas the complete resolution will be available with the fix of CELLSNET-43751 because both issues are related. We are working on these tickets along with the CELLSNET-43750 and hope to provide more updates very soon.

Regarding the inquiry related to Workbook.ChangePalette method, please note that the said feature applicable for XLS file format only because XLS file format has the limit of 56 pallets per workbook therefore the aforesaid method allows to change the preset entry with any custom color of choice, however, the method will not have any effect if the spreadsheet is of type XLSX. Please read more about Colors and Palette.

12

Hi Edwin,

This is to update you regarding the tickets logged earlier as CELLSNET-43751. Further analysis of the problem revealed that it is caused due to the fact that Aspose.Cells APIs currently do not support the sorting of the PivotField by DataField. We are analyzing the scenario further to provide the required support with future releases of the Aspose.Cells APIs. Adding more, the problem logged earlier as CELLSNET-43750 has been resolved. We will shortly provide the fix with next maintenance release of Aspose.Cells for .NET after incorporating other enhancements and performing quality assurance.

As soon as we get more updates regarding the issues mentioned in this thread, we will post here for your kind reference. We are sorry for the inconvenience caused to you.

13

Hi,

Thanks for your using Aspose.Cells.

We have fixed the issue logged as CELLSNET-43750.

Please download and try the latest fix: Aspose.Cells for .NET (Latest Version) and let us know your feedback.

14

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

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

@PerfectView,

This is to inform you that we have fixed your issue (logged earlier as “CELLSNET-43751”) now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.

16

The issues you have found earlier (filed as CELLSNET-43751) have been fixed in this update. This message was posted using Bugs notification tool by simon.zhao