Excel Pivot RemoveField problems

w.overink · May 5, 2014, 7:00pm

Hi,

i have some problems with removing data-fields from a pivot. In a pivot there are two data-fields, i try to remove one but both are removed, if i try to remove both then i get a “Null Reference Error” (because the first remove already removes all data-fields). The code that i use for removing a field is: pivotTable.RemoveField(PivotFieldType.Data, “Menge”);

Attached is a sample project, you only have to adjust the path’s. I hope someone can help me.

Thanks in advance,

Willem-Jan Overink

babar.raza · May 5, 2014, 7:12pm

Hi Willem,

Thank you for reporting this problem.

I have evaluated the presented issue with your provided sample project and spreadsheet while using the latest version of Aspose.Cells for .NET 7.6.0.7. I have noticed that removing one field removes the other as well, that is an incorrect behavior, and therefore needs to be investigated. A ticket (CELLSNET-42149) has been logged in our bug tracking system to look further into this matter. Please spare us little time to properly analyze the problem cause, and to provide a fix for this situation. In meanwhile, we will keep you posted with updates in this regard.

Please accept our apologies for your inconvenience.

amjad.sahi · May 5, 2014, 7:12pm

Hi,

Please change the line of code:

pivotTable.RemoveField(PivotFieldType.Data, “Menge”);

to:

pivotTable.RemoveField(PivotFieldType.Data, pivotTable.DataFields[0]);

it works fine for your needs.

Thank you.

w.overink · May 5, 2014, 7:12pm

Thanks for the quick reply.

Yes that works, but it isn’t possible to remove both(all) datafields -> “Null reference”.

Sincerely,

Willem-Jan

amjad.sahi · May 5, 2014, 7:12pm

Hi,

Please use the line instead, it will remove all the Data fields in the pivot table:

pivotTable.RemoveField(PivotFieldType.Data, pivotTable.DataField);

Thank you.

w.overink · May 5, 2014, 7:12pm

Thanks i can use that, but now i have another strange behavior:
i have one datafield, then i remove all datafields, then i add the same datafield again somehow the datafield is now twice added. This only happens with one datafield, if there are more datafields and i remove them and add them again it works fine.

amjad.sahi · May 5, 2014, 7:12pm

Hi,

Thanks for providing us new sample project with template file(s).

I have tested your scenario/ case a bit and found the issue as you mentioned. If we remove all the datafield (since there is one data field added to the table), somehow the datafield is now added twice if we add it again to the table later on in the code. We have logged it again your issue into our database and will look into it soon.

Also since your original issue “CELLSNET-42149” is still logged as a bug in our database (and is active), so we may provide a fix soon which will cope with all types of issues when adding/removing data pivot fields in the PivotTable.

Once we have any update on it, we will let you know here.

Thank you.

w.overink · May 5, 2014, 7:12pm

Thanks,

sorry but found another problem with deleting all datafields and add them again (maybe it is the same bug as before), the Pivot looks broken in excel and i get a question in excel : “There’s already data in […]Sheet1. Do you want to replace it?”.

Sincerely,

Willem-Jan

amjad.sahi · May 5, 2014, 7:12pm

Hi,

Thanks for the new sample project.

I have tested your scenario/ case using your sample project and found the issue. Yes, I think this issue is linked with the original issue where we cannot remove pivot data fields fine in the pivot table, so you find this behavior consequently. I have logged your new project and concerns against your issue “CELLSNET-42149” into our database. We will look into it to figure it out soon.

Thank you.

w.overink · May 5, 2014, 7:12pm

Hi,

can you give me an update about the issue?

Thanks in advance,

Willem-Jan

amjad.sahi · May 5, 2014, 7:12pm

Hi Willem-Jan,

I have checked the status of your issue “CELLSNET-42149” into our database, I am afraid, it is not resolved yet. However, I have asked the concerned developer to update on your issue or share an eta for it.

Once we have any update on it, we will let you know here.

Thank you.

amjad.sahi · May 5, 2014, 7:12pm

Hi,

Please download and try our latest version/fix: Aspose.Cells for .NET v7.7.0.2

We have fixed this issue.

· There are some
points for attention though:

PageFields attribute of PivotTable specifies a collection of PivotField in page or filter label area. RowFields attribute of PivotTable specifies a collection of PivotField in row label area. ColumnFields attribute of PivotTable specifies a collection of PivotField in column label area. DataFields attribute of PivotTable specifies a collection of PivotField in data label area. DataField attribute of PivotTable exists only if DataFields contains two or more PivotField.
DataField is a virtual PivotField, which can be placed in Column label area or Row label area.
If you want to remove a PivotField, you have the following three ways:

First, using the method RemoveField(PivotFieldType fieldType, string fieldName).

Second, using the method RemoveField(PivotFieldType fieldType, int baseFieldIndex).

Third, using the method RemoveField(PivotFieldType fieldType, PivotField pivotField).

But if you use the method RemoveField(PivotFieldType fieldType, PivotField pivotField), there are some points to be noted. If the second parameter pivotField is DataField attribute of PivotTable, you will remove all the PivotField in DataFields. If you do it like this in MS Excel, you will get the same result. So the code in sample project should be as follows:

if (pivotTable.DataField != null)

{

pivotTable.RemoveField(PivotFieldType.Data, pivotTable.DataField);

}

pivotTable.AddFieldToArea(PivotFieldType.Data, "Betrag Netto FW");

As a result of the count of DataFields is one, so DataField attribute of PivotTable is null, if block will not be executed. So, when you add a PivotField existed in DataFields to Data label area, it is added twice.

For the problem, “There’s already data in […]Sheet1. Do you want to replace it?”

Please change the codes accordingly:
e.g.

pivotTable.AddFieldToArea(PivotFieldType.Data, "Betrag Netto FW");

pivotTable.AddFieldToArea(PivotFieldType.Data, "Menge");

to:

pivotTable.AddFieldToArea(PivotFieldType.Data, "Betrag Netto FW");

pivotTable.AddFieldToArea(PivotFieldType.Data, "Menge");

pivotTable.RefreshDataFlag = true;

as a result of the default position of DataField is in row label area, so if you want to get the same result like source file, you should use the codes as follows:

pivotTable.AddFieldToArea(PivotFieldType.Data, "Betrag Netto FW");

pivotTable.AddFieldToArea(PivotFieldType.Data, "Menge");

pivotTable.AddFieldToArea(PivotFieldType.Column, pivotTable.DataField);// add DataField to column label area

pivotTable.RefreshDataFlag = true;

Hope, this helps a bit.

Thank you.

w.overink · May 5, 2014, 7:12pm

Hi,

Thanks, it works better but i have a problem when there is one datafield left, maybe i don’t understand it but as you can see in the screenshot the pivot-table looks broken or there are two pivot-tables somehow.

Also attached is the example project.

Hope you can help me.

Thanks in advance,

Willem-Jan

amjad.sahi · May 5, 2014, 7:12pm

Hi Willem-Jan,

Thanks for providing us sample project with template file.

I have tested your scenario/ case using your attached project with your template file. I observed the issue as you pointed out. There is still a DataField left in the table as you have mentioned. I have reopened your issue “CELLSNET-42149”. We will look into your issue soon.

Once we have any update on it, we will let you know here.

Thank you.

amjad.sahi · May 5, 2014, 7:12pm

Hi,

We have analyzed your issue further. Well, as the range of PivotTable is changed after adding or deleting the PivotField(s), so the original data would exist in the new range of PivotTable.

So, before adding or deleting the PivotField, you need clear the original content of PivotTable, see the sample lines of code below:

e.g

Sample code:

sheet.Cells.ClearRange(pivotTable.RowRange);
sheet.Cells.ClearRange(pivotTable.ColumnRange);
sheet.Cells.ClearRange(pivotTable.DataBodyRange);

And, of course, we can provide a method named: ClearPivotTableData to clear it accordingly.

But, we thought the above approach may help you to resolve the issue for the time being.

Thank you.

w.overink · May 5, 2014, 7:12pm

Hi,

Thanks for the reply the “sheet.Cells.ClearRange” works fine in excel, but if i export (SheetRender) it to xps the pivot table is empty.

Attached is a sample project.

Best regards,

Willem-Jan

amjad.sahi · May 5, 2014, 7:12pm

Hi Willem-Jan,

Thanks for sharing us the project.

I have tested your scenario a bit and found the issue. I have observed the issue with rendering XPS file which does not render data in PivotTable. As we already reopened your issue and working over your issue, so we will look into it as well. I have logged your comments against your issue “CELLSNET-42149” into our database.

Once we have any update on it, we will let you know here.

Thank you.

amjad.sahi · May 5, 2014, 7:12pm

Hi,

We have further evaluated your issue “CELLSNET-42149”. Well, it is not an issue with the product. The XPS file does not contain data because you don’t refresh data of PivotTable after you add or delete PivotFiled(s). Please change the codes as follows:

pivotTable.RefreshDataFlag = true;

to :

pivotTable.RefreshDataFlag = true;
pivotTable.RefreshData();
pivotTable.CalculateData();

I have tested with it and it works fine, the output XPS file does contain data fine.

Thank you.

w.overink · May 5, 2014, 7:12pm

Hi,

Thanks that works! But the formatting in xps is not 100% correct as you can see in the xps file.

Best regards,

Willem-Jan

amjad.sahi · May 5, 2014, 7:12pm

Hi Willem-Jan,

Thanks for sharing the sample XPS file.

I have tested your scenario/ case and observed the issue as you mentioned. The formatting of PivotTable is not fine in the output XPS file as per the Excel file. I have logged a separate ticket with an id “CELLSNET-42269” for your issue. We will look into it soon.

Once we have any update on it, we will let you know here.

Thank you.