Excel Pivot RemoveField problems

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.

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

Hi,

Sorry found a new problem with deleting and adding dataareas, if i have one dataarea delete it and add it again then it is duplicated.

Best regards,

Willem-Jan

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

Hi Willem-Jan,

Thanks for sharing the project.

I have tested your scenario/ case and found the issue as you mentioned. The Data field is duplicated in the output PivotTable. I have reopened your issue “CELLSNET-42149” again. 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,

Please download and try our latest fix/version: Aspose.Cells for Java v7.7.0.4

Please change the sample
codes as follows:
e.g.

sheet.Cells.ClearRange(pivotTable.RowRange);

sheet.Cells.ClearRange(pivotTable.ColumnRange);

sheet.Cells.ClearRange(pivotTable.DataBodyRange);

if (pivotTable.DataField != null)

{

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

}

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

to:

pivotTable.ClearData();

pivotTable.DataFields.Clear();

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

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

Thanks that is really fast, the only problem is i don’t use java i need .net dll’s.

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

Hi,

Sure, please download and try our latest .NET fix/version: Aspose.Cells for .NET v7.7.0.4

Thank you.

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

Thanks! It works

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

Hi,

Thanks for your feedback.

Good to know that it figures out your issue now. We have closed your issue “CELLSNET-42149” now. Feel free to contact us any time if you need further help or have some other issue or queries, we will happy to assist you soon.

Thank you.

aspose.notifier · May 5, 2014, 7:12pm

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

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.

shakeel.faiz · May 5, 2014, 7:12pm

Hi,

Thanks for using Aspose.Cells.

Please download and try this fix: Aspose.Cells
for .NET v8.0.1.1 for your issue CELLSNET-42269 and let us know your feedback.

aspose.notifier · May 5, 2014, 7:12pm

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

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.