Pivot creation issue

Aspose.Cells Product Family
1

Hi Team,

Please check the attached pivot TEST.zip (140.1 KB)

Code snippet for creating pivot. Here, i just set the column names to respective fields

private void configurePivot(Header header, int rowCount, int colCount) {
List pivots = header.getPivots();
if (pivots != null) {
for (Pivot pivot : pivots) {
try {
String dataSheetName = “Data”;
String pivotName = pivot.getName();
Worksheet worksheet = (this.workbook.getWorksheets()).add(pivotName);
PivotTableCollection pivotTables = worksheet.getPivotTables();
String sourceData = “’” + dataSheetName + “’!$A$” + (1) + “:” + CellsHelper.cellIndexToName(rowCount, colCount - 1);
int index = pivotTables.add(sourceData, “A3”, pivotName);
PivotTable pivotTable = pivotTables.get(index);
pivotTable.setName(pivotName);
pivotTable.setGridDropZones(true);
pivotTable.setRowGrand(false);

                PageField pf = pivot.getPageField();
                ColumnField cf = pivot.getColumnField();
                RowField rf = pivot.getRowField();
                DataField df = pivot.getDataField();

                if (pf != null) {
                    if (pf.getColumns() != null) {
                        for (String column : pf.getColumns()) {
                            pivotTable.addFieldToArea(PivotFieldType.PAGE, column);
                        }
                    }
                }

                if (cf != null) {
                    if (cf.getColumns() != null) {
                        for (String column : cf.getColumns()) {
                            pivotTable.addFieldToArea(PivotFieldType.COLUMN, column);
                        }
                    }
                }

                if (rf != null) {
                    if (rf.getColumns() != null) {
                        for (String column : rf.getColumns()) {
                            pivotTable.addFieldToArea(PivotFieldType.ROW, column);
                        }
                    }
                }

                if (df != null) {
                    if (df.getColumns() != null) {
                        for (String column : df.getColumns()) {
                            pivotTable.addFieldToArea(PivotFieldType.DATA, column);
                        }
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

The pivot is not shown the same way i manually create pivot in MS Excel. Please let me know what am i doing wrong here.

Thanks,
Sukesh

2

@sukesh.kotian

Thanks for using Aspose APIs.

Please provide us your expected pivot table. You can create one manually using Microsoft Excel and provide it to us. This will enable us to understand the structure of your pivot table and we will provide you a sample code to achieve it via Aspose.Cells.

3

Hi Shakeel,

Please check the attached image pivot.png (41.4 KB)

4

@sukesh.kotian

Please see the following sample code in Java and C#. The code creates your desired pivot table as shown in the following screenshot. Please download the output Excel file generated by the code and read the comments inside the code for more help.

Download Link:
Output Pivot Table Excel File–1.zip (203.1 KB)

Java

//Load the source Excel file
Workbook wb = new Workbook(dirPath + "TEST.xlsx");

//Add a new worksheet
Worksheet ws = wb.getWorksheets().add("TestSheet");

//Activate the worksheet
wb.getWorksheets().setActiveSheetIndex(ws.getIndex());

//Add a pivot table
int idx = ws.getPivotTables().add("Data!$A$1:$I$2474", "C4", "MyPivot");

//Access first pivot table
PivotTable pt = ws.getPivotTables().get(0);

//Refresh and calculate data
pt.refreshData();
pt.calculateData();

//Access the sale price and product line fields
PivotField salePrice = pt.getBaseFields().get(3);
PivotField prodLine = pt.getBaseFields().get(5);

//Add fields to row and data area
pt.addFieldToArea(PivotFieldType.ROW, prodLine);
pt.addFieldToArea(PivotFieldType.DATA, salePrice);

//Set the consolidation function to Count, you can set it to Sum, Average etc.
pt.getDataFields().get(0).setFunction(ConsolidationFunction.COUNT);

//Refresh and calculate data
pt.refreshData();
pt.calculateData();
          
//Save the output Excel file
wb.save(dirPath + "output.xlsx");

C#

//Load the source Excel file
Workbook wb = new Workbook("TEST.xlsx");

//Add a new worksheet
Worksheet ws = wb.Worksheets.Add("TestSheet");

//Activate the worksheet
wb.Worksheets.ActiveSheetIndex = ws.Index;

//Add a pivot table
int idx = ws.PivotTables.Add("Data!$A$1:$I$2474", "C4", "MyPivot");

//Access first pivot table
PivotTable pt = ws.PivotTables[0];

//Refresh and calculate data
pt.RefreshData();
pt.CalculateData();

//Access the sale price and product line fields
PivotField salePrice = pt.BaseFields[3];
PivotField prodLine = pt.BaseFields[5];

//Add fields to row and data area
pt.AddFieldToArea(PivotFieldType.Row, prodLine);
pt.AddFieldToArea(PivotFieldType.Data, salePrice);

//Set the consolidation function to Count, you can set it to Sum, Average etc.
pt.DataFields[0].Function = ConsolidationFunction.Count;

//Refresh and calculate data
pt.RefreshData();
pt.CalculateData();
      
//Save the output Excel file
wb.Save("output.xlsx");

Output Excel File - Screenshot