Pivot Table error

Aspose.Cells Product Family
1

Hi, IAM getting below error while trying to do pivot

Error running instance method\ncom.aspose.cells.CellsException: This pivot field has more unique items than can be used in a pivot table.\n\tat com.aspose.cells.PivotFieldCollection.a(Unknown Source)\n\tat com.aspose.cells.PivotFieldCollection.add(Unknown Source)\n\tat com.aspose.cells.PivotTable.addFieldToArea(Unknown Source)\n\tat com.aspose.cells.PivotTable.addFieldToArea(Unknown Source)\n\tat java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)\n\tat java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)\n\tat java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)\n\tat java.base/java.lang.reflect.Method.invoke(Method.java:569)\n\tat node.MethodCallBaton.invokeMethod9(MethodCallBaton.java:25)\n "

Below is my code to add fields to pivot

_.map(rowLabels, (eachGrpLabel) => {
eachGrpLabel =
eachGrpLabel.alias ??
toTitleCase(eachGrpLabel.systemAlias ?? eachGrpLabel.label);
pivotTable.addFieldToArea(
aspose.cells.PivotFieldType.ROW,
eachGrpLabel
);
});
_.map(columnLabels, (eachCatLabel) => {
eachCatLabel =
eachCatLabel.alias ??
toTitleCase(eachCatLabel.systemAlias ?? eachCatLabel.label);
pivotTable.addFieldToArea(
aspose.cells.PivotFieldType.COLUMN,
eachCatLabel
);
});

What is the solution for this?

2

@vinaybabu

The error you are encountering, “This pivot field has more unique items than can be used in a pivot table,” typically occurs when the number of unique items in a pivot field exceeds the limit that can be handled by the pivot table. This limit can vary depending on the version of Excel and the specific implementation of the pivot table.

To resolve this issue, you can consider the following approaches:

  1. Reduce Unique Items: If possible, try to reduce the number of unique items in the fields you are adding to the pivot table. This can be done by filtering the data or aggregating similar items.

  2. Check Data Source: Ensure that the data source you are using for the pivot table is appropriate and does not contain excessive unique values that are unnecessary for your analysis.

  3. Use Grouping: If your data allows, consider grouping certain items together to reduce the number of unique entries. This can help in fitting within the limits of the pivot table.

  4. Review Pivot Table Configuration: Make sure that the fields you are adding to the pivot table are correctly configured and that you are not inadvertently adding fields that contribute to the high number of unique items.

Here is a simplified example of how you might add fields to a pivot table while ensuring you manage the unique items:

// Assuming 'pivotTable' is your PivotTable instance
foreach (var eachGrpLabel in rowLabels)
{
    string label = eachGrpLabel.alias ?? toTitleCase(eachGrpLabel.systemAlias ?? eachGrpLabel.label);
    if (CanAddToPivot(label)) // Implement this method to check if the label can be added
    {
        pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, label);
    }
}

foreach (var eachCatLabel in columnLabels)
{
    string label = eachCatLabel.alias ?? toTitleCase(eachCatLabel.systemAlias ?? eachCatLabel.label);
    if (CanAddToPivot(label)) // Implement this method to check if the label can be added
    {
        pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Column, label);
    }
}

In this example, CanAddToPivot would be a method you implement to check if adding a particular label would exceed the unique item limit.

If you continue to face issues, consider checking the documentation for any specific limits related to the version of Aspose.Cells you are using, or consult the community for further insights.

Sources:
[1]: CreatePivotTableWithFormatting.cs
[2]: CreatePivotTable.cs

3

@vinaybabu,

Please note, since the release of Aspose.Cells v24.10, we included an enhancement regarding pivot table feature to cope with similar issue. Please note, we have added (overloaded) method: public int Add(string sourceData, string cell, string tableName, bool useSameSource, bool isXlsClassic) of PivotTableCollection that you may use in code (while adding the pivot table) with the new release to sort out your issue. Please see the concerned thread for your reference.
You may try setting isXlsClassic to false which may resolve your issue.

In case, you still find the issue, kindly provide a sample (console) application/program (complete source code without compilation errors) along with a sample Excel files (if any) to reproduce the issue on our end? We will check your issue soon.

PS. Please zip the resource files before attaching them.

4

Hi @amjad.sahi can you share sample code how to use the fuction

5

@vinaybabu,

You just need to use the suggested PivotTableCollection.add() overload instead in code. If you still could not make it work, kindly provide your sample (console) application/program (complete source code without compilation errors) along with a Excel file(s). We will check and assist you soon.

6

@vinaybabu
Please refer to the following java example code:

Workbook workbook = new Workbook();
Worksheet sheet = workbook.getWorksheets().get(0);
Cells cells = sheet.getCells();
cells.get("A1").putValue("a");
cells.get("B1").putValue("b");
cells.get("C1").putValue("c");
for (int i = 1; i < 65535; i++)
{
    cells.get(i, 0).putValue(i);
    cells.get(i, 1).putValue(i);
    cells.get(i, 2).putValue(i);
}


// Add a Pivot Table
int pivotTableIndex = sheet.getPivotTables().add("=A1:C133823", "J1", "PivotTable1", false, false);
PivotTable pivotTable = sheet.getPivotTables().get(pivotTableIndex);

// Set the row fields
pivotTable.addFieldToArea(PivotFieldType.ROW, 0);

// Set the data field
pivotTable.addFieldToArea(PivotFieldType.DATA, 2);

workbook.save(filePath + "out_java.xlsx");

Hope helps a bit.