Pivot Table Field Position Exception

Aspose.Cells Product Family
1

Hi



I am evaluating Aspose.Cells and have come across an issue with adding Pivot Table Fields.



I am using the following code;



//Create a Work Sheet

Worksheet myWorkSheet = ActiveWorkBook.Worksheets.Add(“Pivot Table " + (ObjectIndex + 1));



//Create Pivot Table

PivotTable pivotTable = myWorkSheet.PivotTables[myWorkSheet.PivotTables.Add(”=‘Raw Data " + (this.DataSourceIndex + 1) + "’!" + myWorkSheet.Cells[0,0].Name + “:” + myWorkSheet.Cells[DataSource.Rows.Count,DataSource.Columns.Count - 1].Name , “A1”, “PivotTable” + (ObjectIndex + 1))];



//Unshowing grand totals for rows.

if (this.ShowGrandTotal)

{

pivotTable.RowGrand = true;

}

else

{

pivotTable.RowGrand = false;

}



//Add Fields

foreach (CascadeExcelReportingPivotTableFields field in this.Fields)

{

int holdIndex = pivotTable.AddFieldToArea(field.FieldType, DataSource.Columns.IndexOf(field.ColumnName));

PivotField myField = pivotTable.BaseFields[holdIndex];

myField.DisplayName = field.ColumnAlias;

}





The first two items (Postions 0 and 1) work fine, but after Position 1 (in this case 2 to 4), the PivotTable.BaseFields.PivotField object is throwing the following exception in the Position property;



‘((Aspose.Cells.PivotField)((new System.Collections.ArrayList.ArrayListDebugView(pivotTable.BaseFields.ᔐ)).Items[4])).Position’ threw an exception of type ‘System.NullReferenceException’



Am I using the wrong collection to find the PivotFields?



I have attached my spread sheet. The pivot table is getting it’s data from the ‘Raw Data 1’ worksheet.



Thanks



Jared

2

Hi,

Well, I 'm afraid, currently, we don't support to create pivot tables in .xlsx files(MS Excel 2007), we only keep them preserved in the template file when you open and save / save as the .xlsx file containing the pivot table(s). I think you may try to use .xls template file (MS Excel 97-2003) with its data source and create the pivot table report in it.

Thank you.

3

Hi



Thanks for you reply, I am not sure if I explained my problem properly.



The method AddFieldToArea() is not working properly. I am passing in the correct index for the Position and that Position exists in the BaseFields collection (it has the correct Name property, so it has definitely found it).



The problem occurs when I use the method and it tries to get the Postion, as the Position property returns the exception described previously. The AddFieldToArea() seems to handle this exception by returning an index of 0, which means it is adding the wrong field.





But, all this might be in vain if I cannot put a Pivot table into an xlsx file. When will this be supported?



Thanks



Jared

4

I have attached an example of one of the PivotField objects that is causing the problem. As you can see, your assembly has found the range and the column names, but for some reason cannot get the Position index.



Cheers



Jared

5

Hi,

We will get back to you soon.

Thanks for being patient!

6

Hi Jared,
If you add the pivot field to area , we will return the index of field in the area not the index in the base field.So if you want to change the display name the filed, please do as the following :

for (int i = 0; i < 5; i++)
{
pivotTable.BaseFields[i].DisplayName = "display " + i;
}

And we will fix the bug of BaseFields[index].Position.

7

Thanks, I will give it a try and let you know how I get on.



Cheers



Jared

8

Hi Jared,

Please try this fix in <A href="https://forum.aspose.com/t/86456</A></P> <P>We have fixed the bug of getting position of the base field in the BaseFields.</P>

9

Thanks that appears to be working.



Please could you let me know when will Pivot table functionality be available for Office 2007? This is critical to our being able to make use of your component.



Thanks



Jared

10

@jaredfholgate,

We supported the feature (create, manipulate and refresh Pivot Tables in XLS/XLSX (MS Excel 2007 - 2019)) with enhancements in newer versions. We recommend you to kindly upgrade to and give it a try to latest version of Aspose.Cells. Also, see the document for your reference:
Create Pivot Tables and Pivot Charts