I've had a great deal of trouble getting pivot tables and charts working properly.  I'm opening an Excel 97-style xls file and creating a pivot table and chart from the existing data like so:

Worksheet ws109 = book.Worksheets["109"];
int ptindex = ws109.PivotTables.Add(getRangeName(ws109), 2,  getDataArea(ws109).EndColumn + 2, "Pivot");
PivotTable pt = ws109.PivotTables[ptindex];

pt.DataFields.AddByBaseIndex(3);
int rowWeeks = pt.RowFields.AddByBaseIndex(6);
pt.RowFields[rowWeeks].IsAutoSort = true;

int chartIndex = ws109.Charts.Add(ChartType.ColumnStacked,
                pt.TableRange1.EndRow + 2, pt.TableRange1.StartColumn,
                pt.TableRange1.EndRow + 20, pt.TableRange1.StartColumn + 6);

Chart chart = ws109.Charts[chartIndex];

chart.PivotSource = getRangeName(ws109, pt.TableRange1);



---The getRangeName() method just finds the text name of the C1:C2 format of the area, such as "=109!A1:J20", which I've verified to work properly.
---The getDataArea() method just creates and returns a CellArea of the bounding area of the data, which I've also verified.


When doing this, I end up with a correct pivot table and a blank chart every time.  Now here's where it gets odd...if I rename the file as an ".xlsx" file and save it, excel complains about the pivot table when I open it, but the chart actual renders with the right data.  Are there any hot fixes for this version of Aspose Cells that fixes this?

Hi,

 

Thank you for considering Aspose.

 

Please try the attached latest version of Aspose.Cells. If you still face any problem, please share the template file and complete code to reproduce the issue. We will check it soon.

 

Thank You & Best Regards,

---

Nausherwan Aslam
Support Developer,
Aspose Sialkot Team
Contact Us

4.8.2 works exactly the same as 4.7.1.  I finally discovered the fix, however.  When setting the PivotSource field for the chart, it must be in the format of: "=[file.xls]SheetName!PivotTableName". 

The PivotSource field works differently for xlsx files.  Please correct this in future versions or at least spell it out clearly in your documentation.  With an xlsx file, I was able to simply set PivotSource to "=SheetName!A1:E20".  This may be a limitation of Excel, but I think it would help your users greatly if you explain this in your documentation and especially in the intellisense.

On a side note, you mentioned that I should try 4.8.2.  Is my license for 4.7.1 the same as 4.8.2?  It didn't pop up any warnings about me using the same license key, so can I assume that I can switch to the newer version with my production code?

Thanks!

mike

Hi Mike,

Well, if you are not getting the evaluation page, then you may update your application with new dll. Also, you may check the license subscription expiry date by simply opening your license file in Notepad and check <SubscriptionExpiry> tag.

Thank You & Best Regards,

 

---

Nausherwan Aslam
Support Developer,
Aspose Sialkot Team
Contact Us