Create excel sheet from the pivot data

[vijaysebastian]

How I can create an excel sheet from 'pivot' variable

const pivot = new Pivot(array, ['Region'], ['ActiveCustomers','ActiveUsers'], 'viewaccess', 'sum').collapseAll();

my data looks like

[ { value: [ 'sum viewaccess', '2', 'Totals' ], depth: 0, type: 'colHeader', row: 0 }, { value: [ 'sum viewaccess', '1', '' ], depth: 1, type: 'colHeader', row: 1 }, { value: [ 'North West', 0, 0 ], type: 'data', depth: 0, row: 2 }, { value: [ 'Totals', 0, '' ], type: 'aggregated' } ]

[daveRanjan]

@pat310 Do we have any update on this? I need to provide data in excel (csv can also work) format.

[ghost]

@daveRanjan @vijaysebastian : You can use this cool project https://github.com/exceljs/exceljs to make an Excel file.

Example in nodejs :

const ExcelJS = require('exceljs');
const Pivot = require('quick-pivot');

// Self-Executing Anonymous Functions
(async function(){

const wb = new ExcelJS.Workbook();
wb.creator = 'Example';
const sheet2 = wb.addWorksheet('Test',
    {
        views: [
            {state: 'frozen', ySplit: 1}
        ],
        properties:
            {
                tabColor:
                    {argb: 'FFC0000'}
            }
    }
);

// Load your data, make your pivot table
// For example

const dataArray = [
    ['name', 'gender', 'house', 'age'],
    ['Jon', 'm', 'Stark', 14],
    ['Arya', 'f', 'Stark', 10],
    ['Cersei', 'f', 'Baratheon', 38],
    ['Tywin', 'm', 'Lannister', 67],
    ['Tyrion', 'm', 'Lannister', 34],
    ['Joffrey', 'm', 'Baratheon', 18],
    ['Bran', 'm', 'Stark', 8],
    ['Jaime', 'm', 'Lannister', 32],
    ['Sansa', 'f', 'Stark', 12]
];

const rowsToPivot = ['name'];
const colsToPivot = ['house', 'gender'];
const aggregationDimension = 'age';
const aggregator = 'sum';

const pivot = new Pivot(dataArray, rowsToPivot, colsToPivot, aggregationDimension, aggregator);

pivot.data.table.forEach(line => {
    // rowHeader in bold for example
    switch(line.type) {
        case 'rowHeader' :
            sheet2.addRow(line.value).font = {
                bold: true
            }
            break;
        case 'aggregated':
            sheet2.addRow(line.value).font = {
                bold: true
            }
            break;
        default:
            sheet2.addRow(line.value)
    }
})

await wb.xlsx.writeFile('your-physical-file.xlsx');

})();