RefreshPivotData issue

Aspose.Cells Product Family
1

Hi Support team,

I am getting error while using the RefreshPivotData Method in few situation while trying to creating the chart through the pivottable. The error message is "Object Reference not set". Here i have attached two xlsx files

1. WokingFine_RefreshPivotData.xlsx(This has source data and the expected output)
2. Object_Reference_Not_Set_Source.xlsx (This is having only source)

Sample code:
obj.ExcelWorkBook.Worksheets["Pivot"].Charts.Add(ChartType.BarStacked, 2, 0, 25, 10);
obj.ExcelWorkBook.Worksheets["Pivot"].Charts[0].PivotSource = "Voyage Summary!PivotTable";
obj.ExcelWorkBook.Worksheets["Pivot"].Charts[0].RefreshPivotData();
obj.ExcelWorkBook.Worksheets["Pivot"].Charts[0].NSeries[0].Area.ForegroundColor = Color.White;

if we use Object_Reference_Not_Set_Source.xlsx values as source, we are getting issue like "Object Reference not set".

Please help me how to solve this issue?

Thanks
Saravanan

2

Hi,

I tested your file. I found, there is no worksheet namely “Pivot” in your Object_Reference_Not_Set_Source.xlsx file. That’s why you are getting exception.

Please change your code like this.


ExcelWorkBook.Worksheets[0].Charts.Add(ChartType.BarStacked, 2, 0, 25, 10);

ExcelWorkBook.Worksheets[0].Charts[0].PivotSource = “Voyage Summary!PivotTable”;

ExcelWorkBook.Worksheets[0].Charts[0].RefreshPivotData();

ExcelWorkBook.Worksheets[0].Charts[0].NSeries[0].Area.ForegroundColor = Color.White;

3

Hi Support team,

This is the sample sheet and the sample code. Thats why i told in this previous forum like this " 2. Object_Reference_Not_Set_Source.xlsx (This is having only source)". I remove the pivot sheet manually because you need the sample source.

I have used the same code to create the data sheet and Pivot chart for both excel file but requesting the volume of the data is different. if my code is wrong means how i get the WokingFine_RefreshPivotData.xlsx file?


Please refer the original code for chart creation only from pivot table:

obj.ExcelWorkBook.Worksheets["Voyage Summary"].Charts.Add(ChartType.BarStacked, 2, 0, 25, 10);
obj.ExcelWorkBook.Worksheets["Voyage Summary"].Charts[0].PivotSource = "Voyage Summary!PivotTable";
obj.ExcelWorkBook.Worksheets["Voyage Summary"].Charts[0].RefreshPivotData();
obj.ExcelWorkBook.Worksheets["Voyage Summary"].Charts[0].NSeries[0].Area.ForegroundColor = Color.White;
obj.ExcelWorkBook.Worksheets["Voyage Summary"].Charts[0].NSeries[0].Area.Transparency = 1;
obj.ExcelWorkBook.Worksheets["Voyage Summary"].Charts[0].ChartArea.Area.ForegroundColor = Color.White;

The above code is working for the WokingFine_RefreshPivotData.xlsx which is contains the less data. But Object_Reference_Not_Set_Source.xlsx is contains the huge data.

Please help me to solve this issue and let me know if you need any additional information regarding this.

Thanks
Saravanan

4

Hi,

Please provide me a sample console application replicating the problem along with your complete source xls/xlsx files. You can remove part of huge data to keep it small but don’t remove any worksheet.

Please also test your issue with the latest version:
Aspose.Cells for .NET 7.0.3
and find it if it resolves your problem.

5

Hi Aspose Support team,

Thank you for the reply. I have used the version. 7.0.3. Still i am not getting the issue while using the RefreshPivotData method. i have attached the sample Project(solution) and the RefreshPivotData.xlsx(For source, Please put this file in C drive). I have added the another one simple source (RefreshPivotData_Simple.xlsx), for this source RefreshPivotData method is working fine (Please replace the excel file and test).

In the sample solution, i have 2 excel sheets. 1.Data. 2.VSummary. I have used the RefreshPivotData method in the second sheet.

Please let me know if you need additional information regarding this.

Thank you
Saravanan

6

Hi,

Thanks for your source files and code.

Please remove all the irrelevant code that is not related to your pivot table issue and simplify the code.

Also, remove all the database related code, if you need sample data then use hard-coded values instead.

7

Hi Support team,

Thanks for your reply. RefreshPivotData is working fine with the RefreshPivotData_Simple.xlsx source.

I am creating the Chart based on the pivot table. Pivot table creation based on the Data sheet. Thats why i sent the entire code in the test application and also I am reading the records from the Excel file only.

I am not getting the issue in the RefreshPivotData method while i used the RefreshPivotData_Simple.xlsx as a source of the file, because this source is contained the less data (I guess). But i am getting the issue with RefreshPivotData.xlsx source which is contained the huge record(around 10,000 rows). So i cannot able to hardcode this much of rows.

What kind of difficulty you are facing during the testing, because reading the record from the excel.

Let me know if you need any additional information regarding this.

Thanks
Saravanan

8

Hi,

It would be better if you provide us a simpler code with sample hardcoded values (you don’t need thousands of lines) and runnable code replicating the problem using the latest version.

Anyway, I have logged your issue in the database. We will look into it and update you. If we need any further information, we will let you know.

This issue has been logged as CELLSNET-40111.

9

Hi,


Please try our latest version/fix: v7.0.3.2, your issue should be fixed by the fix.

Thank you

10

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

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