Summarised Data from Pivot Table

[vinaybabu]

Is there any way to get Summarised Data / Flattened Data from pivot Table?

Example Config

• Row fields: Location > Department > Designation > Employee Type
• Column fields: Gender > Band > Grade > Age-Group
• Aggregation fields: Headcount, Average Salary, Average Years of Experience

Each cell in the pivot table will correspond to a Location, Department, Designation, Employee Type, Gender, Bnad, Grade and Age group and will represent on of the values among Headcount, Average Salary, Average Years of Experience. Assume two sample groups:

1. Rows : Hyderabad → Engineering → Senior Developer → Full Time
2. Columns : Male → B2 Band → G3 Grade → 30-40 Age Group
   1. Values : Headcount 12, Average Salary : 1450000, Average Years of Experience : 14
3. Rows : Bangalore → Sales → Manager → Contract
4. Columns : Female → B1 Band→ G2 Grade→ 20-30 Age group
   1. Values : Headcount : 2, Average Salary : 1000000, Average Years of Experience : 6

They shall be formatted as below in the response sent.

{ "location": "Hyderabad", "department": "Engineering", "designation": "Senior Developer", "employment_type": "Full-time", "gender": "Male", "band": "B2", "grade": "G3", "age_group": "30-40", "headcount": 12, "average_salary": 1450000, "average_experience_years": 14 }, { "location": "Bangalore", "department": "Sales", "designation": "Manager", "employment_type": "Contract", "gender": "Female", "band": "B1", "grade": "G2", "age_group": "20-30", "headcount": 2, "average_salary": 1000000, "average_experience_years": 6 }

[Professionalize.Discourse]

@vinaybabu

To extract summarized or flattened data from a pivot table in Aspose.Cells, you can iterate through the pivot table’s data and format it as needed. Below is an example of how you can achieve this in C#:

using Aspose.Cells;
using System;
using System.Collections.Generic;

class Program
{
    static void Main()
    {
        // Load the workbook
        Workbook workbook = new Workbook("your_file.xlsx");
        Worksheet worksheet = workbook.Worksheets[0];

        // Access the pivot table
        PivotTable pivotTable = worksheet.PivotTables[0];

        // List to hold the summarized data
        List<Dictionary<string, object>> summarizedData = new List<Dictionary<string, object>>();

        // Iterate through the pivot table
        for (int rowIndex = 0; rowIndex < pivotTable.RowCount; rowIndex++)
        {
            for (int colIndex = 0; colIndex < pivotTable.ColumnCount; colIndex++)
            {
                // Create a dictionary to hold the row data
                var rowData = new Dictionary<string, object>
                {
                    { "location", pivotTable.GetRowFieldValue(rowIndex, 0) },
                    { "department", pivotTable.GetRowFieldValue(rowIndex, 1) },
                    { "designation", pivotTable.GetRowFieldValue(rowIndex, 2) },
                    { "employment_type", pivotTable.GetRowFieldValue(rowIndex, 3) },
                    { "gender", pivotTable.GetColumnFieldValue(colIndex, 0) },
                    { "band", pivotTable.GetColumnFieldValue(colIndex, 1) },
                    { "grade", pivotTable.GetColumnFieldValue(colIndex, 2) },
                    { "age_group", pivotTable.GetColumnFieldValue(colIndex, 3) },
                    { "headcount", pivotTable.GetValue(rowIndex, colIndex, 0) },
                    { "average_salary", pivotTable.GetValue(rowIndex, colIndex, 1) },
                    { "average_experience_years", pivotTable.GetValue(rowIndex, colIndex, 2) }
                };

                summarizedData.Add(rowData);
            }
        }

        // Output the summarized data
        foreach (var data in summarizedData)
        {
            Console.WriteLine(Newtonsoft.Json.JsonConvert.SerializeObject(data, Newtonsoft.Json.Formatting.Indented));
        }
    }
}

In this code:

• Replace "your_file.xlsx" with the path to your Excel file.
• The GetRowFieldValue and GetColumnFieldValue methods are used to retrieve the values from the pivot table.
• The summarized data is stored in a list of dictionaries, which can be easily serialized to JSON format.

This approach effectively flattens the pivot table data into a more usable format. If you have any specific requirements or need further assistance, feel free to ask!

[vinaybabu]

Aspose.cells via node js please

[amjad.sahi]

@vinaybabu,

I think you will need to build the logic yourself by reading the pivot table structure + aggregated values, iterating through the combinations of row & column fields and constructing your output. Then, you may build your JSON object with properties for the row fields + column fields + data values to export the contents. Moreover,You should also refresh the pivot data to ensure values are current:

pivotTable.refreshData();
pivotTable.calculateData();

In case, you still could not accomplish your task, please zip and attach your template Excel file containing the pivot table. We will look into it soon.

[John.He]

@vinaybabu
Please refer to the following example code for Aspose.Cells for Node.js via C++ and check the attachment. pivot-nodejs-cpp.zip (13.1 KB)

const AsposeCells = require("aspose.cells.node");

console.log(AsposeCells.CellsHelper.getVersion());
var workbook = new AsposeCells.Workbook(AsposeCells.FileFormatType.Xlsx);
// Get the first worksheet in the worksheets collection.
const sheet = workbook.getWorksheets().get(0);

const cells = sheet.getCells();

const json = "[{ \"location\": \"Hyderabad\", \"department\": \"Engineering\", \"designation\": \"Senior Developer\", \"employment_type\": \"Full-time\", \"gender\": \"Male\", \"band\": \"B2\", \"grade\": \"G3\", \"age_group\": \"30-40\", \"headcount\": 12, \"average_salary\": 1450000, \"average_experience_years\": 14 }, { \"location\": \"Bangalore\", \"department\": \"Sales\", \"designation\": \"Manager\", \"employment_type\": \"Contract\", \"gender\": \"Female\", \"band\": \"B1\", \"grade\": \"G2\", \"age_group\": \"20-30\", \"headcount\": 2, \"average_salary\": 1000000, \"average_experience_years\": 6 }]";
var jsonLayoutOptions = new AsposeCells.JsonLayoutOptions();
AsposeCells.JsonUtility.importData(json, cells, 0, 0, jsonLayoutOptions);


const pivotTables = sheet.getPivotTables();

// Adding a PivotTable to the worksheet
const index = pivotTables.add("=A1:K3", "A9", "Test Pivot");

// Accessing the instance of the newly added PivotTable
const pivotTable = pivotTables.get(index);

// Row fields: location > department > designation > employment_type
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Row, "location");
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Row, "department");
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Row, "designation");
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Row, "employment_type");

// Column fields: gender > band > grade > age_group
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Column, "gender");
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Column, "band");
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Column, "grade");
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Column, "age_group");

// Aggregation fields: headcount, Average average_salary, average_experience_years
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Data, "headcount");
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Data, "average_salary");
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Data, "average_experience_years");

pivotTable.setPivotTableStyleType(AsposeCells.PivotTableStyleType.PivotTableStyleLight10);

sheet.refreshPivotTables();

console.log(pivotTable.getTableRange2().toString());



const firstColumn = pivotTable.getTableRange2().startColumn;
const lastColumn = pivotTable.getTableRange2().endColumn;
const lastRow = pivotTable.getTableRange2().endRow;
const dataCount = pivotTable.getDataFields().getCount();

for (var c = dataCount - 1; c >= 0; c--)
{
    console.log(cells.get(lastRow - c, firstColumn).stringValue + ": " + cells.get(lastRow - c, lastColumn).stringValue);
}

workbook.save("pivot-nodejs-cpp.xlsx");

The output:

25.10.0
Aspose.Cells.CellArea(A9:N40)[8,0,39,13]
Total Sum of headcount: 14
Total Sum of average_salary: 2450000
Total Sum of average_experience_years: 20

Hope helps a bit.