Pivot Chart not updating with the Pivot Table

Aspose.Cells Product Family
1

Hi,


I am currently evaluating the capabilities of Aspose.Cells for .Net for the company I work for. We need to be able to convert an excel spreadsheet into a pdf file (The .xlsx file has been generated using aspose.cells from a template .xlsx file) Note: I am aware that an xlsx file is not a template file type.

An excel file is generated correctly with it’s chart, pivot table, and pivot chart. I am having a problem when I try to convert that excel file into a pdf file. The Chart does not get updated with the pivot table.

I have attached a screenshot of the generated excel and pdf files.

Is this a known problem? or am I doing something wrong with the conversion? I can’t seem to find a topic on this in the forums.

Sample code below:

//ExcelFile file contains a table, chart, pivot table, and a pivot chart
WorkBook wb = new Workbook(ExcelFile);
WorkSheet ws = wb.Worksheets[“worksheetName”];
Aspose.Cells.Tables.ListObject lo = ws.ListObjects[“tableName”];
foreach (Aspose.Cells.Pivot.PivotTable t in ws.PivotTables)
{
t.PreserveFormatting = true;
t.RefreshDataFlag = true;
t.RefreshData();
t.RefreshDataFlag = false;
}

SaveOptions options = new XlsSaveOptions(SaveFormat.Pdf);

wb.Save(sDialog.FileName.Replace(".xlsx", “.pdf”), SaveFormat.Pdf);

Thanks
2

Hi,


Could you please download and try our latest fix/version: Aspose.Cells for .NET v7.4.0.5 and let us know your feedback.

If you still find the issue, kindly attach your template Excel file and output PDF file. Also paste your runnable code or attach a sample console application here, we will check your issue soon.

Thank you.

3

Hi Amjad Sahi,

I have tried to use the fix/version you have linked, however I could no compile that fix/version.

Thank you

4

Hi,


Could you give us details about your issue, which error(s) you are getting compiling the project with latest fix. Also the fix v7.4.0.5 will work fine on any regular / normal .NET framework that is equal or greater than 2.0 (e.g 2.x, 3.x, 4.0, 4.5 etc.) in your project. Which project type you are using. Also paste your sample runnable code here so that we may help you to sort it out soon, if you was using some older version of the product, you have to update your existing code a bit accordingly for new/latest APIs of the product.

Thank you.
5

Hi,

The project we're using to do the evaluation is using .Net Framework 3.5 using Aspose.Cells. I have attached the evaluation project as it includes codes to simulate data passed into Aspose.Cells Objects.

NOTE: The code lines used to save the xlsx file and and convert it into a pdf file are between lines 226 - 265 in the "Form1.cs" class.

Also attached are the template xlsx file (Templateexcel3Rowsplu formula.xlsx), the output xlsx file (testFile3.xlsx), and the output pdf file (testFile3.pdf ).

6

Hi,


Thanks for the project and sample files.

Well, I did test your project with latest fix/version: v7.4.0.5 by running it and getting the output files giving the template file to open dialog box. The project does compiled fine and run OK. I have attached the output files. By the way, do you have some other issue, I mean, the data is not rendered to the lists / pivot tables / charts, etc. Please elaborate it more and provide us some screen shots highlighting the problematic areas encircling with read color etc. It will help us to analyze the issue more accurately. We will check it soon.

Thank you.
7

Hi,

I'm sorry, I wasn't clear with the problem we were having; I have attached a screenshot highlighting the problem we're getting when we try to convert an xlsx file (created using aspose) into a pdf file without having to open the xlsx file in excel first (The code for saving the xlsx file is in the same method as the code for saving the pdf file).

Additional question: Is there something we're missing/wrong on the way we are saving the xlsx file?

Thank you

8

Hi,


Thanks for the screen shot.

I can notice the issue as you have talked about.

I think your code seems to me fine with a few minor adjustments, e.g your line of code:
SaveOptions options = new XlsSaveOptions(SaveFormat.Pdf);
should be:
PdfSaveOptions options = new PdfSaveOptions(SaveFormat.Pdf);

Also, I did try to use PivotTable.ChangeDataSource method if it works and the pivot charts are updated accordingly, but it does not help much. e.g

if (result == System.Windows.Forms.DialogResult.OK)
{
SetPdfSettings(wb);
Aspose.Cells.Tables.ListObject lo = ws.ListObjects[“mainTable”];
foreach (Aspose.Cells.Pivot.PivotTable t in ws.PivotTables)
{
t.ChangeDataSource(new[] { “=Demo!$B$1:$F$4” });
t.RefreshData();
t.CalculateData();
t.ShowPivotStyleColumnHeader = true;
t.ShowPivotStyleRowHeader = true;
t.ShowRowHeaderCaption = true;
}
wb.Save(sDialog.FileName, SaveFormat.Xlsx);//“D:\Spearson\Desktop\New folder\Result.xlsx”, SaveFormat.Xlsx);

wb = new Workbook(sDialog.FileName);
ws = wb.Worksheets[“Demo”];

//wb = new Workbook(sDialog.FileName);
PdfSaveOptions options = new PdfSaveOptions(SaveFormat.Pdf);

Anyways, I have logged a ticket with an id “CELLSNET-41432” for your issue. Our concerned developer will look into it soon. Once we have any update on it, we will let you know here.

Thank you.
9
Amjad Sahi:
Also, I did try to use PivotTable.ChangeDataSource method if it works and the pivot charts are updated accordingly, but it does not help much.
I have unfortunately tried the changes you suggested, however I couldnt get the pivot chart to update accordingly, I'm assuming you meant the other chart updated accordingly (the normal chart). The top chart from the template is just a normal chart, but the bottom one is a pivot chart (circled red in my last screenshot).
Thank you for you help, we hope there will be an update to this in time.
10

Hi,


Yes, I mean it, PivotTable.ChangeDataSource works fine for other scenario but not yous here. Anyways, as we have already logged a ticket with an id “CELLSNET-41432” for your issue, once we have any update on it, we will let you know here.

Thank you.
11

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

Please download and try this fix: Aspose.Cells for .NET v7.4.1.1.

Please add this sample code before saving using the latest aspose version
7.4.1.1:

ws.Charts[1].RefreshPivotData();//add this code
wb.Save(sDialog.FileName, SaveFormat.Xlsx);//"D:\\Spearson\\Desktop\\New folder\\Result.xlsx", SaveFormat.Xlsx);



12

Hi,

I have just tried the fix, it's doing what it's meant to do, which is fantastic. My boss will be happy to know this.

However is another problem found. I will just open different thread for it.

Thank you,

Sandie

13

Hi,


Good to know that your issue is resolved now.

Sure, feel to post using separate threads in the forums if you got any other issue, we will sort it out soon.

Thank you.
14

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

This message was posted using Notification2Forum from Downloads module by aspose.notifier.