Aspose.Cells.Cell Exception: This pivot field has more unique items than can be used in a pivot table

Aspose.Cells Product Family
1

Hello,

I am getting the below error when creating pivot table. Please help.

Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> Aspose.Cells.CellsException: This pivot field has more unique items than can be used in a pivot table.

This is my code:

//Getting the pivottables collection in the sheet

Aspose.Cells.Pivot.

PivotTableCollection pivotTables = sheet2.PivotTables;

//Adding a PivotTable to the worksheet

int index = pivotTables.Add(targetRange.Name, "A8", "PivotTable1");

//Accessing the instance of the newly added PivotTable

Aspose.Cells.Pivot.

PivotTable pivotTable = pivotTables[index];

//Showing the grand totals

pivotTable.RowGrand =

true;

pivotTable.ColumnGrand =

true;

//Setting the PivotTable report is automatically formatted

pivotTable.IsAutoFormat =

true;

//Setting the PivotTable autoformat type.

pivotTable.AutoFormatType = Aspose.Cells.Pivot.

PivotTableAutoFormatType.Report6;

//Draging the first field to the row area.

pivotTable.AddFieldToArea(Aspose.Cells.Pivot.

PivotFieldType.Row, 7);

pivotTable.AddFieldToArea(Aspose.Cells.Pivot.

PivotFieldType.Row, 4);

pivotTable.AddFieldToArea(Aspose.Cells.Pivot.

PivotFieldType.Row, 3);

pivotTable.AddFieldToArea(Aspose.Cells.Pivot.

PivotFieldType.Row, 5);

//Draging the fourth field to the column area.

pivotTable.AddFieldToArea(Aspose.Cells.Pivot.

PivotFieldType.Column, 13);

pivotTable.AddFieldToArea(Aspose.Cells.Pivot.

PivotFieldType.Column, 14);

pivotTable.AddFieldToArea(Aspose.Cells.Pivot.

PivotFieldType.Column, 15);

//Getting the pivottables collection in the sheet

Aspose.Cells.Pivot.

PivotTableCollection pivotTables = sheet2.PivotTables;

//Adding a PivotTable to the worksheet

int index = pivotTables.Add(targetRange.Name, "A8", "PivotTable1");

//Accessing the instance of the newly added PivotTable

Aspose.Cells.Pivot.

PivotTable pivotTable = pivotTables[index];

//Showing the grand totals

pivotTable.RowGrand =

true;

pivotTable.ColumnGrand =

true;

//Setting the PivotTable report is automatically formatted

pivotTable.IsAutoFormat =

true;

//Setting the PivotTable autoformat type.

pivotTable.AutoFormatType = Aspose.Cells.Pivot.

PivotTableAutoFormatType.Report6;

//Draging the first field to the row area.

pivotTable.AddFieldToArea(Aspose.Cells.Pivot.

PivotFieldType.Row, 7);

pivotTable.AddFieldToArea(Aspose.Cells.Pivot.

PivotFieldType.Row, 4);

pivotTable.AddFieldToArea(Aspose.Cells.Pivot.

PivotFieldType.Row, 3);

pivotTable.AddFieldToArea(Aspose.Cells.Pivot.

PivotFieldType.Row, 5);

//Draging the fourth field to the column area.

pivotTable.AddFieldToArea(Aspose.Cells.Pivot.

PivotFieldType.Column, 13);

pivotTable.AddFieldToArea(Aspose.Cells.Pivot.

PivotFieldType.Column, 14);

pivotTable.AddFieldToArea(Aspose.Cells.Pivot.

PivotFieldType.Column, 15);

Thanks,

Jyotshna


2

Hi Jyotshna,

Thank you for contacting Aspose support.

We have tried your provided code snippet on one of our own samples while using the latest build of Aspose.Cells for .NET 7.7.2.3. We believe the problem cause could be the sample it self or the issue could have already been rectified with the latest version (in case you are using an older version of the API). Therefore, we would request you to please give a try to the latest assemblies (link shared above) on your end to see if it makes any difference. In case the problem persists, please provide us the sample spreadsheet for our review.

3

Thank You for the reply.

I have downloaded the files you provided in the link but that did not solve the issue. I am still getting same error.

Even earlier I was using latest version itself and that did not work too. Please help ASAP since I have deployment to do this week and this is the only pending task, all others is good.

4

Hi Jyotshna,

Sorry to know that the latest build didn’t help. As discussed earlier, we need your sample spreadsheet to replicate the exception on our end before we can log a ticket for further investigation. It is requested again to please provide the problematic sample file for our review.

Thank you for your cooperation and understanding.

5

Please check the attached file.

6

Hi Jyotshna,

Thank you for providing the sample spreadsheet. We have re-evaluated your presented scenario on our end while using the code snippet provided in your first post against latest build of Aspose.Cells for .NET 7.7.2.3. Unfortunately, the said exception didn’t replicate nor we were able to get a perfect Pivot Table as a result (please see the resultant file). Moreover, when we loaded your sample spreadsheet with MS Excel to see the data range, the application pops-up a message about unreadable contents in the file. When recovered, MS Excel removed the Pivot Table cache entries from the spreadsheet. This led us to believe the sample is corrupted it self and therefore could be the cause of said exception. Please see attached snapshots for your kind reference.

We are looking into this matter further to determine the problem cause. In the meanwhile, please confirm if the code snippet provided here is complete, and if you are performing other operations then please provide complete source code or a sample application to replicate the exception on our end.

7

Hi,

I have attached the file again. Please look and let me know whats the problem, why cant I create pivot table.

Thanks,

Jyotshna

8

Hi Jyotshna,

Thank you for providing the sample file again.

This time the spreadsheet seems to be correct but I am still unable to add the Pivot Table on the data present in sample. Although, I was unable to experience any exception with the following code snippet, but when resultant file is loaded with MS Excel, it pops up a message that Excel has found unreadable contents in the spreadsheet. Upon recovered, MS Excel removes the Pivot Cache, as a result the Pivot Table is blank.

C#


var book = new Workbook(@“HHF+Common+Servicer+File+Load+Status.xlsx”);
var sheetIndex = book.Worksheets.Add();
var sheet = book.Worksheets[sheetIndex];

//Getting the pivottables collection in the sheet
var pivotTables = sheet.PivotTables;

//Adding a PivotTable to the worksheet
int index = pivotTables.Add(“Data!A5:O37494”, “A8”, “PivotTable1”);

//Accessing the instance of the newly added PivotTable
Aspose.Cells.Pivot.PivotTable pivotTable = pivotTables[index];

//Showing the grand totals
pivotTable.RowGrand = true;
pivotTable.ColumnGrand = true;

//Setting the PivotTable report is automatically formatted
pivotTable.IsAutoFormat = true;

//Setting the PivotTable autoformat type.
pivotTable.AutoFormatType = Aspose.Cells.Pivot.PivotTableAutoFormatType.Report6;

//Draging the first field to the row area.
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 7);
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 4);
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 3);
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 5);

//Draging the fourth field to the column area.
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Column, 13);
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Column, 14);
///pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Column, 15);
book.Save(myDir + “out.xlsx”, SaveFormat.Xlsx);

I have logged an investigative ticket (CELLSNET-42468) in our bug tracking system to further investigate the matter, and to provide a fix at earliest (if applicable). In the meanwhile, we will keep you posted with updates in this regard.
9

Finally I am able to resolve the issue but I have one more question.

I want to add a pivot field to report filter area but I am getting errors when doing that. Please look at the below code and let me know what is wrong with it. I want to add 8th row to report filter area where value = "FALSE".

PivotField pivotField = pivotTable.RowFields[8];

PivotItemCollection items = pivotField.PivotItems;

//Get the pivot item

PivotItem item = items["FALSE"];

Thanks,

Jyotshna

10

Hi Jyotshna,

Good to know you have overcome the problem that you were facing during the creation of Pivot Table. We are interested in the source code lines that accomplished your task as we have already logged a ticket in that reference, and the source code snippet may help us resolve the ticket.

Regarding your recent inquiry, as we currently do not have the spreadsheet with Pivot Table in question therefore we can suggest a generic solution. Please check the below provided source code lines to add a Pivot Field to Pivot Filter.

C#

var pivot = sheet.PivotTables[0];

int index = pivot.PivotFilters.Add(1, PivotFilterType.Count);

Please note, the first parameter to PivotFilters.Add()<!–[if gte mso 9]>
<w:WordDocument>
<w:View>Normal</w:View>
<w:Zoom>0</w:Zoom>
<w:TrackMoves/>
<w:TrackFormatting/>
<w:PunctuationKerning/>
<w:ValidateAgainstSchemas/>
<w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid>
<w:IgnoreMixedContent>false</w:IgnoreMixedContent>
<w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText>
<w:DoNotPromoteQF/>
<w:LidThemeOther>EN-US</w:LidThemeOther>
<w:LidThemeAsian>X-NONE</w:LidThemeAsian>
<w:LidThemeComplexScript>X-NONE</w:LidThemeComplexScript>
<w:Compatibility>
<w:BreakWrappedTables/>
<w:SnapToGridInCell/>
<w:WrapTextWithPunct/>
<w:UseAsianBreakRules/>
<w:DontGrowAutofit/>
<w:SplitPgBreakAndParaMark/>
<w:EnableOpenTypeKerning/>
<w:DontFlipMirrorIndents/>
<w:OverrideTableStyleHps/>
</w:Compatibility>
<m:mathPr>
<m:mathFont m:val=“Cambria Math”/>
<m:brkBin m:val=“before”/>
<m:brkBinSub m:val="–"/>
<m:smallFrac m:val=“off”/>
<m:dispDef/>
<m:lMargin m:val=“0”/>
<m:rMargin m:val=“0”/>
<m:defJc m:val=“centerGroup”/>
<m:wrapIndent m:val=“1440”/>
<m:intLim m:val=“subSup”/>
<m:naryLim m:val=“undOvr”/>
</m:mathPr></w:WordDocument>
<![endif]–><!–[if gte mso 10]>

/* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin-top:0in; mso-para-margin-right:0in; mso-para-margin-bottom:10.0pt; mso-para-margin-left:0in; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin;}

<![endif]–> indicates the Pivot Field index whereas second parameter specifies the filter type.

It would be of great help in understanding your exact requirements if you can share a spreadsheet containing the Pivot Table in question and another spreadsheet exhibiting your desired results, that you can manually create with MS Excel. After reviewing the requirements we will try to mimic the functionality using Aspose.Cells API.

11

Thanks You for the reply. Only thing I changed is instead of having in columns area, I put those in data area and that resolved my issue since thats were I wanted and by mistake I didnt realize that it was using column as PivotFieldType.

I have attached the file for sample. I need 8th row in the Report filter area for which I was having issues, I tried several ways but that didnt work.

Please help.

12

Hi Jyotshna,


Thank you for the sample spreadsheet.

Unfortunately, I was unable to manipulate the Pivot Table present in your provided spreadsheet because I have experienced unexpected exceptions while dragging the field to Report Filter area. It seems that the spreadsheet it self is damaged too.

Anyway, I have worked over a new spreadsheet by copying the data and creating the Pivot Table from scratch. Please check the below provided code snippet and attached resultant spreadsheet for your reference. Please note, I have highlighted the source code statement to drag the field to the Report Filter area so you may use any field for your requirement.

C#

var book = new Workbook(myDir + “pivot.xlsx”);
var sheet = book.Worksheets[1];
var index = sheet.PivotTables.Add(“Data!A5:P37490”,“A8”,“Pivot”);
var pivotTable = sheet.PivotTables[0];
//Showing the grand totals
pivotTable.RowGrand = true;
pivotTable.ColumnGrand = true;
//Setting the PivotTable report is automatically formatted
pivotTable.IsAutoFormat = true;
//Setting the PivotTable autoformat type.
pivotTable.AutoFormatType = Aspose.Cells.Pivot.PivotTableAutoFormatType.Classic;

//Dragging Fields to Row Labels
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, “FileSatusType”);
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, “BoundType”);
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, “SrcName”);
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, “FileName”);

//Dragging Fields to Report Filter Area
pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Page, “AttemptedTodayBit”);

//Dragging Fields to Data Area
PivotField SrcRecCnt = pivotTable.BaseFields[“SrcRecCnt”];
SrcRecCnt.Function = ConsolidationFunction.Sum;
SrcRecCnt.NumberFormat = “#,##0.00”;
pivotTable.AddFieldToArea(PivotFieldType.Data, SrcRecCnt);

PivotField LoadedRecCnt = pivotTable.BaseFields[“LoadedRecCnt”];
LoadedRecCnt.Function = ConsolidationFunction.Sum;
LoadedRecCnt.NumberFormat = “#,##0.00”;
pivotTable.AddFieldToArea(PivotFieldType.Data, LoadedRecCnt);

PivotField DiffRecCnt = pivotTable.BaseFields[“DiffRecCnt”];
DiffRecCnt.Function = ConsolidationFunction.Sum;
DiffRecCnt.NumberFormat = “#,##0.00”;
pivotTable.AddFieldToArea(PivotFieldType.Data, DiffRecCnt);

book.Save(myDir + “output.xlsx”);
13

Hi again,


I have noticed that my previously provided code snippet isn’t fulfilling the complete requirement. The DataField, that is the virtual field was placed under Row Labels area whereas in your recently shared spreadsheet the DataField is in Column Labels area. You can drag the DataField to Column Labels using the following code snippet.

C#

pivotTable.AddFieldToArea(PivotFieldType.Column, pivotTable.DataField);

Here is the complete program for your kind reference.

C#
var book = new Workbook(myDir + "pivot.xlsx"); var sheet = book.Worksheets[1]; var index = sheet.PivotTables.Add("Data!A5:P37490", "A8", "Pivot"); var pivotTable = sheet.PivotTables[0]; //Showing the grand totals pivotTable.RowGrand = true; pivotTable.ColumnGrand = true; //Setting the PivotTable report is automatically formatted pivotTable.IsAutoFormat = true; //Setting the PivotTable autoformat type. pivotTable.AutoFormatType = Aspose.Cells.Pivot.PivotTableAutoFormatType.Classic;
//Dragging Fields to Row Labels pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, "FileSatusType"); pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, "BoundType"); pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, "SrcName"); pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, "FileName");
//Dragging Fields to Report Filter Area pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Page, "AttemptedTodayBit");
//Dragging Fields to Data Area PivotField SrcRecCnt = pivotTable.BaseFields["SrcRecCnt"]; SrcRecCnt.Function = ConsolidationFunction.Sum; SrcRecCnt.NumberFormat = "#,##0.00"; pivotTable.AddFieldToArea(PivotFieldType.Data, SrcRecCnt);
PivotField LoadedRecCnt = pivotTable.BaseFields["LoadedRecCnt"]; LoadedRecCnt.Function = ConsolidationFunction.Sum; LoadedRecCnt.NumberFormat = "#,##0.00"; pivotTable.AddFieldToArea(PivotFieldType.Data, LoadedRecCnt);
PivotField DiffRecCnt = pivotTable.BaseFields["DiffRecCnt"]; DiffRecCnt.Function = ConsolidationFunction.Sum; DiffRecCnt.NumberFormat = "#,##0.00"; pivotTable.AddFieldToArea(PivotFieldType.Data, DiffRecCnt);
if (pivotTable.DataField != null) { //DataField attribute of PivotTable exists only if DataFields contains two or more PivotField pivotTable.AddFieldToArea(PivotFieldType.Column, pivotTable.DataField); }
book.Save(myDir + "output.xlsx");

Please feel free to write back in case you need our further assistance.
14

Hi,

Thanks for using Aspose.Cells.

Please download and try the fix: Aspose.Cells for .NET 8.0.0.1 and let us know your feedback.

15

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

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